Why "how many leads?" is the wrong first question
If you run or market a law firm, an accounting practice or a consultancy, you probably know the feeling: the phone rings, the contact form fills up, and at the end of the month somebody asks whether marketing is working. The honest answer depends on a chain of events, not on a single number. An enquiry is only the start. Somebody has to answer it, a first call or meeting has to be booked, the person has to turn up, and finally they have to sign an engagement letter. Each link can break, and each link has a rate you can measure.
Counting leads alone hides the difference between sources. A directory listing that sends forty enquiries a month looks better than a client recommendation that sends thirty-one, until you notice what happens next. This free lesson shows you how to follow every enquiry source through the funnel in a spreadsheet, so that you compare sources by what they produce, not by how loud they are.
The example is Ardenvale Partners, a fictional firm with four service lines: employment law, property law, tax advice and business consulting. All people, sources and numbers are invented for learning, and the figures are illustrative, not benchmarks. You will use the same ten enquiry sources as in the lab above, with the results of one month, March 2027.
What counts as an enquiry, a consultation and a new client
Before you calculate anything, fix the definitions. Two people who use the same word for different things will produce numbers that cannot be compared. For this course we use three stages:
- Enquiry: a person or business asked the firm for help about a possible matter, by phone, form, email or in person. Spam, job applications and supplier calls do not count.
- Consultation booked: a first call or meeting is in the diary. The person has not necessarily attended yet; lesson 7 of the course separates booked from attended.
- New client: the person or business signed an engagement letter and the firm opened a matter.
Write the definitions down and give them to everyone who logs enquiries. A receptionist who counts every call as an enquiry and a partner who counts only written requests will otherwise disagree about the booking rate for ever.
The three rates and why they multiply
The funnel has two steps, so it has two step rates and one overall rate:
- Booking rate = consultations booked ÷ enquiries. How many enquiries turn into a diary entry?
- Close rate = new clients ÷ consultations booked. How many consultations end with a signed engagement?
- Enquiry-to-client rate = new clients ÷ enquiries. How many enquiries become clients, start to finish?
The three are linked: enquiry-to-client = booking rate × close rate, because the booked consultations cancel out: (booked ÷ enquiries) × (clients ÷ booked) = clients ÷ enquiries. This is useful when you diagnose a weak source. If the enquiry-to-client rate is low, the booking rate and the close rate tell you where the funnel leaks: before the first meeting (response and qualification) or at the meeting itself (fit, price, trust).
The worked example: ten sources in one month
Here is the March data of Ardenvale Partners. It is the same data as in the lab: ten sources, the number of enquiries each brought, the consultations booked and the new clients.
| Source | Enquiries | Booked | New clients |
|---|---|---|---|
| Organic search | 62 | 38 | 17 |
| Paid search ads | 48 | 26 | 9 |
| Client referrals | 31 | 27 | 19 |
| Professional referrals | 18 | 16 | 12 |
| Directory listing | 40 | 19 | 6 |
| Website contact form | 35 | 21 | 8 |
| 22 | 9 | 3 | |
| Seminars and events | 16 | 12 | 6 |
| Partner newsletter | 14 | 8 | 4 |
| Local press and print | 9 | 3 | 1 |
| All sources | 295 | 179 | 85 |
Even before any ratio, the table shows that the sources differ in shape. Organic search brings the most enquiries (62), but only 17 clients. Client referrals bring 31 enquiries and 19 clients. Seminars and events bring only 16 enquiries but 6 clients. To compare them fairly you need rates.
Step 1: add up the funnel
The first formula is the humblest and the most important: the total row. In the lab you type =SUM(B4:B13) in B14 and copy it to the right, so that C14 and D14 add up the consultations booked and the new clients. Excel adjusts the column letters when you copy a formula sideways, which is why the same formula works for all three columns.
The totals are 295 enquiries, 179 consultations booked and 85 new clients. Check them against your own list before you go on: a total row that is off by a few rows is the most common reason why a funnel report loses trust. A typical slip is a range that stops one row short, for example =SUM(C4:C12), which silently leaves out Local press and print.
Step 2: the three rates, row by row
Now put the rates next to the counts. In E4 you type =C4/B4 for the booking rate, in F4 =D4/C4 for the close rate and in G4 =D4/B4 for the enquiry-to-client rate. Copy each formula down to row 14, including the total row, and format the columns as percentages. Because the total row has its own counts, its ratios are the correct overall rates, not an average of the rows above.
| Source | Booking rate | Close rate | Enquiry-to-client |
|---|---|---|---|
| Organic search | 61.3 % | 44.7 % | 27.4 % |
| Paid search ads | 54.2 % | 34.6 % | 18.8 % |
| Client referrals | 87.1 % | 70.4 % | 61.3 % |
| Professional referrals | 88.9 % | 75.0 % | 66.7 % |
| Directory listing | 47.5 % | 31.6 % | 15.0 % |
| Website contact form | 60.0 % | 38.1 % | 22.9 % |
| 40.9 % | 33.3 % | 13.6 % | |
| Seminars and events | 75.0 % | 50.0 % | 37.5 % |
| Partner newsletter | 57.1 % | 50.0 % | 28.6 % |
| Local press and print | 33.3 % | 33.3 % | 11.1 % |
| All sources | 60.7 % | 47.5 % | 28.8 % |
Check one row by hand before you trust the sheet. Professional referrals: 18 enquiries, 16 booked, 12 new clients. Booking rate 16 ÷ 18 = 0.889, close rate 12 ÷ 16 = 0.75, and 0.889 × 0.75 = 0.667, which is exactly 12 ÷ 18, the enquiry-to-client rate. If the three numbers in your sheet do not obey this identity, one of the formulas points at the wrong column.
Notice two things. The booking rate and the close rate are different questions: Professional referrals are booked 88.9 % of the time and close 75 % of the consultations, while Directory listing enquiries are booked less than half the time (47.5 %) and close only 31.6 %. And the all-sources row is not the simple average of the ten rows. It is weighted by volume, which is exactly what you want when you report to partners.
Step 3: reading the result
The bar chart in Figure 1 makes the ranking obvious. Professional referrals (66.7 %) and client referrals (61.3 %) convert best, followed by seminars and events (37.5 %). At the bottom are local press and print (11.1 %), LinkedIn (13.6 %) and directory listing (15.0 %).
A good reading has three layers. First, which sources are above or below the all-sources rate of 28.8 %? Second, for the weak sources, is the leak before or after the meeting? Directory listings have a poor booking rate, so people ask but do not book: the listing may attract price comparers, or the response may be slow (the course measures this in lessons 5 and 6). Third, how big is the source? A source with a very high rate but few enquiries cannot fill the diary by itself.
From rates to decisions
A table of rates is only useful if it changes what you do. A simple way to read each source is to put its volume and its enquiry-to-client rate side by side:
- High rate, low volume (professional referrals, seminars): protect and feed these sources. Thank the referrer, repeat the event, ask satisfied clients for introductions where your rules allow it.
- High volume, middling rate (organic search, the contact form): improve the steps after the enquiry, such as how fast and how well you reply. Small gains in the booking rate multiply across many enquiries.
- High volume, low rate (the directory listing): ask whether the enquiries are suitable and whether the listing sets the right expectations before you pay for another year.
- Low volume, low rate (local press, LinkedIn): do not decide on one month. Collect several months of data, and compare their cost per client in lesson 2 before you keep or drop them.
Rates describe past behaviour. They do not prove that more spending on a source would bring more clients at the same rate, because the first enquiries from a source are often the best ones. The course comes back to this when it builds a budget what-if grid in lesson 17.
Keeping the enquiry log clean
A funnel report is only as good as the log behind it. Five habits keep the data trustworthy:
- Record the source when the enquiry arrives, with a short fixed list of values, not free text.
- Log every enquiry once, on the day it arrives, even when it is passed between colleagues.
- Update the status when the consultation is booked and when the engagement letter is signed, using the dates, not memory.
- Review the list of sources once a quarter, merge duplicates and retire sources that no longer exist.
- Compare the number of new clients in your enquiry log with the matter-opening list from the accounts system every month.
Common mistakes
- Dividing by the wrong total. The close rate divides by the consultations booked, not by the enquiries. Mixing the two gives a rate that is neither.
- Averaging percentages. The overall rate is total clients ÷ total enquiries, not the average of the ten source rates. Small sources would count as much as large ones.
- Summing a range that is too short. Count the rows, and check the total against another source such as the matter-opening report.
- Forgetting the dollar signs. In lesson 1 every ratio refers to its own row, so no dollar signs are needed. Later, when a formula refers to the total row, you will anchor it with
$D$14so that it does not slide when copied. - Comparing months with different definitions. If the firm starts counting phone calls as enquiries in April, the booking rate will fall without anything changing in reality.
Small numbers: how much should you trust a rate?
Look at Local press and print: 9 enquiries, 3 consultations, 1 new client. Its enquiry-to-client rate is 11.1 %. One more client would make it 22.2 %, and one fewer would make it 0 %. A rate built on a handful of cases moves a lot with a single event. This is not an argument against measuring; it is an argument for adding months together, for looking at several months before you cut a source, and for treating the smallest sources as questions rather than conclusions.
How this differs between law, accounting and consulting
The arithmetic is the same, but the shape of the funnel differs. Employment and property clients often arrive with a deadline, so the cycle is short and response time dominates the result. Accounting and tax clients often become annual clients, so the value of a win is larger than the first invoice. Consulting buyers compare several firms over weeks and often start with a referral or an event. When you apply this to your own firm, calculate the rates per service line as well as for the firm as a whole, because an overall rate mixes quick, small matters with slow, large ones.
Advertising rules and referrals
Rules for professional advertising differ by country and by profession. They may cover what you can say about results, the use of testimonials, how fees are described and whether referral fees are allowed at all. This course is about arithmetic, so it does not tell you what is permitted where. Before you act on any number from your own firm, check the rules of your regulator or professional body. Lesson 19 builds a simple review log so that marketing items do not go out unchecked.
What the rest of the course covers
The free lesson gave you the funnel. The remaining 19 labs add cost, value, speed and planning:
- Lessons 2-4: cost per enquiry and per client, fees billed and collected, lifetime value and the allowable cost of winning a client.
- Lessons 5-8: intake response time, speed bands and booking rate, show-up and no-shows, close rate and net value by consultation type.
- Lessons 9-12: referral sources, content leads, seminar and webinar leads, and lead quality (suitable and unsuitable enquiries).
- Lessons 13-16: the sales cycle, capacity planning, a trend and forecast, and a test of two enquiry forms.
- Lessons 17-20: a budget what-if grid, attribution (told vs recorded source), a marketing review log and a final scorecard.
Quick reference: the formulas of this lesson.
| Function | What it does here | Example |
|---|---|---|
SUM | Totals the funnel | =SUM(B4:B13) |
| Division | Booking rate | =C4/B4 |
| Division | Close rate | =D4/C4 |
| Division | Enquiry-to-client rate | =D4/B4 |
| Ctrl + Shift + % | Formats a cell as a percentage | Columns E to G |
| Ctrl + C, then Ctrl + V | Copies a formula down or to the right | E4 to E14 |
Summary
A professional-services funnel has three stages: enquiry, booked consultation and new client. The booking rate is booked ÷ enquiries, the close rate is clients ÷ booked, and their product is the enquiry-to-client rate. At Ardenvale Partners, 85 of 295 enquiries became clients (28.8 %); professional referrals and client referrals converted best and local press, LinkedIn and directory listings worst. Totals come from =SUM, rates from simple divisions, and the overall rate must be calculated from the totals, never averaged.
Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each step, and you will end up with a funnel you can rebuild from your own enquiry log today.
Frequently asked questions
What is a good enquiry-to-client rate for a law firm or accounting practice?
There is no single good number. It depends on the service, the source and how enquiries are counted. Referrals usually convert far better than directories or paid search. Compare your own sources with each other and with your own past months rather than with an outside figure.
What is the difference between booking rate and close rate?
The booking rate is consultations booked divided by enquiries; it shows how many enquiries lead to a first call or meeting. The close rate is new clients divided by consultations booked; it shows how many consultations end with a signed engagement. Multiplied together they give the enquiry-to-client rate.
Why is the overall rate not the average of the source rates?
Because sources differ in size. A source with nine enquiries would count as much as one with sixty-two in a simple average. The overall rate divides total clients by total enquiries, which weights every source by its volume.
How do I total a column in Excel and copy the formula to the other columns?
Type =SUM(B4:B13) under the column and copy the cell to the right. Excel moves the column letters automatically, so the same formula totals the consultations booked and the new clients. Check that the range covers every data row.
Should I include enquiries that I could not serve, such as conflicts or wrong practice areas?
Count them in the enquiries, but track them separately so you can also report a suitable-enquiry rate. Lesson 12 of the course shows how. Mixing them silently lowers the booking rate of a source for reasons that have nothing to do with how well you handled the enquiry.
Can I use this for referrals, seminars and my website at the same time?
Yes. Give every enquiry a source when you log it, then use one row per source in the funnel table. Keep the list of sources short and fixed, otherwise the same source will appear under several names and the counts will be split.
Keep going: Professional Services Lead Generation: Enquiry Sources, Booking & Close Rates, Value per Matter, Content & Seminar Leads, Response Time & Capacity
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.
- Cost per Enquiry, Cost per Consultation and Cost per New Client by Channel
- Value per Matter: Fees Billed, Fees Collected, Collection Rate and Average Fee by Practice Area
- Client Lifetime Value: Repeat Matters, Lifetime Margin and the Allowable Cost of Winning a Client
- Intake Response Time: Minutes to First Reply, Share Within Target, Average and Median
- Response Speed Bands: XLOOKUP Approximate Match, Booking Rate per Band and the Cost of Waiting
- Consultation Show-Up and No-Shows: Attendance Rate, Close Rate of Attended Consultations and the Value of Reminders
- Close Rate by Consultation Type: Expected Fee per Consultation, Time Cost and Net Value
- Referral Sources: Close Rate, Fees per Referral, a League Table with RANK.EQ and the Concentration of the Top Three
- Content Leads: Enquiries per 1,000 Views, Production Cost per Client, INDEX-MATCH and a Scatter Chart
- Seminar & Webinar Leads: Attendance Rate, Cost per Attendee, Cost per Client and the Zero-Client Case
- Lead Quality: Suitable and Unsuitable Enquiries, Conflicts, Suitable Rate and Cost per Suitable Enquiry
- The Sales Cycle: Days from Enquiry to Instruction, Average and Longest Wait by Practice Area
- Capacity Before Campaigns: Hours Needed, Utilisation, IFS Status Bands and the Most Matters We Can Take
- Enquiry Trend and Forecast: Moving Average, SLOPE, FORECAST.LINEAR and New Clients Next Quarter
- Is the Short Form Really Better? Pooled Rate, Standard Error, z Statistic and p-Value for Two Enquiry Forms
- Budget What-If Grid: Mixed References, New Clients, Net Margin and the Break-Even Enquiry-to-Client Rate
- Source Attribution: What Clients Say vs What the System Records, Mismatch Rate and Clustered Columns
- Marketing Review Log: Review Status, Days Live Without Review, Items at Risk and a Rule That Marks Them
- Final Project: Annual Lead Generation Scorecard with Cost per Client, Return per £1, Review Flags and a Combo Chart