Cohort Analysis - Scoring a member-based e-commerce business with binaries and pivot tables

Project Milestone 1 - October 28, 2012:

In working with my business' data in the templates provided by Cassie, I've elected to focus on the following metrics:
1. Time to first purchase or engagement (e.g. follow, post)
2. % activating in first 1 day, 7 days, 30 days, etc. (B2C)
3. Repeat rate, 2x+ repeat rate, 3x+ repeat rate, etc.
4. How does first purchase (or engagement proxy) impact future revenue/engagement?
5. Email Opt-out behavior or member churn 
The areas that I can easily score with the 1/0 rating are:
1. Opted Out
2. 1+, 2+, 3+ Orders
3. Purchased in Last 30
4. Purchased in Last 90
I've been able to leverage a customer and an order file in my data set and I've used lookups and formuals to pull things together into one working sheet. As Cassie pointed out in her second office hours, it is important to use formulas only on the initial calculation and then convert the colums of data to values only, as the files sizes get huge quickly. Also, the trickiest calculations are best handled in pivot tables. I think once the excel formuals gets beyond the intermediate level, its time to move into pivot tables or restructure the data. 


Please sign in or sign up to comment.