Campaign Stats Rollup

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

SELECT
  b.tenant_id as tenant_id,
  b.user_id,
  MIN(b.start_date) as first_start_date,
  MIN(b.lead_date) as first_lead_date,
  MIN(b.visit100_date) as first_vis100_date,
  MIN(b.first_campaign_date) as campaign_first_date,
  sum(b.visitors) as visitors,
  sum(b.views) as views,
  sum(b.cta_submits) as cta_submits,
  sum(b.unique_cta_submits) as unique_cta_submits,
  sum(b.cta_clicks) as cta_clicks,
  sum(b.leads) as leads,
  sum(b.clicks) as clicks,
  sum(b.total_spend) as total_spend,
  sum(b.impressions) as impressions
  FROM (
    SELECT
    d.tenant_id,
    d.user_id,
    d.start_date,
    MIN(d.start_date) OVER (PARTITION BY tenant_id) as first_campaign_date,
    d.lead_date,
    d.visitors,
    CASE WHEN d.sumvis >=100 AND d.sumvis IS NOT NULL THEN d.start_date
      ELSE NULL
    END
      AS visit100_date,
    d.views,
    d.cta_submits,
    d.unique_cta_submits,
    d.cta_clicks,
    d.leads,
    d.clicks,
    d.total_spend,
    d.impressions
    FROM (
      SELECT
      a.tenant_id,
      a.user_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.tenant_id,a.user_id ORDER BY a.start_date ROWS UNBOUNDED PRECEDING) as sumvis,
      a.views,
      a.cta_submits,
      a.unique_cta_submits,
      a.cta_clicks,
      a.leads,
      a.clicks,
      a.total_spend,
      a.impressions
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, p.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_id,
        COALESCE(g.user_id, p.user_id) as user_id,
        z.start_date,
        CASE WHEN z.cta_submits > 0 AND z.cta_submits IS NOT NULL THEN z.start_date
          ELSE NULL
        END
          AS lead_date,
        z.visitors,
        z.views,
        z.cta_submits,
        z.unique_cta_submits,
        z.cta_clicks,
        z.leads,
        z.clicks,
        z.total_spend,
        z.impressions
        FROM (
          SELECT
            x.id as engagement_id,
            y.id as ad_id,
            x.pitch_site_id as engagement_pitch_site_id,
            CASE WHEN x.provider_ad_id IS NOT NULL THEN x.provider_ad_id
              ELSE y.provider_ad_id
            END
              AS provider_ad_id_all,
            CASE WHEN x.start_date IS NOT NULL then x.start_date
             ELSE y.start_date
            END
             as start_date,
            y.tenant_id,
            x.visitors,
            x.views,
            x.cta_submits,
            x.unique_cta_submits,
            x.cta_clicks,
            x.leads,
            y.clicks,
            y.total_spend,
            y.impressions
          FROM (
            SELECT
              *
            FROM
              public_schema.provider_engagement_stats
            )  as x
          FULL OUTER JOIN
            public_schema.provider_ad_stats y
          ON
            x.provider_ad_id = y.provider_ad_id AND
            x.start_date = y.start_date
        ) as z
        LEFT OUTER JOIN
          public_schema.pitch_sites g
        ON
          z.engagement_pitch_site_id = g.id
        LEFT OUTER JOIN
          public_schema.provider_ads p
        ON
          z.provider_ad_id_all = p.id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.tenant_id,user_id

# models/schema.yml
version: 2

models:
  - name: campaign_stats_rollup
    columns:
      - name: tenant_id
      - name: lead_time
      - name: vis100_time
      - name: message_leads
      - name: leads
      - name: visitors
      - name: low_conversion_alert
      - name: low_traction_alert
      - name: salesforce_industry
      - name: company_size
      - name: company_size_range
      - name: days_into_campaign
      - name: conversion_pct
      - name: clicks
      - name: impressions
      - name: click_through_rate
      - name: leads_target
      - name: low_ctr
        description: "Less than .3% CTR"
      - name: visitors_target
      - name: low_visitors
        description: "Less than 10 visitors per day"
      - name: low_leads
        description: "Less than 1 lead every 10 days"
      - name: low_conversion
        description: "Less than .5% conversion rate"
      - name: alert_list
        description: "Low leads, low conversion rate, low visitors or low CTR"
      - name: manager
      - name: operations_lead

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.

SELECT
  b.tenant_id as tenant_id,
  b.user_id,
  MIN(b.start_date) as first_start_date,
  MIN(b.lead_date) as first_lead_date,
  MIN(b.visit100_date) as first_vis100_date,
  MIN(b.first_campaign_date) as campaign_first_date,
  sum(b.visitors) as visitors,
  sum(b.views) as views,
  sum(b.cta_submits) as cta_submits,
  sum(b.unique_cta_submits) as unique_cta_submits,
  sum(b.cta_clicks) as cta_clicks,
  sum(b.leads) as leads,
  sum(b.clicks) as clicks,
  sum(b.total_spend) as total_spend,
  sum(b.impressions) as impressions
  FROM (
    SELECT
    d.tenant_id,
    d.user_id,
    d.start_date,
    MIN(d.start_date) OVER (PARTITION BY tenant_id) as first_campaign_date,
    d.lead_date,
    d.visitors,
    CASE WHEN d.sumvis >=100 AND d.sumvis IS NOT NULL THEN d.start_date
      ELSE NULL
    END
      AS visit100_date,
    d.views,
    d.cta_submits,
    d.unique_cta_submits,
    d.cta_clicks,
    d.leads,
    d.clicks,
    d.total_spend,
    d.impressions
    FROM (
      SELECT
      a.tenant_id,
      a.user_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.tenant_id,a.user_id ORDER BY a.start_date ROWS UNBOUNDED PRECEDING) as sumvis,
      a.views,
      a.cta_submits,
      a.unique_cta_submits,
      a.cta_clicks,
      a.leads,
      a.clicks,
      a.total_spend,
      a.impressions
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, p.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_id,
        COALESCE(g.user_id, p.user_id) as user_id,
        z.start_date,
        CASE WHEN z.cta_submits > 0 AND z.cta_submits IS NOT NULL THEN z.start_date
          ELSE NULL
        END
          AS lead_date,
        z.visitors,
        z.views,
        z.cta_submits,
        z.unique_cta_submits,
        z.cta_clicks,
        z.leads,
        z.clicks,
        z.total_spend,
        z.impressions
        FROM (
          SELECT
            x.id as engagement_id,
            y.id as ad_id,
            x.pitch_site_id as engagement_pitch_site_id,
            CASE WHEN x.provider_ad_id IS NOT NULL THEN x.provider_ad_id
              ELSE y.provider_ad_id
            END
              AS provider_ad_id_all,
            CASE WHEN x.start_date IS NOT NULL then x.start_date
             ELSE y.start_date
            END
             as start_date,
            y.tenant_id,
            x.visitors,
            x.views,
            x.cta_submits,
            x.unique_cta_submits,
            x.cta_clicks,
            x.leads,
            y.clicks,
            y.total_spend,
            y.impressions
          FROM (
            SELECT
              *
            FROM
              public_schema.provider_engagement_stats
            )  as x
          FULL OUTER JOIN
            public_schema.provider_ad_stats y
          ON
            x.provider_ad_id = y.provider_ad_id AND
            x.start_date = y.start_date
        ) as z
        LEFT OUTER JOIN
          public_schema.pitch_sites g
        ON
          z.engagement_pitch_site_id = g.id
        LEFT OUTER JOIN
          public_schema.provider_ads p
        ON
          z.provider_ad_id_all = p.id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.tenant_id,user_id

view: campaign_stats_rollup {
  derived_table: {
    sql: SELECT
  b.tenant_id as tenant_id,
  b.user_id,
  MIN(b.start_date) as first_start_date,
  MIN(b.lead_date) as first_lead_date,
  MIN(b.visit100_date) as first_vis100_date,
  MIN(b.first_campaign_date) as campaign_first_date,
  sum(b.visitors) as visitors,
  sum(b.views) as views,
  sum(b.cta_submits) as cta_submits,
  sum(b.unique_cta_submits) as unique_cta_submits,
  sum(b.cta_clicks) as cta_clicks,
  sum(b.leads) as leads,
  sum(b.clicks) as clicks,
  sum(b.total_spend) as total_spend,
  sum(b.impressions) as impressions
  FROM (
    SELECT
    d.tenant_id,
    d.user_id,
    d.start_date,
    MIN(d.start_date) OVER (PARTITION BY tenant_id) as first_campaign_date,
    d.lead_date,
    d.visitors,
    CASE WHEN d.sumvis >=100 AND d.sumvis IS NOT NULL THEN d.start_date
      ELSE NULL
    END
      AS visit100_date,
    d.views,
    d.cta_submits,
    d.unique_cta_submits,
    d.cta_clicks,
    d.leads,
    d.clicks,
    d.total_spend,
    d.impressions
    FROM (
      SELECT
      a.tenant_id,
      a.user_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.tenant_id,a.user_id ORDER BY a.start_date ROWS UNBOUNDED PRECEDING) as sumvis,
      a.views,
      a.cta_submits,
      a.unique_cta_submits,
      a.cta_clicks,
      a.leads,
      a.clicks,
      a.total_spend,
      a.impressions
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, p.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_id,
        COALESCE(g.user_id, p.user_id) as user_id,
        z.start_date,
        CASE WHEN z.cta_submits > 0 AND z.cta_submits IS NOT NULL THEN z.start_date
          ELSE NULL
        END
          AS lead_date,
        z.visitors,
        z.views,
        z.cta_submits,
        z.unique_cta_submits,
        z.cta_clicks,
        z.leads,
        z.clicks,
        z.total_spend,
        z.impressions
        FROM (
          SELECT
            x.id as engagement_id,
            y.id as ad_id,
            x.pitch_site_id as engagement_pitch_site_id,
            CASE WHEN x.provider_ad_id IS NOT NULL THEN x.provider_ad_id
              ELSE y.provider_ad_id
            END
              AS provider_ad_id_all,
            CASE WHEN x.start_date IS NOT NULL then x.start_date
             ELSE y.start_date
            END
             as start_date,
            y.tenant_id,
            x.visitors,
            x.views,
            x.cta_submits,
            x.unique_cta_submits,
            x.cta_clicks,
            x.leads,
            y.clicks,
            y.total_spend,
            y.impressions
          FROM (
            SELECT
              *
            FROM
              public_schema.provider_engagement_stats
            )  as x
          FULL OUTER JOIN
            public_schema.provider_ad_stats y
          ON
            x.provider_ad_id = y.provider_ad_id AND
            x.start_date = y.start_date
        ) as z
        LEFT OUTER JOIN
          public_schema.pitch_sites g
        ON
          z.engagement_pitch_site_id = g.id
        LEFT OUTER JOIN
          public_schema.provider_ads p
        ON
          z.provider_ad_id_all = p.id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.tenant_id,user_id ;;
  }

  dimension: tenant_id {
    type: int
    sql: ${TABLE}.tenant_id ;;
  }

  dimension: lead_time {
    type: number
    hidden: yes
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_lead_date) ;;
  }

  dimension: vis100_time {
    type: number
    hidden: yes
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_vis100_date) ;;
  }

  dimension: message_leads {
    type: int
    sql: CASE
  WHEN ${campaign_message_leads.message_leads} IS NULL THEN 0
  ELSE ${campaign_message_leads.message_leads}
END ;;
  }

  dimension: leads {
    type: int
    sql: CASE
  WHEN ${TABLE}.unique_cta_submits IS NULL THEN ${message_leads}
  ELSE ${TABLE}.unique_cta_submits + ${message_leads}
END ;;
  }

  dimension: visitors {
    type: int
    hidden: yes
    sql: ${TABLE}.visitors ;;
  }

  dimension: low_conversion_alert {
    type: int
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${leads}=0 AND ${TABLE}.clicks>=100 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${leads}=0 AND ${TABLE}.clicks>=150 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${leads}=0 AND ${TABLE}.clicks>=200 THEN 1
WHEN ${subscription_plans.price}= 5000 AND ${leads}=0 AND ${TABLE}.clicks>=400 THEN 1
ELSE 0 END ;;
  }

  dimension: low_traction_alert {
    type: int
    hidden: yes
    sql: CASE
WHEN (now()::date - ${campaign_first_date}>3) AND ${TABLE}.visitors<10 THEN 1
ELSE 0 END ;;
  }

  dimension: salesforce_industry {
    sql: ${_account.industry} ;;
  }

  dimension: company_size {
    type: int
    sql: ${_account.number_of_employees} ;;
  }

  dimension: company_size_range {
    type: tier
    sql: ${_account.number_of_employees} ;;
  }

  dimension: days_into_campaign {
    type: int
    hidden: yes
    sql: CURRENT_DATE - ${campaign_first_date} + 1 ;;
  }

  dimension: conversion_pct {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: 1.00*${leads}/NULLIF(${visitors},0) ;;
  }

  dimension: clicks {
    type: int
    hidden: yes
    sql: ${TABLE}.clicks ;;
  }

  dimension: impressions {
    type: int
    hidden: yes
    sql: ${TABLE}.impressions ;;
  }

  dimension: click_through_rate {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: 1.00*${clicks}/NULLIF(${impressions},0) ;;
  }

  dimension: leads_target {
    type: int
    sql: FLOOR(${days_into_campaign} * .1) ;;
  }

  dimension: low_ctr {
    description: "Less than .3% CTR"
    type: int
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${clicks}>=100 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${clicks}>=150 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${clicks}>=200 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}>= 5000 AND ${clicks}>=400 AND ${click_through_rate} < .003 THEN 1
ELSE 0 END ;;
  }

  dimension: visitors_target {
    type: int
    hidden: yes
    sql: ${days_into_campaign} * 10 ;;
  }

  dimension: low_visitors {
    description: "Less than 10 visitors per day"
    type: int
    sql: CASE
WHEN ${visitors} < ${visitors_target} THEN 1
ELSE 0 END ;;
  }

  dimension: low_leads {
    description: "Less than 1 lead every 10 days"
    type: int
    sql: CASE
WHEN ${leads} < ${leads_target} THEN 1
ELSE 0 END ;;
  }

  dimension: low_conversion {
    description: "Less than .5% conversion rate"
    type: int
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${visitors}>=100 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${visitors}>=150 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${visitors}>=200 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}>= 5000 AND ${visitors}>=400 AND ${conversion_pct} < .005 THEN 1
ELSE 0 END ;;
  }

  dimension: alert_list {
    description: "Low leads, low conversion rate, low visitors or low CTR"
    type: int
    sql: CASE
WHEN ${low_conversion} = 1 THEN 1
WHEN ${low_leads} = 1 THEN 1
WHEN ${low_visitors} = 1 THEN 1
WHEN ${low_ctr} = 1 THEN 1
ELSE 0 END ;;
  }

  dimension: manager {
    sql: COALESCE(${_account.manager}, ${people2.display_name}) ;;
  }

  dimension: operations_lead {
    sql: COALESCE(${_account.operations_lead}, ${people3.display_name}) ;;
  }

  dimension_group: first_start {
    label: "First Ad"
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.first_start_date ;;
  }

  dimension_group: campaign_first {
    label: "Campaign First Ad"
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.campaign_first_date ;;
  }

  dimension_group: first_lead {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.first_lead_date ;;
  }

  dimension_group: first_vis100 {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.first_vis100_date ;;
  }

  measure: first_lead_days {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${lead_time}/24 ;;
  }

  measure: first_vis100_days {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${vis100_time}/24 ;;
  }

  measure: total_visitors {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.visitors ;;
  }

  measure: total_views {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.views ;;
  }

  measure: total_clicks {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.clicks ;;
  }

  measure: total_impressions {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.impressions ;;
  }

  measure: sum_total_spend {
    label: "Total Spend"
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: (1.00)*${TABLE}.total_spend/100 ;;
  }

  measure: total_non_redirect_leads {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.leads ;;
  }

  measure: total_cta_clicks {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.cta_clicks ;;
  }

  measure: total_leads {
    type: sum
    drill_fields: [detail*]
    sql: ${leads} ;;
  }

  measure: total_message_leads {
    type: sum
    drill_fields: [detail*]
    sql: ${message_leads} ;;
  }

  measure: sum_mrr {
    type: sum
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: ${subscription_plans.price} ;;
  }

  measure: avg_visitors {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.visitors ;;
  }

  measure: avg_views {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.views ;;
  }

  measure: avg_clicks {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.clicks ;;
  }

  measure: avg_impressions {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.impressions ;;
  }

  measure: avg_total_spend {
    type: average
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: (1.00)*${TABLE}.total_spend/100 ;;
  }

  measure: avg_non_redirect_leads {
    type: average
    hidden: yes
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.leads ;;
  }

  measure: avg_cta_clicks {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.cta_clicks ;;
  }

  measure: avg_leads {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${leads} ;;
  }

  measure: avg_message_leads {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${message_leads} ;;
  }

  measure: pct_message_leads {
    type: number
    value_format: "#.00%"
    drill_fields: [detail*]
    sql: 1.00*${total_message_leads}/NULLIF(${total_leads},0) ;;
  }

  measure: ctr {
    type: number
    value_format: "#.00%"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.total_clicks}/NULLIF(${campaign_stats_rollup.total_impressions},0) ;;
  }

  measure: cpc {
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.sum_total_spend}/NULLIF(${campaign_stats_rollup.total_clicks},0) ;;
  }

  measure: percent_cta_clicks {
    type: number
    value_format: "#.00%"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.total_cta_clicks}/NULLIF(${campaign_stats_rollup.total_views},0) ;;
  }

  measure: percent_submit {
    type: number
    value_format: "#.00%"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.total_leads}/NULLIF(${campaign_stats_rollup.total_cta_clicks},0) ;;
  }

  measure: cpl {
    type: number
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.sum_total_spend}/NULLIF(${campaign_stats_rollup.total_leads},0) ;;
  }

  measure: conversion_rate {
    type: number
    value_format: "#.00%"
    drill_fields: [detail*]
    sql: 1.00*${campaign_stats_rollup.total_leads}/NULLIF(${campaign_stats_rollup.total_visitors},0) ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}

- view: campaign_stats_rollup
  derived_table:
    sql: SELECT
  b.tenant_id as tenant_id,
  b.user_id,
  MIN(b.start_date) as first_start_date,
  MIN(b.lead_date) as first_lead_date,
  MIN(b.visit100_date) as first_vis100_date,
  MIN(b.first_campaign_date) as campaign_first_date,
  sum(b.visitors) as visitors,
  sum(b.views) as views,
  sum(b.cta_submits) as cta_submits,
  sum(b.unique_cta_submits) as unique_cta_submits,
  sum(b.cta_clicks) as cta_clicks,
  sum(b.leads) as leads,
  sum(b.clicks) as clicks,
  sum(b.total_spend) as total_spend,
  sum(b.impressions) as impressions
  FROM (
    SELECT
    d.tenant_id,
    d.user_id,
    d.start_date,
    MIN(d.start_date) OVER (PARTITION BY tenant_id) as first_campaign_date,
    d.lead_date,
    d.visitors,
    CASE WHEN d.sumvis >=100 AND d.sumvis IS NOT NULL THEN d.start_date
      ELSE NULL
    END
      AS visit100_date,
    d.views,
    d.cta_submits,
    d.unique_cta_submits,
    d.cta_clicks,
    d.leads,
    d.clicks,
    d.total_spend,
    d.impressions
    FROM (
      SELECT
      a.tenant_id,
      a.user_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.tenant_id,a.user_id ORDER BY a.start_date ROWS UNBOUNDED PRECEDING) as sumvis,
      a.views,
      a.cta_submits,
      a.unique_cta_submits,
      a.cta_clicks,
      a.leads,
      a.clicks,
      a.total_spend,
      a.impressions
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, p.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_id,
        COALESCE(g.user_id, p.user_id) as user_id,
        z.start_date,
        CASE WHEN z.cta_submits > 0 AND z.cta_submits IS NOT NULL THEN z.start_date
          ELSE NULL
        END
          AS lead_date,
        z.visitors,
        z.views,
        z.cta_submits,
        z.unique_cta_submits,
        z.cta_clicks,
        z.leads,
        z.clicks,
        z.total_spend,
        z.impressions
        FROM (
          SELECT
            x.id as engagement_id,
            y.id as ad_id,
            x.pitch_site_id as engagement_pitch_site_id,
            CASE WHEN x.provider_ad_id IS NOT NULL THEN x.provider_ad_id
              ELSE y.provider_ad_id
            END
              AS provider_ad_id_all,
            CASE WHEN x.start_date IS NOT NULL then x.start_date
             ELSE y.start_date
            END
             as start_date,
            y.tenant_id,
            x.visitors,
            x.views,
            x.cta_submits,
            x.unique_cta_submits,
            x.cta_clicks,
            x.leads,
            y.clicks,
            y.total_spend,
            y.impressions
          FROM (
            SELECT
              *
            FROM
              public_schema.provider_engagement_stats
            )  as x
          FULL OUTER JOIN
            public_schema.provider_ad_stats y
          ON
            x.provider_ad_id = y.provider_ad_id AND
            x.start_date = y.start_date
        ) as z
        LEFT OUTER JOIN
          public_schema.pitch_sites g
        ON
          z.engagement_pitch_site_id = g.id
        LEFT OUTER JOIN
          public_schema.provider_ads p
        ON
          z.provider_ad_id_all = p.id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.tenant_id,user_id
  fields:
  - dimension: tenant_id
    type: int
    sql: ${TABLE}.tenant_id
  - dimension: lead_time
    type: number
    hidden: true
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_lead_date)
  - dimension: vis100_time
    type: number
    hidden: true
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_vis100_date)
  - dimension: message_leads
    type: int
    sql: CASE
  WHEN ${campaign_message_leads.message_leads} IS NULL THEN 0
  ELSE ${campaign_message_leads.message_leads}
END
  - dimension: leads
    type: int
    sql: CASE
  WHEN ${TABLE}.unique_cta_submits IS NULL THEN ${message_leads}
  ELSE ${TABLE}.unique_cta_submits + ${message_leads}
END
  - dimension: visitors
    type: int
    hidden: true
    sql: ${TABLE}.visitors
  - dimension: low_conversion_alert
    type: int
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${leads}=0 AND ${TABLE}.clicks>=100 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${leads}=0 AND ${TABLE}.clicks>=150 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${leads}=0 AND ${TABLE}.clicks>=200 THEN 1
WHEN ${subscription_plans.price}= 5000 AND ${leads}=0 AND ${TABLE}.clicks>=400 THEN 1
ELSE 0 END
  - dimension: low_traction_alert
    type: int
    hidden: true
    sql: CASE
WHEN (now()::date - ${campaign_first_date}>3) AND ${TABLE}.visitors<10 THEN 1
ELSE 0 END
  - dimension: salesforce_industry
    sql: ${_account.industry}
  - dimension: company_size
    type: int
    sql: ${_account.number_of_employees}
  - dimension: company_size_range
    type: tier
    sql: ${_account.number_of_employees}
  - dimension: days_into_campaign
    type: int
    hidden: true
    sql: CURRENT_DATE - ${campaign_first_date} + 1
  - dimension: conversion_pct
    type: number
    hidden: true
    value_format: '#.00%'
    sql: 1.00*${leads}/NULLIF(${visitors},0)
  - dimension: clicks
    type: int
    hidden: true
    sql: ${TABLE}.clicks
  - dimension: impressions
    type: int
    hidden: true
    sql: ${TABLE}.impressions
  - dimension: click_through_rate
    type: number
    hidden: true
    value_format: '#.00%'
    sql: 1.00*${clicks}/NULLIF(${impressions},0)
  - dimension: leads_target
    type: int
    sql: FLOOR(${days_into_campaign} * .1)
  - dimension: low_ctr
    type: int
    description: 'Less than .3% CTR'
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${clicks}>=100 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${clicks}>=150 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${clicks}>=200 AND ${click_through_rate} < .003 THEN 1
WHEN ${subscription_plans.price}>= 5000 AND ${clicks}>=400 AND ${click_through_rate} < .003 THEN 1
ELSE 0 END
  - dimension: visitors_target
    type: int
    hidden: true
    sql: ${days_into_campaign} * 10
  - dimension: low_visitors
    type: int
    description: Less than 10 visitors per day
    sql: CASE
WHEN ${visitors} < ${visitors_target} THEN 1
ELSE 0 END
  - dimension: low_leads
    type: int
    description: Less than 1 lead every 10 days
    sql: CASE
WHEN ${leads} < ${leads_target} THEN 1
ELSE 0 END
  - dimension: low_conversion
    type: int
    description: 'Less than .5% conversion rate'
    sql: CASE
WHEN ${subscription_plans.price}<=500 AND ${visitors}>=100 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}= 900 AND ${visitors}>=150 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}= 2000 AND ${visitors}>=200 AND ${conversion_pct} < .005 THEN 1
WHEN ${subscription_plans.price}>= 5000 AND ${visitors}>=400 AND ${conversion_pct} < .005 THEN 1
ELSE 0 END
  - dimension: alert_list
    type: int
    description: Low leads, low conversion rate, low visitors or low CTR
    sql: CASE
WHEN ${low_conversion} = 1 THEN 1
WHEN ${low_leads} = 1 THEN 1
WHEN ${low_visitors} = 1 THEN 1
WHEN ${low_ctr} = 1 THEN 1
ELSE 0 END
  - dimension: manager
    sql: COALESCE(${_account.manager}, ${people2.display_name})
  - dimension: operations_lead
    sql: COALESCE(${_account.operations_lead}, ${people3.display_name})
  - dimension_group: first_start
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.first_start_date
  - dimension_group: campaign_first
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.campaign_first_date
  - dimension_group: first_lead
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.first_lead_date
  - dimension_group: first_vis100
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.first_vis100_date
  - measure: first_lead_days
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${lead_time}/24
  - measure: first_vis100_days
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${vis100_time}/24
  - measure: total_visitors
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.visitors
  - measure: total_views
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.views
  - measure: total_clicks
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.clicks
  - measure: total_impressions
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.impressions
  - measure: sum_total_spend
    type: sum
    label: Total Spend
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: (1.00)*${TABLE}.total_spend/100
  - measure: total_non_redirect_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.leads
  - measure: total_cta_clicks
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.cta_clicks
  - measure: total_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${leads}
  - measure: total_message_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${message_leads}
  - measure: sum_mrr
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: ${subscription_plans.price}
  - measure: avg_visitors
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.visitors
  - measure: avg_views
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.views
  - measure: avg_clicks
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.clicks
  - measure: avg_impressions
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.impressions
  - measure: avg_total_spend
    type: average
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: (1.00)*${TABLE}.total_spend/100
  - measure: avg_non_redirect_leads
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.leads
  - measure: avg_cta_clicks
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.cta_clicks
  - measure: avg_leads
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${leads}
  - measure: avg_message_leads
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${message_leads}
  - measure: pct_message_leads
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${total_message_leads}/NULLIF(${total_leads},0)
  - measure: ctr
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.total_clicks}/NULLIF(${campaign_stats_rollup.total_impressions},0)
  - measure: cpc
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.sum_total_spend}/NULLIF(${campaign_stats_rollup.total_clicks},0)
  - measure: percent_cta_clicks
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.total_cta_clicks}/NULLIF(${campaign_stats_rollup.total_views},0)
  - measure: percent_submit
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.total_leads}/NULLIF(${campaign_stats_rollup.total_cta_clicks},0)
  - measure: cpl
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.sum_total_spend}/NULLIF(${campaign_stats_rollup.total_leads},0)
  - measure: conversion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${campaign_stats_rollup.total_leads}/NULLIF(${campaign_stats_rollup.total_visitors},0)
  - measure: count
    type: count
    drill_fields:
    - detail*