SQL Ecommerce discount

Customer Order Facts

Customer Order Facts model for ecommerce discount.

source-preserved

Blocks

dbt SQL LKML LookML

customer_order_facts.sql · 54 lines

inferred

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

SELECT
t.customer_id,
MIN(IFNULL(t.created_on, 0)) AS first_order,
MAX(IFNULL(t.created_on, 0)) AS latest_order,
MIN(IFNULL(t.order_id, 0)) AS first_order_id,
MAX(IFNULL(t.order_id, 0)) AS latest_order_id,
lifetime_order_items,
lifetime_orders,
DATEDIFF(CURDATE(), MIN(NULLIF(t.created_on, 0))) AS days_as_customer,
DATEDIFF(CURDATE(), MAX(NULLIF(t.created_on, 0))) AS days_since_purchase,
COUNT(DISTINCT CONCAT(MONTH(NULLIF(t.created_on, 0))),
YEAR(NULLIF(t.created_on, 0))) AS number_of_distinct_months_with_orders
FROM
transactions t
JOIN
(SELECT
customer_id,
SUM(quantity) AS lifetime_order_items,
COUNT(DISTINCT order_id) AS lifetime_orders
FROM
order_items
WHERE
approved = 1 AND order_id > 0
GROUP BY customer_id) AS oi ON oi.customer_id = t.customer_id
WHERE
t.code = <% parameter placeholder_code %>
GROUP BY t.customer_id

# models/schema.yml
version: 2

models:
  - name: customer_order_facts
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: lifetime_orders
      - name: lifetime_order_items
      - name: first_order_id
      - name: latest_order_id
      - name: lifetime_value
      - name: lifetime_number_of_orders_tier
      - name: repeat_customer
      - name: quarter
      - name: latest_order
      - name: days_as_customer
      - name: days_as_customer_tiered
      - name: days_since_purchase
      - name: number_of_distinct_months_with_orders

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.

customer_order_facts.sql · 27 lines

source-preserved validation: not checked

SELECT
t.customer_id,
MIN(IFNULL(t.created_on, 0)) AS first_order,
MAX(IFNULL(t.created_on, 0)) AS latest_order,
MIN(IFNULL(t.order_id, 0)) AS first_order_id,
MAX(IFNULL(t.order_id, 0)) AS latest_order_id,
lifetime_order_items,
lifetime_orders,
DATEDIFF(CURDATE(), MIN(NULLIF(t.created_on, 0))) AS days_as_customer,
DATEDIFF(CURDATE(), MAX(NULLIF(t.created_on, 0))) AS days_since_purchase,
COUNT(DISTINCT CONCAT(MONTH(NULLIF(t.created_on, 0))),
YEAR(NULLIF(t.created_on, 0))) AS number_of_distinct_months_with_orders
FROM
transactions t
JOIN
(SELECT
customer_id,
SUM(quantity) AS lifetime_order_items,
COUNT(DISTINCT order_id) AS lifetime_orders
FROM
order_items
WHERE
approved = 1 AND order_id > 0
GROUP BY customer_id) AS oi ON oi.customer_id = t.customer_id
WHERE
t.code = <% parameter placeholder_code %>
GROUP BY t.customer_id

customer_order_facts.view.lkml · 111 lines

inferred

view: customer_order_facts {
  derived_table: {
    sql: SELECT
t.customer_id,
MIN(IFNULL(t.created_on, 0)) AS first_order,
MAX(IFNULL(t.created_on, 0)) AS latest_order,
MIN(IFNULL(t.order_id, 0)) AS first_order_id,
MAX(IFNULL(t.order_id, 0)) AS latest_order_id,
lifetime_order_items,
lifetime_orders,
DATEDIFF(CURDATE(), MIN(NULLIF(t.created_on, 0))) AS days_as_customer,
DATEDIFF(CURDATE(), MAX(NULLIF(t.created_on, 0))) AS days_since_purchase,
COUNT(DISTINCT CONCAT(MONTH(NULLIF(t.created_on, 0))),
YEAR(NULLIF(t.created_on, 0))) AS number_of_distinct_months_with_orders
FROM
transactions t
JOIN
(SELECT
customer_id,
SUM(quantity) AS lifetime_order_items,
COUNT(DISTINCT order_id) AS lifetime_orders
FROM
order_items
WHERE
approved = 1 AND order_id > 0
GROUP BY customer_id) AS oi ON oi.customer_id = t.customer_id
WHERE
t.code = <% parameter placeholder_code %>
GROUP BY t.customer_id ;;
  }

  dimension: customer_id {
    primary_key: yes
    hidden: yes
  }

  dimension: lifetime_orders {
    type: int
  }

  dimension: lifetime_order_items {
    type: int
  }

  dimension: first_order_id {
    type: int
  }

  dimension: latest_order_id {
    type: int
  }

  dimension: lifetime_value {
    type: number
  }

  dimension: lifetime_number_of_orders_tier {
    type: tier
    sql: ${lifetime_orders} ;;
  }

  dimension: repeat_customer {
    type: yesno
    sql: ${lifetime_orders} > 1 ;;
  }

  dimension: quarter {
    sql: CONCAT(
   CONCAT(
      cast(EXTRACT(year FROM ${TABLE}.first_order) as char),
       ' - Q'
       )
   ,
   cast(EXTRACT(quarter FROM ${TABLE}.first_order) as char)
) ;;
  }

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

  dimension: days_as_customer {
    type: int
  }

  dimension: days_as_customer_tiered {
    type: tier
    sql: ${days_as_customer} ;;
  }

  dimension: days_since_purchase {
    type: int
  }

  dimension: number_of_distinct_months_with_orders {
    type: int
  }

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

  measure: average_ltv {
    type: average
    drill_fields: [lifetime_orders, users.count]
    sql: ${lifetime_orders} ;;
  }
}

customer_order_facts.view.lookml · 85 lines

source-preserved

- view: customer_order_facts
  derived_table:
    sql: SELECT
t.customer_id,
MIN(IFNULL(t.created_on, 0)) AS first_order,
MAX(IFNULL(t.created_on, 0)) AS latest_order,
MIN(IFNULL(t.order_id, 0)) AS first_order_id,
MAX(IFNULL(t.order_id, 0)) AS latest_order_id,
lifetime_order_items,
lifetime_orders,
DATEDIFF(CURDATE(), MIN(NULLIF(t.created_on, 0))) AS days_as_customer,
DATEDIFF(CURDATE(), MAX(NULLIF(t.created_on, 0))) AS days_since_purchase,
COUNT(DISTINCT CONCAT(MONTH(NULLIF(t.created_on, 0))),
YEAR(NULLIF(t.created_on, 0))) AS number_of_distinct_months_with_orders
FROM
transactions t
JOIN
(SELECT
customer_id,
SUM(quantity) AS lifetime_order_items,
COUNT(DISTINCT order_id) AS lifetime_orders
FROM
order_items
WHERE
approved = 1 AND order_id > 0
GROUP BY customer_id) AS oi ON oi.customer_id = t.customer_id
WHERE
t.code = <% parameter placeholder_code %>
GROUP BY t.customer_id
  fields:
  - dimension: customer_id
    primary_key: true
    hidden: true
  - dimension: lifetime_orders
    type: int
  - dimension: lifetime_order_items
    type: int
  - dimension: first_order_id
    type: int
  - dimension: latest_order_id
    type: int
  - dimension: lifetime_value
    type: number
  - dimension: lifetime_number_of_orders_tier
    type: tier
    sql: ${lifetime_orders}
  - dimension: repeat_customer
    type: yesno
    sql: ${lifetime_orders} > 1
  - dimension: quarter
    sql: CONCAT(
   CONCAT(
      cast(EXTRACT(year FROM ${TABLE}.first_order) as char),
       ' - Q'
       )
   ,
   cast(EXTRACT(quarter FROM ${TABLE}.first_order) as char)
)
  - dimension: latest_order
    type: time
    sql: ${TABLE}.latest_order
  - dimension: days_as_customer
    type: int
  - dimension: days_as_customer_tiered
    type: tier
    sql: ${days_as_customer}
  - dimension: days_since_purchase
    type: int
  - dimension: number_of_distinct_months_with_orders
    type: int
  - dimension_group: first_purchase
    type: time
    timeframes:
    - time
    - date
    - week
    - quarter
    - month
    sql: ${TABLE}.first_order
  - measure: average_ltv
    type: average
    drill_fields:
    - lifetime_orders
    - users.count
    sql: ${lifetime_orders}

customer_order_facts.sql
customer_order_facts.sql
customer_order_facts.view.lkml
customer_order_facts.view.lookml

Customer Order Facts

Blocks

Assumptions

Contents