data.ml
dbt Dating platform

Churn

Churn model for dating platform.

inferred

Blocks

churn.sql · 79 lines
inferred 
-- models/churn.sql
{{ config(materialized='table') }}

SELECT  site, source, calendar_date,
        SUM(upgrades) AS upgrades,
        SUM(downgrades) AS downgrades,
        SUM(leads) AS leads,
        SUM(renewals) AS renewals
FROM  (
  SELECT  U.site,
           U.source,
           TRUNC(U.created_at) AS calendar_date,
           COUNT(1) AS leads,
           0 AS downgrades,
           0 AS upgrades,
           0 AS renewals
    FROM  users U
    GROUP BY U.site,
           U.source,
           trunc(U.created_at)
  UNION ALL
  SELECT    U.site,
            U.source,
            TRUNC(S.next_renewal_on) AS calendar_date,
            0,
            COUNT(1) AS downgrades,
            0,
            0 AS renewals
  FROM  subscriptions S
        JOIN users U ON S.user_id = U.id
        JOIN user_transactions T
        ON  S.user_id = T.user_id
            AND T.type = 'upgrade'
  WHERE S.auto_renew = false
        AND S.next_renewal_on < GETDATE()
  GROUP BY U.site,
           U.source,
           trunc(S.next_renewal_on)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          COUNT(1) AS upgrades,
          0 AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'upgrade'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          0,
          COUNT(1) AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'renewal'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
         ) T
GROUP BY site,  source, calendar_date

# models/schema.yml
version: 2

models:
  - name: churn
    columns:
      - name: day
      - name: month
      - name: site
      - name: source

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.
churn.sql · 65 lines
source-preserved validation: parses 
SELECT  site, source, calendar_date,
        SUM(upgrades) AS upgrades,
        SUM(downgrades) AS downgrades,
        SUM(leads) AS leads,
        SUM(renewals) AS renewals
FROM  (
  SELECT  U.site,
           U.source,
           TRUNC(U.created_at) AS calendar_date,
           COUNT(1) AS leads,
           0 AS downgrades,
           0 AS upgrades,
           0 AS renewals
    FROM  users U
    GROUP BY U.site,
           U.source,
           trunc(U.created_at)
  UNION ALL
  SELECT    U.site,
            U.source,
            TRUNC(S.next_renewal_on) AS calendar_date,
            0,
            COUNT(1) AS downgrades,
            0,
            0 AS renewals
  FROM  subscriptions S
        JOIN users U ON S.user_id = U.id
        JOIN user_transactions T
        ON  S.user_id = T.user_id
            AND T.type = 'upgrade'
  WHERE S.auto_renew = false
        AND S.next_renewal_on < GETDATE()
  GROUP BY U.site,
           U.source,
           trunc(S.next_renewal_on)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          COUNT(1) AS upgrades,
          0 AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'upgrade'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          0,
          COUNT(1) AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'renewal'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
         ) T
GROUP BY site,  source, calendar_date
churn.view.lkml · 108 lines
inferred 
view: churn {
  derived_table: {
    sql: SELECT  site, source, calendar_date,
        SUM(upgrades) AS upgrades,
        SUM(downgrades) AS downgrades,
        SUM(leads) AS leads,
        SUM(renewals) AS renewals
FROM  (
  SELECT  U.site,
           U.source,
           TRUNC(U.created_at) AS calendar_date,
           COUNT(1) AS leads,
           0 AS downgrades,
           0 AS upgrades,
           0 AS renewals
    FROM  users U
    GROUP BY U.site,
           U.source,
           trunc(U.created_at)
  UNION ALL
  SELECT    U.site,
            U.source,
            TRUNC(S.next_renewal_on) AS calendar_date,
            0,
            COUNT(1) AS downgrades,
            0,
            0 AS renewals
  FROM  subscriptions S
        JOIN users U ON S.user_id = U.id
        JOIN user_transactions T
        ON  S.user_id = T.user_id
            AND T.type = 'upgrade'
  WHERE S.auto_renew = false
        AND S.next_renewal_on < GETDATE()
  GROUP BY U.site,
           U.source,
           trunc(S.next_renewal_on)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          COUNT(1) AS upgrades,
          0 AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'upgrade'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          0,
          COUNT(1) AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'renewal'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
         ) T
GROUP BY site,  source, calendar_date ;;
    persist_for: "1 hour"
  }

  dimension: day {
    type: date
    sql: ${TABLE}.calendar_date ;;
  }

  dimension: month {
    type: date_month
    sql: ${TABLE}.calendar_date ;;
  }

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

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

  measure: leads {
    type: sum
    sql: ${TABLE}.leads ;;
  }

  measure: downgrades {
    type: sum
    sql: ${TABLE}.downgrades ;;
  }

  measure: renewals {
    type: sum
    sql: ${TABLE}.renewals ;;
  }

  measure: upgrades {
    type: sum
    sql: ${TABLE}.upgrades ;;
  }
}
churn.view.lookml · 91 lines
source-preserved 
- view: churn
  derived_table:
    sql: SELECT  site, source, calendar_date,
        SUM(upgrades) AS upgrades,
        SUM(downgrades) AS downgrades,
        SUM(leads) AS leads,
        SUM(renewals) AS renewals
FROM  (
  SELECT  U.site,
           U.source,
           TRUNC(U.created_at) AS calendar_date,
           COUNT(1) AS leads,
           0 AS downgrades,
           0 AS upgrades,
           0 AS renewals
    FROM  users U
    GROUP BY U.site,
           U.source,
           trunc(U.created_at)
  UNION ALL
  SELECT    U.site,
            U.source,
            TRUNC(S.next_renewal_on) AS calendar_date,
            0,
            COUNT(1) AS downgrades,
            0,
            0 AS renewals
  FROM  subscriptions S
        JOIN users U ON S.user_id = U.id
        JOIN user_transactions T
        ON  S.user_id = T.user_id
            AND T.type = 'upgrade'
  WHERE S.auto_renew = false
        AND S.next_renewal_on < GETDATE()
  GROUP BY U.site,
           U.source,
           trunc(S.next_renewal_on)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          COUNT(1) AS upgrades,
          0 AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'upgrade'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
  UNION ALL
  SELECT  U.site,
          U.source,
          TRUNC(T.created_at) AS calendar_date,
          0,
          0,
          0,
          COUNT(1) AS renewals
  FROM    user_transactions  T
            JOIN users U ON T.user_id = U.id
  WHERE   T.type = 'renewal'
  GROUP BY U.site,
           U.source,
           trunc(T.created_at)
         ) T
GROUP BY site,  source, calendar_date
    persist_for: 1 hour
  fields:
  - dimension: day
    type: date
    sql: ${TABLE}.calendar_date
  - dimension: month
    type: date_month
    sql: ${TABLE}.calendar_date
  - dimension: site
    sql: ${TABLE}.site
  - dimension: source
    sql: ${TABLE}.source
  - measure: leads
    type: sum
    sql: ${TABLE}.leads
  - measure: downgrades
    type: sum
    sql: ${TABLE}.downgrades
  - measure: renewals
    type: sum
    sql: ${TABLE}.renewals
  - measure: upgrades
    type: sum
    sql: ${TABLE}.upgrades

Contents

  • churn.sql
  • churn.sql
  • churn.view.lkml
  • churn.view.lookml