Hey there,

We’re trying to create Intercom reporting within Looker.

I have a few questions and was wondering if anybody else already tried to do this.

Median Response Time

- The conversation_history.statistics_median_response_time is a median already, but when aggregating this as a measure within Looker (to group per assignee_id or team_name), you’d get the average median response time or median median response time. These numbers could differ from the regular median response time within Intercom. Anybody who could think of a solution for this?
 

- Anybody knows which timestamp is used within Intercom report filters for Median Response Time?
Is Intercom’s timestamp the created_at, statistics_last_admin_reply or something else?

Volume metric
- We’re also looking into a volume metric where we want to understand how many NEW answered conversations were assigned to a specific person in a specific week. The issue here is that conversation ID’s are not necessarily unique within conversation_history. Which would lead to duplicate counts. The issue here as well is that, if you’d want to understand which conversation_history.assignee_ID corresponds with which admin.name, numerous admins can be part of 1 conversation.

→ We’re currently building a table where we get unique conversations based on the latest conversation_history.updated_at timestamp. Hope this would resolve this issue.

Hope it’s clear and if anybody has any ideas, let me know!