A retention template is a small piece of structure that decides, months in advance, whether the number will be comparable to itself. The layout below is deliberately boring: three tabs, one row per event, dates stored as dates. Everything that makes retention reporting fail later is a shortcut taken now.
Three tabs, and no more
- A client tab, holding one row per person
- A visit tab, holding one row per appointment
- A summary tab, holding one row per month
Adding a tab for every question is how workbooks become unusable. Anything else you want to know should be a column or a filtered view of these three.
The client tab
| Column | Why it exists |
|---|---|
| client_id | A stable key that never changes, generated once |
| mobile | Normalised, one number per person, used to spot duplicates |
| display_name | What staff will recognise on a list |
| first_visit | The date the relationship started, which keys any cohort |
| source | How the client first arrived: phone, online, referral, walk-in |
| preferred_staff | Optional, useful when explaining a pattern |
| status | Active, inactive, or do-not-contact |
The only column that needs care at entry is mobile. Strip spaces and punctuation on the way in, and check for existing matches before adding a row.
The visit tab
| Column | Why it exists |
|---|---|
| appointment_id | One per booking, so a reschedule keeps its history |
| client_id | The link back to the client tab |
| visit_date | Stored as a date, not as text |
| service | The booked service, ideally a short fixed list |
| staff | Who delivered it |
| channel | How the booking was made |
| booked_next_at_visit | Yes or no, recorded at checkout |
| outcome | Completed, cancelled, no-show, rescheduled |
booked_next_at_visit is the column most salons omit and later wish they had. It separates a client who chose not to return from a client who meant to and never got round to it, and those two need different responses.
The summary tab
One row per month, with the same columns every time:
- Month
- Clients due
- Clients returned
- Rate, calculated rather than typed
- Booked next at visit, as a count
- Lapsed clients added to the list
- A short note on anything that changed operationally
The note column is not decoration. Without it, a rate that moved because the shop closed for a week reads as a change in client behaviour.
Formulas worth writing once
Set these up in the first row of the summary tab and copy the row down each month, rather than retyping arithmetic into a fresh sheet:
- Rate as returned divided by due, guarded against an empty denominator so the cell shows blank rather than an error
- Due as a count of clients whose last visit falls inside the window, computed from the visit tab rather than entered by hand
- Returned as a count of distinct clients with at least one completed visit inside the window
- Booked-next as a count of completed visits where the flag says yes
A formula that lives in one place can be corrected once. A number typed by hand has to be trusted.
Feeding it without retyping
Export from the booking system on a fixed day, keep the untouched file, and append its rows to the visit tab rather than starting a new workbook each month. Check the row count before and after the append: an export that silently dropped cancelled appointments is the failure mode that flatters the result.
Where the booking system cannot supply the channel or the booked-next flag, the front desk supplies them, and the phone is where the channel is decided. Elevoi records whether a booking came through a call, captures the caller's details, and sends the confirmation, so the column arrives populated rather than reconstructed from memory.
Once the columns exist, the retention rate benchmarks calculator explains the arithmetic they feed, and the retention rate benchmarks checklist covers the audit before the first run.