data.ml
SQL Crowdfunding education

Referral

Referral model for crowdfunding education.

source-preserved

Blocks

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

SELECT
paymentid
, rfparam
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 1) ELSE NULL END) as medium
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 2) ELSE NULL END) as source
, (CASE
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' -- SIMILAR TO '%~_[0-9]{4}~_[0-9]{2}%' ESCAPE '~'
  THEN
    (CASE WHEN SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7) ~ '[0-9]{4}-[0-9]{2}'
    THEN to_timestamp(SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7), 'YYYY-MM')
    ELSE NULL END)
  ELSE NULL END) as campaign_date
, created
, (CASE
  WHEN rfparam like '%mmi%' THEN trim(substring(rfparam from '&?-mmi_?([^$]+)+?(?=)' ))
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacherid_x'
  ELSE split_part(rfparam, '-', 5) END) AS campaign
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacher'
  ELSE split_part(rfparam, '-', 6) END) as user_type
, landingurl

FROM ReferredSiteVisit visit
JOIN
  (SELECT paymentid, MAX(id) as visit_id
    FROM {{ ref('payment') }} pay
    INNER JOIN ReferredSiteVisit visit ON pay.donorid = visit.userid
    WHERE 1=1
    --Hiding this for a short while, just to troubleshoot some queries. It makes for a longer table in the meantime.
    --WHERE visit.rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%'
    AND (visit.rfparam IS NOT NULL AND LENGTH(visit.rfparam) > 0)
    AND pay.received = {% parameter y_value_placeholder %}
    AND pay.received_date BETWEEN visit.created AND visit.created + '5 days'
    GROUP BY 1
  ) one_to_one ON one_to_one.visit_id = visit.id
-- Must groupby, to remove duplicated resulting from visit table entries
GROUP BY 1,2,3,4,5,6,7,9

# models/schema.yml
version: 2

models:
  - name: referral
    columns:
      - name: paymentid
        tests:
          - unique
          - not_null
      - name: rfparam
      - name: medium
      - name: source
      - name: campaign
      - name: user_type
      - name: landing_url
      - name: mmi

Assumptions

  • Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
  • 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.
referral.sql · 36 lines
source-preserved validation: not checked 
SELECT
paymentid
, rfparam
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 1) ELSE NULL END) as medium
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 2) ELSE NULL END) as source
, (CASE
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' -- SIMILAR TO '%~_[0-9]{4}~_[0-9]{2}%' ESCAPE '~'
  THEN
    (CASE WHEN SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7) ~ '[0-9]{4}-[0-9]{2}'
    THEN to_timestamp(SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7), 'YYYY-MM')
    ELSE NULL END)
  ELSE NULL END) as campaign_date
, created
, (CASE
  WHEN rfparam like '%mmi%' THEN trim(substring(rfparam from '&?-mmi_?([^$]+)+?(?=)' ))
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacherid_x'
  ELSE split_part(rfparam, '-', 5) END) AS campaign
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacher'
  ELSE split_part(rfparam, '-', 6) END) as user_type
, landingurl

FROM ReferredSiteVisit visit
JOIN
  (SELECT paymentid, MAX(id) as visit_id
    FROM ${payment.SQL_TABLE_NAME} pay
    INNER JOIN ReferredSiteVisit visit ON pay.donorid = visit.userid
    WHERE 1=1
    --Hiding this for a short while, just to troubleshoot some queries. It makes for a longer table in the meantime.
    --WHERE visit.rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%'
    AND (visit.rfparam IS NOT NULL AND LENGTH(visit.rfparam) > 0)
    AND pay.received = {% parameter y_value_placeholder %}
    AND pay.received_date BETWEEN visit.created AND visit.created + '5 days'
    GROUP BY 1
  ) one_to_one ON one_to_one.visit_id = visit.id
-- Must groupby, to remove duplicated resulting from visit table entries
GROUP BY 1,2,3,4,5,6,7,9
referral.view.lkml · 90 lines
inferred 
view: referral {
  derived_table: {
    sql: SELECT
paymentid
, rfparam
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 1) ELSE NULL END) as medium
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 2) ELSE NULL END) as source
, (CASE
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' -- SIMILAR TO '%~_[0-9]{4}~_[0-9]{2}%' ESCAPE '~'
  THEN
    (CASE WHEN SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7) ~ '[0-9]{4}-[0-9]{2}'
    THEN to_timestamp(SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7), 'YYYY-MM')
    ELSE NULL END)
  ELSE NULL END) as campaign_date
, created
, (CASE
  WHEN rfparam like '%mmi%' THEN trim(substring(rfparam from '&?-mmi_?([^$]+)+?(?=)' ))
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacherid_x'
  ELSE split_part(rfparam, '-', 5) END) AS campaign
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacher'
  ELSE split_part(rfparam, '-', 6) END) as user_type
, landingurl

FROM ReferredSiteVisit visit
JOIN
  (SELECT paymentid, MAX(id) as visit_id
    FROM ${payment.SQL_TABLE_NAME} pay
    INNER JOIN ReferredSiteVisit visit ON pay.donorid = visit.userid
    WHERE 1=1
    --Hiding this for a short while, just to troubleshoot some queries. It makes for a longer table in the meantime.
    --WHERE visit.rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%'
    AND (visit.rfparam IS NOT NULL AND LENGTH(visit.rfparam) > 0)
    AND pay.received = {% parameter y_value_placeholder %}
    AND pay.received_date BETWEEN visit.created AND visit.created + '5 days'
    GROUP BY 1
  ) one_to_one ON one_to_one.visit_id = visit.id
-- Must groupby, to remove duplicated resulting from visit table entries
GROUP BY 1,2,3,4,5,6,7,9 ;;
  }

  dimension: paymentid {
    label: "Payment ID"
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.paymentid ;;
  }

  dimension: rfparam {
    sql: lower(${TABLE}.rfparam) ;;
  }

  dimension: medium {
    sql: lower(${TABLE}.medium) ;;
  }

  dimension: source {
    sql: lower(${TABLE}.source) ;;
  }

  dimension: campaign {
    sql: lower(${TABLE}.campaign) ;;
  }

  dimension: user_type {
    sql: lower(${TABLE}.user_type) ;;
  }

  dimension: landing_url {
    sql: lower(${TABLE}.landingurl) ;;
  }

  dimension: mmi {
    label: "REFERRAL MMI (Yes / No)"
    type: yesno
    sql: CASE WHEN ${TABLE}.rfparam like '%mmi%' THEN TRUE ELSE FALSE END ;;
  }

  dimension_group: campaign {
    type: time
    timeframes: [month]
    sql: ${TABLE}.campaign_date ;;
  }

  dimension_group: created {
    type: time
    timeframes: [date]
    sql: ${TABLE}.created ;;
  }
}
referral.view.lookml · 71 lines
source-preserved 
- view: referral
  derived_table:
    sql: SELECT
paymentid
, rfparam
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 1) ELSE NULL END) as medium
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' THEN split_part(rfparam, '-', 2) ELSE NULL END) as source
, (CASE
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%' -- SIMILAR TO '%~_[0-9]{4}~_[0-9]{2}%' ESCAPE '~'
  THEN
    (CASE WHEN SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7) ~ '[0-9]{4}-[0-9]{2}'
    THEN to_timestamp(SUBSTRING(rfparam FROM STRPOS(rfparam, '-20')+1 FOR 7), 'YYYY-MM')
    ELSE NULL END)
  ELSE NULL END) as campaign_date
, created
, (CASE
  WHEN rfparam like '%mmi%' THEN trim(substring(rfparam from '&?-mmi_?([^$]+)+?(?=)' ))
  WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacherid_x'
  ELSE split_part(rfparam, '-', 5) END) AS campaign
, (CASE WHEN rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%teacherid_[0-9]+%' THEN 'teacher'
  ELSE split_part(rfparam, '-', 6) END) as user_type
, landingurl

FROM ReferredSiteVisit visit
JOIN
  (SELECT paymentid, MAX(id) as visit_id
    FROM ${payment.SQL_TABLE_NAME} pay
    INNER JOIN ReferredSiteVisit visit ON pay.donorid = visit.userid
    WHERE 1=1
    --Hiding this for a short while, just to troubleshoot some queries. It makes for a longer table in the meantime.
    --WHERE visit.rfparam SIMILAR TO '%-[0-9]{4}-[0-9]{2}%'
    AND (visit.rfparam IS NOT NULL AND LENGTH(visit.rfparam) > 0)
    AND pay.received = {% parameter y_value_placeholder %}
    AND pay.received_date BETWEEN visit.created AND visit.created + '5 days'
    GROUP BY 1
  ) one_to_one ON one_to_one.visit_id = visit.id
-- Must groupby, to remove duplicated resulting from visit table entries
GROUP BY 1,2,3,4,5,6,7,9
  fields:
  - dimension: paymentid
    type: int
    primary_key: true
    hidden: true
    label: Payment ID
    sql: ${TABLE}.paymentid
  - dimension: rfparam
    sql: lower(${TABLE}.rfparam)
  - dimension: medium
    sql: lower(${TABLE}.medium)
  - dimension: source
    sql: lower(${TABLE}.source)
  - dimension: campaign
    sql: lower(${TABLE}.campaign)
  - dimension: user_type
    sql: lower(${TABLE}.user_type)
  - dimension: landing_url
    sql: lower(${TABLE}.landingurl)
  - dimension: mmi
    type: yesno
    label: REFERRAL MMI (Yes / No)
    sql: CASE WHEN ${TABLE}.rfparam like '%mmi%' THEN TRUE ELSE FALSE END
  - dimension_group: campaign
    type: time
    timeframes:
    - month
    sql: ${TABLE}.campaign_date
  - dimension_group: created
    type: time
    timeframes:
    - date
    sql: ${TABLE}.created

Contents

  • referral.sql
  • referral.sql
  • referral.view.lkml
  • referral.view.lookml