Spot Campaign Stats Rollup

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

SELECT
  b.pitch_site_id as pitch_site_id,
  b.tenant_id as tenant_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,
  min(b.pitch_sites_count) as total_spots
  FROM (
    SELECT
    d.pitch_site_id,
    d.tenant_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,
    d.pitch_sites_count
    FROM (
      SELECT
      a.pitch_site_id,
      a.tenant_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.pitch_site_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,
      a.pitch_sites_count
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, h.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_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,
        hh.pitch_sites_count
        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
        LEFT OUTER JOIN
          public_schema.pitch_sites gg
        ON
          p.pitch_site_id = gg.id
        LEFT OUTER JOIN
          public_schema.hubs hh
        ON
          gg.public_url = hh.public_url and z.engagement_pitch_site_id is null
        LEFT OUTER JOIN
          public_schema.pitch_site_hubs h
        ON
          hh.id = h.hub_id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.pitch_site_id, b.tenant_id

# models/schema.yml
version: 2

models:
  - name: spot_campaign_stats_rollup
    columns:
      - name: pitch_sites_count
      - name: pitch_site_id
      - name: tenant_id
      - name: lead_time
      - name: vis100_time
      - name: message_leads
      - name: leads_without_messages
      - name: leads
      - name: template
      - name: visitors
      - name: views
      - name: clicks
      - name: impressions
      - name: total_spend
      - name: cta_clicks
      - 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.pitch_site_id as pitch_site_id,
  b.tenant_id as tenant_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,
  min(b.pitch_sites_count) as total_spots
  FROM (
    SELECT
    d.pitch_site_id,
    d.tenant_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,
    d.pitch_sites_count
    FROM (
      SELECT
      a.pitch_site_id,
      a.tenant_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.pitch_site_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,
      a.pitch_sites_count
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, h.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_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,
        hh.pitch_sites_count
        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
        LEFT OUTER JOIN
          public_schema.pitch_sites gg
        ON
          p.pitch_site_id = gg.id
        LEFT OUTER JOIN
          public_schema.hubs hh
        ON
          gg.public_url = hh.public_url and z.engagement_pitch_site_id is null
        LEFT OUTER JOIN
          public_schema.pitch_site_hubs h
        ON
          hh.id = h.hub_id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.pitch_site_id, b.tenant_id

view: spot_campaign_stats_rollup {
  derived_table: {
    sql: SELECT
  b.pitch_site_id as pitch_site_id,
  b.tenant_id as tenant_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,
  min(b.pitch_sites_count) as total_spots
  FROM (
    SELECT
    d.pitch_site_id,
    d.tenant_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,
    d.pitch_sites_count
    FROM (
      SELECT
      a.pitch_site_id,
      a.tenant_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.pitch_site_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,
      a.pitch_sites_count
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, h.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_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,
        hh.pitch_sites_count
        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
        LEFT OUTER JOIN
          public_schema.pitch_sites gg
        ON
          p.pitch_site_id = gg.id
        LEFT OUTER JOIN
          public_schema.hubs hh
        ON
          gg.public_url = hh.public_url and z.engagement_pitch_site_id is null
        LEFT OUTER JOIN
          public_schema.pitch_site_hubs h
        ON
          hh.id = h.hub_id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.pitch_site_id, b.tenant_id ;;
  }

  dimension: pitch_sites_count {
    sql: CASE
WHEN ${TABLE}.total_spots IS NULL THEN 1
ELSE ${TABLE}.total_spots
END ;;
  }

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

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

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

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

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

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

  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: template {
    sql: CASE
  WHEN ${pitch_sites.template} = 'wide column' OR ${pitch_sites.template} = 'introduction' THEN 'wide column'
  ELSE ${pitch_sites.template}
END ;;
  }

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

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

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

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

  dimension: total_spend {
    type: int
    hidden: yes
    sql: ${TABLE}.total_spend/${pitch_sites_count}/100 ;;
  }

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

  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: "Spot 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: ${lead_time}/24 ;;
  }

  measure: first_vis100_days {
    type: average
    drill_fields: [detail*]
    sql: ${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/${pitch_sites_count} ;;
  }

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

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

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

  measure: total_cta_clicks {
    type: sum
    drill_fields: [detail*]
    sql: CASE
WHEN ${TABLE}.cta_clicks > 0 THEN ${TABLE}.cta_clicks
ELSE 0
END ;;
  }

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

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

  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/${pitch_sites_count} ;;
  }

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

  measure: avg_total_spend {
    type: average
    value_format: "$#,##0.00"
    drill_fields: [detail*]
    sql: (1.00)*${TABLE}.total_spend/${pitch_sites_count}/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: CASE
WHEN ${TABLE}.cta_clicks > 0 THEN 1.00*${TABLE}.cta_clicks
ELSE 0
END ;;
  }

  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*${spot_campaign_stats_rollup.total_clicks}/NULLIF(${spot_campaign_stats_rollup.total_impressions},0) ;;
  }

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

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

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

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

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

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

- view: spot_campaign_stats_rollup
  derived_table:
    sql: SELECT
  b.pitch_site_id as pitch_site_id,
  b.tenant_id as tenant_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,
  min(b.pitch_sites_count) as total_spots
  FROM (
    SELECT
    d.pitch_site_id,
    d.tenant_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,
    d.pitch_sites_count
    FROM (
      SELECT
      a.pitch_site_id,
      a.tenant_id,
      a.start_date,
      a.lead_date,
      a.visitors,
      sum(a.visitors) OVER (PARTITION BY a.pitch_site_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,
      a.pitch_sites_count
      FROM (
      SELECT
        z.engagement_id,
        z.ad_id,
        z.provider_ad_id_all,
        COALESCE(z.engagement_pitch_site_id, h.pitch_site_id) AS pitch_site_id,
        COALESCE(g.tenant_id, z.tenant_id, p.tenant_id) as tenant_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,
        hh.pitch_sites_count
        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
        LEFT OUTER JOIN
          public_schema.pitch_sites gg
        ON
          p.pitch_site_id = gg.id
        LEFT OUTER JOIN
          public_schema.hubs hh
        ON
          gg.public_url = hh.public_url and z.engagement_pitch_site_id is null
        LEFT OUTER JOIN
          public_schema.pitch_site_hubs h
        ON
          hh.id = h.hub_id
      ) as a
    ) as d
  ) as b
  GROUP BY
    b.pitch_site_id, b.tenant_id
  fields:
  - dimension: pitch_sites_count
    sql: CASE
WHEN ${TABLE}.total_spots IS NULL THEN 1
ELSE ${TABLE}.total_spots
END
  - dimension: pitch_site_id
    type: int
    sql: ${TABLE}.pitch_site_id
  - dimension: tenant_id
    type: int
    sql: ${TABLE}.tenant_id
  - dimension: lead_time
    type: number
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_lead_date)
  - dimension: vis100_time
    type: number
    sql: DATEDIFF(hours, ${TABLE}.first_start_date, ${TABLE}.first_vis100_date)
  - dimension: message_leads
    type: int
    sql: CASE
  WHEN ${spot_message_leads.message_leads} IS NULL THEN 0
  ELSE ${spot_message_leads.message_leads}
END
  - dimension: leads_without_messages
    type: int
    hidden: true
    sql: ${TABLE}.unique_cta_submits
  - 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: template
    sql: CASE
  WHEN ${pitch_sites.template} = 'wide column' OR ${pitch_sites.template} = 'introduction' THEN 'wide column'
  ELSE ${pitch_sites.template}
END
  - dimension: visitors
    type: int
    hidden: true
    sql: ${TABLE}.visitors
  - dimension: views
    type: int
    hidden: true
    sql: ${TABLE}.views
  - dimension: clicks
    type: int
    hidden: true
    sql: ${TABLE}.clicks/${pitch_sites_count}
  - dimension: impressions
    type: int
    hidden: true
    sql: ${TABLE}.impressions/${pitch_sites_count}
  - dimension: total_spend
    type: int
    hidden: true
    sql: ${TABLE}.total_spend/${pitch_sites_count}/100
  - dimension: cta_clicks
    type: int
    hidden: true
    sql: ${TABLE}.cta_clicks
  - 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: ${lead_time}/24
  - measure: first_vis100_days
    type: average
    drill_fields:
    - detail*
    sql: ${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/${pitch_sites_count}
  - measure: total_impressions
    type: sum
    drill_fields:
    - detail*
    sql: ${impressions}
  - measure: sum_total_spend
    type: sum
    label: Total Spend
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: (1.00)*${TABLE}.total_spend/${pitch_sites_count}/100
  - measure: total_non_redirect_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.leads
  - measure: total_cta_clicks
    type: sum
    drill_fields:
    - detail*
    sql: CASE
WHEN ${TABLE}.cta_clicks > 0 THEN ${TABLE}.cta_clicks
ELSE 0
END
  - measure: total_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${leads}
  - measure: total_message_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${message_leads}
  - 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/${pitch_sites_count}
  - measure: avg_impressions
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.impressions/${pitch_sites_count}
  - measure: avg_total_spend
    type: average
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: (1.00)*${TABLE}.total_spend/${pitch_sites_count}/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: CASE
WHEN ${TABLE}.cta_clicks > 0 THEN 1.00*${TABLE}.cta_clicks
ELSE 0
END
  - 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*${spot_campaign_stats_rollup.total_clicks}/NULLIF(${spot_campaign_stats_rollup.total_impressions},0)
  - measure: cpc
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${spot_campaign_stats_rollup.sum_total_spend}/NULLIF(${spot_campaign_stats_rollup.total_clicks},0)
  - measure: percent_cta_clicks
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${spot_campaign_stats_rollup.total_cta_clicks}/NULLIF(${spot_campaign_stats_rollup.total_views},0)
  - measure: percent_submit
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${spot_campaign_stats_rollup.total_leads}/NULLIF(${spot_campaign_stats_rollup.total_cta_clicks},0)
  - measure: cpl
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${spot_campaign_stats_rollup.sum_total_spend}/NULLIF(${spot_campaign_stats_rollup.total_leads},0)
  - measure: conversion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${spot_campaign_stats_rollup.total_leads}/NULLIF(${spot_campaign_stats_rollup.total_visitors},0)
  - measure: count
    type: count
    drill_fields:
    - detail*