Turn customer enquiries into a simple follow-up workflow

Part 1: Build the tracker

You can import our file (File → Import → Upload in Google Sheets) and skip to step 6 to check it. Building it yourself takes about 20 minutes, and it's worth doing once so you understand every column.

Step 1. Decide what the tracker has to answer

Before you make any columns, write down the questions you want answered every week. Ours are:

• Which enquiries are waiting on us, and who owns each one?
• Which follow-ups are overdue?
• Which enquiries have nobody looking after them?

Every column below exists to answer one of those. If a column doesn't help, leave it out.

Step 2. Create the columns and freeze the header

In a new Google Sheet, type these headings in row 1, columns A to N:

Then View → Freeze → 1 row, so the headings stay on screen when you scroll.

Step 3. Turn Status, Owner, Service and Source into dropdowns

Select column I (click the letter I), then Insert → Dropdown. Add the options New, Waiting on us, Waiting on customer, Won and Closed. Under Advanced options, choose to reject input that isn't on the list. Do the same for Owner (your team's first names), Service and Source.

Dropdowns matter more than they look. Without them you end up with "Open", "open" and "ongoing" meaning the same thing, and your filters quietly miss rows.

Step 4. Make sure dates are real dates

Go to File → Settings and set Locale to Ireland, so 02/10/2026 means 2 October, not 10 February. Then select columns B and K and choose Format → Number → Date.

To check, type a date and look at where it sits in the cell. Real dates line up on the right. If it sits on the left, the spreadsheet is treating it as text and the overdue check won't work.

Step 5. Add the "Needs attention" formula

Click cell N2 and paste this formula exactly:

=IF(A2="","",IF(OR(I2="Won",I2="Closed"),"",IF(H2="","No owner",IF(K2="","No follow-up date",IF(K2<TODAY(),"Overdue","")))))

In plain English: ignore empty rows; ignore anything Won or Closed; otherwise say No owner if nobody owns it, No follow-up date if there's no date, and Overdue if the date has passed. Copy N2 down the column (select N2, then drag the small square at its bottom-right corner down a few hundred rows).

To make those rows stand out: select A2:N, choose Format → Conditional formatting, pick "Custom formula is" and enter =$N2="Overdue" with a red fill. Add a second rule, =OR($N2="No owner",$N2="No follow-up date"), with an amber fill. Colour is only a helper: the words in column N carry the meaning, which matters for anyone who doesn't see colour well.

Step 6. Create a "Needs attention" filter view

Choose Data → Create filter view (in some versions it's Data → Filter views → Create new filter view). Name it Needs attention. Click the filter icon on column N and untick "(Blanks)", so only flagged rows show.

A filter view only changes what you see. A normal filter changes the sheet for everyone who has it open, which confuses colleagues. That's why we use a filter view.

Step 7. Check it works (with our sample data)

Enter or import the 12 sample rows. Because the formula uses today's date, your results depend on when you do this. To reproduce our exact check, temporarily replace TODAY() in N2 with DATE(2026,10,9) and copy it down again. You should see exactly four rows flagged:

E-1012 is due on 09/10/2026 itself, so it's due today rather than overdue, and isn't flagged. Then put TODAY() back.

One thing the formula can't catch: E-1006 and E-1007 are the same person asking about the same tap, once by web form and once by phone. Sort by the Email column now and then to spot these, and close one of the pair with a note.

Part 2: Draft replies with AI, from your own rules

Most "AI email reply" advice is a list of prompts. The trouble is that a prompt on its own lets the AI fill gaps with confident guesses: a same-day visit, a discount, a price you don't charge. So we write the rules first and make the AI stick to them.

Step 8. Write a one-page reply playbook

Open reply-playbook-template.md from the pack (any text editor, or paste it into a Google Doc). Fill in four short lists:

• We may say: services, area, hours, prices you're happy to put in writing, your reply-time promise.
• Must not promise: same-day visits, arrival times, discounts, refunds, guarantees.
• Ask if missing: the facts you always need, like an Eircode, a boiler type or photos.
• Escalate: complaints, refunds, safety issues and big contracts. A person replies to these, not the AI.

Our filled-in example for Harbour Home Services is in the pack (reply-playbook-harbour-example.md). Use it to practise, then write yours.

Step 9. Set up your AI tool safely

• Use an account your business is happy to use for work. Free personal accounts are fine for practising with our made-up data.
• In ChatGPT on a personal account, you can turn off "Improve the model for everyone" under Settings → Data controls, and use a Temporary Chat for one-off drafts. Business plans from the main providers say they don't train on your data by default. Check your own tool's settings.
• Only paste what the AI needs: the playbook and one enquiry. Remove phone numbers, account numbers and other people's details first.

Step 10. Paste the prompt, the playbook and one enquiry

Here's the prompt (also in the pack as reply-prompt.txt). Paste it into a new chat, then paste your playbook and one enquiry where shown.

You are helping a small business draft a reply to a customer enquiry. A person will check and send it; you are only drafting.

Rules:
1. Use ONLY facts from the PLAYBOOK. If the customer asks something the playbook doesn't answer, don't guess: say we'll check and come back to them, and list it under CHECK BEFORE SENDING.
2. Never promise anything listed under "Must not promise".
3. If anything under "Ask if missing" is missing from the enquiry, ask for it. No more than 3 questions.
4. If the enquiry matches anything under "Escalate", write only a short, kind acknowledgement saying the owner will be in touch, and start your answer with the word ESCALATE.
5. Plain, friendly Irish English. Under 120 words. No emojis. Sign off as: [Your name], [Business name].
6. After the reply, add a section headed CHECK BEFORE SENDING. List every price, time, place or promise in your draft next to the playbook line it came from, and anything you were unsure about.
7. Finally, add one line headed TRACKER in this format: Status | Next action | Follow up in N working days. Status must be one of: New, Waiting on us, Waiting on customer.

PLAYBOOK:
<<<
[paste your playbook here]
>>>

ENQUIRY:
<<<
[paste ONE enquiry here]
>>>

One enquiry per chat, or start a new chat each time. Otherwise details from an earlier customer can leak into a later reply.

Step 11. Check the draft against the playbook

Here are three drafts from our own test run with the Harbour playbook. Your tool's wording will differ; what matters is whether it follows the rules.

E-1001, a simple price question ("How much is a boiler service? It's a gas combi… We're in Rathfarnham.")

Hi Niamh,

Thanks for getting in touch. A gas boiler service is €120 including VAT, and Rathfarnham is within our area.

If you'd like to book, just reply with your Eircode and a couple of days that suit you, and we'll come back to you with available times.

[Your name], Harbour Home Services

CHECK BEFORE SENDING
- €120 including VAT: "Gas boiler service: €120 including VAT."
- Rathfarnham in our area: "Area: Dublin city and county, plus Bray."
- No visit date or time promised.

TRACKER: Waiting on customer | Wait for Eircode and preferred days | Follow up in 3 working days

Passes the checklist: every fact traces to a playbook line, and nothing is promised.

E-1012, an urgent leak ("There's water coming through our kitchen ceiling!! Can someone come TODAY?")

Hi Emma,

I'm sorry to hear about the leak. If it's safe to do so, turn off the water at the stopcock and keep away from light switches and electrics near the leak.

We can't promise a visit today, but we'll check what's available and come back to you as soon as we can. Could you send your Eircode, and let us know if the water is near any lights or sockets?

Our plumbing call-out is €95 including VAT, which covers the first hour. Parts are extra.

[Your name], Harbour Home Services

CHECK BEFORE SENDING
- No same-day visit promised: "Must not promise: a same-day visit".
- Stopcock advice: the playbook's water-through-a-ceiling line.
- €95 call-out: "Plumbing call-out: €95 including VAT, covers the first hour. Parts are extra."
- Unsure: this is urgent. Consider phoning her instead of emailing.

TRACKER: Waiting on us | Check today's diary and phone back | Follow up in 0 working days

Passes, and the CHECK section did its job: it flagged that a phone call might be better than an email. That's a human decision.

E-1009, a complaint ("…the boiler is making the same noise again. I'm not happy paying twice.")

ESCALATE

Hi Mark,

Thanks for letting us know, and I'm sorry the boiler is still making that noise. I've passed your message to Siobhán, who looks after this, and she'll be in touch within one working day.

[Your name], Harbour Home Services

CHECK BEFORE SENDING
- No refund or free visit offered: "Must not promise: refunds or free work".
- "Within one working day": "We reply to every enquiry within one working day."

TRACKER: Waiting on us | Siobhán to call Mark | Follow up in 1 working day

Correct: complaints are escalated, so the draft only acknowledges.

Try one yourself: run E-1011 ("How much to service an oil boiler? We're in Naas."). A correct draft says Naas is outside the area, politely, and doesn't quote a price for a job Harbour won't do. If yours quotes €140 and offers to book, it hasn't followed the area rule: reject it.

Step 12. Run the checklist, then send it yourself

• It's addressed to the right person, with no details from another customer.
• Every price matches the playbook exactly, including "including VAT" or "parts extra".
• It doesn't promise a time, date, discount, refund or free work.
• It answers the question actually asked, and asks for anything missing.
• Complaints, refunds, safety issues and big contracts are acknowledged only, with a person following up.
• Everything under CHECK BEFORE SENDING has been checked or removed.
• The sign-off is yours.

Copy the reply into your own email (delete the CHECK and TRACKER sections first), and send it from your normal inbox.

Step 13. Update the tracker

Use the TRACKER line to fill in Status, Next action and Follow-up date for that enquiry. This closes the loop: the reply goes out, and the tracker knows when to look at it again.

The five-minute weekly review

1. Open the Needs attention filter view.
2. Give every "No owner" row an owner.
3. Give every "No follow-up date" row a date.
4. Deal with or reschedule each "Overdue" row.
5. Sort by email to spot duplicates, and close anything that's finished as Won or Closed.

When it goes wrong

Where this stops, and what comes next

You can run this by hand for as long as it works. Signs you've outgrown it: you're copying the same enquiry in from several places, two people keep overwriting each other, you need a history of who said what, or the weekly review takes too long. The pack's manual-first-checklist.md goes through this.

• Practise it live, free. The free class builds this exact workflow with made-up data.
• Next tutorial. Create a weekly update from a spreadsheet, and check the numbers.
• Want it connected to your real inbox and forms? That's paid work, and optional. You don't need to have done the class. Business help.