I have been using the Intercom API to try to recreate the regular metrics in Metabase. I have tried using this resource but haven’t found it to be particularly helpful.
For example, if I try this query, the response maps to the New Conversations Metric in the Intercom dashboard instead of the Assigned Conversations metric which is my intention.
SELECT
"Inbox Details - Team Assignee"."inbox_name" AS "Inbox Details - Team Assignee__inbox_name",
COUNT(*) AS "count"
FROM
"public"."conversations_data"
LEFT JOIN "public"."inbox_details" AS "Inbox Details - Team Assignee" ON "public"."conversations_data"."team_assignee_id" = "Inbox Details - Team Assignee"."inbox_id"
WHERE
(
(
"Inbox Details - Team Assignee"."inbox_name" = 'VIP Support Inbox'
)
OR (
"Inbox Details - Team Assignee"."inbox_name" = 'Support'
)
OR (
"Inbox Details - Team Assignee"."inbox_name" = 'VIP Escalation Inbox'
)
OR (
"Inbox Details - Team Assignee"."inbox_name" = 'Escalation Inbox'
)
)
AND (
"public"."conversations_data"."created_at" >= timestamp with time zone '2025-02-27 00:00:00.000 +00:00'
)
AND (
"public"."conversations_data"."created_at" < timestamp with time zone '2025-03-06 00:00:00.000 +00:00'
)
GROUP BY
"Inbox Details - Team Assignee"."inbox_name"
ORDER BY
"Inbox Details - Team Assignee"."inbox_name" ASC
I suspect that not getting the right Assigned Conversations metric is cascading to why my closed conversations, median response time, and others are also wrong.
It would be super helpful to get the calculations for regular conversation metrics based on the exact fields provided in the API response.
