[Analytic Block] Cohorting Users by Creation Date and Order Transactions

About This Block

Evaluating customer behavior over time can provide a number of insights into the lifetime purchasing patterns of your customer base.

• Do your users come back and buy often?


• Of users that signed up 6 months ago, how many are still buying today?


• Is there a drop-off point where customers cease purchasing?


• Have certain events or promotions triggered certain customer segments to make more purchases than others?

These questions can serve as jumping off points for further analysis. Individual cohorts can be isolated and further segmented to understand your consumer base at every level.

Ideal Data Types

This block can be applied to any data where users are being created and performing an transaction (such as purchase).

Expected Output

In this example, we use Orders Created Month and Users Created Month to see when users cohorted by created month made orders. This shows the number of orders made by each user cohort for each order month. This can show you how many orders a user group makes X months after creation, as well as how the month of creation affects that pattern.

Simply add Orders Created Month and a count to an explore, and pivot by Users Created Month:

Explore Data in Full Screen

You can then visualize this data as cohorts like this:

Explore Data in Full Screen

Try it Yourself!

How it’s Done

For this block, you simply need two dates: the date a user was created or signed up, and the date of a transaction or order.

1. 
   

   Create a dimension_group for each of your date fields in their associated view files.

   
   


2. 
   

   Create a count measure in one of the views.

   
   


3. 
   

   Finally, make sure to join these views in your model file!

   
   

In view files:

- view: users

  fields:

  - dimension: id

    type: int

    primary_key: true

    sql: ${TABLE}.id



  - dimension_group: created       # Step 1: A dimension group for

    type: time                     # user created date

    timeframes: [date, week, month, year]

    sql: ${TABLE}.created_at

- view: orders

  fields:

  - dimension: id

    type: int

    primary_key: true

    sql: ${TABLE}.id



  - dimension: user_id

    type: int

    sql: ${TABLE}.user_id



  - dimension_group: created       # Step 1: A dimension group for

    type: time                     # order created date

    timeframes: [date, week, month, year]

    sql: ${TABLE}.created_at



  - measure: count                 # Step 2: A count of orders

    type: count

    drill_fields: [id, created_date, users.id]

In the model file:

- explore: orders                 # Step 3: join the two views in the model file

  joins:

  - join: users

    foreign_key: user_id