Vistelio | Cohort and Retention Analysis in LookML: Building the Retention Triangle in the Semantic Layer

Cohort retention is the question every SaaS, subscription, e-commerce, and product analytics team eventually asks Looker: of the users who first showed up in March, how many were still active three months later? It is also the question that most often escapes the semantic layer and ends up as a one-off SQL runner query or a spreadsheet, because the shape of a retention triangle does not fall out of a normal star schema.

It should. A retention analysis is just two facts (a cohort anchor per entity, and activity events per entity) plus a period offset. This tutorial builds that in LookML so business users can pivot retention by plan, channel, or region without a developer in the loop.

1. The three pieces you need

A cohort spine — one row per entity with the date of its first qualifying event (signup, first order, activation).
An activity fact — one row per entity per event, with a date.
A period offset — the whole-number difference between the activity period and the cohort period.

Everything else is presentation.

2. Build the cohort spine as a PDT

Do not compute MIN(created_at) inline in every measure. Persist it once so it is stable, cheap, and joinable.

view: user_cohorts {
  derived_table: {
    datagroup_trigger: daily_etl
    distribution_style: all   # Redshift; use partition_by/cluster_by on BigQuery
    sql:
      SELECT
        o.user_id,
        MIN(o.created_at)                        AS first_order_at,
        DATE_TRUNC(MIN(o.created_at), MONTH)     AS cohort_month,
        MIN(u.acquisition_channel)               AS acquisition_channel
      FROM ${order_items.SQL_TABLE_NAME} o
      JOIN ${users.SQL_TABLE_NAME} u ON u.id = o.user_id
      WHERE o.status NOT IN ('cancelled','returned')
      GROUP BY 1 ;;
  }

  dimension: user_id { primary_key: yes  type: number  hidden: yes }

  dimension_group: cohort {
    type: time
    timeframes: [month, quarter, year]
    datatype: date
    sql: ${TABLE}.cohort_month ;;
  }

  dimension: acquisition_channel { type: string }

  measure: cohort_size {
    type: count_distinct
    sql: ${user_id} ;;
    drill_fields: [user_id, cohort_month, acquisition_channel]
  }
}

Two things matter here. The WHERE clause defines what "joining the cohort" means — get the business to agree on it before you write it, because changing it later silently moves every number on the dashboard. And cohort_size must be a distinct count on the spine, never on the activity fact, or fan-out will inflate your denominator.

3. Join the spine into the activity explore

explore: order_items {
  join: users {
    sql_on: ${order_items.user_id} = ${users.id} ;;
    relationship: many_to_one
  }
  join: user_cohorts {
    sql_on: ${order_items.user_id} = ${user_cohorts.user_id} ;;
    relationship: many_to_one
  }
}

many_to_one is correct: many activity rows, one cohort row per user. If you get this wrong, symmetric aggregates will quietly rescue your sums but not your distinct counts.

4. The period offset dimension

This is the axis of the retention triangle — months since the cohort started.

# in the activity view (order_items)
dimension: months_since_cohort {
  type: number
  sql: DATE_DIFF(
         DATE_TRUNC(${created_raw}, MONTH),
         ${user_cohorts.cohort_month},
         MONTH
       ) ;;
  description: "0 = the user's first month, 1 = the following month, and so on."
}

dimension: months_since_cohort_tier {
  type: tier
  tiers: [0, 1, 3, 6, 12]
  style: integer
  sql: ${months_since_cohort} ;;
}

Use the warehouse's native date-diff for whole calendar periods (DATE_DIFF(..., MONTH) on BigQuery, DATEDIFF(month, ...) on Snowflake/Redshift, TIMESTAMPDIFF on MySQL). Do not divide day counts by 30 — the drift makes month 12 land in month 11 for roughly a third of your users.

5. Retained users and retention rate

measure: retained_users {
  type: count_distinct
  sql: ${user_id} ;;
  drill_fields: [user_id, users.email, created_date]
}

measure: retention_rate {
  type: number
  sql: 1.0 * ${retained_users} / NULLIF(${user_cohorts.cohort_size}, 0) ;;
  value_format_name: percent_1
  description: "Share of the cohort active in this period."
}

NULLIF is not optional. Without it, any filter combination that empties the denominator returns a division error and the whole tile fails, not just one cell.

Pivot months_since_cohort across the columns, put cohort_month down the rows, and retention_rate in the cells: that is the retention triangle, built entirely in the semantic layer.

6. The incomplete-cohort trap

The bottom-right of every retention triangle is a lie. A cohort that started last month cannot have a month-3 value, but if the underlying data is sparse the cell renders as 0% rather than blank, and executives read the chart as a cliff.

Filter those cells out in the model rather than trusting each analyst to remember:

dimension: period_is_complete {
  type: yesno
  sql: DATE_ADD(${user_cohorts.cohort_month},
        INTERVAL ${months_since_cohort} MONTH)
       <= DATE_TRUNC(CURRENT_DATE(), MONTH) - INTERVAL 1 MONTH ;;
}

Then add it as a guardrail on a dedicated retention explore:

explore: retention {
  from: order_items
  sql_always_where: ${period_is_complete} ;;
}

7. Rolling (unbounded) retention

Classic retention asks "active in month N". Rolling retention asks "active in month N or later" — the right definition for low-frequency products where a customer buying twice a year is not churned.

Model it as a separate measure so users can compare the two side by side:

measure: rolling_retained_users {
  type: count_distinct
  sql: CASE WHEN ${user_cohorts.last_activity_date} >=
              DATE_ADD(${user_cohorts.cohort_month},
                       INTERVAL ${months_since_cohort} MONTH)
            THEN ${user_id} END ;;
}

Add MAX(created_at) AS last_activity_date to the cohort PDT to support it. Label both measures unambiguously — "Retention Rate (Classic)" and "Retention Rate (Rolling)" — because the two numbers will differ and someone will screenshot the wrong one.

8. Making it performant

Aggregate the triangle. Retention grids are read constantly and change once a day. Add an aggregate table keyed on cohort_month, months_since_cohort, and your one or two highest-value pivot dimensions, and let aggregate awareness serve the dashboard while ad-hoc slices fall through to the detail table.
Trigger on the ETL, not on a clock. Give the cohort PDT and the aggregate table the same datagroup_trigger as the pipeline that loads the fact, so the denominator and numerator are never built from different snapshots.
Cap the horizon. Nobody reads month 47. A sql_always_where of ${months_since_cohort} <= 24 on the retention explore cuts scan volume dramatically on long-lived datasets.

9. Testing it

Retention math fails silently, so pin it with LookML data tests in CI:

test: retention_month_zero_is_100_percent {
  explore_source: retention {
    column: retention_rate {}
    filters: [retention.months_since_cohort: "0"]
  }
  assert: month_zero_full {
    expression: ${retention.retention_rate} = 1.0 ;;
  }
}

Month 0 must be 100% by construction — every member of a cohort was active in the period that defined the cohort. If that test fails, your cohort spine and your activity fact disagree about what counts as an event, and every other cell in the grid is wrong too.

Where teams get this wrong

The recurring failure is not the SQL; it is definitional drift. Marketing counts a cohort from signup, finance counts from first paid invoice, and product counts from activation — and all three build their own derived table. The value of doing this in LookML is that the cohort spine becomes one governed object with one owner, and the disagreements surface as a conversation instead of as three dashboards that do not match.

If your Looker instance already has three competing retention dashboards, our team can consolidate them into a single modeled cohort layer — and leave your analysts a pattern they can extend.