[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

[anika1]

If you’d like like to compare this to total cohort size, check out this article! Cohort Exploration Normalized by Cohort Size (no modeling required)

[lyndsey_pileggi]

Great use case however, it appears some of the links no longer work.

[izzymiller]

Which links aren’t working? I tried to root em out but couldn’t find a broken one.

[Krishna_Shukla]

Hi @izzymiller, I am trying to implement the same but unfortunately the links given in block https://blocks.looker.com/embed/explore/cohorting_users_by_creation/order_items?show=data&query=jZRg... is not working.

[izzymiller]

I think that is a placeholder link that you’d have to flesh out with your own looker instance name (not just blocks.looker.com) and change the explore name + field names as well if you’ve changed any of those.