LookML view · dating-platform

How this LookML events view models page views, pings, transactions and usage time

At a glance

Does: A derived table over events_atomic with page, referer, structured event and ecommerce columns. It defines event, page view, ping and transaction counts, per-session and per-visitor ratios, and an approximate usage time.Trust: Source-defined
Why: Event-level engagement for the dating site: how many pages and pings a session produces and how often sessions end in a transaction.Trust: Inferred
Breaks: 4 warnings. Top: Seconds measures are computed from minutes
Grain: One row per tracked event, keyed by event_id, which is the declared primary key. Rows without a domain_userid are dropped.Trust: Source-defined
Source: dating_platform/activities.view.lookml 338 lines

Source: activities.view.lookml L1-10

dating_platform/activities.view.lookmlL1-10 Show context

1- view: events2  derived_table:3    sql: |4      SELECT5      app_id,6      domain_userid,7      domain_sessionidx,8      event_id,9      event,10      collector_tstamp,11      dvce_tstamp,12      page_title,13      page_urlscheme,

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

Shape

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 48

Name	Type	SQL	Used by	Tags
`event_id`L57	string	`${TABLE}.event_id`	-	primary key
`event_type`L60	string	`${TABLE}.event`	-
`timestamp`L62	string	`${TABLE}.collector_tstamp`	-	name reused
`timestamp`time, date, week, monthL64	time group	`${TABLE}.collector_tstamp`	-	name reused
`session_index`L72	number	`${TABLE}.domain_sessionidx`	-
`user_id`L75	string	`${TABLE}.domain_userid`	-
`session_id`L77	string	`${TABLE}.domain_userid \|\| '-' \|\| ${TABLE}.domain_sessionidx`	-	derived
`device_timestamp`time, date, week, monthL79	time group	`${TABLE}.dvce_tstamp`	-	hidden
`page_title`L88	string	`${TABLE}.page_title`	-
`page_url_scheme`L90	string	`${TABLE}.page_urlscheme`	-
`page_url_host`L92	string	`${TABLE}.page_urlhost`	-
`page_url_path`L94	string	`${TABLE}.page_urlpath`	-
`page_url_port`L96	int	`${TABLE}.page_urlport`	-	hidden
`page_url_query`L100	string	`${TABLE}.page_urlquery`	-	hidden
`page_url_fragment`L103	string	`${TABLE}.page_urlfragment`	-	hidden
`refr_medium`L106	string	`${TABLE}.refr_medium`	-
`first_event_in_session`L108	yesno	`${TABLE}.refr_medium != 'internal'`	-	derived
`first_event_for_visitor`L111	yesno	`${TABLE}.refr_medium != 'internal' AND ${TABLE}.domai...` `${TABLE}.refr_medium != 'internal' AND ${TABLE}.domain_sessionidx = 1`	-	derived
`refr_source`L114	string	`${TABLE}.refr_source`	-
`refr_term`L116	string	`${TABLE}.refr_term`	-
`refr_url_host`L118	string	`${TABLE}.refr_urlhost`	-
`refr_url_path`L120	string	`${TABLE}.refr_urlpath`	-
`x_offset`L122	int	`${TABLE}.pp_xoffset_max`	-
`pp_xoffset_min`L125	int	`${TABLE}.pp_xoffset_min`	-	hidden
`y_offset`L129	int	`${TABLE}.pp_yoffset_max`	-
`pp_yoffset_min`L132	int	`${TABLE}.pp_yoffset_min`	-	hidden
`structured_event_action`L136	string	`${TABLE}.se_action`	-
`structured_event_category`L138	string	`${TABLE}.se_category`	-
`structured_event_label`L140	string	`${TABLE}.se_label`	-
`structured_event_property`L142	string	`${TABLE}.se_property`	-
`structured_event_value`L144	number	`${TABLE}.se_value`	-
`user_agent`L147	string	`${TABLE}.useragent`	-	hidden
`page_height`L150	int	`${TABLE}.doc_height`	-
`page_width`L153	int	`${TABLE}.doc_width`	-
`transaction_order_id`L156	string	`${TABLE}.tr_orderid`	-
`transaction_affiliation`L158	string	`${TABLE}.tr_affiliation`	-
`transaction_value`L160	number	`${TABLE}.tr_total`	-
`transaction_tax`L164	number	`${TABLE}.tr_tax`	-
`transaction_address_city`L168	string	`${TABLE}.tr_city`	-
`transaction_address_state`L170	string	`${TABLE}.tr_state`	-
`transaction_address_country`L172	string	`${TABLE}.tr_country`	-
`transaction_item_order_id`L174	string	`${TABLE}.ti_orderid`	-
`transaction_item_category`L176	string	`${TABLE}.ti_category`	-
`transaction_item_sku`L178	string	`${TABLE}.ti_sku`	-
`transaction_item_name`L180	string	`${TABLE}.ti_name`	-
`transaction_item_price`L182	number	`${TABLE}.ti_price`	-
`transaction_item_quantity`L186	int	`${TABLE}.ti_quantity`	-
`transaction_items_list`L189	string	`${transaction_order_id}`	-	derived

Measures 21

Name	Type	SQL	Used by	Tags
`count`L191	count	`COUNT(*) of rows`	-
`page_pings_count`L194	count	`COUNT(*) of rows`filters: `event_type = page_ping`	-	filtered
`page_views_count`L199	count	`COUNT(*) of rows`filters: `event_type = page_view`	-	filtered
`transactions_count`L204	count_distinct	`${transaction_order_id}`filters: `event_type = transaction`	-	derivedfiltered
`distinct_pages_viewed_count`L210	count_distinct	`${page_url_path}`	-	derived
`sessions_count`L214	count_distinct	`${session_id}`	-	derived
`page_pings_per_session`L218	number	`NULLIF(${page_pings_count},0)/NULLIF(${sessions_count...` `NULLIF(${page_pings_count},0)/NULLIF(${sessions_count},0)::REAL`	-	derived
`page_pings_per_visitor`L222	number	`NULLIF(${page_pings_count},0)/NULLIF(${visitors_count...` `NULLIF(${page_pings_count},0)/NULLIF(${visitors_count},0)::REAL`	-	derived
`transactions_per_session`L226	number	`NULLIF(${transactions_count},0)/NULLIF(${sessions_cou...` `NULLIF(${transactions_count},0)/NULLIF(${sessions_count},0)::REAL`	-	derived
`transactions_per_visitor`L230	number	`NULLIF(${transactions_count},0)/NULLIF(${visitors_cou...` `NULLIF(${transactions_count},0)/NULLIF(${visitors_count},0)::REAL`	-	derived
`visitors_count`L234	count_distinct	`${user_id}`	-	hiddenderived
`events_per_session`L239	number	`${count}/NULLIF(${sessions_count},0)::REAL`	-	derived
`events_per_visitor`L243	number	`${count}/NULLIF(${visitors_count},0)::REAL`	-	derived
`page_views_per_sessions`L247	number	`${page_views_count}/NULLIF(${sessions_count},0)::REAL`	-	derived
`page_views_per_visitor`L251	number	`${page_views_count}/NULLIF(${visitors_count},0)::REAL`	-	derived
`landing_page_views_count`L255	count	`COUNT(*) of rows`filters: `event_type = page_view, first_event_in_session = true`	-	filtered
`internal_page_views_count`L260	count	`COUNT(*) of rows`filters: `event_type = page_view, first_event_in_session = false`	-	filtered
`approx_user_usage_in_minutes`L265	number	`APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPO...` `APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPOCH FROM ${TABLE}.collector_tstamp)/10), ${TABLE}.domain_userid) ) /6`	-	derived
`approx_usage_per_visitor_in_seconds`L270	number	`${approx_user_usage_in_minutes}/NULLIF(${visitors_cou...` `${approx_user_usage_in_minutes}/NULLIF(${visitors_count}, 0)::REAL`	-	derived
`approx_usage_per_visit_in_seconds`L274	number	`${approx_user_usage_in_minutes}/NULLIF(${sessions_cou...` `${approx_user_usage_in_minutes}/NULLIF(${sessions_count}, 0)::REAL`	-	derived
`transactions_value`L278	sum	`${transaction_value}`filters: `event_type = transaction`	-	derivedfiltered

Relationships

Source-defined

No joins, no upstream views and no dashboard tiles reference this view. It is an island in the model.

Relationships of events

This Block

Text version

events is the base view of events: base view

Filters and exclusions that change meaning

Events without a user id are excluded

Source-defined

The derived table keeps only domain_userid IS NOT NULL. Events from clients that never set the cookie are invisible to every measure.

Source: activities.view.lookml L47-50

dating_platform/activities.view.lookmlL47-50 Show context

44      ti_sku,45      ti_name,46      ti_price,47      ti_quantity48      FROM ${TABLE}.events_atomic49      WHERE domain_userid IS NOT NULL50    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic51    distkey: domain_userid52    sortkeys:53    - domain_userid

dating-platform, activities.view.lookml, lines 47-50

Worth noticing

Landing page views are inferred from the referer

Source-defined

first_event_in_session is true when refr_medium != 'internal'. landing_page_views_count counts page views where that flag is true.

Source: activities.view.lookml L108-113

dating_platform/activities.view.lookmlL108-113 Show context

105    hidden: true106  - dimension: refr_medium107    sql: ${TABLE}.refr_medium108  - dimension: first_event_in_session109    type: yesno110    sql: ${TABLE}.refr_medium != 'internal'111  - dimension: first_event_for_visitor112    type: yesno113    sql: ${TABLE}.refr_medium != 'internal' AND ${TABLE}.domain_sessionidx = 1114  - dimension: refr_source115    sql: ${TABLE}.refr_source116  - dimension: refr_term

dating-platform, activities.view.lookml, lines 108-113

Usage time counts active 10-second buckets

Source-defined

approx_user_usage_in_minutes counts distinct 10-second windows per user with APPROXIMATE COUNT(DISTINCT ...) and divides by 6 to get minutes.

Source: activities.view.lookml L265-277

dating_platform/activities.view.lookmlL265-277 Show context

262    filters:263      event_type: page_view264      first_event_in_session: false265  - measure: approx_user_usage_in_minutes266    type: number267    decimals: 2268    sql: APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPOCH FROM ${TABLE}.collector_tstamp)/10),269      ${TABLE}.domain_userid) ) /6270  - measure: approx_usage_per_visitor_in_seconds271    type: number272    decimals: 2273    sql: ${approx_user_usage_in_minutes}/NULLIF(${visitors_count}, 0)::REAL274  - measure: approx_usage_per_visit_in_seconds275    type: number276    decimals: 2277    sql: ${approx_user_usage_in_minutes}/NULLIF(${sessions_count}, 0)::REAL278  - measure: transactions_value279    type: sum280    sql: ${transaction_value}

dating-platform, activities.view.lookml, lines 265-277

What can change the answer 4

Caution: Seconds measures are computed from minutes

Source-defined

approx_usage_per_visitor_in_seconds and approx_usage_per_visit_in_seconds divide approx_user_usage_in_minutes by visitors or sessions without multiplying by 60.

What goes wrong: A visitor with 3 minutes of activity shows as 3 seconds, so usage looks 60 times lower than it is.

Source: activities.view.lookml L265-277

dating_platform/activities.view.lookmlL265-277 Show context

262    filters:263      event_type: page_view264      first_event_in_session: false265  - measure: approx_user_usage_in_minutes266    type: number267    decimals: 2268    sql: APPROXIMATE COUNT( DISTINCT CONCAT(FLOOR(EXTRACT (EPOCH FROM ${TABLE}.collector_tstamp)/10),269      ${TABLE}.domain_userid) ) /6270  - measure: approx_usage_per_visitor_in_seconds271    type: number272    decimals: 2273    sql: ${approx_user_usage_in_minutes}/NULLIF(${visitors_count}, 0)::REAL274  - measure: approx_usage_per_visit_in_seconds275    type: number276    decimals: 2277    sql: ${approx_user_usage_in_minutes}/NULLIF(${sessions_count}, 0)::REAL278  - measure: transactions_value279    type: sum280    sql: ${transaction_value}

dating-platform, activities.view.lookml, lines 265-277

Caution: NULL referers are not landing views

Inferred

When refr_medium is NULL, refr_medium != 'internal' is NULL, so the event is neither a landing view nor an internal view.

What goes wrong: Direct traffic with no referer can drop out of both landing_page_views_count and internal_page_views_count.

Read the guide: Filters that change metric meaning

Source: activities.view.lookml L108-110

dating_platform/activities.view.lookmlL108-110 Show context

105    hidden: true106  - dimension: refr_medium107    sql: ${TABLE}.refr_medium108  - dimension: first_event_in_session109    type: yesno110    sql: ${TABLE}.refr_medium != 'internal'111  - dimension: first_event_for_visitor112    type: yesno113    sql: ${TABLE}.refr_medium != 'internal' AND ${TABLE}.domain_sessionidx = 1

dating-platform, activities.view.lookml, lines 108-110

Caution: user_agent reads a column the derived table does not select

Source-defined

user_agent reads ${TABLE}.useragent, but the derived SQL never selects useragent. The field is hidden, which hides the error too.

What goes wrong: Any query that unhides or references user_agent fails with a missing column error.

Read the guide: Missing tables and stale references

Source: activities.view.lookml L147-149

dating_platform/activities.view.lookmlL147-149 Show context

144  - dimension: structured_event_value145    type: number146    sql: ${TABLE}.se_value147  - dimension: user_agent148    sql: ${TABLE}.useragent149    hidden: true150  - dimension: page_height151    type: int152    sql: ${TABLE}.doc_height

dating-platform, activities.view.lookml, lines 147-149

Note: Ratios return NULL instead of zero

Source-defined

Measures such as page_pings_per_session wrap the numerator in NULLIF(..., 0). Sessions with no pings show an empty cell, not 0. Drill sets also reference sessions.*, source.* and domain_session_index, which do not exist here.

Source: activities.view.lookml L218-233

dating_platform/activities.view.lookmlL218-233 Show context

215    type: count_distinct216    sql: ${session_id}217    detail: detail*218  - measure: page_pings_per_session219    type: number220    decimals: 2221    sql: NULLIF(${page_pings_count},0)/NULLIF(${sessions_count},0)::REAL222  - measure: page_pings_per_visitor223    type: number224    decimals: 2225    sql: NULLIF(${page_pings_count},0)/NULLIF(${visitors_count},0)::REAL226  - measure: transactions_per_session227    type: number228    decimals: 3229    sql: NULLIF(${transactions_count},0)/NULLIF(${sessions_count},0)::REAL230  - measure: transactions_per_visitor231    type: number232    decimals: 2233    sql: NULLIF(${transactions_count},0)/NULLIF(${visitors_count},0)::REAL234  - measure: visitors_count235    type: count_distinct236    sql: ${user_id}

dating-platform, activities.view.lookml, lines 218-233

Parser checks 4

Source-defined

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

Show all 4 checks

name-collision 1
- activities.view.lookml:64 events.timestamp is declared as both dimension and dimension_group.
set-missing 1
- activities.view.lookml:214 events.sessions_count drills into set "detail", which is not declared.
set-member-missing 2
- activities.view.lookml:296 Set events.visit_detail lists "domain_session_index", which is not a field or set in this view.
- activities.view.lookml:322 Set events.transaction_detail lists "domain_session_index", which is not a field or set in this view.

At a glance

Shape

Dimensions 48

Measures 21

Relationships

Filters and exclusions that change meaning

Events without a user id are excluded

Worth noticing

Landing page views are inferred from the referer

Usage time counts active 10-second buckets

What can change the answer 4

Caution: Seconds measures are computed from minutes

Caution: NULL referers are not landing views

Caution: user_agent reads a column the derived table does not select

Note: Ratios return NULL instead of zero

Parser checks 4

Related

Guides built on this file

Related source files

Source model