Campaign Affinity

-- models/campaign_affinity.sql
{{ config(materialized='table') }}

SELECT
campaign_a ,
campaign_b ,
prop.gender,
prop.age_group,
joint_frequency ,
top1.campaign_frequency AS campaign_a_frequency ,
top2.campaign_frequency AS campaign_b_frequency
FROM
(
  SELECT
      op1.mts_program_id AS campaign_a_id,
      op1.name AS campaign_a ,
      op1.gender AS gender,
      op1.age_group as age_group,
      op2.mts_program_id AS campaign_b_id,
      op2.name AS campaign_b ,
      COUNT(*) AS joint_frequency
  FROM
      {{ ref('session_campaigns') }} AS op1
  INNER JOIN {{ ref('session_campaigns') }} AS op2
  ON  op1.session_id = op2.session_id
  GROUP BY
      1,2,3,4,5,6
) AS prop
INNER JOIN {{ ref('campaign_session_frequency') }} AS top1 ON prop.campaign_a_id = top1.mts_program_id and prop.gender = top1.gender and prop.age_group = top1.age_group
INNER JOIN {{ ref('campaign_session_frequency') }} AS top2 ON prop.campaign_b_id = top2.mts_program_id and prop.gender = top2.gender and prop.age_group = top2.age_group
WHERE campaign_a <> campaign_b
ORDER BY
3 DESC

# models/schema.yml
version: 2

models:
  - name: campaign_affinity
    columns:
      - name: campaign_a
      - name: campaign_b
      - name: gender
      - name: age_group

Assumptions

• Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
campaign_a ,
campaign_b ,
prop.gender,
prop.age_group,
joint_frequency ,
top1.campaign_frequency AS campaign_a_frequency ,
top2.campaign_frequency AS campaign_b_frequency
FROM
(
  SELECT
      op1.mts_program_id AS campaign_a_id,
      op1.name AS campaign_a ,
      op1.gender AS gender,
      op1.age_group as age_group,
      op2.mts_program_id AS campaign_b_id,
      op2.name AS campaign_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${session_campaigns.SQL_TABLE_NAME} AS op1
  INNER JOIN ${session_campaigns.SQL_TABLE_NAME} AS op2
  ON  op1.session_id = op2.session_id
  GROUP BY
      1,2,3,4,5,6
) AS prop
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top1 ON prop.campaign_a_id = top1.mts_program_id and prop.gender = top1.gender and prop.age_group = top1.age_group
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top2 ON prop.campaign_b_id = top2.mts_program_id and prop.gender = top2.gender and prop.age_group = top2.age_group
WHERE campaign_a <> campaign_b
ORDER BY
3 DESC

view: campaign_affinity {
  derived_table: {
    sql: SELECT
campaign_a ,
campaign_b ,
prop.gender,
prop.age_group,
joint_frequency ,
top1.campaign_frequency AS campaign_a_frequency ,
top2.campaign_frequency AS campaign_b_frequency
FROM
(
  SELECT
      op1.mts_program_id AS campaign_a_id,
      op1.name AS campaign_a ,
      op1.gender AS gender,
      op1.age_group as age_group,
      op2.mts_program_id AS campaign_b_id,
      op2.name AS campaign_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${session_campaigns.SQL_TABLE_NAME} AS op1
  INNER JOIN ${session_campaigns.SQL_TABLE_NAME} AS op2
  ON  op1.session_id = op2.session_id
  GROUP BY
      1,2,3,4,5,6
) AS prop
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top1 ON prop.campaign_a_id = top1.mts_program_id and prop.gender = top1.gender and prop.age_group = top1.age_group
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top2 ON prop.campaign_b_id = top2.mts_program_id and prop.gender = top2.gender and prop.age_group = top2.age_group
WHERE campaign_a <> campaign_b
ORDER BY
3 DESC ;;
    persist_for: "24 hours"
  }

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

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

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

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

  measure: joint_frequency {
    type: sum
    sql: ${TABLE}.joint_frequency ;;
  }

  measure: campaign_a_frequency {
    type: sum
    sql: ${TABLE}.campaign_a_frequency ;;
  }

  measure: campaign_b_frequency {
    type: sum
    sql: ${TABLE}.campaign_b_frequency ;;
  }

  measure: affinity {
    type: number
    sql: 100 * (${joint_frequency}/(${campaign_a_frequency} + ${campaign_b_frequency} - ${joint_frequency})) ;;
  }
}

- view: campaign_affinity
  derived_table:
    sql: SELECT
campaign_a ,
campaign_b ,
prop.gender,
prop.age_group,
joint_frequency ,
top1.campaign_frequency AS campaign_a_frequency ,
top2.campaign_frequency AS campaign_b_frequency
FROM
(
  SELECT
      op1.mts_program_id AS campaign_a_id,
      op1.name AS campaign_a ,
      op1.gender AS gender,
      op1.age_group as age_group,
      op2.mts_program_id AS campaign_b_id,
      op2.name AS campaign_b ,
      COUNT(*) AS joint_frequency
  FROM
      ${session_campaigns.SQL_TABLE_NAME} AS op1
  INNER JOIN ${session_campaigns.SQL_TABLE_NAME} AS op2
  ON  op1.session_id = op2.session_id
  GROUP BY
      1,2,3,4,5,6
) AS prop
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top1 ON prop.campaign_a_id = top1.mts_program_id and prop.gender = top1.gender and prop.age_group = top1.age_group
INNER JOIN ${campaign_session_frequency.SQL_TABLE_NAME} AS top2 ON prop.campaign_b_id = top2.mts_program_id and prop.gender = top2.gender and prop.age_group = top2.age_group
WHERE campaign_a <> campaign_b
ORDER BY
3 DESC
    persist_for: 24 hours
  fields:
  - dimension: campaign_a
    sql: ${TABLE}.campaign_a
  - dimension: campaign_b
    sql: ${TABLE}.campaign_b
  - dimension: gender
    sql: ${TABLE}.gender
  - dimension: age_group
    sql: ${TABLE}.age_group
  - measure: joint_frequency
    type: sum
    sql: ${TABLE}.joint_frequency
  - measure: campaign_a_frequency
    type: sum
    sql: ${TABLE}.campaign_a_frequency
  - measure: campaign_b_frequency
    type: sum
    sql: ${TABLE}.campaign_b_frequency
  - measure: affinity
    type: number
    sql: 100 * (${joint_frequency}/(${campaign_a_frequency} + ${campaign_b_frequency} - ${joint_frequency}))