E-commerce Subscription Store Data Model

Ad Spend

Advertising spend, clicks and impressions from every paid platform the store runs, on one daily grain, down to the ad group and the account the money was spent from. Because it shares source, medium and campaign naming with the storefront's traffic sources, spend can be set against the sessions, orders and subscriptions it produced instead of being read on its own.

For a subscription business the interesting comparison is not cost per order but cost per subscriber, and the two diverge sharply by channel: a channel with expensive first orders can still be the cheapest source of long-lived subscribers.

Fields

Relationships

Customer Value

What each customer is worth today: revenue and units over the last twelve months, lifetime revenue, how recently they bought, the loyalty band that behaviour puts them in, and where they stand with the subscription programme. The session that first brought them in sits in the same row, which is what lets value be traced back to the channel that acquired it.

Subscription standing is explicit — never subscribed, active, paused, churned or reactivated — alongside the share of the customer's revenue that is recurring. That combination answers the question the programme exists to settle: whether subscribers are genuinely worth more than one-time buyers, and by how much.

Fields

Relationships

Customers

Everyone who has bought from the store, with the channel that first brought them in, the market they buy from, their contact details for lifecycle marketing, and the behavioural type their buying history puts them in. This is the join point between acquisition and everything that follows: the same row explains how a customer was won and where they are today.

This mart stays a register of who the customer is, not of what they have bought: order history, value and loyalty band live in Customer Value, which joins one-to-one and is computed over the orders themselves. Marketing consent is explicit, because win-back and dunning campaigns can only address customers who allow it.

Fields

Relationships

Order Items

The order lines behind every order — one row per product bought, with quantity, the price actually paid, the cost of goods, and the revenue and profit that result. Because unit cost sits next to the price paid, margin is a property of the line rather than something reconstructed later, and the true cost of subscription discounting becomes visible at the product level.

Subscription lines are flagged apart from one-time lines, so the same product can be compared in both worlds: what it earns when bought once, and what it earns when it is delivered on a schedule at a standing discount.

Fields

Relationships

Orders

One row per order — the header of the transaction, with the money on it and the reason it exists. Every order says whether it was a one-time purchase, the first order of a new subscription, or a recurring delivery, and recurring orders carry the subscription and the cycle they belong to. That single classification is what separates predictable revenue from the rest without leaving the order grain.

Gross sales, discounts, tax, shipping and net revenue all sit on the header, so the discount the subscription programme gives away is measurable rather than assumed. Retail and wholesale orders are flagged apart, and cancelled or returned orders keep their row, so booked and settled revenue never quietly merge.

Fields

Relationships

Page Views

Every page view recorded on the storefront, in sequence within its session — which page, when, and where in the session the view sits. This is the finest grain in the model and the raw material for funnel analysis: the step-by-step path between arriving and buying, or between arriving and leaving.

Because the subscription management pages are part of the same page catalog, the same funnel logic covers both new sign-ups and existing subscribers going in to skip, swap or cancel — the behaviour that precedes churn is visible before the churn itself.

Fields

Relationships

Pages

The catalog of pages that make up the storefront — path, display title, and the function each page serves. It turns raw URL paths into something a business user can read, which is what makes funnel analysis by page type possible at all.

The subscription portal is a page type of its own, next to product, cart and checkout. That is deliberate: the pages where subscribers skip, swap or cancel are as load-bearing for a recurring business as the checkout is for a one-time one.

Fields

Products

The catalog: what the store sells, at what price, at what unit cost, which brand and category it belongs to, whether it can be subscribed to, and which page on the site it lives on. Price and cost in the same row make unit margin a property of the product, which is the starting point for judging whether a product can carry a standing subscription discount at all.

Two naming columns are kept side by side on purpose: the name as it appears in the storefront, and a cleaned unified name that groups variants and inconsistent spellings of the same product. Reporting on the unified name is what stops one product from appearing as several.

Fields

Relationships

Selling Plans

The subscription offers the store sells against — every cadence a customer can choose, the discount attached to it, and whether it is paid per delivery or upfront for several deliveries at once. Because the plan carries both the delivery rhythm and the discount, the cost of the subscription programme and the retention it buys can be judged offer by offer rather than as one blended number.

Two families of offer live here, and they behave differently in every report: pay-as-you-go ("subscribe & save"), where the customer is charged on each delivery, and prepaid, where a single payment covers a fixed number of future deliveries. Prepaid revenue arrives in one lump and its churn shows up much later, which is exactly why the plan type has to be explicit rather than inferred.

Fields

Sessions

Every browsing session on the storefront, with the device it happened on, the market it came from, the traffic source and campaign that produced it, the page it started on, and what it ended in. Sessions are the hinge of the model: advertising spend attaches on one side and orders on the other, which is what lets acquisition cost be weighed against the revenue it returns.

Conversions are recorded twice over on purpose — whether the session produced an order at all, and whether it started a subscription. Those are different outcomes worth very different amounts, and a channel that produces plenty of the first and none of the second is exactly what the distinction is there to expose.

Fields

Relationships

Subscription Events

Everything that ever happened to a subscription, in order — created, charged, declined, retried, skipped, paused, resumed, swapped to another plan, changed in quantity, cancelled and reactivated. Each row carries the cycle it belongs to and what it did to recurring revenue, which is what makes a month-over-month recurring revenue movement explainable rather than merely visible.

Billing attempts live here alongside lifecycle changes on purpose. A declined charge never becomes an order, so without these rows the failed-payment half of churn would be invisible — and it is the half that is recoverable, because a retry that succeeds saves a subscriber who never intended to leave.

Fields

Relationships

Subscription Monthly Snapshot

Where every subscriber stood at the end of each month: how many subscriptions they held, how much recurring and one-time revenue they produced, how many cycles were charged, skipped or declined, and whether that month was their first, their last, or a return after a break. Retention and recurring revenue become a single grouping rather than a window calculation.

The cohort the subscriber belongs to travels with every row, so month-three and month-twelve retention can be read straight off this mart and compared across cadences and acquisition channels. Recurring and one-time revenue are kept apart deliberately: a store that sells both ways cannot judge the health of its subscription programme from a blended total.

Fields

Relationships

Subscriptions

One row per subscription contract — who subscribed, to which product on which offer, how much it bills per cycle, how many cycles it has survived, and, when it ended, why. This is the centre of the recurring business: the active rows are the revenue base, and the ended rows are the entire churn story with its reason attached.

A contract distinguishes the two ways subscriptions die, which reporting that only counts cancellations cannot: a subscriber who decided to leave, and a subscriber whose card kept failing until the retries ran out. Pause and skip are held apart from cancellation too, so a subscriber taking a break is never counted as lost.

Fields

Relationships

Traffic Sources

Every source, medium and campaign combination that sends traffic to the storefront, down to the keyword and the creative, grouped into the channels the business actually reports on and flagged for whether the traffic was paid. It is the vocabulary that makes marketing comparable: the same grouping applies to sessions, to customers and to advertising spend.

Keeping the paid flag and the channel grouping here rather than deriving them per report is what stops the same channel from being named three different ways in three different places.

Fields

Visitors

Everyone who has visited the storefront, whether they ever bought or not: when they first and last appeared, how many sessions they ran, the channel that originally acquired them, the month they belong to, and — where they later bought — the customer they became. This is where anonymous traffic and known customers meet.

Because the acquisition channel is recorded on the visitor rather than only on the session, every later order and subscription can be credited back to what first brought that person to the site, even when they returned through a different channel.

Fields