Harbor & Pine: Revenue Inflated by Item-Level Fan-Out
Harbor & Pine is an e-commerce retailer. Its warehouse has orders at one row per order and order_items at one row per item. The Explore 'Order Items' starts from order_items, joins orders with relationship: many_to_one, and joins products many_to_one. The measure total_revenue is defined in the order_items view as sum(${orders.order_total}). For the week of March 3-9, the Explore shows $4.2M, but Finance's order-level report shows $2.8M. When a user filters on product category 'Outdoor', revenue falls to $1.1M, which seems too low. The team also has a total_units measure defined as sum(${order_items.quantity}) and an average_order_value measure defined as ${total_revenue} / ${orders.count}. The data team is debating whether to fix measures or build a new order-level derived table. They have a 30-minute maintenance window and cannot change the source system.
1. Diagnose why `total_revenue` is inflated and why filtering by product category changes it. Explain the fan-out in plain terms and what the correct grain should be.
Read the worked answer
The Explore's base grain is one row per order item, but orders.order_total is an order-level value. A three-item order therefore appears three times, and summing order_total counts the full order three times. That is why $2.8M becomes roughly $4.2M. The behavior is not random: the inflation factor depends on how many item rows each order has. Filtering by product category makes the result worse in a different way. The filter keeps only item rows in 'Outdoor', but each remaining row still carries the full order total. An order with three Outdoor items contributes three times its total, while its other categories disappear. The number is neither total company revenue nor Outdoor revenue; it is a distorted subset of order totals. The correct grain for order revenue is one row per order. Units and item-level gross margin belong at the item grain. If the business needs revenue by product category, that should be calculated from item-level quantity * unit_price, not by allocating an order total to arbitrary items. Clarify the grain before choosing a LookML fix.
2. Write a corrected LookML measure for `total_revenue` and explain when `sql_distinct_key` is appropriate. Also assess whether `average_order_value` is safe.
Read the worked answer
If the measure must stay in the order_items view, make the distinct grain explicit:
measure: total_revenue {
type: sum
sql: ${orders.order_total} ;;
sql_distinct_key: ${orders.order_id} ;;
value_format_name: usd
}
sql_distinct_key tells Looker's symmetric aggregate logic to count each order once even though the underlying rows are items. It is appropriate when a measure's SQL is at a coarser grain than the Explore and the measure would otherwise multiply. It is not a license to mix grains casually: it fixes multiplication, but it does not turn an order total into product revenue. For average_order_value, check the denominator. If ${orders.count} is a count measure on the orders primary key, it counts distinct orders and AOV becomes safe once the numerator is fixed. If the denominator counts item rows, AOV is still wrong. Also label the result carefully: filtering to Outdoor would produce AOV for orders containing Outdoor items, not AOV for Outdoor product sales. That may be useful, but it is not the same question.
3. Compare fixing the measure in the current Explore with building an order-level derived table. Which would you choose and why? Include filter behavior and maintainability.
Read the worked answer
Fixing the measure is the lower-risk first move. It is a small LookML change, fits the 30-minute window, preserves item-level fields for units and product analysis, and immediately stops revenue multiplication. It does not, however, make order totals allocatable to product categories. If users need product revenue, add an item-level measure ${order_items.quantity} * ${order_items.unit_price} and label it separately. An order-level derived table would make order metrics simple and fast, but it changes the Explore's grain and can hide item filters unless you join back carefully. It also adds build and freshness maintenance. My choice depends on the dominant use case: if most questions are order and customer questions, build an order-level Explore and keep item analysis separate; if users need one Explore for both, fix the measure and add the correct item revenue measure. Either way, document the grain in the view description, add tests against Finance's totals, and avoid letting users assume that 'total revenue' can be sliced by product category without meaning a different calculation.