[Analytic Block] Creating Custom Cohort Analysis

[zachtaylor]

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:

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:

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:

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

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

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 {



derived_table:



sql:



SELECT



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



, COUNT(*) as cohort_size



FROM users u



WHERE



-- 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 %}



GROUP BY 1 ;;



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'



}