Cohort Analysis

[intapiuser]

Data Preparation

Orange: Borrowed from another table via lookup()
Blue: Custom column

If the first visit date is available in the dimension:

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)

If the first visit date is NOT available in the dimension:

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

Select userID, min([activity date]) as minimumDate from [Fact Activity] group by userID

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)

For a pivot table:

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)

COUNT(userID) / (COUNT(userID), [Month Cohort])

NOTE: Month Cohort is FUNCTION FILTER called a Measured Value

For a line chart for every cohort:

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