DataBull Search
3 pitfalls

Retention and engagement

How this LookML project separates new and returning visitors

What share of sessions comes from returning visitors, and do weekly cohorts come back?

Short answer

Trust: Inferred

Count people, not sessions. This project calls a session new when its session index is 1, and its visitor share divides sessions by sessions, so a heavy user counts many times. Use distinct visitors for the share, and remember a visitor here is a cookie: cleared cookies and new devices look like new visitors.

Decision it supports: Whether acquisition is bringing people who return, or only first visits.

What to measure

New or returning

Trust: Source-defined

A session is new when its session index is 1, returning when it is higher.

Defined as sessions.new_vs_returning_visitor

Show the SQL: New or returning
Redshift SQLTrust: Translated
CASE  WHEN sessions.domain_sessionidx = 1 THEN 'new'  WHEN sessions.domain_sessionidx > 1 THEN 'returning'  ELSE 'unknown'END

Translated to Redshift SQL from user_sessions.view.lookml L166-170. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L166-170
dating_platform/user_sessions.view.lookmlL166-170
  - dimension: bounce    type: yesno    sql: ${TABLE}.number_of_events = 1  - dimension: new_vs_returning_visitor    sql_case:      new: ${TABLE}.domain_sessionidx = 1      returning: ${TABLE}.domain_sessionidx > 1      else: unknown  - dimension: distinct_pages_viewed    sql: ${TABLE}.distinct_pages_viewed  - dimension: distinct_pages_viewed_tiered

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

Sessions from new visitors

Trust: Source-defined

Sessions with index 1.

Defined as sessions.sessions_from_new_visitors_count

COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)Trust: Translated

Show the SQL: Sessions from new visitors
Redshift SQLTrust: Translated
COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)

Translated to Redshift SQL from user_sessions.view.lookml L102-106, L304-309. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L304-309
dating_platform/user_sessions.view.lookmlL304-309
    type: number    decimals: 2    sql: ${bounced_sessions_count}/NULLIF(${count},0)::REAL  - measure: sessions_from_new_visitors_count    type: count_distinct    sql: ${session_id}    filters:      session_index: 1    detail: individual_detail*  - measure: sessions_from_returning_visitor_count    type: number    sql: ${count} - ${sessions_from_new_visitors_count}

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

Sessions from returning visitors

Trust: Source-defined

All sessions minus new ones.

Defined as sessions.sessions_from_returning_visitor_count

COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) - COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)Trust: Translated

Show the SQL: Sessions from returning visitors
Redshift SQLTrust: Translated
COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) - COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)

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

Show the original LookML: user_sessions.view.lookml L310-313
dating_platform/user_sessions.view.lookmlL310-313
    filters:      session_index: 1    detail: individual_detail*  - measure: sessions_from_returning_visitor_count    type: number    sql: ${count} - ${sessions_from_new_visitors_count}    detail: individual_detail*  - measure: new_visitors_count_over_total_visitors_count    type: number    decimals: 2

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

New visitor share, as named

Trust: Source-defined

Named as a visitor share, computed as a share of sessions.

Defined as sessions.new_visitors_count_over_total_visitors_count

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

Show the SQL: New visitor share, as named
Redshift SQLTrust: Translated
COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 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 L102-106, L285-288, L304-309, L314-317. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L314-317
dating_platform/user_sessions.view.lookmlL314-317
    type: number    sql: ${count} - ${sessions_from_new_visitors_count}    detail: individual_detail*  - measure: new_visitors_count_over_total_visitors_count    type: number    decimals: 2    sql: ${sessions_from_new_visitors_count}/NULLIF(${count},0)::REAL  - measure: returning_visitors_count_over_total_visitors_count    type: number    decimals: 2

dating-platform, user_sessions.view.lookml, lines 314-317

Visitors

Trust: Source-defined

Distinct visitor cookies.

Defined as visitors.count

COUNT(DISTINCT visitors.domain_userid)Trust: Translated

Show the SQL: Visitors
Redshift SQLTrust: Translated
COUNT(DISTINCT visitors.domain_userid)

Translated to Redshift SQL from guests.view.lookml L39-40, L141-144. Not run against a warehouse.

Show the original LookML: guests.view.lookml L141-144
dating_platform/guests.view.lookmlL141-144
    sql: ${TABLE}.mkt_term  - dimension: campaign_name    sql: ${TABLE}.mkt_campaign  - measure: count    type: count_distinct    sql: ${user_id}    detail: individual_detail*  - measure: bounced_visitor_count    type: count_distinct    sql: ${user_id}

dating-platform, guests.view.lookml, lines 141-144

Returning visitor rate

Trust: Inferred

A visitor-level rate needs distinct returning visitors over distinct visitors. The source only has session shares.

Cohort retention

Trust: Suggested

The cohort tile shows visitor counts per first-touch week and session week. A retention percentage is not in the source.

The query

New and returning sessions by day

Trust: Translated

The dashboard's daily tile: sessions per day, split into new and returning. The pivot comes back as rows.

Show the SQL: new and returning sessions by day
Redshift SQLTrust: Translated
SELECT  CAST(sessions.session_start_ts AS DATE) AS start_date,  CASE    WHEN sessions.domain_sessionidx = 1 THEN 'new'    WHEN sessions.domain_sessionidx > 1 THEN 'returning'    ELSE 'unknown'  END AS new_vs_returning_visitor,  COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) AS countFROM sessions  -- derived table, defined in the sourceGROUP BY 1, 2LIMIT 500
  • 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 L156-190, event_tracker.model.lookml L13-16 and user_sessions.view.lookml L105-106, L122-129, L166-170, L285-288. Not run against a warehouse.

Show the original LookML: traffic_overview.dashboard.lookml L156-172
dating_platform/traffic_overview.dashboard.lookmlL156-172
    width: 6    height: 4    legend_align: null  - name: new_vs_returning_visitors_by_day    title: New vs returning visitors    type: looker_area    explore: sessions    dimensions:    - sessions.start_date    pivots:    - sessions.new_vs_returning_visitor    measures:    - sessions.count    filters: null    listen:      date: sessions.start_date      referer_medium: sessions.referer_medium      new_vs_returning: sessions.new_vs_returning_visitor      country: sessions.geography_country      landing_page: sessions.landing_page_path      referer_host: sessions.referer_url_host      app_id: sessions.app_id    limit: 500

dating-platform, traffic_overview.dashboard.lookml, lines 156-172

The data behind it

Trust: Inferred
Entities
session, visitor (cookie)
Grain
One row per session. Visitors are cookies (domain_userid).
Time
Session start week and visitor first-touch week.
Key fields
sessions.new_vs_returning_visitorsessions.session_indexsessions.session_index_tiervisitors.first_touch_week

What can change the answer 3

Caution: A visitor is a cookie

Trust: Inferred

domain_userid is a first-party cookie. Cleared cookies, new devices and private windows all start again at session 1.

What goes wrong: The new visitor share is overstated and the returning share understated.

Show the SQL: user id
Redshift SQLTrust: Translated
sessions.domain_userid

Translated to Redshift SQL from user_sessions.view.lookml L100-101. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L100-101
dating_platform/user_sessions.view.lookmlL100-101
  - dimension: app_id    type: string    sql: ${TABLE}.app_id  - dimension: user_id    sql: ${TABLE}.domain_userid  - dimension: session_index    type: int    sql: ${TABLE}.domain_sessionidx

dating-platform, user_sessions.view.lookml, lines 100-101

Caution: Session shares named as visitor shares

Trust: Source-defined

new_visitors_count_over_total_visitors_count divides sessions by sessions. A visitor with five sessions counts five times.

Show the SQL: new visitors count over total visitors count
Redshift SQLTrust: Translated
COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 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 L102-106, L285-288, L304-309, L314-317. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L314-317
dating_platform/user_sessions.view.lookmlL314-317
    type: number    sql: ${count} - ${sessions_from_new_visitors_count}    detail: individual_detail*  - measure: new_visitors_count_over_total_visitors_count    type: number    decimals: 2    sql: ${sessions_from_new_visitors_count}/NULLIF(${count},0)::REAL  - measure: returning_visitors_count_over_total_visitors_count    type: number    decimals: 2

dating-platform, user_sessions.view.lookml, lines 314-317

Caution: Cohorts cut by a session date

Trust: Inferred

The date filter applies to session start, so older cohorts only show visitors with sessions in the window.

Read the guide: Filters that change metric meaning

Source: traffic_overview.dashboard.lookml L263-283
dating_platform/traffic_overview.dashboard.lookmlL263-283
    y_axis_labels: null    y_axis_min: null    y_axis_max: null  - name: cohort_analysis    title: Cohort analysis    type: table    explore: sessions    dimensions:    - visitors.first_touch_week    pivots:    - sessions.start_week    measures:    - visitors.count    listen:      date: sessions.start_date      referer_medium: sessions.referer_medium      new_vs_returning: sessions.new_vs_returning_visitor      country: sessions.geography_country      landing_page: sessions.landing_page_path      referer_host: sessions.referer_url_host      app_id: sessions.app_id    limit: 500    width: null    height: null  - name: session_count_and_engagement_level_by_referer_host    title: Session count and events per sesion for top 10 referer hosts    type: looker_column

dating-platform, traffic_overview.dashboard.lookml, lines 263-283

How the real implementation does it

sessions

Trust: Source-defined

New vs returning is decided by the session index alone.

Show the SQL: new or returning
Redshift SQLTrust: Translated
CASE  WHEN sessions.domain_sessionidx = 1 THEN 'new'  WHEN sessions.domain_sessionidx > 1 THEN 'returning'  ELSE 'unknown'END

Translated to Redshift SQL from user_sessions.view.lookml L166-170. Not run against a warehouse.

Show the original LookML: user_sessions.view.lookml L166-170
dating_platform/user_sessions.view.lookmlL166-170
  - dimension: bounce    type: yesno    sql: ${TABLE}.number_of_events = 1  - dimension: new_vs_returning_visitor    sql_case:      new: ${TABLE}.domain_sessionidx = 1      returning: ${TABLE}.domain_sessionidx > 1      else: unknown  - dimension: distinct_pages_viewed    sql: ${TABLE}.distinct_pages_viewed  - dimension: distinct_pages_viewed_tiered

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

sessions

Trust: Source-defined

Returning sessions are derived by subtraction, not counted directly.

Show the SQL: returning sessions
Redshift SQLTrust: Translated
COUNT(DISTINCT sessions.domain_userid || '-' || sessions.domain_sessionidx) - COUNT(DISTINCT CASE WHEN sessions.domain_sessionidx = 1 THEN sessions.domain_userid || '-' || sessions.domain_sessionidx END)

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

Show the original LookML: user_sessions.view.lookml L310-313
dating_platform/user_sessions.view.lookmlL310-313
    filters:      session_index: 1    detail: individual_detail*  - measure: sessions_from_returning_visitor_count    type: number    sql: ${count} - ${sessions_from_new_visitors_count}    detail: individual_detail*  - measure: new_visitors_count_over_total_visitors_count    type: number    decimals: 2

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

traffic_pulse

Trust: Source-defined

The cohort table pivots session week against visitor first-touch week.

Show the SQL: weekly cohorts
Redshift SQLTrust: Translated
SELECT  DATE_TRUNC('week', visitors.first_touch) AS first_touch_week,  DATE_TRUNC('week', sessions.session_start_ts) AS start_week,  COUNT(DISTINCT visitors.domain_userid) AS countFROM sessions  -- derived table, defined in the sourceLEFT JOIN visitors  -- derived table, defined in the source  ON sessions.domain_userid = visitors.domain_useridGROUP BY 1, 2LIMIT 500
  • 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 L263-283, event_tracker.model.lookml L13-16, guests.view.lookml L39-40, L43-50, L141-144 and user_sessions.view.lookml L122-129. Not run against a warehouse.

Show the original LookML: traffic_overview.dashboard.lookml L263-283
dating_platform/traffic_overview.dashboard.lookmlL263-283
    y_axis_labels: null    y_axis_min: null    y_axis_max: null  - name: cohort_analysis    title: Cohort analysis    type: table    explore: sessions    dimensions:    - visitors.first_touch_week    pivots:    - sessions.start_week    measures:    - visitors.count    listen:      date: sessions.start_date      referer_medium: sessions.referer_medium      new_vs_returning: sessions.new_vs_returning_visitor      country: sessions.geography_country      landing_page: sessions.landing_page_path      referer_host: sessions.referer_url_host      app_id: sessions.app_id    limit: 500    width: null    height: null  - name: session_count_and_engagement_level_by_referer_host    title: Session count and events per sesion for top 10 referer hosts    type: looker_column

dating-platform, traffic_overview.dashboard.lookml, lines 263-283

Other ways to measure it

Count visitors, not sessions

Trust: Suggested

Distinct visitors with a session index above 1, over distinct visitors, gives a true returning share for the period.

Show the suggested SQL: returning visitor share
Suggested, not from source
SELECT  COUNT(DISTINCT CASE WHEN domain_sessionidx > 1 THEN domain_userid END) AS returning_visitors,  COUNT(DISTINCT domain_userid) AS visitors,  1.0 * COUNT(DISTINCT CASE WHEN domain_sessionidx > 1 THEN domain_userid END)    / NULLIF(COUNT(DISTINCT domain_userid), 0) AS returning_visitor_shareFROM sessions  -- the derived table defined in the source

Use a member id after signup

Trust: Suggested

Map cookies to member ids once people sign up, so returning is measured per person rather than per browser.