Tabellab

The New-Patient Funnel: Enquiry Sources, Booking Rate and Show Rate

Lesson 1 of 20Lab

All lessons
  1. The New-Patient Funnel: Enquiry Sources, Booking Rate and Show RateLabFree
  2. Cost per New Patient: Cost per Enquiry, per Booking and per Attended Patient by SourceLab Locked
  3. Speed to First Reply: Booking Rate by Response Time, Index and Bookings ForgoneLab Locked
  4. Missed Calls and After-Hours Enquiries: Missed Rate, Rank and New Patients LostLab Locked
  5. Appointment Reminders and No-Shows: Show Rate by Method and Extra Visits to Reach a TargetLab Locked
  6. First-Year Value of a New Patient: Revenue, Contribution and the Highest Affordable Acquisition CostLab Locked
  7. Service-Line Mix: Share of New Patients, Total Contribution, Rank and Weighted AverageLab Locked
  8. Recall Reminders: Contact Coverage, Booking Rate, Recall Rate and Revenue from Recalled VisitsLab Locked
  9. Capacity and Empty Slots: Fill Rate, Spare-Capacity Weeks and Where Marketing Can HelpLab Locked
  10. Local Listing Performance: Views, Actions, Action Rate and Bookings per 100 Actions by SiteLab Locked
  11. Referral Partners: Change, Share, Attended Rate and the Concentration of the Top ThreeLab Locked
  12. Compliant Claims: A Review Register with Status, Risk Weights, Open Risk and Live Items on HoldLab Locked
  13. Privacy-Safe Reporting: Small-Cell Suppression, Combined Groups and Aggregate-Only TablesLab Locked
  14. Attribution: Tracked vs Self-Reported Sources, Agreement and Blended CreditLab Locked
  15. Waiting Time: Show Rate by Wait Band, Weighted Average Wait and Working Days with NETWORKDAYSLab Locked
  16. Trend and Forecast: Moving Average, Linear Trend, Seasonal Gaps and FORECAST.LINEARLab Locked
  17. Budget Allocation with Capacity Limits: Expected Patients, Unused Budget and Room to AddLab Locked
  18. Testing Changes: Pooled Rate, Standard Error, z Value, p-Value and the People Needed per GroupLab Locked
  19. Data Quality: Reconciling Marketing Counts with the Practice System and Finding Untracked PatientsLab Locked
  20. Final Project: A Quarterly Patient-Acquisition Scorecard with Rates, Cost, Return and StatusLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — €9.90
Loading the spreadsheet…

Why a clinic's marketing is a funnel problem

A clinic that wants more patients usually thinks first about visibility: a better website, more search ads, a nicer listing. But a new patient is not created when someone sees an advertisement. A new patient exists when a person has made an enquiry, booked a first visit and then turned up. Three steps, and every step loses people. If you only count the enquiries, you will celebrate a source that brings many enquiries and few patients. If you only count the patients, you will not know which step to fix.

This free lesson shows the basic tool of patient-acquisition marketing: a funnel with three counts per enquiry source and three ratios between them. The same logic works for a single-doctor practice, a physiotherapy group, a dental clinic or a multi-site medical centre, and it needs nothing more than a spreadsheet. You will build the ratios yourself in the lab above, with the same data as in this article.

The example company is Linnet Row Clinic Group, a fictional group of clinics. All clinics, sources and numbers in this lesson are invented for learning and are not benchmarks. They are also aggregate counts: you will not see a single patient name, date of birth or diagnosis, and you do not need one to do this work.

The three counts: enquiry, booked, attended

Before any formula, agree what each count means. Different definitions are the most common reason why two reports about the same month do not match.

  • Enquiry: a person asks about an appointment or a service, by phone, form, chat or message, and the clinic can tell where the contact came from. Count a person once per enquiry, not once per message.
  • Booked: the enquiry led to a first visit in the diary. A booking that was cancelled before the visit is not a booking for this report, or it is counted in a separate line, but the rule must be written down.
  • Attended: the person came to the first visit. This is the new patient. The count normally comes from the practice system, because it is the only place that knows who really arrived.
  • Source: the channel that brought the enquiry, for example search ads, organic search, the map listing, referrals from other professionals, patient recommendations, social ads, email or a printed directory.
Count first visits, not appointments. A patient who comes five times is still one new patient in the month of the first visit. Follow-up visits belong in the recall and value lessons, not in the acquisition funnel.

Where do the counts come from?

In most clinics the three counts live in different places. Enquiries are collected by the website form, the phone system, the chat tool and the front desk. Bookings are in the diary or the booking system. Attended first visits are in the practice system. The easiest way to join them is a single source field on every enquiry and booking, filled from a short fixed list such as the eight sources in the example. Free text produces ten spellings of the same source and a report nobody trusts.

Plan for the enquiries that arrive without a clear source. A phone call from an unknown number, or a person who walks in after seeing a leaflet, needs a rule: ask one friendly question at the front desk, record the answer, and use an "unknown" value rather than an empty cell when the person cannot say. The share of unknown sources is itself a number worth tracking, and a later lab reconciles it with the practice system.

Finally, fix the time window. A monthly report can group enquiries by the day the enquiry arrived (a cohort view) or count the bookings and visits that happened in the month, whichever enquiry they belong to (an activity view). Both are legitimate; mixing them produces rates above 100 % in some months. The example in this article uses the cohort view for simplicity: every number refers to the March enquiries.

The three ratios

From three counts you can calculate three ratios. Each one divides by the count at the START of the step it describes.

  • Booking rate = booked ÷ enquiries. It shows how well the enquiry is handled: the speed of the reply, the clarity of the offer, the ease of finding a slot.
  • Show rate = attended ÷ booked. It shows what happens between the booking and the visit: reminders, waiting time, how easy it is to cancel or move the appointment.
  • Enquiry to patient rate = attended ÷ enquiries. It describes the whole journey, and it equals booking rate × show rate.

Because the ratios chain together, a small weakness in each step adds up. A source with a 70 % booking rate and an 80 % show rate delivers 56 patients per 100 enquiries, not 70 and not 80. This is why a funnel view finds problems that a single number hides.

The worked example: eight sources in March

Linnet Row Clinic Group received 828 enquiries in March from eight sources. The table shows what happened to them. The rates in the right-hand columns are the ones you calculate in the lab.

SourceEnquiriesBookedAttendedBooking rateShow rateEnquiry to patient
Search ads1681098864.9 %80.7 %52.4 %
Organic search142988369.0 %84.7 %58.5 %
Maps listing121907874.4 %86.7 %64.5 %
Professional referrals64585490.6 %93.1 %84.4 %
Patient referrals57494686.0 %93.9 %80.7 %
Social ads210926343.8 %68.5 %30.0 %
Email to past enquirers36211758.3 %81.0 %47.2 %
Phone directory30171256.7 %70.6 %40.0 %
Total82853444164.5 %82.6 %53.3 %

The last row is the total. Notice that the total rates are not simple averages of the rows above them, a point we return to below.

The formulas, step by step

In the spreadsheet the source names sit in column A, enquiries in B, bookings in C and attended first visits in D, with the first source in row 4. The formulas for row 4, copied down to row 11, are:

  • Booking rate (E4): =C4/B4. Booked divided by enquiries.
  • Show rate (F4): =D4/C4. Attended divided by booked.
  • Enquiry to patient rate (G4): =D4/B4. Attended divided by enquiries.
  • Totals (B12:D12): =SUM(B4:B11), copied to the right.
  • Overall rates (E12:G12): =SUM(C4:C11)/SUM(B4:B11), =SUM(D4:D11)/SUM(C4:C11) and =SUM(D4:D11)/SUM(B4:B11), or =D12/B12 once the totals are in.

Format the rate columns as percentages. A rate of 0.644927 is the same number as 64.5 %, but a table full of percentages is far easier to read in a meeting.

Take professional referrals as an example. 64 enquiries became 58 bookings, so the booking rate is 58 ÷ 64 = 90.6 %. 54 of the 58 booked people attended, so the show rate is 54 ÷ 58 = 93.1 %. And 54 of the original 64 became patients, an enquiry to patient rate of 84.4 %. Check: 0.906 × 0.931 ≈ 0.844.

Reading the result

Figure 1 ranks the sources by enquiry to patient rate. The dashed line is the overall rate of 53.3 %, which comes from the totals row.

Enquiry to patient rate by source, MarchAttended first visit ÷ enquiriesOverall 53.3 %Social ads30.0 %Phone directory40.0 %Email to past enquirers47.2 %Search ads52.4 %Organic search58.5 %Maps listing64.5 %Patient referrals80.7 %Professional referrals84.4 %
Figure 1. Enquiry to patient rate by source, March. Sources below the dashed line convert fewer enquiries than the clinic group as a whole.

Referrals from other professionals (84.4 %) and from patients (80.7 %) convert best, which is typical: the person arrives with a recommendation and a reason. The map listing (64.5 %), organic search (58.5 %) and search ads (52.4 %) sit in the middle, email (47.2 %) and the phone directory (40.0 %) below them. Social ads convert worst at 30.0 %, and they also brought the most enquiries of any source (210 of 828), so they pull the overall rate down.

Now look at the steps. Social ads have a booking rate of 43.8 % and a show rate of 68.5 %, the lowest value in both columns. That points to two different questions: are the enquiries from social ads less ready to book (perhaps the ad is vague about prices or locations), and is the booking made so far in advance, or on impulse, that people forget it? A funnel does not answer these questions, but it tells you where to look.

The email source tells a different story. Its show rate is 81.0 %, close to the group's 82.6 %, but its booking rate is 58.3 %. Few people answer, but those who answer and book also come. The phone directory has a similar booking rate and a lower show rate of 70.6 %, which might be linked to older contact details or to a longer wait.

Why the total row is not an average

It is tempting to fill the total row of a rate column with =AVERAGE of the rows above. Do not do it. In this example the simple average of the eight enquiry to patient rates is 57.2 %, while the correct overall rate is 53.3 %. The average treats the 30 enquiries from the phone directory like the 210 enquiries from social ads, and it overstates the performance of the whole group by about four percentage points.

The rule is general: add the counts first, then divide once. The overall booking rate is total booked ÷ total enquiries = 534 ÷ 828 = 64.5 %. The overall show rate is 441 ÷ 534 = 82.6 %. The overall enquiry to patient rate is 441 ÷ 828 = 53.3 %. The ratio of totals weights every source by its volume, which is exactly what the clinic group experiences.

From enquiry to attended first visit, all sourcesEnquiries: 828Booked a first visit: 534Attended the first visit: 441Booking rate 64.5 % (534 ÷ 828)Show rate 82.6 % (441 ÷ 534)Enquiry to patient rate 53.3 % (441 ÷ 828)
Figure 2. The March funnel for all sources: 828 enquiries, 534 bookings and 441 attended first visits.

Figure 2 shows where the people are lost: 294 of 828 enquiries never book, and 93 of the 534 bookings never attend. In absolute numbers the booking step loses about three times as many people as the show step. In percentage terms the booking step loses 35.5 % and the show step 17.4 %. Both views are useful: the larger absolute loss shows where the most patients are, the rate shows how much better the best sources already do.

What would a better step be worth?

A funnel also lets you put a size on an improvement before you spend anything on it. Suppose the clinic group could lift the show rate of social ads from 68.5 % to 80 %, for example with better reminders. With 92 bookings, that is 92 × (0.80 − 0.685) = about 10.6 more patients per month. If, instead, a clearer offer lifted the booking rate of social ads from 43.8 % to 55 % while the show rate stayed at 68.5 %, the result would be 210 × (0.55 − 0.438) × 0.685 = about 16.1 more patients.

Neither figure is a forecast. Both are what-if calculations that show which step is worth more attention, and that is exactly what a spreadsheet is good at: change one input, read the effect. Later labs add costs and capacity, because a patient who cannot be treated within a reasonable time is not an improvement for anyone.

Common mistakes

  1. Dividing by the wrong count. Attended ÷ enquiries is not the show rate. Always ask: which people could have taken this step?
  2. Averaging rates. Add the counts, then divide, as shown above.
  3. Mixing definitions between sources. If one source counts a cancelled booking and another does not, the rates are not comparable.
  4. Comparing tiny groups. The phone directory had 30 enquiries. One more or fewer booking moves its rate by more than three percentage points. Treat small groups as hints, not as proof.
  5. Ignoring the lag. Someone who enquires on 29 March may attend on 5 April. For a monthly report, decide whether you group by enquiry date or by visit date, and keep to it.
Put the three definitions at the top of the spreadsheet or the report. When someone asks next quarter why the booking rate has changed, the first thing to check is whether the definition has.

What the funnel does not tell you

The funnel describes what happened. It does not prove why. Referral sources convert well partly because the patients are already convinced, not because the clinic does something special for them. Social ads may convert less well because they reach people earlier in their thinking. Do not conclude that social ads should be switched off or that referrals should be doubled. You also need the cost of each source, the value of the patients it brings, and the capacity to treat them; later lessons add each of these.

The funnel also says nothing about the quality of care or the health of the person. Marketing's job is to help people find a suitable service and book it, honestly and easily. It is not to choose patients, to push treatment, or to promise a result.

Privacy and rules. Use aggregate counts only: no names, no diagnoses, no free-text from messages in marketing reports. Advertising rules, consent rules for messages and data protection law differ by country and by profession. This course teaches the arithmetic, not legal advice, and it never gives medical advice. Check the local rules with your regulator or a qualified adviser.

Questions to ask your team each month

  • Did anything change in how enquiries, bookings or first visits are counted?
  • Which source has the lowest booking rate, and what do we know about those enquiries?
  • Which source has the lowest show rate, and which reminder or waiting time applies to it?
  • Do the totals match the practice system? If not, where are the missing patients?
  • Which sources have too few enquiries to judge, and how many months of data do we need?

From counts to decisions

With the table you can ask better questions. Which source loses the most people at the booking step, and could a faster reply or a clearer offer help? Which source has a good booking rate but a weak show rate, so that reminders or shorter waits are the lever? Which sources are so small that they need more months of data before anyone draws a conclusion?

A practical monthly routine is short. Export the three counts per source from the booking and practice systems, paste them into the sheet, check that the totals match the practice system, read the three rates, and write down two actions. Keep the sheet from month to month; the trend of each rate is more informative than any single month.

What the rest of the course covers

This lesson is the first of 20 hands-on labs. The rest of the course builds on the same data habits:

  • Lessons 2 to 5: cost per enquiry, booking and new patient; speed of the first reply; missed calls; reminders and no-shows.
  • Lessons 6 to 8: first-year value of a new patient, service-line mix and recall reminders.
  • Lessons 9 to 12: capacity and empty slots, local listings, referral partners and a claims review register for compliant advertising.
  • Lessons 13 to 16: privacy-safe aggregate reporting, attribution, waiting times and trend forecasts.
  • Lessons 17 to 20: budget allocation with capacity limits, simple tests, reconciliation with the practice system and a final quarterly scorecard.

Every lab uses a different fictional data set from Linnet Row Clinic Group, and every formula is checked as you type, so you learn the spreadsheet skills (ratios, absolute references, SUMIFS, XLOOKUP, SUMPRODUCT, IFS, FORECAST.LINEAR and more) together with the marketing logic.

Try it yourself

Open the lab above and fill in the booking rate, the show rate and the enquiry to patient rate for all eight sources. Then calculate the totals and the overall rates from the totals row. If your overall enquiry to patient rate is not 53.3 %, check whether you used SUM and divided once, instead of averaging the rows.

When you finish, change one number, for example the bookings from social ads, and watch which rates move. A spreadsheet that reacts correctly to changed inputs is a spreadsheet you can trust with real data.

All companies, clinics, sources and numbers in this article are fictional and for learning only. Benchmarks in marketing differ widely between specialties and countries, so measure your own funnel and compare it with its own past rather than with someone else's figures. Start with the three counts, write the definitions down, and let the ratios tell you which step to look at first.

Frequently asked questions

What is a patient-acquisition funnel for a clinic?

It is the path from enquiry to booked first visit to attended first visit. Counting the three steps per source, and the ratios between them, shows where potential patients are lost and which sources actually deliver new patients.

How do I calculate the booking rate and the show rate in Excel?

Booking rate = booked ÷ enquiries, for example =C4/B4. Show rate = attended ÷ booked, for example =D4/C4. The enquiry to patient rate is attended ÷ enquiries, =D4/B4, and equals booking rate times show rate.

Why should the overall rate not be the average of the source rates?

A simple average gives a small source the same weight as a large one. Add the counts first (SUM) and divide the totals once, so that each source counts with its number of enquiries.

What counts as a new patient in the report?

Usually a person who attended a first visit in the period, as recorded in the practice system. Cancelled bookings and follow-up visits are not new patients, and the rule should be written down so that every month is counted the same way.

Do I need patient data to measure this?

No. Aggregate counts per source are enough: enquiries, bookings and attended first visits. Names, diagnoses and message contents are not needed for marketing reports and should stay in the clinical systems.

Can I compare my rates with industry benchmarks?

Use benchmarks with great care. Rates differ by specialty, country, price level and how an enquiry is defined. The most reliable comparison is your own funnel over time and between your own sources.

Keep going: Healthcare Clinic Patient Acquisition: Enquiry Sources, Booking & Show Rates, Cost per New Patient, Recall Reminders, Service-Line Mix & Compliant Reporting

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time €9.90.

  1. Cost per New Patient: Cost per Enquiry, per Booking and per Attended Patient by Source
  2. Speed to First Reply: Booking Rate by Response Time, Index and Bookings Forgone
  3. Missed Calls and After-Hours Enquiries: Missed Rate, Rank and New Patients Lost
  4. Appointment Reminders and No-Shows: Show Rate by Method and Extra Visits to Reach a Target
  5. First-Year Value of a New Patient: Revenue, Contribution and the Highest Affordable Acquisition Cost
  6. Service-Line Mix: Share of New Patients, Total Contribution, Rank and Weighted Average
  7. Recall Reminders: Contact Coverage, Booking Rate, Recall Rate and Revenue from Recalled Visits
  8. Capacity and Empty Slots: Fill Rate, Spare-Capacity Weeks and Where Marketing Can Help
  9. Local Listing Performance: Views, Actions, Action Rate and Bookings per 100 Actions by Site
  10. Referral Partners: Change, Share, Attended Rate and the Concentration of the Top Three
  11. Compliant Claims: A Review Register with Status, Risk Weights, Open Risk and Live Items on Hold
  12. Privacy-Safe Reporting: Small-Cell Suppression, Combined Groups and Aggregate-Only Tables
  13. Attribution: Tracked vs Self-Reported Sources, Agreement and Blended Credit
  14. Waiting Time: Show Rate by Wait Band, Weighted Average Wait and Working Days with NETWORKDAYS
  15. Trend and Forecast: Moving Average, Linear Trend, Seasonal Gaps and FORECAST.LINEAR
  16. Budget Allocation with Capacity Limits: Expected Patients, Unused Budget and Room to Add
  17. Testing Changes: Pooled Rate, Standard Error, z Value, p-Value and the People Needed per Group
  18. Data Quality: Reconciling Marketing Counts with the Practice System and Finding Untracked Patients
  19. Final Project: A Quarterly Patient-Acquisition Scorecard with Rates, Cost, Return and Status

Buy course — €9.90 See the full course