LookML view · dating-platform

How this LookML sessions view models acquisition, engagement and new vs returning traffic

At a glance

Does: One derived table that stitches session basics, geo, landing page, exit page, source and technology into a wide session row. It defines sessions.count, bounce_rate, new and returning session counts, events per session, and distinct counts of geos, campaigns, referers and devices.Trust: Source-defined
Why: The backbone of web acquisition reporting: where sessions come from, whether they bounce and whether visitors return. Every tile on the traffic dashboard reads it.Trust: Inferred
Breaks: 4 warnings. Top: Four upstream session tables are not in the repo
Grain: One row per session, keyed by session_id built as domain_userid || '-' || domain_sessionidx. No primary key is declared.Trust: Inferred
Source: dating_platform/user_sessions.view.lookml 469 lines

Source: user_sessions.view.lookml L1-10

dating_platform/user_sessions.view.lookmlL1-10 Show context

1- view: session_apps2  derived_table:3    sql: |4      SELECT  DISTINCT s.app_id5      FROM    ${sessions.SQL_TABLE_NAME}6    persist_for: 1 days7    distkey: app_id8    sortkeys:9    - app_id10  fields:11  - dimension: app_id12    sql: ${TABLE}.app_id13- view: sessions

dating-platform, user_sessions.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.

`session_apps` user_sessions.view.lookml

Derived table: persists for 1 days. Builds on sessions

Dimensions 1

Name	Type	SQL	Used by	Tags
`app_id`L11	string	`${TABLE}.app_id`	-

`sessions` user_sessions.view.lookml

Derived table: rebuilt on a SQL trigger. Builds on sessions_basic (not in repo) sessions_geo (not in repo) sessions_last_page (not in repo) sessions_technology (not in repo)

Dimensions 62

Name	Type	SQL	Used by	Tags
`app_id`L97	string	`${TABLE}.app_id`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`user_id`L100	string	`${TABLE}.domain_userid`	-
`session_index`L102	int	`${TABLE}.domain_sessionidx`	-
`session_id`L105	string	`${TABLE}.domain_userid \|\| '-' \|\| ${TABLE}.domain_sessionidx`	-	derived
`session_index_tier`L107	tier	`${session_index}`	Split in number of sessions by number of previous visits	derived
`start`L120	string	`${TABLE}.session_start_ts`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session	name reused
`start`time, date, week, monthL122	time group	`${TABLE}.session_start_ts`	-	name reused
`end`L130	string	`${TABLE}.session_end_ts`	-
`session_duration_seconds`L132	int	`EXTRACT(EPOCH FROM (${TABLE}.session_end_ts - ${TABLE...` `EXTRACT(EPOCH FROM (${TABLE}.session_end_ts - ${TABLE}.session_start_ts))`	-	derived
`session_duration_seconds_tiered`L135	tier	`${session_duration_seconds}`	-	derived
`events_during_session`L147	int	`${TABLE}.number_of_events`	-
`events_during_session_tiered`L150	tier	`${TABLE}.number_of_events`	Split in number of sessions by number of events per session
`bounce`L163	yesno	`${TABLE}.number_of_events = 1`	-	derived
`new_vs_returning_visitor`L166	string	`new: ${TABLE}.domain_sessionidx = 1; returning: ${TAB...` `new: ${TABLE}.domain_sessionidx = 1; returning: ${TABLE}.domain_sessionidx > 1; else: unknown`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`distinct_pages_viewed`L171	string	`${TABLE}.distinct_pages_viewed`	-
`distinct_pages_viewed_tiered`L173	tier	`${TABLE}.distinct_pages_viewed`	-
`event_stream`L186	string	`${session_id}`	-	derived
`geography_country`L188	string	`${TABLE}.geo_country`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`geography_country_three_letter_iso_code`L190	string	`${TABLE}.geo_country_code_3_characters`	Sessions count by country
`geography_country_two_letter_iso_code`L192	string	`${TABLE}.geo_country_code_2_characters`	-
`geography_region`L194	string	`${TABLE}.geo_region`	-
`geography_city`L196	string	`${TABLE}.geo_city`	-
`geography_zipcode`L198	string	`${TABLE}.geo_zipcode`	-
`geography_latitude`L200	string	`${TABLE}.geo_latitude`	-
`geography_longitude`L202	string	`${TABLE}.geo_longitude`	-
`landing_page_host`L204	string	`BTRIM(${TABLE}.landing_page_host)`	-	derived
`landing_page_path`L207	string	`${TABLE}.landing_page_path`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`landing_page`L210	string	`${TABLE}.landing_page_host \|\| ${TABLE}.landing_page_path`	-	derived
`exit_page_host`L212	string	`${TABLE}.exit_page_host`	-
`exit_page_path`L214	string	`${TABLE}.exit_page_path`	-
`exit_page`L216	string	`${TABLE}.exit_page_host \|\| ${TABLE}.exit_page_path`	-	derived
`referer_medium`L218	string	`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`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`referer_source`L225	string	`${TABLE}.refr_source`	-
`referer_term`L227	string	`${TABLE}.refr_term`	-
`referer_url_host`L229	string	`${TABLE}.refr_urlhost`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Cohort analysis Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session
`referer_url_path`L231	string	`${TABLE}.refr_urlpath`	-
`campaign_medium`L233	string	`${TABLE}.mkt_medium`	-
`campaign_source`L235	string	`${TABLE}.mkt_source`	-
`campaign_term`L237	string	`${TABLE}.mkt_term`	-
`campaign_name`L239	string	`${TABLE}.mkt_campaign`	-
`device_type`L241	string	`${TABLE}.dvce_type`	-
`device_is_mobile`L243	string	`${TABLE}.dvce_ismobile`	-
`device_screen_width`L245	string	`${TABLE}.dvce_screenwidth`	-
`device_screen_height`L247	string	`${TABLE}.dvce_screenheight`	-
`operating_system`L249	string	`${TABLE}.os_name`	-
`operating_system_family`L251	string	`${TABLE}.os_family`	-
`operating_system_manufacturer`L253	string	`${TABLE}.os_manufacturer`	-
`browser`L255	string	`${TABLE}.br_name`	-
`browser_version`L257	string	`${TABLE}.br_version`	-
`browser_type`L259	string	`${TABLE}.br_type`	-
`browser_renderengine`L261	string	`${TABLE}.br_renderengine`	-
`browser_language`L263	string	`${TABLE}.br_lang`	-
`browser_has_director_plugin`L265	string	`${TABLE}.br_features_director`	-
`browser_has_flash_plugin`L267	string	`${TABLE}.br_features_flash`	-
`browser_has_gears_plugin`L269	string	`${TABLE}.br_features_gears`	-
`browser_has_java_plugin`L271	string	`${TABLE}.br_features_java`	-
`browser_has_pdf_plugin`L273	string	`${TABLE}.br_features_pdf`	-
`browser_has_quicktime_plugin`L275	string	`${TABLE}.br_features_quicktime`	-
`browser_has_realplayer_plugin`L277	string	`${TABLE}.br_features_realplayer`	-
`browser_has_silverlight_plugin`L279	string	`${TABLE}.br_features_silverlight`	-
`browser_has_windowsmedia_plugin`L281	string	`${TABLE}.br_features_windowsmedia`	-
`browser_supports_cookies`L283	string	`${TABLE}.br_cookies`	-

Measures 25

Name	Type	SQL	Used by	Tags
`count`L285	count_distinct	`${session_id}`	New vs returning sessions count Sessions count by referer medium Sessions count by country Sessions count by landing page Sessions count by country New vs returning visitors Sessions by day by referer medium Sessions count and events per session for top 10 landing pages Session count and events per sesion for top 10 referer hosts Split in number of sessions by number of previous visits Split in number of sessions by number of events per session	derived
`visitors_count`L289	count_distinct	`${user_id}`	-	hiddenderived
`bounced_sessions_count`L294	count_distinct	`${session_id}`filters: `bounce = true`	-	derivedfiltered
`bounce_rate`L300	number	`${bounced_sessions_count}/NULLIF(${count},0)::REAL`	Sessions count and events per session for top 10 landing pages Session count and events per sesion for top 10 referer hosts	derived
`sessions_from_new_visitors_count`L304	count_distinct	`${session_id}`filters: `session_index = 1`	-	derivedfiltered
`sessions_from_returning_visitor_count`L310	number	`${count} - ${sessions_from_new_visitors_count}`	-	derived
`new_visitors_count_over_total_visitors_count`L314	number	`${sessions_from_new_visitors_count}/NULLIF(${count},0)::REAL`	-	derived
`returning_visitors_count_over_total_visitors_count`L318	number	`${sessions_from_returning_visitor_count}/NULLIF(${cou...` `${sessions_from_returning_visitor_count}/NULLIF(${count},0)::REAL`	-	derived
`events_count`L322	sum	`${TABLE}.number_of_events`	-
`events_per_session`L325	number	`${events_count}/NULLIF(${count},0)::REAL`	Sessions count by country Sessions count and events per session for top 10 landing pages Session count and events per sesion for top 10 referer hosts	derived
`events_per_visitor`L329	number	`${events_count}/NULLIF(${visitors_count},0)::REAL`	-	derived
`country_count`L333	count_distinct	`${geography_country}`	-	derived
`region_count`L339	count_distinct	`${geography_region}`	-	derived
`city_count`L346	count_distinct	`${geography_city}`	-	derived
`zip_code_count`L354	count_distinct	`${geography_zipcode}`	-	derived
`campaign_medium_count`L363	count_distinct	`${campaign_medium}`	-	derived
`campaign_source_count`L369	count_distinct	`${campaign_source}`	-	derived
`campaign_term_count`L376	count_distinct	`${campaign_term}`	-	derived
`campaign_count`L384	count_distinct	`${campaign_name}`	-	derived
`referer_medium_count`L393	count_distinct	`${referer_medium}`	-	derived
`referer_source_count`L399	count_distinct	`${referer_source}`	-	derived
`referer_term_count`L406	count_distinct	`${referer_term}`	-	derived
`device_count`L414	count_distinct	`${device_type}`	-	derived
`operating_system_count`L418	count_distinct	`${operating_system}`	-	derived
`browser_count`L424	count_distinct	`${browser}`	-	derived

Relationships

Source-defined

Relationships of session_apps, sessions

This Block
No relationship declared
Not in the repo

Text version

sessions feeds session_apps
session_apps is the base view of session_apps: base view
sessions_basic (not in repo) feeds sessions (no relationship declared)
sessions_geo (not in repo) feeds sessions (no relationship declared)
sessions_last_page (not in repo) feeds sessions (no relationship declared)
sessions_technology (not in repo) feeds sessions (no relationship declared)
sessions is the base view of sessions: base view
sessions is joined into events: as sessions, on events.domain_userid = sessions... (no relationship declared)
sessions is read by traffic_pulse: 12 tiles

Worth noticing

New vs returning comes from the cookie session index

Source-defined

new_vs_returning_visitor is new when domain_sessionidx = 1 and returning when it is above 1. The index is per cookie, so a cleared cookie or a second device counts as new.

Source: user_sessions.view.lookml L166-170

dating_platform/user_sessions.view.lookmlL166-170 Show context

163  - dimension: bounce164    type: yesno165    sql: ${TABLE}.number_of_events = 1166  - dimension: new_vs_returning_visitor167    sql_case:168      new: ${TABLE}.domain_sessionidx = 1169      returning: ${TABLE}.domain_sessionidx > 1170      else: unknown171  - dimension: distinct_pages_viewed172    sql: ${TABLE}.distinct_pages_viewed173  - dimension: distinct_pages_viewed_tiered

dating-platform, user_sessions.view.lookml, lines 166-170

Returning sessions are a subtraction

Source-defined

sessions_from_returning_visitor_count is sessions.count minus sessions_from_new_visitors_count. Sessions with a NULL index land in returning rather than unknown.

Source: user_sessions.view.lookml L304-313

dating_platform/user_sessions.view.lookmlL304-313 Show context

301    type: number302    decimals: 2303    sql: ${bounced_sessions_count}/NULLIF(${count},0)::REAL304  - measure: sessions_from_new_visitors_count305    type: count_distinct306    sql: ${session_id}307    filters:308      session_index: 1309    detail: individual_detail*310  - measure: sessions_from_returning_visitor_count311    type: number312    sql: ${count} - ${sessions_from_new_visitors_count}313    detail: individual_detail*314  - measure: new_visitors_count_over_total_visitors_count315    type: number316    decimals: 2

dating-platform, user_sessions.view.lookml, lines 304-313

Unknown referers become other_website, NULL becomes direct

Source-defined

referer_medium maps refr_medium = 'unknown' to other_website and sends every other value, including NULL and internal, to the else branch direct.

Source: user_sessions.view.lookml L218-224

dating_platform/user_sessions.view.lookmlL218-224 Show context

215    sql: ${TABLE}.exit_page_path216  - dimension: exit_page217    sql: ${TABLE}.exit_page_host || ${TABLE}.exit_page_path218  - dimension: referer_medium219    sql_case:220      email: ${TABLE}.refr_medium = 'email'221      search: ${TABLE}.refr_medium = 'search'222      social: ${TABLE}.refr_medium = 'social'223      other_website: ${TABLE}.refr_medium = 'unknown'224      else: direct225  - dimension: referer_source226    sql: ${TABLE}.refr_source227  - dimension: referer_term

dating-platform, user_sessions.view.lookml, lines 218-224

What can change the answer 4

Caution: Four upstream session tables are not in the repo

Missing

The derived table reads ${sessions_basic.SQL_TABLE_NAME}, sessions_geo, sessions_last_page and sessions_technology. None of these views is defined here, so the session logic itself cannot be reviewed.

What goes wrong: Bounce, duration and landing page all depend on rules you cannot see or test from this repo.

Read the guide: Missing tables and stale references

Source: user_sessions.view.lookml L69-89

dating_platform/user_sessions.view.lookmlL69-89 Show context

66        t.dvce_ismobile,67        t.dvce_screenwidth,68        t.dvce_screenheight69      FROM ${sessions_basic.SQL_TABLE_NAME} AS s70      LEFT JOIN ${sessions_geo.SQL_TABLE_NAME} AS g71        ON s.app_id = g.app_id AND72        s.domain_userid = g.domain_userid AND73        s.domain_sessionidx = g.domain_sessionidx74      LEFT JOIN ${TABLE}.sessions_landing_page AS l75        ON s.app_id = l.app_id AND76        s.domain_userid = l.domain_userid AND77        s.domain_sessionidx = l.domain_sessionidx78      LEFT JOIN ${sessions_last_page.SQL_TABLE_NAME} AS l279        ON s.app_id = l2.app_id AND80        s.domain_userid = l2.domain_userid AND81        s.domain_sessionidx = l2.domain_sessionidx82      LEFT JOIN ${TABLE}.sessions_source AS s283        ON s.app_id = s2.app_id AND84        s.domain_userid = s2.domain_userid AND85        s.domain_sessionidx = s2.domain_sessionidx86      LEFT JOIN ${sessions_technology.SQL_TABLE_NAME} AS t87        ON s.app_id = t.app_id AND88        s.domain_userid = t.domain_userid AND89        s.domain_sessionidx = t.domain_sessionidx90    sql_trigger_value: SELECT COUNT(*) FROM ${TABLE}.events_atomic91    distkey: domain_userid92    sortkeys:

dating-platform, user_sessions.view.lookml, lines 69-89

Caution: session_apps selects from an alias that does not exist

Source-defined

session_apps.app_id is built with SELECT DISTINCT s.app_id FROM ${sessions.SQL_TABLE_NAME}, but the FROM clause gives no alias s.

What goes wrong: The persisted table fails to build, so the session_apps explore returns an error rather than a list of apps.

Read the guide: Missing tables and stale references

Source: user_sessions.view.lookml L3-9

dating_platform/user_sessions.view.lookmlL3-9 Show context

1- view: session_apps2  derived_table:3    sql: |4      SELECT  DISTINCT s.app_id5      FROM    ${sessions.SQL_TABLE_NAME}6    persist_for: 1 days7    distkey: app_id8    sortkeys:9    - app_id10  fields:11  - dimension: app_id12    sql: ${TABLE}.app_id

dating-platform, user_sessions.view.lookml, lines 3-9

Caution: No primary key on a view that is joined

Source-defined

The events explore joins sessions on two keys, yet sessions declares no primary_key. Looker cannot apply symmetric aggregates to its sums.

What goes wrong: events_count summed through the events explore repeats each session total once per event.

Read the guide: Primary keys and counts

Source: user_sessions.view.lookml L105-106

dating_platform/user_sessions.view.lookmlL105-106 Show context

102  - dimension: session_index103    type: int104    sql: ${TABLE}.domain_sessionidx105  - dimension: session_id106    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx107  - dimension: session_index_tier108    type: tier109    tiers:

dating-platform, user_sessions.view.lookml, lines 105-106

Note: start is both a dimension and a dimension group

Source-defined

dimension: start and dimension_group: start share a name, and the detail set lists zipe_code_count and sessions_from_returning_visitors_count, which are not declared.

Source: user_sessions.view.lookml L120-129

dating_platform/user_sessions.view.lookmlL120-129 Show context

117    - 100118    - 1000119    sql: ${session_index}120  - dimension: start121    sql: ${TABLE}.session_start_ts122  - dimension_group: start123    type: time124    timeframes:125    - time126    - date127    - week128    - month129    sql: ${TABLE}.session_start_ts130  - dimension: end131    sql: ${TABLE}.session_end_ts132  - dimension: session_duration_seconds

dating-platform, user_sessions.view.lookml, lines 120-129

Parser checks 9

Source-defined

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

Show all 9 checks

no-primary-key 2
- user_sessions.view.lookml:1 session_apps declares no primary_key.
- user_sessions.view.lookml:13 sessions declares no primary_key.
missing-view 4
- user_sessions.view.lookml:15 sessions builds on ${sessions_basic.SQL_TABLE_NAME}; no view named sessions_basic is in the repo.
- user_sessions.view.lookml:15 sessions builds on ${sessions_geo.SQL_TABLE_NAME}; no view named sessions_geo is in the repo.
- user_sessions.view.lookml:15 sessions builds on ${sessions_last_page.SQL_TABLE_NAME}; no view named sessions_last_page is in the repo.
- user_sessions.view.lookml:15 sessions builds on ${sessions_technology.SQL_TABLE_NAME}; no view named sessions_technology is in the repo.
name-collision 1
- user_sessions.view.lookml:122 sessions.start is declared as both dimension and dimension_group.
set-member-missing 2
- user_sessions.view.lookml:431 Set sessions.detail lists "sessions_from_returning_visitors_count", which is not a field or set in this view.
- user_sessions.view.lookml:431 Set sessions.detail lists "zipe_code_count", which is not a field or set in this view.

At a glance

Shape

session_apps user_sessions.view.lookml

Dimensions 1

sessions user_sessions.view.lookml

Dimensions 62

Measures 25

Relationships

Worth noticing

New vs returning comes from the cookie session index

Returning sessions are a subtraction

Unknown referers become other_website, NULL becomes direct

What can change the answer 4

Caution: Four upstream session tables are not in the repo

Caution: session_apps selects from an alias that does not exist

Caution: No primary key on a view that is joined

Note: start is both a dimension and a dimension group

Parser checks 9

Related

Guides built on this file

Related source files

Source model

`session_apps` user_sessions.view.lookml

`sessions` user_sessions.view.lookml