Hi, Carsten here.
I’m trying to replicate the “median response time” under the responsiveness reporting page in the Intercom UI, but I struggle a bit to make it accurate, where sometimes there is 10+ minutes discrepancy. So I would hear if someone in this community forum can help a bit?
What I can understand from reading other posts in this forum with a similar issue is the statistics_median_time_to_reply metric should be the only one I need to replicate the same number I see in the Intercom reporting, so I’m not sure why it seems difficult to get to match. The other posts doesn’t seem to post the solution to their issues either though.
The calculations I’m doing at moment is:
- Get the newest version of a conversation id (qualify
row_number() over (partition by id order by updated_at desc) = 1) - Then convert created_at to date and calculate the median on the median_time_to_reply.
date_trunc(day, created_at)::date as day,
median(statistics_median_time_to_reply) as median_response_time
Hope someone can help me understand the calculations behind the median response time in the Intercom UI, and point me in the right direction.
Thank you in advance! Looking forward to hearing back from you.
Kind regards.