LookML view · dating-platform
How this LookML order-item view models SKUs, quantity and average order value
At a glance
Why Basket analysis for web purchases: what was bought, how many items per order, and the value of an average order. Trust: Inferred
Grain Intended as one row per order line. After the GROUP BY it is one row per distinct line value within an order. No primary key is declared. Trust: Inferred
Source dating_platform/purchase_items.view.lookml 76 lines Source: purchase_items.view.lookml L1-10
dating_platform/purchase_items.view.lookml L1-10 Copy
1 - view : transaction_items 2 derived_table : 3 sql : | 4 SELECT 5 ti_orderid, 6 ti_category, 7 ti_sku, 8 ti_name, 9 ti_price, 10 ti_quantity 11 FROM ${TABLE} .events_atomic 12 WHERE event = 'transaction_item' 13 GROUP BY 1 ,2 ,3 ,4 ,5 ,5 ,6 dating-platform, purchase_items.view.lookml, lines 1-10
Shape Trust: Source-definedEvery field as declared, with the dashboard tiles that read it. Computed from the source; nothing here is edited.
Derived table: rebuilt on a SQL trigger. Reads warehouse tables directly.
Relationships Trust: Source-definedNo joins, no upstream views and no dashboard tiles reference this view. It is an island in the model.
Relationships of transaction_items
Text version transaction_items is the base view of transaction_items: base view
What can change the answer 3
!
Caution: Identical order lines are mergedTrust: Source-definedThe derived table groups by all six selected columns. Two lines in one order with the same SKU, price and quantity collapse into one row.
What goes wrong: Items, quantity and sales are undercounted for repeat lines, so basket size reads low.
Read the guide: Primary keys and counts
Source: purchase_items.view.lookml L3-17
dating_platform/purchase_items.view.lookml L3-17 Copy
1 - view : transaction_items 2 derived_table : 3 sql : | 4 SELECT 5 ti_orderid, 6 ti_category, 7 ti_sku, 8 ti_name, 9 ti_price, 10 ti_quantity 11 FROM ${TABLE} .events_atomic 12 WHERE event = 'transaction_item' 13 GROUP BY 1 ,2 ,3 ,4 ,5 ,5 ,6 14 ORDER BY ti_orderid 15 sql_trigger_value : SELECT MAX (collector_tstamp) FROM ${TABLE} .events_atomic 16 distkey : ti_orderid 17 sortkeys : ti_orderid 18 fields : 19 - dimension : order_id 20 sql : ${TABLE} .ti_orderid dating-platform, purchase_items.view.lookml, lines 3-17
!
Caution: Sales ignore quantityTrust: Source-definedsales_valueSUM(price). A line with quantity 3 contributes one price.
What goes wrong: Average order value is understated for any order with multi-unit lines.
Source: purchase_items.view.lookml L42-44
!
Caution: The join key names a column, not a dimensionTrust: Source-definedevent_tracker joins transactions with sql_foreign_key: transaction_items.ti_orderid. The declared dimension is order_id
What goes wrong: The join may not resolve, and if it does, many items per order make transaction sums fan out.
Read the guide: Join fanout
Source: event_tracker.model.lookml L19-22
Parser checks 1 Trust: Source-definedMechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.
Show all 1 check
no-primary-key 1 purchase_items.view.lookml:1 transaction_items declares no primary_key. Related
Guides built on this file Related source files Source model
