Skip to main content
Answered

Struggle to calculate the correct median response time via the API.

  • June 7, 2024
  • 1 reply
  • 75 views

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:

  1. Get the newest version of a conversation id (qualify
        row_number() over (partition by id order by updated_at desc) = 1)
  2. 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. 

Best answer by Ebenezer.Laleye

Hi @Datasystems Ageras Ebenezer here from Engineering Support👋.

I see you were able to get in touch with us via the Messenger and got an answer.

I’ll paste the answer here for others to see! 👇

If you want to get the true median for multiple conversations, you should not use the median_time_to_reply statistic. This statistic gives you the median value for that conversation, but if you use this figure with every conversation you are essentially getting the median of all the medians, not the median of all the replies in the conversations. When using an aggregation, you need to make sure that aggregation is applied across the correct value.

For example:

Here are three conversations, A, B & C.

Conversation A's reply times are (in seconds): [1, 2, 3, 4, 5]

Conversation B's reply times are (in seconds): [6, 7, 8, 9, 10]

Conversation C's reply times are (in seconds): [11, 12, 13]

 

The Median of A is [3] and the median of B is [8], and the median of C is [12].

 

The median of those medians is [8], as those 3 values are [3, 8, 12], but the median of all reply times is actually 7, as all reply times together looks like

[1, 2, 3, 4, 5, 6, (7), 8, 9, 10, 11, 12, 13].

 

In order to accurately calculate the Median response time, you would need to search for conversations within your designated time-frame, and grab the timestamp for each user and teammate response.

 

You would then need to calculate the time from the last user message, if there is a chunk of user messages uninterrupted by a teammate, to the first teammate response after that last message, and then do that for every user message followed by a teammate message.

 

For example:

  • User sends message 1
  • User sends message 2
  • User sends message 3
  • Teammate sends message 1
  • User sends message 4
  • Teammate sends message 2
  • Teammate sends message 3

In this case, Intercom measures the response time from User message 3 to Teammate message 1, and from User message 4 to Teammate message 2.

 

View original
Did this topic help you find an answer to your question?

1 reply

Forum|alt.badge.img+4

Hi @Datasystems Ageras Ebenezer here from Engineering Support👋.

I see you were able to get in touch with us via the Messenger and got an answer.

I’ll paste the answer here for others to see! 👇

If you want to get the true median for multiple conversations, you should not use the median_time_to_reply statistic. This statistic gives you the median value for that conversation, but if you use this figure with every conversation you are essentially getting the median of all the medians, not the median of all the replies in the conversations. When using an aggregation, you need to make sure that aggregation is applied across the correct value.

For example:

Here are three conversations, A, B & C.

Conversation A's reply times are (in seconds): [1, 2, 3, 4, 5]

Conversation B's reply times are (in seconds): [6, 7, 8, 9, 10]

Conversation C's reply times are (in seconds): [11, 12, 13]

 

The Median of A is [3] and the median of B is [8], and the median of C is [12].

 

The median of those medians is [8], as those 3 values are [3, 8, 12], but the median of all reply times is actually 7, as all reply times together looks like

[1, 2, 3, 4, 5, 6, (7), 8, 9, 10, 11, 12, 13].

 

In order to accurately calculate the Median response time, you would need to search for conversations within your designated time-frame, and grab the timestamp for each user and teammate response.

 

You would then need to calculate the time from the last user message, if there is a chunk of user messages uninterrupted by a teammate, to the first teammate response after that last message, and then do that for every user message followed by a teammate message.

 

For example:

  • User sends message 1
  • User sends message 2
  • User sends message 3
  • Teammate sends message 1
  • User sends message 4
  • Teammate sends message 2
  • Teammate sends message 3

In this case, Intercom measures the response time from User message 3 to Teammate message 1, and from User message 4 to Teammate message 2.

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings