DataBull Search
dating_platform/guests.view.lookml 26 dims15 measures 4 warnings Repo files

LookML view · dating-platform

How this LookML visitors view models first-touch attribution and lifetime engagement

At a glance

Does
One row per cookie visitor with first and last touch, lifetime events, pages and sessions, plus the landing page and referer of the first session. Measures cover visitors, bounce rate, sessions per visitor and distinct campaign and referer counts.Trust: Source-defined
Why
First-touch acquisition and cohort reporting: which source brought a visitor in and how much they used the site afterwards. The traffic dashboard cohort tile reads it.Trust: Inferred
Breaks
4 warnings. Top: The bounce filter uses filter, not filters
Grain
One row per domain_userid from visitors_basic. user_id should be unique, but no primary key is declared.Trust: Inferred
Source
dating_platform/guests.view.lookml 255 lines
Source: guests.view.lookml L1-10
dating_platform/guests.view.lookmlL1-10
- view: visitors  derived_table:    sql: |      SELECT        v.app_id,        v.domain_userid,        v.first_touch,        v.last_touch,        v.number_of_events,        v.distinct_pages_viewed,        v.number_of_sessions,        l.page_urlhost,        l.page_urlpath,

dating-platform, guests.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 26
NameTypeSQLUsed byTags
app_idL37string${TABLE}.app_id-
user_idL39string${TABLE}.domain_userid-
first_touchL41string${TABLE}.first_touchCohort analysisname reused
first_touchtime, date, week, monthL43time group${TABLE}.first_touch-name reused
last_touchL51time${TABLE}.last_touch-
events_during_lifetimeL59int${TABLE}.number_of_events-
events_during_lifetime_tieredL62tier${TABLE}.number_of_events-
bounceL75yesno${TABLE}.number_of_events = 1-derived
distinct_pages_viewedL78int${TABLE}.distinct_pages_viewed-
distinct_pages_viewed_tieredL81tier${distinct_pages_viewed}-derived
number_of_sessionsL93int${TABLE}.number_of_sessions-
number_of_sessions_tieredL96tier${number_of_sessions}-derived
session_streamL108string${user_id}-derived
event_streamL110string${user_id}-derived
landing_page_url_hostL112string${TABLE}.page_urlhost-
landing_page_url_pathL114string${TABLE}.page_urlpath-
landing_page_urlL116string${TABLE}.page_urlhost || ${TABLE}.page_urlpath-derived
referer_mediumL118string
email: ${TABLE}.refr_medium = 'email'; search: ${TABL...email: ${TABLE}.refr_medium = 'email'; search: ${TABLE}.refr_medium = 'search'; social: ${TABLE}.refr_medium = 'social'; other_website: ${TABLE}.refr_medium = 'unknown'; else: direct
-
referer_sourceL125string${TABLE}.refr_source-
referer_termL127string${TABLE}.refr_term-
referer_url_hostL129string${TABLE}.refr_urlhost-
referer_url_pathL131string${TABLE}.refr_urlpath-
campaign_mediumL133string${TABLE}.mkt_medium-
campaign_sourceL135string${TABLE}.mkt_source-
campaign_termL137string${TABLE}.mkt_term-
campaign_nameL139string${TABLE}.mkt_campaign-

Measures 15
NameTypeSQLUsed byTags
countL141count_distinct${user_id}Cohort analysisderived
bounced_visitor_countL145count_distinct${user_id}-derivedfiltered
bounce_rateL151number${bounced_visitor_count}/NULLIF(${count},0)::REAL-derived
events_countL155sum${TABLE}.number_of_events-
events_per_visitorL158number${events_count}/NULLIF(${count},0)::REAL-derived
sessions_countL162sum${TABLE}.number_of_sessions-
sessions_per_visitorL166number${sessions_count}/NULLIF(${count},0)::REAL-derived
landing_page_countL170count_distinct${landing_page_url}-derived
campaign_medium_countL176count_distinct${campaign_medium}-derived
campaign_source_countL182count_distinct${campaign_source}-derived
campaign_term_countL189count_distinct${campaign_term}-derived
campaign_countL197count_distinct${campaign_name}-derived
referer_medium_countL205count_distinct${referer_medium}-derived
referer_source_countL211count_distinct${referer_source}-derived
referer_term_countL218count_distinct${referer_term}-derived

Relationships

Trust: Source-defined
Relationships of visitors
Relationships of visitors. Text version follows the diagram.This viewUsed by: explores, views, dashboardsbase viewas visitors, on events.domain_userid = visitors...as visitors, on sessions.domain_userid = visito...1 tile visitors explore in event_tracker events explore in event_tracker sessions explore in event_tracker traffic_pulse dashboard
  • This Block
  • No relationship declared
Text version
  • visitors is the base view of visitors: base view
  • visitors is joined into events: as visitors, on events.domain_userid = visitors... (no relationship declared)
  • visitors is joined into sessions: as visitors, on sessions.domain_userid = visito... (no relationship declared)
  • visitors is read by traffic_pulse: 1 tile

Worth noticing

First touch is the first session's landing page and source

Trust: Source-defined

The derived table joins sessions_landing_page and sessions_source on domain_sessionidx = 1. Attribution follows the cookie's first session, not the first paid touch.

Source: guests.view.lookml L23-30
dating_platform/guests.view.lookmlL23-30
        s.refr_term,        s.refr_urlhost,        s.refr_urlpath      FROM        ${TABLE}.visitors_basic AS v        LEFT JOIN ${TABLE}.sessions_landing_page AS l        ON v.domain_userid = l.domain_userid        AND l.domain_sessionidx = 1        LEFT JOIN ${TABLE}.sessions_source AS s        ON v.domain_userid = s.domain_userid        AND s.domain_sessionidx = 1    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic    distkey: domain_userid    sortkeys:

dating-platform, guests.view.lookml, lines 23-30

Lifetime sessions are a stored column

Trust: Source-defined

sessions_count sums number_of_sessions from visitors_basic instead of counting sessions, so it reflects whatever window built that table.

Source: guests.view.lookml L162-165
dating_platform/guests.view.lookmlL162-165
    type: number    decimals: 2    sql: ${events_count}/NULLIF(${count},0)::REAL  - measure: sessions_count    type: sum    sql: ${TABLE}.number_of_sessions    detail: details*  - measure: sessions_per_visitor    type: number    decimals: 2

dating-platform, guests.view.lookml, lines 162-165

What can change the answer 4

Caution: The bounce filter uses filter, not filters

Trust: Inferred

bounced_visitor_count declares filter: bounce: true. The measure parameter is filters, so the condition is likely ignored.

What goes wrong: Bounced visitors equals all visitors and bounce_rate reads close to 1.0 on every row.

Read the guide: Filters that change metric meaning

Source: guests.view.lookml L145-150
dating_platform/guests.view.lookmlL145-150
    type: count_distinct    sql: ${user_id}    detail: individual_detail*  - measure: bounced_visitor_count    type: count_distinct    sql: ${user_id}    filter:      bounce: true    detail: detail*  - measure: bounce_rate    type: number    decimals: 2

dating-platform, guests.view.lookml, lines 145-150

Caution: The tables behind first touch are not in the repo

Trust: Missing

${TABLE}.visitors_basic, sessions_landing_page and sessions_source are read through ${TABLE} as if it were a schema. None is defined here.

What goes wrong: First-touch source cannot be checked against session logic, and a change upstream silently changes attribution.

Read the guide: Missing tables and stale references

Source: guests.view.lookml L23-31
dating_platform/guests.view.lookmlL23-31
        s.refr_term,        s.refr_urlhost,        s.refr_urlpath      FROM        ${TABLE}.visitors_basic AS v        LEFT JOIN ${TABLE}.sessions_landing_page AS l        ON v.domain_userid = l.domain_userid        AND l.domain_sessionidx = 1        LEFT JOIN ${TABLE}.sessions_source AS s        ON v.domain_userid = s.domain_userid        AND s.domain_sessionidx = 1    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic    distkey: domain_userid    sortkeys:    - domain_userid

dating-platform, guests.view.lookml, lines 23-31

Note: last_touch is a dimension with type: time

Trust: Source-defined

last_touch lists timeframes on a plain dimension, while first_touch is declared both as a dimension and a dimension group.

Source: guests.view.lookml L41-58
dating_platform/guests.view.lookmlL41-58
    sql: ${TABLE}.app_id  - dimension: user_id    sql: ${TABLE}.domain_userid  - dimension: first_touch    sql: ${TABLE}.first_touch  - dimension_group: first_touch    type: time    timeframes:    - time    - date    - week    - month    sql: ${TABLE}.first_touch  - dimension: last_touch    type: time    timeframes:    - time    - date    - week    - month    sql: ${TABLE}.last_touch  - dimension: events_during_lifetime    type: int    sql: ${TABLE}.number_of_events

dating-platform, guests.view.lookml, lines 41-58

Note: No primary key and a missing drill set

Trust: Source-defined

There is no primary_key, though user_id is the natural one. sessions_count drills into details*, which is not declared.

Read the guide: Primary keys and counts

Source: guests.view.lookml L39-40
dating_platform/guests.view.lookmlL39-40
  fields:  - dimension: app_id    sql: ${TABLE}.app_id  - dimension: user_id    sql: ${TABLE}.domain_userid  - dimension: first_touch    sql: ${TABLE}.first_touch  - dimension_group: first_touch

dating-platform, guests.view.lookml, lines 39-40

Parser checks 7

Trust: Source-defined

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

Show all 7 checks
  • no-primary-key 1
    • guests.view.lookml:1 visitors declares no primary_key.
  • name-collision 1
    • guests.view.lookml:43 visitors.first_touch is declared as both dimension and dimension_group.
  • time-dimension 1
    • guests.view.lookml:51 visitors.last_touch uses type: time on a dimension instead of a dimension_group.
  • unknown-param 1
    • guests.view.lookml:145 visitors.bounced_visitor_count uses "filter:" (singular), which is not the measure filters parameter.
  • set-missing 1
    • guests.view.lookml:162 visitors.sessions_count drills into set "details", which is not declared.
  • set-member-missing 2
    • guests.view.lookml:239 Set visitors.individual_detail lists "referer_host", which is not a field or set in this view.
    • guests.view.lookml:239 Set visitors.individual_detail lists "number_of_events", which is not a field or set in this view.

Related

Guides built on this file

Guide How this LookML project measures acquisition quality Which channels bring visitors who engage? How a real product compares bounce rate and events per session by channel and landing page, with the SQL. Acquisition Guide Filters that quietly change what a metric means Why two reports disagree on one metric: hidden default filters, tile filters and unwired dashboard filters, with a checklist and real examples. Best practicesAcquisitionRetention Guide Checking the primary key before you trust a count A count is only as clear as the key behind it. How to state and test a table's grain, with real tables whose keys repeat, merge rows or are missing. Best practicesRevenueRetention 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 sessions One derived table that stitches session basics, geo, landing page, exit page, source and technology into a ... 2 views4 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 traffic_pulse Twelve tiles on the sessions explore: new vs returning split, sessions by referer medium, country and landi... dashboard3 warnings

Source model

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