WhatsApp message volume by day and number in Google Sheets
Count WhatsApp messages in and out per day and per number in Google Sheets with a QUERY formula, from a message log filled by webhooks.
- Written by
- By WavesFlo team
- Published
- Published
- Reading time
- 2 min read
A daily volume table answers whether the week was busy and which number carried it. With one row per message in a log, a helper column for the day and one QUERY formula produce it, and a pivot by number splits it further. It is a count of events, not of people: one customer writing ten times is ten.
| 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 add the day?
Add column G, Day: =ARRAYFORMULA(IF(B2:B="","",INT(B2:B))). Format the column as a date.
How do you build the daily table?
=QUERY(Messages!A:G,"select G, count(A) where G is not null group by G order by G desc label G 'Day', count(A) 'Messages'",1)To split by direction, use pivot D at the end of the query: group by G pivot D. To split by number, group by G, E instead.
| You want | Change the query to |
|---|---|
| Inbound only | add: where D = 'in' |
| One number | add: and E = 'YOUR_NUMBER_ID' |
| Per week | put =INT(B2)-WEEKDAY(B2,2)+1 in the helper column |
| Last 7 days | add: and G >= date '2026-10-26' using a cell reference built with TEXT(TODAY()-7,"yyyy-mm-dd") |
How do you show names instead of number ids?
The event carries the id of your connected number, not a friendly name. Keep a Numbers tab with the id, the phone number and the team, and add =VLOOKUP(E2,Numbers!A:C,3,FALSE) in a helper column.
What counts as a message?
- Every event the workflow writes: texts, photos, voice notes and documents each count once.
- Group messages count if you log them. Filter them out if the table is for customer chats.
- A retried delivery is one event with one identifier, so match on it when writing rows.
For campaigns, use the campaign results in WavesFlo, counted from delivery receipts.
Because each event names the number it moved on, the same table splits cleanly by store or branch once every number links into one workspace. Campaign results and reports are covered on the features page.
Questions
How do I count WhatsApp messages per day in Google Sheets?
Add a Day column with INT of the timestamp, then use a QUERY that groups by day and counts the event identifiers.
Can I split by number?
Yes. The event carries the id of the connected number; group by it, and map ids to names on a Numbers tab.
Does it count unique customers?
No, it counts messages. Count unique customers with COUNTA(UNIQUE(...)) over the chat column.