dbt Ecommerce beverage

Is Customers

Is Customers model for ecommerce beverage.

inferred

Blocks

dbt SQL LKML LookML

is_customers.sql · 54 lines

inferred

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

SELECT
c.Id 'id'
, c.DateCreated 'created'
, c.Company 'company'
, c.Email 'email'
, c.FirstName 'first_name'
, c.LastName 'last_name'
, COALESCE(c.StreetAddress1, c.Address2Street1) 'street1'
, COALESCE(c.StreetAddress2, c.Address2Street2) 'street2'
, COALESCE(c.City, c.City2, c.City3) 'city'
, COALESCE(c.State, c.State2, c.State3) 'state'
, COALESCE(c.PostalCode, c.PostalCode2, c.PostalCode3) 'postal_code'
, COALESCE(c.Country, c.Country2, c.Country3) 'country'
, COALESCE(c.Phone1, c.Phone2, c.Phone3) 'phone'
, COUNT(IF(o.IsSubscriptionOrder,1,NULL)) > 0 'is_subscriber'
-- , GROUP_CONCAT(DISTINCT IF(cg.GroupCategoryId IS NOT NULL, c.Id, NULL)) 'subscriber_id'
, MIN(IF(o.IsSubscriptionOrder, o.DateCreated, NULL)) 'subscriber_since'
, MIN(o.DateCreated) 'first_order_created'
, COALESCE(mgcg.customer_group_cat1, IF(LOCATE(Campaign_Keyword, c.Email), 'GYM', 'NON-MG'), 'NON-MG' ) 'customer_group_cat1'
FROM Company_CustomerContacts c
LEFT JOIN Company_Jobs o ON o.ContactId=c.Id
LEFT JOIN MG_Customers mgc ON mgc.is_contact_id=c.Id
LEFT JOIN MG_Customer_Groups mgcg ON mgcg.customer_group_id=mgc.group_id
GROUP BY c.Id

# models/schema.yml
version: 2

models:
  - name: is_customers
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: created
      - name: company
      - name: email
      - name: first_name
      - name: last_name
      - name: street1
      - name: street2
      - name: city
      - name: state
      - name: postal_code
      - name: country
      - name: phone
      - name: is_subscriber
      - name: subscriber_since
      - name: first_order_created
      - name: customer_group_cat1

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.

is_customers.sql · 24 lines

source-preserved validation: not checked

SELECT
c.Id 'id'
, c.DateCreated 'created'
, c.Company 'company'
, c.Email 'email'
, c.FirstName 'first_name'
, c.LastName 'last_name'
, COALESCE(c.StreetAddress1, c.Address2Street1) 'street1'
, COALESCE(c.StreetAddress2, c.Address2Street2) 'street2'
, COALESCE(c.City, c.City2, c.City3) 'city'
, COALESCE(c.State, c.State2, c.State3) 'state'
, COALESCE(c.PostalCode, c.PostalCode2, c.PostalCode3) 'postal_code'
, COALESCE(c.Country, c.Country2, c.Country3) 'country'
, COALESCE(c.Phone1, c.Phone2, c.Phone3) 'phone'
, COUNT(IF(o.IsSubscriptionOrder,1,NULL)) > 0 'is_subscriber'
-- , GROUP_CONCAT(DISTINCT IF(cg.GroupCategoryId IS NOT NULL, c.Id, NULL)) 'subscriber_id'
, MIN(IF(o.IsSubscriptionOrder, o.DateCreated, NULL)) 'subscriber_since'
, MIN(o.DateCreated) 'first_order_created'
, COALESCE(mgcg.customer_group_cat1, IF(LOCATE(Campaign_Keyword, c.Email), 'GYM', 'NON-MG'), 'NON-MG' ) 'customer_group_cat1'
FROM Company_CustomerContacts c
LEFT JOIN Company_Jobs o ON o.ContactId=c.Id
LEFT JOIN MG_Customers mgc ON mgc.is_contact_id=c.Id
LEFT JOIN MG_Customer_Groups mgcg ON mgcg.customer_group_id=mgc.group_id
GROUP BY c.Id

is_customers.view.lkml · 122 lines

inferred

view: is_customers {
  derived_table: {
    sql: SELECT
c.Id 'id'
, c.DateCreated 'created'
, c.Company 'company'
, c.Email 'email'
, c.FirstName 'first_name'
, c.LastName 'last_name'
, COALESCE(c.StreetAddress1, c.Address2Street1) 'street1'
, COALESCE(c.StreetAddress2, c.Address2Street2) 'street2'
, COALESCE(c.City, c.City2, c.City3) 'city'
, COALESCE(c.State, c.State2, c.State3) 'state'
, COALESCE(c.PostalCode, c.PostalCode2, c.PostalCode3) 'postal_code'
, COALESCE(c.Country, c.Country2, c.Country3) 'country'
, COALESCE(c.Phone1, c.Phone2, c.Phone3) 'phone'
, COUNT(IF(o.IsSubscriptionOrder,1,NULL)) > 0 'is_subscriber'
-- , GROUP_CONCAT(DISTINCT IF(cg.GroupCategoryId IS NOT NULL, c.Id, NULL)) 'subscriber_id'
, MIN(IF(o.IsSubscriptionOrder, o.DateCreated, NULL)) 'subscriber_since'
, MIN(o.DateCreated) 'first_order_created'
, COALESCE(mgcg.customer_group_cat1, IF(LOCATE(Campaign_Keyword, c.Email), 'GYM', 'NON-MG'), 'NON-MG' ) 'customer_group_cat1'
FROM Company_CustomerContacts c
LEFT JOIN Company_Jobs o ON o.ContactId=c.Id
LEFT JOIN MG_Customers mgc ON mgc.is_contact_id=c.Id
LEFT JOIN MG_Customer_Groups mgcg ON mgcg.customer_group_id=mgc.group_id
GROUP BY c.Id ;;
    persist_for: "180 minutes"
  }

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: created {
    type: time
    sql: ${TABLE}.created ;;
  }

  dimension: company {
    type: string
    sql: ${TABLE}.company ;;
  }

  dimension: email {
    type: string
    sql: ${TABLE}.email ;;
  }

  dimension: first_name {
    type: string
    sql: ${TABLE}.first_name ;;
  }

  dimension: last_name {
    type: string
    sql: ${TABLE}.last_name ;;
  }

  dimension: street1 {
    type: string
    sql: ${TABLE}.street1 ;;
  }

  dimension: street2 {
    type: string
    sql: ${TABLE}.street2 ;;
  }

  dimension: city {
    type: string
    sql: ${TABLE}.city ;;
  }

  dimension: state {
    type: string
    sql: ${TABLE}.state ;;
  }

  dimension: postal_code {
    type: zipcode
    sql: ${TABLE}.postal_code ;;
  }

  dimension: country {
    type: string
    sql: ${TABLE}.country ;;
  }

  dimension: phone {
    type: string
    sql: ${TABLE}.phone ;;
  }

  dimension: is_subscriber {
    type: yesno
    sql: ${TABLE}.is_subscriber ;;
  }

  dimension: subscriber_since {
    type: time
  }

  dimension: first_order_created {
    type: time
  }

  dimension: customer_group_cat1 {
    type: string
    sql: ${TABLE}.customer_group_cat1 ;;
  }

  measure: count {
    type: count
    drill_fields: [${id}]
  }

  measure: subscribers_count {
    type: count_distinct
    sql: IF(${is_subscriber},${id},NULL) ;;
  }
}

is_customers.view.lookml · 84 lines

source-preserved

- view: is_customers
  derived_table:
    sql: SELECT
c.Id 'id'
, c.DateCreated 'created'
, c.Company 'company'
, c.Email 'email'
, c.FirstName 'first_name'
, c.LastName 'last_name'
, COALESCE(c.StreetAddress1, c.Address2Street1) 'street1'
, COALESCE(c.StreetAddress2, c.Address2Street2) 'street2'
, COALESCE(c.City, c.City2, c.City3) 'city'
, COALESCE(c.State, c.State2, c.State3) 'state'
, COALESCE(c.PostalCode, c.PostalCode2, c.PostalCode3) 'postal_code'
, COALESCE(c.Country, c.Country2, c.Country3) 'country'
, COALESCE(c.Phone1, c.Phone2, c.Phone3) 'phone'
, COUNT(IF(o.IsSubscriptionOrder,1,NULL)) > 0 'is_subscriber'
-- , GROUP_CONCAT(DISTINCT IF(cg.GroupCategoryId IS NOT NULL, c.Id, NULL)) 'subscriber_id'
, MIN(IF(o.IsSubscriptionOrder, o.DateCreated, NULL)) 'subscriber_since'
, MIN(o.DateCreated) 'first_order_created'
, COALESCE(mgcg.customer_group_cat1, IF(LOCATE(Campaign_Keyword, c.Email), 'GYM', 'NON-MG'), 'NON-MG' ) 'customer_group_cat1'
FROM Company_CustomerContacts c
LEFT JOIN Company_Jobs o ON o.ContactId=c.Id
LEFT JOIN MG_Customers mgc ON mgc.is_contact_id=c.Id
LEFT JOIN MG_Customer_Groups mgcg ON mgcg.customer_group_id=mgc.group_id
GROUP BY c.Id
    persist_for: 180 minutes
  fields:
  - dimension: id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: created
    type: time
    sql: ${TABLE}.created
  - dimension: company
    type: string
    sql: ${TABLE}.company
  - dimension: email
    type: string
    sql: ${TABLE}.email
  - dimension: first_name
    type: string
    sql: ${TABLE}.first_name
  - dimension: last_name
    type: string
    sql: ${TABLE}.last_name
  - dimension: street1
    type: string
    sql: ${TABLE}.street1
  - dimension: street2
    type: string
    sql: ${TABLE}.street2
  - dimension: city
    type: string
    sql: ${TABLE}.city
  - dimension: state
    type: string
    sql: ${TABLE}.state
  - dimension: postal_code
    type: zipcode
    sql: ${TABLE}.postal_code
  - dimension: country
    type: string
    sql: ${TABLE}.country
  - dimension: phone
    type: string
    sql: ${TABLE}.phone
  - dimension: is_subscriber
    type: yesno
    sql: ${TABLE}.is_subscriber
  - dimension: subscriber_since
    type: time
  - dimension: first_order_created
    type: time
  - dimension: customer_group_cat1
    type: string
    sql: ${TABLE}.customer_group_cat1
  - measure: count
    type: count
    drill_fields:
    - ${id}
  - measure: subscribers_count
    type: count_distinct
    sql: IF(${is_subscriber},${id},NULL)

is_customers.sql
is_customers.sql
is_customers.view.lkml
is_customers.view.lookml

Is Customers

Blocks

Assumptions

Contents