SQL Insurance vehicle

Campaign Touches

Campaign Touches model for insurance vehicle.

source-preserved

Blocks

dbt SQL LKML LookML

campaign_touches.sql · 63 lines

inferred

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

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  mkt_medium,
  mkt_source,
  mkt_term,
  mkt_campaign,
  mkt_content
FROM events_data
WHERE mkt_medium IS NOT NULL
AND page_urlhost = '<% domain_placeholder %>' -- we are only interested in marketing touches to rentalcover.com, not ads that drive users to partner websites

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'partner partner_company_logo' AS mkt_medium,
  page_urlhost AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE se_category = 'partner_company_logo'
AND se_action = 'impression'

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'email' AS mkt_medium,
  NULL AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE page_title='<% email_template_placeholder %>'

# models/schema.yml
version: 2

models:
  - name: campaign_touches
    columns:
      - name: user_id
      - name: session_index
      - name: session_id
      - name: timestamp
      - name: mkt_medium
      - name: mkt_source
      - name: mkt_term
      - name: mkt_campaign
      - name: mkt_content

Assumptions

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.

campaign_touches.sql · 44 lines

source-preserved validation: not checked

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  mkt_medium,
  mkt_source,
  mkt_term,
  mkt_campaign,
  mkt_content
FROM events_data
WHERE mkt_medium IS NOT NULL
AND page_urlhost = '<% domain_placeholder %>' -- we are only interested in marketing touches to rentalcover.com, not ads that drive users to partner websites

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'partner partner_company_logo' AS mkt_medium,
  page_urlhost AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE se_category = 'partner_company_logo'
AND se_action = 'impression'

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'email' AS mkt_medium,
  NULL AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE page_title='<% email_template_placeholder %>'

campaign_touches.view.lkml · 100 lines

inferred

view: campaign_touches {
  derived_table: {
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  mkt_medium,
  mkt_source,
  mkt_term,
  mkt_campaign,
  mkt_content
FROM events_data
WHERE mkt_medium IS NOT NULL
AND page_urlhost = '<% domain_placeholder %>' -- we are only interested in marketing touches to rentalcover.com, not ads that drive users to partner websites

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'partner partner_company_logo' AS mkt_medium,
  page_urlhost AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE se_category = 'partner_company_logo'
AND se_action = 'impression'

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'email' AS mkt_medium,
  NULL AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE page_title='<% email_template_placeholder %>' ;;
  }

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

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

  dimension: session_id {
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx ;;
  }

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

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

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

  dimension: mkt_term {
    sql: ${TABLE}.mkt_term ;;
  }

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

  dimension: mkt_content {
    sql: ${TABLE}.mkt_content ;;
  }

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

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

  measure: count {
    type: count
  }
}

campaign_touches.view.lookml · 80 lines

source-preserved

- view: campaign_touches
  derived_table:
    sql: SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  mkt_medium,
  mkt_source,
  mkt_term,
  mkt_campaign,
  mkt_content
FROM events_data
WHERE mkt_medium IS NOT NULL
AND page_urlhost = '<% domain_placeholder %>' -- we are only interested in marketing touches to rentalcover.com, not ads that drive users to partner websites

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'partner partner_company_logo' AS mkt_medium,
  page_urlhost AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE se_category = 'partner_company_logo'
AND se_action = 'impression'

UNION ALL

SELECT
  domain_userid,
  domain_sessionidx,
  collector_tstamp,
  event_id,
  'email' AS mkt_medium,
  NULL AS mkt_source,
  NULL AS mkt_term,
  NULL AS mkt_campaign,
  NULL AS mkt_content
FROM events_data
WHERE page_title='<% email_template_placeholder %>'
  fields:
  - dimension: user_id
    sql: ${TABLE}.domain_userid
  - dimension: session_index
    type: int
    sql: ${TABLE}.domain_sessionidx
  - dimension: session_id
    sql: ${TABLE}.domain_userid || '-' || ${TABLE}.domain_sessionidx
  - dimension: timestamp
    sql: ${TABLE}.collector_tstamp
  - dimension: mkt_medium
    sql: ${TABLE}.mkt_medium
  - dimension: mkt_source
    sql: ${TABLE}.mkt_source
  - dimension: mkt_term
    sql: ${TABLE}.mkt_term
  - dimension: mkt_campaign
    sql: ${TABLE}.mkt_campaign
  - dimension: mkt_content
    sql: ${TABLE}.mkt_content
  - dimension_group: timestamp
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}.collector_tstamp
  - measure: count_distinct_users
    type: count_distinct
    sql: ${user_id}
  - measure: count
    type: count

campaign_touches.sql
campaign_touches.sql
campaign_touches.view.lkml
campaign_touches.view.lookml

Campaign Touches

Blocks

Assumptions

Contents