Derived Emails 24hr Revenue

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

select
   g.id as group_id,
   concat(g.id, '-', g.name) as group_name,
   e.id as email_id,
   concat(e.id, '-', e.name) as email_name,
   v.id as variation_id,
   concat(v.id, '-', v.name) as variation_name,
 p.id as product_id, p.sku, p.name as product_name, sum(case when ck.id is not null then cse.quantity else 0 end) as quantity,
 sum(case when ck.id is not null then cse.quantity *pp.price else 0 end) as revenue
 from marketing_campaign_email_instances i
   join marketing_campaign_email_variation_events ve
     on i.campaign_email_variation_event_id = ve.id
   join marketing_campaign_email_variations v
     on ve.campaign_email_variation_id = v.id
   join marketing_campaign_emails e
     on v.campaign_email_id = e.id
   join marketing_campaign_email_groups g
     on e.campaign_email_group_id = g.id
   join marketing_campaign_email_clicks ck
     on ck.campaign_email_instance_id = i.id
 join customer_subscriptions cs
   on cs.customer_id = i.customer_id and cs.status in (1,2,4)
 left join customer_subscription_extras cse
   on cse.customer_subscription_id = cs.id and cse.created_at >= ck.created_at
 left join extras x
   on cse.extra_id = x.id
 left join products p
   on x.product_id = p.id
 left join product_prices pp
   on x.product_id = pp.product_id and pp.price > 0
   where i.created_at >= date(date_add(now(), interval -24 hour))
   group by 1, 2, 3, 4, 5, 6, 7, 8, 9

# models/schema.yml
version: 2

models:
  - name: derived_emails_24hr_revenue
    columns:
      - name: group_id
      - name: group_name
      - name: email_id
      - name: email_name
      - name: variation_id
      - name: variation_name
      - name: product_id
      - name: sku
      - name: product_name
      - name: quantity
      - name: revenue

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
   g.id as group_id,
   concat(g.id, '-', g.name) as group_name,
   e.id as email_id,
   concat(e.id, '-', e.name) as email_name,
   v.id as variation_id,
   concat(v.id, '-', v.name) as variation_name,
 p.id as product_id, p.sku, p.name as product_name, sum(case when ck.id is not null then cse.quantity else 0 end) as quantity,
 sum(case when ck.id is not null then cse.quantity *pp.price else 0 end) as revenue
 from marketing_campaign_email_instances i
   join marketing_campaign_email_variation_events ve
     on i.campaign_email_variation_event_id = ve.id
   join marketing_campaign_email_variations v
     on ve.campaign_email_variation_id = v.id
   join marketing_campaign_emails e
     on v.campaign_email_id = e.id
   join marketing_campaign_email_groups g
     on e.campaign_email_group_id = g.id
   join marketing_campaign_email_clicks ck
     on ck.campaign_email_instance_id = i.id
 join customer_subscriptions cs
   on cs.customer_id = i.customer_id and cs.status in (1,2,4)
 left join customer_subscription_extras cse
   on cse.customer_subscription_id = cs.id and cse.created_at >= ck.created_at
 left join extras x
   on cse.extra_id = x.id
 left join products p
   on x.product_id = p.id
 left join product_prices pp
   on x.product_id = pp.product_id and pp.price > 0
   where i.created_at >= date(date_add(now(), interval -24 hour))
   group by 1, 2, 3, 4, 5, 6, 7, 8, 9

view: derived_emails_24hr_revenue {
  derived_table: {
    sql: select
   g.id as group_id,
   concat(g.id, '-', g.name) as group_name,
   e.id as email_id,
   concat(e.id, '-', e.name) as email_name,
   v.id as variation_id,
   concat(v.id, '-', v.name) as variation_name,
 p.id as product_id, p.sku, p.name as product_name, sum(case when ck.id is not null then cse.quantity else 0 end) as quantity,
 sum(case when ck.id is not null then cse.quantity *pp.price else 0 end) as revenue
 from marketing_campaign_email_instances i
   join marketing_campaign_email_variation_events ve
     on i.campaign_email_variation_event_id = ve.id
   join marketing_campaign_email_variations v
     on ve.campaign_email_variation_id = v.id
   join marketing_campaign_emails e
     on v.campaign_email_id = e.id
   join marketing_campaign_email_groups g
     on e.campaign_email_group_id = g.id
   join marketing_campaign_email_clicks ck
     on ck.campaign_email_instance_id = i.id
 join customer_subscriptions cs
   on cs.customer_id = i.customer_id and cs.status in (1,2,4)
 left join customer_subscription_extras cse
   on cse.customer_subscription_id = cs.id and cse.created_at >= ck.created_at
 left join extras x
   on cse.extra_id = x.id
 left join products p
   on x.product_id = p.id
 left join product_prices pp
   on x.product_id = pp.product_id and pp.price > 0
   where i.created_at >= date(date_add(now(), interval -24 hour))
   group by 1, 2, 3, 4, 5, 6, 7, 8, 9 ;;
  }

  dimension: group_id {
    type: number
    sql: ${TABLE}.group_id ;;
  }

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

  dimension: email_id {
    type: number
    sql: ${TABLE}.email_id ;;
  }

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

  dimension: variation_id {
    type: number
    sql: ${TABLE}.variation_id ;;
  }

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

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

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

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

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

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

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

- view: derived_emails_24hr_revenue
  derived_table:
    sql: select
   g.id as group_id,
   concat(g.id, '-', g.name) as group_name,
   e.id as email_id,
   concat(e.id, '-', e.name) as email_name,
   v.id as variation_id,
   concat(v.id, '-', v.name) as variation_name,
 p.id as product_id, p.sku, p.name as product_name, sum(case when ck.id is not null then cse.quantity else 0 end) as quantity,
 sum(case when ck.id is not null then cse.quantity *pp.price else 0 end) as revenue
 from marketing_campaign_email_instances i
   join marketing_campaign_email_variation_events ve
     on i.campaign_email_variation_event_id = ve.id
   join marketing_campaign_email_variations v
     on ve.campaign_email_variation_id = v.id
   join marketing_campaign_emails e
     on v.campaign_email_id = e.id
   join marketing_campaign_email_groups g
     on e.campaign_email_group_id = g.id
   join marketing_campaign_email_clicks ck
     on ck.campaign_email_instance_id = i.id
 join customer_subscriptions cs
   on cs.customer_id = i.customer_id and cs.status in (1,2,4)
 left join customer_subscription_extras cse
   on cse.customer_subscription_id = cs.id and cse.created_at >= ck.created_at
 left join extras x
   on cse.extra_id = x.id
 left join products p
   on x.product_id = p.id
 left join product_prices pp
   on x.product_id = pp.product_id and pp.price > 0
   where i.created_at >= date(date_add(now(), interval -24 hour))
   group by 1, 2, 3, 4, 5, 6, 7, 8, 9
  fields:
  - dimension: group_id
    type: number
    sql: ${TABLE}.group_id
  - dimension: group_name
    sql: ${TABLE}.group_name
  - dimension: email_id
    type: number
    sql: ${TABLE}.email_id
  - dimension: email_name
    sql: ${TABLE}.email_name
  - dimension: variation_id
    type: number
    sql: ${TABLE}.variation_id
  - dimension: variation_name
    sql: ${TABLE}.variation_name
  - dimension: product_id
    sql: ${TABLE}.product_id
  - dimension: sku
    sql: ${TABLE}.sku
  - dimension: product_name
    sql: ${TABLE}.product_name
  - dimension: quantity
    type: int
    sql: ${TABLE}.quantity
  - dimension: revenue
    sql: ${TABLE}.revenue
  - measure: count
    type: count
    drill_fields:
    - detail*