dbt Ecommerce fashion

Engagement Order Facts

Engagement Order Facts model for ecommerce fashion.

inferred

Blocks

dbt SQL LKML LookML

engagement_order_facts.sql · 38 lines

inferred

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

SELECT e.id AS engagement_id
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.id ELSE NULL END) AS orders_next_24_hours
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.item_total ELSE 0 END) AS order_revenue_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.id ELSE NULL END) AS orders_next_30_days
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.id ELSE NULL END) AS orders_next_1_year
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 day and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_1_year
FROM engagements e
INNER JOIN orders o ON o.user_id = e.user_id AND (o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY)
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: engagement_order_facts
    columns:
      - name: engagement_id
        tests:
          - unique
          - not_null
      - name: orders_next_24_hours
      - name: order_revenue_next_24_hours
      - name: orders_next_30_days
      - name: order_revenue_next_30_days
      - name: orders_next_1_year
      - name: order_revenue_next_1_year
      - name: act_next_1_year
      - name: act_next_30_days
      - name: act_next_24_hours

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.

engagement_order_facts.sql · 15 lines

source-preserved validation: parses

SELECT e.id AS engagement_id
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.id ELSE NULL END) AS orders_next_24_hours
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.item_total ELSE 0 END) AS order_revenue_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.id ELSE NULL END) AS orders_next_30_days
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.id ELSE NULL END) AS orders_next_1_year
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 day and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_1_year
FROM engagements e
INNER JOIN orders o ON o.user_id = e.user_id AND (o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY)
GROUP BY 1

engagement_order_facts.view.lkml · 116 lines

inferred

view: engagement_order_facts {
  derived_table: {
    sql: SELECT e.id AS engagement_id
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.id ELSE NULL END) AS orders_next_24_hours
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.item_total ELSE 0 END) AS order_revenue_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.id ELSE NULL END) AS orders_next_30_days
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.id ELSE NULL END) AS orders_next_1_year
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 day and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_1_year
FROM engagements e
INNER JOIN orders o ON o.user_id = e.user_id AND (o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY)
GROUP BY 1 ;;
  }

  dimension: engagement_id {
    type: number
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.engagement_id ;;
  }

  dimension: orders_next_24_hours {
    type: number
    sql: ${TABLE}.orders_next_24_hours ;;
  }

  dimension: order_revenue_next_24_hours {
    type: number
    sql: ${TABLE}.order_revenue_next_24_hours ;;
  }

  dimension: orders_next_30_days {
    type: number
    sql: ${TABLE}.orders_next_30_days ;;
  }

  dimension: order_revenue_next_30_days {
    type: number
    sql: ${TABLE}.order_revenue_next_30_days ;;
  }

  dimension: orders_next_1_year {
    type: number
    sql: ${TABLE}.orders_next_1_year ;;
  }

  dimension: order_revenue_next_1_year {
    type: number
    sql: ${TABLE}.order_revenue_next_1_year ;;
  }

  dimension: act_next_1_year {
    type: number
    sql: ${TABLE}.act_next_1_year ;;
  }

  dimension: act_next_30_days {
    type: number
    sql: ${TABLE}.act_next_30_days ;;
  }

  dimension: act_next_24_hours {
    type: number
    sql: ${TABLE}.act_next_24_hours ;;
  }

  measure: total_act_next_24_hours {
    type: sum
    sql: ${act_next_24_hours} ;;
  }

  measure: total_orders_next_24_hours {
    type: sum
    sql: ${orders_next_24_hours} ;;
  }

  measure: total_order_revenue_next_24_hours {
    type: sum
    sql: ${order_revenue_next_24_hours} ;;
  }

  measure: total_act_next_30_days {
    type: sum
    sql: ${act_next_30_days} ;;
  }

  measure: total_orders_next_30_days {
    type: sum
    sql: ${orders_next_30_days} ;;
  }

  measure: total_order_revenue_next_30_days {
    type: sum
    sql: ${order_revenue_next_30_days} ;;
  }

  measure: total_orders_next_1_year {
    type: sum
    sql: ${orders_next_1_year} ;;
  }

  measure: total_order_revenue_next_1_year {
    type: sum
    sql: ${order_revenue_next_1_year} ;;
  }

  measure: total_act_next_1_year {
    type: sum
    sql: ${act_next_1_year} ;;
  }
}

engagement_order_facts.view.lookml · 77 lines

source-preserved

- view: engagement_order_facts
  derived_table:
    sql: SELECT e.id AS engagement_id
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.id ELSE NULL END) AS orders_next_24_hours
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR THEN o.item_total ELSE 0 END) AS order_revenue_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 24 HOUR and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_24_hours
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.id ELSE NULL END) AS orders_next_30_days
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 30 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_30_days
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.id ELSE NULL END) AS orders_next_1_year
  , SUM(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY THEN o.item_total ELSE 0 END) AS order_revenue_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY and o.purchase_number = 0 THEN o.id ELSE NULL END) AS act_next_1_year
  , COUNT(DISTINCT CASE WHEN o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 day and o.purchase_number = 0 THEN o.item_total ELSE NULL END) AS act_rev_1_year
FROM engagements e
INNER JOIN orders o ON o.user_id = e.user_id AND (o.completed_at BETWEEN e.visited_at AND e.visited_at + INTERVAL 365 DAY)
GROUP BY 1
  fields:
  - dimension: engagement_id
    type: number
    primary_key: true
    hidden: true
    sql: ${TABLE}.engagement_id
  - dimension: orders_next_24_hours
    type: number
    sql: ${TABLE}.orders_next_24_hours
  - dimension: order_revenue_next_24_hours
    type: number
    sql: ${TABLE}.order_revenue_next_24_hours
  - dimension: orders_next_30_days
    type: number
    sql: ${TABLE}.orders_next_30_days
  - dimension: order_revenue_next_30_days
    type: number
    sql: ${TABLE}.order_revenue_next_30_days
  - dimension: orders_next_1_year
    type: number
    sql: ${TABLE}.orders_next_1_year
  - dimension: order_revenue_next_1_year
    type: number
    sql: ${TABLE}.order_revenue_next_1_year
  - dimension: act_next_1_year
    type: number
    sql: ${TABLE}.act_next_1_year
  - dimension: act_next_30_days
    type: number
    sql: ${TABLE}.act_next_30_days
  - dimension: act_next_24_hours
    type: number
    sql: ${TABLE}.act_next_24_hours
  - measure: total_act_next_24_hours
    type: sum
    sql: ${act_next_24_hours}
  - measure: total_orders_next_24_hours
    type: sum
    sql: ${orders_next_24_hours}
  - measure: total_order_revenue_next_24_hours
    type: sum
    sql: ${order_revenue_next_24_hours}
  - measure: total_act_next_30_days
    type: sum
    sql: ${act_next_30_days}
  - measure: total_orders_next_30_days
    type: sum
    sql: ${orders_next_30_days}
  - measure: total_order_revenue_next_30_days
    type: sum
    sql: ${order_revenue_next_30_days}
  - measure: total_orders_next_1_year
    type: sum
    sql: ${orders_next_1_year}
  - measure: total_order_revenue_next_1_year
    type: sum
    sql: ${order_revenue_next_1_year}
  - measure: total_act_next_1_year
    type: sum
    sql: ${act_next_1_year}

engagement_order_facts.sql
engagement_order_facts.sql
engagement_order_facts.view.lkml
engagement_order_facts.view.lookml

Engagement Order Facts

Blocks

Assumptions

Contents