dbt Saas crm life sciences

Engagement Facts

Engagement Facts model for saas crm life sciences.

inferred

Blocks

dbt SQL LKML LookML

engagement_facts.sql · 80 lines

inferred

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

SELECT *
  , d1.created_date AS action_day
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d2
    WHERE d1.email = d2.email
      AND d1.created_date >= DATE_ADD(d2.created_date, interval -1 day)
      AND d1.created_date < d2.created_date
    ) AS active_next_day
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d3
    WHERE d1.email = d3.email
      AND d1.created_date >= DATE_ADD(d3.created_date, interval -3 day)
      AND d1.created_date < d3.created_date
    ) AS days_active_of_following_3
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d4
    WHERE d1.email = d4.email
      AND d1.created_date >= DATE_ADD(d4.created_date, interval -7 day)
      AND d1.created_date < d4.created_date
    ) AS days_active_of_following_7
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d5
    WHERE d1.email = d5.email
      AND d1.created_date >= DATE_ADD(d5.created_date, interval -14 day)
      AND d1.created_date < d5.created_date
    ) AS days_active_of_following_14
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d6
    WHERE d1.email = d6.email
      AND d1.created_date >= DATE_ADD(d6.created_date, interval -30 day)
      AND d1.created_date < d6.created_date
    ) AS days_active_of_following_30
  , (
    SELECT COUNT(*)
    FROM {{ ref('daily_interaction') }} AS d7
    WHERE d1.email = d7.email
      AND d1.created_date < DATE_ADD(d7.created_date, interval 30 day)
      AND d1.created_date >= d7.created_date
    ) AS days_active_of_prev_30
  FROM {{ ref('daily_interaction') }} AS d1
  GROUP BY email, action_day

# models/schema.yml
version: 2

models:
  - name: engagement_facts
    columns:
      - name: email
      - name: action
      - name: days_since_user_signup
      - name: phone_action_total
      - name: user_called
      - name: number_of_events_total
      - name: record_save_total
      - name: email_action_total
      - name: user_emailed
      - name: text_action_total
      - name: user_communication_total
      - name: user_interacted
      - name: active_next_day
      - name: days_active_of_following_3
      - name: active_within_3_days
      - name: days_active_of_following_7
      - name: active_within_7_days
      - name: days_active_of_following_14
      - name: active_within_14_days
      - name: days_active_of_following_30
      - name: active_within_30_days
      - name: days_active_of_prev_30
      - name: days_active_of_prev_30_tier
      - name: active_in_prev_30_days

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.

engagement_facts.sql · 46 lines

source-preserved validation: not checked

SELECT *
  , d1.created_date AS action_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d2
    WHERE d1.email = d2.email
      AND d1.created_date >= DATE_ADD(d2.created_date, interval -1 day)
      AND d1.created_date < d2.created_date
    ) AS active_next_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d3
    WHERE d1.email = d3.email
      AND d1.created_date >= DATE_ADD(d3.created_date, interval -3 day)
      AND d1.created_date < d3.created_date
    ) AS days_active_of_following_3
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d4
    WHERE d1.email = d4.email
      AND d1.created_date >= DATE_ADD(d4.created_date, interval -7 day)
      AND d1.created_date < d4.created_date
    ) AS days_active_of_following_7
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d5
    WHERE d1.email = d5.email
      AND d1.created_date >= DATE_ADD(d5.created_date, interval -14 day)
      AND d1.created_date < d5.created_date
    ) AS days_active_of_following_14
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d6
    WHERE d1.email = d6.email
      AND d1.created_date >= DATE_ADD(d6.created_date, interval -30 day)
      AND d1.created_date < d6.created_date
    ) AS days_active_of_following_30
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d7
    WHERE d1.email = d7.email
      AND d1.created_date < DATE_ADD(d7.created_date, interval 30 day)
      AND d1.created_date >= d7.created_date
    ) AS days_active_of_prev_30
  FROM ${daily_interaction.SQL_TABLE_NAME} AS d1
  GROUP BY email, action_day

engagement_facts.view.lkml · 436 lines

inferred

view: engagement_facts {
  derived_table: {
    sql: SELECT *
  , d1.created_date AS action_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d2
    WHERE d1.email = d2.email
      AND d1.created_date >= DATE_ADD(d2.created_date, interval -1 day)
      AND d1.created_date < d2.created_date
    ) AS active_next_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d3
    WHERE d1.email = d3.email
      AND d1.created_date >= DATE_ADD(d3.created_date, interval -3 day)
      AND d1.created_date < d3.created_date
    ) AS days_active_of_following_3
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d4
    WHERE d1.email = d4.email
      AND d1.created_date >= DATE_ADD(d4.created_date, interval -7 day)
      AND d1.created_date < d4.created_date
    ) AS days_active_of_following_7
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d5
    WHERE d1.email = d5.email
      AND d1.created_date >= DATE_ADD(d5.created_date, interval -14 day)
      AND d1.created_date < d5.created_date
    ) AS days_active_of_following_14
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d6
    WHERE d1.email = d6.email
      AND d1.created_date >= DATE_ADD(d6.created_date, interval -30 day)
      AND d1.created_date < d6.created_date
    ) AS days_active_of_following_30
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d7
    WHERE d1.email = d7.email
      AND d1.created_date < DATE_ADD(d7.created_date, interval 30 day)
      AND d1.created_date >= d7.created_date
    ) AS days_active_of_prev_30
  FROM ${daily_interaction.SQL_TABLE_NAME} AS d1
  GROUP BY email, action_day ;;
  }

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

  dimension: action {
    type: time
    sql: ${TABLE}.action_day ;;
  }

  dimension: days_since_user_signup {
    type: number
    hidden: yes
    sql: ${users.days_since_signup} ;;
  }

  dimension: phone_action_total {
    type: number
    hidden: yes
    sql: ${TABLE}.phone_action ;;
  }

  dimension: user_called {
    type: yesno
    sql: ${phone_action_total} > 0 ;;
  }

  dimension: number_of_events_total {
    type: number
    hidden: yes
    sql: ${TABLE}.number_of_events ;;
  }

  dimension: record_save_total {
    type: number
    hidden: yes
    sql: ${TABLE}.record_save ;;
  }

  dimension: email_action_total {
    type: number
    hidden: yes
    sql: ${TABLE}.email_action ;;
  }

  dimension: user_emailed {
    type: yesno
    sql: ${email_action_total} > 0 ;;
  }

  dimension: text_action_total {
    type: number
    hidden: yes
    sql: ${TABLE}.text_action ;;
  }

  dimension: user_communication_total {
    type: number
    hidden: yes
    sql: ${phone_action_total} + ${email_action_total} + ${text_action_total} ;;
  }

  dimension: user_interacted {
    type: yesno
    sql: ${user_communication_total} > 0 ;;
  }

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

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

  dimension: active_within_3_days {
    type: yesno
    sql: ${TABLE}.days_active_of_following_3 > 0 ;;
  }

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

  dimension: active_within_7_days {
    type: yesno
    sql: ${TABLE}.days_active_of_following_7 > 0 ;;
  }

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

  dimension: active_within_14_days {
    type: yesno
    sql: ${TABLE}.days_active_of_following_14 > 0 ;;
  }

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

  dimension: active_within_30_days {
    type: yesno
    sql: ${TABLE}.days_active_of_following_30 > 0 ;;
  }

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

  dimension: days_active_of_prev_30_tier {
    type: tier
    sql: ${days_active_of_prev_30} ;;
  }

  dimension: active_in_prev_30_days {
    type: yesno
    sql: ${days_active_of_prev_30} > 0 ;;
  }

  measure: user_count {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: phone_action_sum {
    type: sum
    sql: ${TABLE}.phone_action ;;
  }

  measure: users_with_call_count {
    type: count_distinct
    filters: [user_called: "yes"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: number_of_events_sum {
    type: sum
    sql: ${number_of_events_total} ;;
  }

  measure: record_save_sum {
    type: sum
    sql: ${TABLE}.record_save ;;
  }

  measure: email_action_sum {
    type: sum
    sql: ${email_action_total} ;;
  }

  measure: user_emailed_count {
    type: count_distinct
    filters: [user_emailed: "yes"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: text_action_sum {
    type: sum
    sql: ${text_action_total} ;;
  }

  measure: user_communication_sum {
    type: sum
    sql: ${user_communication_total} ;;
  }

  measure: user_interacted_count {
    type: count_distinct
    filters: [user_interacted: "yes"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_next_day_count {
    type: count_distinct
    hidden: yes
    filters: [active_next_day: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_call_active_next_day {
    type: count_distinct
    hidden: yes
    filters: [phone_action_total: "1 to"]
    filters: [active_next_day: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_interacted_active_next_day {
    type: count_distinct
    hidden: yes
    filters: [user_interacted: "True"]
    filters: [active_next_day: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_next_day_percent {
    type: number
    sql: 100.0 * ${users_active_next_day_count}/${user_count} ;;
  }

  measure: users_with_call_active_next_day_percent {
    type: number
    sql: 100.0 * ${users_with_call_active_next_day}/${users_with_call_count} ;;
  }

  measure: users_who_interacted_active_next_day_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_active_next_day}/${user_interacted_count} ;;
  }

  measure: users_active_within_3_days_count {
    type: count_distinct
    hidden: yes
    filters: [active_within_3_days: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_within_3_days_percent {
    type: number
    sql: 100.0 * ${users_active_within_3_days_count}/${user_count} ;;
  }

  measure: users_with_call_active_within_3_days {
    type: count_distinct
    filters: [phone_action_total: "1 to"]
    filters: [active_within_3_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_call_active_within_3_days_percent {
    type: number
    sql: 100.0 * ${users_with_call_active_within_3_days}/${users_with_call_count} ;;
  }

  measure: users_who_interacted_active_within_3_days {
    type: count_distinct
    filters: [user_interacted: "True"]
    filters: [active_within_3_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_interacted_active_within_3_days_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_3_days}/${user_interacted_count} ;;
  }

  measure: users_active_within_7_days_count {
    type: count_distinct
    hidden: yes
    filters: [active_within_7_days: "True"]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_within_7_days_percent {
    type: number
    sql: 100.0 * ${users_active_within_7_days_count}/${user_count} ;;
  }

  measure: users_with_call_active_within_7_days {
    type: count_distinct
    filters: [phone_action_total: "1 to"]
    filters: [active_within_7_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_call_active_within_7_days_percent {
    type: number
    sql: 100.0 * ${users_with_call_active_within_7_days}/${users_with_call_count} ;;
  }

  measure: users_who_interacted_active_within_7_days {
    type: count_distinct
    filters: [user_interacted: "True"]
    filters: [active_within_7_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_interacted_active_within_7_days_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_7_days}/${user_interacted_count} ;;
  }

  measure: users_with_call_active_within_14_days {
    type: count_distinct
    filters: [phone_action_total: "1 to"]
    filters: [active_within_14_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_comm_active_within_14_days {
  }

  measure: active_within_30_days_count {
    type: count_distinct
    filters: [active_within_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_within_30_days_percent {
    type: number
    sql: 100.0 * ${active_within_30_days_count}/${user_count} ;;
  }

  measure: users_with_call_active_within_30_days {
    type: count_distinct
    filters: [phone_action_total: "1 to"]
    filters: [active_within_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_call_active_within_30_days_percent {
    type: number
    sql: 100.0 * ${users_with_call_active_within_30_days}/${users_with_call_count} ;;
  }

  measure: users_who_interacted_active_within_30_days {
    type: count_distinct
    filters: [user_interacted: "True"]
    filters: [active_within_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_interacted_active_within_30_days_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_30_days}/${user_interacted_count} ;;
  }

  measure: active_in_prev_30_days_count {
    type: count_distinct
    filters: [active_in_prev_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_active_in_prev_30_days_percent {
    type: number
    sql: 100.0 * ${active_in_prev_30_days_count}/${user_count} ;;
  }

  measure: users_with_call_active_in_prev_30_days {
    type: count_distinct
    filters: [phone_action_total: "1 to"]
    filters: [active_in_prev_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_with_call_active_in_prev_30_days_percent {
    type: number
    sql: 100.0 * ${users_with_call_active_in_prev_30_days}/${users_with_call_count} ;;
  }

  measure: users_who_interacted_active_in_prev_30_days {
    type: count_distinct
    filters: [user_interacted: "True"]
    filters: [active_in_prev_30_days: "True"]
    drill_fields: [detail*]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_interacted_active_in_prev_30_days_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_active_in_prev_30_days}/${user_interacted_count} ;;
  }
}

engagement_facts.view.lookml · 334 lines

source-preserved

- view: engagement_facts
  derived_table:
    sql: SELECT *
  , d1.created_date AS action_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d2
    WHERE d1.email = d2.email
      AND d1.created_date >= DATE_ADD(d2.created_date, interval -1 day)
      AND d1.created_date < d2.created_date
    ) AS active_next_day
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d3
    WHERE d1.email = d3.email
      AND d1.created_date >= DATE_ADD(d3.created_date, interval -3 day)
      AND d1.created_date < d3.created_date
    ) AS days_active_of_following_3
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d4
    WHERE d1.email = d4.email
      AND d1.created_date >= DATE_ADD(d4.created_date, interval -7 day)
      AND d1.created_date < d4.created_date
    ) AS days_active_of_following_7
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d5
    WHERE d1.email = d5.email
      AND d1.created_date >= DATE_ADD(d5.created_date, interval -14 day)
      AND d1.created_date < d5.created_date
    ) AS days_active_of_following_14
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d6
    WHERE d1.email = d6.email
      AND d1.created_date >= DATE_ADD(d6.created_date, interval -30 day)
      AND d1.created_date < d6.created_date
    ) AS days_active_of_following_30
  , (
    SELECT COUNT(*)
    FROM ${daily_interaction.SQL_TABLE_NAME} AS d7
    WHERE d1.email = d7.email
      AND d1.created_date < DATE_ADD(d7.created_date, interval 30 day)
      AND d1.created_date >= d7.created_date
    ) AS days_active_of_prev_30
  FROM ${daily_interaction.SQL_TABLE_NAME} AS d1
  GROUP BY email, action_day
  fields:
  - dimension: email
    sql: ${TABLE}.email
  - dimension: action
    type: time
    sql: ${TABLE}.action_day
  - dimension: days_since_user_signup
    type: number
    hidden: true
    sql: ${users.days_since_signup}
  - dimension: phone_action_total
    type: number
    hidden: true
    sql: ${TABLE}.phone_action
  - dimension: user_called
    type: yesno
    sql: ${phone_action_total} > 0
  - dimension: number_of_events_total
    type: number
    hidden: true
    sql: ${TABLE}.number_of_events
  - dimension: record_save_total
    type: number
    hidden: true
    sql: ${TABLE}.record_save
  - dimension: email_action_total
    type: number
    hidden: true
    sql: ${TABLE}.email_action
  - dimension: user_emailed
    type: yesno
    sql: ${email_action_total} > 0
  - dimension: text_action_total
    type: number
    hidden: true
    sql: ${TABLE}.text_action
  - dimension: user_communication_total
    type: number
    hidden: true
    sql: ${phone_action_total} + ${email_action_total} + ${text_action_total}
  - dimension: user_interacted
    type: yesno
    sql: ${user_communication_total} > 0
  - dimension: active_next_day
    type: yesno
    sql: ${TABLE}.active_next_day > 0
  - dimension: days_active_of_following_3
    type: number
    sql: ${TABLE}.days_active_of_following_3
  - dimension: active_within_3_days
    type: yesno
    sql: ${TABLE}.days_active_of_following_3 > 0
  - dimension: days_active_of_following_7
    type: number
    sql: ${TABLE}.days_active_of_following_7
  - dimension: active_within_7_days
    type: yesno
    sql: ${TABLE}.days_active_of_following_7 > 0
  - dimension: days_active_of_following_14
    type: number
    sql: ${TABLE}.days_active_of_following_14
  - dimension: active_within_14_days
    type: yesno
    sql: ${TABLE}.days_active_of_following_14 > 0
  - dimension: days_active_of_following_30
    type: number
    sql: ${TABLE}.days_active_of_following_30
  - dimension: active_within_30_days
    type: yesno
    sql: ${TABLE}.days_active_of_following_30 > 0
  - dimension: days_active_of_prev_30
    type: number
    sql: ${TABLE}.days_active_of_prev_30
  - dimension: days_active_of_prev_30_tier
    type: tier
    sql: ${days_active_of_prev_30}
  - dimension: active_in_prev_30_days
    type: yesno
    sql: ${days_active_of_prev_30} > 0
  - measure: user_count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${TABLE}.email
  - measure: phone_action_sum
    type: sum
    sql: ${TABLE}.phone_action
  - measure: users_with_call_count
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_called: yes
    sql: ${TABLE}.email
  - measure: number_of_events_sum
    type: sum
    sql: ${number_of_events_total}
  - measure: record_save_sum
    type: sum
    sql: ${TABLE}.record_save
  - measure: email_action_sum
    type: sum
    sql: ${email_action_total}
  - measure: user_emailed_count
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_emailed: yes
    sql: ${TABLE}.email
  - measure: text_action_sum
    type: sum
    sql: ${text_action_total}
  - measure: user_communication_sum
    type: sum
    sql: ${user_communication_total}
  - measure: user_interacted_count
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: yes
    sql: ${TABLE}.email
  - measure: users_active_next_day_count
    type: count_distinct
    filters:
    - active_next_day: True
    sql: ${TABLE}.email
  - measure: users_with_call_active_next_day
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_next_day: True
    sql: ${TABLE}.email
  - measure: users_who_interacted_active_next_day
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: True
    - active_next_day: True
    sql: ${TABLE}.email
  - measure: users_active_next_day_percent
    type: number
    sql: 100.0 * ${users_active_next_day_count}/${user_count}
  - measure: users_with_call_active_next_day_percent
    type: number
    sql: 100.0 * ${users_with_call_active_next_day}/${users_with_call_count}
  - measure: users_who_interacted_active_next_day_percent
    type: number
    sql: 100.0 * ${users_who_interacted_active_next_day}/${user_interacted_count}
  - measure: users_active_within_3_days_count
    type: count_distinct
    filters:
    - active_within_3_days: True
    sql: ${TABLE}.email
  - measure: users_active_within_3_days_percent
    type: number
    sql: 100.0 * ${users_active_within_3_days_count}/${user_count}
  - measure: users_with_call_active_within_3_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_within_3_days: True
    sql: ${TABLE}.email
  - measure: users_with_call_active_within_3_days_percent
    type: number
    sql: 100.0 * ${users_with_call_active_within_3_days}/${users_with_call_count}
  - measure: users_who_interacted_active_within_3_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: True
    - active_within_3_days: True
    sql: ${TABLE}.email
  - measure: users_who_interacted_active_within_3_days_percent
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_3_days}/${user_interacted_count}
  - measure: users_active_within_7_days_count
    type: count_distinct
    filters:
    - active_within_7_days: True
    sql: ${TABLE}.email
  - measure: users_active_within_7_days_percent
    type: number
    sql: 100.0 * ${users_active_within_7_days_count}/${user_count}
  - measure: users_with_call_active_within_7_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_within_7_days: True
    sql: ${TABLE}.email
  - measure: users_with_call_active_within_7_days_percent
    type: number
    sql: 100.0 * ${users_with_call_active_within_7_days}/${users_with_call_count}
  - measure: users_who_interacted_active_within_7_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: True
    - active_within_7_days: True
    sql: ${TABLE}.email
  - measure: users_who_interacted_active_within_7_days_percent
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_7_days}/${user_interacted_count}
  - measure: users_with_call_active_within_14_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_within_14_days: True
    sql: ${TABLE}.email
  - measure: users_with_comm_active_within_14_days
  - measure: active_within_30_days_count
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - active_within_30_days: True
    sql: ${TABLE}.email
  - measure: users_active_within_30_days_percent
    type: number
    sql: 100.0 * ${active_within_30_days_count}/${user_count}
  - measure: users_with_call_active_within_30_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_within_30_days: True
    sql: ${TABLE}.email
  - measure: users_with_call_active_within_30_days_percent
    type: number
    sql: 100.0 * ${users_with_call_active_within_30_days}/${users_with_call_count}
  - measure: users_who_interacted_active_within_30_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: True
    - active_within_30_days: True
    sql: ${TABLE}.email
  - measure: users_who_interacted_active_within_30_days_percent
    type: number
    sql: 100.0 * ${users_who_interacted_active_within_30_days}/${user_interacted_count}
  - measure: active_in_prev_30_days_count
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - active_in_prev_30_days: True
    sql: ${TABLE}.email
  - measure: users_active_in_prev_30_days_percent
    type: number
    sql: 100.0 * ${active_in_prev_30_days_count}/${user_count}
  - measure: users_with_call_active_in_prev_30_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - phone_action_total: 1 to
    - active_in_prev_30_days: True
    sql: ${TABLE}.email
  - measure: users_with_call_active_in_prev_30_days_percent
    type: number
    sql: 100.0 * ${users_with_call_active_in_prev_30_days}/${users_with_call_count}
  - measure: users_who_interacted_active_in_prev_30_days
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - user_interacted: True
    - active_in_prev_30_days: True
    sql: ${TABLE}.email
  - measure: users_who_interacted_active_in_prev_30_days_percent
    type: number
    sql: 100.0 * ${users_who_interacted_active_in_prev_30_days}/${user_interacted_count}

engagement_facts.sql
engagement_facts.sql
engagement_facts.view.lkml
engagement_facts.view.lookml

Engagement Facts

Blocks

Assumptions

Contents