Cohort Analysis

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

If the first visit date is available in the dimension:
- Lookup the user’s First Visit Date from the dimension table in a custom column of the fact table based on the userID
- Build the EC change
- Create three custom columns for the cohort calculations
- Day Cohort = daydiff([First Visit Date], Activity Date)
- Month Cohort = monthdif([First Visit Date], Activity Date)
- Year Cohort = yeardif([First Visit Date], Activity Date)
If the first visit date is NOT available in the dimension:
- 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
- In a custom column in the dimension table lookup the minimumDate from the custom SQL table based on the userID
- Lookup the user’s First Visit Date from the dimension table in a custom column of the fact table based on the userID
- Build the EC change
- Create three custom columns for the cohort calculations
- Day Cohort = daydiff([First Visit Date], Activity Date)
- Month Cohort = monthdif([First Visit Date], Activity Date)
- Year Cohort = yeardif([First Visit Date], Activity Date)
Dashboarding (Example Showing Month Use Case)
For a pivot table:
- 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.
- Put the matching cohort column from the fact table into columns of the pivot table. In this case we will use Month Cohort.
- 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:
- Duplicate the pivot table you just created
- Move the Month Cohort card into the Xaxis panel
- Move the First Month card into the Break By panel
Updated 03-02-2023
intapiuser
Admin
Joined December 15, 2022