Customer contact audit: track requests for seven days

How to use it

1. First delete all the example rows. They are fictional (a made-up garage) and would otherwise be counted in the Summary tab.
2. Before you start, agree on a target time for the first response for each channel, for example “WhatsApp within 1 hour during business hours.” That is your own choice, not a standard. Write the target times down, so that everyone judges “late” in the same way.
3. For seven days in a row, make one row per contact: every call (including missed ones), every new WhatsApp chat or WhatsApp call, every email or website question and every walk-in with a question. Enter the date as MM/DD/YYYY and the time like 7:42 AM.
4. Under Contact type, choose “New request” (first contact about a new question), “Existing customer” (first contact about a job, order or appointment already under way), “Repeat message” (same person, same question, not yet resolved) or “Spam/irrelevant” (advertising, wrong number, private).
5. Fill in Status, First response, Resolved, Owner, Next action, Follow-up date and Outcome only on the first row of a request. Leave them empty for repeat messages and spam, so that each request counts once.
6. First response (minutes) is the time from the first contact to the first real response from a staff member. Picked up right away counts as 0. An automatic message or a callback attempt that nobody answers does not count. If there was never a response, leave the field empty and choose “Unanswered” as the status.
7. Keep the Subject general, for example “price minor service” or “reschedule appointment.” Do not note names, phone numbers, license plates, addresses, health or payment details.
8. Update the log at a fixed time every day, with the list of missed calls, the unread WhatsApp chats and the inbox alongside. If a response comes later, still fill it in on that request’s row.
9. After seven days, update the open rows and read the Summary tab. There, only rows with type “New request” or “Existing customer” count as a unique request; all rows except spam together are your contact attempts.
10. Use the results in Lesson 1 for the opportunity estimate and the worksheet with your top 3 communication bottlenecks.

What the spreadsheet calculates for you

• Unique requests: rows with contact type New request or Existing customer (repeat messages and spam are not counted)
• Answered on time, answered late and unanswered
• Response rate and the share answered within your target time
• Average time to the first response, in minutes
• Requests outside business hours and fully resolved requests
• Open follow-up and the percentage of follow-up completed
• Unique requests per channel

The formulas are in the file itself; click a cell to see them. In Google Sheets, open the file with File → Import or by dragging it into Google Drive.

The columns

Example data (fictional)

The file contains a few made-up example rows, marked in gray. They show what a completed row looks like. Delete them before you start.

Privacy and retention

Collect only what you need for the audit: no names, phone numbers, license plates, addresses, health or payment details. You can recognize repeat callers in your phone’s call log or in the WhatsApp chat itself; the number does not need to be in the file. Keep the file in an account with a strong password (preferably with two-step verification) and share it only with the colleagues who are recording, never via a public link. After the analysis, keep only the totals from the Summary and delete the detail rows, no later than a month after the measurement week. It is best to check which rules apply in Suriname to storing customer data; this audit is set up so that you do not need to collect any personal data.