Account Cust Serv Facts

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

select
account_id
,min(case when type=0 then id else null end) AS first_support_email_id
,min(case when type=1 then id else null end) AS first_hot_trigger_success_id
,min(case when type=2 then id else null end) AS first_sales_email_response_id
,min(case when type=3 then id else null end) AS first_chat_id
,min(case when type=4 then id else null end) AS first_support_phone_id
,min(case when type=5 then id else null end) AS first_live_chat_id
,min(case when type=6 then id else null end) AS first_offline_chat_id
,min(case when type=7 then id else null end) AS first_sales_call_id
,min(case when type=8 then id else null end) AS first_marketing_site_email_id
,min(case when type=9 then id else null end) AS first_marketing_site_live_chat_id
,min(case when type=10 then id else null end) AS first_marketing_site_offline_chat_id
,min(case when type=11 then id else null end) AS first_sales_scheduled_id
,min(case when type=12 then id else null end) AS first_hot_trigger_attempt_id
,min(case when type=13 then id else null end) AS first_hot_support_attempt_id
,min(case when type=14 then id else null end) AS first_hot_support_success_id
,min(case when type=15 then id else null end) AS first_unscheduled_sales_call_attempt_id
,min(case when type=16 then id else null end) AS first_unscheduled_sales_call_success_id
,min(case when type=17 then id else null end) AS first_support_email_follow_up_attempt_id
,min(case when type=18 then id else null end) AS first_hot_hand_off_id
,min(case when type=19 then id else null end) AS first_qualified_out_id
,min(case when type=20 then id else null end) AS first_qualified_in_id
,min(case when type=21 then id else null end) AS first_inbound_inquiry_id
,min(case when type=22 then id else null end) AS first_outbound_attempt_id
,min(case when type=23 then id else null end) AS first_marketing_sales_call_id
,min(case when type=24 then id else null end) AS first_bad_phone_id
,min(case when type=25 then id else null end) AS first_proactive_sales_email_id
,min(case when type=26 then id else null end) AS first_success_proactive_email_id
,min(case when type=27 then id else null end) AS first_success_email_response_id
,min(case when type=28 then id else null end) AS first_success_phone_id
,min(case when type=29 then id else null end) AS first_success_opt_out_id
,min(case when type=30 then id else null end) AS first_success_call_scheduled_id
,min(case when type=31 then id else null end) AS first_success_on_boarding_call_interest_id
,min(case when type=32 then id else null end) AS first_success_pre_call_email_id
,min(case when type=33 then id else null end) AS first_success_post_call_follow_up_email_id
,min(case when type=34 then id else null end) AS first_changed_group_to_dev_id
,min(case when type=35 then id else null end) AS first_group_dev_case_updated_id
,min(case when type=36 then id else null end) AS first_changed_group_from_dev_id
,min(case when type=37 then id else null end) AS first_group_dev_case_resolved_id
,min(case when type=38 then id else null end) AS first_success_proactive_call_id
,min(case when type=39 then id else null end) AS first_stop_sales_contact_id
,min(case when type=40 then id else null end) AS first_success_on_boarding_no_show_id
,min(case when type=41 then id else null end) AS first_success_on_boarding_cancel_id
,min(case when type=42 then id else null end) AS first_gave_a_review_id
,min(case when type=43 then id else null end) AS first_cancel_request_id
,min(case when type=44 then id else null end) AS first_save_attempt_id
,min(case when type=45 then id else null end) AS first_save_success_id
,min(case when type=46 then id else null end) AS first_success_proactive_health_call_id
,min(case when type=47 then id else null end) AS first_success_follow_up_call_id
,min(case when type=48 then id else null end) AS first_success_proactive_health_call_attempt_id
,min(case when type=49 then id else null end) AS first_success_follow_up_call_attempt_id
,min(case when type=50 then id else null end) AS first_custom_report_request_id
,min(case when type=51 then id else null end) AS first_trial_date_extended_id
,min(case when type=52 then id else null end) AS first_support_proactive_phone_attempt_id
,min(case when type=53 then id else null end) AS first_support_proactive_phone_success_id
,min(case when type=54 then id else null end) AS first_support_phone_callback_attempt_id
,min(case when type=55 then id else null end) AS first_lead_confirm_sql_id
,min(case when type=56 then id else null end) AS first_lead_confirm_mql_id
,min(case when type=57 then id else null end) AS first_lead_confirm_disqual_id
,max(case when type IN (55, 56, 57) then id else null end) AS last_funnel_stage_change
from central_data.customer_service_event
where active = 1
group by account_id

# models/schema.yml
version: 2

models:
  - name: account_cust_serv_facts
    columns:
      - name: first_support_email_id
        description: "customer_service_event ID for first support email"
      - name: first_hot_trigger_success_id
        description: "customer_service_event ID for first hot trigger success"
      - name: first_sales_email_response_id
        description: "customer_service_event ID for first sales email response (account emails sales and sales responds)"
      - name: first_chat_id
        description: "customer_service_event ID for first live chat - deprecated"
      - name: first_support_phone_id
        description: "customer_service_event ID for first support call"
      - name: first_live_chat_id
        description: "customer_service_event ID for first live chat (to use)"
      - name: first_offline_chat_id
        description: "customer_service_event ID for first offline chat"
      - name: first_sales_call_id
        description: "customer_service_event ID for first sales call"
      - name: first_marketing_site_email_id
        description: "customer_service_event ID for first marketing site email"
      - name: first_marketing_site_live_chat_id
        description: "customer_service_event ID for first marketing site live chat"
      - name: first_marketing_site_offline_chat_id
        description: "customer_service_event ID for first marketing site offline chat"
      - name: first_sales_scheduled_id
        description: "customer_service_event ID for first time sales call scheduled"
      - name: first_hot_trigger_attempt_id
        description: "customer_service_event ID for first sales hot trigger attempt"
      - name: first_hot_support_attempt_id
        description: "customer_service_event ID for first support hot trigger event - support proactively live chats user in-app"
      - name: first_hot_support_success_id
        description: "customer_service_event ID for first support hot trigger success - support proactively live chats user in-app AND user responds"
      - name: first_unscheduled_sales_call_attempt_id
        description: "customer_service_event ID for first unscheduled sales call attempt"
      - name: first_unscheduled_sales_call_success_id
        description: "customer_service_event ID for first unscheduled sales call success (customer actually picked up)"
      - name: first_support_email_follow_up_attempt_id
        description: "customer_service_event ID for first support email follow up attempt - support proactively follows up with customer"
      - name: first_hot_hand_off_id
        description: "customer_service_event ID for first instance of account being reached via hot trigger then handed off to sales"
      - name: first_qualified_out_id
        description: "customer_service_event ID for first time Sales qualified lead out - lead not worth pursuing"
      - name: first_qualified_in_id
        description: "customer_service_event ID for first time Sales qualified lead in - lead worth pursuing"
      - name: first_inbound_inquiry_id
        description: "customer_service_event ID for first time account called in to the phone number on the marketing site (mainly SDR use)"
      - name: first_outbound_attempt_id
        description: "customer_service_event ID for first time we call someone back who called in (SDR or Master <% parameter partner_name %>er)"
      - name: first_marketing_sales_call_id
        description: "customer_service_event ID for first time Master <% parameter partner_name %>er called back and got someone to sign up"
      - name: first_bad_phone_id
        description: "customer_service_event ID for first time we attempted to call someone but phone number was incorrect/invalid"
      - name: first_proactive_sales_email_id
        description: "customer_service_event ID for first time sales sends proactive email as follow up to unscheduled call attempt"
      - name: first_success_proactive_email_id
        description: "customer_service_event ID for first time success sends proactive email"
      - name: first_success_email_response_id
        description: "customer_service_event ID for first time success responds to an email"
      - name: first_success_phone_id
        description: "customer_service_event ID for first success call"
      - name: first_success_opt_out_id
        description: "customer_service_event ID for first time account opts out of the success process"
      - name: first_success_call_scheduled_id
        description: "customer_service_event ID for first time success call scheduled"
      - name: first_success_on_boarding_call_interest_id
        description: "customer_service_event ID for first time customer indicates interest in a success call"
      - name: first_success_pre_call_email_id
        description: "customer_service_event ID for first time success sends pre call preparatory email"
      - name: first_success_post_call_follow_up_email_id
        description: "customer_service_event ID for first time success sends proactive email"
      - name: first_changed_group_to_dev_id
        description: "customer_service_event ID for first time desk case group changed to dev (old pre-JIRA bug procedure)"
      - name: first_group_dev_case_updated_id
        description: "customer_service_event ID for first time desk case assigned to dev updated (old pre-JIRA bug procedure)"
      - name: first_changed_group_from_dev_id
        description: "customer_service_event ID for first time desk case changed from dev to different group (old pre-JIRA bug procedure)"
      - name: first_group_dev_case_resolved_id
        description: "customer_service_event ID for first time desk case assigned to dev resolved (old pre-JIRA bug procedure)"
      - name: first_success_proactive_call_id
        description: "customer_service_event ID for first time success proactively calls account to schedule a success call"
      - name: first_stop_sales_contact_id
        description: "customer_service_event ID for first time account requests to have sales stop contacting them"

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.

select
account_id
,min(case when type=0 then id else null end) AS first_support_email_id
,min(case when type=1 then id else null end) AS first_hot_trigger_success_id
,min(case when type=2 then id else null end) AS first_sales_email_response_id
,min(case when type=3 then id else null end) AS first_chat_id
,min(case when type=4 then id else null end) AS first_support_phone_id
,min(case when type=5 then id else null end) AS first_live_chat_id
,min(case when type=6 then id else null end) AS first_offline_chat_id
,min(case when type=7 then id else null end) AS first_sales_call_id
,min(case when type=8 then id else null end) AS first_marketing_site_email_id
,min(case when type=9 then id else null end) AS first_marketing_site_live_chat_id
,min(case when type=10 then id else null end) AS first_marketing_site_offline_chat_id
,min(case when type=11 then id else null end) AS first_sales_scheduled_id
,min(case when type=12 then id else null end) AS first_hot_trigger_attempt_id
,min(case when type=13 then id else null end) AS first_hot_support_attempt_id
,min(case when type=14 then id else null end) AS first_hot_support_success_id
,min(case when type=15 then id else null end) AS first_unscheduled_sales_call_attempt_id
,min(case when type=16 then id else null end) AS first_unscheduled_sales_call_success_id
,min(case when type=17 then id else null end) AS first_support_email_follow_up_attempt_id
,min(case when type=18 then id else null end) AS first_hot_hand_off_id
,min(case when type=19 then id else null end) AS first_qualified_out_id
,min(case when type=20 then id else null end) AS first_qualified_in_id
,min(case when type=21 then id else null end) AS first_inbound_inquiry_id
,min(case when type=22 then id else null end) AS first_outbound_attempt_id
,min(case when type=23 then id else null end) AS first_marketing_sales_call_id
,min(case when type=24 then id else null end) AS first_bad_phone_id
,min(case when type=25 then id else null end) AS first_proactive_sales_email_id
,min(case when type=26 then id else null end) AS first_success_proactive_email_id
,min(case when type=27 then id else null end) AS first_success_email_response_id
,min(case when type=28 then id else null end) AS first_success_phone_id
,min(case when type=29 then id else null end) AS first_success_opt_out_id
,min(case when type=30 then id else null end) AS first_success_call_scheduled_id
,min(case when type=31 then id else null end) AS first_success_on_boarding_call_interest_id
,min(case when type=32 then id else null end) AS first_success_pre_call_email_id
,min(case when type=33 then id else null end) AS first_success_post_call_follow_up_email_id
,min(case when type=34 then id else null end) AS first_changed_group_to_dev_id
,min(case when type=35 then id else null end) AS first_group_dev_case_updated_id
,min(case when type=36 then id else null end) AS first_changed_group_from_dev_id
,min(case when type=37 then id else null end) AS first_group_dev_case_resolved_id
,min(case when type=38 then id else null end) AS first_success_proactive_call_id
,min(case when type=39 then id else null end) AS first_stop_sales_contact_id
,min(case when type=40 then id else null end) AS first_success_on_boarding_no_show_id
,min(case when type=41 then id else null end) AS first_success_on_boarding_cancel_id
,min(case when type=42 then id else null end) AS first_gave_a_review_id
,min(case when type=43 then id else null end) AS first_cancel_request_id
,min(case when type=44 then id else null end) AS first_save_attempt_id
,min(case when type=45 then id else null end) AS first_save_success_id
,min(case when type=46 then id else null end) AS first_success_proactive_health_call_id
,min(case when type=47 then id else null end) AS first_success_follow_up_call_id
,min(case when type=48 then id else null end) AS first_success_proactive_health_call_attempt_id
,min(case when type=49 then id else null end) AS first_success_follow_up_call_attempt_id
,min(case when type=50 then id else null end) AS first_custom_report_request_id
,min(case when type=51 then id else null end) AS first_trial_date_extended_id
,min(case when type=52 then id else null end) AS first_support_proactive_phone_attempt_id
,min(case when type=53 then id else null end) AS first_support_proactive_phone_success_id
,min(case when type=54 then id else null end) AS first_support_phone_callback_attempt_id
,min(case when type=55 then id else null end) AS first_lead_confirm_sql_id
,min(case when type=56 then id else null end) AS first_lead_confirm_mql_id
,min(case when type=57 then id else null end) AS first_lead_confirm_disqual_id
,max(case when type IN (55, 56, 57) then id else null end) AS last_funnel_stage_change
from central_data.customer_service_event
where active = 1
group by account_id

view: account_cust_serv_facts {
  derived_table: {
    sql: select
account_id
,min(case when type=0 then id else null end) AS first_support_email_id
,min(case when type=1 then id else null end) AS first_hot_trigger_success_id
,min(case when type=2 then id else null end) AS first_sales_email_response_id
,min(case when type=3 then id else null end) AS first_chat_id
,min(case when type=4 then id else null end) AS first_support_phone_id
,min(case when type=5 then id else null end) AS first_live_chat_id
,min(case when type=6 then id else null end) AS first_offline_chat_id
,min(case when type=7 then id else null end) AS first_sales_call_id
,min(case when type=8 then id else null end) AS first_marketing_site_email_id
,min(case when type=9 then id else null end) AS first_marketing_site_live_chat_id
,min(case when type=10 then id else null end) AS first_marketing_site_offline_chat_id
,min(case when type=11 then id else null end) AS first_sales_scheduled_id
,min(case when type=12 then id else null end) AS first_hot_trigger_attempt_id
,min(case when type=13 then id else null end) AS first_hot_support_attempt_id
,min(case when type=14 then id else null end) AS first_hot_support_success_id
,min(case when type=15 then id else null end) AS first_unscheduled_sales_call_attempt_id
,min(case when type=16 then id else null end) AS first_unscheduled_sales_call_success_id
,min(case when type=17 then id else null end) AS first_support_email_follow_up_attempt_id
,min(case when type=18 then id else null end) AS first_hot_hand_off_id
,min(case when type=19 then id else null end) AS first_qualified_out_id
,min(case when type=20 then id else null end) AS first_qualified_in_id
,min(case when type=21 then id else null end) AS first_inbound_inquiry_id
,min(case when type=22 then id else null end) AS first_outbound_attempt_id
,min(case when type=23 then id else null end) AS first_marketing_sales_call_id
,min(case when type=24 then id else null end) AS first_bad_phone_id
,min(case when type=25 then id else null end) AS first_proactive_sales_email_id
,min(case when type=26 then id else null end) AS first_success_proactive_email_id
,min(case when type=27 then id else null end) AS first_success_email_response_id
,min(case when type=28 then id else null end) AS first_success_phone_id
,min(case when type=29 then id else null end) AS first_success_opt_out_id
,min(case when type=30 then id else null end) AS first_success_call_scheduled_id
,min(case when type=31 then id else null end) AS first_success_on_boarding_call_interest_id
,min(case when type=32 then id else null end) AS first_success_pre_call_email_id
,min(case when type=33 then id else null end) AS first_success_post_call_follow_up_email_id
,min(case when type=34 then id else null end) AS first_changed_group_to_dev_id
,min(case when type=35 then id else null end) AS first_group_dev_case_updated_id
,min(case when type=36 then id else null end) AS first_changed_group_from_dev_id
,min(case when type=37 then id else null end) AS first_group_dev_case_resolved_id
,min(case when type=38 then id else null end) AS first_success_proactive_call_id
,min(case when type=39 then id else null end) AS first_stop_sales_contact_id
,min(case when type=40 then id else null end) AS first_success_on_boarding_no_show_id
,min(case when type=41 then id else null end) AS first_success_on_boarding_cancel_id
,min(case when type=42 then id else null end) AS first_gave_a_review_id
,min(case when type=43 then id else null end) AS first_cancel_request_id
,min(case when type=44 then id else null end) AS first_save_attempt_id
,min(case when type=45 then id else null end) AS first_save_success_id
,min(case when type=46 then id else null end) AS first_success_proactive_health_call_id
,min(case when type=47 then id else null end) AS first_success_follow_up_call_id
,min(case when type=48 then id else null end) AS first_success_proactive_health_call_attempt_id
,min(case when type=49 then id else null end) AS first_success_follow_up_call_attempt_id
,min(case when type=50 then id else null end) AS first_custom_report_request_id
,min(case when type=51 then id else null end) AS first_trial_date_extended_id
,min(case when type=52 then id else null end) AS first_support_proactive_phone_attempt_id
,min(case when type=53 then id else null end) AS first_support_proactive_phone_success_id
,min(case when type=54 then id else null end) AS first_support_phone_callback_attempt_id
,min(case when type=55 then id else null end) AS first_lead_confirm_sql_id
,min(case when type=56 then id else null end) AS first_lead_confirm_mql_id
,min(case when type=57 then id else null end) AS first_lead_confirm_disqual_id
,max(case when type IN (55, 56, 57) then id else null end) AS last_funnel_stage_change
from central_data.customer_service_event
where active = 1
group by account_id ;;
  }

  dimension: first_support_email_id {
    description: "customer_service_event ID for first support email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_email_id ;;
  }

  dimension: first_hot_trigger_success_id {
    description: "customer_service_event ID for first hot trigger success"
    type: int
    hidden: yes
    sql: ${TABLE}.first_hot_trigger_success_id ;;
  }

  dimension: first_sales_email_response_id {
    description: "customer_service_event ID for first sales email response (account emails sales and sales responds)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_sales_email_response_id ;;
  }

  dimension: first_chat_id {
    description: "customer_service_event ID for first live chat - deprecated"
    type: int
    hidden: yes
    sql: ${TABLE}.first_chat_id ;;
  }

  dimension: first_support_phone_id {
    description: "customer_service_event ID for first support call"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_phone_id ;;
  }

  dimension: first_live_chat_id {
    description: "customer_service_event ID for first live chat (to use)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_live_chat_id ;;
  }

  dimension: first_offline_chat_id {
    description: "customer_service_event ID for first offline chat"
    type: int
    hidden: yes
    sql: ${TABLE}.first_offline_chat_id ;;
  }

  dimension: first_sales_call_id {
    description: "customer_service_event ID for first sales call"
    type: int
    hidden: yes
    sql: ${TABLE}.first_sales_call_id ;;
  }

  dimension: first_marketing_site_email_id {
    description: "customer_service_event ID for first marketing site email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_marketing_site_email_id ;;
  }

  dimension: first_marketing_site_live_chat_id {
    description: "customer_service_event ID for first marketing site live chat"
    type: int
    hidden: yes
    sql: ${TABLE}.first_marketing_site_live_chat_id ;;
  }

  dimension: first_marketing_site_offline_chat_id {
    description: "customer_service_event ID for first marketing site offline chat"
    type: int
    hidden: yes
    sql: ${TABLE}.first_marketing_site_offline_chat_id ;;
  }

  dimension: first_sales_scheduled_id {
    description: "customer_service_event ID for first time sales call scheduled"
    type: int
    hidden: yes
    sql: ${TABLE}.first_sales_scheduled_id ;;
  }

  dimension: first_hot_trigger_attempt_id {
    description: "customer_service_event ID for first sales hot trigger attempt"
    type: int
    hidden: yes
    sql: ${TABLE}.first_hot_trigger_attempt_id ;;
  }

  dimension: first_hot_support_attempt_id {
    description: "customer_service_event ID for first support hot trigger event - support proactively live chats user in-app"
    type: int
    hidden: yes
    sql: ${TABLE}.first_hot_support_attempt_id ;;
  }

  dimension: first_hot_support_success_id {
    description: "customer_service_event ID for first support hot trigger success - support proactively live chats user in-app AND user responds"
    type: int
    hidden: yes
    sql: ${TABLE}.first_hot_support_success_id ;;
  }

  dimension: first_unscheduled_sales_call_attempt_id {
    description: "customer_service_event ID for first unscheduled sales call attempt"
    type: int
    hidden: yes
    sql: ${TABLE}.first_unscheduled_sales_call_attempt_id ;;
  }

  dimension: first_unscheduled_sales_call_success_id {
    description: "customer_service_event ID for first unscheduled sales call success (customer actually picked up)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_unscheduled_sales_call_success_id ;;
  }

  dimension: first_support_email_follow_up_attempt_id {
    description: "customer_service_event ID for first support email follow up attempt - support proactively follows up with customer"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_email_follow_up_attempt_id ;;
  }

  dimension: first_hot_hand_off_id {
    description: "customer_service_event ID for first instance of account being reached via hot trigger then handed off to sales"
    type: int
    hidden: yes
    sql: ${TABLE}.first_hot_hand_off_id ;;
  }

  dimension: first_qualified_out_id {
    description: "customer_service_event ID for first time Sales qualified lead out - lead not worth pursuing"
    type: int
    hidden: yes
    sql: ${TABLE}.first_qualified_out_id ;;
  }

  dimension: first_qualified_in_id {
    description: "customer_service_event ID for first time Sales qualified lead in - lead worth pursuing"
    type: int
    hidden: yes
    sql: ${TABLE}.first_qualified_in_id ;;
  }

  dimension: first_inbound_inquiry_id {
    description: "customer_service_event ID for first time account called in to the phone number on the marketing site (mainly SDR use)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_inbound_inquiry_id ;;
  }

  dimension: first_outbound_attempt_id {
    description: "customer_service_event ID for first time we call someone back who called in (SDR or Master <% parameter partner_name %>er)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_outbound_attempt_id ;;
  }

  dimension: first_marketing_sales_call_id {
    description: "customer_service_event ID for first time Master <% parameter partner_name %>er called back and got someone to sign up"
    type: int
    hidden: yes
    sql: ${TABLE}.first_marketing_sales_call_id ;;
  }

  dimension: first_bad_phone_id {
    description: "customer_service_event ID for first time we attempted to call someone but phone number was incorrect/invalid"
    type: int
    hidden: yes
    sql: ${TABLE}.first_bad_phone_id ;;
  }

  dimension: first_proactive_sales_email_id {
    description: "customer_service_event ID for first time sales sends proactive email as follow up to unscheduled call attempt"
    type: int
    hidden: yes
    sql: ${TABLE}.first_proactive_sales_email_id ;;
  }

  dimension: first_success_proactive_email_id {
    description: "customer_service_event ID for first time success sends proactive email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_proactive_email_id ;;
  }

  dimension: first_success_email_response_id {
    description: "customer_service_event ID for first time success responds to an email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_email_response_id ;;
  }

  dimension: first_success_phone_id {
    description: "customer_service_event ID for first success call"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_phone_id ;;
  }

  dimension: first_success_opt_out_id {
    description: "customer_service_event ID for first time account opts out of the success process"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_opt_out_id ;;
  }

  dimension: first_success_call_scheduled_id {
    description: "customer_service_event ID for first time success call scheduled"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_call_scheduled_id ;;
  }

  dimension: first_success_on_boarding_call_interest_id {
    description: "customer_service_event ID for first time customer indicates interest in a success call"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_on_boarding_call_interest_id ;;
  }

  dimension: first_success_pre_call_email_id {
    description: "customer_service_event ID for first time success sends pre call preparatory email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_pre_call_email_id ;;
  }

  dimension: first_success_post_call_follow_up_email_id {
    description: "customer_service_event ID for first time success sends proactive email"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_post_call_follow_up_email_id ;;
  }

  dimension: first_changed_group_to_dev_id {
    description: "customer_service_event ID for first time desk case group changed to dev (old pre-JIRA bug procedure)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_changed_group_to_dev_id ;;
  }

  dimension: first_group_dev_case_updated_id {
    description: "customer_service_event ID for first time desk case assigned to dev updated (old pre-JIRA bug procedure)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_group_dev_case_updated_id ;;
  }

  dimension: first_changed_group_from_dev_id {
    description: "customer_service_event ID for first time desk case changed from dev to different group (old pre-JIRA bug procedure)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_changed_group_from_dev_id ;;
  }

  dimension: first_group_dev_case_resolved_id {
    description: "customer_service_event ID for first time desk case assigned to dev resolved (old pre-JIRA bug procedure)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_group_dev_case_resolved_id ;;
  }

  dimension: first_success_proactive_call_id {
    description: "customer_service_event ID for first time success proactively calls account to schedule a success call"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_proactive_call_id ;;
  }

  dimension: first_stop_sales_contact_id {
    description: "customer_service_event ID for first time account requests to have sales stop contacting them"
    type: int
    hidden: yes
    sql: ${TABLE}.first_stop_sales_contact_id ;;
  }

  dimension: first_success_on_boarding_no_show_id {
    description: "customer_service_event ID for first time account does not show up to their success call (same day)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_on_boarding_no_show_id ;;
  }

  dimension: first_success_on_boarding_cancel_id {
    description: "customer_service_event ID for first time account cancels their success call (one day in advance)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_on_boarding_cancel_id ;;
  }

  dimension: first_gave_a_review_id {
    description: "customer_service_event ID for first time account gave <% parameter partner_name %> a review"
    type: int
    hidden: yes
    sql: ${TABLE}.first_gave_a_review_id ;;
  }

  dimension: first_cancel_request_id {
    description: "customer_service_event ID for first time account requested to cancel their <% parameter partner_name %> account"
    type: int
    hidden: yes
    sql: ${TABLE}.first_cancel_request_id ;;
  }

  dimension: first_save_attempt_id {
    description: "customer_service_event ID for first time we attempt to save an account from cancelling"
    type: int
    hidden: yes
    sql: ${TABLE}.first_save_attempt_id ;;
  }

  dimension: first_save_success_id {
    description: "customer_service_event ID for first time we successfully save an account from cancelling"
    type: int
    hidden: yes
    sql: ${TABLE}.first_save_success_id ;;
  }

  dimension: first_success_proactive_health_call_id {
    description: "customer_service_event ID for first time success successfully calls an account that had a low health score (Preact)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_proactive_health_call_id ;;
  }

  dimension: first_success_follow_up_call_id {
    description: "customer_service_event ID for first itme success successfully proactively calls an account they had a success call with previously as a follow up"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_follow_up_call_id ;;
  }

  dimension: first_success_proactive_health_call_attempt_id {
    description: "customer_service_event ID for first time success attempts to call an account that had a low health score (Preact)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_proactive_health_call_attempt_id ;;
  }

  dimension: first_success_follow_up_call_attempt_id {
    description: "customer_service_event ID for first time success attempts to proactively call an account they had a success call with previously as a follow up"
    type: int
    hidden: yes
    sql: ${TABLE}.first_success_follow_up_call_attempt_id ;;
  }

  dimension: first_custom_report_request_id {
    description: "customer_service_event ID for first custom report request"
    type: int
    hidden: yes
    sql: ${TABLE}.first_custom_report_request_id ;;
  }

  dimension: first_trial_date_extended_id {
    description: "customer_service_event ID for first time trial date extended"
    type: int
    hidden: yes
    sql: ${TABLE}.first_trial_date_extended_id ;;
  }

  dimension: first_support_proactive_phone_attempt_id {
    description: "customer_service_event ID for first time support attempts to proactively call an account to check in"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_proactive_phone_attempt_id ;;
  }

  dimension: first_support_proactive_phone_success_id {
    description: "customer_service_event ID for first time support successfully proactively calls an account to check in"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_proactive_phone_success_id ;;
  }

  dimension: first_support_phone_callback_attempt_id {
    description: "customer_service_event ID for first time support attempts to call back a customer that put in a support phone request"
    type: int
    hidden: yes
    sql: ${TABLE}.first_support_phone_callback_attempt_id ;;
  }

  dimension: first_lead_confirm_sql_id {
    description: "customer_service_event ID for first time a lead confirmed as sales ready (SQL)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_lead_confirm_sql_id ;;
  }

  dimension: first_lead_confirm_mql_id {
    description: "customer_service_event ID for first time a lead confirmed as Not sales ready, but not disqualified (MQL)"
    type: int
    hidden: yes
    sql: ${TABLE}.first_lead_confirm_mql_id ;;
  }

  dimension: first_lead_confirm_disqual_id {
    description: "customer_service_event ID for first time a lead disqualified from the sales process"
    type: int
    hidden: yes
    sql: ${TABLE}.first_lead_confirm_disqual_id ;;
  }

  dimension: last_funnel_stage_change {
    description: "customer_service_event ID for last time a lead confirmed as sales ready, not sales ready, or disqualified"
    type: int
    hidden: yes
    sql: ${TABLE}.last_funnel_stage_change ;;
  }
}

- view: account_cust_serv_facts
  derived_table:
    sql: select
account_id
,min(case when type=0 then id else null end) AS first_support_email_id
,min(case when type=1 then id else null end) AS first_hot_trigger_success_id
,min(case when type=2 then id else null end) AS first_sales_email_response_id
,min(case when type=3 then id else null end) AS first_chat_id
,min(case when type=4 then id else null end) AS first_support_phone_id
,min(case when type=5 then id else null end) AS first_live_chat_id
,min(case when type=6 then id else null end) AS first_offline_chat_id
,min(case when type=7 then id else null end) AS first_sales_call_id
,min(case when type=8 then id else null end) AS first_marketing_site_email_id
,min(case when type=9 then id else null end) AS first_marketing_site_live_chat_id
,min(case when type=10 then id else null end) AS first_marketing_site_offline_chat_id
,min(case when type=11 then id else null end) AS first_sales_scheduled_id
,min(case when type=12 then id else null end) AS first_hot_trigger_attempt_id
,min(case when type=13 then id else null end) AS first_hot_support_attempt_id
,min(case when type=14 then id else null end) AS first_hot_support_success_id
,min(case when type=15 then id else null end) AS first_unscheduled_sales_call_attempt_id
,min(case when type=16 then id else null end) AS first_unscheduled_sales_call_success_id
,min(case when type=17 then id else null end) AS first_support_email_follow_up_attempt_id
,min(case when type=18 then id else null end) AS first_hot_hand_off_id
,min(case when type=19 then id else null end) AS first_qualified_out_id
,min(case when type=20 then id else null end) AS first_qualified_in_id
,min(case when type=21 then id else null end) AS first_inbound_inquiry_id
,min(case when type=22 then id else null end) AS first_outbound_attempt_id
,min(case when type=23 then id else null end) AS first_marketing_sales_call_id
,min(case when type=24 then id else null end) AS first_bad_phone_id
,min(case when type=25 then id else null end) AS first_proactive_sales_email_id
,min(case when type=26 then id else null end) AS first_success_proactive_email_id
,min(case when type=27 then id else null end) AS first_success_email_response_id
,min(case when type=28 then id else null end) AS first_success_phone_id
,min(case when type=29 then id else null end) AS first_success_opt_out_id
,min(case when type=30 then id else null end) AS first_success_call_scheduled_id
,min(case when type=31 then id else null end) AS first_success_on_boarding_call_interest_id
,min(case when type=32 then id else null end) AS first_success_pre_call_email_id
,min(case when type=33 then id else null end) AS first_success_post_call_follow_up_email_id
,min(case when type=34 then id else null end) AS first_changed_group_to_dev_id
,min(case when type=35 then id else null end) AS first_group_dev_case_updated_id
,min(case when type=36 then id else null end) AS first_changed_group_from_dev_id
,min(case when type=37 then id else null end) AS first_group_dev_case_resolved_id
,min(case when type=38 then id else null end) AS first_success_proactive_call_id
,min(case when type=39 then id else null end) AS first_stop_sales_contact_id
,min(case when type=40 then id else null end) AS first_success_on_boarding_no_show_id
,min(case when type=41 then id else null end) AS first_success_on_boarding_cancel_id
,min(case when type=42 then id else null end) AS first_gave_a_review_id
,min(case when type=43 then id else null end) AS first_cancel_request_id
,min(case when type=44 then id else null end) AS first_save_attempt_id
,min(case when type=45 then id else null end) AS first_save_success_id
,min(case when type=46 then id else null end) AS first_success_proactive_health_call_id
,min(case when type=47 then id else null end) AS first_success_follow_up_call_id
,min(case when type=48 then id else null end) AS first_success_proactive_health_call_attempt_id
,min(case when type=49 then id else null end) AS first_success_follow_up_call_attempt_id
,min(case when type=50 then id else null end) AS first_custom_report_request_id
,min(case when type=51 then id else null end) AS first_trial_date_extended_id
,min(case when type=52 then id else null end) AS first_support_proactive_phone_attempt_id
,min(case when type=53 then id else null end) AS first_support_proactive_phone_success_id
,min(case when type=54 then id else null end) AS first_support_phone_callback_attempt_id
,min(case when type=55 then id else null end) AS first_lead_confirm_sql_id
,min(case when type=56 then id else null end) AS first_lead_confirm_mql_id
,min(case when type=57 then id else null end) AS first_lead_confirm_disqual_id
,max(case when type IN (55, 56, 57) then id else null end) AS last_funnel_stage_change
from central_data.customer_service_event
where active = 1
group by account_id
  fields:
  - dimension: first_support_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first support email
    sql: ${TABLE}.first_support_email_id
  - dimension: first_hot_trigger_success_id
    type: int
    hidden: true
    description: customer_service_event ID for first hot trigger success
    sql: ${TABLE}.first_hot_trigger_success_id
  - dimension: first_sales_email_response_id
    type: int
    hidden: true
    description: customer_service_event ID for first sales email response (account emails sales and sales responds)
    sql: ${TABLE}.first_sales_email_response_id
  - dimension: first_chat_id
    type: int
    hidden: true
    description: customer_service_event ID for first live chat - deprecated
    sql: ${TABLE}.first_chat_id
  - dimension: first_support_phone_id
    type: int
    hidden: true
    description: customer_service_event ID for first support call
    sql: ${TABLE}.first_support_phone_id
  - dimension: first_live_chat_id
    type: int
    hidden: true
    description: customer_service_event ID for first live chat (to use)
    sql: ${TABLE}.first_live_chat_id
  - dimension: first_offline_chat_id
    type: int
    hidden: true
    description: customer_service_event ID for first offline chat
    sql: ${TABLE}.first_offline_chat_id
  - dimension: first_sales_call_id
    type: int
    hidden: true
    description: customer_service_event ID for first sales call
    sql: ${TABLE}.first_sales_call_id
  - dimension: first_marketing_site_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first marketing site email
    sql: ${TABLE}.first_marketing_site_email_id
  - dimension: first_marketing_site_live_chat_id
    type: int
    hidden: true
    description: customer_service_event ID for first marketing site live chat
    sql: ${TABLE}.first_marketing_site_live_chat_id
  - dimension: first_marketing_site_offline_chat_id
    type: int
    hidden: true
    description: customer_service_event ID for first marketing site offline chat
    sql: ${TABLE}.first_marketing_site_offline_chat_id
  - dimension: first_sales_scheduled_id
    type: int
    hidden: true
    description: customer_service_event ID for first time sales call scheduled
    sql: ${TABLE}.first_sales_scheduled_id
  - dimension: first_hot_trigger_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first sales hot trigger attempt
    sql: ${TABLE}.first_hot_trigger_attempt_id
  - dimension: first_hot_support_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first support hot trigger event - support proactively live chats user in-app
    sql: ${TABLE}.first_hot_support_attempt_id
  - dimension: first_hot_support_success_id
    type: int
    hidden: true
    description: customer_service_event ID for first support hot trigger success - support proactively live chats user in-app AND user responds
    sql: ${TABLE}.first_hot_support_success_id
  - dimension: first_unscheduled_sales_call_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first unscheduled sales call attempt
    sql: ${TABLE}.first_unscheduled_sales_call_attempt_id
  - dimension: first_unscheduled_sales_call_success_id
    type: int
    hidden: true
    description: customer_service_event ID for first unscheduled sales call success (customer actually picked up)
    sql: ${TABLE}.first_unscheduled_sales_call_success_id
  - dimension: first_support_email_follow_up_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first support email follow up attempt - support proactively follows up with customer
    sql: ${TABLE}.first_support_email_follow_up_attempt_id
  - dimension: first_hot_hand_off_id
    type: int
    hidden: true
    description: customer_service_event ID for first instance of account being reached via hot trigger then handed off to sales
    sql: ${TABLE}.first_hot_hand_off_id
  - dimension: first_qualified_out_id
    type: int
    hidden: true
    description: customer_service_event ID for first time Sales qualified lead out - lead not worth pursuing
    sql: ${TABLE}.first_qualified_out_id
  - dimension: first_qualified_in_id
    type: int
    hidden: true
    description: customer_service_event ID for first time Sales qualified lead in - lead worth pursuing
    sql: ${TABLE}.first_qualified_in_id
  - dimension: first_inbound_inquiry_id
    type: int
    hidden: true
    description: customer_service_event ID for first time account called in to the phone number on the marketing site (mainly SDR use)
    sql: ${TABLE}.first_inbound_inquiry_id
  - dimension: first_outbound_attempt_id
    type: int
    hidden: true
    description: 'customer_service_event ID for first time we call someone back who called in (SDR or Master <% parameter partner_name %>er)'
    sql: ${TABLE}.first_outbound_attempt_id
  - dimension: first_marketing_sales_call_id
    type: int
    hidden: true
    description: 'customer_service_event ID for first time Master <% parameter partner_name %>er called back and got someone to sign up'
    sql: ${TABLE}.first_marketing_sales_call_id
  - dimension: first_bad_phone_id
    type: int
    hidden: true
    description: customer_service_event ID for first time we attempted to call someone but phone number was incorrect/invalid
    sql: ${TABLE}.first_bad_phone_id
  - dimension: first_proactive_sales_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first time sales sends proactive email as follow up to unscheduled call attempt
    sql: ${TABLE}.first_proactive_sales_email_id
  - dimension: first_success_proactive_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success sends proactive email
    sql: ${TABLE}.first_success_proactive_email_id
  - dimension: first_success_email_response_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success responds to an email
    sql: ${TABLE}.first_success_email_response_id
  - dimension: first_success_phone_id
    type: int
    hidden: true
    description: customer_service_event ID for first success call
    sql: ${TABLE}.first_success_phone_id
  - dimension: first_success_opt_out_id
    type: int
    hidden: true
    description: customer_service_event ID for first time account opts out of the success process
    sql: ${TABLE}.first_success_opt_out_id
  - dimension: first_success_call_scheduled_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success call scheduled
    sql: ${TABLE}.first_success_call_scheduled_id
  - dimension: first_success_on_boarding_call_interest_id
    type: int
    hidden: true
    description: customer_service_event ID for first time customer indicates interest in a success call
    sql: ${TABLE}.first_success_on_boarding_call_interest_id
  - dimension: first_success_pre_call_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success sends pre call preparatory email
    sql: ${TABLE}.first_success_pre_call_email_id
  - dimension: first_success_post_call_follow_up_email_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success sends proactive email
    sql: ${TABLE}.first_success_post_call_follow_up_email_id
  - dimension: first_changed_group_to_dev_id
    type: int
    hidden: true
    description: customer_service_event ID for first time desk case group changed to dev (old pre-JIRA bug procedure)
    sql: ${TABLE}.first_changed_group_to_dev_id
  - dimension: first_group_dev_case_updated_id
    type: int
    hidden: true
    description: customer_service_event ID for first time desk case assigned to dev updated (old pre-JIRA bug procedure)
    sql: ${TABLE}.first_group_dev_case_updated_id
  - dimension: first_changed_group_from_dev_id
    type: int
    hidden: true
    description: customer_service_event ID for first time desk case changed from dev to different group (old pre-JIRA bug procedure)
    sql: ${TABLE}.first_changed_group_from_dev_id
  - dimension: first_group_dev_case_resolved_id
    type: int
    hidden: true
    description: customer_service_event ID for first time desk case assigned to dev resolved (old pre-JIRA bug procedure)
    sql: ${TABLE}.first_group_dev_case_resolved_id
  - dimension: first_success_proactive_call_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success proactively calls account to schedule a success call
    sql: ${TABLE}.first_success_proactive_call_id
  - dimension: first_stop_sales_contact_id
    type: int
    hidden: true
    description: customer_service_event ID for first time account requests to have sales stop contacting them
    sql: ${TABLE}.first_stop_sales_contact_id
  - dimension: first_success_on_boarding_no_show_id
    type: int
    hidden: true
    description: customer_service_event ID for first time account does not show up to their success call (same day)
    sql: ${TABLE}.first_success_on_boarding_no_show_id
  - dimension: first_success_on_boarding_cancel_id
    type: int
    hidden: true
    description: customer_service_event ID for first time account cancels their success call (one day in advance)
    sql: ${TABLE}.first_success_on_boarding_cancel_id
  - dimension: first_gave_a_review_id
    type: int
    hidden: true
    description: 'customer_service_event ID for first time account gave <% parameter partner_name %> a review'
    sql: ${TABLE}.first_gave_a_review_id
  - dimension: first_cancel_request_id
    type: int
    hidden: true
    description: 'customer_service_event ID for first time account requested to cancel their <% parameter partner_name %> account'
    sql: ${TABLE}.first_cancel_request_id
  - dimension: first_save_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first time we attempt to save an account from cancelling
    sql: ${TABLE}.first_save_attempt_id
  - dimension: first_save_success_id
    type: int
    hidden: true
    description: customer_service_event ID for first time we successfully save an account from cancelling
    sql: ${TABLE}.first_save_success_id
  - dimension: first_success_proactive_health_call_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success successfully calls an account that had a low health score (Preact)
    sql: ${TABLE}.first_success_proactive_health_call_id
  - dimension: first_success_follow_up_call_id
    type: int
    hidden: true
    description: customer_service_event ID for first itme success successfully proactively calls an account they had a success call with previously as a follow up
    sql: ${TABLE}.first_success_follow_up_call_id
  - dimension: first_success_proactive_health_call_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success attempts to call an account that had a low health score (Preact)
    sql: ${TABLE}.first_success_proactive_health_call_attempt_id
  - dimension: first_success_follow_up_call_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first time success attempts to proactively call an account they had a success call with previously as a follow up
    sql: ${TABLE}.first_success_follow_up_call_attempt_id
  - dimension: first_custom_report_request_id
    type: int
    hidden: true
    description: customer_service_event ID for first custom report request
    sql: ${TABLE}.first_custom_report_request_id
  - dimension: first_trial_date_extended_id
    type: int
    hidden: true
    description: customer_service_event ID for first time trial date extended
    sql: ${TABLE}.first_trial_date_extended_id
  - dimension: first_support_proactive_phone_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first time support attempts to proactively call an account to check in
    sql: ${TABLE}.first_support_proactive_phone_attempt_id
  - dimension: first_support_proactive_phone_success_id
    type: int
    hidden: true
    description: customer_service_event ID for first time support successfully proactively calls an account to check in
    sql: ${TABLE}.first_support_proactive_phone_success_id
  - dimension: first_support_phone_callback_attempt_id
    type: int
    hidden: true
    description: customer_service_event ID for first time support attempts to call back a customer that put in a support phone request
    sql: ${TABLE}.first_support_phone_callback_attempt_id
  - dimension: first_lead_confirm_sql_id
    type: int
    hidden: true
    description: customer_service_event ID for first time a lead confirmed as sales ready (SQL)
    sql: ${TABLE}.first_lead_confirm_sql_id
  - dimension: first_lead_confirm_mql_id
    type: int
    hidden: true
    description: customer_service_event ID for first time a lead confirmed as Not sales ready, but not disqualified (MQL)
    sql: ${TABLE}.first_lead_confirm_mql_id
  - dimension: first_lead_confirm_disqual_id
    type: int
    hidden: true
    description: customer_service_event ID for first time a lead disqualified from the sales process
    sql: ${TABLE}.first_lead_confirm_disqual_id
  - dimension: last_funnel_stage_change
    type: int
    hidden: true
    description: customer_service_event ID for last time a lead confirmed as sales ready, not sales ready, or disqualified
    sql: ${TABLE}.last_funnel_stage_change