DataBull Search
dating_platform/event_transactions.view.lookml 12 dims2 measures 3 warnings Repo files

LookML view · dating-platform

How this LookML transactions view models web orders from tracked events

At a glance

Does
Builds web orders from transaction events: order id, affiliation, total, tax, city, state, country and the first timestamp. It defines transactions_count and transactions_value.Trust: Source-defined
Why
Ecommerce revenue captured by the web tracker, likely subscription purchases, so revenue can be tied to sessions and acquisition.Trust: Inferred
Breaks
3 warnings. Top: GROUP BY includes the aggregate column
Grain
One row per order, with order_id declared as the primary key.Trust: Source-defined
Source
dating_platform/event_transactions.view.lookml 78 lines
Source: event_transactions.view.lookml L1-10
dating_platform/event_transactions.view.lookmlL1-10
- view: transactions  derived_table:    sql: |      SELECT        domain_userid,        domain_sessionidx,        tr_orderid,        tr_affiliation,        tr_total,        tr_tax,        tr_city,        tr_state,        tr_country,

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

Shape

Trust: Source-defined

Every 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.

Dimensions 12
NameTypeSQLUsed byTags
user_idL25string${TABLE}.domain_userid-
session_indexL27int${TABLE}.domain_sessionidx-
session_idL30string${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx-derived
order_idL32string${TABLE}.tr_orderid-primary key
affiliationL35string${TABLE}.tr_affiliation-
transaction_valueL37number${TABLE}.tr_total-
taxL41number${TABLE}.tr_tax-
cityL45string${TABLE}.tr_city-
stateL47string${TABLE}.tr_state-
countryL49string${TABLE}.tr_country-
occurredtime, date, week, monthL51time group${TABLE}.tr_tstamp-
order_itemsL59string${order_id}-derived

Measures 2
NameTypeSQLUsed byTags
transactions_countL61count_distinct${order_id}-derived
transactions_valueL65sum${transaction_value}-derived

Relationships

Trust: Source-defined
Relationships of transactions
Relationships of transactions. Text version follows the diagram.This viewUsed by: explores, views, dashboardsbase viewas transactions, fk transaction_items.ti_orderid transactions explore in event_tracker transaction_items explore in event_tracker
  • This Block
  • No relationship declared
Text version
  • transactions is the base view of transactions: base view
  • transactions is joined into transaction_items: as transactions, fk transaction_items.ti_orderid (no relationship declared)

Worth noticing

Order time is the first event time

Trust: Source-defined

occurred_time comes from MIN(collector_tstamp) across the order's transaction events, the collector clock rather than the device clock.

Source: event_transactions.view.lookml L14-18
dating_platform/event_transactions.view.lookmlL14-18
        tr_city,        tr_state,        tr_country,        MIN(collector_tstamp) AS tr_tstamp      FROM ${TABLE}.events_atomic      WHERE event = 'transaction'      GROUP BY 1,2,3,4,5,5,6,7,8,9,10      ORDER BY tr_orderid    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic    distkey: domain_userid    sortkeys:

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

Revenue is the tracked total

Trust: Source-defined

transactions_value sums transaction_value, which is tr_total. Tax is a separate dimension and is not subtracted.

Source: event_transactions.view.lookml L61-67
dating_platform/event_transactions.view.lookmlL61-67
    sql: ${TABLE}.tr_tstamp  - dimension: order_items    sql: ${order_id}  - measure: transactions_count    type: count_distinct    sql: ${order_id}    detail: transaction_detail*  - measure: transactions_value    type: sum    sql: ${transaction_value}  sets:    transaction_detail:    - order_id

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

What can change the answer 3

Caution: GROUP BY includes the aggregate column

Trust: Source-defined

The SELECT has nine plain columns and MIN(collector_tstamp) as column 10, but the query groups by 1,2,3,4,5,5,6,7,8,9,10. Grouping by an aggregate is rejected by most warehouses.

What goes wrong: The persisted table fails to rebuild, and the explore serves stale orders or an error.

Source: event_transactions.view.lookml L3-18
dating_platform/event_transactions.view.lookmlL3-18
- view: transactions  derived_table:    sql: |      SELECT        domain_userid,        domain_sessionidx,        tr_orderid,        tr_affiliation,        tr_total,        tr_tax,        tr_city,        tr_state,        tr_country,        MIN(collector_tstamp) AS tr_tstamp      FROM ${TABLE}.events_atomic      WHERE event = 'transaction'      GROUP BY 1,2,3,4,5,5,6,7,8,9,10      ORDER BY tr_orderid    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic    distkey: domain_userid    sortkeys:

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

Caution: Two models declare an explore named transactions

Trust: Source-defined

event_tracker exposes this view as transactions. enterprise declares another transactions explore from user_transactions, which is not in the repo. Dashboards that say explore: transactions do not say which one they mean.

What goes wrong: Revenue tiles read transactions.total_revenue, a field this view does not have.

Read the guide: Missing tables and stale references

Source: event_tracker.model.lookml L18-22
dating_platform/event_tracker.model.lookmlL18-22
  - join: visitors    sql_on: sessions.domain_userid = visitors.domain_userid- explore: visitors- explore: transactions- explore: transaction_items  joins:  - join: transactions    sql_foreign_key: transaction_items.ti_orderid- explore: session_apps

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

Note: The drill set names raw columns

Trust: Source-defined

transaction_detail lists domain_userid and domain_sessionidx. The declared fields are user_id and session_index.

Source: event_transactions.view.lookml L69-78
dating_platform/event_transactions.view.lookmlL69-78
    type: sum    sql: ${transaction_value}  sets:    transaction_detail:    - order_id    - occurred_time    - domain_userid    - domain_sessionidx    - transaction_value    - city    - state    - country    - order_items

dating-platform, event_transactions.view.lookml, lines 69-78

Parser checks 2

Trust: Source-defined

Mechanical findings from parsing the LookML. They are facts about the files. The cards above explain which ones matter.

Show all 2 checks
  • set-member-missing 2
    • event_transactions.view.lookml:69 Set transactions.transaction_detail lists "domain_userid", which is not a field or set in this view.
    • event_transactions.view.lookml:69 Set transactions.transaction_detail lists "domain_sessionidx", which is not a field or set in this view.

Related

Guides built on this file

Guide Avoiding revenue fanout when joining orders to order items Where order revenue lives, why joining orders to items can double-count it, and how a real ecommerce model sums totals, items and average order value. Revenue Guide Catching missing tables and stale references before a dashboard breaks Models can name tables, fields and explores that do not exist, and nothing fails until a chart runs. Real examples, and how to check yours before release. Best practices

Related source files

Source file transaction_items Order lines from transaction_item events: order id, category, SKU, name, price and quantity. Measures are i... view3 warnings Source file events A derived table over events_atomic with page, referer, structured event and ecommerce columns. It defines e... view4 warnings Source file event_tracker Six explores on project_connection: events joined to sessions and visitors, sessions joined to visitors, pl... model3 warnings Source file business dashboards Business overview (7 tiles), Conversion Rates (3 tiles) and Maps (3 tiles). They chart revenue, transaction... 3 dashboards4 warnings

Source model

Source model Inside a dating platform LookML project: sessions, revenue, subscriptions and messaging Product, revenue and customer analytics 17 views20 joins