Provider Ad Stats

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

SELECT
b.provider_ad_id_all AS provider_ad_id,
case when <% parameter date_group %> = 'Week'
          then date_trunc('week',b.period_start_date)::date
     when <% parameter date_group %> = 'Month'
          then date_trunc('month',b.period_start_date)::date
     when <% parameter date_group %> = 'Rollup'
          then '1900-01-01'::date
     when <% parameter date_group %> = 'Day'
          then b.period_start_date
     end as timeframe,
MIN(b.first_start_date) AS first_start_date,
b.ad_period AS ad_period,
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
    c.engagement_id,
    c.ad_id,
    c.provider_ad_id_all,
    MIN(c.start_date) OVER (PARTITION BY c.provider_ad_id_all,c.ad_period) as period_start_date,
    c.first_start_date,
    c.ad_period,
    c.visitors,
    c.views,
    c.cta_submits,
    c.unique_cta_submits,
    c.cta_clicks,
    c.leads,
    c.clicks,
    c.total_spend,
    c.impressions
    FROM (
    SELECT
      a.engagement_id,
      a.ad_id,
      a.provider_ad_id_all,
      a.start_date,
      a.first_start_date,
      case when <% parameter date_group %> = 'Week'
               then (start_date - first_start_date)/7::int
           when <% parameter date_group %> = 'Month'
               then (EXTRACT('year' FROM a.start_date) - EXTRACT('year' FROM a.first_start_date)) * 12 +
                    (EXTRACT('month' FROM a.start_date) - EXTRACT('month' FROM a.first_start_date)) -
                     CASE WHEN (CEILING(DATE_PART('day',a.start_date) - DATE_PART('day',a.first_start_date))) < 0 THEN 1 ELSE 0 end
           when <% parameter date_group %> = 'Day'
               then (start_date - first_start_date)::int
           when <% parameter date_group %> = 'Rollup'
               then 0::int
           end as ad_period,
      a.visitors,
      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,
        z.start_date,
        MIN(z.start_date) OVER (PARTITION BY z.provider_ad_id_all) as first_start_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,
            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,
            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
            WHERE
              provider_ad_id IS NOT NULL
            )  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
      ) as a
    ) as c
  ) as b
  GROUP BY
    b.provider_ad_id_all, timeframe, b.ad_period

# models/schema.yml
version: 2

models:
  - name: provider_ad_stats
    columns:
      - name: provider_ad_id
      - name: ad_period
      - name: period_start
      - name: period_end
      - name: spend
      - name: messaging_version
      - 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.provider_ad_id_all AS provider_ad_id,
case when <% parameter date_group %> = 'Week'
          then date_trunc('week',b.period_start_date)::date
     when <% parameter date_group %> = 'Month'
          then date_trunc('month',b.period_start_date)::date
     when <% parameter date_group %> = 'Rollup'
          then '1900-01-01'::date
     when <% parameter date_group %> = 'Day'
          then b.period_start_date
     end as timeframe,
MIN(b.first_start_date) AS first_start_date,
b.ad_period AS ad_period,
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
    c.engagement_id,
    c.ad_id,
    c.provider_ad_id_all,
    MIN(c.start_date) OVER (PARTITION BY c.provider_ad_id_all,c.ad_period) as period_start_date,
    c.first_start_date,
    c.ad_period,
    c.visitors,
    c.views,
    c.cta_submits,
    c.unique_cta_submits,
    c.cta_clicks,
    c.leads,
    c.clicks,
    c.total_spend,
    c.impressions
    FROM (
    SELECT
      a.engagement_id,
      a.ad_id,
      a.provider_ad_id_all,
      a.start_date,
      a.first_start_date,
      case when <% parameter date_group %> = 'Week'
               then (start_date - first_start_date)/7::int
           when <% parameter date_group %> = 'Month'
               then (EXTRACT('year' FROM a.start_date) - EXTRACT('year' FROM a.first_start_date)) * 12 +
                    (EXTRACT('month' FROM a.start_date) - EXTRACT('month' FROM a.first_start_date)) -
                     CASE WHEN (CEILING(DATE_PART('day',a.start_date) - DATE_PART('day',a.first_start_date))) < 0 THEN 1 ELSE 0 end
           when <% parameter date_group %> = 'Day'
               then (start_date - first_start_date)::int
           when <% parameter date_group %> = 'Rollup'
               then 0::int
           end as ad_period,
      a.visitors,
      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,
        z.start_date,
        MIN(z.start_date) OVER (PARTITION BY z.provider_ad_id_all) as first_start_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,
            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,
            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
            WHERE
              provider_ad_id IS NOT NULL
            )  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
      ) as a
    ) as c
  ) as b
  GROUP BY
    b.provider_ad_id_all, timeframe, b.ad_period

view: provider_ad_stats {
  derived_table: {
    sql: SELECT
b.provider_ad_id_all AS provider_ad_id,
case when <% parameter date_group %> = 'Week'
          then date_trunc('week',b.period_start_date)::date
     when <% parameter date_group %> = 'Month'
          then date_trunc('month',b.period_start_date)::date
     when <% parameter date_group %> = 'Rollup'
          then '1900-01-01'::date
     when <% parameter date_group %> = 'Day'
          then b.period_start_date
     end as timeframe,
MIN(b.first_start_date) AS first_start_date,
b.ad_period AS ad_period,
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
    c.engagement_id,
    c.ad_id,
    c.provider_ad_id_all,
    MIN(c.start_date) OVER (PARTITION BY c.provider_ad_id_all,c.ad_period) as period_start_date,
    c.first_start_date,
    c.ad_period,
    c.visitors,
    c.views,
    c.cta_submits,
    c.unique_cta_submits,
    c.cta_clicks,
    c.leads,
    c.clicks,
    c.total_spend,
    c.impressions
    FROM (
    SELECT
      a.engagement_id,
      a.ad_id,
      a.provider_ad_id_all,
      a.start_date,
      a.first_start_date,
      case when <% parameter date_group %> = 'Week'
               then (start_date - first_start_date)/7::int
           when <% parameter date_group %> = 'Month'
               then (EXTRACT('year' FROM a.start_date) - EXTRACT('year' FROM a.first_start_date)) * 12 +
                    (EXTRACT('month' FROM a.start_date) - EXTRACT('month' FROM a.first_start_date)) -
                     CASE WHEN (CEILING(DATE_PART('day',a.start_date) - DATE_PART('day',a.first_start_date))) < 0 THEN 1 ELSE 0 end
           when <% parameter date_group %> = 'Day'
               then (start_date - first_start_date)::int
           when <% parameter date_group %> = 'Rollup'
               then 0::int
           end as ad_period,
      a.visitors,
      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,
        z.start_date,
        MIN(z.start_date) OVER (PARTITION BY z.provider_ad_id_all) as first_start_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,
            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,
            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
            WHERE
              provider_ad_id IS NOT NULL
            )  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
      ) as a
    ) as c
  ) as b
  GROUP BY
    b.provider_ad_id_all, timeframe, b.ad_period ;;
  }

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

  dimension: ad_period {
    type: int
    sql: ${TABLE}.ad_period + 1 ;;
  }

  dimension: period_start {
    label: "Ad Period Start"
    type: time
    sql: CASE
  WHEN <% parameter date_group %> = 'Month' THEN ${ad_date} + date_part('day',${first_date})::int - 1
  WHEN <% parameter date_group %> = 'Week' THEN ${ad_date} + date_part('dow', ${first_date})::int - 1
  ELSE NULL
END ;;
  }

  dimension: period_end {
    label: "Ad Period End"
    type: time
    sql: CASE
  WHEN <% parameter date_group %> = 'Month' THEN ADD_MONTHS(${ad_date} + date_part('day',${first_date})::int - 2,1)
  WHEN <% parameter date_group %> = 'Week' THEN ${ad_date} + date_part('dow',${first_date})::int - 2 + interval '1 week'
  ELSE NULL
END ;;
  }

  dimension: spend {
    type: number
    value_format: "$#,##0.00"
    sql: ${TABLE}.total_spend/100 ;;
  }

  dimension: messaging_version {
    sql: CASE
  WHEN ${TABLE}.timeframe < <% parameter placeholder_date %>::date THEN 'pre-messaging'
  ELSE 'v1'
END ;;
  }

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

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

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

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

  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_submits {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.cta_submits ;;
  }

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

  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: average_impressions_new {
    type: number
    hidden: yes
    drill_fields: [detail*]
    sql: 1.00*${total_impressions} / NULLIF(${count_provider_ad_id},0) ;;
  }

  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_cta_submits {
    type: average
    drill_fields: [detail*]
    sql: 1.00*${TABLE}.cta_submits ;;
  }

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

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

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

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

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

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

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

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

  measure: count_provider_ad_id {
    label: "Unique Provider Ad Count"
    type: count_distinct
    drill_fields: [detail*]
    sql: ${provider_ad_id} ;;
  }
}

- view: provider_ad_stats
  derived_table:
    sql: SELECT
b.provider_ad_id_all AS provider_ad_id,
case when <% parameter date_group %> = 'Week'
          then date_trunc('week',b.period_start_date)::date
     when <% parameter date_group %> = 'Month'
          then date_trunc('month',b.period_start_date)::date
     when <% parameter date_group %> = 'Rollup'
          then '1900-01-01'::date
     when <% parameter date_group %> = 'Day'
          then b.period_start_date
     end as timeframe,
MIN(b.first_start_date) AS first_start_date,
b.ad_period AS ad_period,
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
    c.engagement_id,
    c.ad_id,
    c.provider_ad_id_all,
    MIN(c.start_date) OVER (PARTITION BY c.provider_ad_id_all,c.ad_period) as period_start_date,
    c.first_start_date,
    c.ad_period,
    c.visitors,
    c.views,
    c.cta_submits,
    c.unique_cta_submits,
    c.cta_clicks,
    c.leads,
    c.clicks,
    c.total_spend,
    c.impressions
    FROM (
    SELECT
      a.engagement_id,
      a.ad_id,
      a.provider_ad_id_all,
      a.start_date,
      a.first_start_date,
      case when <% parameter date_group %> = 'Week'
               then (start_date - first_start_date)/7::int
           when <% parameter date_group %> = 'Month'
               then (EXTRACT('year' FROM a.start_date) - EXTRACT('year' FROM a.first_start_date)) * 12 +
                    (EXTRACT('month' FROM a.start_date) - EXTRACT('month' FROM a.first_start_date)) -
                     CASE WHEN (CEILING(DATE_PART('day',a.start_date) - DATE_PART('day',a.first_start_date))) < 0 THEN 1 ELSE 0 end
           when <% parameter date_group %> = 'Day'
               then (start_date - first_start_date)::int
           when <% parameter date_group %> = 'Rollup'
               then 0::int
           end as ad_period,
      a.visitors,
      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,
        z.start_date,
        MIN(z.start_date) OVER (PARTITION BY z.provider_ad_id_all) as first_start_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,
            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,
            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
            WHERE
              provider_ad_id IS NOT NULL
            )  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
      ) as a
    ) as c
  ) as b
  GROUP BY
    b.provider_ad_id_all, timeframe, b.ad_period
  fields:
  - dimension: provider_ad_id
    type: int
    sql: ${TABLE}.provider_ad_id
  - dimension: ad_period
    type: int
    sql: ${TABLE}.ad_period + 1
  - dimension: period_start
    type: time
    label: Ad Period Start
    sql: CASE
  WHEN <% parameter date_group %> = 'Month' THEN ${ad_date} + date_part('day',${first_date})::int - 1
  WHEN <% parameter date_group %> = 'Week' THEN ${ad_date} + date_part('dow', ${first_date})::int - 1
  ELSE NULL
END
  - dimension: period_end
    type: time
    label: Ad Period End
    sql: CASE
  WHEN <% parameter date_group %> = 'Month' THEN ADD_MONTHS(${ad_date} + date_part('day',${first_date})::int - 2,1)
  WHEN <% parameter date_group %> = 'Week' THEN ${ad_date} + date_part('dow',${first_date})::int - 2 + interval '1 week'
  ELSE NULL
END
  - dimension: spend
    type: number
    value_format: '$#,##0.00'
    sql: ${TABLE}.total_spend/100
  - dimension: messaging_version
    sql: CASE
  WHEN ${TABLE}.timeframe < <% parameter placeholder_date %>::date THEN 'pre-messaging'
  ELSE 'v1'
END
  - dimension: manager
    sql: COALESCE(${_account.manager}, ${people2.display_name})
  - dimension: operations_lead
    sql: COALESCE(${_account.operations_lead}, ${people3.display_name})
  - dimension_group: first
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.first_start_date
  - dimension_group: ad
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.timeframe
  - 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_submits
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.cta_submits
  - measure: total_leads
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.unique_cta_submits
  - 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: average_impressions_new
    type: number
    drill_fields:
    - detail*
    sql: 1.00*${total_impressions} / NULLIF(${count_provider_ad_id},0)
  - 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_cta_submits
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.cta_submits
  - measure: avg_leads
    type: average
    drill_fields:
    - detail*
    sql: 1.00*${TABLE}.unique_cta_submits
  - measure: ctr
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.total_clicks}/NULLIF(${provider_ad_stats.total_impressions},0)
  - measure: cpc
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.sum_total_spend}/NULLIF(${provider_ad_stats.total_clicks},0)
  - measure: percent_cta_clicks
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.total_cta_clicks}/NULLIF(${provider_ad_stats.total_views},0)
  - measure: percent_submit
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.total_leads}/NULLIF(${provider_ad_stats.total_cta_clicks},0)
  - measure: cpl
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.sum_total_spend}/NULLIF(${provider_ad_stats.total_leads},0)
  - measure: conversion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats.total_leads}/NULLIF(${provider_ad_stats.total_visitors},0)
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_provider_ad_id
    type: count_distinct
    label: Unique Provider Ad Count
    drill_fields:
    - detail*
    sql: ${provider_ad_id}