Evaluating customer behavior over time can provide a number of insights into the lifetime purchasing patterns of your customer base.
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.
This block can be applied to any data where users are being created and performing an transaction (such as purchase).
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:
You can then visualize this data as cohorts like this:
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.
Create a dimension_group
for each of your date fields in their associated view files.
Create a count measure in one of the views.
Finally, make sure to join these views in your model file!
- 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]
- explore: orders # Step 3: join the two views in the model file
joins:
- join: users
foreign_key: user_id
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)
Great use case however, it appears some of the links no longer work.
Which links aren’t working? I tried to root em out but couldn’t find a broken one.
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.
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.