Monthly Engagement Churn

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

/* find all engagements */
with (
    select distinct date_trunc('day',e.created_at) as day, e.member_id as id
    from engagements e
    where
    {% condition date_month %} e.created_at {% endcondition %} and
    {% condition date_week %} e.created_at {% endcondition %} and
    {% condition date_date %} e.created_at {% endcondition %}
    group by 1,2
) as e
(
    /* churned - find all engagements where there is no enagagement within the next 30 days */
    select e.day as date, e.id, 'churned_at'  as type from e
    left join e e2 on e.id=e2.id and e2.day >e.day and e2.day < e.day + interval '30 days'
    where e2.day is null
) union (
    /* reengaged - find all engagements where the previous engagement was more than 30 days ago */
    select e.day as date, e.id, 'reengaged_at' as type from e
    left join e e2 on e.id=e2.id and e2.day < e.day and e2.day >= e.day - interval '30 days'
    left join e e3 on e.id=e3.id and e3.day < e.day - interval '30 days'
    where e2.day is null and e3.day is not null
) union (
    /* created - just find all campaign_new users */
    select min(m.created_at) as date,
    m.id,
    'created_at' as type
    from users m
    group by 2
) union (
    /* "bounced" = find all campaign_new users with no engagements */
    select
    min(m.created_at) as date,
    m.id,
    'bounced_at' as type
    from users m
    where not exists (select 1 from engagements e where e.member_id = m.id limit 1)
    group by 2
) union (
    /* engaged - just find all engaged users  */
    select
    e.day,
    e.id,
    'engaged_at' as type
    from e
)

# models/schema.yml
version: 2

models:
  - name: monthly_engagement_churn
    columns:
      - name: type
      - name: date
      - name: id

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.

/* find all engagements */
with (
    select distinct date_trunc('day',e.created_at) as day, e.member_id as id
    from engagements e
    where
    {% condition date_month %} e.created_at {% endcondition %} and
    {% condition date_week %} e.created_at {% endcondition %} and
    {% condition date_date %} e.created_at {% endcondition %}
    group by 1,2
) as e
(
    /* churned - find all engagements where there is no enagagement within the next 30 days */
    select e.day as date, e.id, 'churned_at'  as type from e
    left join e e2 on e.id=e2.id and e2.day >e.day and e2.day < e.day + interval '30 days'
    where e2.day is null
) union (
    /* reengaged - find all engagements where the previous engagement was more than 30 days ago */
    select e.day as date, e.id, 'reengaged_at' as type from e
    left join e e2 on e.id=e2.id and e2.day < e.day and e2.day >= e.day - interval '30 days'
    left join e e3 on e.id=e3.id and e3.day < e.day - interval '30 days'
    where e2.day is null and e3.day is not null
) union (
    /* created - just find all campaign_new users */
    select min(m.created_at) as date,
    m.id,
    'created_at' as type
    from users m
    group by 2
) union (
    /* "bounced" = find all campaign_new users with no engagements */
    select
    min(m.created_at) as date,
    m.id,
    'bounced_at' as type
    from users m
    where not exists (select 1 from engagements e where e.member_id = m.id limit 1)
    group by 2
) union (
    /* engaged - just find all engaged users  */
    select
    e.day,
    e.id,
    'engaged_at' as type
    from e
)

view: monthly_engagement_churn {
  derived_table: {
    sql: /* find all engagements */
with (
    select distinct date_trunc('day',e.created_at) as day, e.member_id as id
    from engagements e
    where
    {% condition date_month %} e.created_at {% endcondition %} and
    {% condition date_week %} e.created_at {% endcondition %} and
    {% condition date_date %} e.created_at {% endcondition %}
    group by 1,2
) as e
(
    /* churned - find all engagements where there is no enagagement within the next 30 days */
    select e.day as date, e.id, 'churned_at'  as type from e
    left join e e2 on e.id=e2.id and e2.day >e.day and e2.day < e.day + interval '30 days'
    where e2.day is null
) union (
    /* reengaged - find all engagements where the previous engagement was more than 30 days ago */
    select e.day as date, e.id, 'reengaged_at' as type from e
    left join e e2 on e.id=e2.id and e2.day < e.day and e2.day >= e.day - interval '30 days'
    left join e e3 on e.id=e3.id and e3.day < e.day - interval '30 days'
    where e2.day is null and e3.day is not null
) union (
    /* created - just find all campaign_new users */
    select min(m.created_at) as date,
    m.id,
    'created_at' as type
    from users m
    group by 2
) union (
    /* "bounced" = find all campaign_new users with no engagements */
    select
    min(m.created_at) as date,
    m.id,
    'bounced_at' as type
    from users m
    where not exists (select 1 from engagements e where e.member_id = m.id limit 1)
    group by 2
) union (
    /* engaged - just find all engaged users  */
    select
    e.day,
    e.id,
    'engaged_at' as type
    from e
) ;;
  }

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

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

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

  measure: bounced {
    description: "campaign_new members who signed this date and have no engagements"
    type: count_distinct
    sql: case when type='bounced_at' then ${id} else null end ;;
  }

  measure: churned {
    description: "members who have an engagement now but none in the next 30 days"
    type: count_distinct
    sql: case when type='churned_at' then ${id} else null end ;;
  }

  measure: reengaged {
    description: "members who have an engagement before 30 days ago, none between now and 30 days ago, and now have a campaign_new engagement"
    type: count_distinct
    sql: case when type='reengaged_at' then ${id} else null end ;;
  }

  measure: engaged {
    description: "members who have an engagement"
    type: count_distinct
    sql: case when type='engaged_at' then ${id} else null end ;;
  }

  measure: campaign_new {
    description: "campaign_new members who signed this date"
    type: count_distinct
    sql: case when type='created_at' then ${id} else null end ;;
  }
}

- view: monthly_engagement_churn
  derived_table:
    sql: /* find all engagements */
with (
    select distinct date_trunc('day',e.created_at) as day, e.member_id as id
    from engagements e
    where
    {% condition date_month %} e.created_at {% endcondition %} and
    {% condition date_week %} e.created_at {% endcondition %} and
    {% condition date_date %} e.created_at {% endcondition %}
    group by 1,2
) as e
(
    /* churned - find all engagements where there is no enagagement within the next 30 days */
    select e.day as date, e.id, 'churned_at'  as type from e
    left join e e2 on e.id=e2.id and e2.day >e.day and e2.day < e.day + interval '30 days'
    where e2.day is null
) union (
    /* reengaged - find all engagements where the previous engagement was more than 30 days ago */
    select e.day as date, e.id, 'reengaged_at' as type from e
    left join e e2 on e.id=e2.id and e2.day < e.day and e2.day >= e.day - interval '30 days'
    left join e e3 on e.id=e3.id and e3.day < e.day - interval '30 days'
    where e2.day is null and e3.day is not null
) union (
    /* created - just find all campaign_new users */
    select min(m.created_at) as date,
    m.id,
    'created_at' as type
    from users m
    group by 2
) union (
    /* "bounced" = find all campaign_new users with no engagements */
    select
    min(m.created_at) as date,
    m.id,
    'bounced_at' as type
    from users m
    where not exists (select 1 from engagements e where e.member_id = m.id limit 1)
    group by 2
) union (
    /* engaged - just find all engaged users  */
    select
    e.day,
    e.id,
    'engaged_at' as type
    from e
)
  fields:
  - dimension: type
    sql: ${TABLE}.type
  - dimension: date
    type: time
    sql: ${TABLE}.date
  - dimension: id
    sql: ${TABLE}.id
  - measure: bounced
    type: count_distinct
    description: campaign_new members who signed this date and have no engagements
    sql: case when type='bounced_at' then ${id} else null end
  - measure: churned
    type: count_distinct
    description: members who have an engagement now but none in the next 30 days
    sql: case when type='churned_at' then ${id} else null end
  - measure: reengaged
    type: count_distinct
    description: members who have an engagement before 30 days ago, none between now and 30 days ago, and now have a campaign_new engagement
    sql: case when type='reengaged_at' then ${id} else null end
  - measure: engaged
    type: count_distinct
    description: members who have an engagement
    sql: case when type='engaged_at' then ${id} else null end
  - measure: campaign_new
    type: count_distinct
    description: campaign_new members who signed this date
    sql: case when type='created_at' then ${id} else null end