Subscription e-commerce data model: tables, keys and one definition per metric

What the numbers mean, and what they are not

Four words the model has to keep apart:

• Cash collected is what the payment processor settled. It includes the VAT you owe and arrives with the processor’s fees deducted. A prepaid plan collects months of cash on day one.
• Recognised revenue is what finance reports, net of VAT, from its ledger. Under IFRS 15 a box is revenue when control passes to the customer, under the delivery terms, and a membership is spread over the period it covers. Cash for boxes not yet shipped is deferred revenue, a liability.
• MRR is the monthly value of the subscriptions active on a date; ARR is MRR × 12. Both are operating metrics, not revenue: they are not in the accounts, and finance owns the revenue figure they are compared with.
• Order value is what a checkout says, tax and shipping included. Not revenue either.

The model on one page

Twelve tables. Start every table’s description with its grain, “one row per payment attempt”, and make the grain its primary key:

subscription_status and subscription_month are built from the others; nothing is typed into them. Order lines, products, sessions and ad spend come from an ordinary e-commerce model and are not repeated here.

A customer can hold several subscriptions, and each subscription produces many shipments. Subscriber counts count customers, MRR sums contracts, and shipments per subscriber divides one by the other. Write the unit into each definition.

Which system each table comes from

One customer, three ids

Each of those systems gives the same person its own customer id. customer_map keys everyone on the store’s customer id and stores the other two beside it. Match on the ids each system keeps about the others before anything else: Recharge’s API, for example, stores the store’s customer id on its customer (external_customer_id.ecommerce), the store’s order id on each charge (external_order_id.ecommerce) and the processor’s transaction id (external_transaction_id.payment_processor) (Recharge API). Fall back to a lower-cased email only where no id matches, and count those rows so the share is a number you can show.

Contracts and their state changes

A subscription is a contract that moves between states, and the history of those moves is the data. If you keep only the current status, every past month’s churn and MRR changes whenever a subscriber does something today.

One table for the contract, one for what happened to it

subscription holds what does not change: who subscribed, on which plan, when it started. It can also carry the current status for convenience.

subscription_event holds every change, one row each:

• what happened: created, paused, resumed, cancelled, reactivated, plan changed, quantity changed;
• when it takes effect (effective_on) and when it was recorded (recorded_at), which differ more often than you expect. A subscriber cancels on 10 February, the box they have already paid for still ships, and the contract ends on 3 March;
• the new value, on events that change it: price, quantity or cadence, as a monthly value;
• for a cancellation, who ended it: voluntary (the subscriber chose to) or involuntary (payment retries ran out), and the reason given.

Key it on the platform’s own event id, so a webhook that fires twice does not produce a second cancellation.

Status as a table with validity dates

From the events, build subscription_status: one row per contract per period in one state, with valid_from and valid_to. A contract’s state on 28 February is the row whose period contains 28 February. It is a type 2 slowly changing dimension.

SELECT subscription_id,
  CASE event_type
    WHEN 'paused' THEN 'paused'
    WHEN 'cancelled' THEN 'cancelled'
    ELSE 'active' END AS status,
  LAST_VALUE(new_mrr IGNORE NULLS) OVER (
    PARTITION BY subscription_id
    ORDER BY effective_on, recorded_at
  ) AS contract_mrr,
  effective_on AS valid_from,
  LEAD(effective_on) OVER (
    PARTITION BY subscription_id
    ORDER BY effective_on, recorded_at
  ) AS valid_to
FROM subscription_event
WHERE changes_state
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY subscription_id,
    effective_on
  ORDER BY recorded_at DESC) = 1

Only events that change the status or the value go in (changes_state is that flag); a skip or a charge attempt does not open a period. When two land on the same day, the one recorded last wins, so subscription, valid_from stays unique.

The status is derived by replaying events in effective order, not stored on each event. A stored “status after” on each event lets a late, backdated cancellation be reopened by the next event’s stored value; replaying from the event type does not.

Events arrive late. A cancellation recorded on 4 March can take effect on 28 February. Decide once whether closed months are restated (February changes, and the report says so) or frozen (February keeps its reported figure, stored at month close, and the late event counts in March).

A cancellation scheduled for a future date and reversed before that date is dropped together with its reversal, so it never reaches churn. A gift subscription has a payer and a recipient: key the contract to the recipient, who receives the boxes, and keep the payer on the charge.

Pauses and skips are not cancellations

• A pause is a state. The contract moves to paused for a period: no charges, no shipments, and a date it resumes or a rule that ends it. It is a row in subscription_status.
• A skip is an event on one cycle. The subscriber stays active, one delivery is not charged or shipped, and the next cycle proceeds. It is a row in subscription_event (or a skipped flag on the cycle), and it never opens a new status period.

The rules to write down, because the model cannot decide them:

• A paused subscriber is not churned, and is not new when they resume. Count them separately.
• A paused contract contributes zero to MRR here, since nothing is billed. Full value is also defensible; mixing the two is not.
• A pause that never ends needs a limit: the platform’s auto-cancel date, or a cut-off you set.
• A skip does not change MRR. It lowers shipments and revenue for that month.

Dunning: when a charge fails

A failed charge is not yet churn. The subscriber is in dunning: the platform retries the card on a schedule and emails them. In this model the contract stays active, and in MRR, until the last retry fails; then a cancellation event takes effect with cancel_type = involuntary. Store retries as charges, so the churn retries saved can be counted. Whether MRR in dunning counts is a decision for whoever owns the number.

When a prepaid term ends

A prepaid plan not renewed after its last box was cancelled by nobody, but it still ends. Count it as voluntary churn on the day the next charge would have been due, the end of the last prepaid period, not on the day the last box ships. Until then it is active and in MRR; where renewal is automatic, the failed renewal ends it.

Charges, orders and shipments are three tables

In a one-time shop a payment, an order and a shipment happen together. In a subscription business they come apart.

• charge, one row per payment attempt: amount, currency, status (succeeded, failed, refunded), the retry number, and the service period it pays for. A declined charge never becomes an order, so without this table the failed-payment half of churn is invisible.
• order, one row per order: one-time, a subscription’s first order, or a recurring order, with the subscription and cycle number on recurring ones. The subscription discount sits here.
• shipment, one row per box shipped, with the ship date and the order it fulfils. This is the event that usually makes a box recognised revenue, and the count behind shipments per subscriber.

Prepaid plans show why the three cannot share a table: one charge, one order, three shipments, one a month. Cash arrives in month one, revenue arrives a third at a time, and MRR is a third of the charge from the first month. A membership has a charge and no shipment, and its revenue is spread over the days it covers.

Gift cards are not revenue when sold. The cash is a liability until the card is redeemed, the share never redeemed (breakage) follows finance’s policy, and a subscription paid with a gift card brings in no new cash that month.

Plans, prices and the monthly value

plan describes the offer: the cadence (every 2 weeks, every month, every 2 months), the discount against the one-time price, whether it is prepaid and for how many cycles, and whether skips are allowed. The price a subscriber pays belongs to their contract, not to the plan: existing subscribers often keep their price after a rise. Store a price change as a subscription_event, and the list price history in plan_price.

MRR needs every cadence in one unit. The contract’s monthly value is its price per cycle, after discount and before tax and shipping, times the number of cycles in an average month:

Some models use a 30-day month instead (a fortnightly box is then 30 ÷ 14 = 2.14 cycles, €25.71). Either works if it is the only one.

Two rules to state with it. A discount that ends (an introductory coupon expiring) is a price change, not expansion, unless your definition says otherwise. A mid-cycle change that the platform prorates changes MRR from its effective date by the new monthly value; the prorated amount is a charge, not a movement.

In more than one currency, convert MRR at finance’s month-end rate.

Dates: which one each number reads

Give every metric one date, and join every date to one calendar table in one time zone, so “February” means the same days in every report.

One definition per metric

Defensible defaults. The point is one of each, written down, used by every report and assistant.

Three notes on choices that move numbers most:

• Churn’s denominator. Whether paused subscribers are in it changes the rate (see the worked example).
• LTV as a formula. “Revenue per subscriber ÷ churn rate” assumes constant churn, which early cohorts rarely have. Report observed value by cohort; a projection is a separate, labelled number. LTV to CAC, the ratio investors ask for, divides it by what it cost to acquire a customer, which comes from ad spend and lives in your marketing model, not here.
• Pause and resume are movements of their own. Filed as churn and new, they still add up, but overstate both. Reactivated means the same customer starts a contract within the reactivation window after their last cancellation; after it, they are new. Recharge sends a product swap as its own event (subscription/swapped in its webhook list); a platform that records a swap as a cancellation plus a new contract needs the two merged into one change, matched on customer within a few minutes. The SaaS data model shows how the movement table is built.

A worked example you can run

Five subscriptions over three months, small and clean on purpose, typed in as literal rows so the query runs in any BigQuery project as written. Prices exclude VAT and the example ignores processor fees. One customer per subscription, so customers and contracts count the same here:

• S1, monthly box at €30, from 5 January; skips the February delivery.
• S2, a €12 box every 2 weeks (€26 a month), from 10 January; paused on 14 February, resumed on 20 March.
• S3, monthly at €30, from 20 January; the February charge fails, the retries fail, involuntary cancellation effective 1 March.
• S4, prepaid 3 boxes for €81 (€27 a month), from 1 February.
• S5, monthly at €30, from 3 January; cancels on 10 February, effective 3 March, after the box already paid for.

WITH state_event AS (
  SELECT * FROM UNNEST([
    STRUCT('S1' AS sub_id,
      DATE '2026-01-05' AS effective_on,
      TIMESTAMP '2026-01-05 08:00:00'
        AS recorded_at,
      'created' AS event_type,
      30.00 AS new_mrr),
    ('S2', DATE '2026-01-10',
      TIMESTAMP '2026-01-10 08:00:00',
      'created', 26.00),
    ('S2', DATE '2026-02-14',
      TIMESTAMP '2026-02-14 08:00:00',
      'paused', NULL),
    ('S2', DATE '2026-03-20',
      TIMESTAMP '2026-03-20 08:00:00',
      'resumed', NULL),
    ('S3', DATE '2026-01-20',
      TIMESTAMP '2026-01-20 08:00:00',
      'created', 30.00),
    ('S3', DATE '2026-03-01',
      TIMESTAMP '2026-03-01 08:00:00',
      'cancelled', NULL),
    ('S4', DATE '2026-02-01',
      TIMESTAMP '2026-02-01 08:00:00',
      'created', 27.00),
    ('S5', DATE '2026-01-03',
      TIMESTAMP '2026-01-03 08:00:00',
      'created', 30.00),
    ('S5', DATE '2026-03-03',
      TIMESTAMP '2026-02-10 08:00:00',
      'cancelled', NULL)
  ])
),
status AS (
  SELECT sub_id,
    CASE event_type
      WHEN 'paused' THEN 'paused'
      WHEN 'cancelled' THEN 'cancelled'
      ELSE 'active' END AS status,
    LAST_VALUE(new_mrr IGNORE NULLS)
      OVER w AS contract_mrr,
    effective_on AS valid_from,
    LEAD(effective_on) OVER w
      AS valid_to
  FROM state_event
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY sub_id, effective_on
    ORDER BY recorded_at DESC) = 1
  WINDOW w AS (
    PARTITION BY sub_id
    ORDER BY effective_on, recorded_at)
),
month_end AS (
  SELECT LAST_DAY(m) AS d
  FROM UNNEST(GENERATE_DATE_ARRAY(
    '2026-01-01', '2026-03-01',
    INTERVAL 1 MONTH)) AS m
),
sub_month AS (
  SELECT e.d AS month_end, s.sub_id,
    s.status,
    IF(s.status = 'active',
      s.contract_mrr, 0) AS mrr
  FROM month_end e
  JOIN status s
    ON s.valid_from <= e.d
    AND (s.valid_to IS NULL
      OR s.valid_to > e.d)
)
SELECT month_end,
  SUM(mrr) AS mrr,
  COUNTIF(status = 'active')
    AS active,
  COUNTIF(status = 'paused')
    AS paused,
  COUNTIF(status = 'cancelled')
    AS cancelled
FROM sub_month
GROUP BY month_end
ORDER BY month_end

What it returns:

The same rows, as MRR movements from one month end to the next:

The movements come from the same WITH block, comparing each contract’s month end with the one before. Replace the final SELECT with:

SELECT c.month_end,
  CASE
    WHEN p.status IS NULL THEN 'new'
    WHEN p.status = 'cancelled'
      THEN 'reactivated'
    WHEN c.status = 'paused'
      THEN 'paused'
    WHEN p.status = 'paused'
      THEN 'resumed'
    WHEN c.status = 'cancelled'
      THEN 'churned'
    WHEN c.mrr > p.mrr
      THEN 'expansion'
    ELSE 'downgrade'
  END AS movement,
  SUM(c.mrr - IFNULL(p.mrr, 0))
    AS mrr_change
FROM sub_month c
LEFT JOIN sub_month p
  ON p.sub_id = c.sub_id
  AND p.month_end = LAST_DAY(
    DATE_SUB(c.month_end,
      INTERVAL 1 MONTH))
WHERE c.month_end > '2026-01-31'
  AND c.mrr != IFNULL(p.mrr, 0)
GROUP BY 1, 2
ORDER BY 1, 2

Here “reactivated” is any return; in your data, apply the window and match on the customer. If closed months are frozen, add a late adjustment line for the change a late event makes to the frozen opening figure, so each month starts where the last ended.

Churn for March, both ways, as a month-end snapshot: it compares the status at the two month ends, so a contract that starts and cancels inside one month is missed (count those from subscription_event). Same WITH block, final SELECT:

SELECT
  COUNTIF(p.status = 'active'
    AND c.status = 'cancelled')
  / COUNTIF(p.status = 'active')
    AS churn_active_base,
  COUNTIF(p.status != 'cancelled'
    AND c.status = 'cancelled')
  / COUNTIF(p.status != 'cancelled')
    AS churn_with_paused
FROM sub_month p
JOIN sub_month c
  ON c.sub_id = p.sub_id
WHERE p.month_end = '2026-02-28'
  AND c.month_end = '2026-03-31'

It returns 0.5 and 0.4. Splitting the churned into voluntary and involuntary needs the cancellation’s cancel_type from subscription_event, which these literal rows leave out.

Four things the same five rows show.

February has three different money figures. Cash collected was €123 (before VAT and fees, in this example): S2’s charge on 7 February (€12), S4’s prepaid €81 and S5’s €30. Revenue for boxes shipped in February was €69: S2 €12, S4’s first box €27, S5 €30 (S1 skipped, S3’s charge failed). MRR at month end was €117. None of them is wrong, and only one of them, the €69 if finance recognises boxes on shipment, is revenue. S4’s other €54 sits as deferred revenue until those boxes ship.

March churn is 50% or 40%, from the same data. Two subscribers churned in March, S3 involuntarily and S5 voluntarily. With active subscribers at the start of March as the denominator (S1, S3, S4, S5) it is 2 ÷ 4 = 50%. Count the paused S2 in the base and it is 2 ÷ 5 = 40%. This is the decision to write down.

S3 is in February’s MRR while its card is failing. Under the dunning rule above, its €30 counts until the last retry fails and the cancellation takes effect on 1 March. Exclude contracts in dunning and February MRR is €87.

Cancellation by effective date keeps S5 in February. S5 cancelled on 10 February (recorded_at), but the contract ran to 3 March. By request date, February MRR is €87; by effective date, €117.

For the January cohort (S1, S2, S3, S5), retention at the end of February is 100% not cancelled and 75% active; at the end of March it is 50% either way.

Start from a free model

OWOX publishes a free E-commerce Subscription Store data model: 15 Data Marts for a store that sells the same products once and on a schedule, including Subscriptions, Subscription Events, a monthly snapshot and Selling Plans. It opens in the browser on the OWOX canvas, where you can edit it and export it as an OKF bundle, one Markdown file per table. The definition is on GitHub.

It agrees with this article on pause vs cancellation, voluntary vs involuntary churn, recorded billing attempts and one-time vs recurring orders. Where it differs, adapt it:

• Billing attempts live in Subscription Events, next to lifecycle changes, instead of a charge table; split them for prepaid plans or refunds.
• The monthly value uses a 30-day month. Change it or write it into the definition.
• There is no shipment table and no price history. Add them for prepaid plans or price changes.
• The monthly snapshot is per customer, not per contract, so MRR movement by contract comes from Subscription Events.

It describes the business; it does not connect to your platform.

Where OWOX Data Marts fits, and what it does not do

Everything above is warehouse SQL that any BI tool can read. It is written in BigQuery’s dialect and ports to Snowflake or another warehouse with small changes; in dbt, a snapshot of the month-close figures is one way to keep closed months frozen. OWOX Data Marts takes on three parts:

• Getting the data in: the built-in sources include Shopify for orders; a subscription platform’s API can be added as a custom connector, described in a configuration file.
• Owning the definitions: each table becomes a Data Mart with its grain and primary key, together forming the data model, and each metric is defined once as a calculated field. Data quality checks on primary key uniqueness catch a webhook loaded twice.
• Delivery: reports to Google Sheets on a schedule; through the MCP server, part of OWOX Data Marts Cloud, Claude or ChatGPT can query the published Data Marts, and the rows returned are sent to that AI provider.

What it does not do: it does not write the SQL that turns events into status periods, and it does not decide whether a pause is churn. Those are yours and the business’s. It does not recognise revenue or replace finance’s figures either; MRR next to recognised revenue is a comparison you build, with finance’s number on one side.

For the person who asked

A note from the analyst to whoever asked for the numbers, ready to forward:

• I can give you MRR, active and paused subscribers, churn, retention by cohort, lifetime value by cohort and boxes shipped per subscriber, monthly, in our spreadsheet, from one model in our warehouse.
• MRR is not revenue. It is the monthly value of the subscriptions running at month end. Revenue is what finance reports. Cash is what we collected. I will show all three side by side so nobody has to guess which one they are looking at.
• That is why our three sources disagree. The payment processor shows cash collected, the subscription platform shows its own MRR, and finance reports recognised revenue. In a test month of five subscriptions, cash was €123, revenue €69 and MRR €117, and all three were right.
• Revenue follows accounting rules. MRR, churn and retention follow ours, and they need to be set. In the same test, March churn was 50% if paused subscribers are left out of the count and 40% if they are in it. Same data, two honest answers.
• Decisions for you:
  • Do paused subscribers count in MRR and in churn, and after how long does a pause become a cancellation?
  • Does a cancellation count when it is requested or when the last paid box ships?
  • Do subscribers whose card is being retried count in MRR?
  • How do we turn a fortnightly or prepaid box into a monthly value: 26 boxes a year, or 30-day months?
  • Is a subscriber who comes back after cancelling new, or returning, and within how long?
  • Does lifetime value run to 6, 12 or 24 months, and is it observed or projected?
• Decisions for finance:
  • When a box or a membership month becomes revenue. Revenue in our reports will come from finance’s ledger, and the warehouse figure will be reconciled to it each month.
  • Whether closed months are restated when late changes arrive, or frozen.
  • The exchange rate: finance’s month-end rate.
  • Product cost for lifetime value as margin, and that all figures are net of VAT.
  • Gift cards: breakage policy, and how gift-card-paid subscriptions are shown.
  • Refunds and store credits: which month they reduce revenue in, and whether a credit reduces MRR.
• Until you decide, I will report paused subscribers separately, outside MRR and outside churn, and count a pause as a cancellation after [N] missed cycles; cancellations on the date they take effect; subscribers whose card is being retried in MRR until the last retry fails; monthly value on 26 boxes a year for fortnightly ones; a customer who comes back within [N] days as returning, not new; and LTV as observed revenue at 3, 6 and 12 months, and say so at the top of the report.
• What you can tell investors, once those are set: “MRR on [date] was [€], counting [active and paused / active only] subscriptions. Monthly subscriber churn was [x]%, excluding paused subscribers.” They may also ask for gross MRR churn (monthly value lost to cancellations and downgrades), net MRR churn (the same, less value gained from subscribers who upgraded or came back) and LTV to CAC (lifetime value against the cost of winning a customer, from our ad spend). I can add each, defined the same way.

Data ModelingBenefits of data modeling for business reportingMarch 3, 2025→Data ModelingBest Data Modeling Tools in 2026July 21, 2026→Data ModelingBest Free ERD Tools in 2026July 10, 2026→

See all articles →