data.ml
SQL Saas crm

Provider Ad Stats Rollup

Provider Ad Stats Rollup model for saas crm.

source-preserved

Blocks

provider_ad_stats_rollup.sql · 79 lines
inferred 
-- models/provider_ad_stats_rollup.sql
{{ config(materialized='view') }}

SELECT
a.provider_ad_id_all as provider_ad_id,
a.first_start_date,
sum(a.visitors) as visitors,
sum(a.views) as views,
sum(a.cta_submits) as cta_submits,
sum(a.unique_cta_submits) as unique_cta_submits,
sum(a.cta_clicks) as cta_clicks,
sum(a.leads) as leads,
sum(a.clicks) as clicks,
sum(a.total_spend) as total_spend,
sum(a.impressions) as 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
GROUP BY
  a.provider_ad_id_all, a.first_start_date

# models/schema.yml
version: 2

models:
  - name: provider_ad_stats_rollup
    columns:
      - name: provider_ad_id
      - 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.
provider_ad_stats_rollup.sql · 66 lines
source-preserved validation: parses 
SELECT
a.provider_ad_id_all as provider_ad_id,
a.first_start_date,
sum(a.visitors) as visitors,
sum(a.views) as views,
sum(a.cta_submits) as cta_submits,
sum(a.unique_cta_submits) as unique_cta_submits,
sum(a.cta_clicks) as cta_clicks,
sum(a.leads) as leads,
sum(a.clicks) as clicks,
sum(a.total_spend) as total_spend,
sum(a.impressions) as 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
GROUP BY
  a.provider_ad_id_all, a.first_start_date
provider_ad_stats_rollup.view.lkml · 238 lines
inferred 
view: provider_ad_stats_rollup {
  derived_table: {
    sql: SELECT
a.provider_ad_id_all as provider_ad_id,
a.first_start_date,
sum(a.visitors) as visitors,
sum(a.views) as views,
sum(a.cta_submits) as cta_submits,
sum(a.unique_cta_submits) as unique_cta_submits,
sum(a.cta_clicks) as cta_clicks,
sum(a.leads) as leads,
sum(a.clicks) as clicks,
sum(a.total_spend) as total_spend,
sum(a.impressions) as 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
GROUP BY
  a.provider_ad_id_all, a.first_start_date ;;
  }

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

  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 ;;
  }

  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: ${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: 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*${TABLE}.unique_cta_submits ;;
  }

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

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

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

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

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

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

  measure: count {
    type: count
    drill_fields: [detail*]
  }
}
provider_ad_stats_rollup.view.lookml · 207 lines
source-preserved 
- view: provider_ad_stats_rollup
  derived_table:
    sql: SELECT
a.provider_ad_id_all as provider_ad_id,
a.first_start_date,
sum(a.visitors) as visitors,
sum(a.views) as views,
sum(a.cta_submits) as cta_submits,
sum(a.unique_cta_submits) as unique_cta_submits,
sum(a.cta_clicks) as cta_clicks,
sum(a.leads) as leads,
sum(a.clicks) as clicks,
sum(a.total_spend) as total_spend,
sum(a.impressions) as 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
GROUP BY
  a.provider_ad_id_all, a.first_start_date
  fields:
  - dimension: provider_ad_id
    type: int
    sql: ${TABLE}.provider_ad_id
  - 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
  - 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: ${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: 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*${TABLE}.unique_cta_submits
  - measure: ctr
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.total_clicks}/NULLIF(${provider_ad_stats_rollup.total_impressions},0)
  - measure: cpc
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.sum_total_spend}/NULLIF(${provider_ad_stats_rollup.total_clicks},0)
  - measure: percent_cta_clicks
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.total_cta_clicks}/NULLIF(${provider_ad_stats_rollup.total_views},0)
  - measure: percent_submit
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.total_leads}/NULLIF(${provider_ad_stats_rollup.total_cta_clicks},0)
  - measure: cpl
    type: number
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.sum_total_spend}/NULLIF(${provider_ad_stats_rollup.total_leads},0)
  - measure: conversion_rate
    type: number
    value_format: '#.00%'
    drill_fields:
    - detail*
    sql: 1.00*${provider_ad_stats_rollup.total_leads}/NULLIF(${provider_ad_stats_rollup.total_visitors},0)
  - measure: count
    type: count
    drill_fields:
    - detail*

Contents

  • provider_ad_stats_rollup.sql
  • provider_ad_stats_rollup.sql
  • provider_ad_stats_rollup.view.lkml
  • provider_ad_stats_rollup.view.lookml