SQL Social greetings

All Orders

All Orders model for social greetings.

source-preserved

Blocks

dbt SQL LKML LookML

all_orders.sql · 72 lines

inferred

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

SELECT
id::text,
NULL as document_id,
amount,
user_agent,
owner_id::text,
status,
source,
created_at,
date(created_at) as created_join,
updated_at,
shipping_option_id,
billing_address_id,
comments,
promo_code_id,
discount,
tracking_numbers,
notification_sent,
'OrderSource' as village_or_underwood
FROM orders

UNION

SELECT
object_id as id,
document_id,
(_extra_props ->> 'total')::real as amount,
_extra_props ->> 'user_agent' as user_agent,
user_id as owner_id,
_extra_props ->> 'state' as status,
'app' as source,
created_at,
date(created_at) as created_join,
NULL as updated_at,
NULL as shipping_option_id,
NULL as billing_address_id,
NULL as comments,
NULL as promo_code_id,
NULL as discount,
NULL as tracking_numbers,
NULL as notification_sent,
'AltOrderSource' as village_or_underwood
FROM community_orders

# models/schema.yml
version: 2

models:
  - name: all_orders
    columns:
      - name: created_join
      - name: id
        tests:
          - unique
          - not_null
      - name: document_id
      - name: amount
      - name: owner_id
      - name: status
      - name: source
      - name: shipping_option_id
      - name: billing_address_id
      - name: comments
      - name: promo_code_id
      - name: discount
      - name: tracking_numbers
      - name: notification_sent
      - name: village_or_underwood
      - name: user_agent

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_orders.sql · 43 lines

source-preserved validation: parses

SELECT
id::text,
NULL as document_id,
amount,
user_agent,
owner_id::text,
status,
source,
created_at,
date(created_at) as created_join,
updated_at,
shipping_option_id,
billing_address_id,
comments,
promo_code_id,
discount,
tracking_numbers,
notification_sent,
'OrderSource' as village_or_underwood
FROM orders

UNION

SELECT
object_id as id,
document_id,
(_extra_props ->> 'total')::real as amount,
_extra_props ->> 'user_agent' as user_agent,
user_id as owner_id,
_extra_props ->> 'state' as status,
'app' as source,
created_at,
date(created_at) as created_join,
NULL as updated_at,
NULL as shipping_option_id,
NULL as billing_address_id,
NULL as comments,
NULL as promo_code_id,
NULL as discount,
NULL as tracking_numbers,
NULL as notification_sent,
'AltOrderSource' as village_or_underwood
FROM community_orders

all_orders.view.lkml · 176 lines

inferred

view: all_orders {
  derived_table: {
    sql: SELECT
id::text,
NULL as document_id,
amount,
user_agent,
owner_id::text,
status,
source,
created_at,
date(created_at) as created_join,
updated_at,
shipping_option_id,
billing_address_id,
comments,
promo_code_id,
discount,
tracking_numbers,
notification_sent,
'OrderSource' as village_or_underwood
FROM orders

UNION

SELECT
object_id as id,
document_id,
(_extra_props ->> 'total')::real as amount,
_extra_props ->> 'user_agent' as user_agent,
user_id as owner_id,
_extra_props ->> 'state' as status,
'app' as source,
created_at,
date(created_at) as created_join,
NULL as updated_at,
NULL as shipping_option_id,
NULL as billing_address_id,
NULL as comments,
NULL as promo_code_id,
NULL as discount,
NULL as tracking_numbers,
NULL as notification_sent,
'AltOrderSource' as village_or_underwood
FROM community_orders ;;
  }

  dimension: created_join {
    hidden: yes
    sql: ${TABLE}.created_join ;;
  }

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

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

  dimension: amount {
    type: number
    value_format: "$#,##0.00"
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END ;;
  }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  measure: total_amount {
    type: sum
    value_format: "$#,##0.00"
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END ;;
  }

  measure: average_order_value {
    type: average
    value_format: "$#,##0.00"
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END ;;
  }

  measure: average_revenue_per_customer {
    description: "arpc- average rev per customer"
    type: number
    value_format: "$#,##0.00"
    sql: ${total_amount}/${count_distinct_customers_placing_orders} ;;
  }

  measure: commerce_conversion_rate {
    type: number
    value_format: "0.00\%"
    sql: (${count}/${ga_main_derived.total_users})*100 ;;
  }

  measure: customer_conversion_rate {
    type: number
    value_format: "0.00\%"
    sql: (${count_distinct_customers_placing_orders}/${ga_main_derived.total_users})*100 ;;
  }

  measure: count_distinct_customers_placing_orders {
    type: count_distinct
    sql: ${owner_id} ;;
  }
}

all_orders.view.lookml · 136 lines

source-preserved

- view: all_orders
  derived_table:
    sql: SELECT
id::text,
NULL as document_id,
amount,
user_agent,
owner_id::text,
status,
source,
created_at,
date(created_at) as created_join,
updated_at,
shipping_option_id,
billing_address_id,
comments,
promo_code_id,
discount,
tracking_numbers,
notification_sent,
'OrderSource' as village_or_underwood
FROM orders

UNION

SELECT
object_id as id,
document_id,
(_extra_props ->> 'total')::real as amount,
_extra_props ->> 'user_agent' as user_agent,
user_id as owner_id,
_extra_props ->> 'state' as status,
'app' as source,
created_at,
date(created_at) as created_join,
NULL as updated_at,
NULL as shipping_option_id,
NULL as billing_address_id,
NULL as comments,
NULL as promo_code_id,
NULL as discount,
NULL as tracking_numbers,
NULL as notification_sent,
'AltOrderSource' as village_or_underwood
FROM community_orders
  fields:
  - dimension: created_join
    hidden: true
    sql: ${TABLE}.created_join
  - dimension: id
    primary_key: true
    sql: ${TABLE}.id
  - dimension: document_id
    sql: ${TABLE}.document_id
  - dimension: amount
    type: number
    value_format: '$#,##0.00'
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END
  - dimension: owner_id
    sql: ${TABLE}.owner_id
  - dimension: status
    sql: ${TABLE}.status
  - dimension: source
    sql: ${TABLE}.source
  - dimension: shipping_option_id
    type: number
    sql: ${TABLE}.shipping_option_id
  - dimension: billing_address_id
    sql: ${TABLE}.billing_address_id
  - dimension: comments
    sql: ${TABLE}.comments
  - dimension: promo_code_id
    type: number
    sql: ${TABLE}.promo_code_id
  - dimension: discount
    type: number
    sql: ${TABLE}.discount
  - dimension: tracking_numbers
    sql: ${TABLE}.tracking_numbers
  - dimension: notification_sent
    sql: ${TABLE}.notification_sent
  - dimension: village_or_underwood
    sql: ${TABLE}.village_or_underwood
  - dimension: user_agent
    sql: ${TABLE}.user_agent
  - dimension_group: created_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    convert_tz: false
    sql: ${TABLE}.created_at
  - dimension_group: updated_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    convert_tz: false
    sql: ${TABLE}.updated_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: total_amount
    type: sum
    value_format: '$#,##0.00'
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END
  - measure: average_order_value
    type: average
    value_format: '$#,##0.00'
    sql: CASE WHEN village_or_underwood = 'AltOrderSource' THEN (${TABLE}.amount/100)
ELSE ${TABLE}.amount END
  - measure: average_revenue_per_customer
    type: number
    description: arpc- average rev per customer
    value_format: '$#,##0.00'
    sql: ${total_amount}/${count_distinct_customers_placing_orders}
  - measure: commerce_conversion_rate
    type: number
    value_format: '0.00\%'
    sql: (${count}/${ga_main_derived.total_users})*100
  - measure: customer_conversion_rate
    type: number
    value_format: '0.00\%'
    sql: (${count_distinct_customers_placing_orders}/${ga_main_derived.total_users})*100
  - measure: count_distinct_customers_placing_orders
    type: count_distinct
    sql: ${owner_id}

all_orders.sql
all_orders.sql
all_orders.view.lkml
all_orders.view.lookml

All Orders

Blocks

Assumptions

Contents