SQL
Clothing app
Customer Churn View
Customer Churn View model for clothing app.
source-preserved
Blocks
customer_churn_view.sql · 143 lines
inferred
-- models/customer_churn_view.sql
{{ config(materialized='view') }}
WITH ALL_MONTHS AS (
SELECT
DISTINCT date_trunc('month', afm.first_bill_date)::date AS MONTHS
FROM
central_data.account_financial_mrr afm
UNION ALL
select date_trunc('month', current_date)::date UNION ALL
select DATEADD(month ,1, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,2, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,3, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,4, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,5, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,6, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,7, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,8, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,9, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,10, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,11, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,12, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,13, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,14, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,15, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,16, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,17, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,18, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,19, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,20, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,21, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,22, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,23, date_trunc('month', current_date))::date
),
PofX AS (
SELECT 1 AS months_since_signup, 0.917121048 as pofx, 'a' as cohort_subgroup UNION ALL
SELECT 2 , 0.841111016, 'a' UNION ALL
SELECT 3 , 0.771400617, 'a' UNION ALL
SELECT 4 , 0.707467742, 'a' UNION ALL
SELECT 5 , 0.648833558, 'a' UNION ALL
SELECT 6 , 0.595058913, 'a' UNION ALL
SELECT 7 , 0.545741055, 'a' UNION ALL
SELECT 8 , 0.500510609, 'a' UNION ALL
SELECT 9 , 0.459028815, 'a' UNION ALL
SELECT 10 , 0.420984989, 'a' UNION ALL
SELECT 11 , 0.386094195, 'a' UNION ALL
SELECT 12 , 0.354095113, 'a' UNION ALL
SELECT 13 , 0.324748082, 'a' UNION ALL
SELECT 14 , 0.297833303, 'a' UNION ALL
SELECT 15 , 0.273149191, 'a' UNION ALL
SELECT 16 , 0.250510874, 'a' UNION ALL
SELECT 17 , 0.229748796, 'a' UNION ALL
SELECT 18 , 0.210707457, 'a' UNION ALL
SELECT 19 , 0.193244245, 'a' UNION ALL
SELECT 20 , 0.177228365, 'a' UNION ALL
SELECT 21 , 0.162539865, 'a' UNION ALL
SELECT 22 , 0.149068732, 'a' UNION ALL
SELECT 23 , 0.136714072, 'a' UNION ALL
SELECT 24 , 0.125383354 , 'a'
),
REALIZED_COHORTS AS (
SELECT
date_trunc('month', afm.first_bill_date)::date first_bill_month
,date_trunc('month', afm.invoice_date)::date invoice_month
,FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer months_since_signup
--,count(afm.invoice__id) as cohort_size
,afm.account_id
,afm.churn_date
FROM
central_data.account_financial_mrr afm
WHERE
1=1
AND date_trunc('month', afm.invoice_date) <> date_trunc('month', current_date)
AND FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer <> 0
GROUP BY
1,2,3,4,5
)
SELECT
DISTINCT
AM.MONTHS AS revenue_month,
R.first_bill_month as start_month,
--R2.cohort_size AS cohort_size,
FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer months_since_signup,
--PofX.pofx,
--R.cohort_size as cohort_initial_size,
-- R.churn_date as ch1,
-- R2.churn_date as ch2,
R.account_id as a1,
-- R2.account_id as a2,
CASE
-- --
/* History */
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is null or R.churn_date > AM.MONTHS) THEN 1 -- Historical active = 1
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- historical not active (churn null or churn date < rev month) = 0
/* Future */
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- Projected not active = 0
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is null) THEN (PofX.pofx*1.0/OBSERVATION2.pofx) -- Projected active probability (PofX.pofx)
-- --
ELSE null
END as account_churn_probability,
-- PofX.pofx as O1,
-- OBSERVATION2.pofx as O2,
CASE
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) THEN 'Projection'
ELSE 'Actual'
END AS projection
FROM
ALL_MONTHS AM
LEFT JOIN REALIZED_COHORTS R ON (
AM.MONTHS >= R.first_bill_month
AND R.months_since_signup = 1
)
LEFT JOIN REALIZED_COHORTS R2 ON (
AM.MONTHS = R2.invoice_month
AND R2.first_bill_month = R.first_bill_month
-- AND R2.account_id = <% parameter churned_account_id %> -- churned account
-- AND R2.account_id = <% parameter active_account_id %> -- non-churned account
)
LEFT JOIN PofX ON (
PofX.months_since_signup = FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer
)
LEFT JOIN PofX OBSERVATION2 ON (
OBSERVATION2.months_since_signup = (FLOOR((current_date - R.first_bill_month)*1.0/30)-1)::integer
)
WHERE
1=1
AND NOT (PofX.pofx is null AND R2.account_id is null)
-- AND R.first_bill_month = '2013-01-01'::date
-- AND R.account_id = <% parameter churned_account_id %> -- churned account
-- AND R.account_id = <% parameter active_account_id %> -- non-churned account
-- ORDER BY
-- 2,1,3
# models/schema.yml
version: 2
models:
- name: customer_churn_view
columns:
- name: months_since_signup
- name: account_churn_probability
- name: account_id
- name: projectionAssumptions
- 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.
customer_churn_view.sql · 129 lines
source-preserved
validation: not checked
WITH ALL_MONTHS AS (
SELECT
DISTINCT date_trunc('month', afm.first_bill_date)::date AS MONTHS
FROM
central_data.account_financial_mrr afm
UNION ALL
select date_trunc('month', current_date)::date UNION ALL
select DATEADD(month ,1, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,2, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,3, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,4, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,5, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,6, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,7, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,8, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,9, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,10, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,11, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,12, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,13, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,14, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,15, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,16, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,17, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,18, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,19, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,20, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,21, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,22, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,23, date_trunc('month', current_date))::date
),
PofX AS (
SELECT 1 AS months_since_signup, 0.917121048 as pofx, 'a' as cohort_subgroup UNION ALL
SELECT 2 , 0.841111016, 'a' UNION ALL
SELECT 3 , 0.771400617, 'a' UNION ALL
SELECT 4 , 0.707467742, 'a' UNION ALL
SELECT 5 , 0.648833558, 'a' UNION ALL
SELECT 6 , 0.595058913, 'a' UNION ALL
SELECT 7 , 0.545741055, 'a' UNION ALL
SELECT 8 , 0.500510609, 'a' UNION ALL
SELECT 9 , 0.459028815, 'a' UNION ALL
SELECT 10 , 0.420984989, 'a' UNION ALL
SELECT 11 , 0.386094195, 'a' UNION ALL
SELECT 12 , 0.354095113, 'a' UNION ALL
SELECT 13 , 0.324748082, 'a' UNION ALL
SELECT 14 , 0.297833303, 'a' UNION ALL
SELECT 15 , 0.273149191, 'a' UNION ALL
SELECT 16 , 0.250510874, 'a' UNION ALL
SELECT 17 , 0.229748796, 'a' UNION ALL
SELECT 18 , 0.210707457, 'a' UNION ALL
SELECT 19 , 0.193244245, 'a' UNION ALL
SELECT 20 , 0.177228365, 'a' UNION ALL
SELECT 21 , 0.162539865, 'a' UNION ALL
SELECT 22 , 0.149068732, 'a' UNION ALL
SELECT 23 , 0.136714072, 'a' UNION ALL
SELECT 24 , 0.125383354 , 'a'
),
REALIZED_COHORTS AS (
SELECT
date_trunc('month', afm.first_bill_date)::date first_bill_month
,date_trunc('month', afm.invoice_date)::date invoice_month
,FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer months_since_signup
--,count(afm.invoice__id) as cohort_size
,afm.account_id
,afm.churn_date
FROM
central_data.account_financial_mrr afm
WHERE
1=1
AND date_trunc('month', afm.invoice_date) <> date_trunc('month', current_date)
AND FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer <> 0
GROUP BY
1,2,3,4,5
)
SELECT
DISTINCT
AM.MONTHS AS revenue_month,
R.first_bill_month as start_month,
--R2.cohort_size AS cohort_size,
FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer months_since_signup,
--PofX.pofx,
--R.cohort_size as cohort_initial_size,
-- R.churn_date as ch1,
-- R2.churn_date as ch2,
R.account_id as a1,
-- R2.account_id as a2,
CASE
-- --
/* History */
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is null or R.churn_date > AM.MONTHS) THEN 1 -- Historical active = 1
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- historical not active (churn null or churn date < rev month) = 0
/* Future */
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- Projected not active = 0
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is null) THEN (PofX.pofx*1.0/OBSERVATION2.pofx) -- Projected active probability (PofX.pofx)
-- --
ELSE null
END as account_churn_probability,
-- PofX.pofx as O1,
-- OBSERVATION2.pofx as O2,
CASE
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) THEN 'Projection'
ELSE 'Actual'
END AS projection
FROM
ALL_MONTHS AM
LEFT JOIN REALIZED_COHORTS R ON (
AM.MONTHS >= R.first_bill_month
AND R.months_since_signup = 1
)
LEFT JOIN REALIZED_COHORTS R2 ON (
AM.MONTHS = R2.invoice_month
AND R2.first_bill_month = R.first_bill_month
-- AND R2.account_id = <% parameter churned_account_id %> -- churned account
-- AND R2.account_id = <% parameter active_account_id %> -- non-churned account
)
LEFT JOIN PofX ON (
PofX.months_since_signup = FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer
)
LEFT JOIN PofX OBSERVATION2 ON (
OBSERVATION2.months_since_signup = (FLOOR((current_date - R.first_bill_month)*1.0/30)-1)::integer
)
WHERE
1=1
AND NOT (PofX.pofx is null AND R2.account_id is null)
-- AND R.first_bill_month = '2013-01-01'::date
-- AND R.account_id = <% parameter churned_account_id %> -- churned account
-- AND R.account_id = <% parameter active_account_id %> -- non-churned account
-- ORDER BY
-- 2,1,3customer_churn_view.view.lkml · 175 lines
inferred
view: customer_churn_view {
derived_table: {
sql: WITH ALL_MONTHS AS (
SELECT
DISTINCT date_trunc('month', afm.first_bill_date)::date AS MONTHS
FROM
central_data.account_financial_mrr afm
UNION ALL
select date_trunc('month', current_date)::date UNION ALL
select DATEADD(month ,1, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,2, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,3, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,4, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,5, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,6, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,7, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,8, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,9, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,10, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,11, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,12, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,13, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,14, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,15, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,16, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,17, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,18, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,19, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,20, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,21, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,22, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,23, date_trunc('month', current_date))::date
),
PofX AS (
SELECT 1 AS months_since_signup, 0.917121048 as pofx, 'a' as cohort_subgroup UNION ALL
SELECT 2 , 0.841111016, 'a' UNION ALL
SELECT 3 , 0.771400617, 'a' UNION ALL
SELECT 4 , 0.707467742, 'a' UNION ALL
SELECT 5 , 0.648833558, 'a' UNION ALL
SELECT 6 , 0.595058913, 'a' UNION ALL
SELECT 7 , 0.545741055, 'a' UNION ALL
SELECT 8 , 0.500510609, 'a' UNION ALL
SELECT 9 , 0.459028815, 'a' UNION ALL
SELECT 10 , 0.420984989, 'a' UNION ALL
SELECT 11 , 0.386094195, 'a' UNION ALL
SELECT 12 , 0.354095113, 'a' UNION ALL
SELECT 13 , 0.324748082, 'a' UNION ALL
SELECT 14 , 0.297833303, 'a' UNION ALL
SELECT 15 , 0.273149191, 'a' UNION ALL
SELECT 16 , 0.250510874, 'a' UNION ALL
SELECT 17 , 0.229748796, 'a' UNION ALL
SELECT 18 , 0.210707457, 'a' UNION ALL
SELECT 19 , 0.193244245, 'a' UNION ALL
SELECT 20 , 0.177228365, 'a' UNION ALL
SELECT 21 , 0.162539865, 'a' UNION ALL
SELECT 22 , 0.149068732, 'a' UNION ALL
SELECT 23 , 0.136714072, 'a' UNION ALL
SELECT 24 , 0.125383354 , 'a'
),
REALIZED_COHORTS AS (
SELECT
date_trunc('month', afm.first_bill_date)::date first_bill_month
,date_trunc('month', afm.invoice_date)::date invoice_month
,FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer months_since_signup
--,count(afm.invoice__id) as cohort_size
,afm.account_id
,afm.churn_date
FROM
central_data.account_financial_mrr afm
WHERE
1=1
AND date_trunc('month', afm.invoice_date) <> date_trunc('month', current_date)
AND FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer <> 0
GROUP BY
1,2,3,4,5
)
SELECT
DISTINCT
AM.MONTHS AS revenue_month,
R.first_bill_month as start_month,
--R2.cohort_size AS cohort_size,
FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer months_since_signup,
--PofX.pofx,
--R.cohort_size as cohort_initial_size,
-- R.churn_date as ch1,
-- R2.churn_date as ch2,
R.account_id as a1,
-- R2.account_id as a2,
CASE
-- --
/* History */
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is null or R.churn_date > AM.MONTHS) THEN 1 -- Historical active = 1
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- historical not active (churn null or churn date < rev month) = 0
/* Future */
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- Projected not active = 0
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is null) THEN (PofX.pofx*1.0/OBSERVATION2.pofx) -- Projected active probability (PofX.pofx)
-- --
ELSE null
END as account_churn_probability,
-- PofX.pofx as O1,
-- OBSERVATION2.pofx as O2,
CASE
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) THEN 'Projection'
ELSE 'Actual'
END AS projection
FROM
ALL_MONTHS AM
LEFT JOIN REALIZED_COHORTS R ON (
AM.MONTHS >= R.first_bill_month
AND R.months_since_signup = 1
)
LEFT JOIN REALIZED_COHORTS R2 ON (
AM.MONTHS = R2.invoice_month
AND R2.first_bill_month = R.first_bill_month
-- AND R2.account_id = <% parameter churned_account_id %> -- churned account
-- AND R2.account_id = <% parameter active_account_id %> -- non-churned account
)
LEFT JOIN PofX ON (
PofX.months_since_signup = FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer
)
LEFT JOIN PofX OBSERVATION2 ON (
OBSERVATION2.months_since_signup = (FLOOR((current_date - R.first_bill_month)*1.0/30)-1)::integer
)
WHERE
1=1
AND NOT (PofX.pofx is null AND R2.account_id is null)
-- AND R.first_bill_month = '2013-01-01'::date
-- AND R.account_id = <% parameter churned_account_id %> -- churned account
-- AND R.account_id = <% parameter active_account_id %> -- non-churned account
-- ORDER BY
-- 2,1,3 ;;
}
dimension: months_since_signup {
type: int
sql: ${TABLE}.months_since_signup ;;
}
dimension: account_churn_probability {
type: number
sql: ${TABLE}.account_churn_probability ;;
}
dimension: account_id {
type: int
sql: ${TABLE}.a1 ;;
}
dimension: projection {
type: string
sql: ${TABLE}.projection ;;
}
dimension_group: revenue_month {
type: time
timeframes: [date, month, week, year]
sql: ${TABLE}.revenue_month ;;
}
dimension_group: start_month {
type: time
timeframes: [date, month, week, year]
sql: ${TABLE}.start_month ;;
}
measure: count {
type: count
drill_fields: [detail*]
}
measure: customer_count {
type: sum
sql: ${account_churn_probability} ;;
}
}customer_churn_view.view.lookml · 167 lines
source-preserved
- view: customer_churn_view
derived_table:
sql: WITH ALL_MONTHS AS (
SELECT
DISTINCT date_trunc('month', afm.first_bill_date)::date AS MONTHS
FROM
central_data.account_financial_mrr afm
UNION ALL
select date_trunc('month', current_date)::date UNION ALL
select DATEADD(month ,1, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,2, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,3, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,4, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,5, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,6, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,7, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,8, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,9, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,10, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,11, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,12, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,13, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,14, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,15, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,16, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,17, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,18, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,19, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,20, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,21, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,22, date_trunc('month', current_date))::date UNION ALL
select DATEADD(month ,23, date_trunc('month', current_date))::date
),
PofX AS (
SELECT 1 AS months_since_signup, 0.917121048 as pofx, 'a' as cohort_subgroup UNION ALL
SELECT 2 , 0.841111016, 'a' UNION ALL
SELECT 3 , 0.771400617, 'a' UNION ALL
SELECT 4 , 0.707467742, 'a' UNION ALL
SELECT 5 , 0.648833558, 'a' UNION ALL
SELECT 6 , 0.595058913, 'a' UNION ALL
SELECT 7 , 0.545741055, 'a' UNION ALL
SELECT 8 , 0.500510609, 'a' UNION ALL
SELECT 9 , 0.459028815, 'a' UNION ALL
SELECT 10 , 0.420984989, 'a' UNION ALL
SELECT 11 , 0.386094195, 'a' UNION ALL
SELECT 12 , 0.354095113, 'a' UNION ALL
SELECT 13 , 0.324748082, 'a' UNION ALL
SELECT 14 , 0.297833303, 'a' UNION ALL
SELECT 15 , 0.273149191, 'a' UNION ALL
SELECT 16 , 0.250510874, 'a' UNION ALL
SELECT 17 , 0.229748796, 'a' UNION ALL
SELECT 18 , 0.210707457, 'a' UNION ALL
SELECT 19 , 0.193244245, 'a' UNION ALL
SELECT 20 , 0.177228365, 'a' UNION ALL
SELECT 21 , 0.162539865, 'a' UNION ALL
SELECT 22 , 0.149068732, 'a' UNION ALL
SELECT 23 , 0.136714072, 'a' UNION ALL
SELECT 24 , 0.125383354 , 'a'
),
REALIZED_COHORTS AS (
SELECT
date_trunc('month', afm.first_bill_date)::date first_bill_month
,date_trunc('month', afm.invoice_date)::date invoice_month
,FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer months_since_signup
--,count(afm.invoice__id) as cohort_size
,afm.account_id
,afm.churn_date
FROM
central_data.account_financial_mrr afm
WHERE
1=1
AND date_trunc('month', afm.invoice_date) <> date_trunc('month', current_date)
AND FLOOR((date_trunc('month', afm.invoice_date)::date - date_trunc('month', afm.first_bill_date)::date)*1.0/30)::integer <> 0
GROUP BY
1,2,3,4,5
)
SELECT
DISTINCT
AM.MONTHS AS revenue_month,
R.first_bill_month as start_month,
--R2.cohort_size AS cohort_size,
FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer months_since_signup,
--PofX.pofx,
--R.cohort_size as cohort_initial_size,
-- R.churn_date as ch1,
-- R2.churn_date as ch2,
R.account_id as a1,
-- R2.account_id as a2,
CASE
-- --
/* History */
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is null or R.churn_date > AM.MONTHS) THEN 1 -- Historical active = 1
WHEN (/* FH Criterion */ AM.MONTHS <= date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- historical not active (churn null or churn date < rev month) = 0
/* Future */
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is not null and R.churn_date < AM.MONTHS) THEN 0 -- Projected not active = 0
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) AND (R.churn_date is null) THEN (PofX.pofx*1.0/OBSERVATION2.pofx) -- Projected active probability (PofX.pofx)
-- --
ELSE null
END as account_churn_probability,
-- PofX.pofx as O1,
-- OBSERVATION2.pofx as O2,
CASE
WHEN (/* FH Criterion */ AM.MONTHS > date_trunc('month',current_date)) THEN 'Projection'
ELSE 'Actual'
END AS projection
FROM
ALL_MONTHS AM
LEFT JOIN REALIZED_COHORTS R ON (
AM.MONTHS >= R.first_bill_month
AND R.months_since_signup = 1
)
LEFT JOIN REALIZED_COHORTS R2 ON (
AM.MONTHS = R2.invoice_month
AND R2.first_bill_month = R.first_bill_month
-- AND R2.account_id = <% parameter churned_account_id %> -- churned account
-- AND R2.account_id = <% parameter active_account_id %> -- non-churned account
)
LEFT JOIN PofX ON (
PofX.months_since_signup = FLOOR((AM.MONTHS - R.first_bill_month)*1.0/30)::integer
)
LEFT JOIN PofX OBSERVATION2 ON (
OBSERVATION2.months_since_signup = (FLOOR((current_date - R.first_bill_month)*1.0/30)-1)::integer
)
WHERE
1=1
AND NOT (PofX.pofx is null AND R2.account_id is null)
-- AND R.first_bill_month = '2013-01-01'::date
-- AND R.account_id = <% parameter churned_account_id %> -- churned account
-- AND R.account_id = <% parameter active_account_id %> -- non-churned account
-- ORDER BY
-- 2,1,3
fields:
- dimension: months_since_signup
type: int
sql: ${TABLE}.months_since_signup
- dimension: account_churn_probability
type: number
sql: ${TABLE}.account_churn_probability
- dimension: account_id
type: int
sql: ${TABLE}.a1
- dimension: projection
type: string
sql: ${TABLE}.projection
- dimension_group: revenue_month
type: time
timeframes:
- date
- month
- week
- year
sql: ${TABLE}.revenue_month
- dimension_group: start_month
type: time
timeframes:
- date
- month
- week
- year
sql: ${TABLE}.start_month
- measure: count
type: count
drill_fields:
- detail*
- measure: customer_count
type: sum
sql: ${account_churn_probability}Contents
- customer_churn_view.sql
- customer_churn_view.sql
- customer_churn_view.view.lkml
- customer_churn_view.view.lookml