WhatsApp first response time in Google Sheets: the formulas
Work out how long customers wait for a first WhatsApp reply from message events in Google Sheets: MINIFS formulas, out-of-order events and a sample.
- Written by
- By WavesFlo team
- Published
- Published
- Reading time
- 3 min read
WhatsApp does not report reply times, so the number has to be built. Log every message received and sent into a sheet with its own timestamp, then for each chat take the first incoming message and the first outgoing one after it. The gap is the first response time. Three MINIFS formulas do it.
| Column | Holds | Comes from |
|---|---|---|
| A Event ID | A value that identifies the event | The event's identifier, which stays the same on every retry |
| B Time | A real date-time, not text | The event's own timestamp, converted to your timezone |
| C Chat | One value per conversation | The chat id the event carries; for a direct chat, the customer |
| D Direction | in or out | in for message.received, out for message.sent, set in the workflow |
| E Number | Which of your numbers | The id of the connected number the message arrived on or left from |
| F Type | text, image, voice note and so on | The message type the event carries |
How do you set up the Chats tab?
- On a new tab called Chats, put =UNIQUE(Messages!C2:C) in A2 so every chat appears once.
- In B2 (first message in): =IF(A2="","",MINIFS(Messages!$B:$B,Messages!$C:$C,A2,Messages!$D:$D,"in"))
- In C2 (first reply after it): =IF(B2="","",IF(COUNTIFS(Messages!$C:$C,A2,Messages!$D:$D,"out",Messages!$B:$B,">"&B2)=0,"",MINIFS(Messages!$B:$B,Messages!$C:$C,A2,Messages!$D:$D,"out",Messages!$B:$B,">"&B2)))
- In D2 (minutes waited): =IF(C2="","",ROUND((C2-B2)*1440,1))
- Fill B2:D2 down the column, or wrap each in ARRAYFORMULA so it grows with the list.
The COUNTIFS guard in C2 matters: MINIFS returns 0 when nothing matches, which would read as a reply at midnight on 30 December 1899. Keep the sheet's timezone (File, Settings) the same as the one the workflow converts to, or the formulas will compare times that are hours apart.
How do you summarise it?
| Figure | Formula |
|---|---|
| Median first response, minutes | =MEDIAN(D2:D) |
| 90th percentile | =PERCENTILE(D2:D,0.9) |
| Chats with no reply yet | =COUNTIFS(B2:B,"<>",C2:C,"") |
| Replied within 30 minutes | =COUNTIF(D2:D,"<=30")/COUNT(D2:D) |
Report the median and the 90th percentile, not the average: one chat that waited a weekend drags an average far from what most customers felt. Change the 30 to whatever target you set yourself.
What can make the figure wrong?
- Events arrive at least once and can arrive out of order. The formulas use each event's own timestamp, so order does not matter, but a duplicate row can; match on the event identifier when you write rows.
- A reply typed on the phone counts as a reply, because message.sent includes it. That is usually right: the customer was answered.
- A customer who writes again after being answered is not a new wait here, because the formulas look only at the first message in. To measure every wait, add a column for the most recent inbound before each reply.
- Outside working hours the clock keeps running. A message at 11 pm and a reply at 9 am reads as ten hours.
- Automated replies count as replies if they leave a connected number.
WavesFlo's own tickets measure first-reply and resolution times against targets you set, counted in your working hours, with the median and 90th percentile reported. Use the sheet for chats that are not tickets and as a cross-check; a target is a goal you choose, not a time anyone guarantees.
Both figures start from the same conversations, which reach WavesFlo from every number linked from its own phone. Tickets, targets and reports are described together on the features page.
Questions
How do I measure WhatsApp response time?
Log each message received and sent with its timestamp, then take the gap between a chat's first incoming message and the first outgoing one after it. MINIFS does this in Google Sheets.
Why does MINIFS return 1899?
MINIFS returns 0 when nothing matches, and 0 is a date in 1899. Count the matches first and return an empty cell when there are none.
Should I report the average or the median?
The median and the 90th percentile. A single chat that waited a weekend can make an average misleading.
Does WhatsApp report response time itself?
No. Its own tools show ticks and Message info per message, not reply times across chats.