Community

[Data Tool] - Cohort Analysis

About This Tool

Cohort Analysis Data Tool makes it easy to segment customers and identify patterns in behavior or retention. View revenue, transactions, web count and more, for a selected segment of users. This block builds on the concepts described in our Cohort Block and allows for further customization based on user-selected criteria.

First start with the basics - Cohort user behavior patterns (Order counts, revenue, or other metrics) based on an attribute of the user (Signup month, Age). Refer to our existing cohort block to get started.

Expected Output

Extend your base cohort with parameters to allow for full end user customizability for the cohort attribute and cohort behavior pattern.

Define Parameters

Parameters (surfaced as a filter-only field in the UI) allow for UI flexibility when selecting dimensions and measures. This can allow the user to define the dimension or measure to pull in to the report at run-time.

Lets create a parameter that acts as metric selector that we can use in our cohorts –

  parameter: measure_picker {
    type: string
    allowed_value: { value: "Cumulative Revenue" }
    allowed_value: { value: "Transactions" }
    allowed_value: { value: "User Count" }
    allowed_value: { value: "Active Users" }
  }

Define base measures

How is cumulative revenue calculated? Let’s say that cumulative revenue is the sum of sale_prices. A measure for just Cumulative Revenue would look like:

  measure: cumulative_revenue{
    type: sum
    value_format_name: usd
    sql: ${sale_price};;
  }

Attach Parameters to Metrics to allow for dynamic selection

In the cohorted measure, we want to apply the same logic. By defining it as a measure of type: number, we can dynamically change the aggregation applied to match our cohort selection simply by referencing the already defined sum or count measures.

  measure: cohort_values {
    type: number
    sql: CASE WHEN {% parameter measure_picker %} = 'Cumulative Revenue' THEN ${cumulative_revenue}
        WHEN {% parameter measure_picker %} = 'Transactions' THEN ${order_count}
        WHEN {% parameter measure_picker %} = 'User Count' THEN ${user_count}
        WHEN {% parameter measure_picker %} = 'Active Users' THEN ${active_user_count}
        ELSE 0
      END ;;
  }

You’ll notice that the underlying value of the measure (the column to run the calculation on) gets selected based on the selection of the parameter.

Let’s build a cohort!

3 Likes