dbt Crowdfunding

Event Attributes

Event Attributes model for crowdfunding.

inferred

Blocks

dbt SQL LKML LookML

_event_attributes.sql · 124 lines

inferred

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

(SELECT event_id AS "event_id"
   , sent_at as "sent_at"
   , received_at as "received_at"
   , anonymous_id as "anonymous_id"
   , user_id as "user_id"
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up started' as "event_type"
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up succesful'
   , NULL as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_success
   )

               UNION ALL
   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page creation started'
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page succesfully created'
   , NULL as link
   , flow_id
   , NULL as context_campaign_medium
   , NULL as context_campaign_source
   , NULL as context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_success
   )

# models/schema.yml
version: 2

models:
  - name: _event_attributes
    columns:
      - name: flow_id
      - name: context_campaign_medium
      - name: context_campaign_source
      - name: context_campaign_name
      - name: context_page_url
      - name: context_page_title
      - name: context_page_search
      - name: pk
        tests:
          - unique
          - not_null
      - name: event_id
      - name: context_page_referrer
      - name: campaign_uid
      - name: context_page_path
      - name: context_user_agent
      - name: region
      - name: anonymous_id
      - name: user_id
      - name: event_type
      - name: link
      - name: context_user_agent_device

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.

_event_attributes.sql · 92 lines

source-preserved validation: parses

(SELECT event_id AS "event_id"
   , sent_at as "sent_at"
   , received_at as "received_at"
   , anonymous_id as "anonymous_id"
   , user_id as "user_id"
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up started' as "event_type"
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up succesful'
   , NULL as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_success
   )

               UNION ALL
   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page creation started'
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page succesfully created'
   , NULL as link
   , flow_id
   , NULL as context_campaign_medium
   , NULL as context_campaign_source
   , NULL as context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_success
   )

_event_attributes.view.lkml · 245 lines

inferred

view: _event_attributes {
  derived_table: {
    sql: (SELECT event_id AS "event_id"
   , sent_at as "sent_at"
   , received_at as "received_at"
   , anonymous_id as "anonymous_id"
   , user_id as "user_id"
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up started' as "event_type"
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up succesful'
   , NULL as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_success
   )

               UNION ALL
   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page creation started'
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page succesfully created'
   , NULL as link
   , flow_id
   , NULL as context_campaign_medium
   , NULL as context_campaign_source
   , NULL as context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_success
   ) ;;
  }

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

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

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

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

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

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

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

  dimension: pk {
    primary_key: yes
    hidden: yes
    sql: ${event_id} || ${sent_at_time} || ${anonymous_id} || ${user_id} ;;
  }

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

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

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

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

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

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

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

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

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

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

  dimension: context_user_agent_device {
    sql: CASE
  WHEN POSITION('iPad' IN ${context_user_agent}) != 0 THEN 'Tablet'
  WHEN POSITION('iPhone' IN ${context_user_agent}) != 0 OR POSITION('iphone' IN ${context_user_agent}) != 0 AND POSITION('iPad' IN ${context_user_agent}) = 0 THEN 'Mobile Phone'
  WHEN POSITION('Windows' IN ${context_user_agent}) != 0 OR POSITION('windows' IN ${context_user_agent}) != 0 OR POSITION('Win/5.0' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Nokia' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('DoCoMo' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Mobile' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Linux' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Mac OS X' IN ${context_user_agent}) != 0 OR POSITION('Mac OSX' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Android' IN ${context_user_agent}) != 0 OR POSITION('android' IN ${context_user_agent}) != 0THEN 'Mobile Phone'
  WHEN POSITION('SAMSUNG' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Opera Mini' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Macintosh' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('X11' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('BlackBerry' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  ELSE 'Other'
END ;;
  }

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

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

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

  measure: count_sign_ups_started {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${aliases_mapping.universal_visitor_id} ;;
  }

  measure: count_successful_sign_ups {
    type: count_distinct
    sql: ${aliases_mapping.universal_visitor_id} ;;
  }

  measure: count_page_creation_started {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${aliases_mapping.universal_visitor_id} ;;
  }

  measure: count_successful_page_creation {
    type: count_distinct
    sql: ${aliases_mapping.universal_visitor_id} ;;
  }

  measure: conversion_rate_sign_up {
    type: number
    value_format: "##.00%"
    sql: 1.00*${count_successful_sign_ups}/NULLIF(${count_sign_ups_started},0) ;;
  }

  measure: conversion_rate_create_page {
    type: number
    value_format: "##.00%"
    sql: 1.00*${count_successful_page_creation}/NULLIF(${count_page_creation_started},0) ;;
  }
}

_event_attributes.view.lookml · 198 lines

source-preserved

- view: _event_attributes
  derived_table:
    sql: (SELECT event_id AS "event_id"
   , sent_at as "sent_at"
   , received_at as "received_at"
   , anonymous_id as "anonymous_id"
   , user_id as "user_id"
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up started' as "event_type"
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'sign up succesful'
   , NULL as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.sign_up_success
   )

               UNION ALL
   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page creation started'
   , link_id as link
   , flow_id
   , context_campaign_medium
   , context_campaign_source
   , context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_start
   )

   UNION ALL

   (SELECT event_id
   , sent_at
   , received_at
   , anonymous_id
   , user_id::text
   , region
   , context_page_referrer
   , campaign_uid
   , context_page_path
   , context_user_agent
   , 'page succesfully created'
   , NULL as link
   , flow_id
   , NULL as context_campaign_medium
   , NULL as context_campaign_source
   , NULL as context_campaign_name
   , context_page_url
   , context_page_title
   , context_page_search
   from production.page_creation_success
   )
  fields:
  - dimension: flow_id
    sql: ${TABLE}.flow_id
  - dimension: context_campaign_medium
    sql: ${TABLE}.context_campaign_medium
  - dimension: context_campaign_source
    sql: ${TABLE}.context_campaign_source
  - dimension: context_campaign_name
    sql: ${TABLE}.context_campaign_name
  - dimension: context_page_url
    sql: ${TABLE}.context_page_url
  - dimension: context_page_title
    sql: ${TABLE}.context_page_title
  - dimension: context_page_search
    sql: ${TABLE}.context_page_search
  - dimension: pk
    primary_key: true
    hidden: true
    sql: ${event_id} || ${sent_at_time} || ${anonymous_id} || ${user_id}
  - dimension: event_id
    hidden: true
    sql: ${TABLE}.event_id
  - dimension: context_page_referrer
    sql: ${TABLE}.context_page_referrer
  - dimension: campaign_uid
    sql: ${TABLE}.campaign_uid
  - dimension: context_page_path
    sql: ${TABLE}.context_page_path
  - dimension: context_user_agent
    sql: ${TABLE}.context_user_agent
  - dimension: region
    sql: ${TABLE}.region
  - dimension: anonymous_id
    hidden: true
    sql: ${TABLE}.anonymous_id
  - dimension: user_id
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: event_type
    sql: ${TABLE}.event_type
  - dimension: link
    sql: ${TABLE}.link
  - dimension: context_user_agent_device
    sql: CASE
  WHEN POSITION('iPad' IN ${context_user_agent}) != 0 THEN 'Tablet'
  WHEN POSITION('iPhone' IN ${context_user_agent}) != 0 OR POSITION('iphone' IN ${context_user_agent}) != 0 AND POSITION('iPad' IN ${context_user_agent}) = 0 THEN 'Mobile Phone'
  WHEN POSITION('Windows' IN ${context_user_agent}) != 0 OR POSITION('windows' IN ${context_user_agent}) != 0 OR POSITION('Win/5.0' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Nokia' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('DoCoMo' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Mobile' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Linux' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Mac OS X' IN ${context_user_agent}) != 0 OR POSITION('Mac OSX' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('Android' IN ${context_user_agent}) != 0 OR POSITION('android' IN ${context_user_agent}) != 0THEN 'Mobile Phone'
  WHEN POSITION('SAMSUNG' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Opera Mini' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  WHEN POSITION('Macintosh' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('X11' IN ${context_user_agent}) != 0 THEN 'Desktop'
  WHEN POSITION('BlackBerry' IN ${context_user_agent}) != 0 THEN 'Mobile Phone'
  ELSE 'Other'
END
  - dimension_group: sent_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.sent_at
  - dimension_group: received_at
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.received_at
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: count_sign_ups_started
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${aliases_mapping.universal_visitor_id}
  - measure: count_successful_sign_ups
    type: count_distinct
    sql: ${aliases_mapping.universal_visitor_id}
  - measure: count_page_creation_started
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${aliases_mapping.universal_visitor_id}
  - measure: count_successful_page_creation
    type: count_distinct
    sql: ${aliases_mapping.universal_visitor_id}
  - measure: conversion_rate_sign_up
    type: number
    value_format: '##.00%'
    sql: 1.00*${count_successful_sign_ups}/NULLIF(${count_sign_ups_started},0)
  - measure: conversion_rate_create_page
    type: number
    value_format: '##.00%'
    sql: 1.00*${count_successful_page_creation}/NULLIF(${count_page_creation_started},0)

_event_attributes.sql
_event_attributes.sql
_event_attributes.view.lkml
_event_attributes.view.lookml

Event Attributes

Blocks

Assumptions

Contents