Active Subs Facts Day Aggr

-- models/active_subs_facts_day_aggr.sql
{{ config(materialized='table') }}

select
evt.wk as timeframe
,gs.day AS day
,sub.bop as bop_subs
,evt.adds as new_subs
,evt.reactivations
,-1*evt.cancels as cancellations
,-1*evt.pauses as pauses
,sub.eop as eop_subs
,(100.0*sub.eop/(case when sub.bop=0 then null else sub.bop end))-100.0 as eop_growth
,-1*coalesce(f.fails,0) as fails
,-1*coalesce(skp.skips,0) as skips
,sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0) as orders
,100.0*(evt.cancels-evt.reactivations)/(case when sub.bop=0 then null else sub.bop end) as churn
,100.0*(sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0))/(case when sub.eop=0 then null else sub.eop end) as order_rate
--,sub.bop+evt.adds+evt.reactivations-evt.cancels-sub.eop as eopsum
--,lag(sub.eop) over(order by evt.wk) - sub.bop as eoplag
from (
  select
  d.date as wk
  ,sum(case when so.had_add_event then 1 else 0 end) as adds
  ,sum(case when so.had_cancel_event then 1 else 0 end) as cancels
  ,sum(case when so.had_reactivation_event then 1 else 0 end) as reactivations
  ,sum(case when so.had_pause_event then 1 else 0 end) as pauses
  from misc.dates d
  left join analytics_stored_orders2 so on date_trunc('week',so.ship_date)=d.date and (so.had_add_event or so.had_cancel_event or so.had_reactivation_event)
  where 1=1
    and d.day_of_week=1 --Monday
    and d.date >=<% parameter start_date %>
    --and d.date >= current_date-'14 weeks'::interval
    and d.date <=current_date+'7 days'::interval
  group by 1
) evt
left join (
  select
  x.wk
  ,count(distinct case when sob.active_sub_before then x.user_id else null end) as bop
  ,count(distinct case when soe.is_active_sub then x.user_id else null end) as eop
  from (
    select
    date_trunc('week',so.ship_date)::date wk
    ,so.user_id
    ,min(so.ship_date) as first_intra_week_ship_date
    ,max(so.ship_date) as last_intra_week_ship_date
    from analytics_stored_orders2 so
    where 1=1
      --and d.day_of_week=1 --Monday
      --and so.ship_date >= current_date-'14 weeks'::interval
      --and d.date <=current_date
    group by 1,2
  ) x
  join analytics_stored_orders2 sob on sob.user_id=x.user_id and sob.ship_date=x.first_intra_week_ship_date
  join analytics_stored_orders2 soe on soe.user_id=x.user_id and soe.ship_date=x.last_intra_week_ship_date
  group by 1
) sub on sub.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as fails
  from analytics_stored_orders2 so
  where 1=1
    and so.is_active_sub
    and exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status='Failed'
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) f on f.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as skips
  from analytics_stored_orders2 so
  where 1=1
    --and so.user_plan_option_id>1
    and so.is_active_sub
    and not exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status in ('Paid','Failed','Scheduled')
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) skp on skp.wk=evt.wk
LEFT JOIN (
    SELECT generate_series AS day
    FROM GENERATE_SERIES(<% parameter series_start_date %>::TIMESTshift_aP, NOW(), '1 day')
) AS gs ON (gs.day >= evt.wk - 1 AND gs.day <= evt.wk + 5)
where 1=1

# models/schema.yml
version: 2

models:
  - name: active_subs_facts_day_aggr
    columns:
      - name: aggr_week
      - name: aggr
      - name: dummy_pivot
      - name: sub_pct_growth

Assumptions

• materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

select
evt.wk as timeframe
,gs.day AS day
,sub.bop as bop_subs
,evt.adds as new_subs
,evt.reactivations
,-1*evt.cancels as cancellations
,-1*evt.pauses as pauses
,sub.eop as eop_subs
,(100.0*sub.eop/(case when sub.bop=0 then null else sub.bop end))-100.0 as eop_growth
,-1*coalesce(f.fails,0) as fails
,-1*coalesce(skp.skips,0) as skips
,sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0) as orders
,100.0*(evt.cancels-evt.reactivations)/(case when sub.bop=0 then null else sub.bop end) as churn
,100.0*(sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0))/(case when sub.eop=0 then null else sub.eop end) as order_rate
--,sub.bop+evt.adds+evt.reactivations-evt.cancels-sub.eop as eopsum
--,lag(sub.eop) over(order by evt.wk) - sub.bop as eoplag
from (
  select
  d.date as wk
  ,sum(case when so.had_add_event then 1 else 0 end) as adds
  ,sum(case when so.had_cancel_event then 1 else 0 end) as cancels
  ,sum(case when so.had_reactivation_event then 1 else 0 end) as reactivations
  ,sum(case when so.had_pause_event then 1 else 0 end) as pauses
  from misc.dates d
  left join analytics_stored_orders2 so on date_trunc('week',so.ship_date)=d.date and (so.had_add_event or so.had_cancel_event or so.had_reactivation_event)
  where 1=1
    and d.day_of_week=1 --Monday
    and d.date >=<% parameter start_date %>
    --and d.date >= current_date-'14 weeks'::interval
    and d.date <=current_date+'7 days'::interval
  group by 1
) evt
left join (
  select
  x.wk
  ,count(distinct case when sob.active_sub_before then x.user_id else null end) as bop
  ,count(distinct case when soe.is_active_sub then x.user_id else null end) as eop
  from (
    select
    date_trunc('week',so.ship_date)::date wk
    ,so.user_id
    ,min(so.ship_date) as first_intra_week_ship_date
    ,max(so.ship_date) as last_intra_week_ship_date
    from analytics_stored_orders2 so
    where 1=1
      --and d.day_of_week=1 --Monday
      --and so.ship_date >= current_date-'14 weeks'::interval
      --and d.date <=current_date
    group by 1,2
  ) x
  join analytics_stored_orders2 sob on sob.user_id=x.user_id and sob.ship_date=x.first_intra_week_ship_date
  join analytics_stored_orders2 soe on soe.user_id=x.user_id and soe.ship_date=x.last_intra_week_ship_date
  group by 1
) sub on sub.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as fails
  from analytics_stored_orders2 so
  where 1=1
    and so.is_active_sub
    and exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status='Failed'
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) f on f.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as skips
  from analytics_stored_orders2 so
  where 1=1
    --and so.user_plan_option_id>1
    and so.is_active_sub
    and not exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status in ('Paid','Failed','Scheduled')
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) skp on skp.wk=evt.wk
LEFT JOIN (
    SELECT generate_series AS day
    FROM GENERATE_SERIES(<% parameter series_start_date %>::TIMESTshift_aP, NOW(), '1 day')
) AS gs ON (gs.day >= evt.wk - 1 AND gs.day <= evt.wk + 5)
where 1=1

view: active_subs_facts_day_aggr {
  derived_table: {
    sql: select
evt.wk as timeframe
,gs.day AS day
,sub.bop as bop_subs
,evt.adds as new_subs
,evt.reactivations
,-1*evt.cancels as cancellations
,-1*evt.pauses as pauses
,sub.eop as eop_subs
,(100.0*sub.eop/(case when sub.bop=0 then null else sub.bop end))-100.0 as eop_growth
,-1*coalesce(f.fails,0) as fails
,-1*coalesce(skp.skips,0) as skips
,sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0) as orders
,100.0*(evt.cancels-evt.reactivations)/(case when sub.bop=0 then null else sub.bop end) as churn
,100.0*(sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0))/(case when sub.eop=0 then null else sub.eop end) as order_rate
--,sub.bop+evt.adds+evt.reactivations-evt.cancels-sub.eop as eopsum
--,lag(sub.eop) over(order by evt.wk) - sub.bop as eoplag
from (
  select
  d.date as wk
  ,sum(case when so.had_add_event then 1 else 0 end) as adds
  ,sum(case when so.had_cancel_event then 1 else 0 end) as cancels
  ,sum(case when so.had_reactivation_event then 1 else 0 end) as reactivations
  ,sum(case when so.had_pause_event then 1 else 0 end) as pauses
  from misc.dates d
  left join analytics_stored_orders2 so on date_trunc('week',so.ship_date)=d.date and (so.had_add_event or so.had_cancel_event or so.had_reactivation_event)
  where 1=1
    and d.day_of_week=1 --Monday
    and d.date >=<% parameter start_date %>
    --and d.date >= current_date-'14 weeks'::interval
    and d.date <=current_date+'7 days'::interval
  group by 1
) evt
left join (
  select
  x.wk
  ,count(distinct case when sob.active_sub_before then x.user_id else null end) as bop
  ,count(distinct case when soe.is_active_sub then x.user_id else null end) as eop
  from (
    select
    date_trunc('week',so.ship_date)::date wk
    ,so.user_id
    ,min(so.ship_date) as first_intra_week_ship_date
    ,max(so.ship_date) as last_intra_week_ship_date
    from analytics_stored_orders2 so
    where 1=1
      --and d.day_of_week=1 --Monday
      --and so.ship_date >= current_date-'14 weeks'::interval
      --and d.date <=current_date
    group by 1,2
  ) x
  join analytics_stored_orders2 sob on sob.user_id=x.user_id and sob.ship_date=x.first_intra_week_ship_date
  join analytics_stored_orders2 soe on soe.user_id=x.user_id and soe.ship_date=x.last_intra_week_ship_date
  group by 1
) sub on sub.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as fails
  from analytics_stored_orders2 so
  where 1=1
    and so.is_active_sub
    and exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status='Failed'
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) f on f.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as skips
  from analytics_stored_orders2 so
  where 1=1
    --and so.user_plan_option_id>1
    and so.is_active_sub
    and not exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status in ('Paid','Failed','Scheduled')
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) skp on skp.wk=evt.wk
LEFT JOIN (
    SELECT generate_series AS day
    FROM GENERATE_SERIES(<% parameter series_start_date %>::TIMESTshift_aP, NOW(), '1 day')
) AS gs ON (gs.day >= evt.wk - 1 AND gs.day <= evt.wk + 5)
where 1=1 ;;
    persist_for: "24 hours"
  }

  dimension: aggr_week {
    type: time
    sql: ${TABLE}.timeframe ;;
  }

  dimension: aggr {
    type: time
    sql: ${TABLE}.day ;;
  }

  dimension: dummy_pivot {
    label: "x"
    sql: case when 1=1 then null end ;;
  }

  dimension: sub_pct_growth {
    label: "% Growth"
    type: max
    sql: ${TABLE}.eop_growth ;;
  }

  measure: bop_subs {
    label: "BOP Subs"
    type: max
    sql: ${TABLE}.bop_subs ;;
  }

  measure: new_subs {
    label: "New Subs"
    type: max
    sql: ${TABLE}.new_subs ;;
  }

  measure: reactivations {
    label: "Plus: Reactivations"
    type: max
    sql: ${TABLE}.reactivations ;;
  }

  measure: cancellations {
    label: "Less: Cancellations"
    type: max
    sql: ${TABLE}.cancellations ;;
  }

  measure: pauses {
    label: "Less: Cancellations (pause)"
    type: max
    sql: ${TABLE}.pauses ;;
  }

  measure: eop_subs {
    label: "EOP Active Subs"
    type: max
    sql: ${TABLE}.eop_subs ;;
  }

  measure: fails {
    label: "Less: Failed Charge"
    type: max
    sql: ${TABLE}.fails ;;
  }

  measure: skips {
    label: "Less: Skipped"
    type: max
    sql: ${TABLE}.skips ;;
  }

  measure: sub_with_deliveries {
    label: "Active Subs with Deliveries"
    type: max
    sql: ${TABLE}.orders ;;
  }

  measure: weekly_churn {
    type: max
    sql: ${TABLE}.churn ;;
  }

  measure: frac_receiving_deliveries {
    label: "Weekly Order Rate"
    type: max
    sql: ${TABLE}.order_rate/100 ;;
  }

  measure: pct_receiving_deliveries {
    label: "% Receiving Deliveries"
    type: max
    sql: ${TABLE}.order_rate ;;
  }
}

- view: active_subs_facts_day_aggr
  derived_table:
    sql: select
evt.wk as timeframe
,gs.day AS day
,sub.bop as bop_subs
,evt.adds as new_subs
,evt.reactivations
,-1*evt.cancels as cancellations
,-1*evt.pauses as pauses
,sub.eop as eop_subs
,(100.0*sub.eop/(case when sub.bop=0 then null else sub.bop end))-100.0 as eop_growth
,-1*coalesce(f.fails,0) as fails
,-1*coalesce(skp.skips,0) as skips
,sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0) as orders
,100.0*(evt.cancels-evt.reactivations)/(case when sub.bop=0 then null else sub.bop end) as churn
,100.0*(sub.eop - coalesce(f.fails,0) - coalesce(skp.skips,0))/(case when sub.eop=0 then null else sub.eop end) as order_rate
--,sub.bop+evt.adds+evt.reactivations-evt.cancels-sub.eop as eopsum
--,lag(sub.eop) over(order by evt.wk) - sub.bop as eoplag
from (
  select
  d.date as wk
  ,sum(case when so.had_add_event then 1 else 0 end) as adds
  ,sum(case when so.had_cancel_event then 1 else 0 end) as cancels
  ,sum(case when so.had_reactivation_event then 1 else 0 end) as reactivations
  ,sum(case when so.had_pause_event then 1 else 0 end) as pauses
  from misc.dates d
  left join analytics_stored_orders2 so on date_trunc('week',so.ship_date)=d.date and (so.had_add_event or so.had_cancel_event or so.had_reactivation_event)
  where 1=1
    and d.day_of_week=1 --Monday
    and d.date >=<% parameter start_date %>
    --and d.date >= current_date-'14 weeks'::interval
    and d.date <=current_date+'7 days'::interval
  group by 1
) evt
left join (
  select
  x.wk
  ,count(distinct case when sob.active_sub_before then x.user_id else null end) as bop
  ,count(distinct case when soe.is_active_sub then x.user_id else null end) as eop
  from (
    select
    date_trunc('week',so.ship_date)::date wk
    ,so.user_id
    ,min(so.ship_date) as first_intra_week_ship_date
    ,max(so.ship_date) as last_intra_week_ship_date
    from analytics_stored_orders2 so
    where 1=1
      --and d.day_of_week=1 --Monday
      --and so.ship_date >= current_date-'14 weeks'::interval
      --and d.date <=current_date
    group by 1,2
  ) x
  join analytics_stored_orders2 sob on sob.user_id=x.user_id and sob.ship_date=x.first_intra_week_ship_date
  join analytics_stored_orders2 soe on soe.user_id=x.user_id and soe.ship_date=x.last_intra_week_ship_date
  group by 1
) sub on sub.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as fails
  from analytics_stored_orders2 so
  where 1=1
    and so.is_active_sub
    and exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status='Failed'
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) f on f.wk=evt.wk
left join (
  select
  date_trunc('week',so.ship_date) wk
  ,count(distinct so.user_id) as skips
  from analytics_stored_orders2 so
  where 1=1
    --and so.user_plan_option_id>1
    and so.is_active_sub
    and not exists (
      select 1
      from analytics_orders o
      join analytics_delivery_windows dw on dw.id=o.delivery_window_id
      where 1=1
        and o.user_id=so.user_id
        and not o.is_archived
        and o.status in ('Paid','Failed','Scheduled')
        and date_trunc('week',dw.ship_date)=date_trunc('week',so.ship_date)
    )
  group by 1
) skp on skp.wk=evt.wk
LEFT JOIN (
    SELECT generate_series AS day
    FROM GENERATE_SERIES(<% parameter series_start_date %>::TIMESTshift_aP, NOW(), '1 day')
) AS gs ON (gs.day >= evt.wk - 1 AND gs.day <= evt.wk + 5)
where 1=1
    persist_for: 24 hours
  fields:
  - dimension: aggr_week
    type: time
    sql: ${TABLE}.timeframe
  - dimension: aggr
    type: time
    sql: ${TABLE}.day
  - dimension: dummy_pivot
    label: x
    sql: case when 1=1 then null end
  - dimension: sub_pct_growth
    type: max
    label: '% Growth'
    sql: ${TABLE}.eop_growth
  - measure: bop_subs
    type: max
    label: BOP Subs
    sql: ${TABLE}.bop_subs
  - measure: new_subs
    type: max
    label: New Subs
    sql: ${TABLE}.new_subs
  - measure: reactivations
    type: max
    label: 'Plus: Reactivations'
    sql: ${TABLE}.reactivations
  - measure: cancellations
    type: max
    label: 'Less: Cancellations'
    sql: ${TABLE}.cancellations
  - measure: pauses
    type: max
    label: 'Less: Cancellations (pause)'
    sql: ${TABLE}.pauses
  - measure: eop_subs
    type: max
    label: EOP Active Subs
    sql: ${TABLE}.eop_subs
  - measure: fails
    type: max
    label: 'Less: Failed Charge'
    sql: ${TABLE}.fails
  - measure: skips
    type: max
    label: 'Less: Skipped'
    sql: ${TABLE}.skips
  - measure: sub_with_deliveries
    type: max
    label: Active Subs with Deliveries
    sql: ${TABLE}.orders
  - measure: weekly_churn
    type: max
    sql: ${TABLE}.churn
  - measure: frac_receiving_deliveries
    type: max
    label: Weekly Order Rate
    sql: ${TABLE}.order_rate/100
  - measure: pct_receiving_deliveries
    type: max
    label: '% Receiving Deliveries'
    sql: ${TABLE}.order_rate