[Feature] Add new Intercom fields for real-time performance tracking based on conversation type and rating

[fivetran-avinash]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Several metrics are useful for assessing performance more rapidly in Intercom and can be mostly identified via case statements and aggregations.

Live conversation metrics

Intercom has four states of a conversation: Open, idle, snoozed, closed. Making it easier to track the former three conversation types will allow for better live tracking of Intercom data for end customers.

• Open and snoozed tickets can be derived in conversations_metrics model by conversation_state based on Intercom API, but aggregates need to be calculated upstream for total_conversations_open, total_conversations_snoozed in admin_metrics and company_metrics.
• For idle, is_idle boolean can be created in conversation_metrics based on lack of first_admin_response_at value. Idle conversations can be derived by null values in first_admin_response_at. total_conversations_idle can then be calculated upstream in admin_metrics and company_metrics.
• Stretch:total_conversations_participated gives us a potential true denominator for each of these metrics--we could examine the conversation parts model. This should be fairly doable for company_metrics, but might need some creativity for admin_metrics as we'll need to break down which teammates participated in which conversations. We might need to dis-aggregate all_conversation_admins to associate conversations with admins.

Conversation ratings

Intercom has the satisfaction rating to assess CSAT for most customers, looking at the % of conversations that have received a 4 or 5 star rating (a 😀 or 🤩 review from a customer after an Intercom conversation has concluded).

An is_satisfied boolean in conversation_metrics would be the easiest solve, and then upstream satisfaction_ratings to count those totals in company_metrics and admin_metrics`.

Describe alternatives you've considered

Most of these metrics can probably be derived in the current models, but would require a ton of filtering on the dashboards themselves to generate. This provides simpler model metrics from which to perform aggregate calculations.

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response