Analytic Block

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

  • 15 May 2015
  • 5 replies
  • 2425 views

Userlevel 5
Badge

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

5 replies

Userlevel 1

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.

Userlevel 7
Badge +1

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

Userlevel 1

Hi @izzy, 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=jZRgST6&vis={}&filter_config={"orders.created_month":[{"type":"past","values":[{"constant":"12","unit":"mo"},{}],"id":0}],"users.created_month":[{"type":"past","values":[{"constant":"12","unit":"mo"},{}],"id":1}]}&f[orders.created_month]=12%20months&f[users.created_month]=12%20months is not working.

Userlevel 7
Badge +1

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.

Reply