data.ml
SQL Delivery app

Users Active Facts Daily

Users Active Facts Daily model for delivery app.

source-preserved

Blocks

users_active_facts_daily.sql · 37 lines
inferred 
-- models/users_active_facts_daily.sql
{{ config(materialized='view') }}

SELECT LOWER(TRIM(source)) AS source,
        LOWER(TRIM(medium)) AS medium,
        --LOWER(TRIM(campaign)) AS campaign, --removing campaign for now
        DATE(active_date) AS active_date,
        COUNT(1) AS active_count,
        SUM(CASE WHEN promotion_spend is NULL THEN 0
            ELSE 1 END) AS promotion_count,
        sum(ltv) as summed_ltv,
        sum(smv) as summed_smv,
        sum(promotion_spend) as summed_promotion_spend
FROM analytics_users_signup_facts
where active_date is not null
GROUP BY 1,2,3

# models/schema.yml
version: 2

models:
  - name: users_active_facts_daily
    columns:
      - name: source
        tests:
          - unique
          - not_null
      - name: medium
      - name: facility
      - name: source_roll_up
      - name: active
      - name: daily_active_count
      - name: daily_promotion_count
      - name: summed_ltv
      - name: summed_smv
      - name: summed_promotion_spend
      - name: dummy_pivot

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.
users_active_facts_daily.sql · 13 lines
source-preserved validation: parses 
SELECT LOWER(TRIM(source)) AS source,
        LOWER(TRIM(medium)) AS medium,
        --LOWER(TRIM(campaign)) AS campaign, --removing campaign for now
        DATE(active_date) AS active_date,
        COUNT(1) AS active_count,
        SUM(CASE WHEN promotion_spend is NULL THEN 0
            ELSE 1 END) AS promotion_count,
        sum(ltv) as summed_ltv,
        sum(smv) as summed_smv,
        sum(promotion_spend) as summed_promotion_spend
FROM analytics_users_signup_facts
where active_date is not null
GROUP BY 1,2,3
users_active_facts_daily.view.lkml · 157 lines
inferred 
view: users_active_facts_daily {
  derived_table: {
    sql: SELECT LOWER(TRIM(source)) AS source,
        LOWER(TRIM(medium)) AS medium,
        --LOWER(TRIM(campaign)) AS campaign, --removing campaign for now
        DATE(active_date) AS active_date,
        COUNT(1) AS active_count,
        SUM(CASE WHEN promotion_spend is NULL THEN 0
            ELSE 1 END) AS promotion_count,
        sum(ltv) as summed_ltv,
        sum(smv) as summed_smv,
        sum(promotion_spend) as summed_promotion_spend
FROM analytics_users_signup_facts
where active_date is not null
GROUP BY 1,2,3 ;;
  }

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

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

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

  dimension: source_roll_up {
    sql: CASE WHEN ${medium} = 'email' AND lower(${source}) similar to 'invit%|market%' THEN ${source}
  WHEN ${medium} = 'email' THEN ''
WHEN ${medium} ilike 'referral' then ''
WHEN ${medium} = 'directmail' AND ${source} IN ('belardi', 'handybook') THEN lower(${source})
WHEN ${medium} = 'directmail' THEN 'other'
WHEN ${medium} = 'video-paid' AND ${source} IN ('facebook', 'youtube', 'hulu', 'virool') THEN lower(${source})
WHEN ${medium} = 'video-paid' THEN 'other'
else ${source}
END ;;
  }

  dimension: active {
    type: time
    sql: ${TABLE}.active_date ;;
  }

  dimension: daily_active_count {
    type: number
    hidden: yes
    sql: ${TABLE}.active_count ;;
  }

  dimension: daily_promotion_count {
    type: number
    hidden: yes
    sql: ${TABLE}.promotion_count ;;
  }

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

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

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

  dimension: dummy_pivot {
    label: "x"
    type: int
    sql: case when 1=1 then null end ;;
  }

  measure: total_ltv {
    type: sum
    sql: ${summed_ltv} ;;
  }

  measure: total_smv {
    type: sum
    sql: ${summed_smv} ;;
  }

  measure: avg_ltv {
    type: number
    sql: ${total_ltv}/nullif(${total_active_count},0) ;;
  }

  measure: avg_smv {
    type: number
    sql: ${total_smv}/nullif(${total_active_count},0) ;;
  }

  measure: total_active_count {
    type: sum
    sql: ${daily_active_count} ;;
  }

  measure: total_promotion_count {
    type: sum
    sql: ${daily_promotion_count} ;;
  }

  measure: total_promotion_spend_amount {
    type: sum
    sql: ${summed_promotion_spend} ;;
  }

  measure: cpa {
    label: "MARKETING CPA"
    type: number
    sql: ${marketing_spend.total_spend_amount}/nullif(${total_active_count},0) ;;
  }

  measure: cpa2 {
    label: "Cpa"
    type: number
    sql: ${marketing_spend.total_spend_amount}/nullif(${total_active_count},0) ;;
  }

  measure: adjusted_cpa {
    label: "MARKETING Adjusted CPA"
    type: number
    sql: (coalesce(${marketing_spend.total_spend_amount},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_active_count},0) ;;
  }

  measure: adjusted_cpa2 {
    label: "Adjusted CPA"
    type: number
    sql: (coalesce(${marketing_spend.total_spend_amount},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_active_count},0) ;;
  }

  measure: margin {
    type: number
    sql: (${avg_ltv}-${cpa}) ;;
  }

  measure: roi_smv {
    type: number
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100 ;;
  }

  measure: roi_ltv {
    type: number
    sql: (${avg_ltv}/nullif(${adjusted_cpa},0) - 1) * 100 ;;
  }
}
users_active_facts_daily.view.lookml · 106 lines
source-preserved 
- view: users_active_facts_daily
  derived_table:
    sql: SELECT LOWER(TRIM(source)) AS source,
        LOWER(TRIM(medium)) AS medium,
        --LOWER(TRIM(campaign)) AS campaign, --removing campaign for now
        DATE(active_date) AS active_date,
        COUNT(1) AS active_count,
        SUM(CASE WHEN promotion_spend is NULL THEN 0
            ELSE 1 END) AS promotion_count,
        sum(ltv) as summed_ltv,
        sum(smv) as summed_smv,
        sum(promotion_spend) as summed_promotion_spend
FROM analytics_users_signup_facts
where active_date is not null
GROUP BY 1,2,3
  fields:
  - dimension: source
    primary_key: true
    sql: ${TABLE}.source
  - dimension: medium
    sql: ${TABLE}.medium
  - dimension: facility
    sql: ${TABLE}.facility
  - dimension: source_roll_up
    sql: CASE WHEN ${medium} = 'email' AND lower(${source}) similar to 'invit%|market%' THEN ${source}
  WHEN ${medium} = 'email' THEN ''
WHEN ${medium} ilike 'referral' then ''
WHEN ${medium} = 'directmail' AND ${source} IN ('belardi', 'handybook') THEN lower(${source})
WHEN ${medium} = 'directmail' THEN 'other'
WHEN ${medium} = 'video-paid' AND ${source} IN ('facebook', 'youtube', 'hulu', 'virool') THEN lower(${source})
WHEN ${medium} = 'video-paid' THEN 'other'
else ${source}
END
  - dimension: active
    type: time
    sql: ${TABLE}.active_date
  - dimension: daily_active_count
    type: number
    hidden: true
    sql: ${TABLE}.active_count
  - dimension: daily_promotion_count
    type: number
    hidden: true
    sql: ${TABLE}.promotion_count
  - dimension: summed_ltv
    type: number
    hidden: true
    sql: ${TABLE}.summed_ltv
  - dimension: summed_smv
    type: number
    hidden: true
    sql: ${TABLE}.summed_smv
  - dimension: summed_promotion_spend
    type: number
    hidden: true
    sql: ${TABLE}.summed_promotion_spend
  - dimension: dummy_pivot
    type: int
    label: x
    sql: case when 1=1 then null end
  - measure: total_ltv
    type: sum
    sql: ${summed_ltv}
  - measure: total_smv
    type: sum
    sql: ${summed_smv}
  - measure: avg_ltv
    type: number
    sql: ${total_ltv}/nullif(${total_active_count},0)
  - measure: avg_smv
    type: number
    sql: ${total_smv}/nullif(${total_active_count},0)
  - measure: total_active_count
    type: sum
    sql: ${daily_active_count}
  - measure: total_promotion_count
    type: sum
    sql: ${daily_promotion_count}
  - measure: total_promotion_spend_amount
    type: sum
    sql: ${summed_promotion_spend}
  - measure: cpa
    type: number
    label: MARKETING CPA
    sql: ${marketing_spend.total_spend_amount}/nullif(${total_active_count},0)
  - measure: cpa2
    type: number
    label: Cpa
    sql: ${marketing_spend.total_spend_amount}/nullif(${total_active_count},0)
  - measure: adjusted_cpa
    type: number
    label: MARKETING Adjusted CPA
    sql: (coalesce(${marketing_spend.total_spend_amount},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_active_count},0)
  - measure: adjusted_cpa2
    type: number
    label: Adjusted CPA
    sql: (coalesce(${marketing_spend.total_spend_amount},0) + coalesce(${total_promotion_spend_amount},0))/nullif(${total_active_count},0)
  - measure: margin
    type: number
    sql: (${avg_ltv}-${cpa})
  - measure: roi_smv
    type: number
    sql: (${avg_smv}/nullif(${adjusted_cpa},0) - 1) * 100
  - measure: roi_ltv
    type: number
    sql: (${avg_ltv}/nullif(${adjusted_cpa},0) - 1) * 100

Contents

  • users_active_facts_daily.sql
  • users_active_facts_daily.sql
  • users_active_facts_daily.view.lkml
  • users_active_facts_daily.view.lookml