Retention cohort reporting for DTC: the Monday dashboard

A DTC operator opened a Monday-morning call and asked whether the brand's August cohort had stalled. The marketing team had been saying yes for three weeks. The CFO had been saying no for two. Both were reading the same Klaviyo dashboard, and neither answer was actually wrong as a reading of what that dashboard reported. The dashboard was reporting modeled flow revenue, not cohort retention, so it could not answer the question either side was asking. The fix was a four-panel report sitting on top of joined Shopify and Klaviyo data in the warehouse, refreshed nightly, segmented by acquisition channel and first product. By the end of this walkthrough you will have shipped that report and replaced the Monday-morning argument with a chart everyone can read the same way.

What "retention cohort reporting DTC" actually has to answer

The Monday dashboard exists to answer three questions, and exactly three. Is the most recent cohort tracking ahead, on, or behind the trailing benchmark at month one and month three. Which acquisition channels are producing cohorts whose retention curves hold up past month six. Which first-product purchases predict a healthier curve than the brand-level average. Anything that does not feed one of those three questions is dashboard noise.

A single LTV number cannot answer any of them. LTV is an average over a population that has not finished living. The average hides the spread between cohorts, which is where the operator's decisions actually live. A brand can have a healthy 12-month LTV and a collapsing month-three retention curve, and the LTV number will not flinch for another three quarters. By the time it does, the team has been spending against a number that was never true.

The shape of the dashboard before any SQL gets written is four panels stacked vertically:

• A cohort retention chart with a row per cohort and a curve at horizons one, three, six, and twelve.
• A channel cut showing the same curves grouped by acquisition channel.
• A first-product cut showing curves grouped by the SKU that opened the relationship.
• An alert panel that flags any cohort more than two percentage points below the rolling 12-month benchmark at month three, because that is the earliest horizon at which retention failure is statistically defensible.

Prerequisites: Shopify orders and Klaviyo events in the warehouse

You need four things in place before the rest of this walkthrough lands. A warehouse with Shopify paid orders modeled into a clean staging schema. Klaviyo events landed in the same warehouse, joined to orders via hashed email plus order_id inside a forty-eight-hour window. A customer_id to acquisition_channel map, hardened off the customer's first session UTM source and medium. First-product attribution computed from Shopify line items on the customer's first paid order.

If you have BigQuery, the BigQuery setup for Shopify data covers the staging schema. The Klaviyo-to-orders join is in the warehouse attribution piece. The base SQL pattern this builds on lives in the cohort retention SQL walkthrough. If you are running a smaller stack and want the budget version, the same pattern fits in Snowflake or Postgres with minor syntax changes; the math is identical.

The two pieces most teams skip are the channel map and the first-product attribution. Both of them are unglamorous and both of them are the reason the dashboard ends up actually predicting something. Without a stable first_channel per customer, you are filtering cohorts by whatever channel showed up most recently in your CRM, which moves around. Without first-product attribution, your cohorts collapse into one column and you cannot see that the soft-launch SKU produces a 20-point-better month-six retention curve than the gateway SKU.

Step 1: build the cohort base table

Every cohort report in this pattern starts from a single dimension table. Call it dim_customer_cohort. One row per customer, with the customer's first paid order date, their cohort month, the acquisition channel attached to that first order, and the first product they bought. Refresh it daily off the staging Shopify and session tables.

CREATE OR REPLACE TABLE analytics.dim_customer_cohort AS
WITH first_orders AS (
  SELECT
    customer_id,
    MIN(created_at) AS first_order_at,
    DATE_TRUNC(DATE(MIN(created_at)), MONTH) AS cohort_month
  FROM {{ ref('stg_shopify__orders') }}
  WHERE financial_status = 'paid'
    AND customer_id IS NOT NULL
  GROUP BY customer_id
),
first_product AS (
  SELECT DISTINCT
    o.customer_id,
    FIRST_VALUE(li.product_id) OVER (
      PARTITION BY o.customer_id
      ORDER BY o.created_at, li.line_item_id
    ) AS first_product_id
  FROM {{ ref('stg_shopify__orders') }} o
  JOIN {{ ref('stg_shopify__line_items') }} li USING (order_id)
  WHERE o.financial_status = 'paid'
),
first_channel AS (
  SELECT
    customer_id,
    FIRST_VALUE(channel) OVER (
      PARTITION BY customer_id
      ORDER BY session_started_at
    ) AS first_channel
  FROM {{ ref('dim_customer_session_first') }}
)
SELECT
  fo.customer_id,
  fo.first_order_at,
  fo.cohort_month,
  fp.first_product_id,
  fc.first_channel
FROM first_orders fo
LEFT JOIN first_product fp USING (customer_id)
LEFT JOIN first_channel fc USING (customer_id)

Two choices in here are not obvious. first_paid_order is the cohort anchor, not first session, because session anchoring leaks freebies and abandoned-cart browsers into your cohorts and quietly tanks every retention number you compute. first_channel reads off whatever your warehouse calls the first identified session for that customer, which is its own modeling problem with edge cases around iOS Safari ITP and consent gaps; treat it as a snapshot, not gospel.

The table itself should be small enough that a daily full rebuild is fine. A brand at $5M annually has under a hundred thousand customers, which is a few seconds of BigQuery time. Snapshot semantics matter more than rebuild cost. If a customer's first session attribution gets corrected upstream, you want yesterday's dashboard to keep yesterday's snapshot, not silently change underneath the operator.

Step 2: compute the monthly retention matrix

The matrix is the report's core. One row per (cohort_month, months_since_first) cell, value equal to the percent of that cohort with at least one paid order in that horizon. Build it as a view on top of dim_customer_cohort joined to all paid orders.

CREATE OR REPLACE VIEW analytics.fct_cohort_retention AS
WITH cohort_orders AS (
  SELECT
    c.customer_id,
    c.cohort_month,
    c.first_channel,
    c.first_product_id,
    DATE_DIFF(DATE(o.created_at), DATE(c.first_order_at), MONTH)
      AS months_since_first
  FROM analytics.dim_customer_cohort c
  JOIN {{ ref('stg_shopify__orders') }} o
    ON c.customer_id = o.customer_id
    AND o.financial_status = 'paid'
),
cohort_size AS (
  SELECT cohort_month, COUNT(DISTINCT customer_id) AS n
  FROM analytics.dim_customer_cohort
  GROUP BY cohort_month
)
SELECT
  co.cohort_month,
  co.months_since_first,
  COUNT(DISTINCT co.customer_id) AS returning_customers,
  cs.n AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT co.customer_id) / cs.n, 2) AS pct_returned
FROM cohort_orders co
JOIN cohort_size cs USING (cohort_month)
WHERE co.months_since_first BETWEEN 1 AND 12
GROUP BY co.cohort_month, co.months_since_first, cs.n

Monthly resolution is the right default. Weekly cohorts go noisy below two hundred customers per cohort, which is most cohorts at most DTC brands. Quarterly is too coarse to see a problem soon enough to do anything about it. Monthly buckets give an ad team time to react inside the same quarter the problem started.

The matrix has a partial-maturity hazard. Your most recent cohort has a month-twelve cell of zero, because it is two months old. The dashboard has to filter that down or it will look like every cohort is collapsing to zero. The convention I use is to gray out any horizon cell where the cohort has not yet reached that horizon plus a one-month buffer, and to compute the trailing benchmark on the last twelve mature cohorts only. Anything that does not pass that filter shows as "n/a" in the panel.

Step 3: segment by acquisition channel and first product

The segmented matrix is the same SQL pattern as Step 2 with two extra GROUP BY columns. Build a wide mart that holds the matrix at three levels: brand-level, by channel, and by first product. Each level is a separate row per (cohort_month, months_since_first) cell. The dashboard filters on the level via a dropdown.

The Meta-prospecting plus soft-launch-SKU cut is, in my experience, the most predictive segment in DTC retention reporting. It bears almost no resemblance to the brand-level curve. At the operator I worked with in Q1 2026, the brand-level twelve-month repurchase rate was around 41 percent, the Meta-prospecting curve was around 32 percent, and the same-channel-but-soft-launch-SKU cut was around 49 percent. That last cohort told the operator their best retention was being produced by a SKU they were not advertising, which changed the next quarter's media plan in one meeting.

The segmentation has to live in SQL, not in the dashboarding tool. Two reasons. The first is that BI tools default to filtering rows, not redefining cohorts; if you filter the matrix to "channel = Meta," you are looking at customers who are in the brand-level cohort whose SECOND-or-later order came from a Meta touchpoint, which is not a cohort definition. The second reason is reconciliation. A SQL-defined segment is auditable; a dashboard filter is not, and the operator who has to defend the number in a board review needs the SQL.

The Klaviyo side of this matters too. The Klaviyo lifecycle playbook for DTC retention is the cluster hub for what your flows should be doing on top of these cohorts; the dashboard tells you which cohorts are weakening, the lifecycle program is what you do about it. The cohort LTV pattern library extends the same matrix into dollars when you are ready to layer the revenue side.

Step 4: wire the Monday-morning dashboard

Four panels, in this order:

• Retention curve panel at the top, where the operator picks a cohort and sees the curve laid against the trailing benchmark.
• Channel cut panel showing three to five channel curves overlaid on a single chart, with the brand-level benchmark dashed behind them.
• First-product cut panel showing the same chart for the top three or four first-product SKUs.
• Alert panel listing any cohort whose month-three retention is more than two percentage points below the trailing benchmark.

Refresh once a day, end of day Pacific Time. Email teams do not need real-time retention; the underlying behavior is moving in monthly cycles. Daily refresh is plenty.

The alert panel deserves its own sentence. Most teams build the chart and forget the alert, which means the chart only matters when someone remembers to look at it. The alert panel reads from fct_cohort_retention filtered to the most recent two mature cohorts and any horizon where pct_returned < benchmark - 2. Wire it to a daily Slack message at 8 AM Pacific Time so the operator opens the day with the question, not with the dashboard.

The shape of the report I ship was proven out on a Q1 2026 analytics engine rebuild, where this exact pattern fed a half-dozen dashboards on top of joined Shopify, web, ads, and email data. The productized version of this work, including the warehouse review and the four-panel report scaffolded for your stack, is the DTC stack audit.

Common mistakes that break retention cohort reporting

Five failure modes show up at almost every brand I audit on this dashboard.

The first is cohort assignment by first session instead of first paid order. First-session anchoring inflates the denominator with browsers who never bought, which collapses month-one retention by a factor of three to five and makes every curve look broken. Anchor on first paid order or do not bother.

The second is mixing welcome-series opt-ins into customer cohorts. An email opt-in is not a customer. It is a person who has signaled interest and is now in the welcome-series flow described in the welcome series architecture for DTC. Email subscribers and customers belong on different dashboards because the questions you ask about them are different.

The third is reporting the cohort revenue using Klaviyo's modeled attributed_revenue. That number is Klaviyo's opinion of what credit each touch deserves, not the dollar value of the order. Use Shopify's total_price or settled-revenue equivalent for cohort dollars, and keep Klaviyo's attributed number on its own panel for inside-Klaviyo decisions.

The fourth is reading the most-recent cohort like it is a mature one. The two most recent months will always have suppressed month-six and month-twelve numbers because those horizons have not happened yet. Gray out the cells, do not let the operator misread them.

The fifth is counting refunded and cancelled orders. The retention matrix needs a hard filter on settled status. If your warehouse does not yet have a clean is_settled flag, build it before you trust the dashboard.

What to try next

Three extensions to this report are worth shipping after the base version is live.

Layer flow revenue onto the retention matrix. Once the Klaviyo-to-orders join from the warehouse attribution piece is in place, the same matrix can be re-cut by the flow that drove the repeat purchase. That is when retention reporting starts answering the question of which lifecycle flow is producing real returning customers, not just inside-Klaviyo flow revenue.

Add a cohort LTV layer. The retention curve tells you what percent of a cohort came back; the LTV layer tells you what those returners are worth in settled dollars. The pattern is in the cohort LTV pattern library, and it slots in cleanly on top of the matrix without rewriting any of the SQL above.

Move from views to a daily snapshot mart. The view-based version refreshes implicitly every time someone opens the dashboard, which is fine for the first ninety days. Past that, snapshot the matrix into a snap_cohort_retention table keyed by as_of_date and serve the dashboard from snapshots only. That is what makes a number from three months ago still readable when an executive asks where the curve was the last time the team made a media decision.

FAQ

Sources and specifics

• The pattern was shipped at a mid-market DTC operator in Q1 2026 as part of a warehouse-first analytics rebuild documented in the analytics engine case study.
• The cohort dashboard refreshes once daily off Shopify orders and Klaviyo events joined in BigQuery via hashed email plus order_id inside a forty-eight-hour window.
• Cohorts are anchored on first paid order, bucketed monthly, with retention horizons measured at month one, three, six, and twelve.
• Acquisition channel and first-product segmentation live in SQL on top of a dim_customer_cohort dimension table refreshed daily.
• The alert threshold of two percentage points below the trailing 12-month benchmark at month three is operator-defined; trailing-benchmark math excludes the most recent three partially mature cohorts.
• Settled revenue from Shopify, not Klaviyo's modeled attributed_revenue, is the cohort dollar figure on the report.