SQL Entertainment tech

Account Event Facts

Account Event Facts model for entertainment tech.

source-preserved

Blocks

dbt SQL LKML LookML

account_event_facts.sql · 126 lines

inferred

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

SELECT
  ss.install AS visitor_id
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Doctor Of The Heart', 'E01 Baby Bird Short', 'E01 Space', 'Mouse and Cheese', 'Winston Sly - This Little Piggy')
        THEN 1
        ELSE 0
      END
  ) AS e1_events
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Crayons Short', 'E01 Lion v Zebra', 'E02 Intro - Let\'s be Friends', 'E02 Space Quest', 'Enchanted Forest', 'Winston Sly Trouble\'s Brewin')
        THEN 1
        ELSE 0
      END
  ) AS e2_events
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$manufacturer'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_manufacturer
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$app_release'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_app_release
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_SHOWN'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_started
  , MIN(
      CASE
        WHEN ss.category = 'PLAY_SCREEN_ENTRY'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_entered
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete_tap
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION' AND ss.statistic_value = 'SUCCESS'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CONSENT_DONE_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS consent_old
  , MIN(
      CASE
        WHEN ss.category = 'APP_OPENED' AND ss.account IS NULL
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_open
  , (
    SELECT ss1.account
    FROM statistic_string AS ss1
    WHERE ss.install = ss1.install
      AND ss1.account IS NOT NULL
    ORDER BY ss1.time_utc ASC
    LIMIT 1) AS account_id
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-07-01'
GROUP BY visitor_id

# models/schema.yml
version: 2

models:
  - name: account_event_facts
    columns:
      - name: visitor_id
        tests:
          - unique
          - not_null
      - name: has_complete_e1
      - name: has_complete_e2
      - name: account_id
      - name: first_os_version
      - name: first_major_os_version
      - name: first_manufacturer
      - name: first_os
      - name: first_app_release
      - name: days_since_activation
      - name: days_since_activation_tier
      - name: history

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.

account_event_facts.sql · 101 lines

source-preserved validation: parses

SELECT
  ss.install AS visitor_id
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Doctor Of The Heart', 'E01 Baby Bird Short', 'E01 Space', 'Mouse and Cheese', 'Winston Sly - This Little Piggy')
        THEN 1
        ELSE 0
      END
  ) AS e1_events
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Crayons Short', 'E01 Lion v Zebra', 'E02 Intro - Let\'s be Friends', 'E02 Space Quest', 'Enchanted Forest', 'Winston Sly Trouble\'s Brewin')
        THEN 1
        ELSE 0
      END
  ) AS e2_events
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$manufacturer'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_manufacturer
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$app_release'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_app_release
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_SHOWN'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_started
  , MIN(
      CASE
        WHEN ss.category = 'PLAY_SCREEN_ENTRY'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_entered
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete_tap
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION' AND ss.statistic_value = 'SUCCESS'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CONSENT_DONE_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS consent_old
  , MIN(
      CASE
        WHEN ss.category = 'APP_OPENED' AND ss.account IS NULL
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_open
  , (
    SELECT ss1.account
    FROM statistic_string AS ss1
    WHERE ss.install = ss1.install
      AND ss1.account IS NOT NULL
    ORDER BY ss1.time_utc ASC
    LIMIT 1) AS account_id
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-07-01'
GROUP BY visitor_id

account_event_facts.view.lkml · 257 lines

inferred

view: account_event_facts {
  derived_table: {
    sql: SELECT
  ss.install AS visitor_id
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Doctor Of The Heart', 'E01 Baby Bird Short', 'E01 Space', 'Mouse and Cheese', 'Winston Sly - This Little Piggy')
        THEN 1
        ELSE 0
      END
  ) AS e1_events
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Crayons Short', 'E01 Lion v Zebra', 'E02 Intro - Let\'s be Friends', 'E02 Space Quest', 'Enchanted Forest', 'Winston Sly Trouble\'s Brewin')
        THEN 1
        ELSE 0
      END
  ) AS e2_events
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$manufacturer'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_manufacturer
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$app_release'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_app_release
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_SHOWN'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_started
  , MIN(
      CASE
        WHEN ss.category = 'PLAY_SCREEN_ENTRY'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_entered
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete_tap
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION' AND ss.statistic_value = 'SUCCESS'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CONSENT_DONE_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS consent_old
  , MIN(
      CASE
        WHEN ss.category = 'APP_OPENED' AND ss.account IS NULL
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_open
  , (
    SELECT ss1.account
    FROM statistic_string AS ss1
    WHERE ss.install = ss1.install
      AND ss1.account IS NOT NULL
    ORDER BY ss1.time_utc ASC
    LIMIT 1) AS account_id
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-07-01'
GROUP BY visitor_id ;;
  }

  dimension: visitor_id {
    primary_key: yes
  }

  dimension: has_complete_e1 {
    type: yesno
    sql: ${TABLE}.e1_events > 0 ;;
  }

  dimension: has_complete_e2 {
    type: yesno
    sql: ${TABLE}.e2_events > 0 ;;
  }

  dimension: account_id {
  }

  dimension: first_os_version {
  }

  dimension: first_major_os_version {
    sql: SUBSTRING_INDEX(${first_os_version},'.',1) ;;
  }

  dimension: first_manufacturer {
  }

  dimension: first_os {
  }

  dimension: first_app_release {
  }

  dimension: days_since_activation {
    type: int
    sql: DATEDIFF(CURRENT_DATE,${consent_date}) ;;
  }

  dimension: days_since_activation_tier {
    type: tier
    sql: ${days_since_activation} ;;
  }

  dimension: history {
    sql: ${visitor_id} ;;
  }

  dimension_group: first_open {
    type: time
    timeframes: [yesno, time, date, week, month]
  }

  dimension_group: signup_complete_tap {
    type: time
    timeframes: [yesno, time, date, week, month]
  }

  dimension_group: signup_started {
    type: time
    timeframes: [yesno, time, date, week, month]
  }

  dimension_group: signup_complete {
    type: time
    timeframes: [yesno, time, date, week, month]
  }

  dimension_group: consent {
    type: time
    timeframes: [yesno, time, date, week, month]
    sql: ${account_details.consent_received_time} ;;
  }

  dimension_group: first_entered {
    type: time
    timeframes: [yesno, time, date, week, month]
  }

  measure: account.count {
    type: count
  }

  measure: account.percent_of_total {
    type: percent_of_total
    sql: ${account.count} ;;
  }

  measure: account.opened_count {
    type: count
    filters: [first_open: "True"]
  }

  measure: account.entered_opened_percent {
    type: number
    sql: 100.0 * ${account.opened_count}/${account.entered_count} ;;
  }

  measure: account.signup_started_count {
    type: count
    filters: [signup_started: "True"]
  }

  measure: account.opened_started_percent {
    type: number
    sql: 100.0 * ${account.signup_started_count}/${account.opened_count} ;;
  }

  measure: account.signup_complete_count {
    type: count
    filters: [signup_complete: "True"]
  }

  measure: account.started_complete_tap_percent {
    type: number
    sql: 100.0 * ${account.signup_complete_tap_count}/${account.signup_started_count} ;;
  }

  measure: account.consent_count {
    type: count
    filters: [consent: "True"]
  }

  measure: account.complete_tap_completed_percent {
    type: number
    sql: 100.0 * ${account.signup_complete_count}/${account.signup_complete_tap_count} ;;
  }

  measure: account.signup_complete_tap_count {
    type: count
    filters: [signup_complete_tap: "True"]
  }

  measure: account.complete_consent_percent {
    type: number
    sql: 100.0 * ${account.consent_count}/${account.signup_complete_count} ;;
  }

  measure: account.entered_count {
    type: count
    filters: [first_entered: "True"]
  }

  measure: account.opened_app_to_consent_percent {
    type: number
    sql: 100.0 * ${account.consent_count}/${account.entered_count} ;;
  }

  measure: account.opened_app_to_created_percent {
    type: number
    sql: 100.0 * ${account.signup_complete_count}/${account.entered_count} ;;
  }
}

account_event_facts.view.lookml · 226 lines

source-preserved

- view: account_event_facts
  derived_table:
    sql: SELECT
  ss.install AS visitor_id
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Doctor Of The Heart', 'E01 Baby Bird Short', 'E01 Space', 'Mouse and Cheese', 'Winston Sly - This Little Piggy')
        THEN 1
        ELSE 0
      END
  ) AS e1_events
  , SUM(
      CASE
        WHEN ss.category ='ACTIVITY_COMPLETED'
        AND ss.statistic_type ='CURRENT_ACTIVITY_NAME'
        AND ss.statistic_value IN ('Crayons Short', 'E01 Lion v Zebra', 'E02 Intro - Let\'s be Friends', 'E02 Space Quest', 'Enchanted Forest', 'Winston Sly Trouble\'s Brewin')
        THEN 1
        ELSE 0
      END
  ) AS e2_events
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os_version'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os_version
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$manufacturer'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_manufacturer
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$os'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_os
  , MIN(
      CASE
        WHEN ss.account IS NULL AND ss.sequence = 0 AND ss.statistic_type = '$app_release'
        THEN ss.statistic_value
        ELSE NULL
      END
    ) AS first_app_release
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_SHOWN'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_started
  , MIN(
      CASE
        WHEN ss.category = 'PLAY_SCREEN_ENTRY'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_entered
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete_tap
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CREATION' AND ss.statistic_value = 'SUCCESS'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS signup_complete
  , MIN(
      CASE
        WHEN ss.category = 'ACCOUNT_CONSENT_DONE_PRESSED'
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS consent_old
  , MIN(
      CASE
        WHEN ss.category = 'APP_OPENED' AND ss.account IS NULL
        THEN ss.time_utc
        ELSE NULL
      END
    ) AS first_open
  , (
    SELECT ss1.account
    FROM statistic_string AS ss1
    WHERE ss.install = ss1.install
      AND ss1.account IS NOT NULL
    ORDER BY ss1.time_utc ASC
    LIMIT 1) AS account_id
FROM statistic_string AS ss
WHERE ss.application IN ('ShowName', 'TheShowName')
  AND ss.time_utc > '2013-07-01'
GROUP BY visitor_id
  fields:
  - dimension: visitor_id
    primary_key: true
  - dimension: has_complete_e1
    type: yesno
    sql: ${TABLE}.e1_events > 0
  - dimension: has_complete_e2
    type: yesno
    sql: ${TABLE}.e2_events > 0
  - dimension: account_id
  - dimension: first_os_version
  - dimension: first_major_os_version
    sql: SUBSTRING_INDEX(${first_os_version},'.',1)
  - dimension: first_manufacturer
  - dimension: first_os
  - dimension: first_app_release
  - dimension: days_since_activation
    type: int
    sql: DATEDIFF(CURRENT_DATE,${consent_date})
  - dimension: days_since_activation_tier
    type: tier
    sql: ${days_since_activation}
  - dimension: history
    sql: ${visitor_id}
  - dimension_group: first_open
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
  - dimension_group: signup_complete_tap
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
  - dimension_group: signup_started
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
  - dimension_group: signup_complete
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
  - dimension_group: consent
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
    sql: ${account_details.consent_received_time}
  - dimension_group: first_entered
    type: time
    timeframes:
    - yesno
    - time
    - date
    - week
    - month
  - measure: account.count
    type: count
  - measure: account.percent_of_total
    type: percent_of_total
    sql: ${account.count}
  - measure: account.opened_count
    type: count
    filters:
    - first_open: True
  - measure: account.entered_opened_percent
    type: number
    sql: 100.0 * ${account.opened_count}/${account.entered_count}
  - measure: account.signup_started_count
    type: count
    filters:
    - signup_started: True
  - measure: account.opened_started_percent
    type: number
    sql: 100.0 * ${account.signup_started_count}/${account.opened_count}
  - measure: account.signup_complete_count
    type: count
    filters:
    - signup_complete: True
  - measure: account.started_complete_tap_percent
    type: number
    sql: 100.0 * ${account.signup_complete_tap_count}/${account.signup_started_count}
  - measure: account.consent_count
    type: count
    filters:
    - consent: True
  - measure: account.complete_tap_completed_percent
    type: number
    sql: 100.0 * ${account.signup_complete_count}/${account.signup_complete_tap_count}
  - measure: account.signup_complete_tap_count
    type: count
    filters:
    - signup_complete_tap: True
  - measure: account.complete_consent_percent
    type: number
    sql: 100.0 * ${account.consent_count}/${account.signup_complete_count}
  - measure: account.entered_count
    type: count
    filters:
    - first_entered: True
  - measure: account.opened_app_to_consent_percent
    type: number
    sql: 100.0 * ${account.consent_count}/${account.entered_count}
  - measure: account.opened_app_to_created_percent
    type: number
    sql: 100.0 * ${account.signup_complete_count}/${account.entered_count}

account_event_facts.sql
account_event_facts.sql
account_event_facts.view.lkml
account_event_facts.view.lookml

Account Event Facts

Blocks

Assumptions

Contents