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

[lindsey1]

Analytic Block

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