Derived Emails 24hr

-- models/derived_emails_24hr.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,
   i.status,
   count(distinct i.id) as count_emails,
   count(distinct o.id) as opens,
   count(distinct ck.id) as clicks,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) as revenue,
   count(distinct o.id) / count(1) as open_pct,
   count(distinct ck.id) / count(1) as click_pct,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) / count(distinct i.id) as rev_per_mail
 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
   left join marketing_campaign_email_opens o
     on o.campaign_email_instance_id = i.id
   left 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 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

# models/schema.yml
version: 2

models:
  - name: derived_emails_24hr
    columns:
      - name: group_id
      - name: group_name
      - name: email_id
      - name: email_name
      - name: variation_id
      - name: variation_name
      - name: status
      - name: count_emails
      - name: opens
      - name: clicks
      - name: revenue
      - name: open_pct
      - name: click_pct
      - name: rev_per_mail

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,
   i.status,
   count(distinct i.id) as count_emails,
   count(distinct o.id) as opens,
   count(distinct ck.id) as clicks,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) as revenue,
   count(distinct o.id) / count(1) as open_pct,
   count(distinct ck.id) / count(1) as click_pct,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) / count(distinct i.id) as rev_per_mail
 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
   left join marketing_campaign_email_opens o
     on o.campaign_email_instance_id = i.id
   left 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 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

view: derived_emails_24hr {
  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,
   i.status,
   count(distinct i.id) as count_emails,
   count(distinct o.id) as opens,
   count(distinct ck.id) as clicks,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) as revenue,
   count(distinct o.id) / count(1) as open_pct,
   count(distinct ck.id) / count(1) as click_pct,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) / count(distinct i.id) as rev_per_mail
 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
   left join marketing_campaign_email_opens o
     on o.campaign_email_instance_id = i.id
   left 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 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 ;;
  }

  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: status {
    type: number
    sql: ${TABLE}.status ;;
  }

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

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

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

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

  dimension: open_pct {
    type: number
    sql: 100.0 * ${TABLE}.open_pct ;;
  }

  dimension: click_pct {
    type: number
    sql: 100.0 * ${TABLE}.click_pct ;;
  }

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

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

- view: derived_emails_24hr
  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,
   i.status,
   count(distinct i.id) as count_emails,
   count(distinct o.id) as opens,
   count(distinct ck.id) as clicks,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) as revenue,
   count(distinct o.id) / count(1) as open_pct,
   count(distinct ck.id) / count(1) as click_pct,
   sum(case when ck.id is not null then cse.quantity*pp.price else 0 end) / count(distinct i.id) as rev_per_mail
 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
   left join marketing_campaign_email_opens o
     on o.campaign_email_instance_id = i.id
   left 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 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
  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: status
    type: number
    sql: ${TABLE}.status
  - dimension: count_emails
    type: number
    sql: ${TABLE}.count_emails
  - dimension: opens
    type: number
    sql: ${TABLE}.opens
  - dimension: clicks
    type: number
    sql: ${TABLE}.clicks
  - dimension: revenue
    type: number
    sql: ${TABLE}.revenue
  - dimension: open_pct
    type: number
    sql: 100.0 * ${TABLE}.open_pct
  - dimension: click_pct
    type: number
    sql: 100.0 * ${TABLE}.click_pct
  - dimension: rev_per_mail
    type: number
    sql: ${TABLE}.rev_per_mail
  - measure: count
    type: count
    drill_fields:
    - detail*