dbt App event sharing

Event Action Facts

Event Action Facts model for app event sharing.

inferred

Blocks

dbt SQL LKML LookML

event_action_facts.sql · 42 lines

inferred

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

SELECT
  event_id
  , COUNT(*) as actions
  , MIN(NULLIF(actions.created_at,0)) AS first_event
  , MAX(NULLIF(actions.created_at,0)) AS last_event
  , DATEDIFF(CURDATE(),MAX(NULLIF(actions.created_at,0))) as days_since_last_event
  , COUNT(CASE WHEN actions.action_type = 'post_like' THEN 1 ELSE NULL END) AS post_likes
  , COUNT(CASE WHEN actions.action_type = 'join' THEN 1 ELSE NULL END) AS joins
  , COUNT(CASE WHEN actions.action_type = 'post_comment' THEN 1 ELSE NULL END) AS post_comments
  , COUNT(CASE WHEN actions.action_type = 'user_tagged' THEN 1 ELSE NULL END) AS user_tags
  , COUNT(DISTINCT user_id) AS users_with_actions
  , COUNT(DISTINCT recipient_user_id) AS recipients_invited
  , COUNT(DISTINCT DAY(NULLIF(actions.created_at,0))) as number_of_distinct_days_with_actions
FROM actions
GROUP BY event_id

# models/schema.yml
version: 2

models:
  - name: event_action_facts
    columns:
      - name: event_id
        tests:
          - unique
          - not_null
      - name: actions
      - name: actions_tiered
      - name: has_action
      - name: days_since_last_event
      - name: days_since_last_event_tier
      - name: post_likes
      - name: joins
      - name: post_comments
      - name: user_tags
      - name: users_with_actions
      - name: number_of_distinct_days_with_actions
      - name: number_of_distinct_days_with_actions_tier
      - name: recipients_invited

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_action_facts.sql · 15 lines

source-preserved validation: parses

SELECT
  event_id
  , COUNT(*) as actions
  , MIN(NULLIF(actions.created_at,0)) AS first_event
  , MAX(NULLIF(actions.created_at,0)) AS last_event
  , DATEDIFF(CURDATE(),MAX(NULLIF(actions.created_at,0))) as days_since_last_event
  , COUNT(CASE WHEN actions.action_type = 'post_like' THEN 1 ELSE NULL END) AS post_likes
  , COUNT(CASE WHEN actions.action_type = 'join' THEN 1 ELSE NULL END) AS joins
  , COUNT(CASE WHEN actions.action_type = 'post_comment' THEN 1 ELSE NULL END) AS post_comments
  , COUNT(CASE WHEN actions.action_type = 'user_tagged' THEN 1 ELSE NULL END) AS user_tags
  , COUNT(DISTINCT user_id) AS users_with_actions
  , COUNT(DISTINCT recipient_user_id) AS recipients_invited
  , COUNT(DISTINCT DAY(NULLIF(actions.created_at,0))) as number_of_distinct_days_with_actions
FROM actions
GROUP BY event_id

event_action_facts.view.lkml · 104 lines

inferred

view: event_action_facts {
  derived_table: {
    sql: SELECT
  event_id
  , COUNT(*) as actions
  , MIN(NULLIF(actions.created_at,0)) AS first_event
  , MAX(NULLIF(actions.created_at,0)) AS last_event
  , DATEDIFF(CURDATE(),MAX(NULLIF(actions.created_at,0))) as days_since_last_event
  , COUNT(CASE WHEN actions.action_type = 'post_like' THEN 1 ELSE NULL END) AS post_likes
  , COUNT(CASE WHEN actions.action_type = 'join' THEN 1 ELSE NULL END) AS joins
  , COUNT(CASE WHEN actions.action_type = 'post_comment' THEN 1 ELSE NULL END) AS post_comments
  , COUNT(CASE WHEN actions.action_type = 'user_tagged' THEN 1 ELSE NULL END) AS user_tags
  , COUNT(DISTINCT user_id) AS users_with_actions
  , COUNT(DISTINCT recipient_user_id) AS recipients_invited
  , COUNT(DISTINCT DAY(NULLIF(actions.created_at,0))) as number_of_distinct_days_with_actions
FROM actions
GROUP BY event_id ;;
  }

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

  dimension: actions {
    type: number
    sql: ${TABLE}.actions ;;
  }

  dimension: actions_tiered {
    type: tier
    sql: ${actions} ;;
  }

  dimension: has_action {
    type: yesno
    sql: ${actions} > 0 ;;
  }

  dimension: days_since_last_event {
    type: number
    sql: ${TABLE}.days_since_last_event ;;
  }

  dimension: days_since_last_event_tier {
    type: tier
  }

  dimension: post_likes {
    type: number
    sql: ${TABLE}.post_likes ;;
  }

  dimension: joins {
    type: number
    sql: ${TABLE}.joins ;;
  }

  dimension: post_comments {
    type: number
    sql: ${TABLE}.post_comments ;;
  }

  dimension: user_tags {
    type: number
    sql: ${TABLE}.user_tags ;;
  }

  dimension: users_with_actions {
    type: number
    sql: ${TABLE}.users_with_actions ;;
  }

  dimension: number_of_distinct_days_with_actions {
    type: number
    sql: ${TABLE}.number_of_distinct_days_with_actions ;;
  }

  dimension: number_of_distinct_days_with_actions_tier {
    type: tier
    sql: ${number_of_distinct_days_with_actions} ;;
  }

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

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

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

  measure: total_actions {
    type: sum
    sql: ${actions} ;;
  }
}

event_action_facts.view.lookml · 79 lines

source-preserved

- view: event_action_facts
  derived_table:
    sql: SELECT
  event_id
  , COUNT(*) as actions
  , MIN(NULLIF(actions.created_at,0)) AS first_event
  , MAX(NULLIF(actions.created_at,0)) AS last_event
  , DATEDIFF(CURDATE(),MAX(NULLIF(actions.created_at,0))) as days_since_last_event
  , COUNT(CASE WHEN actions.action_type = 'post_like' THEN 1 ELSE NULL END) AS post_likes
  , COUNT(CASE WHEN actions.action_type = 'join' THEN 1 ELSE NULL END) AS joins
  , COUNT(CASE WHEN actions.action_type = 'post_comment' THEN 1 ELSE NULL END) AS post_comments
  , COUNT(CASE WHEN actions.action_type = 'user_tagged' THEN 1 ELSE NULL END) AS user_tags
  , COUNT(DISTINCT user_id) AS users_with_actions
  , COUNT(DISTINCT recipient_user_id) AS recipients_invited
  , COUNT(DISTINCT DAY(NULLIF(actions.created_at,0))) as number_of_distinct_days_with_actions
FROM actions
GROUP BY event_id
  fields:
  - dimension: event_id
    primary_key: true
    sql: ${TABLE}.event_id
  - dimension: actions
    type: number
    sql: ${TABLE}.actions
  - dimension: actions_tiered
    type: tier
    sql: ${actions}
  - dimension: has_action
    type: yesno
    sql: ${actions} > 0
  - dimension: days_since_last_event
    type: number
    sql: ${TABLE}.days_since_last_event
  - dimension: days_since_last_event_tier
    type: tier
  - dimension: post_likes
    type: number
    sql: ${TABLE}.post_likes
  - dimension: joins
    type: number
    sql: ${TABLE}.joins
  - dimension: post_comments
    type: number
    sql: ${TABLE}.post_comments
  - dimension: user_tags
    type: number
    sql: ${TABLE}.user_tags
  - dimension: users_with_actions
    type: number
    sql: ${TABLE}.users_with_actions
  - dimension: number_of_distinct_days_with_actions
    type: number
    sql: ${TABLE}.number_of_distinct_days_with_actions
  - dimension: number_of_distinct_days_with_actions_tier
    type: tier
    sql: ${number_of_distinct_days_with_actions}
  - dimension: recipients_invited
    sql: ${TABLE}.recipients_invited
  - dimension_group: first_event
    type: time
    timeframes:
    - date
    - week
    - month
    - dow
    - year
    sql: ${TABLE}.first_event
  - dimension_group: last_event
    type: time
    timeframes:
    - date
    - week
    - month
    - dow
    - year
    sql: ${TABLE}.last_event
  - measure: total_actions
    type: sum
    sql: ${actions}

event_action_facts.sql
event_action_facts.sql
event_action_facts.view.lkml
event_action_facts.view.lookml

Event Action Facts

Blocks

Assumptions

Contents