Derived Email Stats

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

select
date(i.created_at) as email_created,
  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(1) as count_emails,
  sum(case when o.id is not null then 1 else 0 end) as opens,
  sum(case when ck.id is not null then 1 else 0 end) as clicks,
  sum(case when o.id is not null then 1 else 0 end) / count(1) as open_pct,
  sum(case when ck.id is not null then 1 else 0 end) / count(1) as click_pct
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
where i.created_at >= date(date_add(now(), interval -2 day))
group by 1, 2, 3, 4, 5, 6;

# models/schema.yml
version: 2

models:
  - name: derived_email_stats
    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: open_pct
      - name: click_pct

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
date(i.created_at) as email_created,
  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(1) as count_emails,
  sum(case when o.id is not null then 1 else 0 end) as opens,
  sum(case when ck.id is not null then 1 else 0 end) as clicks,
  sum(case when o.id is not null then 1 else 0 end) / count(1) as open_pct,
  sum(case when ck.id is not null then 1 else 0 end) / count(1) as click_pct
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
where i.created_at >= date(date_add(now(), interval -2 day))
group by 1, 2, 3, 4, 5, 6;

view: derived_email_stats {
  derived_table: {
    sql: select
date(i.created_at) as email_created,
  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(1) as count_emails,
  sum(case when o.id is not null then 1 else 0 end) as opens,
  sum(case when ck.id is not null then 1 else 0 end) as clicks,
  sum(case when o.id is not null then 1 else 0 end) / count(1) as open_pct,
  sum(case when ck.id is not null then 1 else 0 end) / count(1) as click_pct
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
where i.created_at >= date(date_add(now(), interval -2 day))
group by 1, 2, 3, 4, 5, 6; ;;
  }

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

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

  dimension_group: email_created {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.email_created ;;
  }

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

- view: derived_email_stats
  derived_table:
    sql: select
date(i.created_at) as email_created,
  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(1) as count_emails,
  sum(case when o.id is not null then 1 else 0 end) as opens,
  sum(case when ck.id is not null then 1 else 0 end) as clicks,
  sum(case when o.id is not null then 1 else 0 end) / count(1) as open_pct,
  sum(case when ck.id is not null then 1 else 0 end) / count(1) as click_pct
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
where i.created_at >= date(date_add(now(), interval -2 day))
group by 1, 2, 3, 4, 5, 6;
  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: open_pct
    type: number
    sql: 100.0 * ${TABLE}.open_pct
  - dimension: click_pct
    type: number
    sql: 100.0 * ${TABLE}.click_pct
  - dimension_group: email_created
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.email_created
  - measure: count
    type: count
    drill_fields:
    - detail*