Looker for e-commerce: margin, AOV, repeat, one revenue number

An e-commerce steering committee needs about ten metrics, and each of them has one good definition and three bad ones. The approach that lasts is to write each rule once in dbt, on a fact table at order-line grain, and let Looker expose nothing but sums and ratios of totals. We have published a complete reference repository on the public TheLook dataset, with dbt, LookML, a dashboard and CI, to show what that looks like in practice.

The ten metrics and their single definition

The table below is taken from the reference repository's glossary. Each line has an identifier, a one-sentence definition, a dbt column and a LookML measure. There is no second definition anywhere.

Metric	Definition	The usual trap
Gross revenue	Sum of sale price over all lines, whatever their status	Presented as "revenue" to the board
Net revenue	Sum of sale price over lines neither cancelled nor returned	Status read on the order header instead of the line
Net margin	Sale price minus unit cost, over net lines	Cost arrives from the ERP a month late
Margin rate	Net margin divided by net revenue	Average of per-product rates
Orders	Distinct orders with at least one net line	Counting lines instead of orders
Average order value	Net revenue divided by orders	Per-order average, then an average of averages
Customers	Distinct customers with at least one net line	Counting accounts created, purchase or not
Repeat customers	Customers with two or more net orders	A cancelled second order counted as a repeat
Repeat rate	Repeat customers divided by customers	Denominator taken over a different period than the numerator
Return rate	Returned lines divided by lines not cancelled	Cancellations counted as returns

The "net" rule is the decisive one. In the repository it lives in a single intermediate model, int_order_items_enriched, as a boolean is_net_sale derived from a dbt variable listing the excluded statuses. Every mart inherits the flag, every LookML measure reads it, and the difference between gross and net revenue is exactly the value of cancelled and returned lines: the two reconcile line by line, which ends the debate in the meeting.

Customer lifetime revenue (M13 in the glossary) follows the same logic: net revenue restricted to one customer, precomputed per customer in dim_users, so cohorts can be read without a join.

The grain trap: order or order line

Most disagreements between e-commerce dashboards come from a confusion of grain. An order has a header (date, customer, overall status) and lines (product, quantity, price, line status). A three-item order with one item returned has a header status of "delivered" and one line marked "returned". If net revenue reads the header status, it counts the returned item.

The second grain effect is silent multiplication. An Explore that joins orders, lines and customers in one model produces one row per item, so the sum of the header amount is multiplied by the number of items. Looker can rescue this with symmetric aggregates, provided primary keys are declared, but the generated SQL becomes unreadable and slow.

The reference repository chooses grain discipline over rescue. Each Explore is anchored on one fact at one grain: fct_order_items per line, fct_orders per order, dim_users per customer. Every join goes from the fact to a dimension with relationship: many_to_one, and measures that live on the dimension side are removed from the fact Explores with fields:. The generated SQL contains nothing but SUM and COUNT DISTINCT. fct_orders is a roll-up of fct_order_items produced in dbt, which is why the two levels match to the cent.

measure: average_order_value {
  label: "Average order value"
  description: "M6. Net revenue divided by net order count. A ratio of totals."
  type: number
  sql: ${net_revenue} / NULLIF(${order_count}, 0) ;;
  value_format_name: usd
}

AOV is a ratio of two totals. A type: average measure on a per-order basket column gives a different number as soon as you filter or group, and a dashboard showing the mean of monthly means gives a third.

Shopify, GA4, ERP: the sources in BigQuery

A mid-sized shop feeds its warehouse from three families of sources, and each has one thing to say and one thing it should not say.

Source	What it carries	What it must not carry
Commerce platform (Shopify, Magento, PrestaShop)	Orders, lines, refunds, customers, catalogue	Unit cost, often missing or typed by hand
GA4, native export to BigQuery	Sessions, events, acquisition sources, conversion	The official revenue number
ERP or back-office system	Unit cost, stock, credit notes, invoicing	Any notion of session or basket

The rule that prevents difficult meetings: one system of record per fact. Revenue comes from the commerce platform, margin from the join with ERP cost, conversion from GA4. Revenue as measured by GA4 is collected in the browser, subject to consent and ad blockers, and will always miss a few percent against real orders. It serves attribution, not the board.

Two technical details weigh more than expected. The reference date first: order, shipment, invoice or payment date produce four different monthly revenue figures, and the semantic layer must name one. Timezone second: timestamps arrive in UTC and an order placed at 11:30 pm local time changes day depending on the conversion. The repository fixes created_date in UTC as the join key to the calendar, with convert_tz: no, while the visible dimension group still converts for display.

What changes past ten analysts

With two or three analysts, a definition shared verbally more or less holds. Past a dozen, three things stop working: nobody knows every measure any more, each new Explore duplicates existing logic, and the executive dashboards depend on fields nobody dares change.

What we see in production, on a Looker semantic layer that has served the executive dashboards of a large industrial group for several years: what allowed the model to scale was not another tool but four habits that became mandatory.

• One pull request per model change, reviewed by a second person, with the LookML validator and the dbt tests in CI.
• A description on every field, starting with the glossary identifier. The reference repository fails CI when one is missing.
• Permissions at the model level, not the dashboard level: margin and personal data behind an access_grant, rows filtered by country through a user attribute.
• One datagroup triggered by data arrival, so the cache and derived tables refresh together rather than on a clock.

These habits also prepare the next step: a conversational agent plugged into Looker uses only the Explores, labels and descriptions as they exist. A model that is clean for ten analysts is a model ready for agents.

The reference repository, step by step

The code is public: looker-semantic-layer-thelook, MIT licence. It deploys in fifteen minutes on a Google Cloud project from the public dataset bigquery-public-data.thelook_ecommerce.

Architecture. Three dbt layers: staging (one view per source table, renaming and typing), intermediate (the only place where margin and the "net" flag are computed) and marts (fct_order_items, fct_orders, dim_users, dim_products, dim_dates). The glossary dbt/metrics.md lists the thirteen metrics M1 to M13.

LookML. One view per mart, sql_table_name read from a @{dataset} constant so switching environments is a one-line change. Three Explores (order_items, orders, users), all many_to_one. One persistent derived table, order_sequence, which numbers each customer's orders to answer "first order or repeat" at order grain. One datagroup, thelook_daily, triggered by MAX(created_at) on the base fact. One user attribute, country_access, driving both the row filter and access to personal fields.

Dashboard. executive_overview, six tiles and two filters. Every tile title carries the glossary identifier, and no tile computes anything: it reads a measure.

Continuous integration. A house linter parses every .lkml file and rejects joins without relationship, hard-coded dates in Explore filters, views without a primary key and fields without a description. A second job runs dbt build, rebuilding only modified models on pull requests.

The repository also lists the eight anti-patterns it deliberately avoids, from revenue defined three times to persist_for: "24 hours" on every derived table. They are the same ones found in the projects we audit.

FAQ

Do we need dbt if we already have Looker?

Looker can carry the logic in derived tables, but it does not test it. dbt brings tests (uniqueness, non-null, consistency with the source) and a single transformation layer readable by other tools. On an e-commerce scope, the "net" rule in dbt with thin LookML on top is the combination that ages best.

GA4 revenue and Shopify revenue disagree, which one is right?

The commerce platform's, because it matches what was paid. The gap with GA4 is normal and should stay stable over time; its variation deserves an alert, not its existence.

Can we reuse the repository on our Shopify data?

Yes, that is the intended use. The staging layer is the only one that knows the source tables: replace the five stg_ models with the tables from your connector. The intermediate, marts and LookML layers stay unchanged as long as the output columns keep their names.

How should partial returns and credit notes be handled?

At line grain, a partial return is one returned line among other net lines of the same order, which the model already handles. A commercial credit note with no physical return is a separate event, to be carried in a dedicated fact table and deducted from net revenue through an explicit measure rather than by altering the line status.

Next step

If your committee still debates the definition of average order value, start with the glossary: ten lines, one definition per line, signed off by the head of e-commerce. The reference repository then shows how those ten lines become tables, measures and a dashboard.

See the Semantic layer on BigQuery, dbt and Looker service, and read next: Why your numbers disagree and The dbt, BigQuery, Looker stack.