cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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
Rate this article:
Version history
Last update:
‎03-02-2023 08:34 AM
Updated by:
Contributors