E-Commerce Data Model Template: 12 Data Marts

E-Commerce

Ecommerce12 data marts87 fields18 calculated fieldsView on GitHubVlad FlaksRus Obolonsky

Traffic, browsing sessions and the product catalogue lead through to orders and repeat purchases, with unified ad spend across major channels tying acquisition cost back to the revenue it drives.

Hand this to your AI

Ad platforms, analytics and the store each report their own revenue, so nobody can say what a customer really cost.

Paste this into your AI assistant. It builds the E-Commerce model's Data Marts in your data warehouse from your ad, analytics and store data, then shows what each acquired customer cost against the revenue they brought.

The prompt

build the E-Commerce model with its Unified Ad Spend, Traffic Sources and Orders Data Marts, then show me which traffic sources bring customers who buy and what each one cost against the revenue it returned

Open in Claude Open in ChatGPT

Overview

An online retail business modeled end to end — from the traffic that arrives on the storefront, through browsing sessions and product discovery, to orders, revenue and repeat purchasing, with paid advertising across every major channel tied back to the sales it drives. Web sessions, pageviews and traffic sources describe how shoppers find and move through the store; the product catalog and its categories describe what they browse; orders, purchases and customers capture what they buy and how often they return; and unified ad spend brings Google, Facebook, TikTok, LinkedIn, Microsoft, Reddit and X advertising together with Shopify sales so acquisition cost can be weighed against the revenue it returns.

How this business is managed

Marketing — brings in shoppers who buy, at a return on ad spend the business can carry.
Advertising — buys paid clicks on seven ad platforms at the lowest cost it can.
Head of Marketing — oversees paid media across every warehouse the company keeps data in.
Product — turns storefront visits into orders.
Sales — grows settled revenue year on year, across categories and markets.
Operations — gets every order to the customer, with fewer cancellations and returns.
Finance — makes sure booked revenue turns into revenue the store keeps; its data is closed to business users.

Key metrics

Settled revenue — Purchases · Item Net Revenue by Orders · Order Date.
Conversion — Sessions · Conversion Rate, orders per session as a fraction (0.028 means 2.8%).
Ad spend — Unified Ad Spend · Spend.
Order outcome — Orders · Completion Rate, the share of orders neither cancelled nor returned, as a fraction (0.90 means 90%), read on Orders alone; Orders · Status says which.

Key Data Marts

Sessions — every visit, with the channel that brought it and whether it ended in an order.
Purchases — every order line: booked and settled revenue, cost of goods and profit.
Unified Ad Spend — spend, clicks and the platforms' own conversion claims from all seven ad platforms.
Ad Performance — spend set against the orders and revenue the store recorded, campaign by campaign.

Example Questions

Which traffic sources and paid channels bring in the shoppers who actually convert, and what does each acquired customer cost versus the revenue they generate?
How does the storefront funnel — sessions to product views to orders — convert, and where are the biggest drop-offs by country, category or landing page?
Which products and categories drive the most revenue and repeat purchasing, and how does that shift across countries and over time?

Explore on canvas →

Relevant Data Sources

To connect live data to your data model, plug in the data sources you need

Countries Data Mart

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

Column	Type	Alias	Description
`country_id`	INTEGER	Country ID	PK. Unique internal identifier for each country record.
`country`	STRING	Country Name	Full name of the country.
`country_code`	STRING	Country Code	Two-letter ISO country code representing the nation.
`weight`	INTEGER	Priority Weight	Numerical value used to prioritize or rank countries in the e-commerce system.

Customers Data Mart

Customers

Everyone who has registered an account on the storefront, with the channel that first brought them in (a Traffic Sources row), 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

Column	Type	Alias	Description
`customer_id`	INTEGER	Customer ID	PK. Unique identifier for an individual registered customer
`customer_segment`	STRING	Customer Segment	The classification of the customer based on their purchase history or value
`registration_date`	DATE	Registration Date	The specific date when the customer account was created in the system
`acquisition_traffic_source_id`	INTEGER		Traffic source that first brought the customer in. FK to Traffic Sources — the acquisition channel a customer is attributed to. FK to Traffic Sources
`country_id`	INTEGER	Country ID	The primary geographical location assigned to the customer FK to Countries

Relationships

Related data mart	On	Cardinality	Meaning
Countries	`country_id = country_id`	N:1	The customer's home market.
Acquisition Traffic Source	`acquisition_traffic_source_id = traffic_source_id`	N:1	The channel that acquired this customer.

Orders Data Mart

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

Column	Type	Alias	Description
`session_id`	STRING	Session ID	Unique identifier of the session where the order was placed FK to Sessions
`customer_id`	INTEGER	Customer ID	Unique identifier of the customer who placed the order FK to Customers
`order_date`	DATE	Order Date	The date when the transaction was completed
`order_id`	STRING	Order ID	PK. Unique identifier of the purchase transaction FK to Purchases
`status`	STRING	Status	Current fulfillment state of the order (e.g., Completed)
`orders_total`	INTEGER	Orders	Calculated Field. Distinct orders in the selected rows, every status. `COUNT(DISTINCT order_id)`
`completed_orders`	INTEGER	Completed Orders	Calculated Field. Orders that completed — a conditional count with no helper column. `COUNTIF(status = 'Completed')`
`completion_rate`	FLOAT	Completion Rate	Calculated Field. Share of orders that completed rather than being cancelled or returned. `SAFE_DIVIDE(completed_orders, COUNT(DISTINCT order_id))`
`distinct_products_in_order`	INTEGER	Distinct Products in Order	Calculated Field. Distinct products across the selected orders' lines, counted through Purchases into Products. `COUNT(DISTINCT purchases_e_commerce.products_e_commerce.product_id)`

Relationships

Related data mart	On	Cardinality	Meaning
Customers	`customer_id = customer_id`	N:1	The customer who placed this order.
Purchases	`order_id = order_id`	1:N	The lines this order is made of.
Sessions	`session_id = session_id`	N:1	The session this order was placed in.

Pages Data Mart

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

Column	Type	Alias	Description
`page_id`	INTEGER	Page ID	PK. Unique numerical identifier for each specific page on the website. FK to Pageviews
`page_path`	STRING	Page Path	The URL path of the page relative to the domain root.
`page_title`	STRING	Page Title	The human-readable name or display title of the web page.
`page_type`	STRING	Page Type	Functional category of the page, such as Product, Category, or Checkout.

Relationships

Related data mart	On	Cardinality	Meaning
Pageviews	`page_id = page_id`	1:N	Every view of this page.

Pageviews Data Mart

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

Column	Type	Alias	Description
`date`	DATE	Date	The calendar date when the pageview occurred.
`session_id`	STRING	Session ID	PK. Unique identifier for a specific user browsing session.
`visitor_id`	STRING	Visitor ID	Unique identifier for an individual user or browser.
`hit_number`	INTEGER	Hit Number	PK. The sequential order of the pageview within a specific session.
`page_id`	INTEGER	Page ID	Unique identifier for the specific page viewed by the visitor. FK to Pages
`hit_timestamp`	TIMESTAMP	Hit Timestamp	The exact date and time when the pageview was recorded, in UTC.

Relationships

Related data mart	On	Cardinality	Meaning
Pages	`page_id = page_id`	N:1	The page that was viewed.

Product Category Data Mart

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

Column	Type	Alias	Description
`category_id`	INTEGER	Category ID	PK. Unique numerical identifier for each product category.
`category_name`	STRING	Category Name	The descriptive name of the product category used for reporting and classification.
`category_manager`	STRING	Category Manager	Full name of the individual responsible for managing the specific product category.
`target_margin`	FLOAT	Target Margin	The desired profit margin percentage set for the category.
`category_group`	STRING	Category Group	High-level classification used to group related categories together, such as hard or soft goods.

Products Data Mart

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

Column	Type	Alias	Description
`product_id`	INTEGER	Product ID	PK. Unique identifier for a specific product in the catalog
`product_name`	STRING	Product Name	The full commercial name of the product
`price`	FLOAT	Price	The current selling price of a single unit of the product
`cost`	FLOAT	Cost	The acquisition cost or production expense per unit of the product
`sub_category`	STRING	Sub Category	The specific sub-classification of the product within its broader category.
`category_id`	INTEGER	Category ID	Unique identifier for the high-level category the product belongs to. FK to Product Category
`page_path`	STRING	Page Path	The URL relative path for the product's detail page on the website.
`page_id`	INTEGER	Page ID	Unique identifier for the specific web page associated with the product. FK to Pages
`unit_margin_pct`	FLOAT	Unit Margin %	Calculated Field. List-price margin of one unit: price less unit cost, over price. Row-level, one value per product; for margin on what actually sold, use Net Margin % in Purchases. `SAFE_DIVIDE(price - cost, price)`
`price_tier`	STRING	Price Tier	Calculated Field. Each product's price band — Budget, Mid, Premium or Luxury — cut where this catalog's prices actually split (most items sell under 200, a long tail runs to about 840). Row-level; group and filter by it. `CASE WHEN price < 100 THEN 'Budget (<100)' WHEN price < 200 THEN 'Mid (100-200)' WHEN price < 500 THEN 'Premium (200-500)' ELSE 'Luxury (500+)' END`
`avg_list_price`	FLOAT	Avg List Price	Calculated Field. Average catalog price of the selected products, unweighted by how often each sells. `AVG(price)`

Relationships

Related data mart	On	Cardinality	Meaning
Pages	`page_id = page_id`	N:1	This product's page on the storefront.
Product Category	`category_id = category_id`	N:1	The category this product sits in.

Purchases Data Mart

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. A line carries no date of its own: its Orders row says when it was placed and in what state it ended.

Fields

Column	Type	Alias	Description
`purchase_id`	STRING	Purchase ID	PK. A unique identifier for each individual item row within an order
`order_id`	STRING	Order ID	The unique identifier of the transaction. Used to join with the Orders Data Mart. FK to Orders
`product_id`	INTEGER	Product ID	The unique identifier of the purchased product FK to Products
`quantity`	INTEGER	Quantity	The number of units of this specific product included in the purchase line
`sale_price`	FLOAT	Item Sale Price	The price per unit at the moment of purchase
`currency`	STRING	Currency	The currency used for the transaction (e.g., USD)
`unit_cost`	FLOAT	Unit Cost	The cost incurred by the business to acquire or produce a single unit of the product.
`line_revenue`	FLOAT	Item Revenue	Gross revenue for this order line = Item Sale Price × Quantity. Sum across lines for total gross revenue (all order statuses).
`line_cost`	FLOAT	Item COGS	Cost of goods sold for this order line = Unit Cost × Quantity, on all order statuses including Cancelled and Returned. Sum for total COGS incurred. For the settled figure that matches recognised revenue, use Item Net COGS.
`line_net_revenue`	FLOAT	Item Net Revenue	Revenue recognised only for Completed orders (Cancelled / Returned = 0). Sum for net revenue.
`line_net_profit`	FLOAT	Item Net Profit	Profit for Completed orders = (Item Sale Price − Unit Cost) × Quantity, else 0. Sum for total net profit.
`line_net_cost`	FLOAT	Item Net COGS	Cost of goods sold recognised only for Completed orders (Cancelled / Returned = 0). This is the cost figure that pairs with Item Net Revenue: net revenue − net cost = net profit.
`net_margin_pct`	FLOAT	Net Margin %	Calculated Field. Settled profit over settled revenue, from summed components. `SAFE_DIVIDE(SUM(line_net_profit), SUM(line_net_revenue))`
`margin_bucket`	STRING	Margin Bucket	Calculated Field. Each line's gross margin band — Thin, Standard, Healthy or Premium — from its sale price and unit cost, banded to how this store's margins actually spread (mid-40s to high-60s percent, not the wide range a generic Loss/Thin/Healthy/Premium split assumes). Row-level; group and filter by it. `CASE WHEN SAFE_DIVIDE(line_revenue - line_cost, line_revenue) < 0.55 THEN 'Thin (<55%)' WHEN SAFE_DIVIDE(line_revenue - line_cost, line_revenue) < 0.60 THEN 'Standard (55-60%)' WHEN SAFE_DIVIDE(line_revenue - line_cost, line_revenue) < 0.65 THEN 'Healthy (60-65%)' ELSE 'Premium (65%+)' END`

Relationships

Related data mart	On	Cardinality	Meaning
Orders	`order_id = order_id`	N:1	The order this line belongs to.
Products	`product_id = product_id`	N:1	The product sold on this line.

Sessions Data Mart

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 — Unified Ad Spend — and Orders on the other, which is what lets acquisition cost be weighed against revenue.

Fields

Column	Type	Alias	Description
`date`	DATE	Date	The specific date when the browsing session occurred FK to Unified Ad Spend
`session_id`	STRING	Session ID	PK. Unique identifier for an individual user session FK to Pageviews
`customer_id`	INTEGER	Customer ID	Unique identifier of the customer associated with the session
`device_category`	STRING	Device Category	The type of hardware device used during the session (e.g., mobile, desktop)
`conversion_seed`	FLOAT	Conversion Seed	A technical value used to simulate the probability of a transaction.
`visitor_id`	STRING	Visitor ID	Unique identifier for the anonymous or recognized visitor. FK to Visitors
`traffic_source_id`	INTEGER	Traffic Source ID	Internal numeric identifier for the marketing traffic source. FK to Traffic Sources
`country_id`	INTEGER	Country ID	Numeric identifier representing the geographic country of the visitor. FK to Countries
`is_conversion`	BOOLEAN	Is Conversion	Indicates whether the session resulted in a successful transaction or goal completion.
`source`	STRING	Source	The origin of the traffic, such as Google, Facebook, or direct entry. FK to Unified Ad Spend
`medium`	STRING	Medium	The high-level channel type of the traffic, such as organic or cost-per-click. FK to Unified Ad Spend
`campaign`	STRING	Campaign	The name of the specific marketing campaign that drove the session.
`sessions_count`	INTEGER	Sessions	Calculated Field. Distinct sessions in the selected rows. `COUNT(DISTINCT session_id)`
`orders_count`	INTEGER	Orders	Calculated Field. Distinct orders placed in those sessions, counted across the join. `COUNT(DISTINCT orders_e_commerce.order_id)`
`conversion_rate`	FLOAT	Conversion Rate	Calculated Field. Orders per session — right at every cut. Inlined rather than composed over `orders_count`, because that formula reads a joined mart (Orders) and a formula may not read another formula that itself reads a join. `SAFE_DIVIDE(COUNT(DISTINCT orders_e_commerce.order_id), sessions_count)`
`traffic_type`	STRING	Traffic Type	Calculated Field. Paid, Organic, Owned or Direct, from the medium. Row-level; group and filter by it. `CASE WHEN medium = 'cpc' THEN 'Paid' WHEN medium = 'organic' THEN 'Organic' WHEN medium = 'newsletter' THEN 'Owned' WHEN medium = '(none)' THEN 'Direct' ELSE 'Other' END`

Relationships

Related data mart	On	Cardinality	Meaning
Countries	`country_id = country_id`	N:1	The market the session came from.
Orders	`session_id = session_id`	1:1	The order this session produced, where it converted.
Pageviews	`session_id = session_id`	1:N	The clickstream of this session.
Traffic Sources	`traffic_source_id = traffic_source_id`	N:1	The channel that drove this session.
Unified Ad Spend	`date = date, source = source, medium = medium`	N:N	Spend on the same day and channel — a cohort match, not this session's cost.
Visitors	`visitor_id = visitor_id`	N:1	The visitor who browsed.

Traffic Sources Data Mart

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

Column	Type	Alias	Description
`traffic_source_id`	INTEGER	Traffic Source ID	PK. Unique internal identifier for a specific combination of traffic source, medium, and campaign.
`source`	STRING	Source	The origin of the website traffic, such as a search engine, social network, or domain.
`medium`	STRING	Medium	The high-level category of the traffic source, such as organic, cost-per-click, or referral.
`campaign`	STRING	Campaign Name	The specific marketing campaign name associated with the traffic.
`is_paid`	BOOLEAN	Is Paid Traffic	Indicates whether the traffic was generated through a paid marketing channel.
`channel_grouping`	STRING	Channel Grouping	The classification of traffic into broad categories like Paid Marketing, Direct, or Organic.

Unified Ad Spend Data Mart

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 — Ad Performance does exactly that, campaign by campaign.

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

Column	Type	Alias	Description
`date`	DATE	Date	PK. The calendar date when the advertising activity occurred.
`source`	STRING	Source	PK. The name of the advertising platform or network where the traffic originated. FK to Traffic Sources
`medium`	STRING	Medium	PK. The marketing channel or payment model used, such as cost-per-click. FK to Traffic Sources
`campaign`	STRING	Campaign	PK. The specific marketing campaign name associated with the ad spend. FK to Traffic Sources
`spend`	FLOAT	Spend	The total cost of advertising incurred during the specified period.
`clicks`	INTEGER	Clicks	The total number of times users clicked on the advertisements.
`impressions`	INTEGER	Impressions	The total number of times the advertisements were displayed to users.
`platform_conversions`	FLOAT	Platform Conversions	Conversions the ad platform itself claims it drove. Deliberately higher than the store's own order count — platforms count post-view as well as post-click, stitch across devices, and two of them will claim the same purchase. Compare with Orders to size the attribution gap. NULL for X, whose connector reports no conversions.
`platform_conversion_value`	FLOAT	Platform Conversion Value	Order value the ad platform attributes to those conversions. NULL for TikTok (its connector reports a conversion count but no value) and for X.
`platform_roas`	FLOAT	Platform ROAS	Calculated Field. Revenue the platforms claim per unit of spend. Spend counts only where a platform reports a value, so TikTok and X do not dilute it. Compare with ROAS in Ad Performance, which divides the store's own revenue. `SAFE_DIVIDE(SUM(platform_conversion_value), SUM(IF(platform_conversion_value IS NOT NULL, spend, NULL)))`
`platform_cpa`	FLOAT	Platform CPA	Calculated Field. Spend per conversion the platforms claim. Spend counts only where a platform reports conversions, so X is left out. `SAFE_DIVIDE(SUM(IF(platform_conversions IS NOT NULL, spend, NULL)), SUM(platform_conversions))`

Relationships

Related data mart	On	Cardinality	Meaning
Traffic Sources	`source = source, medium = medium, campaign = campaign`	N:1	The channel this spend was bought on.

Visitors Data Mart

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

Column	Type	Alias	Description
`visitor_id`	STRING	Visitor ID	PK. Unique identifier for the website visitor.
`linked_customer_id`	INTEGER	Linked Customer ID	Identifier of the registered customer account associated with this visitor, if applicable.
`first_seen_date`	DATE	First Seen Date	The date when the visitor first interacted with the website.
`last_seen_date`	DATE	Last Seen Date	The date of the most recent recorded interaction from this visitor.
`total_sessions`	INTEGER	Total Sessions	Total number of distinct browsing sessions initiated by the visitor.
`acquisition_source`	STRING	Acquisition Source	The specific platform or site that referred the visitor to the website.
`acquisition_medium`	STRING	Acquisition Medium	The high-level channel type used to acquire the visitor, such as organic or paid search.
`acquisition_campaign`	STRING	Acquisition Campaign	The name of the marketing campaign that originally brought the visitor to the site.
`cohort_month`	DATE	Cohort Month	The month and year of the visitor's first visit, used for retention analysis.
`visitor_segment`	STRING	Visitor Segment	Classification of the visitor based on their engagement behavior or frequency.
`country_id`	INTEGER	Country ID	Numeric identifier representing the country where the visitor is located.
`visitors_count`	INTEGER	Visitors	Calculated Field. Distinct visitors in the selected rows, registered or not. `COUNT(DISTINCT visitor_id)`
`returning_visitor_rate`	FLOAT	Returning Visitor Rate	Calculated Field. Share of visitors who came back for a second session or more. `SAFE_DIVIDE(COUNT(DISTINCT IF(total_sessions > 1, visitor_id, NULL)), visitors_count)`
`registration_rate`	FLOAT	Registration Rate	Calculated Field. Share of visitors linked to a registered customer account. `SAFE_DIVIDE(COUNTIF(linked_customer_id IS NOT NULL), visitors_count)`

Apply to your project

1
Install the Import Model plugin
One plugin, installed once, in your own OWOX workspace.
Get the plugin →
2
Import this model
Point it at this bundle and it creates every data mart above, joins and all.
Open the model →
3
Plug in your data and destinations
Connect your own sources and send the results where your team already works.
Browse 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.

Customer stories

ReformationHow OWOX Helped Reformation Make Smarter DecisionsEcommerceMay 23, 2025

DelfastDelfast Saves 80% on Ad Costs: Case Study by OWOXEcommerceMay 15, 2023

Ecommerce BrandHow to Use Available Customer Data for LTV PredictionEcommerceApr 5, 2022

E-Commerce

Example Questions

Countries

Fields

Customers

Fields

Relationships

Orders

Fields

Relationships

Pages

Fields

Relationships

Pageviews

Fields

Relationships

Product Category

Fields

Products

Fields

Relationships

Purchases

Fields

Relationships

Sessions

Fields

Relationships

Traffic Sources

Fields

Unified Ad Spend

Fields

Relationships

Visitors

Fields

Install the Import Model plugin

Import this model

Plug in your data and destinations