dbt Ecommerce beverage

Is Customer Retention

Is Customer Retention model for ecommerce beverage.

inferred

Blocks

dbt SQL LKML LookML

is_customer_retention.sql · 38 lines

inferred

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

SELECT
  o.ContactId 'contact_id'
  , fo.order_date 'first_order_date'
  , MIN(o.DateCreated) 'second_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM COALESCE(MIN(o.DateCreated), CURDATE())),
    EXTRACT(YEAR_MONTH FROM fo.order_date)
  ) 'months_between_2first_orders'
  , MAX(o.DateCreated) 'last_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM CURDATE()),
    EXTRACT(YEAR_MONTH FROM MAX(o.DateCreated))
  ) 'months_since_last_order'
FROM Company_Jobs o
LEFT JOIN (
  SELECT
    o.ContactId 'contact_id',
    MIN(o.DateCreated) 'order_date'
  FROM Company_Jobs o
  GROUP BY o.ContactId HAVING `order_date` IS NOT NULL
) AS fo ON fo.contact_id=o.ContactId AND fo.order_date < o.DateCreated
WHERE fo.contact_id IS NOT NULL
GROUP BY o.ContactId

# models/schema.yml
version: 2

models:
  - name: is_customer_retention
    columns:
      - name: contact_id
      - name: months_between_2first_orders
      - name: months_since_last_order
      - name: is_lost
      - name: is_active

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_customer_retention.sql · 23 lines

source-preserved validation: not checked

SELECT
  o.ContactId 'contact_id'
  , fo.order_date 'first_order_date'
  , MIN(o.DateCreated) 'second_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM COALESCE(MIN(o.DateCreated), CURDATE())),
    EXTRACT(YEAR_MONTH FROM fo.order_date)
  ) 'months_between_2first_orders'
  , MAX(o.DateCreated) 'last_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM CURDATE()),
    EXTRACT(YEAR_MONTH FROM MAX(o.DateCreated))
  ) 'months_since_last_order'
FROM Company_Jobs o
LEFT JOIN (
  SELECT
    o.ContactId 'contact_id',
    MIN(o.DateCreated) 'order_date'
  FROM Company_Jobs o
  GROUP BY o.ContactId HAVING `order_date` IS NOT NULL
) AS fo ON fo.contact_id=o.ContactId AND fo.order_date < o.DateCreated
WHERE fo.contact_id IS NOT NULL
GROUP BY o.ContactId

is_customer_retention.view.lkml · 70 lines

inferred

view: is_customer_retention {
  derived_table: {
    sql: SELECT
  o.ContactId 'contact_id'
  , fo.order_date 'first_order_date'
  , MIN(o.DateCreated) 'second_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM COALESCE(MIN(o.DateCreated), CURDATE())),
    EXTRACT(YEAR_MONTH FROM fo.order_date)
  ) 'months_between_2first_orders'
  , MAX(o.DateCreated) 'last_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM CURDATE()),
    EXTRACT(YEAR_MONTH FROM MAX(o.DateCreated))
  ) 'months_since_last_order'
FROM Company_Jobs o
LEFT JOIN (
  SELECT
    o.ContactId 'contact_id',
    MIN(o.DateCreated) 'order_date'
  FROM Company_Jobs o
  GROUP BY o.ContactId HAVING `order_date` IS NOT NULL
) AS fo ON fo.contact_id=o.ContactId AND fo.order_date < o.DateCreated
WHERE fo.contact_id IS NOT NULL
GROUP BY o.ContactId ;;
  }

  dimension: contact_id {
    type: int
  }

  dimension: months_between_2first_orders {
    type: int
  }

  dimension: months_since_last_order {
    type: int
  }

  dimension: is_lost {
    type: yesno
    sql: {% condition months_lost %} ${months_between_2first_orders} {% endcondition %} ;;
  }

  dimension: is_active {
    type: yesno
    sql: {% condition months_active %} ${months_since_last_order} {% endcondition %} ;;
  }

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

  measure: customers_count {
    type: count
  }

  measure: lost_customers_count {
    type: count
    filters: [is_lost: "True"]
  }

  measure: recovered_customers_count {
    type: count
    filters: [is_lost: "True"]
    filters: [is_active: "True"]
  }
}

is_customer_retention.view.lookml · 57 lines

source-preserved

- view: is_customer_retention
  derived_table:
    sql: SELECT
  o.ContactId 'contact_id'
  , fo.order_date 'first_order_date'
  , MIN(o.DateCreated) 'second_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM COALESCE(MIN(o.DateCreated), CURDATE())),
    EXTRACT(YEAR_MONTH FROM fo.order_date)
  ) 'months_between_2first_orders'
  , MAX(o.DateCreated) 'last_order_date'
  , PERIOD_DIFF(
    EXTRACT(YEAR_MONTH FROM CURDATE()),
    EXTRACT(YEAR_MONTH FROM MAX(o.DateCreated))
  ) 'months_since_last_order'
FROM Company_Jobs o
LEFT JOIN (
  SELECT
    o.ContactId 'contact_id',
    MIN(o.DateCreated) 'order_date'
  FROM Company_Jobs o
  GROUP BY o.ContactId HAVING `order_date` IS NOT NULL
) AS fo ON fo.contact_id=o.ContactId AND fo.order_date < o.DateCreated
WHERE fo.contact_id IS NOT NULL
GROUP BY o.ContactId
  fields:
  - dimension: contact_id
    type: int
  - dimension: months_between_2first_orders
    type: int
  - dimension: months_since_last_order
    type: int
  - dimension: is_lost
    type: yesno
    sql: {% condition months_lost %} ${months_between_2first_orders} {% endcondition %}
  - dimension: is_active
    type: yesno
    sql: {% condition months_active %} ${months_since_last_order} {% endcondition %}
  - dimension_group: first_order
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.first_order_date
  - measure: customers_count
    type: count
  - measure: lost_customers_count
    type: count
    filters:
    - is_lost: True
  - measure: recovered_customers_count
    type: count
    filters:
    - is_lost: True
    - is_active: True

is_customer_retention.sql
is_customer_retention.sql
is_customer_retention.view.lkml
is_customer_retention.view.lookml

Is Customer Retention

Blocks

Assumptions

Contents