Cohort analysis using a thin spine

In the Thin dimensional spine for multi-fact event analysis guide, you learned what a thin dimensional spine is, how it works, and how to implement your own using dbt and Omni. In this guide, we’ll build on the knowledge in that guide to walk you through several approaches to cohort analysis.

Requirements

To follow along with this guide, you’ll need:

To have read the Thin dimensional spine for multi-fact event analysis guide. This guide describes the basics of thin dimensional spines and how to build and materialize your own.
Familiarity with modeling in Omni

Attribute-based cohorts

To build this type of analysis:

Maintain an attribute table with per-user first/last timestamps, e.g., attr__users_event_bounds with first_signup_ts, first_purchase_ts.
Group and filter users by these milestones across event streams

Event-to-event cohorts

This type of analysis looks at the timing between events — for example, signup-to-first-login latency, or engagement patterns following a first purchase.

Dynamic ‘time since selected event’ cohorting

Using the spine, you can model a dynamic “since basis” and “grain” selector to compute buckets of time since a specific event:

"Snippet: dim__user_event_spine_thin.view

dimensions:
  selected_basis_ts:
    type: timestamp
    sql: |
      case {{filters.dim__user_event_spine_thin.cohort_basis.value}}
        when 'first_ad' then ${attr__users_event_bounds.first_ad_ts}
        when 'first_signup' then ${attr__users_event_bounds.first_signup_ts}
        else ${attr__users_event_bounds.first_ad_ts}
      end

  time_since_selected_event:
    type: number
    label: Time Since Selected Event
    sql: |
      datediff(
        case {{filters.dim__user_event_spine_thin.cohort_grain.value}}
          when 'day' then 'day'
          else 'day'
        end,
        ${selected_basis_date},
        ${event_date_day}
      )