Create a weekly update from a spreadsheet with AI, and check the numbers

Part 1: Clean the data

Step 1. Import the data

In a new Google Sheet, choose File → Import → Upload and pick harbour-weekly-data-raw.csv. Choose "Replace current sheet". First set File → Settings → Locale to Ireland so the day/month dates read correctly. Rename the tab Data.

Each row is one enquiry: Date, Enquiry ID, Source, Service, Status (Won, Lost, Quoted or Open), Quote value, Won value, Response hours (how long until the first reply) and Owner. Harbour Home Services Ltd is made up, and so is every row.

Step 2. Decide your measures before you look

Pick the handful of numbers you'd actually act on, and write down exactly what each one means. Ours:

• Enquiries: rows with a date in the week (Monday to Sunday).
• Won: rows in the week with Status = Won.
• Won value: total of the Won value column for the week.
• Average first response: average of Response hours for the week.
• Enquiries by source: count per source.

Deciding this first stops you, or the AI, from hunting for whichever number looks best.

Step 3. Find and fix the three problems

Real exports are messy, so our sample is too. Three problems are hidden in the week starting 21 September:

• A duplicate. Sort by Enquiry ID and look for the same ID twice: H-2123 appears twice. Delete one copy. (Data → Data cleanup → Remove duplicates also finds exact copies, but sorting shows you what you're deleting.)
• Money stored as text. H-2127 has a Won value of €8,960 sitting on the left of its cell. That's text, and formulas will skip it. Retype it as 8960.
• A blank status. H-2128 has no Status. In real life you'd ask whoever owns it. Here the owner says it was Quoted, so type Quoted.

You should now have 146 data rows (147 rows under the header before you deleted the duplicate).

Part 2: Build the summary table

Step 4. Add a "Week starting" column

In the Data tab, type Week starting in J1, then in J2 paste:

=A2-WEEKDAY(A2,2)+1

Copy it down to the last row and format column J as a date. Every row now shows the Monday of its week, which makes weekly sums simple.

Step 5. Set up the Summary tab

Add a new tab called Summary. Type This week starts in A4 and the date 21/09/2026 in B4. In A5 type Last week starts and in B5 put =B4-7. Next week, you only change B4.

Step 6. Paste the formulas

In row 7 type the headings Measure, This week, Last week and Change. Put the measure names in column A from row 8, and these formulas in column B ("This week"). For column C ("Last week"), use the same formulas with $B$5 instead of $B$4. Column D is =B8-C8 and so on.

Add one "Enquiries from…" row for each source: Website form, Phone, Referral, Google Business Profile and Facebook. "Rows with no status" is a built-in alarm: it should always be 0.

Step 7. Check two figures by hand

Never trust a formula you haven't checked once. In the Data tab, turn on a filter (Data → Create a filter) and filter column J to 21/09/2026 and column E to Won. Count the rows: there should be 10. Select the Won value cells for those rows; the sum at the bottom-right of the screen should say 10,990.

Your summary should now match ours:

If yours differs, compare with weekly-report-workbook-finished.xlsx in the pack. The usual cause is one of the three problems in step 3 still being there.

Part 3: Let the AI write the update

Step 8. Copy the table into the prompt

Select the Summary table (headings and numbers, A7 to D18), copy it, and paste it into a new AI chat with this prompt (also in the pack as report-prompt.txt):

Below is a table of this week's and last week's figures for a small business. Write a short weekly update for the owner, under 200 words.

Rules:
1. Use only the numbers in the table. Don't work out anything new except percentage changes, and show the sum for each one (for example: 5 ÷ 21 = 23.8%).
2. Use three headings:
   What changed: facts from the table only.
   Possible reasons: clearly labelled as guesses to check, never written as facts.
   Questions to check before acting.
3. Don't recommend spending money, hiring or changing prices.
4. If a number looks odd (a very big jump, or something that should be zero), say so under Questions.
5. End with a line headed NUMBERS USED that lists every number in your update.

TABLE (copied from the spreadsheet):
<<<
[paste the Summary table here]
>>>

Step 9. Read the draft like an auditor

Here's the draft from our test run with the cleaned table:

What changed
- Enquiries rose from 21 to 26 (+5; 5 ÷ 21 = 23.8%).
- Jobs won rose from 6 to 10.
- Won value rose from €910 to €10,990.
- Quoted value across all enquiries rose from €3,390 to €41,570.
- Average first response slowed from 18.1 to 20.0 hours.
- Facebook brought 6 enquiries, up from 0. Website form enquiries fell from 10 to 6.

Possible reasons (guesses to check)
- The jump in won and quoted value may come from a few large jobs, such as bathroom refits, rather than more small ones.
- The Facebook enquiries may be linked to a post or ad last week.

Questions to check before acting
- Which jobs make up the €10,990 won? Is one job most of it?
- Did anything run on Facebook last week?
- Is the slower response time linked to the higher number of enquiries?

NUMBERS USED: 21, 26, 5, 23.8%, 6, 10, €910, €10,990, €3,390, €41,570, 18.1, 20.0, 0, 6, 10, 6

Now check it:

• Every number is in the table. Tick them off against NUMBERS USED. They're all there, and 5 ÷ 21 is 23.8%.
• Reasons are guesses, not facts. They're labelled as guesses. Good.
• Nothing has been made up. Look closely: "bathroom refits" isn't in the table. The AI guessed it. It happens to be right in this sample data, but the AI had no way of knowing. Either check it against the Data tab or delete it. This is exactly the kind of line that slips through.

Step 10. Send it, with the period and date

Edit the draft, add a first line like "Week of 21 to 27 September 2026, prepared 29 September", and send or file it. Next week: paste in the new export, fix any problems, change B4, check two figures, and run the prompt again.

When it goes wrong

Where this stops, and what comes next

This works well for a weekly export of up to a few thousand rows. If the export itself is the painful part (several systems, copying by hand, or numbers that never quite agree), connecting the sources so the summary fills itself is a bigger job.

• Next tutorial. Turn customer enquiries into a simple follow-up workflow.
• Practise live, free. See the free classes.
• Want the reporting connected and automated? That's optional paid work. Business help.