SQL Ecommerce beverage

Is Subscriptions

Subscriptions

source-preserved

Blocks

dbt SQL LKML LookML

is_subscriptions.sql · 52 lines

inferred

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

SELECT
c.Id AS 'contact_id'
, o.first_order_date AS 'event_time'
, o.last_order_date AS 'last_subscription_order_date'
, 'START' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'first_order_date'
  , MAX(DateCreated) 'last_order_date'
  FROM Company_Jobs
  WHERE IsSubscriptionOrder = 1
  GROUP BY ContactId
) o ON o.ContactId=c.Id
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'start_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR (ContactGroup LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix))
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE o.first_order_date IS NOT NULL
UNION
SELECT
c.Id AS 'contact_id'
, s.end_date AS 'event_time'
, NULL AS 'last_subscription_order_date'
, 'STOP' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MAX(DateCreated) 'end_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix)
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE s.end_date IS NOT NULL

# models/schema.yml
version: 2

models:
  - name: is_subscriptions
    columns:
      - name: contact_id
      - name: last_subscription_order_date
      - name: months_since_last_subscription_order
      - name: active
      - name: event_type

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.

is_subscriptions.sql · 37 lines

source-preserved validation: not checked

SELECT
c.Id AS 'contact_id'
, o.first_order_date AS 'event_time'
, o.last_order_date AS 'last_subscription_order_date'
, 'START' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'first_order_date'
  , MAX(DateCreated) 'last_order_date'
  FROM Company_Jobs
  WHERE IsSubscriptionOrder = 1
  GROUP BY ContactId
) o ON o.ContactId=c.Id
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'start_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR (ContactGroup LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix))
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE o.first_order_date IS NOT NULL
UNION
SELECT
c.Id AS 'contact_id'
, s.end_date AS 'event_time'
, NULL AS 'last_subscription_order_date'
, 'STOP' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MAX(DateCreated) 'end_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix)
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE s.end_date IS NOT NULL

is_subscriptions.view.lkml · 96 lines

inferred

view: is_subscriptions {
  label: "Subscriptions"
  derived_table: {
    sql: SELECT
c.Id AS 'contact_id'
, o.first_order_date AS 'event_time'
, o.last_order_date AS 'last_subscription_order_date'
, 'START' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'first_order_date'
  , MAX(DateCreated) 'last_order_date'
  FROM Company_Jobs
  WHERE IsSubscriptionOrder = 1
  GROUP BY ContactId
) o ON o.ContactId=c.Id
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'start_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR (ContactGroup LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix))
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE o.first_order_date IS NOT NULL
UNION
SELECT
c.Id AS 'contact_id'
, s.end_date AS 'event_time'
, NULL AS 'last_subscription_order_date'
, 'STOP' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MAX(DateCreated) 'end_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix)
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE s.end_date IS NOT NULL ;;
  }

  dimension: contact_id {
    type: int
    hidden: yes
  }

  dimension: last_subscription_order_date {
    hidden: yes
  }

  dimension: months_since_last_subscription_order {
    type: int
    hidden: yes
    sql: PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE()), EXTRACT(YEAR_MONTH FROM ${last_subscription_order_date})) ;;
  }

  dimension: active {
    type: yesno
    hidden: yes
    sql: {% condition idle_months %} ${months_since_last_subscription_order} {% endcondition %} ;;
  }

  dimension: event_type {
    hidden: yes
  }

  dimension_group: event {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.event_time ;;
  }

  measure: subscription_start_count {
    label: "# of new subscribers"
    type: count
    filters: [event_type: "START"]
  }

  measure: subscription_stop_count {
    label: "# of canceled subscriptions"
    type: count
    filters: [event_type: "STOP"]
  }

  measure: actives_count {
    label: "# of subscribers still active"
    type: count
    filters: [active: "True"]
  }

  measure: cumulative_actives_count {
    type: running_total
    sql: ${actives_count} ;;
  }
}

is_subscriptions.view.lookml · 82 lines

source-preserved

- view: is_subscriptions
  label: Subscriptions
  derived_table:
    sql: SELECT
c.Id AS 'contact_id'
, o.first_order_date AS 'event_time'
, o.last_order_date AS 'last_subscription_order_date'
, 'START' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'first_order_date'
  , MAX(DateCreated) 'last_order_date'
  FROM Company_Jobs
  WHERE IsSubscriptionOrder = 1
  GROUP BY ContactId
) o ON o.ContactId=c.Id
LEFT JOIN (
  SELECT ContactId
  , MIN(DateCreated) 'start_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR (ContactGroup LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix AND ContactGroup NOT LIKE Contact_GroupPrefix))
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE o.first_order_date IS NOT NULL
UNION
SELECT
c.Id AS 'contact_id'
, s.end_date AS 'event_time'
, NULL AS 'last_subscription_order_date'
, 'STOP' AS 'event_type'
FROM Company_CustomerContacts c
LEFT JOIN (
  SELECT ContactId
  , MAX(DateCreated) 'end_date'
  FROM Company_ContactGroupAssigns
  WHERE (ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix OR ContactGroup LIKE Contact_GroupPrefix)
  GROUP BY ContactId
) s ON s.ContactId=c.Id
WHERE s.end_date IS NOT NULL
  fields:
  - dimension: contact_id
    type: int
    hidden: true
  - dimension: last_subscription_order_date
    hidden: true
  - dimension: months_since_last_subscription_order
    type: int
    hidden: true
    sql: PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM CURDATE()), EXTRACT(YEAR_MONTH FROM ${last_subscription_order_date}))
  - dimension: active
    type: yesno
    hidden: true
    sql: {% condition idle_months %} ${months_since_last_subscription_order} {% endcondition %}
  - dimension: event_type
    hidden: true
  - dimension_group: event
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.event_time
  - measure: subscription_start_count
    type: count
    label: '# of new subscribers'
    filters:
    - event_type: START
  - measure: subscription_stop_count
    type: count
    label: '# of canceled subscriptions'
    filters:
    - event_type: STOP
  - measure: actives_count
    type: count
    label: '# of subscribers still active'
    filters:
    - active: True
  - measure: cumulative_actives_count
    type: running_total
    sql: ${actives_count}

is_subscriptions.sql
is_subscriptions.sql
is_subscriptions.view.lkml
is_subscriptions.view.lookml

Is Subscriptions

Blocks

Assumptions

Contents