Campaigns

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

SELECT
s.app_id,
s.domain_userid,
s.domain_sessionidx,
s.session_start_ts,

-- Fetch source fields from paid table if they exist there, otherwise fall back to the organic table
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_source WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_source END AS mkt_source,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_medium WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_medium  END AS mkt_medium,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_campaign ELSE o.refr_urlhost END AS mkt_campaign

FROM {{ ref('session_list_calc') }} s
LEFT JOIN {{ ref('paid_campaign_list_calc') }} p
ON s.app_id = p.app_id
AND s.domain_userid = p.domain_userid
AND s.domain_sessionidx = p.domain_sessionidx
LEFT JOIN {{ ref('organic_referer_calc') }} o
ON s.app_id = o.app_id
AND s.domain_userid = o.domain_userid
AND s.domain_sessionidx = o.domain_sessionidx

# models/schema.yml
version: 2

models:
  - name: campaigns
    columns:
      - name: application_id
      - name: user_id
      - name: session_index
      - name: timestamp
      - name: source
      - name: medium
      - name: name
      - name: retargeted

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.

SELECT
s.app_id,
s.domain_userid,
s.domain_sessionidx,
s.session_start_ts,

-- Fetch source fields from paid table if they exist there, otherwise fall back to the organic table
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_source WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_source END AS mkt_source,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_medium WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_medium  END AS mkt_medium,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_campaign ELSE o.refr_urlhost END AS mkt_campaign

FROM ${session_list_calc.SQL_TABLE_NAME} s
LEFT JOIN ${paid_campaign_list_calc.SQL_TABLE_NAME} p
ON s.app_id = p.app_id
AND s.domain_userid = p.domain_userid
AND s.domain_sessionidx = p.domain_sessionidx
LEFT JOIN ${organic_referer_calc.SQL_TABLE_NAME} o
ON s.app_id = o.app_id
AND s.domain_userid = o.domain_userid
AND s.domain_sessionidx = o.domain_sessionidx

view: campaigns {
  derived_table: {
    sql: SELECT
s.app_id,
s.domain_userid,
s.domain_sessionidx,
s.session_start_ts,

-- Fetch source fields from paid table if they exist there, otherwise fall back to the organic table
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_source WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_source END AS mkt_source,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_medium WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_medium  END AS mkt_medium,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_campaign ELSE o.refr_urlhost END AS mkt_campaign

FROM ${session_list_calc.SQL_TABLE_NAME} s
LEFT JOIN ${paid_campaign_list_calc.SQL_TABLE_NAME} p
ON s.app_id = p.app_id
AND s.domain_userid = p.domain_userid
AND s.domain_sessionidx = p.domain_sessionidx
LEFT JOIN ${organic_referer_calc.SQL_TABLE_NAME} o
ON s.app_id = o.app_id
AND s.domain_userid = o.domain_userid
AND s.domain_sessionidx = o.domain_sessionidx ;;
  }

  dimension: application_id {
    sql: ${TABLE}.app_id ;;
  }

  dimension: user_id {
    sql: ${TABLE}.domain_userid ;;
  }

  dimension: session_index {
    sql: ${TABLE}.domain_sessionidx ;;
  }

  dimension: timestamp {
    sql: ${TABLE}.session_start_ts ;;
  }

  dimension: source {
    sql: ${TABLE}.mkt_source ;;
  }

  dimension: medium {
    sql: ${TABLE}.mkt_medium ;;
  }

  dimension: name {
    sql: ${TABLE}.mkt_campaign ;;
  }

  dimension: retargeted {
    type: yesno
    sql: (${name} LIKE 'basicretargeting') OR (${medium} LIKE 'Dynamic Retargeting') ;;
  }

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

  measure: visitors_count {
    type: count_distinct
    sql: ${user_id} ;;
  }

  measure: sessions_count {
    type: count_distinct
    sql: ${user_id} || ${session_index} ;;
  }

  measure: sources_count {
    type: count_distinct
    sql: ${source} ;;
  }

  measure: medium_count {
    type: count_distinct
    sql: ${medium} ;;
  }

  measure: name_count {
    type: count_distinct
    sql: ${name} ;;
  }
}

- view: campaigns
  derived_table:
    sql: SELECT
s.app_id,
s.domain_userid,
s.domain_sessionidx,
s.session_start_ts,

-- Fetch source fields from paid table if they exist there, otherwise fall back to the organic table
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_source WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_source END AS mkt_source,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_medium WHEN o.refr_medium = 'unknown' THEN 'Other website' ELSE o.refr_medium  END AS mkt_medium,
CASE WHEN p.mkt_source IS NOT NULL THEN p.mkt_campaign ELSE o.refr_urlhost END AS mkt_campaign

FROM ${session_list_calc.SQL_TABLE_NAME} s
LEFT JOIN ${paid_campaign_list_calc.SQL_TABLE_NAME} p
ON s.app_id = p.app_id
AND s.domain_userid = p.domain_userid
AND s.domain_sessionidx = p.domain_sessionidx
LEFT JOIN ${organic_referer_calc.SQL_TABLE_NAME} o
ON s.app_id = o.app_id
AND s.domain_userid = o.domain_userid
AND s.domain_sessionidx = o.domain_sessionidx
  fields:
  - dimension: application_id
    sql: ${TABLE}.app_id
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_index
    sql: ${TABLE}.domain_sessionidx
  - dimension: timestamp
    sql: ${TABLE}.session_start_ts
  - dimension: source
    sql: ${TABLE}.mkt_source
  - dimension: medium
    sql: ${TABLE}.mkt_medium
  - dimension: name
    sql: ${TABLE}.mkt_campaign
  - dimension: retargeted
    type: yesno
    sql: (${name} LIKE 'basicretargeting') OR (${medium} LIKE 'Dynamic Retargeting')
  - dimension_group: timestamp
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.session_start_ts
  - measure: visitors_count
    type: count_distinct
    sql: ${user_id}
  - measure: sessions_count
    type: count_distinct
    sql: ${user_id} || ${session_index}
  - measure: sources_count
    type: count_distinct
    sql: ${source}
  - measure: medium_count
    type: count_distinct
    sql: ${medium}
  - measure: name_count
    type: count_distinct
    sql: ${name}