Revenue

Avoiding revenue fanout when joining orders to order items

How much revenue do orders bring, how many items are in an order, and what is the average order value, without counting any order twice?

Based on this implementation

dating-platform: Product, revenue and customer analytics. LookML, Redshift SQL

3 source files

Code excerpts are short and verbatim. SQL is translated from them and has not been run against a warehouse.

Trust key

Trust: Source-defined: Directly observable in the repo.
Trust: Translated: Generated from the source definitions automatically; not run against a warehouse.
Trust: Inferred: Interpretation based on names, usage or structure.
Trust: Suggested: Not in the source. An alternative or extension.
Trust: Missing: Needed to establish correctness but not in the repo.

See the full dating-platform source model

Short answer

Inferred

Sum revenue at the grain where it lives. Order totals belong to the orders table; summed after a join to items, each total repeats once per item. Use item lines (price times quantity) only for item-level revenue, and keep the two numbers apart. In this project the item revenue also leaves quantity out.

Decision it supports: Which revenue number to report, and whether average order value can be trusted.

What to measure

Order revenue

Source-defined

The sum of order totals, one row per order.

Defined as transactions.transactions_value

SUM(transactions.tr_total)Trust: Translated

Show the SQL: Order revenue

Redshift SQLTranslated

SUM(transactions.tr_total)

Translated to Redshift SQL from event_transactions.view.lookml L37-40, L65-67. Not run against a warehouse.

Show the original LookML: event_transactions.view.lookml L65-67

dating_platform/event_transactions.view.lookmlL65-67 Show context

62    type: count_distinct63    sql: ${order_id}64    detail: transaction_detail*65  - measure: transactions_value66    type: sum67    sql: ${transaction_value}68  sets:69    transaction_detail:70    - order_id

dating-platform, event_transactions.view.lookml, lines 65-67

Orders

Source-defined

Distinct order ids.

Defined as transactions.transactions_count

COUNT(DISTINCT transactions.tr_orderid)Trust: Translated

Show the SQL: Orders

Redshift SQLTranslated

COUNT(DISTINCT transactions.tr_orderid)

Translated to Redshift SQL from event_transactions.view.lookml L32-34, L61-64. Not run against a warehouse.

Show the original LookML: event_transactions.view.lookml L61-64

dating_platform/event_transactions.view.lookmlL61-64 Show context

58    sql: ${TABLE}.tr_tstamp59  - dimension: order_items60    sql: ${order_id}61  - measure: transactions_count62    type: count_distinct63    sql: ${order_id}64    detail: transaction_detail*65  - measure: transactions_value66    type: sum67    sql: ${transaction_value}

dating-platform, event_transactions.view.lookml, lines 61-64

Item sales value

Source-defined

The sum of item prices. Quantity is not multiplied in.

Defined as transaction_items.sales_value

SUM(transaction_items.ti_price)Trust: Translated

Show the SQL: Item sales value

Redshift SQLTranslated

SUM(transaction_items.ti_price)

Translated to Redshift SQL from purchase_items.view.lookml L27-30, L42-44. Not run against a warehouse.

Show the original LookML: purchase_items.view.lookml L42-44

dating_platform/purchase_items.view.lookmlL42-44 Show context

39  - measure: items_bought_count40    type: sum41    sql: ${quantity}42  - measure: sales_value43    type: sum44    sql: ${price}45  - measure: orders_count46    type: count_distinct47    sql: ${order_id}

dating-platform, purchase_items.view.lookml, lines 42-44

Items bought

Source-defined

The sum of quantities.

Defined as transaction_items.items_bought_count

SUM(transaction_items.ti_quantity)Trust: Translated

Show the SQL: Items bought

Redshift SQLTranslated

SUM(transaction_items.ti_quantity)

Translated to Redshift SQL from purchase_items.view.lookml L31-33, L39-41. Not run against a warehouse.

Show the original LookML: purchase_items.view.lookml L39-41

dating_platform/purchase_items.view.lookmlL39-41 Show context

36  - measure: distinct_skus_bought37    type: count_distinct38    sql: ${sku}39  - measure: items_bought_count40    type: sum41    sql: ${quantity}42  - measure: sales_value43    type: sum44    sql: ${price}

dating-platform, purchase_items.view.lookml, lines 39-41

Average order value

Source-defined

Item sales value over distinct orders, so it inherits the missing quantity.

Defined as transaction_items.average_order_value

SUM(transaction_items.ti_price) / NULLIF(COUNT(DISTINCT transaction_items.ti_orderid),0)::REALTrust: Translated

Show the SQL: Average order value

Redshift SQLTranslated

SUM(transaction_items.ti_price) / NULLIF(COUNT(DISTINCT transaction_items.ti_orderid),0)::REAL

Translated to Redshift SQL from purchase_items.view.lookml L19-20, L27-30, L42-48, L53-56. Not run against a warehouse.

Show the original LookML: purchase_items.view.lookml L53-56

dating_platform/purchase_items.view.lookmlL53-56 Show context

50    type: number51    decimals: 252    sql: ${items_bought_count} / NULLIF(${orders_count},0)::REAL53  - measure: average_order_value54    type: number55    decimals: 256    sql: ${sales_value} / NULLIF(${orders_count},0)::REAL57  - measure: count58    type: count59  sets:

dating-platform, purchase_items.view.lookml, lines 53-56

Revenue

Inferred

Order totals and summed item prices are two different revenue numbers. The project never says which one is revenue.

Line revenue

Suggested

Price times quantity per line. Not defined in the source.

The query

Order revenue by month

Suggested

Order revenue and orders per month from the orders table alone, so no join can repeat a total. It depends on the orders derived table, which may not build as written (see What can change the answer). The project's own revenue tiles read a view that is missing, so this field combination is ours.

Show the SQL: order revenue by month

Redshift SQLSuggested

SELECT  DATE_TRUNC('month', transactions.tr_tstamp) AS occurred_month,  SUM(transactions.tr_total) AS transactions_value,  COUNT(DISTINCT transactions.tr_orderid) AS transactions_countFROM transactions  -- derived table, defined in the sourceGROUP BY 1ORDER BY occurred_month

Suggested query. Fields translated to Redshift SQL from event_tracker.model.lookml L18 and event_transactions.view.lookml L32-34, L37-40, L51-58, L61-67. Not run against a warehouse.

The data behind it

Inferred

Entities: order (transaction), order item
Grain: Orders: meant to be one row per order id, though the derived table also groups by user and session. Items: one row per distinct item line.
Time: occurred on orders. Items have no timestamp of their own.
Key fields: transactions.order_idtransactions.transaction_valuetransaction_items.order_idtransaction_items.pricetransaction_items.quantity

What can change the answer 4

Caution: Item revenue ignores quantity

Source-defined

sales_value is SUM(price). Quantity is summed separately and never multiplied in.

What goes wrong: An order of three units at 10 reports 10 of sales value, and average order value drops with it.

Show the SQL: sales value

Redshift SQLTranslated

SUM(transaction_items.ti_price)

Translated to Redshift SQL from purchase_items.view.lookml L27-30, L42-44. Not run against a warehouse.

Show the original LookML: purchase_items.view.lookml L42-44

dating_platform/purchase_items.view.lookmlL42-44 Show context

39  - measure: items_bought_count40    type: sum41    sql: ${quantity}42  - measure: sales_value43    type: sum44    sql: ${price}45  - measure: orders_count46    type: count_distinct47    sql: ${order_id}

dating-platform, purchase_items.view.lookml, lines 42-44

Caution: Identical lines collapse

Source-defined

Grouping by all six columns merges two identical lines of one order into a single row.

Read the guide: Primary keys and counts

Source: purchase_items.view.lookml L12-14

dating_platform/purchase_items.view.lookmlL12-14 Show context

9        ti_price,10        ti_quantity11      FROM ${TABLE}.events_atomic12      WHERE event = 'transaction_item'13      GROUP BY 1,2,3,4,5,5,614      ORDER BY ti_orderid15    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic16    distkey: ti_orderid17    sortkeys: ti_orderid

dating-platform, purchase_items.view.lookml, lines 12-14

Caution: The orders table groups by an aggregate

Source-defined

GROUP BY 1,2,3,4,5,5,6,7,8,9,10 includes column 10, which is MIN(collector_tstamp). Most warehouses reject this, so the table may not build.

Source: event_transactions.view.lookml L14-18

dating_platform/event_transactions.view.lookmlL14-18 Show context

11        tr_city,12        tr_state,13        tr_country,14        MIN(collector_tstamp) AS tr_tstamp15      FROM ${TABLE}.events_atomic16      WHERE event = 'transaction'17      GROUP BY 1,2,3,4,5,5,6,7,8,9,1018      ORDER BY tr_orderid19    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic20    distkey: domain_userid21    sortkeys:

dating-platform, event_transactions.view.lookml, lines 14-18

Note: Two explores named transactions

Source-defined

The enterprise model has a different transactions explore over a missing view. Dashboards can resolve to either.

Read the guide: Missing tables and stale references

Source: event_tracker.model.lookml L18-22

dating_platform/event_tracker.model.lookmlL18-22 Show context

15  - join: visitors16    sql_on: sessions.domain_userid = visitors.domain_userid17- explore: visitors18- explore: transactions19- explore: transaction_items20  joins:21  - join: transactions22    sql_foreign_key: transaction_items.ti_orderid23- explore: session_apps

dating-platform, event_tracker.model.lookml, lines 18-22

How the real implementation does it

transactions

Source-defined

Orders come from transaction events, one row per order with its first timestamp.

Source: event_transactions.view.lookml L1-23

dating_platform/event_transactions.view.lookmlL1-23 Show context

1- view: transactions2  derived_table:3    sql: |4      SELECT5        domain_userid,6        domain_sessionidx,7        tr_orderid,8        tr_affiliation,9        tr_total,10        tr_tax,11        tr_city,12        tr_state,13        tr_country,14        MIN(collector_tstamp) AS tr_tstamp15      FROM ${TABLE}.events_atomic16      WHERE event = 'transaction'17      GROUP BY 1,2,3,4,5,5,6,7,8,9,1018      ORDER BY tr_orderid19    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic20    distkey: domain_userid21    sortkeys:22    - domain_sessionidx23    - tr_orderid24  fields:25  - dimension: user_id26    sql: ${TABLE}.domain_userid

dating-platform, event_transactions.view.lookml, lines 1-23

transaction_items

Source-defined

Items come from transaction_item events, grouped by every column.

Source: purchase_items.view.lookml L1-14

dating_platform/purchase_items.view.lookmlL1-14 Show context

1- view: transaction_items2  derived_table:3    sql: |4      SELECT5        ti_orderid,6        ti_category,7        ti_sku,8        ti_name,9        ti_price,10        ti_quantity11      FROM ${TABLE}.events_atomic12      WHERE event = 'transaction_item'13      GROUP BY 1,2,3,4,5,5,614      ORDER BY ti_orderid15    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic16    distkey: ti_orderid17    sortkeys: ti_orderid

dating-platform, purchase_items.view.lookml, lines 1-14

transaction_items

Source-defined

Item sales value sums price only.

Show the SQL: item sales value

Redshift SQLTranslated

SUM(transaction_items.ti_price)

Translated to Redshift SQL from purchase_items.view.lookml L27-30, L42-44. Not run against a warehouse.

Show the original LookML: purchase_items.view.lookml L42-44

dating_platform/purchase_items.view.lookmlL42-44 Show context

39  - measure: items_bought_count40    type: sum41    sql: ${quantity}42  - measure: sales_value43    type: sum44    sql: ${price}45  - measure: orders_count46    type: count_distinct47    sql: ${order_id}

dating-platform, purchase_items.view.lookml, lines 42-44

event_tracker

Inferred

The item explore joins orders on the order id column. Items to orders is many-to-one, but no relationship is declared.

Source: event_tracker.model.lookml L21-22

dating_platform/event_tracker.model.lookmlL21-22 Show context

18- explore: transactions19- explore: transaction_items20  joins:21  - join: transactions22    sql_foreign_key: transaction_items.ti_orderid23- explore: session_apps

dating-platform, event_tracker.model.lookml, lines 21-22

Other ways to measure it

Define line revenue

Suggested

Multiply price by quantity per line and sum that per order. Average order value can then use the same numerator.

Show the suggested SQL: line revenue per order

Suggested, not from source

SELECT  ti_orderid,  SUM(ti_price * ti_quantity) AS line_revenue,  SUM(ti_quantity) AS items_boughtFROM transaction_items  -- the derived table defined in the sourceGROUP BY ti_orderid

Join items only after aggregating them

Suggested

When a report needs order totals and item figures side by side, bring items to one row per order first, then join. The join cannot repeat an order total.

Show the suggested SQL: orders with item figures

Suggested, not from source

WITH items AS (  SELECT    ti_orderid,    SUM(ti_price * ti_quantity) AS line_revenue  FROM transaction_items  -- the derived table defined in the source  GROUP BY ti_orderid)SELECT  SUM(t.tr_total) AS order_revenue,  SUM(i.line_revenue) AS line_revenueFROM transactions AS t  -- the derived table defined in the sourceLEFT JOIN items AS i ON i.ti_orderid = t.tr_orderid

Keep item lines distinct

Suggested

Select the event id with each item so identical lines stay separate, and declare it as the primary key.

Short answer

What to measure

Order revenue

Orders

Item sales value

Items bought

Average order value

Revenue

Line revenue

The query

Order revenue by month

The data behind it

What can change the answer 4

Caution: Item revenue ignores quantity

Caution: Identical lines collapse

Caution: The orders table groups by an aggregate

Note: Two explores named transactions

How the real implementation does it

transactions

transaction_items

transaction_items

event_tracker

Other ways to measure it

Define line revenue

Join items only after aggregating them

Keep item lines distinct

Related questions