data.ml
dbt Content sharing

Funnel

Funnel model for content sharing.

inferred

Blocks

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

SELECT
  cp.time AS event_time
  , cp.id AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip play' AS event_type
  , cp.event_user_id AS event_user_id
  , cp.post_user_id AS post_user_id
FROM analytics_clip_play_events cp
UNION ALL
SELECT
  cr.time AS event_time
  , NULL AS clip_play_id
  , cr.id AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip replay' AS event_type
  , cr.event_user_id AS event_user_id
  , cr.post_user_id AS post_user_id
FROM analytics_clip_replay_events cr
UNION ALL
SELECT
  ev.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , ev.id AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'endcard view' AS event_type
  , ev.event_user_id AS event_user_id
  , ev.post_user_id AS post_user_id
FROM analytics_endcard_view_events ev
UNION ALL
SELECT
  ps.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , ps.id AS post_share_id
  , NULL AS post_create_id
  , 'post share' AS event_type
  , ps.event_user_id AS event_user_id
  , ps.post_user_id AS post_user_id
FROM analytics_post_share_events ps
UNION ALL
SELECT
  pc.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , pc.id AS post_create_id
  , 'post create' AS event_type
  , pc.event_user_id AS event_user_id
  , pc.post_user_id AS post_user_id
FROM analytics_post_create_events pc

# models/schema.yml
version: 2

models:
  - name: funnel
    columns:
      - name: event_user_id
      - name: post_user_id
      - name: clip_play_id
      - name: clip_replay_id
      - name: endcard_view_id
      - name: post_create_id
      - name: post_share_id
      - name: event_type

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.
funnel.sql · 59 lines
source-preserved validation: parses 
SELECT
  cp.time AS event_time
  , cp.id AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip play' AS event_type
  , cp.event_user_id AS event_user_id
  , cp.post_user_id AS post_user_id
FROM analytics_clip_play_events cp
UNION ALL
SELECT
  cr.time AS event_time
  , NULL AS clip_play_id
  , cr.id AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip replay' AS event_type
  , cr.event_user_id AS event_user_id
  , cr.post_user_id AS post_user_id
FROM analytics_clip_replay_events cr
UNION ALL
SELECT
  ev.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , ev.id AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'endcard view' AS event_type
  , ev.event_user_id AS event_user_id
  , ev.post_user_id AS post_user_id
FROM analytics_endcard_view_events ev
UNION ALL
SELECT
  ps.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , ps.id AS post_share_id
  , NULL AS post_create_id
  , 'post share' AS event_type
  , ps.event_user_id AS event_user_id
  , ps.post_user_id AS post_user_id
FROM analytics_post_share_events ps
UNION ALL
SELECT
  pc.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , pc.id AS post_create_id
  , 'post create' AS event_type
  , pc.event_user_id AS event_user_id
  , pc.post_user_id AS post_user_id
FROM analytics_post_create_events pc
funnel.view.lkml · 160 lines
inferred 
view: funnel {
  derived_table: {
    sql: SELECT
  cp.time AS event_time
  , cp.id AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip play' AS event_type
  , cp.event_user_id AS event_user_id
  , cp.post_user_id AS post_user_id
FROM analytics_clip_play_events cp
UNION ALL
SELECT
  cr.time AS event_time
  , NULL AS clip_play_id
  , cr.id AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip replay' AS event_type
  , cr.event_user_id AS event_user_id
  , cr.post_user_id AS post_user_id
FROM analytics_clip_replay_events cr
UNION ALL
SELECT
  ev.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , ev.id AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'endcard view' AS event_type
  , ev.event_user_id AS event_user_id
  , ev.post_user_id AS post_user_id
FROM analytics_endcard_view_events ev
UNION ALL
SELECT
  ps.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , ps.id AS post_share_id
  , NULL AS post_create_id
  , 'post share' AS event_type
  , ps.event_user_id AS event_user_id
  , ps.post_user_id AS post_user_id
FROM analytics_post_share_events ps
UNION ALL
SELECT
  pc.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , pc.id AS post_create_id
  , 'post create' AS event_type
  , pc.event_user_id AS event_user_id
  , pc.post_user_id AS post_user_id
FROM analytics_post_create_events pc ;;
  }

  dimension: event_user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.event_user_id ;;
  }

  dimension: post_user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.post_user_id ;;
  }

  dimension: clip_play_id {
    type: int
    hidden: yes
    sql: ${TABLE}.clip_play_id ;;
  }

  dimension: clip_replay_id {
    type: int
    hidden: yes
    sql: ${TABLE}.clip_replay_id ;;
  }

  dimension: endcard_view_id {
    type: int
    hidden: yes
    sql: ${TABLE}.endcard_view_id ;;
  }

  dimension: post_create_id {
    type: int
    hidden: yes
    sql: ${TABLE}.post_create_id ;;
  }

  dimension: post_share_id {
    type: int
    hidden: yes
    sql: ${TABLE}.post_share_id ;;
  }

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

  dimension_group: event {
    type: time
    timeframes: [time, date, week, month, hour_of_day, day_of_week]
    sql: FROM_UNIXTIME(${TABLE}.event_time) ;;
  }

  measure: count {
    type: count
    drill_fields: [event_time, event_user_id, event_type]
  }

  measure: count_clip_plays {
    type: count
    filters: [event_type: "clip play"]
    drill_fields: [analytics_clip_play_events.id, event_user.id, event_user.name, post_user.id, post_user.name, analytics_clip_play_events.time_time]
  }

  measure: count_clip_replays {
    type: count
    filters: [event_type: "clip replay"]
    drill_fields: [analytics_clip_replay_events.id, event_user.id, event_user.name, post_user.id, post_user.name, analytics_clip_replay_events.time_time]
  }

  measure: count_endcard_views {
    type: count
    filters: [event_type: "endcard view"]
    drill_fields: [analytics_clip_play_events.id, event_user.id, event_user.name, post_user.id, post_user.name, analytics_clip_play_events.time_time]
  }

  measure: count_post_shares {
    type: count
    filters: [event_type: "post share"]
    drill_fields: [analytics_clip_replay_events.id, event_user.id, event_user.name, post_user.id, post_user.name, analytics_clip_replay_events.time_time]
  }

  measure: count_post_creates {
    type: count
    filters: [event_type: "post create"]
    drill_fields: [analytics_clip_replay_events.id, event_user.id, event_user.name, post_user.id, post_user.name, analytics_clip_replay_events.time_time]
  }

  measure: endcard_views_over_clip_plays {
    type: number
    sql: 1.0 * ${count_endcard_views} / ${count_clip_plays} ;;
  }

  measure: clip_replays_over_clip_plays {
    type: number
    sql: 1.0 * ${count_clip_replays} / ${count_clip_plays} ;;
  }
}
funnel.view.lookml · 169 lines
source-preserved 
- view: funnel
  derived_table:
    sql: SELECT
  cp.time AS event_time
  , cp.id AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip play' AS event_type
  , cp.event_user_id AS event_user_id
  , cp.post_user_id AS post_user_id
FROM analytics_clip_play_events cp
UNION ALL
SELECT
  cr.time AS event_time
  , NULL AS clip_play_id
  , cr.id AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'clip replay' AS event_type
  , cr.event_user_id AS event_user_id
  , cr.post_user_id AS post_user_id
FROM analytics_clip_replay_events cr
UNION ALL
SELECT
  ev.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , ev.id AS endcard_view_id
  , NULL AS post_share_id
  , NULL AS post_create_id
  , 'endcard view' AS event_type
  , ev.event_user_id AS event_user_id
  , ev.post_user_id AS post_user_id
FROM analytics_endcard_view_events ev
UNION ALL
SELECT
  ps.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , ps.id AS post_share_id
  , NULL AS post_create_id
  , 'post share' AS event_type
  , ps.event_user_id AS event_user_id
  , ps.post_user_id AS post_user_id
FROM analytics_post_share_events ps
UNION ALL
SELECT
  pc.time AS event_time
  , NULL AS clip_play_id
  , NULL AS clip_replay_id
  , NULL AS endcard_view_id
  , NULL AS post_share_id
  , pc.id AS post_create_id
  , 'post create' AS event_type
  , pc.event_user_id AS event_user_id
  , pc.post_user_id AS post_user_id
FROM analytics_post_create_events pc
  fields:
  - dimension: event_user_id
    type: int
    hidden: true
    sql: ${TABLE}.event_user_id
  - dimension: post_user_id
    type: int
    hidden: true
    sql: ${TABLE}.post_user_id
  - dimension: clip_play_id
    type: int
    hidden: true
    sql: ${TABLE}.clip_play_id
  - dimension: clip_replay_id
    type: int
    hidden: true
    sql: ${TABLE}.clip_replay_id
  - dimension: endcard_view_id
    type: int
    hidden: true
    sql: ${TABLE}.endcard_view_id
  - dimension: post_create_id
    type: int
    hidden: true
    sql: ${TABLE}.post_create_id
  - dimension: post_share_id
    type: int
    hidden: true
    sql: ${TABLE}.post_share_id
  - dimension: event_type
    sql: ${TABLE}.event_type
  - dimension_group: event
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - hour_of_day
    - day_of_week
    sql: FROM_UNIXTIME(${TABLE}.event_time)
  - measure: count
    type: count
    drill_fields:
    - event_time
    - event_user_id
    - event_type
  - measure: count_clip_plays
    type: count
    drill_fields:
    - analytics_clip_play_events.id
    - event_user.id
    - event_user.name
    - post_user.id
    - post_user.name
    - analytics_clip_play_events.time_time
    filters:
    - event_type: clip play
  - measure: count_clip_replays
    type: count
    drill_fields:
    - analytics_clip_replay_events.id
    - event_user.id
    - event_user.name
    - post_user.id
    - post_user.name
    - analytics_clip_replay_events.time_time
    filters:
    - event_type: clip replay
  - measure: count_endcard_views
    type: count
    drill_fields:
    - analytics_clip_play_events.id
    - event_user.id
    - event_user.name
    - post_user.id
    - post_user.name
    - analytics_clip_play_events.time_time
    filters:
    - event_type: endcard view
  - measure: count_post_shares
    type: count
    drill_fields:
    - analytics_clip_replay_events.id
    - event_user.id
    - event_user.name
    - post_user.id
    - post_user.name
    - analytics_clip_replay_events.time_time
    filters:
    - event_type: post share
  - measure: count_post_creates
    type: count
    drill_fields:
    - analytics_clip_replay_events.id
    - event_user.id
    - event_user.name
    - post_user.id
    - post_user.name
    - analytics_clip_replay_events.time_time
    filters:
    - event_type: post create
  - measure: endcard_views_over_clip_plays
    type: number
    sql: 1.0 * ${count_endcard_views} / ${count_clip_plays}
  - measure: clip_replays_over_clip_plays
    type: number
    sql: 1.0 * ${count_clip_replays} / ${count_clip_plays}

Contents

  • funnel.sql
  • funnel.sql
  • funnel.view.lkml
  • funnel.view.lookml