Activation & Retention

30/60/90-Day Cohort
Retention Dashboard

Week 2 / 20·Jun 28, 2026·2 channels at <20% M3 retention — $40K/mo CAC reallocated

dbt (Snowflake)Snowflake SQLLookerStripe (Fivetran)

Representative scenario based on patterns observed across multiple Series A/B SaaS companies. Company details are composited and anonymized.

The Problem

Month-over-month retention had read 58% for three consecutive quarters. The growth team was increasing ad spend. The CEO was satisfied. Nobody could explain why net revenue retention wasn't improving.

Context: Series B SaaS, ~$8M ARR, 45-person team. Four-person marketing team. No dedicated data analyst. Retention was reported as a single company-wide number — no channel split, no cohort triangle, no before/after.

The Approach

Inventory the data sources

Three source tables: billing events from Stripe (via Fivetran → Snowflake), UTM parameters from the marketing attribution dbt model, and product events from Segment. The join key between billing and product had two different ID schemas — required building stg_customer_identity.sql to map them before any cohort analysis could run.

Build the cohort assignment model in dbt

Each paying customer assigned to their first billing month (cohort_month) and acquisition channel (utm_source on first payment). Built fct_cohort_retention as the core model — one row per customer × period, with retained_customers count and retention_pct. This became the single source of truth for all retention reporting.

Run the channel × cohort split

Pulled M1/M2/M3 retention by acquisition channel. Four channels in the model. Results: organic_search 81% M3, product_viral 76% M3, paid_social 19% M3, display_ads 17% M3. The company-wide aggregate had been showing 58% — a number that had felt stable for three quarters.

Build the retention heatmap in Looker

Two views: a cohort triangle (rows = cohort month, columns = M0–M5, cells = retention_pct colored by value) and a bar chart by channel showing M1/M2/M3 side by side. Marketing could immediately see that paid_social users always churned in M2 — consistent across every cohort month tested.

Present and reallocate

Shared the channel comparison in the growth team weekly. Decision made in that meeting: cut display_ads entirely ($15K/mo), reduce paid_social by 60% ($25K/mo), increase SEO content and product referral. Total reallocation: $40K/month. No product changes. No new experiments.

The dbt Model

The core model — one row per customer × period × channel. Every downstream view reads from this.

-- fct_cohort_retention.sql (dbt model)
with customer_cohorts as (
  select
    c.customer_id,
    c.acquisition_channel,
    date_trunc('month', min(p.paid_at))   as cohort_month
  from {{ ref('dim_customers') }}  c
  join {{ ref('fct_payments') }}   p using (customer_id)
  where p.status = 'succeeded'
  group by 1, 2
),
payment_months as (
  select customer_id,
         date_trunc('month', paid_at)     as payment_month
  from {{ ref('fct_payments') }}
  where status = 'succeeded'
  group by 1, 2
),
cohort_sizes as (
  select cohort_month, acquisition_channel,
         count(distinct customer_id)      as cohort_size
  from customer_cohorts
  group by 1, 2
)
select
  c.cohort_month,
  c.acquisition_channel,
  s.cohort_size,
  datediff('month', c.cohort_month,
           p.payment_month)               as period_number,
  count(distinct c.customer_id)           as retained_customers,
  round(100.0 * count(distinct c.customer_id)
        / nullif(s.cohort_size, 0), 1)    as retention_pct
from customer_cohorts  c
join payment_months    p using (customer_id)
join cohort_sizes      s using (cohort_month, acquisition_channel)
where datediff('month', c.cohort_month, p.payment_month) between 0 and 5
group by c.cohort_month, c.acquisition_channel, s.cohort_size, period_number
order by 1, 2, 4

The channel comparison query on top of fct_cohort_retention:

-- M3 retention by channel — the view that changed the budget decision
select
  acquisition_channel,
  round(avg(case when period_number = 1 then retention_pct end), 1) as m1_avg_pct,
  round(avg(case when period_number = 2 then retention_pct end), 1) as m2_avg_pct,
  round(avg(case when period_number = 3 then retention_pct end), 1) as m3_avg_pct
from {{ ref('fct_cohort_retention') }}
group by 1
order by m3_avg_pct desc nulls last

The Result

Retention visibility

Aggregate 58%

By channel × cohort

Channels identified

None flagged

2 at <20% M3

CAC reallocated

$40K/month

The growth team reallocated $40K/month in a single meeting — no product changes, no new experiments. Organic search and product viral channels stayed untouched. Paid social reduced by 60%, display ads cut entirely. The following quarter, company-wide M3 retention moved from 58% to 71% purely through the channel mix change.

The Non-Obvious Takeaway

The product wasn't the retention problem — the acquisition mix was. Paid social users were churning in M2 with near-perfect consistency across every cohort. The product team had been building re-engagement features for three quarters trying to fix a number that wasn't a product problem. You can't see channel-level retention curves in a single aggregate number, no matter how long you stare at it.