E-Commerce Data Model

Countries

The countries the storefront sells into, and the relative weight each one carries in the business. Every visitor, session and customer is tied to a country here, which makes this the single place geography is defined for the whole model — so revenue, conversion and acquisition cost can all be cut the same way across markets.

Fields

Customers

Everyone who has registered an account on the storefront, with the channel that first brought them in, the market they belong to, and the segment their buying behaviour puts them in. This is where acquisition meets retention: the same row explains how a customer was won and how valuable they turned out to be.

Fields

Relationships

Orders

One row per order placed on the storefront — the header of the transaction, tying the browsing session it came from to the customer who placed it, the date, and the fulfilment state. Because a cancelled or returned order still occupies a row, gross order counts and settled revenue can be told apart instead of quietly merged.

Fields

Relationships

Pages

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

Fields

Relationships

Pageviews

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

Fields

Relationships

Product Category

The category tree the catalog is organised by, together with the target margin each category is managed against and the person accountable for it. Because the target sits next to the category, actual margin from the order lines can be judged against what the category was supposed to deliver, not just reported in isolation.

Fields

Products

The product catalog: what the store sells, at what price, at what unit cost, and where each item lives in both the category tree and the website. Price and cost sitting in the same row is what makes unit margin a property of the product rather than something reconstructed downstream.

Fields

Relationships

Purchases

The order lines behind every order — one row per product bought, with quantity, the price actually paid, the cost of goods, and the resulting revenue and profit. Revenue is recognised only for completed orders, so gross booked value and settled value never get confused. This is the mart that answers what the business actually earned.

Fields

Relationships

Sessions

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

Fields

Relationships

Traffic Sources

Every source, medium and campaign combination that sends traffic to the storefront, 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 channel grouping applies to sessions, to customers and to advertising spend.

Fields

Unified Ad Spend

Advertising spend, clicks, impressions and each platform's own conversion claim, from every paid platform the business runs — Google, Facebook, TikTok, LinkedIn, Microsoft, Reddit and X — brought together on one daily grain with a common source, medium and campaign naming. Because it shares that naming with the storefront's traffic sources, spend can be set against the sessions and revenue it produced instead of being read on its own.

Unlike every other mart in this model, this one is defined by SQL inside the data mart itself rather than by a database view — on purpose. Each advertising platform is collected by its own connector-based data mart, each with a different schema and naming, and this query is how those separate marts are unified: seven SELECTs over the tables the connectors land in, normalised to one grain and UNION ALLed together. Open Data Setup to read it — every block is commented with the connector data mart it draws from and links straight to it, which is the shortest demonstration of how connector data becomes reportable with plain SQL.

Fields

Relationships

Visitors

Everyone who has visited the storefront, registered or not: when they first and last appeared, how many sessions they ran, the channel that originally acquired them, the cohort month they belong to, and — where they later signed up — the customer account they became. This is where anonymous traffic and known customers meet, which makes cohort retention answerable.

Fields