SQL Saas crm life sciences

Daily Usage

Daily Usage model for saas crm life sciences.

source-preserved

Blocks

dbt SQL LKML LookML

daily_usage.sql · 60 lines

inferred

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

SELECT
DATE(occurred_at) AS created_date
, email
, COUNT(events.id) AS number_of_events
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:contactProfile Screen' THEN 1 ELSE NULL END) AS viewed_entity
, COUNT(CASE WHEN events.event = 'ContactPrompt:Save' THEN 1 ELSE NULL END) AS entity_save
, COUNT(CASE WHEN events.event = 'phoneAction' THEN 1 ELSE NULL END) AS phone_action
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:taskDetail:creating Screen' THEN 1 ELSE NULL END) AS create_assignment
, COUNT(CASE WHEN events.event = 'taskMarkedCompleted' THEN 1 ELSE NULL END) AS completed_assignment
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Save' THEN 1 ELSE NULL END) AS save_opportunity
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Discard' THEN 1 ELSE NULL END) AS discard_opportunity
, COUNT(CASE WHEN events.event = 'NotePrompt:Save' THEN 1 ELSE NULL END) AS save_remark
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Save' THEN 1 ELSE NULL END) AS record_save
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Discard' THEN 1 ELSE NULL END) AS record_discard
, COUNT(CASE WHEN events.event = 'sendViaEmail' THEN 1 ELSE NULL END) AS email_action
, COUNT(CASE WHEN events.event = 'SMSAction' THEN 1 ELSE NULL END) AS text_action
, COUNT(CASE WHEN events.event = 'activityChangeDay' THEN 1 ELSE NULL END) AS changed_calendar_days
, COUNT(CASE WHEN events.event = 'toggleFavorite' THEN 1 ELSE NULL END) AS toggle_star
, COUNT(CASE WHEN events.event = 'addressAction' THEN 1 ELSE NULL END) AS map_entity
, COUNT(CASE WHEN events.event = 'viewScreen:emails Screen' THEN 1 ELSE NULL END) AS viewed_email_screen
, COUNT(CASE WHEN events.event = 'viewScreen:contactList Screen' THEN 1 ELSE NULL END) AS viewed_entitys_screen
, COUNT(CASE WHEN events.event = 'viewScreen:activities Screen' THEN 1 ELSE NULL END) AS viewed_calendar_screen
, COUNT(CASE WHEN events.event = 'viewScreen:opportunities Screen' THEN 1 ELSE NULL END) AS viewed_oppty_screen
, COUNT(DISTINCT FLOOR(UNIX_TIMESTAMP(occurred_at)/(60*2))) * 2 AS approximate_usage_in_minutes
FROM events
WHERE email NOT LIKE '%onboarding%'
GROUP BY created_date, email
ORDER BY created_date desc, number_of_events desc

# models/schema.yml
version: 2

models:
  - name: daily_usage
    columns:
      - name: email
      - name: average_minutes_per_day_tier
      - name: viewed_email
      - name: viewed_entitys
      - name: viewed_calendar
      - name: viewed_oppty
      - name: called
      - name: phone_action
      - name: record_discard
      - name: record_save
      - name: completed_assignment
      - name: create_assignment
      - name: email_action
      - name: text_action
      - name: viewed_entity
      - name: entity_save
      - name: save_remark
      - name: interacted
      - name: save_opportunity
      - name: discard_opportunity
      - name: efficiency_action
      - name: crm_action

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.

daily_usage.sql · 28 lines

source-preserved validation: parses

SELECT
DATE(occurred_at) AS created_date
, email
, COUNT(events.id) AS number_of_events
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:contactProfile Screen' THEN 1 ELSE NULL END) AS viewed_entity
, COUNT(CASE WHEN events.event = 'ContactPrompt:Save' THEN 1 ELSE NULL END) AS entity_save
, COUNT(CASE WHEN events.event = 'phoneAction' THEN 1 ELSE NULL END) AS phone_action
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:taskDetail:creating Screen' THEN 1 ELSE NULL END) AS create_assignment
, COUNT(CASE WHEN events.event = 'taskMarkedCompleted' THEN 1 ELSE NULL END) AS completed_assignment
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Save' THEN 1 ELSE NULL END) AS save_opportunity
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Discard' THEN 1 ELSE NULL END) AS discard_opportunity
, COUNT(CASE WHEN events.event = 'NotePrompt:Save' THEN 1 ELSE NULL END) AS save_remark
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Save' THEN 1 ELSE NULL END) AS record_save
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Discard' THEN 1 ELSE NULL END) AS record_discard
, COUNT(CASE WHEN events.event = 'sendViaEmail' THEN 1 ELSE NULL END) AS email_action
, COUNT(CASE WHEN events.event = 'SMSAction' THEN 1 ELSE NULL END) AS text_action
, COUNT(CASE WHEN events.event = 'activityChangeDay' THEN 1 ELSE NULL END) AS changed_calendar_days
, COUNT(CASE WHEN events.event = 'toggleFavorite' THEN 1 ELSE NULL END) AS toggle_star
, COUNT(CASE WHEN events.event = 'addressAction' THEN 1 ELSE NULL END) AS map_entity
, COUNT(CASE WHEN events.event = 'viewScreen:emails Screen' THEN 1 ELSE NULL END) AS viewed_email_screen
, COUNT(CASE WHEN events.event = 'viewScreen:contactList Screen' THEN 1 ELSE NULL END) AS viewed_entitys_screen
, COUNT(CASE WHEN events.event = 'viewScreen:activities Screen' THEN 1 ELSE NULL END) AS viewed_calendar_screen
, COUNT(CASE WHEN events.event = 'viewScreen:opportunities Screen' THEN 1 ELSE NULL END) AS viewed_oppty_screen
, COUNT(DISTINCT FLOOR(UNIX_TIMESTAMP(occurred_at)/(60*2))) * 2 AS approximate_usage_in_minutes
FROM events
WHERE email NOT LIKE '%onboarding%'
GROUP BY created_date, email
ORDER BY created_date desc, number_of_events desc

daily_usage.view.lkml · 506 lines

inferred

view: daily_usage {
  derived_table: {
    sql: SELECT
DATE(occurred_at) AS created_date
, email
, COUNT(events.id) AS number_of_events
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:contactProfile Screen' THEN 1 ELSE NULL END) AS viewed_entity
, COUNT(CASE WHEN events.event = 'ContactPrompt:Save' THEN 1 ELSE NULL END) AS entity_save
, COUNT(CASE WHEN events.event = 'phoneAction' THEN 1 ELSE NULL END) AS phone_action
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:taskDetail:creating Screen' THEN 1 ELSE NULL END) AS create_assignment
, COUNT(CASE WHEN events.event = 'taskMarkedCompleted' THEN 1 ELSE NULL END) AS completed_assignment
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Save' THEN 1 ELSE NULL END) AS save_opportunity
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Discard' THEN 1 ELSE NULL END) AS discard_opportunity
, COUNT(CASE WHEN events.event = 'NotePrompt:Save' THEN 1 ELSE NULL END) AS save_remark
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Save' THEN 1 ELSE NULL END) AS record_save
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Discard' THEN 1 ELSE NULL END) AS record_discard
, COUNT(CASE WHEN events.event = 'sendViaEmail' THEN 1 ELSE NULL END) AS email_action
, COUNT(CASE WHEN events.event = 'SMSAction' THEN 1 ELSE NULL END) AS text_action
, COUNT(CASE WHEN events.event = 'activityChangeDay' THEN 1 ELSE NULL END) AS changed_calendar_days
, COUNT(CASE WHEN events.event = 'toggleFavorite' THEN 1 ELSE NULL END) AS toggle_star
, COUNT(CASE WHEN events.event = 'addressAction' THEN 1 ELSE NULL END) AS map_entity
, COUNT(CASE WHEN events.event = 'viewScreen:emails Screen' THEN 1 ELSE NULL END) AS viewed_email_screen
, COUNT(CASE WHEN events.event = 'viewScreen:contactList Screen' THEN 1 ELSE NULL END) AS viewed_entitys_screen
, COUNT(CASE WHEN events.event = 'viewScreen:activities Screen' THEN 1 ELSE NULL END) AS viewed_calendar_screen
, COUNT(CASE WHEN events.event = 'viewScreen:opportunities Screen' THEN 1 ELSE NULL END) AS viewed_oppty_screen
, COUNT(DISTINCT FLOOR(UNIX_TIMESTAMP(occurred_at)/(60*2))) * 2 AS approximate_usage_in_minutes
FROM events
WHERE email NOT LIKE '%onboarding%'
GROUP BY created_date, email
ORDER BY created_date desc, number_of_events desc ;;
  }

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

  dimension: average_minutes_per_day_tier {
    type: tier
    sql: ${approximate_usage_in_minutes_avg} ;;
  }

  dimension: viewed_email {
    type: yesno
    sql: ${TABLE}.viewed_email_screen ;;
  }

  dimension: viewed_entitys {
    type: yesno
    sql: ${TABLE}.viewed_entitys_screen ;;
  }

  dimension: viewed_calendar {
    type: yesno
    sql: ${TABLE}.viewed_calendar_screen ;;
  }

  dimension: viewed_oppty {
    type: yesno
    sql: ${TABLE}.viewed_oppty_screen ;;
  }

  dimension: called {
    type: yesno
    sql: ${TABLE}.phone_action ;;
  }

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

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

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

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

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

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

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

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

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

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

  dimension: interacted {
    hidden: yes
    sql: ${phone_action} + ${text_action} + ${email_action} ;;
  }

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

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

  dimension: efficiency_action {
    hidden: yes
    sql: ${phone_action} + ${email_action} ;;
  }

  dimension: crm_action {
    hidden: yes
    sql: ${record_save} + ${completed_assignment} + ${save_opportunity} ;;
  }

  dimension_group: created {
    type: time
    timeframes: [date, week, month, day_of_week, year]
    sql: ${TABLE}.created_date ;;
  }

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

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

  measure: period_days_of_usage {
    type: count_distinct
    sql: ${TABLE}.created_date ;;
  }

  measure: approximate_usage_in_minutes_total {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.approximate_usage_in_minutes ;;
  }

  measure: approximate_usage_in_minutes_avg {
    type: avg
    drill_fields: [detail*]
    sql: ${TABLE}.approximate_usage_in_minutes ;;
  }

  measure: number_of_events_total {
    type: sum
    drill_fields: [detail*]
    sql: ${TABLE}.number_of_events ;;
  }

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

  measure: users_who_called_count {
    type: count_distinct
    filters: [phone_action: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_called_percent {
    type: number
    sql: 100.0 * ${users_who_called_count}/${distinct_user_count} ;;
  }

  measure: average_number_of_calls_per_user {
    type: number
    drill_fields: [users.detail*]
    sql: ${phone_action_total}/${distinct_user_count} ;;
  }

  measure: calls_percent_of_events {
    type: number
    sql: 100.0 * ${phone_action_total}/${number_of_events_total} ;;
  }

  measure: record_discard_total {
    type: sum
    sql: ${TABLE}.record_discard ;;
  }

  measure: users_who_discarded_capture_count {
    type: count_distinct
    filters: [record_discard: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_discarded_capture_percent {
    type: number
    sql: 100.0 * ${users_who_discarded_capture_count}/${distinct_user_count} ;;
  }

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

  measure: users_who_saved_capture_count {
    type: count_distinct
    filters: [record_save: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_saved_capture_percent {
    type: number
    sql: 100.0 * ${users_who_saved_capture_count}/${distinct_user_count} ;;
  }

  measure: average_number_of_captures_per_user {
    type: number
    drill_fields: [users.detail*]
    sql: ${record_save_total}/${distinct_user_count} ;;
  }

  measure: captures_percent_of_events {
    type: number
    sql: 100.0 * ${record_save_total}/${number_of_events_total} ;;
  }

  measure: completed_assignment_total {
    type: sum
    sql: ${TABLE}.completed_assignment ;;
  }

  measure: users_who_completed_assignment_count {
    type: count_distinct
    filters: [completed_assignment: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_completed_assignment_percent {
    type: number
    sql: 100.0 * ${users_who_completed_assignment_count}/${distinct_user_count} ;;
  }

  measure: average_number_of_completed_assignments_per_user {
    type: number
    drill_fields: [users.detail*]
    sql: ${completed_assignment_total}/${distinct_user_count} ;;
  }

  measure: complete_task_percent_of_events {
    type: number
    sql: 100.0 * ${completed_assignment_total}/${number_of_events_total} ;;
  }

  measure: create_assignment_total {
    type: sum
    sql: ${TABLE}.create_assignment ;;
  }

  measure: users_who_created_tasks_count {
    type: count_distinct
    filters: [create_assignment: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_created_tasks_percent {
    type: number
    sql: 100.0 * ${users_who_created_tasks_count}/${distinct_user_count} ;;
  }

  measure: email_action_total {
    type: sum
    sql: ${TABLE}.email_action ;;
  }

  measure: users_who_emailed_count {
    type: count_distinct
    filters: [email_action: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_emailed_percent {
    type: number
    sql: 100.0 * ${users_who_emailed_count}/${distinct_user_count} ;;
  }

  measure: text_action_total {
    type: sum
    sql: ${TABLE}.text_action ;;
  }

  measure: users_who_texted_count {
    type: count_distinct
    filters: [text_action: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_texted_percent {
    type: number
    sql: 100.0 * ${users_who_texted_count}/${distinct_user_count} ;;
  }

  measure: viewed_entity_total {
    type: sum
    sql: ${TABLE}.viewed_entity ;;
  }

  measure: users_who_viewed_entity_count {
    type: count_distinct
    filters: [viewed_entity: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_viewed_entity_percent {
    type: number
    sql: 100.0 * ${users_who_viewed_entity_count}/${distinct_user_count} ;;
  }

  measure: entity_save__total {
    type: sum
    sql: ${TABLE}.entity_save ;;
  }

  measure: users_who_saved_contacts_count {
    type: count_distinct
    filters: [create_assignment: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_save_contacts_percent {
    type: number
    sql: 100.0 * ${users_who_saved_contacts_count}/${distinct_user_count} ;;
  }

  measure: None {
  }

  measure: save_remark_total {
    type: sum
    sql: ${TABLE}.save_remark ;;
  }

  measure: users_who_saved_notes_count {
    type: count_distinct
    filters: [create_assignment: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_saved_notes_percent {
    type: sum
    sql: ${TABLE}.save_remark ;;
  }

  measure: interacted_total {
    type: sum
    sql: ${interacted} ;;
  }

  measure: users_who_interacted_count {
    type: count_distinct
    filters: [interacted: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_interacted_percent {
    type: number
    sql: 100.0 * ${users_who_interacted_count}/${distinct_user_count} ;;
  }

  measure: interacted_percent_of_events {
    type: number
    sql: 100.0 * ${interacted_total}/${number_of_events_total} ;;
  }

  measure: changed_calendar_days_total {
    type: sum
    sql: ${TABLE}.changed_calendar_days ;;
  }

  measure: changed_calendar_days_percent_of_events {
    type: number
    sql: 100.0 * ${changed_calendar_days_total}/${number_of_events_total} ;;
  }

  measure: toggle_star_total {
    type: sum
    sql: ${TABLE}.toggle_star ;;
  }

  measure: toggle_star_percent_of_events {
    type: number
    sql: 100.0 * ${toggle_star_total}/${number_of_events_total} ;;
  }

  measure: map_entity_total {
    type: sum
    sql: ${TABLE}.map_entity ;;
  }

  measure: map_entity_percent_of_events {
    type: number
    sql: 100.0 * ${map_entity_total}/${number_of_events_total} ;;
  }

  measure: save_opportunity_total {
    type: sum
    sql: ${TABLE}.save_opportunity ;;
  }

  measure: users_who_saved_oppty_count {
    type: count_distinct
    filters: [save_opportunity: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_saved_oppty_percent {
    type: number
    sql: 100.0 * ${users_who_saved_oppty_count}/${distinct_user_count} ;;
  }

  measure: discard_opportunity_total {
    type: sum
    sql: ${TABLE}.discard_opportunity ;;
  }

  measure: users_who_discarded_oppty_count {
    type: count_distinct
    filters: [discard_opportunity: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: active_users_who_discarded_oppty_percent {
    type: number
    sql: 100.0 * ${users_who_discarded_oppty_count}/${distinct_user_count} ;;
  }

  measure: efficiency_action_total {
    type: sum
    sql: ${efficiency_action} ;;
  }

  measure: users_who_completed_efficiency_action_count {
    type: count_distinct
    filters: [efficiency_action: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_completed_min_5_efficiency_actions_count {
    type: count_distinct
    filters: [efficiency_action: "5 to"]
    sql: ${TABLE}.email ;;
  }

  measure: crm_action_total {
    type: sum
    sql: ${crm_action} ;;
  }

  measure: users_who_completed_crm_action_count {
    type: count_distinct
    filters: [crm_action: "1 to"]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_completed_min_5_crm_actions_count {
    type: count_distinct
    filters: [crm_action: "5 to"]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_completed_min_1_crm_actions_count {
    type: count_distinct
    filters: [crm_action: "1 to 4"]
    sql: ${TABLE}.email ;;
  }

  measure: users_who_completed_0_crm_actions_count {
    type: count_distinct
    filters: [crm_action: "0"]
    sql: ${TABLE}.email ;;
  }
}

daily_usage.view.lookml · 357 lines

source-preserved

- view: daily_usage
  derived_table:
    sql: SELECT
DATE(occurred_at) AS created_date
, email
, COUNT(events.id) AS number_of_events
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:contactProfile Screen' THEN 1 ELSE NULL END) AS viewed_entity
, COUNT(CASE WHEN events.event = 'ContactPrompt:Save' THEN 1 ELSE NULL END) AS entity_save
, COUNT(CASE WHEN events.event = 'phoneAction' THEN 1 ELSE NULL END) AS phone_action
, COUNT(CASE WHEN events.event = 'Viewed viewScreen:taskDetail:creating Screen' THEN 1 ELSE NULL END) AS create_assignment
, COUNT(CASE WHEN events.event = 'taskMarkedCompleted' THEN 1 ELSE NULL END) AS completed_assignment
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Save' THEN 1 ELSE NULL END) AS save_opportunity
, COUNT(CASE WHEN events.event = 'OpportunityPrompt:Discard' THEN 1 ELSE NULL END) AS discard_opportunity
, COUNT(CASE WHEN events.event = 'NotePrompt:Save' THEN 1 ELSE NULL END) AS save_remark
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Save' THEN 1 ELSE NULL END) AS record_save
, COUNT(CASE WHEN events.event = 'LoggingPrompt:Discard' THEN 1 ELSE NULL END) AS record_discard
, COUNT(CASE WHEN events.event = 'sendViaEmail' THEN 1 ELSE NULL END) AS email_action
, COUNT(CASE WHEN events.event = 'SMSAction' THEN 1 ELSE NULL END) AS text_action
, COUNT(CASE WHEN events.event = 'activityChangeDay' THEN 1 ELSE NULL END) AS changed_calendar_days
, COUNT(CASE WHEN events.event = 'toggleFavorite' THEN 1 ELSE NULL END) AS toggle_star
, COUNT(CASE WHEN events.event = 'addressAction' THEN 1 ELSE NULL END) AS map_entity
, COUNT(CASE WHEN events.event = 'viewScreen:emails Screen' THEN 1 ELSE NULL END) AS viewed_email_screen
, COUNT(CASE WHEN events.event = 'viewScreen:contactList Screen' THEN 1 ELSE NULL END) AS viewed_entitys_screen
, COUNT(CASE WHEN events.event = 'viewScreen:activities Screen' THEN 1 ELSE NULL END) AS viewed_calendar_screen
, COUNT(CASE WHEN events.event = 'viewScreen:opportunities Screen' THEN 1 ELSE NULL END) AS viewed_oppty_screen
, COUNT(DISTINCT FLOOR(UNIX_TIMESTAMP(occurred_at)/(60*2))) * 2 AS approximate_usage_in_minutes
FROM events
WHERE email NOT LIKE '%onboarding%'
GROUP BY created_date, email
ORDER BY created_date desc, number_of_events desc
  fields:
  - dimension: email
    sql: ${TABLE}.email
  - dimension: average_minutes_per_day_tier
    type: tier
    sql: ${approximate_usage_in_minutes_avg}
  - dimension: viewed_email
    type: yesno
    sql: ${TABLE}.viewed_email_screen
  - dimension: viewed_entitys
    type: yesno
    sql: ${TABLE}.viewed_entitys_screen
  - dimension: viewed_calendar
    type: yesno
    sql: ${TABLE}.viewed_calendar_screen
  - dimension: viewed_oppty
    type: yesno
    sql: ${TABLE}.viewed_oppty_screen
  - dimension: called
    type: yesno
    sql: ${TABLE}.phone_action
  - dimension: phone_action
    hidden: true
    sql: ${TABLE}.phone_action
  - dimension: record_discard
    hidden: true
    sql: ${TABLE}.record_discard
  - dimension: record_save
    hidden: true
    sql: ${TABLE}.record_save
  - dimension: completed_assignment
    hidden: true
    sql: ${TABLE}.completed_assignment
  - dimension: create_assignment
    hidden: true
    sql: ${TABLE}.create_assignment
  - dimension: email_action
    hidden: true
    sql: ${TABLE}.email_action
  - dimension: text_action
    hidden: true
    sql: ${TABLE}.text_action
  - dimension: viewed_entity
    hidden: true
    sql: ${TABLE}.viewed_entity
  - dimension: entity_save
    hidden: true
    sql: ${TABLE}.entity_save
  - dimension: save_remark
    hidden: true
    sql: ${TABLE}.save_remark
  - dimension: interacted
    hidden: true
    sql: ${phone_action} + ${text_action} + ${email_action}
  - dimension: save_opportunity
    hidden: true
    sql: ${TABLE}.save_opportunity
  - dimension: discard_opportunity
    hidden: true
    sql: ${TABLE}.discard_opportunity
  - dimension: efficiency_action
    hidden: true
    sql: ${phone_action} + ${email_action}
  - dimension: crm_action
    hidden: true
    sql: ${record_save} + ${completed_assignment} + ${save_opportunity}
  - dimension_group: created
    type: time
    timeframes:
    - date
    - week
    - month
    - day_of_week
    - year
    sql: ${TABLE}.created_date
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: distinct_user_count
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${TABLE}.email
  - measure: period_days_of_usage
    type: count_distinct
    sql: ${TABLE}.created_date
  - measure: approximate_usage_in_minutes_total
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.approximate_usage_in_minutes
  - measure: approximate_usage_in_minutes_avg
    type: avg
    drill_fields:
    - detail*
    sql: ${TABLE}.approximate_usage_in_minutes
  - measure: number_of_events_total
    type: sum
    drill_fields:
    - detail*
    sql: ${TABLE}.number_of_events
  - measure: phone_action_total
    type: sum
    sql: ${TABLE}.phone_action
  - measure: users_who_called_count
    type: count_distinct
    filters:
    - phone_action: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_called_percent
    type: number
    sql: 100.0 * ${users_who_called_count}/${distinct_user_count}
  - measure: average_number_of_calls_per_user
    type: number
    drill_fields:
    - users.detail*
    sql: ${phone_action_total}/${distinct_user_count}
  - measure: calls_percent_of_events
    type: number
    sql: 100.0 * ${phone_action_total}/${number_of_events_total}
  - measure: record_discard_total
    type: sum
    sql: ${TABLE}.record_discard
  - measure: users_who_discarded_capture_count
    type: count_distinct
    filters:
    - record_discard: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_discarded_capture_percent
    type: number
    sql: 100.0 * ${users_who_discarded_capture_count}/${distinct_user_count}
  - measure: record_save_total
    type: sum
    sql: ${TABLE}.record_save
  - measure: users_who_saved_capture_count
    type: count_distinct
    filters:
    - record_save: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_saved_capture_percent
    type: number
    sql: 100.0 * ${users_who_saved_capture_count}/${distinct_user_count}
  - measure: average_number_of_captures_per_user
    type: number
    drill_fields:
    - users.detail*
    sql: ${record_save_total}/${distinct_user_count}
  - measure: captures_percent_of_events
    type: number
    sql: 100.0 * ${record_save_total}/${number_of_events_total}
  - measure: completed_assignment_total
    type: sum
    sql: ${TABLE}.completed_assignment
  - measure: users_who_completed_assignment_count
    type: count_distinct
    filters:
    - completed_assignment: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_completed_assignment_percent
    type: number
    sql: 100.0 * ${users_who_completed_assignment_count}/${distinct_user_count}
  - measure: average_number_of_completed_assignments_per_user
    type: number
    drill_fields:
    - users.detail*
    sql: ${completed_assignment_total}/${distinct_user_count}
  - measure: complete_task_percent_of_events
    type: number
    sql: 100.0 * ${completed_assignment_total}/${number_of_events_total}
  - measure: create_assignment_total
    type: sum
    sql: ${TABLE}.create_assignment
  - measure: users_who_created_tasks_count
    type: count_distinct
    filters:
    - create_assignment: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_created_tasks_percent
    type: number
    sql: 100.0 * ${users_who_created_tasks_count}/${distinct_user_count}
  - measure: email_action_total
    type: sum
    sql: ${TABLE}.email_action
  - measure: users_who_emailed_count
    type: count_distinct
    filters:
    - email_action: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_emailed_percent
    type: number
    sql: 100.0 * ${users_who_emailed_count}/${distinct_user_count}
  - measure: text_action_total
    type: sum
    sql: ${TABLE}.text_action
  - measure: users_who_texted_count
    type: count_distinct
    filters:
    - text_action: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_texted_percent
    type: number
    sql: 100.0 * ${users_who_texted_count}/${distinct_user_count}
  - measure: viewed_entity_total
    type: sum
    sql: ${TABLE}.viewed_entity
  - measure: users_who_viewed_entity_count
    type: count_distinct
    filters:
    - viewed_entity: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_viewed_entity_percent
    type: number
    sql: 100.0 * ${users_who_viewed_entity_count}/${distinct_user_count}
  - measure: entity_save__total
    type: sum
    sql: ${TABLE}.entity_save
  - measure: users_who_saved_contacts_count
    type: count_distinct
    filters:
    - create_assignment: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_save_contacts_percent
    type: number
    sql: 100.0 * ${users_who_saved_contacts_count}/${distinct_user_count}
  - measure: None
  - measure: save_remark_total
    type: sum
    sql: ${TABLE}.save_remark
  - measure: users_who_saved_notes_count
    type: count_distinct
    filters:
    - create_assignment: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_saved_notes_percent
    type: sum
    sql: ${TABLE}.save_remark
  - measure: interacted_total
    type: sum
    sql: ${interacted}
  - measure: users_who_interacted_count
    type: count_distinct
    filters:
    - interacted: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_interacted_percent
    type: number
    sql: 100.0 * ${users_who_interacted_count}/${distinct_user_count}
  - measure: interacted_percent_of_events
    type: number
    sql: 100.0 * ${interacted_total}/${number_of_events_total}
  - measure: changed_calendar_days_total
    type: sum
    sql: ${TABLE}.changed_calendar_days
  - measure: changed_calendar_days_percent_of_events
    type: number
    sql: 100.0 * ${changed_calendar_days_total}/${number_of_events_total}
  - measure: toggle_star_total
    type: sum
    sql: ${TABLE}.toggle_star
  - measure: toggle_star_percent_of_events
    type: number
    sql: 100.0 * ${toggle_star_total}/${number_of_events_total}
  - measure: map_entity_total
    type: sum
    sql: ${TABLE}.map_entity
  - measure: map_entity_percent_of_events
    type: number
    sql: 100.0 * ${map_entity_total}/${number_of_events_total}
  - measure: save_opportunity_total
    type: sum
    sql: ${TABLE}.save_opportunity
  - measure: users_who_saved_oppty_count
    type: count_distinct
    filters:
    - save_opportunity: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_saved_oppty_percent
    type: number
    sql: 100.0 * ${users_who_saved_oppty_count}/${distinct_user_count}
  - measure: discard_opportunity_total
    type: sum
    sql: ${TABLE}.discard_opportunity
  - measure: users_who_discarded_oppty_count
    type: count_distinct
    filters:
    - discard_opportunity: 1 to
    sql: ${TABLE}.email
  - measure: active_users_who_discarded_oppty_percent
    type: number
    sql: 100.0 * ${users_who_discarded_oppty_count}/${distinct_user_count}
  - measure: efficiency_action_total
    type: sum
    sql: ${efficiency_action}
  - measure: users_who_completed_efficiency_action_count
    type: count_distinct
    filters:
    - efficiency_action: 1 to
    sql: ${TABLE}.email
  - measure: users_who_completed_min_5_efficiency_actions_count
    type: count_distinct
    filters:
    - efficiency_action: 5 to
    sql: ${TABLE}.email
  - measure: crm_action_total
    type: sum
    sql: ${crm_action}
  - measure: users_who_completed_crm_action_count
    type: count_distinct
    filters:
    - crm_action: 1 to
    sql: ${TABLE}.email
  - measure: users_who_completed_min_5_crm_actions_count
    type: count_distinct
    filters:
    - crm_action: 5 to
    sql: ${TABLE}.email
  - measure: users_who_completed_min_1_crm_actions_count
    type: count_distinct
    filters:
    - crm_action: 1 to 4
    sql: ${TABLE}.email
  - measure: users_who_completed_0_crm_actions_count
    type: count_distinct
    filters:
    - crm_action: 0
    sql: ${TABLE}.email

daily_usage.sql
daily_usage.sql
daily_usage.view.lkml
daily_usage.view.lookml

Daily Usage

Blocks

Assumptions

Contents