dbt Ecommerce marketplace

Actual

Actual model for ecommerce marketplace.

inferred

Blocks

dbt SQL LKML LookML

actual.sql · 36 lines

inferred

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

SELECT
  DATE(cart_item_fact.checkout_dt)
  , category.category_name AS category
  , marketing_channel.summary
  , SUM(revenue_minus_vat) AS total_revenue
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 THEN cart_item_fact.user_id ELSE NULL END) AS total_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.user_id ELSE NULL END) AS digital_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.user_id ELSE NULL END) AS physical_new_buyers
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS digital_revenue
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS physical_revenue
FROM warehouse.cart_item_fact
  LEFT JOIN warehouse.user_scorecard user_scorecard ON user_scorecard.user_id = cart_item_fact.user_id
  LEFT JOIN javaprod.category category ON category.category_id = user_scorecard.primary_category_id
  LEFT JOIN javaprod.external_link external_link ON external_link.external_link_id = cart_item_fact.external_link_id
  LEFT JOIN javaprod.marketing_action marketing_action ON marketing_action.marketing_action_id = external_link.marketing_action_id
  LEFT JOIN javaprod.marketing_channel marketing_channel ON marketing_channel.marketing_channel_id = marketing_action.marketing_channel_id
  LEFT JOIN warehouse.cart_fact cart_fact ON cart_fact.cart_fact_id = cart_item_fact.cart_fact_id
GROUP BY 1, 2, 3

# models/schema.yml
version: 2

models:
  - name: actual
    columns:
      - name: category_name
      - name: summary
      - name: total_revenue_dim
      - name: total_new_buyers_dim
      - name: digital_new_buyers_dim
      - name: physical_new_buyers_dim
      - name: digital_revenue_dim
      - name: physical_revenue_dim

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.

actual.sql · 18 lines

source-preserved validation: parses

SELECT
  DATE(cart_item_fact.checkout_dt)
  , category.category_name AS category
  , marketing_channel.summary
  , SUM(revenue_minus_vat) AS total_revenue
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 THEN cart_item_fact.user_id ELSE NULL END) AS total_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.user_id ELSE NULL END) AS digital_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.user_id ELSE NULL END) AS physical_new_buyers
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS digital_revenue
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS physical_revenue
FROM warehouse.cart_item_fact
  LEFT JOIN warehouse.user_scorecard user_scorecard ON user_scorecard.user_id = cart_item_fact.user_id
  LEFT JOIN javaprod.category category ON category.category_id = user_scorecard.primary_category_id
  LEFT JOIN javaprod.external_link external_link ON external_link.external_link_id = cart_item_fact.external_link_id
  LEFT JOIN javaprod.marketing_action marketing_action ON marketing_action.marketing_action_id = external_link.marketing_action_id
  LEFT JOIN javaprod.marketing_channel marketing_channel ON marketing_channel.marketing_channel_id = marketing_action.marketing_channel_id
  LEFT JOIN warehouse.cart_fact cart_fact ON cart_fact.cart_fact_id = cart_item_fact.cart_fact_id
GROUP BY 1, 2, 3

actual.view.lkml · 117 lines

inferred

view: actual {
  derived_table: {
    sql: SELECT
  DATE(cart_item_fact.checkout_dt)
  , category.category_name AS category
  , marketing_channel.summary
  , SUM(revenue_minus_vat) AS total_revenue
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 THEN cart_item_fact.user_id ELSE NULL END) AS total_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.user_id ELSE NULL END) AS digital_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.user_id ELSE NULL END) AS physical_new_buyers
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS digital_revenue
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS physical_revenue
FROM warehouse.cart_item_fact
  LEFT JOIN warehouse.user_scorecard user_scorecard ON user_scorecard.user_id = cart_item_fact.user_id
  LEFT JOIN javaprod.category category ON category.category_id = user_scorecard.primary_category_id
  LEFT JOIN javaprod.external_link external_link ON external_link.external_link_id = cart_item_fact.external_link_id
  LEFT JOIN javaprod.marketing_action marketing_action ON marketing_action.marketing_action_id = external_link.marketing_action_id
  LEFT JOIN javaprod.marketing_channel marketing_channel ON marketing_channel.marketing_channel_id = marketing_action.marketing_channel_id
  LEFT JOIN warehouse.cart_fact cart_fact ON cart_fact.cart_fact_id = cart_item_fact.cart_fact_id
GROUP BY 1, 2, 3 ;;
  }

  dimension: category_name {
    sql: ${TABLE}.category_name ;;
  }

  dimension: summary {
    sql: ${TABLE}.summary ;;
  }

  dimension: total_revenue_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.total_revenue ;;
  }

  dimension: total_new_buyers_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.total_new_buyers ;;
  }

  dimension: digital_new_buyers_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.digital_new_buyers ;;
  }

  dimension: physical_new_buyers_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.physical_new_buyers ;;
  }

  dimension: digital_revenue_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.digital_revenue ;;
  }

  dimension: physical_revenue_dim {
    type: number
    hidden: yes
    sql: ${TABLE}.physical_revenue ;;
  }

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

  measure: digital_revenue {
    type: sum
    sql: ${digital_revenue_dim} ;;
  }

  measure: digital_revenue_cumulative {
    type: running_total
    sql: ${digital_revenue} ;;
  }

  measure: physical_revenue {
    type: sum
    sql: ${physical_revenue_dim} ;;
  }

  measure: physical_revenue_cumulative {
    type: running_total
    sql: ${physical_revenue} ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${total_revenue_dim} ;;
  }

  measure: total_revenue_cumulative {
    type: running_total
    sql: ${total_revenue} ;;
  }

  measure: total_new_buyers {
    type: sum
    sql: ${total_new_buyers_dim} ;;
  }

  measure: digital_new_buyers {
    type: sum
    sql: ${digital_new_buyers_dim} ;;
  }

  measure: phsyical_new_buyers {
    type: sum
    sql: ${physical_new_buyers_dim} ;;
  }
}

actual.view.lookml · 83 lines

source-preserved

- view: actual
  derived_table:
    sql: SELECT
  DATE(cart_item_fact.checkout_dt)
  , category.category_name AS category
  , marketing_channel.summary
  , SUM(revenue_minus_vat) AS total_revenue
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 THEN cart_item_fact.user_id ELSE NULL END) AS total_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.user_id ELSE NULL END) AS digital_new_buyers
  , COUNT(DISTINCT CASE WHEN cart_fact.user_paid_cart_ordinal = 1 AND cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.user_id ELSE NULL END) AS physical_new_buyers
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'COURSE' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS digital_revenue
  , SUM (CASE WHEN cart_item_fact.course_or_ecomm = 'ECOMM' THEN cart_item_fact.revenue_minus_vat ELSE 0 END) AS physical_revenue
FROM warehouse.cart_item_fact
  LEFT JOIN warehouse.user_scorecard user_scorecard ON user_scorecard.user_id = cart_item_fact.user_id
  LEFT JOIN javaprod.category category ON category.category_id = user_scorecard.primary_category_id
  LEFT JOIN javaprod.external_link external_link ON external_link.external_link_id = cart_item_fact.external_link_id
  LEFT JOIN javaprod.marketing_action marketing_action ON marketing_action.marketing_action_id = external_link.marketing_action_id
  LEFT JOIN javaprod.marketing_channel marketing_channel ON marketing_channel.marketing_channel_id = marketing_action.marketing_channel_id
  LEFT JOIN warehouse.cart_fact cart_fact ON cart_fact.cart_fact_id = cart_item_fact.cart_fact_id
GROUP BY 1, 2, 3
  fields:
  - dimension: category_name
    sql: ${TABLE}.category_name
  - dimension: summary
    sql: ${TABLE}.summary
  - dimension: total_revenue_dim
    type: number
    hidden: true
    sql: ${TABLE}.total_revenue
  - dimension: total_new_buyers_dim
    type: number
    hidden: true
    sql: ${TABLE}.total_new_buyers
  - dimension: digital_new_buyers_dim
    type: number
    hidden: true
    sql: ${TABLE}.digital_new_buyers
  - dimension: physical_new_buyers_dim
    type: number
    hidden: true
    sql: ${TABLE}.physical_new_buyers
  - dimension: digital_revenue_dim
    type: number
    hidden: true
    sql: ${TABLE}.digital_revenue
  - dimension: physical_revenue_dim
    type: number
    hidden: true
    sql: ${TABLE}.physical_revenue
  - dimension_group: daily_aggregate
    type: time
    timeframes:
    - date
    - month
    - year
    sql: ${TABLE}.date
  - measure: digital_revenue
    type: sum
    sql: ${digital_revenue_dim}
  - measure: digital_revenue_cumulative
    type: running_total
    sql: ${digital_revenue}
  - measure: physical_revenue
    type: sum
    sql: ${physical_revenue_dim}
  - measure: physical_revenue_cumulative
    type: running_total
    sql: ${physical_revenue}
  - measure: total_revenue
    type: sum
    sql: ${total_revenue_dim}
  - measure: total_revenue_cumulative
    type: running_total
    sql: ${total_revenue}
  - measure: total_new_buyers
    type: sum
    sql: ${total_new_buyers_dim}
  - measure: digital_new_buyers
    type: sum
    sql: ${digital_new_buyers_dim}
  - measure: phsyical_new_buyers
    type: sum
    sql: ${physical_new_buyers_dim}

actual.sql
actual.sql
actual.view.lkml
actual.view.lookml

Actual

Blocks

Assumptions

Contents