Marketing Data Facts Daily

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

SELECT LOWER(TRIM(a.source)) AS source,
        LOWER(TRIM(
                  case
                  when a.medium = 'email' and a.source ilike '%invit%' then 'invitation'
                  when a.medium = 'email' and a.source ilike '%gift%' then 'gift'
                  when a.medium in
                  ('audio-paid', 'blog-organic', 'blog-paid', 'charity', 'content-paid', 'dailydeal',
                   'direct', 'directmail', 'display', 'email', 'retargeting', 'social-organic', 'social-paid',
                   'tv-paid', 'vlog-paid') then a.medium
                  when a.medium ilike 'organic%' then 'organic'
                  when a.medium ilike 'ppc%' then 'search-paid'
                  when a.medium ilike 'social%' then 'social-paid'
                  when a.medium ilike '%display%' then 'display'
                  when a.medium ilike '%blog%' then 'blog-paid'
                  else 'other' end)) AS medium,
        DATE(event_date) AS registration_date,
        sum(a.reg_contrib) AS registration_count,
        count(distinct CASE WHEN b.promotion_spend is NULL THEN null
            ELSE b.user_id END) AS promotion_count,
        sum(a.reg_contrib * b.ltv) as summed_ltv,
        sum(a.reg_contrib * b.smv) as summed_smv,
        sum(a.reg_contrib * b.promotion_spend) as summed_promotion_spend
FROM convertro.regs a
join analytics_users u on a.user_id_sha1 = u.user_id_sha1
join analytics_users_signup_facts b on u.id = b.user_id
GROUP BY 1,2,3

# models/schema.yml
version: 2

models:
  - name: marketing_data_facts_daily
    columns:
      - name: source
        tests:
          - unique
          - not_null
      - name: medium
      - name: facility
      - name: source_roll_up
      - name: registration
      - name: registration_week_sunday
      - name: daily_registration_count
      - name: daily_promotion_count
      - name: summed_ltv
      - name: summed_smv
      - name: summed_promotion_spend
      - name: dummy_pivot

Assumptions

• 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 LOWER(TRIM(a.source)) AS source,
        LOWER(TRIM(
                  case
                  when a.medium = 'email' and a.source ilike '%invit%' then 'invitation'
                  when a.medium = 'email' and a.source ilike '%gift%' then 'gift'
                  when a.medium in
                  ('audio-paid', 'blog-organic', 'blog-paid', 'charity', 'content-paid', 'dailydeal',
                   'direct', 'directmail', 'display', 'email', 'retargeting', 'social-organic', 'social-paid',
                   'tv-paid', 'vlog-paid') then a.medium
                  when a.medium ilike 'organic%' then 'organic'
                  when a.medium ilike 'ppc%' then 'search-paid'
                  when a.medium ilike 'social%' then 'social-paid'
                  when a.medium ilike '%display%' then 'display'
                  when a.medium ilike '%blog%' then 'blog-paid'
                  else 'other' end)) AS medium,
        DATE(event_date) AS registration_date,
        sum(a.reg_contrib) AS registration_count,
        count(distinct CASE WHEN b.promotion_spend is NULL THEN null
            ELSE b.user_id END) AS promotion_count,
        sum(a.reg_contrib * b.ltv) as summed_ltv,
        sum(a.reg_contrib * b.smv) as summed_smv,
        sum(a.reg_contrib * b.promotion_spend) as summed_promotion_spend
FROM convertro.regs a
join analytics_users u on a.user_id_sha1 = u.user_id_sha1
join analytics_users_signup_facts b on u.id = b.user_id
GROUP BY 1,2,3

view: marketing_data_facts_daily {
  derived_table: {
    sql: SELECT LOWER(TRIM(a.source)) AS source,
        LOWER(TRIM(
                  case
                  when a.medium = 'email' and a.source ilike '%invit%' then 'invitation'
                  when a.medium = 'email' and a.source ilike '%gift%' then 'gift'
                  when a.medium in
                  ('audio-paid', 'blog-organic', 'blog-paid', 'charity', 'content-paid', 'dailydeal',
                   'direct', 'directmail', 'display', 'email', 'retargeting', 'social-organic', 'social-paid',
                   'tv-paid', 'vlog-paid') then a.medium
                  when a.medium ilike 'organic%' then 'organic'
                  when a.medium ilike 'ppc%' then 'search-paid'
                  when a.medium ilike 'social%' then 'social-paid'
                  when a.medium ilike '%display%' then 'display'
                  when a.medium ilike '%blog%' then 'blog-paid'
                  else 'other' end)) AS medium,
        DATE(event_date) AS registration_date,
        sum(a.reg_contrib) AS registration_count,
        count(distinct CASE WHEN b.promotion_spend is NULL THEN null
            ELSE b.user_id END) AS promotion_count,
        sum(a.reg_contrib * b.ltv) as summed_ltv,
        sum(a.reg_contrib * b.smv) as summed_smv,
        sum(a.reg_contrib * b.promotion_spend) as summed_promotion_spend
FROM convertro.regs a
join analytics_users u on a.user_id_sha1 = u.user_id_sha1
join analytics_users_signup_facts b on u.id = b.user_id
GROUP BY 1,2,3 ;;
    persist_for: "1 hour"
  }

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

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

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

  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: registration {
    type: time
    sql: ${TABLE}.registration_date ;;
  }

  dimension: registration_week_sunday {
    type: date
    sql: ${registration_date} + (0- (CAST((EXTRACT(dow FROM ${registration_date})+7) AS INTEGER) % 7)) ;;
  }

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

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

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

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

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

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

  measure: total_ltv {
    type: sum
    sql: ${summed_ltv} ;;
  }

  measure: total_smv {
    type: sum
    sql: ${summed_smv} ;;
  }

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

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

  measure: total_registration_count {
    type: sum
    sql: ${daily_registration_count} ;;
  }

  measure: total_promotion_count {
    type: sum
    sql: ${daily_promotion_count} ;;
  }

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

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

  measure: adjusted_cpa {
    label: "MARKETING Adjusted CPA"
    type: number
    sql: (coalesce(${marketing_spend.total_spend_amount},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
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100 ;;
  }

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

- view: marketing_data_facts_daily
  derived_table:
    sql: SELECT LOWER(TRIM(a.source)) AS source,
        LOWER(TRIM(
                  case
                  when a.medium = 'email' and a.source ilike '%invit%' then 'invitation'
                  when a.medium = 'email' and a.source ilike '%gift%' then 'gift'
                  when a.medium in
                  ('audio-paid', 'blog-organic', 'blog-paid', 'charity', 'content-paid', 'dailydeal',
                   'direct', 'directmail', 'display', 'email', 'retargeting', 'social-organic', 'social-paid',
                   'tv-paid', 'vlog-paid') then a.medium
                  when a.medium ilike 'organic%' then 'organic'
                  when a.medium ilike 'ppc%' then 'search-paid'
                  when a.medium ilike 'social%' then 'social-paid'
                  when a.medium ilike '%display%' then 'display'
                  when a.medium ilike '%blog%' then 'blog-paid'
                  else 'other' end)) AS medium,
        DATE(event_date) AS registration_date,
        sum(a.reg_contrib) AS registration_count,
        count(distinct CASE WHEN b.promotion_spend is NULL THEN null
            ELSE b.user_id END) AS promotion_count,
        sum(a.reg_contrib * b.ltv) as summed_ltv,
        sum(a.reg_contrib * b.smv) as summed_smv,
        sum(a.reg_contrib * b.promotion_spend) as summed_promotion_spend
FROM convertro.regs a
join analytics_users u on a.user_id_sha1 = u.user_id_sha1
join analytics_users_signup_facts b on u.id = b.user_id
GROUP BY 1,2,3
    persist_for: 1 hour
  fields:
  - dimension: source
    primary_key: true
    sql: ${TABLE}.source
  - dimension: medium
    sql: ${TABLE}.medium
  - dimension: facility
    sql: ${TABLE}.facility
  - 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: registration
    type: time
    sql: ${TABLE}.registration_date
  - dimension: registration_week_sunday
    type: date
    sql: ${registration_date} + (0- (CAST((EXTRACT(dow FROM ${registration_date})+7) AS INTEGER) % 7))
  - dimension: daily_registration_count
    type: number
    hidden: true
    sql: ${TABLE}.registration_count
  - dimension: daily_promotion_count
    type: number
    hidden: true
    sql: ${TABLE}.promotion_count
  - dimension: summed_ltv
    type: number
    hidden: true
    sql: ${TABLE}.summed_ltv
  - dimension: summed_smv
    type: number
    hidden: true
    sql: ${TABLE}.summed_smv
  - dimension: summed_promotion_spend
    type: number
    hidden: true
    sql: ${TABLE}.summed_promotion_spend
  - dimension: dummy_pivot
    type: int
    label: x
    sql: case when 1=1 then null end
  - measure: total_ltv
    type: sum
    sql: ${summed_ltv}
  - measure: total_smv
    type: sum
    sql: ${summed_smv}
  - measure: avg_ltv
    type: number
    sql: ${total_ltv}/nullif(${total_registration_count},0)
  - measure: avg_smv
    type: number
    sql: ${total_smv}/nullif(${total_registration_count},0)
  - measure: total_registration_count
    type: sum
    sql: ${daily_registration_count}
  - measure: total_promotion_count
    type: sum
    sql: ${daily_promotion_count}
  - measure: total_promotion_spend_amount
    type: sum
    sql: ${summed_promotion_spend}
  - measure: cpa
    type: number
    label: MARKETING CPA
    sql: ${marketing_spend.total_spend_amount}/nullif(${total_registration_count},0)
  - measure: adjusted_cpa
    type: number
    label: MARKETING Adjusted CPA
    sql: (coalesce(${marketing_spend.total_spend_amount},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
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100
  - measure: roi_ltv
    type: number
    sql: (${avg_ltv}/nullif(${adjusted_cpa},0) - 1) * 100