Cohort Analysis

1. Lookup the user’s First Visit Date from the dimension table in a custom column of the fact table based on the userID
2. Build the EC change
3. Create three custom columns for the cohort calculations
4. Day Cohort = daydiff([First Visit Date], Activity Date)
5. Month Cohort = monthdif([First Visit Date], Activity Date)
6. Year Cohort = yeardif([First Visit Date], Activity Date)

1. Create a custom SQL table that gets the Minimum (first) visit date by user

1. In a custom column in the dimension table lookup the minimumDate from the custom SQL table based on the userID
2. Lookup the user’s First Visit Date from the dimension table in a custom column of the fact table based on the userID
3. Build the EC change
4. Create three custom columns for the cohort calculations
5.        Day Cohort = daydiff([First Visit Date], Activity Date)
6.        Month Cohort = monthdif([First Visit Date], Activity Date)
7.        Year Cohort = yeardif([First Visit Date], Activity Date)

Dashboarding (Example Showing Month Use Case)

1. Put the dimension’s First Visit Date into Rows grouped by whatever cohort granularity you want to use. In this case we will use Months.
2. Put the matching cohort column from the fact table into columns of the pivot table. In this case we will use Month Cohort.
3. Write a formula that will divide each cohort unique count of users by the 0 month cohort (the full potential visitors)

1. Duplicate the pivot table you just created
2. Move the Month Cohort card into the Xaxis panel
3. Move the First Month card into the Break By panel