Ecommerce semantic layer: a metric dictionary for gross sales to LTV

An ecommerce semantic layer is a data dictionary with the metrics defined: one written entry for gross sales, net sales, net revenue, contribution margin, CAC, repeat rate, and LTV, each with its grain, date, source tables, and owner. Every dashboard, spreadsheet, and AI tool then reads the same entry.

This post is the dictionary itself, with the places Shopify, Amazon, finance, and marketing usually disagree. It's the starting point I use when building ecommerce data warehouses and reporting. You can download the template as a CSV and fill it in with your team.

Key takeaways

• "Revenue" is at least four different metrics in an ecommerce business. Give each one its own name and decide which is the default when someone says the word.
• Every entry needs six fields to be useful: definition, grain, date used, time zone, source tables, and owner. The date is where most disagreements start.
• Shopify, Amazon, finance, and marketing each report a valid number for their own job. The dictionary's job is to label them, not to force them into one.
• CAC, repeat rate, and LTV each have several honest versions. Write down which one a dashboard shows, and who it includes.
• Test every entry against a source total you trust before anyone builds on it.

Why does an ecommerce brand need a metric dictionary?

Because the same word means different things in different systems, and the differences are rarely mistakes. Shopify counts sales activity, Amazon counts orders and then settles fees later, finance follows its accounting policy, and marketing counts credit for its channels.

I've already walked through why Shopify, finance, and marketing report different revenue, with one store and five answers for October. This post takes the next step: the full set of definitions you'd write down so it stops happening. If the term is new to you, what a semantic layer is covers the basics.

What goes in each dictionary entry?

Each metric gets one row. These are the columns in the template:

Field	What to write	Example for net sales
Definition	One sentence a non-analyst understands	Item sales after discounts and returns
Formula	The calculation, using other dictionary metrics	gross_sales - discounts - returns
Grain	What one row means before adding up	Order line, plus return line
Date used	The date that decides the day and month	Order date for sales, refund date for returns
Time zone	The zone used to cut days	Store time zone
Includes / excludes	The edge cases	Excludes tax, shipping, test orders
Source tables	Where the data comes from	shopify.order_lines, shopify.refund_line_items
Owner	Who approves a change	Finance
Default for	The vague words it answers	"Sales"
Common disagreements	Where other reports differ	Booked vs restated returns
Tie-out check	The total it must match	Shopify net sales, same dates

How should you define each ecommerce metric?

Below are the core entries. They're sample definitions that fit many direct-to-consumer brands. Change them to fit yours, and let finance decide anything that touches the books.

Gross sales

• Definition: item price times quantity, before discounts and returns.
• Grain: order line. Date: order date. Owner: data team.
• Sources: storefront and marketplace order lines.
• Disagreements: Shopify defines gross sales as sales "before discounts and sales reversals are factored in". Amazon's Sales and Traffic report gives ordered product sales by date, which is a sales figure at the time of the order, before its fees and refunds settle. Decide whether gift card sales count.

Net sales

• Definition: gross sales minus discounts and returns. Excludes tax and shipping.
• Grain: order line and return line. Date: order date for sales; for returns, pick one rule. Owner: finance.
• Sources: order lines, refund lines, marketplace settlement refund rows.
• Disagreements: Shopify defines net sales as gross sales minus discounts and sales reversals. Its sales reports put a return on the day it was processed, which lowers the month of the refund, not the month of the sale. A cohort view ties the return back to the order, which restates past months. Both are valid; name them differently, such as net_sales_booked and net_sales_order_cohort. Refund date vs order date shows how far apart they get.

Net revenue

• Definition (sample): net sales plus shipping charged to customers, minus shipping refunded, before tax.
• Grain: order. Date: finance's policy, which might be the order, ship, or delivery date. Owner: finance.
• Sources: orders, shipping lines, and the general ledger for the closed month.
• Disagreements: this is the word most likely to mean four things. Shopify's total sales adds shipping, taxes, duties, and fees to net sales, so it runs higher. Marketing often says "revenue" for attributed order value. Finance recognizes revenue under its own policy and timing. Whatever you choose, the closed month in the ledger is the tie-out, and finance owns the rule.

Contribution margin

• Definition: net revenue minus product cost, fulfillment, and payment and marketplace fees. Before marketing.
• Grain: order. Date: order date, with fees restated when they settle. Owner: finance.
• Sources: dated product costs, 3PL invoices and shipping labels, payment processor and marketplace settlement lines.
• Disagreements: whether ad spend is subtracted (if so, call it contribution after marketing), which fees are included, and when. Amazon fees are estimated at the order and final at settlement, so Amazon profit changes after the sale. Use a dated cost table; a single cost field gives old orders today's cost.

CAC

CAC has several honest versions, and each answers a different question. Four kinds of CAC explains when to use each. For the dictionary, define at least these:

Metric	Formula	Grain	Owner	Main disagreement
blended_cac	All acquisition spend / all new customers	Month	Finance and growth	Which costs count as acquisition: agency fees, creative, retention email
attributed_cac	Channel spend / new customers credited to the channel	Channel and month	Marketing	Platforms credit the same customer, so credits add up to more than total new customers
incremental_cac	Channel spend / new customers the spend caused	Channel and test window	Growth	Test design and how recent the test is

Two rules make CAC comparable: the spend and the new customers cover the same period, and "new customer" uses the same definition as everywhere else in the dictionary.

Repeat rate

• Definition (sample): share of new customers who place another order within 90 days of their first.
• Grain: customer cohort by first order month. Date: first order date. Owner: growth.
• Includes: only customers who've had the full 90 days.
• Disagreements: Shopify's returning customer rate is returning customers divided by all customers in the period, which is a different question from a cohort rate. Decide whether subscription renewals and fully refunded orders count. The retention value calculator shows what a few points of repeat rate are worth.

LTV

• Definition (sample): cumulative contribution per customer 12 months after the first order.
• Grain: customer cohort and acquisition channel. Date: months since first order. Owner: finance and growth.
• Disagreements: revenue LTV vs contribution LTV, observed vs predicted, and how many months. Revenue LTV can rank channels in a different order from contribution LTV. Contribution margin LTV by acquisition channel has the full method.

Where do Shopify, Amazon, finance, and marketing usually differ?

This is the short version to share with the team. It describes common defaults, not rules: check your own reports.

Metric	Shopify reports	Amazon reports	Finance	Marketing
Sales	Gross and net sales on sale and return dates	Ordered product sales by order date	Recognized revenue per policy	Order value credited to channels
Returns	On the date the refund was processed	Through later settlements	In the period they're booked	Check whether conversion value is net of refunds
Fees	Payment fees in Shopify Payments reports	Estimated, then final at settlement	When they post to the ledger	Usually out of scope
New customers	Shopify customer records	Limited buyer identity to match on	Rarely reported	Platform-credited conversions
Time zone	Store time zone, though some finance and payout reports differ	Check each report	Company close calendar	Each ad account's setting

The last row is easy to miss. Shopify itself warns that finance reports and payout exports may use different date ranges or time zones. Ad accounts, marketplaces, and the store can each cut a day at a different hour, so daily numbers drift even when the definitions match. Pick one reporting zone for shared metrics and convert everything to it in the warehouse.

How do you build the dictionary into a semantic layer?

1. Fill in the template. Start with net sales, net revenue, contribution margin, new customers, blended CAC, and repeat rate. That's enough for most leadership meetings.
2. Agree on owners. Each owner signs off on the definition and the tie-out.
3. Build clean tables with one grain each. Order lines, orders, returns, customers, and daily spend. The ecommerce data stack map shows where each source comes from.
4. Put the definitions in one tool. On Snowflake, the semantic views guide builds this exact orders and customers model. Other options sit in the data stack map.
5. Tie out every month. A definition can be right while a connector drops rows.

Frequently asked questions

What is the difference between net sales and net revenue?

In Shopify's definitions, net sales is gross sales minus discounts and returns. Net revenue isn't a single standard term: many brands add shipping charged, and finance applies its own recognition rules. Write down your rule and let finance own it.

Who should own ecommerce metric definitions?

Finance should own anything that ties to the books: net sales, net revenue, and contribution margin. Growth or marketing can own acquisition and retention metrics. The data team owns the build and the tie-out checks.

Should Amazon and Shopify sales be in the same metric?

They can share one definition if you map both to the same grain and dates, and keep a channel dimension. Watch for orders that appear in two systems, such as multi-channel fulfillment. See avoiding double counting across Shopify, Amazon, and TikTok.

Why doesn't my repeat rate match Shopify's returning customer rate?

They answer different questions. Shopify's returning customer rate looks at everyone who ordered in a period. A cohort repeat rate follows new customers from their first order and only counts those with enough time to come back.

Is the template free to use?

Yes. The CSV template has 15 sample entries. The definitions are starting points, not accounting advice.

What to do next

Download the metric dictionary template, fill in the first six rows with your finance and growth leads, and check each against a report you already trust. If your teams still bring different numbers to the same meeting, book a call and we'll work through the first ones together.