[Analytic Block] Creating Custom Cohort Analysis

  • 17 January 2015
  • 0 replies

Userlevel 1

Looker has created this block to make analyzing data easier and more efficient. This Data Block is made available on an “as is” basis, meaning there will not be updates moving forward.

Please note that this pattern expands on concepts described in Creating Custom Cohorts and provides opportunities for further customization of the cohorts, along with additional cohort attributes, based on user-selected criteria.



About This Block


Understanding customer patterns over time can yield valuable insights into customer behavioral and purchasing patterns along with the activities that drive those patterns. These insights provide opportunities to employ sales-targeting-and-optimization techniques:



  • How much is a user who signed up in 2016 worth in their first month when compared to a user who signed up in 2018?
  • Which customer cohort has provided the most relative revenue? Did the cohort come through Facebook, Google, or a deep link?
  • How sticky are certain activities? Which generate the most loyal customers?
  • Did certain cohorts generate healthy revenue initially but fall off as time went on?


Ideal Data Types


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



Expected Output


In this example, we will compute revenue per user signup (revenue/signup). To evaluate these metrics for different cohorts of users, we will create a parameterized derived table that allows us to dynamically calculate cohort size.


Note that this design pattern can be used to compute revenue/signup for users by attribution channel, signup date, state, behavior, and any other user attribute, allowing us to quantify the historical value of users in different cohorts.



Revenue per Signup by Months Since Signup Tier



Total revenue divided by total cohort size, segmented by months since user signup:



Analytic Block - Creating Custom Cohort Analysis 1

Revenue per User Signup in California


Adding a filter for the value California in the users.state field computes both revenue and cohort size for California, showing us revenue/signup in California:



Analytic Block - Creating Custom Cohort Analysis 2

Revenue per User Signup in a User’s First Month


Trend over time of revenue/signup, in the first month after a user signs up:



Analytic Block - Creating Custom Cohort Analysis 13

Revenue and Revenue/Signup by Signup Month and Months Since Signup


Total revenue and revenue/signup by vintage, across different stages (months since signup):




Analytic Block - Creating Custom Cohort Analysis 4

How It’s Done


We will use a standard e-commerce model with users and orders tables as the basis for the analysis.


  • First, we will add three new dimensions to the orders view, to segment revenue/signup by the months that have elapsed since a user signed up. For example, first month since signup, first six months, and so on.


Note: The SQL in the sql parameters and derived table definitions below may need to be adjusted accordingly for your specific database SQL Dialect.​​​​

Dimensions for days and months since user signup:


dimension: days_since_user_signup {

hidden: yes

type: number

sql: DATEDIFF(${created_raw}, ${users.created_raw});;


dimension: months_since_user_signup {

type: number

sql: FLOOR(${days_since_user_signup}/(30)) ;;


dimension: months_since_user_signup_tier {

type: tier

tiers: [1,3,6,12,24]

style: integer

sql: ${months_since_user_signup} ;;


  • Next, we will create a derived table, user_cohort_size, to calculate cohort size.
  • We will include templated filters on users.age and users.state, so that the cohort size is adjustable by those attributes.
  • Below is the full model. Note that the standard join pattern is reversed. Instead of starting with order_items and joining to users (order_items -> orders -> users), we start with users and join to order_items (users -> orders -> order_items). We do this to include all users, as opposed to only those users who have placed an order.


Here, you can also apply any number of filters to further dynamically segment your cohort sample:



# Uses the common Ecommerce Views

include: "*ecommerce.view"

explore: users {

join: orders {

sql_on: ${orders.user_id} = ${users.id} ;;

relationship : one_to_many


join: order_items {

sql_on: ${order_items.order_id} = ${orders.id} ;;

relationship : one_to_many


join: user_cohort_size {

sql_on: ${user_cohort_size.created_month} = ${users.created_month}

relationship: many_to_one;;


# Parameterized derived table to calculate cohort size

view: user_cohort_size {




DATE_FORMAT(CONVERT_TZ(u.created_at,'UTC','America/Los_Angeles'),'%Y-%m') AS created_month

, COUNT(*) as cohort_size

FROM users u


-- Insert filters here using a condition statement, you may add as many filters as desired

{% condition users.age %} u.age {% endcondition %}

AND {% condition users.state %} u.state {% endcondition %}


indexes: [created_month]

dimension: created_month {

primary_key: true


dimension: cohort_size {

type: number


measure: total_cohort_size {

type: sum

sql: ${cohort_size} ;;


measure: total_revenue_over_total_cohort_size {

type: number

sql: ${order_items.total_sale_price} / ${total_cohort_size} ;;

value_format: '$#,##0'


0 replies

Be the first to reply!