A Spreadsheet of Your Last Three Years

The record that outlasts the tools

Every measurement tool you use will eventually be replaced, discontinued, or changed in a way that breaks the comparison.

A spreadsheet you maintain will not, because nobody else has a say in it.

It is also the only place your website figures and your business figures sit next to each other, which is where the useful questions live.

Thirty-six rows is three years, which is enough to see everything seasonal and most things structural.

The columns worth having

• Month.
• Visits.
• Arrivals from search, unbranded.
• Enquiries.
• Quotes sent.
• Jobs won.
• Revenue, if you want it here.
• What happened.

The last column is the one that makes the rest readable. A figure with no explanation is a number; the same figure with a note saying the van was off the road for three weeks is information.

Backfill three years in one sitting

Which is more achievable than it sounds.

Visits and search arrivals come from the exports you are making this month.

Enquiries and quotes come from your email, your form records, or your quoting system.

Jobs won and revenue come from your accounts.

None of it requires anything you do not already hold, and two or three hours produces the whole table.

Where a figure genuinely is not recoverable, leave the cell empty rather than estimating, and start recording it from now.

A worked example

A business built this from their exports and their accounts, covering forty months.

Three things became visible immediately that nobody had known.

Their quiet period was six weeks earlier than everybody believed, which changed when they ran their seasonal offer.

Their quote-to-job rate had fallen steadily for two years, which nobody had noticed because the job count was flat while enquiries rose.

And a marketing spend from the previous year had produced nothing detectable in any column.

None of those questions could have been asked in a dashboard, because the columns had never been next to each other.

The ratios are the point

Which is what a single-source dashboard cannot give you.

Enquiries per hundred visits tells you whether the site is working.

Quotes per enquiry tells you whether you are qualifying properly.

Jobs per quote tells you whether you are competitive and whether your quoting is any good.

Each of those can move independently, and a business watching only visits or only revenue cannot see which of the three changed.

Add them as three more columns computed from the others, which the spreadsheet does for you.

Fill it in on the same day each month

Since the whole thing depends on the habit.

Pick a day, the first working Monday or whenever you do the invoicing, and add one row.

It takes about ten minutes with the numbers to hand, which is why attaching it to something existing works and a separate task does not.

A missed month is not a disaster, and three missed months is the beginning of it being abandoned.

The value is entirely in the length of the series rather than in any single row.

Write the note even when nothing happened

Because the empty months are context too.

Nothing unusual is a legitimate entry and it tells you the figures that month are the baseline.

Where something did happen, write it plainly: raised prices, changed the site, lost a member of staff, ran an advertisement, had two weeks of bad weather.

In two years those notes are the difference between a chart and an explanation.

They also stop the recurring argument about when something changed, which is otherwise settled by whoever remembers most confidently.

Keep it where the business keeps things

And in a plain format.

A spreadsheet file, in the business's own storage, not in a tool that could itself be discontinued.

Anybody should be able to open it in five years with no particular software.

Give somebody else access, since a record only one person can reach is not a business record.

And keep a second copy, for the same reason as any other irreplaceable file.

Read it once a year, properly

Since the point of building it is not the building.

Set an hour in January to read the whole table rather than the last row, looking for the shape rather than the movement.

Sort by each ratio to see which months were unusual, and read the notes beside them to find out why.

That hour produces two or three conclusions about how the business actually works, which is more than any monthly review ever does.

Write those conclusions at the bottom of the sheet with the date, so next year you can see whether you were right.

It also answers the questions other people ask

A benefit that appears at unexpected moments.

An accountant, a lender, an insurer, or a prospective buyer of the business all ask questions this table answers directly and no dashboard does.

Three years of enquiries, quotes, and conversion rates is evidence about how the business actually works rather than an assertion about it.

That has value well beyond marketing, and it is the same two hours.

It is also the document that makes a conversation with a new supplier productive, since you can show what has actually happened rather than describing it.

The counter-case

It requires discipline nobody enjoys.

Ten minutes a month is trivial and it is ten minutes that nothing forces you to spend, which is why most attempts are abandoned by March.

Very small businesses also have figures too low for monthly ratios to be stable, and quarterly would suit them better.

And a business already keeping good records in its accounting system may be duplicating.

Build the three years from what you have this month, add one row monthly, and always write the note.

The two hours

1. One row per month, three years.
2. Visits and search arrivals from the export.
3. Enquiries and quotes from your records.
4. Jobs and revenue from accounts.
5. Add the three ratios.
6. Write what happened each month.
7. Add a row on the same day monthly.

Step five is where the answers come from, since a business can have rising visits, flat jobs, and a falling quote conversion all at once and see none of it in any single figure.

The annual version of this reading is covered in a year of analytics read once.

Frequently asked questions

West Coast Media Solutions Inc. provides web design, web development, hosting, digital marketing, and business consulting to organisations across Canada, drawing on more than twenty-five years in the field.