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.
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:
This block can be applied to any data where users are being created and are performing a transaction (such as making a purchase).
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.
Total revenue divided by total cohort size, segmented by months since user signup:
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:
Trend over time of revenue/signup, in the first month after a user signs up:
Total revenue and revenue/signup by vintage, across different stages (months since signup):
We will use a standard e-commerce model with users
and orders
tables as the basis for the analysis.
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} ;;
}
user_cohort_size
, to calculate cohort size.users.age
and users.state
, so that the cohort size is adjustable by those attributes.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'
}