dbt Media engagement

Session Facts

Session Facts model for media engagement.

inferred

Blocks

dbt SQL LKML LookML

session_facts.sql · 27 lines

inferred

-- models/session_facts.sql
{{ config(materialized='view') }}

SELECT s.session_id
  , LEAST(MAX(em.e_tstamp) + INTERVAL '30 minutes', min(s.next_session_start)) AS ended_at
  , count(distinct em.event_id) AS num_ems
  -- , count(case when em.event = 'view_buy_page' then event_id else null end) as cnt_view_buy_page
  -- , count(case when em.event = 'added_item' then event_id else null end) as cnt_added_item
  -- , count(case when em.event = 'tapped_shipit' then event_id else null end) as cnt_shipit
  -- , count(case when em.event = 'made_purchase' then event_id else null end) as cnt_made_purchase
FROM {{ ref('sessions') }} as s
LEFT JOIN {{ ref('event_session_map') }} as map using(session_id)
LEFT JOIN {{ ref('events_mapped_to_user') }} as em using(event_id)
group by 1
-- limit 10

# models/schema.yml
version: 2

models:
  - name: session_facts
    columns:
      - name: session_id
        tests:
          - unique
          - not_null
      - name: number_events

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
materialized='view' is the default; no persist hint found.
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

session_facts.sql · 12 lines

source-preserved validation: not checked

SELECT s.session_id
  , LEAST(MAX(em.e_tstamp) + INTERVAL '30 minutes', min(s.next_session_start)) AS ended_at
  , count(distinct em.event_id) AS num_ems
  -- , count(case when em.event = 'view_buy_page' then event_id else null end) as cnt_view_buy_page
  -- , count(case when em.event = 'added_item' then event_id else null end) as cnt_added_item
  -- , count(case when em.event = 'tapped_shipit' then event_id else null end) as cnt_shipit
  -- , count(case when em.event = 'made_purchase' then event_id else null end) as cnt_made_purchase
FROM ${sessions.SQL_TABLE_NAME} as s
LEFT JOIN ${event_session_map.SQL_TABLE_NAME} as map using(session_id)
LEFT JOIN ${events_mapped_to_user.SQL_TABLE_NAME} as em using(event_id)
group by 1
-- limit 10

session_facts.view.lkml · 38 lines

inferred

view: session_facts {
  derived_table: {
    sql: SELECT s.session_id
  , LEAST(MAX(em.e_tstamp) + INTERVAL '30 minutes', min(s.next_session_start)) AS ended_at
  , count(distinct em.event_id) AS num_ems
  -- , count(case when em.event = 'view_buy_page' then event_id else null end) as cnt_view_buy_page
  -- , count(case when em.event = 'added_item' then event_id else null end) as cnt_added_item
  -- , count(case when em.event = 'tapped_shipit' then event_id else null end) as cnt_shipit
  -- , count(case when em.event = 'made_purchase' then event_id else null end) as cnt_made_purchase
FROM ${sessions.SQL_TABLE_NAME} as s
LEFT JOIN ${event_session_map.SQL_TABLE_NAME} as map using(session_id)
LEFT JOIN ${events_mapped_to_user.SQL_TABLE_NAME} as em using(event_id)
group by 1
-- limit 10 ;;
  }

  dimension: session_id {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.session_id ;;
  }

  dimension: number_events {
    type: number
    sql: ${TABLE}.num_ems ;;
  }

  dimension_group: ended_at {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.ended_at ;;
  }

  measure: count {
    type: count
    drill_fields: [drill_fields*]
  }
}

session_facts.view.lookml · 34 lines

source-preserved

- view: session_facts
  derived_table:
    sql: SELECT s.session_id
  , LEAST(MAX(em.e_tstamp) + INTERVAL '30 minutes', min(s.next_session_start)) AS ended_at
  , count(distinct em.event_id) AS num_ems
  -- , count(case when em.event = 'view_buy_page' then event_id else null end) as cnt_view_buy_page
  -- , count(case when em.event = 'added_item' then event_id else null end) as cnt_added_item
  -- , count(case when em.event = 'tapped_shipit' then event_id else null end) as cnt_shipit
  -- , count(case when em.event = 'made_purchase' then event_id else null end) as cnt_made_purchase
FROM ${sessions.SQL_TABLE_NAME} as s
LEFT JOIN ${event_session_map.SQL_TABLE_NAME} as map using(session_id)
LEFT JOIN ${events_mapped_to_user.SQL_TABLE_NAME} as em using(event_id)
group by 1
-- limit 10
  fields:
  - dimension: session_id
    primary_key: true
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: number_events
    type: number
    sql: ${TABLE}.num_ems
  - dimension_group: ended_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.ended_at
  - measure: count
    type: count
    drill_fields:
    - drill_fields*

session_facts.sql
session_facts.sql
session_facts.view.lkml
session_facts.view.lookml

Session Facts

Blocks

Assumptions

Contents