SQL Ecommerce window treatments

All Activity

All Activity model for ecommerce window treatments.

source-preserved

Blocks

dbt SQL LKML LookML

all_activity.sql · 147 lines

inferred

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

SELECT
  o.id AS item_id,
  o.lead_id,
  o.type AS item_type,
  o.status AS item_status,
  lc.cart_id AS quote_id,
  o.retailer_id,
  FROM_UNIXTIME(o.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(o.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  orders o
LEFT JOIN
  lead l
  ON l.id = o.lead_id
LEFT JOIN
  lead_cart lc
  ON lc.cart_id = o.cart_id
WHERE 1=1
  AND o.type <> 'repair'
  AND IF(lc.id IS NOT NULL, lc.status <> 'dismissed', 1)

UNION

SELECT
  a.id AS item_id,
  a.lead_id,
  at.name AS item_type,
  s.name AS item_status,
  NULL AS quote_id,
  a.retailer_id,
  FROM_UNIXTIME(a.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(a.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  appointments a
LEFT JOIN
  mi_status s
  ON s.id = a.mi_status_id
LEFT JOIN
  lead l
  ON l.id = a.lead_id
JOIN
  appointment_type at
  ON at.id = a.appointment_type_id

UNION

SELECT
    f.id AS item_id,
    f.lead_id,
    'photo_request' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(f.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(f.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    lead_file f
JOIN
    lead l
    ON l.id = f.lead_id
WHERE 1=1
    AND f.lead_file_type_id = 3

UNION

SELECT
    d.id AS item_id,
    l.id,
    'trade' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(d.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(d.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    designer d
LEFT JOIN
    lead l
    ON l.email = d.email
GROUP BY d.id

UNION

SELECT
    u.id AS item_id,
    l.id,
    'my account' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(u.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(u.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    user u
LEFT JOIN
    lead l
    ON l.email = u.email
GROUP BY u.id

# models/schema.yml
version: 2

models:
  - name: all_activity
    columns:
      - name: item_id
        tests:
          - unique
          - not_null
      - name: lead_id
      - name: item_type
      - name: item_status
      - name: quote_id
      - name: retailer_id

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.

all_activity.sql · 128 lines

source-preserved validation: parses

SELECT
  o.id AS item_id,
  o.lead_id,
  o.type AS item_type,
  o.status AS item_status,
  lc.cart_id AS quote_id,
  o.retailer_id,
  FROM_UNIXTIME(o.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(o.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  orders o
LEFT JOIN
  lead l
  ON l.id = o.lead_id
LEFT JOIN
  lead_cart lc
  ON lc.cart_id = o.cart_id
WHERE 1=1
  AND o.type <> 'repair'
  AND IF(lc.id IS NOT NULL, lc.status <> 'dismissed', 1)

UNION

SELECT
  a.id AS item_id,
  a.lead_id,
  at.name AS item_type,
  s.name AS item_status,
  NULL AS quote_id,
  a.retailer_id,
  FROM_UNIXTIME(a.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(a.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  appointments a
LEFT JOIN
  mi_status s
  ON s.id = a.mi_status_id
LEFT JOIN
  lead l
  ON l.id = a.lead_id
JOIN
  appointment_type at
  ON at.id = a.appointment_type_id

UNION

SELECT
    f.id AS item_id,
    f.lead_id,
    'photo_request' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(f.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(f.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    lead_file f
JOIN
    lead l
    ON l.id = f.lead_id
WHERE 1=1
    AND f.lead_file_type_id = 3

UNION

SELECT
    d.id AS item_id,
    l.id,
    'trade' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(d.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(d.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    designer d
LEFT JOIN
    lead l
    ON l.email = d.email
GROUP BY d.id

UNION

SELECT
    u.id AS item_id,
    l.id,
    'my account' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(u.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(u.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    user u
LEFT JOIN
    lead l
    ON l.email = u.email
GROUP BY u.id

all_activity.view.lkml · 270 lines

inferred

view: all_activity {
  derived_table: {
    sql: SELECT
  o.id AS item_id,
  o.lead_id,
  o.type AS item_type,
  o.status AS item_status,
  lc.cart_id AS quote_id,
  o.retailer_id,
  FROM_UNIXTIME(o.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(o.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  orders o
LEFT JOIN
  lead l
  ON l.id = o.lead_id
LEFT JOIN
  lead_cart lc
  ON lc.cart_id = o.cart_id
WHERE 1=1
  AND o.type <> 'repair'
  AND IF(lc.id IS NOT NULL, lc.status <> 'dismissed', 1)

UNION

SELECT
  a.id AS item_id,
  a.lead_id,
  at.name AS item_type,
  s.name AS item_status,
  NULL AS quote_id,
  a.retailer_id,
  FROM_UNIXTIME(a.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(a.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  appointments a
LEFT JOIN
  mi_status s
  ON s.id = a.mi_status_id
LEFT JOIN
  lead l
  ON l.id = a.lead_id
JOIN
  appointment_type at
  ON at.id = a.appointment_type_id

UNION

SELECT
    f.id AS item_id,
    f.lead_id,
    'photo_request' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(f.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(f.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    lead_file f
JOIN
    lead l
    ON l.id = f.lead_id
WHERE 1=1
    AND f.lead_file_type_id = 3

UNION

SELECT
    d.id AS item_id,
    l.id,
    'trade' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(d.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(d.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    designer d
LEFT JOIN
    lead l
    ON l.email = d.email
GROUP BY d.id

UNION

SELECT
    u.id AS item_id,
    l.id,
    'my account' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(u.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(u.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    user u
LEFT JOIN
    lead l
    ON l.email = u.email
GROUP BY u.id ;;
  }

  dimension: item_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.item_id ;;
  }

  dimension: lead_id {
    type: int
    sql: ${TABLE}.lead_id ;;
  }

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

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

  dimension: quote_id {
    type: int
    sql: ${TABLE}.quote_id ;;
  }

  dimension: retailer_id {
    type: int
    sql: ${TABLE}.retailer_id ;;
  }

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

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

  dimension_group: did_create_lead {
    type: yesno
    sql: ${TABLE}.did_create_lead ;;
  }

  measure: count {
    type: count
    drill_fields: [lead_created_detail*]
  }

  measure: product_order_count {
    description: "The number of product orders"
    type: count
  }

  measure: quote_count {
    description: "The number of quotes"
    type: count_distinct
    sql: ${quote_id} ;;
  }

  measure: quote_unique_count {
    description: "A unique count of Quotes created for in a single day by the same customer."
    type: count_distinct
    filters: [quote_id: "> 0"]
    sql: CONCAT(${lead_id}, '-', ${created_date}) ;;
  }

  measure: catalog_order_count {
    description: "The number of confirmed catalogs"
    type: count
  }

  measure: swatch_order_count {
    description: "The number of confirmed swatches"
    type: count
  }

  measure: welcome_kit_count {
    description: "The number of confirmed welcome kits"
    type: count
  }

  measure: mi_count {
    description: "The number of Measure and/or Install requests"
    type: count
    filters: [item_type: "Measurement, Install, Measure & Install"]
  }

  measure: service_count {
    type: count
    filters: [item_type: "Service"]
  }

  measure: lead_crated_catalog_count {
    description: "The number of leads created by catalogs"
    type: count_distinct
    sql: ${lead_id} ;;
  }

  measure: lead_created_swatch_count {
    description: "The number of leads created by swatches"
    type: count_distinct
    sql: ${lead_id} ;;
  }

  measure: lead_created_welcome_kit_count {
    description: "The number of leads created by welcome kits"
    type: count_distinct
    sql: ${lead_id} ;;
  }

  measure: lead_created_mi_count {
    description: "The number of leads created by Measure and/or Install requests"
    type: count_distinct
    filters: [item_type: "Measurement, Install, Measure & Install"]
    filters: [did_create_lead: "True"]
    sql: ${lead_id} ;;
  }

  measure: manual_lead_created {
    description: "The number of leads created manually"
    type: count_distinct
    filters: [did_create_lead: "False"]
    sql: ${lead_id} ;;
  }

  measure: activity_lead_created {
    description: "The number of leads created by activity"
    type: count_distinct
    filters: [item_type: "Measurement, Install, Measure & Install,welcome_kit,swatch,catalog"]
    filters: [did_create_lead: "True"]
    sql: ${lead_id} ;;
  }
}

all_activity.view.lookml · 238 lines

source-preserved

- view: all_activity
  derived_table:
    sql: SELECT
  o.id AS item_id,
  o.lead_id,
  o.type AS item_type,
  o.status AS item_status,
  lc.cart_id AS quote_id,
  o.retailer_id,
  FROM_UNIXTIME(o.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(o.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  orders o
LEFT JOIN
  lead l
  ON l.id = o.lead_id
LEFT JOIN
  lead_cart lc
  ON lc.cart_id = o.cart_id
WHERE 1=1
  AND o.type <> 'repair'
  AND IF(lc.id IS NOT NULL, lc.status <> 'dismissed', 1)

UNION

SELECT
  a.id AS item_id,
  a.lead_id,
  at.name AS item_type,
  s.name AS item_status,
  NULL AS quote_id,
  a.retailer_id,
  FROM_UNIXTIME(a.created_time) AS created,
  FROM_UNIXTIME(l.created_time) AS lead_created,
  IF(
    FROM_UNIXTIME(a.created_time) BETWEEN
      SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
      AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
    1, 0
  ) AS did_create_lead
FROM
  appointments a
LEFT JOIN
  mi_status s
  ON s.id = a.mi_status_id
LEFT JOIN
  lead l
  ON l.id = a.lead_id
JOIN
  appointment_type at
  ON at.id = a.appointment_type_id

UNION

SELECT
    f.id AS item_id,
    f.lead_id,
    'photo_request' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(f.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(f.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    lead_file f
JOIN
    lead l
    ON l.id = f.lead_id
WHERE 1=1
    AND f.lead_file_type_id = 3

UNION

SELECT
    d.id AS item_id,
    l.id,
    'trade' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(d.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(d.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    designer d
LEFT JOIN
    lead l
    ON l.email = d.email
GROUP BY d.id

UNION

SELECT
    u.id AS item_id,
    l.id,
    'my account' AS item_type,
    '' AS item_status,
    NULL AS quote_id,
    l.retailer_id,
    FROM_UNIXTIME(u.created_time) AS created,
    FROM_UNIXTIME(l.created_time) AS lead_created,
    IF(
      FROM_UNIXTIME(u.created_time) BETWEEN
        SUBTIME(FROM_UNIXTIME(l.created_time), '00:00:04')
        AND ADDTIME(FROM_UNIXTIME(l.created_time), '00:00:04'),
      1, 0
    ) AS did_create_lead
FROM
    user u
LEFT JOIN
    lead l
    ON l.email = u.email
GROUP BY u.id
  fields:
  - dimension: item_id
    type: int
    primary_key: true
    sql: ${TABLE}.item_id
  - dimension: lead_id
    type: int
    sql: ${TABLE}.lead_id
  - dimension: item_type
    type: string
    sql: ${TABLE}.item_type
  - dimension: item_status
    type: string
    sql: ${TABLE}.item_status
  - dimension: quote_id
    type: int
    sql: ${TABLE}.quote_id
  - dimension: retailer_id
    type: int
    sql: ${TABLE}.retailer_id
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.created
  - dimension_group: lead_created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.lead_created
  - dimension_group: did_create_lead
    type: yesno
    sql: ${TABLE}.did_create_lead
  - measure: count
    type: count
    drill_fields:
    - lead_created_detail*
  - measure: product_order_count
    type: count
    description: The number of product orders
  - measure: quote_count
    type: count_distinct
    description: The number of quotes
    sql: ${quote_id}
  - measure: quote_unique_count
    type: count_distinct
    description: A unique count of Quotes created for in a single day by the same customer.
    filters:
    - quote_id: '> 0'
    sql: CONCAT(${lead_id}, '-', ${created_date})
  - measure: catalog_order_count
    type: count
    description: The number of confirmed catalogs
  - measure: swatch_order_count
    type: count
    description: The number of confirmed swatches
  - measure: welcome_kit_count
    type: count
    description: The number of confirmed welcome kits
  - measure: mi_count
    type: count
    description: The number of Measure and/or Install requests
    filters:
    - item_type: 'Measurement, Install, Measure & Install'
  - measure: service_count
    type: count
    filters:
    - item_type: Service
  - measure: lead_crated_catalog_count
    type: count_distinct
    description: The number of leads created by catalogs
    sql: ${lead_id}
  - measure: lead_created_swatch_count
    type: count_distinct
    description: The number of leads created by swatches
    sql: ${lead_id}
  - measure: lead_created_welcome_kit_count
    type: count_distinct
    description: The number of leads created by welcome kits
    sql: ${lead_id}
  - measure: lead_created_mi_count
    type: count_distinct
    description: The number of leads created by Measure and/or Install requests
    filters:
    - item_type: 'Measurement, Install, Measure & Install'
    - did_create_lead: True
    sql: ${lead_id}
  - measure: manual_lead_created
    type: count_distinct
    description: The number of leads created manually
    filters:
    - did_create_lead: False
    sql: ${lead_id}
  - measure: activity_lead_created
    type: count_distinct
    description: The number of leads created by activity
    filters:
    - item_type: 'Measurement, Install, Measure & Install,welcome_kit,swatch,catalog'
    - did_create_lead: True
    sql: ${lead_id}

all_activity.sql
all_activity.sql
all_activity.view.lkml
all_activity.view.lookml

All Activity

Blocks

Assumptions

Contents