Acquisition and traffic quality

How this LookML project measures acquisition quality

Which channels, referers and landing pages bring sessions that engage, rather than sessions that bounce?

Based on this implementation

dating-platform: Product, revenue and customer analytics. LookML, Redshift SQL

3 source files

Code excerpts are short and verbatim. SQL is translated from them and has not been run against a warehouse.

Trust key

Trust: Source-defined: Directly observable in the repo.
Trust: Translated: Generated from the source definitions automatically; not run against a warehouse.
Trust: Inferred: Interpretation based on names, usage or structure.
Trust: Suggested: Not in the source. An alternative or extension.
Trust: Missing: Needed to establish correctness but not in the repo.

See the full dating-platform source model

Short answer

Inferred

Compare channels on engagement, not on session counts. This project maps raw referers to five channels and ranks landing pages by bounce rate and events per session side by side. Watch the catch-all: anything it does not recognise is labelled direct, so broken tracking looks like brand traffic.

Decision it supports: Where to spend acquisition budget and which landing pages to fix.

What to measure

Sessions

Source-defined

Distinct pairs of visitor cookie and session index.

Defined as sessions.count

COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx)Trust: Translated

Show the SQL: Sessions

Redshift SQLTranslated

COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx)

Translated to Redshift SQL from user_sessions.view.lookml L105-106, L285-288. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L285-288

dating_platform/user_sessions.view.lookmlL285-288 Show context

282    sql: ${TABLE}.br_features_windowsmedia283  - dimension: browser_supports_cookies284    sql: ${TABLE}.br_cookies285  - measure: count286    type: count_distinct287    sql: ${session_id}288    detail: individual_detail*289  - measure: visitors_count290    type: count_distinct291    sql: ${user_id}

dating-platform, user_sessions.view.lookml, lines 285-288

Bounce rate

Source-defined

Share of sessions with exactly one event.

Defined as sessions.bounce_rate

COUNT(DISTINCT CASE WHEN sessions.number_of_events = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REALTrust: Translated

Show the SQL: Bounce rate

Redshift SQLTranslated

COUNT(DISTINCT CASE WHEN sessions.number_of_events = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL

Translated to Redshift SQL from user_sessions.view.lookml L105-106, L163-165, L285-288, L294-303. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L300-303

dating_platform/user_sessions.view.lookmlL300-303 Show context

297    filters:298      bounce: true299    detail: detail*300  - measure: bounce_rate301    type: number302    decimals: 2303    sql: ${bounced_sessions_count}/NULLIF(${count},0)::REAL304  - measure: sessions_from_new_visitors_count305    type: count_distinct306    sql: ${session_id}

dating-platform, user_sessions.view.lookml, lines 300-303

Events per session

Source-defined

All events divided by sessions, a rough measure of depth.

Defined as sessions.events_per_session

SUM(sessions.number_of_events)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REALTrust: Translated

Show the SQL: Events per session

Redshift SQLTranslated

SUM(sessions.number_of_events)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL

Translated to Redshift SQL from user_sessions.view.lookml L105-106, L285-288, L322-328. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L325-328

dating_platform/user_sessions.view.lookmlL325-328 Show context

322  - measure: events_count323    type: sum324    sql: ${TABLE}.number_of_events325  - measure: events_per_session326    type: number327    decimals: 2328    sql: ${events_count}/NULLIF(${count},0)::REAL329  - measure: events_per_visitor330    type: number331    decimals: 2

dating-platform, user_sessions.view.lookml, lines 325-328

Channel

Source-defined

The raw referer medium mapped to email, search, social, other website or direct.

Defined as sessions.referer_medium

Show the SQL: Channel

Redshift SQLTranslated

CASE  WHEN sessions.refr_medium = 'email' THEN 'email'  WHEN sessions.refr_medium = 'search' THEN 'search'  WHEN sessions.refr_medium = 'social' THEN 'social'  WHEN sessions.refr_medium = 'unknown' THEN 'other_website'  ELSE 'direct'END

Translated to Redshift SQL from user_sessions.view.lookml L218-224. Not run against a warehouse.

Show the original LookML: 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

Quality score per channel

Suggested

Combining bounce rate and depth into one score is a choice the project does not make.

Direct traffic

Inferred

Anything not email, search, social or unknown is labelled direct, including a NULL referer medium.

The query

Engagement by channel

Suggested

Sessions, bounce rate and events per session for each channel, built from the project's own fields. No dashboard tile asks exactly this, so the field combination is ours.

Show the SQL: engagement by channel

Redshift SQLSuggested

SELECT  CASE    WHEN sessions.refr_medium = 'email' THEN 'email'    WHEN sessions.refr_medium = 'search' THEN 'search'    WHEN sessions.refr_medium = 'social' THEN 'social'    WHEN sessions.refr_medium = 'unknown' THEN 'other_website'    ELSE 'direct'  END AS referer_medium,  COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) AS count,  COUNT(DISTINCT CASE WHEN sessions.number_of_events = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL AS bounce_rate,  SUM(sessions.number_of_events)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL AS events_per_sessionFROM sessions  -- derived table, defined in the sourceGROUP BY 1ORDER BY count DESC

Suggested query. Fields translated to Redshift SQL from event_tracker.model.lookml L13-16 and user_sessions.view.lookml L105-106, L163-165, L218-224, L285-288, L294-303, L322-328. Not run against a warehouse.

The data behind it

Inferred

Entities: session, visitor, landing page, referer
Grain: One row per visitor cookie and session index in sessions.
Time: The start dimension group on session start.
Key fields: sessions.referer_mediumsessions.referer_url_hostsessions.campaign_sourcesessions.landing_page_pathsessions.bouncesessions.events_during_session

What can change the answer 3

Caution: The session building blocks are missing

Source-defined

Sessions are assembled from four derived tables that are not in the repo, so landing page and referer logic cannot be checked.

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: Direct is a catch-all

Source-defined

The else branch labels every unmatched medium as direct, including NULL and any new medium the tracker adds.

What goes wrong: Direct traffic grows whenever tracking breaks, which reads as brand strength.

Show the SQL: referer medium

Redshift SQLTranslated

CASE  WHEN sessions.refr_medium = 'email' THEN 'email'  WHEN sessions.refr_medium = 'search' THEN 'search'  WHEN sessions.refr_medium = 'social' THEN 'social'  WHEN sessions.refr_medium = 'unknown' THEN 'other_website'  ELSE 'direct'END

Translated to Redshift SQL from user_sessions.view.lookml L218-224. Not run against a warehouse.

Show the original LookML: 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

Note: Bounce means one event

Inferred

A one-page visit with page pings is not a bounce, while a page view with no other event is. Bounce depends on which events the tracker sends.

Show the SQL: bounce

Redshift SQLTranslated

CASE WHEN sessions.number_of_events = 1 THEN 'yes' ELSE 'no' END

Translated to Redshift SQL from user_sessions.view.lookml L163-165. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L163-165

dating_platform/user_sessions.view.lookmlL163-165 Show context

160    - 1000161    - 10000162    sql: ${TABLE}.number_of_events163  - dimension: bounce164    type: yesno165    sql: ${TABLE}.number_of_events = 1166  - dimension: new_vs_returning_visitor167    sql_case:168      new: ${TABLE}.domain_sessionidx = 1

dating-platform, user_sessions.view.lookml, lines 163-165

How the real implementation does it

sessions

Source-defined

Maps the raw referer medium to a short channel list with a CASE. Every unmatched value, NULL included, becomes direct.

Show the SQL: channel mapping

Redshift SQLTranslated

CASE  WHEN sessions.refr_medium = 'email' THEN 'email'  WHEN sessions.refr_medium = 'search' THEN 'search'  WHEN sessions.refr_medium = 'social' THEN 'social'  WHEN sessions.refr_medium = 'unknown' THEN 'other_website'  ELSE 'direct'END

Translated to Redshift SQL from user_sessions.view.lookml L218-224. Not run against a warehouse.

Show the original LookML: 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

sessions

Source-defined

A session bounces when it has exactly one event. Bounce rate divides distinct bounced sessions by distinct sessions.

Show the SQL: bounce flag

Redshift SQLTranslated

CASE WHEN sessions.number_of_events = 1 THEN 'yes' ELSE 'no' END

Translated to Redshift SQL from user_sessions.view.lookml L163-165. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L163-165

dating_platform/user_sessions.view.lookmlL163-165 Show context

160    - 1000161    - 10000162    sql: ${TABLE}.number_of_events163  - dimension: bounce164    type: yesno165    sql: ${TABLE}.number_of_events = 1166  - dimension: new_vs_returning_visitor167    sql_case:168      new: ${TABLE}.domain_sessionidx = 1

dating-platform, user_sessions.view.lookml, lines 163-165

traffic_pulse

Source-defined

Ranks the top 10 landing pages by sessions, with bounce rate and events per session side by side.

Show the SQL: engagement by landing page

Redshift SQLTranslated

SELECT  sessions.landing_page_path AS landing_page_path,  COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) AS count,  COUNT(DISTINCT CASE WHEN sessions.number_of_events = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL AS bounce_rate,  SUM(sessions.number_of_events)/NULLIF(COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx),0)::REAL AS events_per_sessionFROM sessions  -- derived table, defined in the sourceGROUP BY 1ORDER BY count DESCLIMIT 10

On the dashboard this tile also follows its filters (date, referer_medium, new_vs_returning, country, landing_page, referer_host, app_id). They are left out here.

Translated to Redshift SQL from traffic_overview.dashboard.lookml L228-262, event_tracker.model.lookml L13-16 and user_sessions.view.lookml L105-106, L163-165, L207-209, L285-288, L294-303, L322-328. Not run against a warehouse.

Show the original LookML: traffic_overview.dashboard.lookml L228-250

dating_platform/traffic_overview.dashboard.lookmlL228-250 Show context

225    y_axis_min: null226    y_axis_max: null227    hide_points: true228  - name: sessions_and_engagement_levels_for_most_popular_landing_pages229    title: Sessions count and events per session for top 10 landing pages230    type: looker_column231    explore: sessions232    dimensions:233    - sessions.landing_page_path234    measures:235    - sessions.count236    - sessions.bounce_rate237    - sessions.events_per_session238    listen:239      date: sessions.start_date240      referer_medium: sessions.referer_medium241      new_vs_returning: sessions.new_vs_returning_visitor242      country: sessions.geography_country243      landing_page: sessions.landing_page_path244      referer_host: sessions.referer_url_host245      app_id: sessions.app_id246    sorts:247    - sessions.count desc248    limit: 10249    width: 6250    height: 4251    legend_align: null252    hide_legend: null253    stacking: null

dating-platform, traffic_overview.dashboard.lookml, lines 228-250

visitors

Source-defined

Keeps first-session source and landing page per visitor, for first-touch attribution.

Source: guests.view.lookml L23-30

dating_platform/guests.view.lookmlL23-30 Show context

20        s.refr_term,21        s.refr_urlhost,22        s.refr_urlpath23      FROM24        ${TABLE}.visitors_basic AS v25        LEFT JOIN ${TABLE}.sessions_landing_page AS l26        ON v.domain_userid = l.domain_userid27        AND l.domain_sessionidx = 128        LEFT JOIN ${TABLE}.sessions_source AS s29        ON v.domain_userid = s.domain_userid30        AND s.domain_sessionidx = 131    sql_trigger_value: SELECT MAX(collector_tstamp) FROM ${TABLE}.events_atomic32    distkey: domain_userid33    sortkeys:

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

Other ways to measure it

Attribute by first touch

Suggested

Use visitors instead of sessions when the question is which channel acquired the visitor, not which channel brought this visit.

Show the SQL: visitors by first-touch channel

Redshift SQLSuggested

SELECT  CASE    WHEN visitors.refr_medium = 'email' THEN 'email'    WHEN visitors.refr_medium = 'search' THEN 'search'    WHEN visitors.refr_medium = 'social' THEN 'social'    WHEN visitors.refr_medium = 'unknown' THEN 'other_website'    ELSE 'direct'  END AS referer_medium,  COUNT(DISTINCT visitors.domain_userid) AS count,  COUNT(DISTINCT CASE WHEN visitors.number_of_events = 1 THEN visitors.domain_userid END)/NULLIF(COUNT(DISTINCT visitors.domain_userid),0)::REAL AS bounce_rate,  SUM(visitors.number_of_sessions)/NULLIF(COUNT(DISTINCT visitors.domain_userid),0)::REAL AS sessions_per_visitorFROM visitors  -- derived table, defined in the sourceGROUP BY 1ORDER BY count DESC

Suggested query. Fields translated to Redshift SQL from event_tracker.model.lookml L17 and guests.view.lookml L39-40, L75-77, L118-124, L141-154, L162-169. Not run against a warehouse.

Count engaged sessions explicitly

Suggested

Write the quality rule down once, so every report counts engaged sessions the same way.

Show the suggested SQL: engaged sessions by channel

Suggested, not from source

WITH s AS (  SELECT    domain_userid || '-' || domain_sessionidx AS session_id,    number_of_events,    CASE refr_medium      WHEN 'email' THEN 'email'      WHEN 'search' THEN 'search'      WHEN 'social' THEN 'social'      WHEN 'unknown' THEN 'other_website'      ELSE 'direct'    END AS channel  FROM sessions  -- the derived table defined in the source)SELECT  channel,  COUNT(DISTINCT session_id) AS sessions,  COUNT(DISTINCT CASE WHEN number_of_events >= 5 THEN session_id END) AS engaged_sessionsFROM sGROUP BY channel

Short answer

What to measure

Sessions

Bounce rate

Events per session

Channel

Quality score per channel

Direct traffic

The query

Engagement by channel

The data behind it

What can change the answer 3

Caution: The session building blocks are missing

Caution: Direct is a catch-all

Note: Bounce means one event

How the real implementation does it

sessions

sessions

traffic_pulse

visitors

Other ways to measure it

Attribute by first touch

Count engaged sessions explicitly

Related questions