Customers Earned Churned Last 7

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

select
# acq.iov_vs_aov, ltr.average_ltr,
count(*) as customers_earned
, max(ltr.average_ltr) as 12_month_average_ltr
, sum(acq.iov_vs_aov * ltr.average_ltr) as 12_month_ltr_earned
, b.*
from
(
  # Initial OV as % of AOV over last 6 months
  select soc.user_id, sum(soc.total / 100.0) / count(*) /
    (
    select sum(soc.total / 100.0) / count(*)
    from order_consolidated soc
    where soc.completed_at between date_add(curdate(), INTERVAL -6 month) and date_add(curdate(), INTERVAL -1 second)
    ) as iov_vs_aov

  from order_consolidated soc
  where soc.completed_at between (date_add(curdate(), INTERVAL -8 day)) and (date_add(curdate(), INTERVAL -1 second))
  group by 1
) acq
inner join
(
  select sum(12_month_ltr) / count(*) as average_ltr
  from (
    select
    fp.user_id
    , sum(soc.total / 100.0) as 12_month_ltr
  # , soc.completed_at
  # , fp.date_first_purchase
  # , datediff(soc.completed_at, fp.date_first_purchase)
    from

    (
      select soc.user_id, min(completed_at) as date_first_purchase
      from order_consolidated soc
      group by 1
      having date_first_purchase BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
    ) fp
     left join order_consolidated as soc
        on soc.user_id = fp.user_id
          and datediff(soc.completed_at, fp.date_first_purchase) <= 365
    group by 1

  ) a
) ltr
inner join (
select
ch.churned_customers as customers_churned
, (revenue_per_0_12 - revenue_per_13plus) as 12_month_lost_ltr_per_churn
, ch.churned_customers * (revenue_per_0_12 - revenue_per_13plus) as 12_month_ltr_churned
from (
  # metrics on active vs lapsed
  select
  (
    # 12-month revenue per 0-12 customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_0_12
    from
    (
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
      and soc.user_id is not null
      group by 1
    ) a
    inner join order_consolidated soc
    on soc.user_id = a.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
  ) as revenue_per_0_12,
  (
    # 12-month revenue per 13+ customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_13plus
  # , sum(soc.total/100.0) as revenue
  # , count(*) as users
    from (
      # acquired ast least 1 year prior as of 1 year ago
      select soc.user_id
      from order_consolidated soc
      where completed_at <= date_add(curdate(), interval -730 day)
    ) u
    # their orders in the last year
    left join order_consolidated soc
    on soc.user_id = u.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
    left join (
      # and didn't buy in the next 12 months
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at between date_add(curdate(), INTERVAL -730 day) AND date_add(curdate(), interval -365 day)
      group by 1
    ) b
    on b.user_id = u.`user_id`
    where b.user_id is null
  ) as revenue_per_13plus
) a
inner join (
  # churned from active to lapsed
  select count(1) as churned_customers
  from (
    select soc.user_id
    , min(soc.completed_at) as first_order_date
    , max(soc.completed_at) as last_order_date
    from order_consolidated soc
    where soc.completed_at between  (date_add(curdate(), INTERVAL -371 DAY)) and (date_add(curdate(), INTERVAL -1 second))
    group by 1
    having first_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
    and last_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
  ) a
) ch
) b
;

# models/schema.yml
version: 2

models:
  - name: customers_earned_churned_last_7
    columns:
      - name: customers_earned
      - name: 12_month_average_ltr
      - name: 12_month_ltr_earned
      - name: customers_churned
      - name: 12_month_lost_ltr_per_churn
      - name: 12_month_ltr_churned
      - name: 12_month_average_ltr_earned
      - name: 12_month_net_ltr_earn_churn
      - name: annualized_earn_churn
      - name: estimated_ecomm_run_rate

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
# acq.iov_vs_aov, ltr.average_ltr,
count(*) as customers_earned
, max(ltr.average_ltr) as 12_month_average_ltr
, sum(acq.iov_vs_aov * ltr.average_ltr) as 12_month_ltr_earned
, b.*
from
(
  # Initial OV as % of AOV over last 6 months
  select soc.user_id, sum(soc.total / 100.0) / count(*) /
    (
    select sum(soc.total / 100.0) / count(*)
    from order_consolidated soc
    where soc.completed_at between date_add(curdate(), INTERVAL -6 month) and date_add(curdate(), INTERVAL -1 second)
    ) as iov_vs_aov

  from order_consolidated soc
  where soc.completed_at between (date_add(curdate(), INTERVAL -8 day)) and (date_add(curdate(), INTERVAL -1 second))
  group by 1
) acq
inner join
(
  select sum(12_month_ltr) / count(*) as average_ltr
  from (
    select
    fp.user_id
    , sum(soc.total / 100.0) as 12_month_ltr
  # , soc.completed_at
  # , fp.date_first_purchase
  # , datediff(soc.completed_at, fp.date_first_purchase)
    from

    (
      select soc.user_id, min(completed_at) as date_first_purchase
      from order_consolidated soc
      group by 1
      having date_first_purchase BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
    ) fp
     left join order_consolidated as soc
        on soc.user_id = fp.user_id
          and datediff(soc.completed_at, fp.date_first_purchase) <= 365
    group by 1

  ) a
) ltr
inner join (
select
ch.churned_customers as customers_churned
, (revenue_per_0_12 - revenue_per_13plus) as 12_month_lost_ltr_per_churn
, ch.churned_customers * (revenue_per_0_12 - revenue_per_13plus) as 12_month_ltr_churned
from (
  # metrics on active vs lapsed
  select
  (
    # 12-month revenue per 0-12 customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_0_12
    from
    (
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
      and soc.user_id is not null
      group by 1
    ) a
    inner join order_consolidated soc
    on soc.user_id = a.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
  ) as revenue_per_0_12,
  (
    # 12-month revenue per 13+ customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_13plus
  # , sum(soc.total/100.0) as revenue
  # , count(*) as users
    from (
      # acquired ast least 1 year prior as of 1 year ago
      select soc.user_id
      from order_consolidated soc
      where completed_at <= date_add(curdate(), interval -730 day)
    ) u
    # their orders in the last year
    left join order_consolidated soc
    on soc.user_id = u.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
    left join (
      # and didn't buy in the next 12 months
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at between date_add(curdate(), INTERVAL -730 day) AND date_add(curdate(), interval -365 day)
      group by 1
    ) b
    on b.user_id = u.`user_id`
    where b.user_id is null
  ) as revenue_per_13plus
) a
inner join (
  # churned from active to lapsed
  select count(1) as churned_customers
  from (
    select soc.user_id
    , min(soc.completed_at) as first_order_date
    , max(soc.completed_at) as last_order_date
    from order_consolidated soc
    where soc.completed_at between  (date_add(curdate(), INTERVAL -371 DAY)) and (date_add(curdate(), INTERVAL -1 second))
    group by 1
    having first_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
    and last_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
  ) a
) ch
) b
;

view: customers_earned_churned_last_7 {
  derived_table: {
    sql: select
# acq.iov_vs_aov, ltr.average_ltr,
count(*) as customers_earned
, max(ltr.average_ltr) as 12_month_average_ltr
, sum(acq.iov_vs_aov * ltr.average_ltr) as 12_month_ltr_earned
, b.*
from
(
  # Initial OV as % of AOV over last 6 months
  select soc.user_id, sum(soc.total / 100.0) / count(*) /
    (
    select sum(soc.total / 100.0) / count(*)
    from order_consolidated soc
    where soc.completed_at between date_add(curdate(), INTERVAL -6 month) and date_add(curdate(), INTERVAL -1 second)
    ) as iov_vs_aov

  from order_consolidated soc
  where soc.completed_at between (date_add(curdate(), INTERVAL -8 day)) and (date_add(curdate(), INTERVAL -1 second))
  group by 1
) acq
inner join
(
  select sum(12_month_ltr) / count(*) as average_ltr
  from (
    select
    fp.user_id
    , sum(soc.total / 100.0) as 12_month_ltr
  # , soc.completed_at
  # , fp.date_first_purchase
  # , datediff(soc.completed_at, fp.date_first_purchase)
    from

    (
      select soc.user_id, min(completed_at) as date_first_purchase
      from order_consolidated soc
      group by 1
      having date_first_purchase BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
    ) fp
     left join order_consolidated as soc
        on soc.user_id = fp.user_id
          and datediff(soc.completed_at, fp.date_first_purchase) <= 365
    group by 1

  ) a
) ltr
inner join (
select
ch.churned_customers as customers_churned
, (revenue_per_0_12 - revenue_per_13plus) as 12_month_lost_ltr_per_churn
, ch.churned_customers * (revenue_per_0_12 - revenue_per_13plus) as 12_month_ltr_churned
from (
  # metrics on active vs lapsed
  select
  (
    # 12-month revenue per 0-12 customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_0_12
    from
    (
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
      and soc.user_id is not null
      group by 1
    ) a
    inner join order_consolidated soc
    on soc.user_id = a.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
  ) as revenue_per_0_12,
  (
    # 12-month revenue per 13+ customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_13plus
  # , sum(soc.total/100.0) as revenue
  # , count(*) as users
    from (
      # acquired ast least 1 year prior as of 1 year ago
      select soc.user_id
      from order_consolidated soc
      where completed_at <= date_add(curdate(), interval -730 day)
    ) u
    # their orders in the last year
    left join order_consolidated soc
    on soc.user_id = u.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
    left join (
      # and didn't buy in the next 12 months
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at between date_add(curdate(), INTERVAL -730 day) AND date_add(curdate(), interval -365 day)
      group by 1
    ) b
    on b.user_id = u.`user_id`
    where b.user_id is null
  ) as revenue_per_13plus
) a
inner join (
  # churned from active to lapsed
  select count(1) as churned_customers
  from (
    select soc.user_id
    , min(soc.completed_at) as first_order_date
    , max(soc.completed_at) as last_order_date
    from order_consolidated soc
    where soc.completed_at between  (date_add(curdate(), INTERVAL -371 DAY)) and (date_add(curdate(), INTERVAL -1 second))
    group by 1
    having first_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
    and last_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
  ) a
) ch
) b
; ;;
    persist_for: "12 hours"
  }

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

  dimension: 12_month_average_ltr {
    type: number
    sql: ${TABLE}.12_month_average_ltr ;;
  }

  dimension: 12_month_ltr_earned {
    type: number
    sql: ${TABLE}.12_month_ltr_earned ;;
  }

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

  dimension: 12_month_lost_ltr_per_churn {
    type: number
    sql: ${TABLE}.12_month_lost_ltr_per_churn ;;
  }

  dimension: 12_month_ltr_churned {
    type: number
    sql: ${TABLE}.12_month_ltr_churned ;;
  }

  dimension: 12_month_average_ltr_earned {
    type: number
    sql: ${12_month_ltr_earned} / ${customers_earned} ;;
  }

  dimension: 12_month_net_ltr_earn_churn {
    type: number
    sql: ${12_month_ltr_earned} - ${12_month_ltr_churned} ;;
  }

  dimension: annualized_earn_churn {
    type: number
    sql: (${12_month_ltr_earned} - ${12_month_ltr_churned}) * 52 ;;
  }

  dimension: estimated_ecomm_run_rate {
    type: number
    sql: (${12_month_ltr_earned} - ${12_month_ltr_churned}) * 52 / 0.65 ;;
  }

  measure: count {
    type: count
  }
}

- view: customers_earned_churned_last_7
  derived_table:
    sql: select
# acq.iov_vs_aov, ltr.average_ltr,
count(*) as customers_earned
, max(ltr.average_ltr) as 12_month_average_ltr
, sum(acq.iov_vs_aov * ltr.average_ltr) as 12_month_ltr_earned
, b.*
from
(
  # Initial OV as % of AOV over last 6 months
  select soc.user_id, sum(soc.total / 100.0) / count(*) /
    (
    select sum(soc.total / 100.0) / count(*)
    from order_consolidated soc
    where soc.completed_at between date_add(curdate(), INTERVAL -6 month) and date_add(curdate(), INTERVAL -1 second)
    ) as iov_vs_aov

  from order_consolidated soc
  where soc.completed_at between (date_add(curdate(), INTERVAL -8 day)) and (date_add(curdate(), INTERVAL -1 second))
  group by 1
) acq
inner join
(
  select sum(12_month_ltr) / count(*) as average_ltr
  from (
    select
    fp.user_id
    , sum(soc.total / 100.0) as 12_month_ltr
  # , soc.completed_at
  # , fp.date_first_purchase
  # , datediff(soc.completed_at, fp.date_first_purchase)
    from

    (
      select soc.user_id, min(completed_at) as date_first_purchase
      from order_consolidated soc
      group by 1
      having date_first_purchase BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
    ) fp
     left join order_consolidated as soc
        on soc.user_id = fp.user_id
          and datediff(soc.completed_at, fp.date_first_purchase) <= 365
    group by 1

  ) a
) ltr
inner join (
select
ch.churned_customers as customers_churned
, (revenue_per_0_12 - revenue_per_13plus) as 12_month_lost_ltr_per_churn
, ch.churned_customers * (revenue_per_0_12 - revenue_per_13plus) as 12_month_ltr_churned
from (
  # metrics on active vs lapsed
  select
  (
    # 12-month revenue per 0-12 customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_0_12
    from
    (
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at BETWEEN (date_add(curdate(), INTERVAL -730 day)) AND (date_add(date_add(curdate(), INTERVAL -365 day), INTERVAL -1 second))
      and soc.user_id is not null
      group by 1
    ) a
    inner join order_consolidated soc
    on soc.user_id = a.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
  ) as revenue_per_0_12,
  (
    # 12-month revenue per 13+ customer
    select sum(soc.total / 100.0) / count(*) as revenue_per_13plus
  # , sum(soc.total/100.0) as revenue
  # , count(*) as users
    from (
      # acquired ast least 1 year prior as of 1 year ago
      select soc.user_id
      from order_consolidated soc
      where completed_at <= date_add(curdate(), interval -730 day)
    ) u
    # their orders in the last year
    left join order_consolidated soc
    on soc.user_id = u.user_id
    and soc.completed_at between date_add(curdate(), INTERVAL -364 day) AND date_add(curdate(), INTERVAL -1 second)
    left join (
      # and didn't buy in the next 12 months
      select soc.user_id
      from order_consolidated soc
      where soc.completed_at between date_add(curdate(), INTERVAL -730 day) AND date_add(curdate(), interval -365 day)
      group by 1
    ) b
    on b.user_id = u.`user_id`
    where b.user_id is null
  ) as revenue_per_13plus
) a
inner join (
  # churned from active to lapsed
  select count(1) as churned_customers
  from (
    select soc.user_id
    , min(soc.completed_at) as first_order_date
    , max(soc.completed_at) as last_order_date
    from order_consolidated soc
    where soc.completed_at between  (date_add(curdate(), INTERVAL -371 DAY)) and (date_add(curdate(), INTERVAL -1 second))
    group by 1
    having first_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
    and last_order_date between date_add(curdate(), INTERVAL -371 DAY) and date_add(curdate(), INTERVAL -364 day)
  ) a
) ch
) b
;
    persist_for: 12 hours
  fields:
  - dimension: customers_earned
    type: number
    sql: ${TABLE}.customers_earned
  - dimension: 12_month_average_ltr
    type: number
    sql: ${TABLE}.12_month_average_ltr
  - dimension: 12_month_ltr_earned
    type: number
    sql: ${TABLE}.12_month_ltr_earned
  - dimension: customers_churned
    type: number
    sql: ${TABLE}.customers_churned
  - dimension: 12_month_lost_ltr_per_churn
    type: number
    sql: ${TABLE}.12_month_lost_ltr_per_churn
  - dimension: 12_month_ltr_churned
    type: number
    sql: ${TABLE}.12_month_ltr_churned
  - dimension: 12_month_average_ltr_earned
    type: number
    sql: ${12_month_ltr_earned} / ${customers_earned}
  - dimension: 12_month_net_ltr_earn_churn
    type: number
    sql: ${12_month_ltr_earned} - ${12_month_ltr_churned}
  - dimension: annualized_earn_churn
    type: number
    sql: (${12_month_ltr_earned} - ${12_month_ltr_churned}) * 52
  - dimension: estimated_ecomm_run_rate
    type: number
    sql: (${12_month_ltr_earned} - ${12_month_ltr_churned}) * 52 / 0.65
  - measure: count
    type: count