Cohort Exploration Normalized by Cohort Size (no modeling required)

retention
cohort
explore
(Anika Kuesters Smith) #1

##Assumptions and Background:

This Cohort Block covers creation of a cohort of user transactions by user creation and transaction month. This post’s exploration depends on the same basic model being present:

In view files:

- view: users
  fields:
  - dimension: id
    type: number
    value_format_name: id
    primary_key: true
    sql: ${TABLE}.id

  - dimension_group: created
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.created_at

  - measure: count
    type: count
- view: orders
  fields:
  - dimension: id
    type: number
    value_format_name: id
    primary_key: true
    sql: ${TABLE}.id

  - dimension: user_id
    type: number
    value_format_name: id
    sql: ${TABLE}.user_id

  - dimension_group: created
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.created_at

In the model file:

- explore: orders
  joins:

  - join: users
    relationship: many_to_one
    type: left_outer
    sql_on: ${orders.user_id} = ${users.id}

Important Note: This pattern shows the cohort of users who have ever actually made a purchase. If a user signed up and never made a purchase, they are not included in this cohort. If you need to know the original size of the user creation cohort, regardless of whether that user eventually made an order, you’ll need a derived table (pattern described in Calculating Cohort Size for Cohorting).

##What’s Next?
One might then wish to look at the percent of cohort remaining, month over month. This can be accomplished with Table Calculations and Totals, as shown here (note that we’re using a user count rather than order count):

Explore this Data

Here’s the definition for the Table Calculation:

With a little bit of modeling, we can cohort by user first transaction and time since that transaction. Read more here.

2 Likes

Expanding on Cohorts Normalized by Cohort Size: Cohorts based on first transaction