Marketing Data Medium Summary

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

select
coalesce(date(m.date), date(u.registration_date)) as registration_date,
coalesce(lower(m.medium),lower(u.medium)) as medium,
coalesce(lower(m.source),lower(u.source)) as source,
sum(coalesce(m.spend,0)) as marketing_spend,
sum(coalesce(u.summed_promotion_spend,0)) as promotion_spend,
sum(u.registration_count) as registrations,
sum(u.summed_smv) as smv,
sum(u.summed_ltv) as ltv
from analytics_marketing_spend m
full outer join  SQL_TABLE_Nshift_aE u on date(m.date)=date(u.registration_date) and lower(m.medium)=lower(u.medium) and lower(m.source) = lower(u.source)
GROUP BY 1,2, 3

# models/schema.yml
version: 2

models:
  - name: marketing_data_medium_summary
    columns:
      - name: registration
      - name: medium
      - name: paid_channel
      - name: source
      - name: source_roll_up
      - name: daily_registration_count
      - name: summed_ltv
      - name: summed_smv
      - name: summed_promotion_spend
      - name: marketing_spend
      - name: dummy_pivot

Assumptions

• Some ${view.field} references were reduced to column names; verify they resolve in the dbt model's scope.
• 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
coalesce(date(m.date), date(u.registration_date)) as registration_date,
coalesce(lower(m.medium),lower(u.medium)) as medium,
coalesce(lower(m.source),lower(u.source)) as source,
sum(coalesce(m.spend,0)) as marketing_spend,
sum(coalesce(u.summed_promotion_spend,0)) as promotion_spend,
sum(u.registration_count) as registrations,
sum(u.summed_smv) as smv,
sum(u.summed_ltv) as ltv
from analytics_marketing_spend m
full outer join  ${marketing_data_facts_daily.SQL_TABLE_Nshift_aE} u on date(m.date)=date(u.registration_date) and lower(m.medium)=lower(u.medium) and lower(m.source) = lower(u.source)
GROUP BY 1,2, 3

view: marketing_data_medium_summary {
  derived_table: {
    sql: select
coalesce(date(m.date), date(u.registration_date)) as registration_date,
coalesce(lower(m.medium),lower(u.medium)) as medium,
coalesce(lower(m.source),lower(u.source)) as source,
sum(coalesce(m.spend,0)) as marketing_spend,
sum(coalesce(u.summed_promotion_spend,0)) as promotion_spend,
sum(u.registration_count) as registrations,
sum(u.summed_smv) as smv,
sum(u.summed_ltv) as ltv
from analytics_marketing_spend m
full outer join  ${marketing_data_facts_daily.SQL_TABLE_Nshift_aE} u on date(m.date)=date(u.registration_date) and lower(m.medium)=lower(u.medium) and lower(m.source) = lower(u.source)
GROUP BY 1,2, 3 ;;
    persist_for: "1 hour"
  }

  dimension: registration {
    type: time
    sql: ${TABLE}.registration_date ;;
  }

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

  dimension: paid_channel {
    sql: case when (lower(${medium}) similar to '%none%|%organic%|%referral%|%null%|%invitation%|%rss%|%gift%|%email%|%not set%') then 'Unpaid' else 'Paid' end ;;
  }

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

  dimension: source_roll_up {
    sql: CASE WHEN ${medium} = 'email' then 'all'
WHEN ${medium} IN ('social-paid', 'retargeting', 'search-paid') THEN ${source}
WHEN ${medium} = 'blog-paid' then 'all'
WHEN ${medium} ilike '%organic%' then 'all'
when ${medium} ilike 'dailydeal' then ${source}
WHEN ${medium} = 'display' AND ${source} IN ('neverblue', 'yahoo', 'quantcast', 'ebay', 'pandora', 'gdn', 'liveintent') THEN ${source}
WHEN ${medium} = 'display' THEN 'all other'
WHEN ${medium} = 'referral' and ${source} ilike '%facebook%' then 'facebook'
WHEN ${medium} = 'referral' THEN 'all'
WHEN ${medium} in ('Invitation','Gift') then ''
WHEN ${medium} = 'directmail' AND ${source} IN ('belardi', 'handybook') THEN lower(${source})
WHEN ${medium} = 'directmail' THEN 'other'
WHEN ${medium} = 'video-paid' AND ${source} IN ('facebook', 'youtube', 'hulu', 'virool') THEN lower(${source})
WHEN ${medium} = 'video-paid' THEN 'other'
ELSE 'all'
END ;;
  }

  dimension: daily_registration_count {
    type: number
    hidden: yes
    sql: ${TABLE}.registrations ;;
  }

  dimension: summed_ltv {
    type: number
    hidden: yes
    sql: ${TABLE}.ltv ;;
  }

  dimension: summed_smv {
    type: number
    hidden: yes
    sql: ${TABLE}.smv ;;
  }

  dimension: summed_promotion_spend {
    type: number
    hidden: yes
    sql: ${TABLE}.promotion_spend ;;
  }

  dimension: marketing_spend {
    type: number
    hidden: yes
    sql: ${TABLE}.marketing_spend ;;
  }

  dimension: dummy_pivot {
    label: "x"
    type: int
    sql: case when 1=1 then null end ;;
  }

  measure: total_ltv {
    label: "Total LTV"
    type: sum
    sql: ${summed_ltv} ;;
  }

  measure: total_smv {
    label: "Total SMV"
    type: sum
    sql: ${summed_smv} ;;
  }

  measure: avg_ltv {
    label: "Avg LTV"
    type: number
    sql: ${total_ltv}/nullif(${total_registration_count},0) ;;
  }

  measure: avg_smv {
    label: "Avg SMV"
    type: number
    sql: ${total_smv}/nullif(${total_registration_count},0) ;;
  }

  measure: total_registration_count {
    label: "Total Regs."
    type: sum
    sql: ${daily_registration_count} ;;
  }

  measure: total_promotion_spend_amount {
    type: sum
    sql: ${summed_promotion_spend} ;;
  }

  measure: total_marketing_spend {
    label: "Total Mkting Spend"
    type: sum
    sql: ${marketing_spend} ;;
  }

  measure: cpa {
    label: "CPA"
    type: number
    sql: ${total_marketing_spend}/nullif(${total_registration_count},0) ;;
  }

  measure: adjusted_cpa {
    label: "Adjusted CPA"
    type: number
    sql: (coalesce(${total_marketing_spend},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_registration_count},0) ;;
  }

  measure: margin {
    type: number
    sql: (${avg_ltv}-${cpa}) ;;
  }

  measure: roi_smv {
    label: "6 Month ROI"
    type: number
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100 ;;
  }

  measure: roi_ltv {
    label: "LT ROI"
    type: number
    sql: (${avg_ltv}/nullif(${adjusted_cpa},0) - 1) * 100 ;;
  }
}

- view: marketing_data_medium_summary
  derived_table:
    sql: select
coalesce(date(m.date), date(u.registration_date)) as registration_date,
coalesce(lower(m.medium),lower(u.medium)) as medium,
coalesce(lower(m.source),lower(u.source)) as source,
sum(coalesce(m.spend,0)) as marketing_spend,
sum(coalesce(u.summed_promotion_spend,0)) as promotion_spend,
sum(u.registration_count) as registrations,
sum(u.summed_smv) as smv,
sum(u.summed_ltv) as ltv
from analytics_marketing_spend m
full outer join  ${marketing_data_facts_daily.SQL_TABLE_Nshift_aE} u on date(m.date)=date(u.registration_date) and lower(m.medium)=lower(u.medium) and lower(m.source) = lower(u.source)
GROUP BY 1,2, 3
    persist_for: 1 hour
  fields:
  - dimension: registration
    type: time
    sql: ${TABLE}.registration_date
  - dimension: medium
    sql: ${TABLE}.medium
  - dimension: paid_channel
    sql: case when (lower(${medium}) similar to '%none%|%organic%|%referral%|%null%|%invitation%|%rss%|%gift%|%email%|%not set%') then 'Unpaid' else 'Paid' end
  - dimension: source
    sql: ${TABLE}.source
  - dimension: source_roll_up
    sql: CASE WHEN ${medium} = 'email' then 'all'
WHEN ${medium} IN ('social-paid', 'retargeting', 'search-paid') THEN ${source}
WHEN ${medium} = 'blog-paid' then 'all'
WHEN ${medium} ilike '%organic%' then 'all'
when ${medium} ilike 'dailydeal' then ${source}
WHEN ${medium} = 'display' AND ${source} IN ('neverblue', 'yahoo', 'quantcast', 'ebay', 'pandora', 'gdn', 'liveintent') THEN ${source}
WHEN ${medium} = 'display' THEN 'all other'
WHEN ${medium} = 'referral' and ${source} ilike '%facebook%' then 'facebook'
WHEN ${medium} = 'referral' THEN 'all'
WHEN ${medium} in ('Invitation','Gift') then ''
WHEN ${medium} = 'directmail' AND ${source} IN ('belardi', 'handybook') THEN lower(${source})
WHEN ${medium} = 'directmail' THEN 'other'
WHEN ${medium} = 'video-paid' AND ${source} IN ('facebook', 'youtube', 'hulu', 'virool') THEN lower(${source})
WHEN ${medium} = 'video-paid' THEN 'other'
ELSE 'all'
END
  - dimension: daily_registration_count
    type: number
    hidden: true
    sql: ${TABLE}.registrations
  - dimension: summed_ltv
    type: number
    hidden: true
    sql: ${TABLE}.ltv
  - dimension: summed_smv
    type: number
    hidden: true
    sql: ${TABLE}.smv
  - dimension: summed_promotion_spend
    type: number
    hidden: true
    sql: ${TABLE}.promotion_spend
  - dimension: marketing_spend
    type: number
    hidden: true
    sql: ${TABLE}.marketing_spend
  - dimension: dummy_pivot
    type: int
    label: x
    sql: case when 1=1 then null end
  - measure: total_ltv
    type: sum
    label: Total LTV
    sql: ${summed_ltv}
  - measure: total_smv
    type: sum
    label: Total SMV
    sql: ${summed_smv}
  - measure: avg_ltv
    type: number
    label: Avg LTV
    sql: ${total_ltv}/nullif(${total_registration_count},0)
  - measure: avg_smv
    type: number
    label: Avg SMV
    sql: ${total_smv}/nullif(${total_registration_count},0)
  - measure: total_registration_count
    type: sum
    label: Total Regs.
    sql: ${daily_registration_count}
  - measure: total_promotion_spend_amount
    type: sum
    sql: ${summed_promotion_spend}
  - measure: total_marketing_spend
    type: sum
    label: Total Mkting Spend
    sql: ${marketing_spend}
  - measure: cpa
    type: number
    label: CPA
    sql: ${total_marketing_spend}/nullif(${total_registration_count},0)
  - measure: adjusted_cpa
    type: number
    label: Adjusted CPA
    sql: (coalesce(${total_marketing_spend},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_registration_count},0)
  - measure: margin
    type: number
    sql: (${avg_ltv}-${cpa})
  - measure: roi_smv
    type: number
    label: 6 Month ROI
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100
  - measure: roi_ltv
    type: number
    label: LT ROI
    sql: (${avg_ltv}/nullif(${adjusted_cpa},0) - 1) * 100