About This Block

One of the most common requests we receive at Looker is for a better way to perform cohort analysis. Charts like these have become commonplace in modern data applications:

From gaming apps to retail stores, countless businesses want to understand how well they are retaining various cohorts of customers and how to effectively leverage that understanding. This means that countless businesses have also felt the pain of having to write some very complex queries.

Retention analysis works with several different data structures. The pattern below utilizes data that contains a users table and a transactions table.

Try It Yourself

It's difficult to analyze cohort-based retention when we use traditional SQL, because we want to simultaneously see the number of users in a cohort (time-invariant) and the number of users in that cohort with activity in a given month (time-variant). So, we want to group by two different months: signup_month and activity_month. But, when we group by activity_month, we want to make sure we don't exclude users without any activity that month. Nasty stuff.

Traditionally, the solution would be to calculate each number separately with multiple queries and then stitch the results together in Excel. But that approach isn't flexible: It doesn't allow us to slice and dice by all the other user attributes in the database (among other applications), and we certainly don't get to drill in to the individual users. Seems like a waste of all that data!

Lookering for a Better Way

The trick is using derived tables to define one table with a large join. Remember, a derived table is not designed to provide an answer to an actual business question; instead, it transforms the underlying data so that, when users start exploring, they see a more meaningful result.

For example, we want our users to be able to explore in Looker to answer a question like this one: "For users acquired from paid search versus organic traffic, what percentage of users are still active after three months, and have those percentages been getting better or worse over time?"

To achieve this, we need the underlying data to contain a row for each user_month combination that demonstrates each user's behavior in each month — including months with no user activity at all — and has the ability to join in all relevant user attributes. To visualize this, imagine a gaming company has users in an app.

Here is a simple schema illustrating what the raw data might look like:

Table 1: Users

Table 2: App Logins

Now, we can use the following LookML pseudo-code to transform the data into a derived table, which gives us one row for each user-month combination and tells us about each user's activity in that month, including months with no activity.

Starting in Looker 7.4, the filters parameter syntax has changed. See the filters parameter documentation page to view the new syntax.

view: monthly_activity {
  derived_table: {
    sql_trigger_value: select current_date ;;
    sortkeys: ["signup_month"]
    distribution: "user_id"
    sql: SELECT

          users.id as user_id
        , date_trunc ('month', users.created_at) as signup_month
        , month_list.purchase_month as purchase_month
        , COALESCE(data.monthly_purchases, 0) as monthly_purchases
        , COALESCE(data.total_purchase_amount, 0) as monthly_spend
        , row_number() over() AS key
      FROM
        users

      LEFT JOIN

        (
          SELECT
            DISTINCT(date_trunc('month', order_items.created_at)) as purchase_month
          FROM order_items
        ) as month_list
      ON month_list.purchase_month >= date_trunc ('month', users.created_at) -- your dialect will vary

      LEFT JOIN

        (
          SELECT
                o.user_id
              , date_trunc('month', o.created_at) as purchase_month
              , COUNT(distinct o.id) AS monthly_purchases
              , sum(o.sale_price) AS total_purchase_amount

          FROM order_items o
          GROUP BY 1,2
        ) as data
      ON data.purchase_month = month_list.purchase_month
      AND data.user_id = users.id
       ;;
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension_group: signup {
    type: time
    timeframes: [month]
    sql: ${TABLE}.signup_month ;;
  }

  dimension_group: purchase {
    type: time
    timeframes: [month]
    sql: ${TABLE}.purchase_month ;;
  }

  dimension: months_since_signup {
    type: number
    sql: datediff('month', ${TABLE}.signup_month, ${TABLE}.purchase_month) ;;
  }

  dimension: monthly_purchases {
    type: number
    sql: ${TABLE}.monthly_purchases ;;
  }

  dimension: monthly_spend {
    type: number
    sql: ${TABLE}.monthly_spend ;;
  }

  measure: total_users {
    type: count_distinct
    sql: ${user_id} ;;
    drill_fields: [users.id, users.age, users.name, user_order_facts.lifetime_orders]
  }

  measure: total_active_users {
    type: count_distinct
    sql: ${user_id} ;;
    drill_fields: [users.id, users.age, users.name, user_order_facts.lifetime_orders]

    filters: {
      field: monthly_purchases
      value: ">0"
    }
  }

  measure: percent_of_cohort_active {
    type: number
    value_format_name: percent_1
    sql: 1.0 * ${total_active_users} / nullif(${total_users},0) ;;
    drill_fields: [user_id, monthly_purchases, total_amount_spent]
  }

  measure: total_amount_spent {
    type: sum
    value_format_name: usd
    sql: ${monthly_spend} ;;
    drill_fields: [detail*]
  }

  measure: spend_per_user {
    type: number
    value_format_name: usd
    sql: ${total_amount_spent} / nullif(${total_users},0) ;;
    drill_fields: [user_id, monthly_purchases, total_amount_spent]
  }

  measure: spend_per_active_user {
    type: number
    value_format_name: usd
    sql: ${total_amount_spent} / nullif(${total_active_users},0) ;;
    drill_fields: [user_id, total_amount_spent]
  }

  dimension: key {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.key ;;
  }

  set: detail {
    fields: [user_id, signup_month, monthly_purchases, monthly_spend]
  }
}

The resulting table would show this:

Our last step is to define this derived table view as an Explore and join our users table:

explore: user_monthly_activity {
  join: users
  sql_on: ${user_monthly_activity.user_id} = ${users.id}
  relationship: many_to_one
}

Now, when our users select activity_month, signup_month, and percent_of_cohort_still_active, they will see the percentage of user retention by monthly activity cohort, all from a single query. Then, our users can filter or pivot by user-acquisition source, drill in to specific users, and discover trends that may impact how their marketing team chooses to spend user-acquisition dollars.