---
title: "E-commerce Subscription Store"
canonical: "https://www.owox.com/data-models/e-commerce-subscription-store"
updated: "2026-09-26"
---

# E-commerce Subscription Store Data Model

15 data marts189 fields[Vlad Flaks](https://github.com/vladflaks)[Rus Obolonsky](https://github.com/Obolrus)

Selling the same catalogue once and on a schedule, this model tracks contracts, charges and lifecycle changes, keeping voluntary churn separate from failed payments and pauses separate from cancellations.

## Overview

An online store that sells the same products two ways — once, and on a schedule — modeled end to end. Traffic, sessions and pageviews describe how shoppers arrive and move through the storefront; the catalog describes what they browse; orders and order lines capture what they buy and what it earns; and a dedicated subscription layer captures the part that makes this business predictable: the contracts, the offers behind them, every charge and lifecycle change they go through, and where each subscriber stands at the end of every month.

The subscription layer is built around the two facts a recurring business lives on. First, that churn has two causes — a subscriber who decides to leave, and a subscriber whose card kept failing — and only one of them is recoverable. Second, that a skip or a pause is not a cancellation, and reporting that conflates them understates retention. Cancellation reasons, dunning retries, skips and pauses are therefore first-class, alongside the offer each contract runs on, so the discount the programme gives away can be weighed against the retention it buys. Advertising spend is unified on a daily grain and shares campaign naming with the storefront's traffic sources, which makes cost per subscriber answerable next to cost per order.

## Example Questions

*   What share of revenue is recurring, how is it moving month over month, and which of new `subscriptions`, `plan changes` and cancellations is driving the change?
*   How do subscriber cohorts retain over three, six and twelve months, and which delivery cadence and acquisition channel produce the stickiest subscribers?
*   What does a new subscriber cost by channel, and how does that compare with the lifetime revenue they return — including the discount the `subscription programme` gives away?

[Explore on canvas →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/ecommerce-subscription-store)

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `ad_spend_id` | STRING | Spend Record ID | PK. Unique identifier of the daily spend record. |
| `date` | DATE | Date | Date the advertising activity occurred on. |
| `source` | STRING | Platform | Advertising platform the spend occurred on. FK to [Traffic Sources](#mart-traffic-sources) |
| `medium` | STRING | Medium | Channel type the spend belongs to, such as cost-per-click. FK to [Traffic Sources](#mart-traffic-sources) |
| `campaign` | STRING | Campaign | Campaign the spend belongs to. FK to [Traffic Sources](#mart-traffic-sources) |
| `ad_group` | STRING | Ad Group | Ad group within the campaign that the spend belongs to. |
| `ad_account` | STRING | Ad Account | Advertising account the money was spent from. |
| `spend` | FLOAT | Spend | Amount spent on advertising on this date. |
| `clicks` | INTEGER | Clicks | Number of clicks the advertising received. |
| `impressions` | INTEGER | Impressions | Number of times the advertising was displayed. |
| `platform_conversions` | FLOAT | Platform-Claimed Conversions | How many purchases the ad platform's own reporting credits to this campaign, set here so it can be read against Orders. It runs ahead of the store's own order count on purpose — 1.25x on Google Ads and Microsoft Ads, 1.40x on Meta Ads, 1.55x on TikTok Ads — because platforms count clicks up to 7 days old plus views up to 1 day old, stitch a purchase across a shopper's devices, and let more than one platform claim the same sale. The gap between this and actual orders is the attribution gap, not an error to reconcile away. |
| `platform_conversion_value` | FLOAT | Platform-Claimed Revenue | Revenue the platform attributes to the conversions it claims above. NULL for TikTok Ads by design — that platform's reporting includes a conversion count but no value, and that gap in what each platform can even tell you is itself worth stating rather than defaulting to zero. |
| `currency` | STRING | Currency | Three-letter code of the currency the spend is reported in. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Traffic Sources](#mart-traffic-sources) | `source = source, medium = medium, campaign = campaign` | N:N | The channel this spend was bought on. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `customer_id` | STRING | Customer ID | PK. Customer this value profile describes. FK to [Customers](#mart-customers) |
| `acquisition_session_id` | STRING | Acquisition Session ID | Browsing session during which the customer was first acquired. FK to [Sessions](#mart-sessions) |
| `net_revenue_last_12m` | FLOAT | Net Revenue Last 12m | Revenue the customer generated over the last twelve months, after discounts and returns. |
| `units_sold_last_12m` | INTEGER | Units Sold Last 12m | Number of individual units the customer bought over the last twelve months. |
| `orders_last_12m` | INTEGER | Orders Last 12m | Number of orders the customer placed over the last twelve months. |
| `lifetime_net_revenue` | FLOAT | Lifetime Net Revenue | Total revenue the customer has generated since their first order. |
| `loyalty_segment` | STRING | Loyalty Segment | Behavioural band the customer falls into, such as New, Returning, Loyal, At risk or Lapsed. |
| `subscriber_status` | STRING | Subscriber Status | Where the customer stands with the subscription programme: Never subscribed, Active, Paused, Churned or Reactivated. |
| `active_subscriptions` | INTEGER | Active Subscriptions | Number of subscriptions the customer currently holds in an active state. |
| `months_subscribed` | INTEGER | Months Subscribed | Number of months the customer has held at least one active subscription. |
| `subscription_revenue_share` | FLOAT | Subscription Revenue Share | Share of the customer's lifetime revenue that came from subscription orders, between zero and one. |
| `first_order_at` | DATE | First Order At | Date the customer placed their first order. |
| `last_order_at` | DATE | Last Order At | Date the customer placed their most recent order. |
| `recency_days` | INTEGER | Recency Days | Number of days since the customer's most recent order. |
| `acquisition_channel_grouping` | STRING | Acquisition Channel Grouping | Channel the customer was originally acquired through, such as Paid Search, Paid Social, Organic or Direct. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Customers](#mart-customers) | `customer_id = customer_id` | 1:1 | The customer this value profile is for. |
| [Sessions](#mart-sessions) | `acquisition_session_id = session_id` | N:1 | The visit that first acquired the customer. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `customer_id` | STRING | Customer ID | PK. Unique identifier of the customer. |
| `registered_at` | DATE | Registered At | Date the customer account was created. |
| `email` | STRING | Email | Most recent known email address of the customer. |
| `phone` | STRING | Phone | Most recent known phone number of the customer. |
| `city` | STRING | City | City the customer's latest order was shipped to. |
| `country` | STRING | Country | Country the customer's latest order was shipped to. |
| `country_code` | STRING | Country Code | Two-letter code of the country the customer buys from. |
| `acquisition_traffic_source_id` | STRING | Acquisition Traffic Source ID | Traffic source that first brought the customer to the store. FK to [Traffic Sources](#mart-traffic-sources) |
| `marketing_opt_in` | BOOLEAN | Marketing Opt In | Whether the customer consented to receive marketing communication. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Traffic Sources](#mart-traffic-sources) | `acquisition_traffic_source_id = traffic_source_id` | N:1 | The channel that acquired this customer. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `order_item_id` | STRING | Order Item ID | PK. Unique identifier of the order line. |
| `order_id` | STRING | Order ID | Order this line belongs to. FK to [Orders](#mart-orders) |
| `product_id` | STRING | Product ID | Product bought on this line. FK to [Products](#mart-products) |
| `quantity` | INTEGER | Quantity | Number of units of the product on this line. |
| `unit_price` | FLOAT | Unit Price | Price paid per unit at the moment of purchase. |
| `unit_cost` | FLOAT | Unit Cost | Cost to the business of one unit of the product. |
| `line_discount` | FLOAT | Line Discount | Discount applied to this line, including the subscription discount. |
| `line_revenue` | FLOAT | Line Revenue | Gross revenue for the line, being quantity multiplied by the price paid. |
| `line_net_revenue` | FLOAT | Line Net Revenue | Revenue recognised for the line, counted only for completed orders. |
| `line_cost` | FLOAT | Line Cost | Cost of goods on this line whatever became of the order — a cancelled or returned line still carries a cost here — so pairing it with `line_net_revenue` instead of `line_net_cost` mixes a booked population with a settled one and lands margin about 0.8 percentage points low. |
| `line_net_cost` | FLOAT | Line Net Cost | Cost of goods for lines that actually sold — zero on a cancelled or returned order — so it is the column that pairs correctly with `line_net_revenue` and `line_net_profit` over that same settled population. |
| `line_net_profit` | FLOAT | Line Net Profit | Profit for the line on completed orders, being net revenue less cost of goods. |
| `is_subscription_item` | BOOLEAN | Is Subscription Item | Whether the line was delivered on a subscription rather than bought one-time. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Orders](#mart-orders) | `order_id = order_id` | N:1 | The order this line belongs to. |
| [Products](#mart-products) | `product_id = product_id` | N:1 | The product sold on this line. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `order_id` | STRING | Order ID | PK. Unique identifier of the order. |
| `order_date` | DATE | Order Date | Date the order was placed. |
| `order_timestamp` | TIMESTAMP | Order Timestamp | Exact moment the order was placed, in UTC. |
| `customer_id` | STRING | Customer ID | Customer who placed the order. FK to [Customers](#mart-customers) |
| `session_id` | STRING | Session ID | Browsing session the order was placed in. FK to [Sessions](#mart-sessions) |
| `subscription_id` | STRING | Subscription ID | Subscription the order was generated by; empty on one-time orders. FK to [Subscriptions](#mart-subscriptions) |
| `purchase_type` | STRING | Purchase Type | Why the order exists: One-time, Subscription first order or Subscription recurring. |
| `order_type` | STRING | Order Type | Whether the order is Retail or Wholesale. |
| `cycle_number` | INTEGER | Cycle Number | Which delivery cycle of the subscription this order fulfils; zero on one-time orders. |
| `status` | STRING | Status | Fulfilment state of the order: Completed, Cancelled or Returned. |
| `gross_sales` | FLOAT | Gross Sales | Value of the order before discounts, tax and shipping. |
| `discounts` | FLOAT | Discounts | Total discount applied to the order, including the subscription discount. |
| `tax` | FLOAT | Tax | Total tax charged on the order. |
| `shipping` | FLOAT | Shipping | Total shipping charged on the order. |
| `net_revenue` | FLOAT | Net Revenue | Settled revenue for the order — zero on a cancelled or returned order, so it never has to be filtered by status to be trusted. Booked revenue is not lost, it is simply a different number: `gross_sales - discounts`, carried on every row regardless of status. |
| `applied_discount_codes` | STRING | Discount Codes | Promotional codes the customer used at checkout. |
| `loyalty_segment` | STRING | Loyalty Segment | Loyalty band the customer was in when the order was placed. |
| `currency` | STRING | Currency | Three-letter code of the currency the order was placed in. |
| `items_count` | INTEGER | Items Count | Number of order lines on the order. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Customers](#mart-customers) | `customer_id = customer_id` | N:1 | The customer who placed this order. |
| [Sessions](#mart-sessions) | `session_id = session_id` | N:1 | The visit this order was placed in — absent on a recurring charge. |
| [Subscriptions](#mart-subscriptions) | `subscription_id = subscription_id` | N:1 | The contract this order was billed under, where it is recurring. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `pageview_id` | STRING | Pageview ID | PK. Unique identifier of the page view. |
| `session_id` | STRING | Session ID | Session the page view belongs to. FK to [Sessions](#mart-sessions) |
| `page_id` | STRING | Page ID | Page that was viewed. FK to [Pages](#mart-pages) |
| `date` | DATE | Date | Date the page view occurred. |
| `hit_number` | INTEGER | Hit Number | Position of the page view within its session, starting at one. |
| `hit_timestamp` | TIMESTAMP | Hit Timestamp | Exact moment the page view was recorded, in UTC. |
| `pageview_count` | INTEGER | Pageview Count | Always one, so page views can be summed without counting distinct identifiers. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Pages](#mart-pages) | `page_id = page_id` | N:1 | The page that was viewed. |
| [Sessions](#mart-sessions) | `session_id = session_id` | N:1 | The visit this page view belongs to. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `page_id` | STRING | Page ID | PK. Unique identifier of the page. |
| `page_path` | STRING | Page Path | URL path of the page relative to the domain root. |
| `page_title` | STRING | Page Title | Human-readable title of the page. |
| `page_type` | STRING | Page Type | Function the page serves, such as Home, Category, Product, Cart, Checkout, Subscription Portal, Blog or Account. |
| `host_name` | STRING | Host Name | Domain the page is served from. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `product_id` | STRING | Product ID | PK. Unique identifier of the product. |
| `product_name` | STRING | Product Name | Name of the product as shown in the storefront. |
| `unified_product_name` | STRING | Unified Product Name | Cleaned product name that groups variants and inconsistent spellings of the same product. |
| `product_brand` | STRING | Product Brand | Brand the product is sold under. |
| `product_category` | STRING | Product Category | Category the product belongs to, such as Supplements, Skincare or Accessories. |
| `sku` | STRING | SKU | Stock keeping unit identifying the exact variant. |
| `price` | FLOAT | Price | Current one-time selling price of a single unit. |
| `unit_cost` | FLOAT | Unit Cost | Cost to the business of producing or acquiring a single unit. |
| `is_subscription_eligible` | BOOLEAN | Is Subscription Eligible | Whether the product can be bought on a subscription plan. |
| `page_id` | STRING | Page ID | Product detail page on the website. FK to [Pages](#mart-pages) |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Pages](#mart-pages) | `page_id = page_id` | N:1 | This product's page on the storefront. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `selling_plan_id` | STRING | Selling Plan ID | PK. Unique identifier of the subscription offer a customer can subscribe to. |
| `plan_name` | STRING | Plan Name | Customer-facing name of the offer, such as Monthly Subscribe & Save or Prepaid 3-Month. |
| `plan_type` | STRING | Plan Type | Whether the customer pays on every delivery (Pay-as-you-go) or upfront for several deliveries (Prepaid). |
| `delivery_interval_unit` | STRING | Delivery Interval Unit | Unit the delivery rhythm is expressed in, such as week or month. |
| `delivery_interval_count` | INTEGER | Delivery Interval Count | Number of interval units between two deliveries, for example 2 with a unit of week means every two weeks. |
| `delivery_interval_days` | INTEGER | Delivery Interval Days | Delivery rhythm normalised to days, so cadences expressed in weeks and months can be compared directly. |
| `prepaid_cycles` | INTEGER | Prepaid Cycles | Number of deliveries covered by a single upfront payment; zero for pay-as-you-go offers. |
| `discount_pct` | FLOAT | Discount % | Discount off the one-time price granted for subscribing on this plan, in percent. |
| `is_skippable` | BOOLEAN | Is Skippable | Whether the subscriber is allowed to skip an upcoming delivery instead of cancelling. |
| `is_active` | BOOLEAN | Is Active | Whether the offer is currently available to new subscribers. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `session_id` | STRING | Session ID | PK. Unique identifier of the browsing session. |
| `date` | DATE | Date | Date the session took place. FK to [Ad Spend](#mart-ad-spend) |
| `visitor_id` | STRING | Visitor ID | Visitor who ran the session. FK to [Visitors](#mart-visitors) |
| `customer_id` | STRING | Customer ID | Customer the session belongs to, when the visitor was recognised. |
| `traffic_source_id` | STRING | Traffic Source ID | Traffic source that produced the session. FK to [Traffic Sources](#mart-traffic-sources) |
| `landing_page_id` | STRING | Landing Page ID | First page of the session. FK to [Pages](#mart-pages) |
| `device_category` | STRING | Device Category | Type of device used during the session, such as mobile, desktop or tablet. |
| `country` | STRING | Country | Country the session came from. |
| `session_count` | INTEGER | Session Count | Always one, so sessions can be summed without counting distinct identifiers. |
| `pageview_count` | INTEGER | Pageview Count | Number of pages viewed during the session. |
| `is_conversion` | BOOLEAN | Is Conversion | Whether the session ended in an order of any kind. |
| `is_subscription_conversion` | BOOLEAN | Is Subscription Conversion | Whether a subscription was started during the session. |
| `source` | STRING | Source | Platform or site the traffic came from, used to align sessions with advertising spend. FK to [Ad Spend](#mart-ad-spend) |
| `medium` | STRING | Medium | Channel type of the traffic, such as cost-per-click or organic. FK to [Ad Spend](#mart-ad-spend) |
| `campaign` | STRING | Campaign | Marketing campaign that produced the session; empty for traffic that runs no campaign, such as organic, direct and referral. FK to [Ad Spend](#mart-ad-spend) |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Ad Spend](#mart-ad-spend) | `date = date, source = source, medium = medium, campaign = campaign` | N:N | Spend on the same day and channel — a cohort match, not this visit's cost. |
| [Pages](#mart-pages) | `landing_page_id = page_id` | N:1 | The page the visit landed on. |
| [Traffic Sources](#mart-traffic-sources) | `traffic_source_id = traffic_source_id` | N:1 | The channel that drove this visit. |
| [Visitors](#mart-visitors) | `visitor_id = visitor_id` | N:1 | The visitor who browsed. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `event_id` | STRING | Event ID | PK. Unique identifier of the subscription event. |
| `subscription_id` | STRING | Subscription ID | Subscription the event belongs to. FK to [Subscriptions](#mart-subscriptions) |
| `event_date` | DATE | Event Date | Calendar date the event occurred on. |
| `event_timestamp` | TIMESTAMP | Event Timestamp | Exact moment the event was recorded, in UTC. |
| `event_type` | STRING | Event Type | What happened: created, charge\_success, charge\_failed, charge\_retry\_success, skipped, paused, resumed, plan\_swapped, quantity\_changed, cancelled or reactivated. |
| `event_category` | STRING | Event Category | Whether the event is a Billing attempt or a Lifecycle change, so the two can be reported apart. |
| `cycle_number` | INTEGER | Cycle Number | Which delivery cycle of the subscription the event relates to, starting at one. |
| `amount` | FLOAT | Amount | Amount involved in the event; the charged amount for billing events and zero for lifecycle changes. |
| `mrr_delta` | FLOAT | MRR Delta | Signed change this event made to the subscription's monthly recurring value. |
| `retry_number` | INTEGER | Retry Number | Which dunning retry this charge attempt was; zero for a first attempt. |
| `decline_reason` | STRING | Decline Reason | Why a charge attempt was declined, such as Insufficient funds, Card expired or Card declined. |
| `quantity_delta` | INTEGER | Quantity Delta | Signed change in the number of units per delivery, on events that changed the quantity. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Subscriptions](#mart-subscriptions) | `subscription_id = subscription_id` | N:1 | The contract this event happened to. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `snapshot_id` | STRING | Snapshot ID | PK. Unique identifier of the subscriber-month record. |
| `month` | DATE | Month | First day of the month the snapshot describes. |
| `customer_id` | STRING | Customer ID | Subscriber the snapshot describes. FK to [Customers](#mart-customers) |
| `primary_selling_plan_id` | STRING | Primary Selling Plan ID | Offer that carried most of this subscriber's recurring revenue in the month. FK to [Selling Plans](#mart-selling-plans) |
| `cohort_month` | DATE | Cohort Month | Month the subscriber first subscribed in, used as the cohort anchor. |
| `months_since_cohort` | INTEGER | Months Since Cohort | Number of months between the cohort month and this snapshot month. |
| `status_at_month_end` | STRING | Status At Month End | Where the subscriber stood on the last day of the month: Active, Paused or Cancelled. |
| `active_subscriptions` | INTEGER | Active Subscriptions | Number of subscriptions the customer held in an active state at month end. |
| `paused_subscriptions` | INTEGER | Paused Subscriptions | Number of the customer's subscriptions that were paused at month end. |
| `recurring_revenue` | FLOAT | Recurring Revenue | Revenue the customer generated from subscription charges during the month. |
| `one_time_revenue` | FLOAT | One Time Revenue | Revenue the customer generated from one-time orders during the month. |
| `total_revenue` | FLOAT | Total Revenue | All revenue the customer generated during the month, recurring and one-time together. |
| `cycles_charged` | INTEGER | Cycles Charged | Number of subscription cycles successfully charged during the month. |
| `failed_charges` | INTEGER | Failed Charges | Number of charge attempts declined during the month. |
| `skipped_cycles` | INTEGER | Skipped Cycles | Number of deliveries the subscriber chose to skip during the month. |
| `is_new_subscriber` | BOOLEAN | Is New Subscriber | Whether the customer's first ever subscription started in this month. |
| `is_churned` | BOOLEAN | Is Churned | Whether the customer's last active subscription ended in this month. |
| `is_reactivated` | BOOLEAN | Is Reactivated | Whether the customer returned to an active subscription this month after a period without one. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Customers](#mart-customers) | `customer_id = customer_id` | N:1 | The subscriber this month belongs to. |
| [Selling Plans](#mart-selling-plans) | `primary_selling_plan_id = selling_plan_id` | N:1 | The offer the subscriber was mainly on that month. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `subscription_id` | STRING | Subscription ID | PK. Unique identifier of the subscription contract. |
| `customer_id` | STRING | Customer ID | Customer who owns this subscription. FK to [Customers](#mart-customers) |
| `selling_plan_id` | STRING | Selling Plan ID | Subscription offer the contract was created on. FK to [Selling Plans](#mart-selling-plans) |
| `product_id` | STRING | Product ID | Product being delivered on this subscription. FK to [Products](#mart-products) |
| `status` | STRING | Status | Current state of the contract: Active, Paused or Cancelled. |
| `started_at` | DATE | Started At | Date the subscription was created. |
| `cohort_month` | DATE | Cohort Month | First day of the month the subscription started in, used for retention cohorts. |
| `quantity` | INTEGER | Quantity | Number of units delivered on each cycle. |
| `unit_price` | FLOAT | Unit Price | Price charged per unit on this contract, after the plan discount. |
| `recurring_value` | FLOAT | Recurring Value | Amount billed on each cycle, being quantity multiplied by the unit price. |
| `monthly_recurring_value` | FLOAT | Monthly Recurring Value | Recurring value normalised to a 30-day month, so cadences of different lengths can be summed into one recurring revenue figure. |
| `billing_interval_days` | INTEGER | Billing Interval Days | Number of days between two charges on this contract. |
| `cycles_completed` | INTEGER | Cycles Completed | Number of cycles successfully charged and delivered so far. |
| `cycles_remaining` | INTEGER | Cycles Remaining | Deliveries still owed on a prepaid contract; zero for pay-as-you-go. |
| `next_charge_date` | DATE | Next Charge Date | Date the next charge is scheduled for, on active contracts. |
| `last_charge_date` | DATE | Last Charge Date | Date of the most recent successful charge. |
| `paused_at` | DATE | Paused At | Date the subscriber paused the contract, when it is currently paused. |
| `cancelled_at` | DATE | Cancelled At | Date the contract ended, when it is no longer active. |
| `cancel_reason` | STRING | Cancel Reason | Why the contract ended, such as Too much product, Too expensive, Product quality, Found alternative, No longer needed or Payment failure. |
| `cancel_type` | STRING | Cancel Type | Whether the ending was Voluntary, meaning the subscriber chose it, or Involuntary, meaning payment retries were exhausted. |
| `failed_payment_count` | INTEGER | Failed Payment Count | Number of charge attempts on this contract that were declined. |
| `max_retries_reached` | BOOLEAN | Max Retries Reached | Whether the dunning process ran out of retries on this contract. |
| `is_prepaid` | BOOLEAN | Is Prepaid | Whether the contract was paid upfront for several deliveries. |
| `tenure_days` | INTEGER | Tenure Days | Number of days the contract has been alive, counted to its end date or to today. |

### Relationships

| Related data mart | On | Cardinality | Meaning |
| --- | --- | --- | --- |
| [Customers](#mart-customers) | `customer_id = customer_id` | N:1 | The subscriber who signed this contract. |
| [Products](#mart-products) | `product_id = product_id` | N:1 | The product shipped on schedule. |
| [Selling Plans](#mart-selling-plans) | `selling_plan_id = selling_plan_id` | N:1 | The offer this contract was signed on. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `traffic_source_id` | STRING | Traffic Source ID | PK. Unique identifier of the source, medium and campaign combination. |
| `source` | STRING | Source | Platform or site the traffic originated from. |
| `medium` | STRING | Medium | Channel type of the traffic, such as cost-per-click, organic or referral. |
| `campaign` | STRING | Campaign | Marketing campaign the traffic belongs to. |
| `keyword` | STRING | Keyword | Search term or targeting keyword that triggered the visit. |
| `ad_content` | STRING | Ad Content | Creative or ad variant the visit came from. |
| `channel_grouping` | STRING | Channel Grouping | Reporting channel the combination rolls up to, such as Paid Search, Paid Social, Organic, Email or Direct. |
| `is_paid` | BOOLEAN | Is Paid | Whether the traffic was acquired through a paid channel. |

## 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

| Column | Type | Alias | Description |
| --- | --- | --- | --- |
| `visitor_id` | STRING | Visitor ID | PK. Unique identifier of the website visitor. |
| `linked_customer_id` | STRING | Linked Customer ID | Customer account this visitor was later recognised as, when they bought. |
| `first_seen_date` | DATE | First Seen Date | Date the visitor first interacted with the site. |
| `last_seen_date` | DATE | Last Seen Date | Date of the visitor's most recent interaction. |
| `total_sessions` | INTEGER | Total Sessions | Number of sessions the visitor has run in total. |
| `acquisition_source` | STRING | Acquisition Source | Platform or site that originally referred the visitor. |
| `acquisition_medium` | STRING | Acquisition Medium | Channel type the visitor was originally acquired through. |
| `acquisition_campaign` | STRING | Acquisition Campaign | Marketing campaign that originally brought the visitor to the site. |
| `cohort_month` | DATE | Cohort Month | First day of the month of the visitor's first visit, used for retention analysis. |
| `visitor_segment` | STRING | Visitor Segment | Engagement band the visitor falls into, such as One-off, Occasional or Frequent. |
| `country` | STRING | Country | Country the visitor browses from. |

## Apply to your project

1.  1

    ### Install the Import Model plugin

    One plugin, installed once, in your own OWOX workspace.

    [Get the plugin →](https://github.com/OWOX/import-model)

2.  2

    ### Import this model

    Point it at this bundle and it creates every data mart above, joins and all.

    [Open the model →](https://model.owox.com/?okf=https://github.com/OWOX/models/tree/main/bundles/ecommerce-subscription-store)

3.  3

    ### Plug in your data and destinations

    Connect your own sources and send the results where your team already works.

    [Browse connectors →](/connectors)

**4\. Optional — customize as you wish.** Rename a column, drop a mart, add your own: once it is imported it is yours, and nothing here syncs back.

## References

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Browse connectors →](https://www.owox.com/connectors) — /connectors.md