Unbounce Promotion

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

select v.reporting_date
, v.unbounce_page_id
, v.unbounce_page_name
, v.variant
, v.visits
, o.orders
, o.units
, o.revenue
from (
select date(fpu.timestamp) as reporting_date
, fpu.unbounce_page_id
, fpu.unbounce_page_name
, fpu.variant
, sum(visits) as visits
from fct_promotion fpu
group by 1, 2, 3, 4
) v
left join (
  select date(soc.completed_at) as reporting_date
  , rs.term as variant
  , rs.content as unbounce_page_id
  , sum(soc.total) / 100.0 as revenue
  , count(distinct soc.id) as orders
  , count(*) as units
  from order_item_consolidated soic
  inner join order_consolidated soc
  on soic.order_id = soc.id
  inner join referral_sources rs
  on soc.referral_source_id = rs.id
  where soc.completed_at is not null
  group by 1, 2, 3
) o
on v.reporting_date = o.reporting_date
and v.unbounce_page_id = o.unbounce_page_id
and v.variant = o.variant

# models/schema.yml
version: 2

models:
  - name: unbounce_promotion
    columns:
      - name: reporting_date
      - name: unbounce_page_id
      - name: unbounce_page_name
      - name: variant

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 v.reporting_date
, v.unbounce_page_id
, v.unbounce_page_name
, v.variant
, v.visits
, o.orders
, o.units
, o.revenue
from (
select date(fpu.timestamp) as reporting_date
, fpu.unbounce_page_id
, fpu.unbounce_page_name
, fpu.variant
, sum(visits) as visits
from fct_promotion fpu
group by 1, 2, 3, 4
) v
left join (
  select date(soc.completed_at) as reporting_date
  , rs.term as variant
  , rs.content as unbounce_page_id
  , sum(soc.total) / 100.0 as revenue
  , count(distinct soc.id) as orders
  , count(*) as units
  from order_item_consolidated soic
  inner join order_consolidated soc
  on soic.order_id = soc.id
  inner join referral_sources rs
  on soc.referral_source_id = rs.id
  where soc.completed_at is not null
  group by 1, 2, 3
) o
on v.reporting_date = o.reporting_date
and v.unbounce_page_id = o.unbounce_page_id
and v.variant = o.variant

view: unbounce_promotion {
  derived_table: {
    sql: select v.reporting_date
, v.unbounce_page_id
, v.unbounce_page_name
, v.variant
, v.visits
, o.orders
, o.units
, o.revenue
from (
select date(fpu.timestamp) as reporting_date
, fpu.unbounce_page_id
, fpu.unbounce_page_name
, fpu.variant
, sum(visits) as visits
from fct_promotion fpu
group by 1, 2, 3, 4
) v
left join (
  select date(soc.completed_at) as reporting_date
  , rs.term as variant
  , rs.content as unbounce_page_id
  , sum(soc.total) / 100.0 as revenue
  , count(distinct soc.id) as orders
  , count(*) as units
  from order_item_consolidated soic
  inner join order_consolidated soc
  on soic.order_id = soc.id
  inner join referral_sources rs
  on soc.referral_source_id = rs.id
  where soc.completed_at is not null
  group by 1, 2, 3
) o
on v.reporting_date = o.reporting_date
and v.unbounce_page_id = o.unbounce_page_id
and v.variant = o.variant ;;
  }

  dimension: reporting_date {
    type: date
    sql: ${TABLE}.reporting_date ;;
  }

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

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

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

  measure: visits {
    type: sum
    sql: ${TABLE}.visits ;;
  }

  measure: orders {
    type: sum
    sql: ${TABLE}.orders ;;
  }

  measure: units {
    type: sum
    sql: ${TABLE}.units ;;
  }

  measure: revenue {
    type: sum
    sql: ${TABLE}.revenue ;;
  }

  measure: conversion {
    type: number
    sql: 100.0 * ${orders} / ${visits} ;;
  }

  measure: dollars_per_visit {
    label: "$ per visit"
    type: number
    sql: ${revenue} / ${visits} ;;
  }
}

- view: unbounce_promotion
  derived_table:
    sql: select v.reporting_date
, v.unbounce_page_id
, v.unbounce_page_name
, v.variant
, v.visits
, o.orders
, o.units
, o.revenue
from (
select date(fpu.timestamp) as reporting_date
, fpu.unbounce_page_id
, fpu.unbounce_page_name
, fpu.variant
, sum(visits) as visits
from fct_promotion fpu
group by 1, 2, 3, 4
) v
left join (
  select date(soc.completed_at) as reporting_date
  , rs.term as variant
  , rs.content as unbounce_page_id
  , sum(soc.total) / 100.0 as revenue
  , count(distinct soc.id) as orders
  , count(*) as units
  from order_item_consolidated soic
  inner join order_consolidated soc
  on soic.order_id = soc.id
  inner join referral_sources rs
  on soc.referral_source_id = rs.id
  where soc.completed_at is not null
  group by 1, 2, 3
) o
on v.reporting_date = o.reporting_date
and v.unbounce_page_id = o.unbounce_page_id
and v.variant = o.variant
  fields:
  - dimension: reporting_date
    type: date
    sql: ${TABLE}.reporting_date
  - dimension: unbounce_page_id
    sql: ${TABLE}.unbounce_page_id
  - dimension: unbounce_page_name
    sql: ${TABLE}.unbounce_page_name
  - dimension: variant
    sql: ${TABLE}.variant
  - measure: visits
    type: sum
    sql: ${TABLE}.visits
  - measure: orders
    type: sum
    sql: ${TABLE}.orders
  - measure: units
    type: sum
    sql: ${TABLE}.units
  - measure: revenue
    type: sum
    sql: ${TABLE}.revenue
  - measure: conversion
    type: number
    sql: 100.0 * ${orders} / ${visits}
  - measure: dollars_per_visit
    type: number
    label: $ per visit
    sql: ${revenue} / ${visits}