Dials divided by appointments that passed QA, counted the same way every week: the bottom number, the sheet, the formulas, and what to check when it creeps up.
Swipe or tap Next · checked on 2026-10-25
Not technical? Start here. Copy this prompt into ChatGPT or Claude (the free versions work) and it walks you through the whole guide, one step at a time, in plain English. It asks before it assumes anything and checks with you before anything that costs money, reaches a real customer or deletes something. The rest of the page is the same guide, written out, if you'd rather do it yourself.
The walkthrough prompt (copy all of it) · lines 1-2 of 65. Copy all copies the whole prompt; read it in full on the page.
Hi! Please be my patient guide for this step-by-step guide from Receipts Group: "Dials per certified appointment: the one number to watch every week"
The guide is here: https://receiptsgroup.com/guides/dials-per-certified-appointment (open it if you can; if you can't, everything you need is below).Two numbers from two places, one division, once a week. The dials come from your dialer. The bottom number comes from QA. The sheet puts them side by side.
Certified counts in the week the appointment was booked, so both numbers come from the same dials. Read a week once QA has finished with it.
The top number is easy: dials. The bottom number is where it goes wrong, because every choice gives you a nicer-looking number than the one that matters. Here's what each one actually tells you.
Dials divided by any of the first three gives you a smaller, nicer number. It just doesn't tell you whether the dials pay.
On our floor an appointment is certified when QA, someone other than the setter, calls the homeowner back and all four come back yes:
Miss one, and it doesn't count. If you use a different check, that's fine. Write it down and use the same one every week.
If you tighten or loosen what passes QA, the number moves even though the dials didn't change. That's fine, just don't compare across it. Note the change in the sheet and measure a new baseline.
Four sentences, on paper or at the top of the sheet. A dial is every call attempt the dialer placed, answered or not: no-answers, voicemails, busy signals and hang-ups all count. If your dialer shows both dials and connected calls, take dials. Booked is an appointment your setter set.
Certified is one that passed your QA check. And the one people skip: what happens to an appointment QA can't reach. Pick a rule (how many tries, and by when) and once it runs out, that appointment counts as booked, not certified.
One tab per floor, or per list if you run more than one. Mixing two lists in one row hides which one is the noise. Numbers only: homeowners' names, phone numbers and addresses stay in your CRM.
The sheet's columns · Part 1 of 2 · Copy copies all 23 lines
THE DIALS PER CERTIFIED SHEET: one tab per floor or list
(numbers only: no homeowner names, phone numbers or addresses)
Row 1 is the headers. One row per week, starting on row 2.
A Week starting the Monday (or whatever day your week starts)
B Dials every call attempt the dialer placed that week,
answered or not
C Booked appointments set that week
D Certified of the ones booked that week, how many passed QA
E Pending QA of the ones booked that week, how many QA
hasn't finished yet
F Dials per certified (formula)
G Booked that passed QA (formula)
H Change vs last week (formula, from row 3)The sheet's columns · Part 2 of 2 · Copy copies all 23 lines
I Change vs baseline (formula)
J Floor cost optional: what the floor cost that week
(payroll, dialer, phone lines, lists)
K Cost per certified (formula)
L What changed new list, new setter, new hours, script change...
(no names)
Your baseline goes off to the side, in N1 to O6 (next step).Dials come from your dialer's call report for that week. Booked, Certified and Pending QA come from your QA log or your CRM. The rule that keeps it honest: an appointment counts in the week it was booked, not the week QA passed it. A Friday booking that QA passes on Monday belongs to Friday's week, because that's the week whose dials made it.
Every dialer and CRM puts its reports somewhere different, so I'm not going to guess your menus. Look for the call report you can run for a date range.
Type each one into the cell named on its left, exactly as written. They do the dividing for you. G, H and I show decimals: 0.71 in G means 71 out of 100 bookings passed QA, and 0.10 in H means 10% more dials per certified appointment than the week before. A minus sign means it went down.
The formulas (cell, then what to type) · Part 1 of 2 · Copy copies all 16 lines
Row 2 (your first week):
F2 =IF(D2=0,"none certified",ROUND(B2/D2))
G2 =IF(C2=0,"none booked",D2/C2)
I2 =IFERROR(F2/$O$4-1)
K2 =IF(D2=0,"none certified",ROUND(J2/D2))
Row 3 (your second week), the same four with 3, plus H:
F3 =IF(D3=0,"none certified",ROUND(B3/D3))
G3 =IF(C3=0,"none booked",D3/C3)
H3 =IFERROR(F3/F2-1)
I3 =IFERROR(F3/$O$4-1)
K3 =IF(D3=0,"none certified",ROUND(J3/D3))The formulas (cell, then what to type) · Part 2 of 2 · Copy copies all 16 lines
Every new week: the same five formulas with that row's
number (row 4 uses B4, C4, D4, J4, and F4/F3 in H).
$O$4 always stays $O$4: that's your baseline.Ten days you actually dialed, not ten calendar days. Add up the dials from your dialer for those days, and count the appointments booked in those days that passed QA. That's your starting number. The trend line in O6 draws F as a little line chart inside the cell, so you can see the creep without reading every row.
The baseline and the trend (off to the side)
N1 Baseline: last 10 dialing days ([first day] to [last day])
N2 Dials O2 [the total from your dialer for those days]
N3 Certified O3 [booked in those days and passed QA]
N4 Dials per certified O4 =ROUND(O2/O3)
N6 Trend O6 =SPARKLINE(F2:F60,{"empty","ignore";"nan","ignore"})Pick a day, the same one every week. Fill in last week's row once QA has finished with it (Pending QA is 0), then read three things: F, the number itself; H, how it moved from last week; and I, how far it's drifted from your baseline.
Column I is the one I'd stare at. A creep is a couple of percent a week, which looks like nothing in H, and it adds up. If it creeps up, you're paying for noise. Before anybody buys more dials, find out what changed. That's what column L is for.
With only a handful of certified appointments in a week, one more or one less moves the number a lot. Read two weeks together.
Want it in dollars? Put what the floor cost that week in J (payroll, dialer, phone lines, lists) and K shows what one certified appointment cost you. Whether that makes money depends on what your sits close for, and that's your number, not mine.
When it creeps up, the tempting fix is more dials: a faster dialer, an older list, longer hours. Check the rules first.
The federal rule (47 CFR 64.1200) says telemarketing calls to homes can't go out before 8 a.m. or after 9 p.m. at the homeowner's local time, numbers on the National Do Not Call Registry are generally off limits, autodialed or prerecorded marketing calls to cell phones need prior express written consent, and a dialer can't abandon more than 3% of the telemarketing calls a person answers, measured over 30 days per campaign.
Plain English, not legal advice: states add their own rules, so have your lawyer look before you change your dialer, your list or your hours.
Example (made up): five weeks on one floor. These are invented numbers to show the arithmetic, not anybody's results.
Example (made up). Pending QA was 0 for every week before it was read.
Here's what the usual dashboard shows: dials up, bookings up every single week, everybody high-fiving. Divide by booked and it even looks like the floor's getting better, 214 down to 175. Divide by certified and it went from 300 to 450 dials for every appointment that passed QA. Same dials, opposite story.
And G says where: fewer and fewer bookings pass QA. That points at the bookings, not the list. So the fix in this example is the end of the setters' calls, not more dials. It's like judging a fishing trip by how many times you cast.
Before you change anything, measure where you are. Take your last ten dialing days, add up the dials, count the appointments booked in those days that passed QA, and divide. That's your baseline, and it costs nothing but the time.
Good: one number you can say out loud with its days and its check, like "so many dials per certified appointment, over these ten days, QA'd this way." What you'll probably find: some appointments nobody can say passed or failed QA. That's the first thing to fix: every booking gets a QA result.
Once you have a few weeks, paste them into this prompt and it reads them for you. Before you paste: numbers only, no names. In Claude you can also choose whether your chats help improve its models: Settings → Privacy → Help Improve our AI models.
Read my weeks · lines 1-6 of 21. Copy all copies the whole prompt; read it in full on the page.
Here are the weekly numbers from my phone floor. Please read them for me in plain words.
What the columns mean:
- Dials: every call attempt the dialer placed that week, answered or not.
- Booked: appointments set that week.
- Certified: of the ones booked that week, how many passed QA (a callback by someone other than the setter confirmed the homeowner, every decision maker, the product and the time).Good: it flags weeks still waiting on QA and small weeks, and points at the list or the bookings using your columns. Bad: it tells you what a "good" number is, or fills in a week you didn't give it. Tell it: "Use only the numbers I pasted."
Checked 2026-10-25 on claude.com/pricing and Google's Sheets page. Prices change, so check before you buy.
#DIV/0! in O4The sheet is dividing by zero: O3 (your baseline's certified) is empty or 0. Fill it in. If it really is 0 over ten dialing days, that's your answer, and the fix starts with QA and the bookings, not the sheet.
Zero appointments passed QA that week: dials with nothing to show for them. Check that Pending QA is 0 first. If it is, find out what changed that week before the next one.
Either QA wasn't finished when you read it (Pending QA wasn't 0), or it was a small week. Wait for QA, and read small weeks two at a time.
One counts every attempt, the other counts connected calls, or one counts per agent. Pick one report and one definition, write it at the top of the sheet, and use it every week.
They're being counted on the day QA passed them. Count each appointment in the week it was booked.
Check before you celebrate. Did QA's check get looser? Did someone start counting booked as certified? If the check changed, start a new baseline.
Every single booking passes QA. Maybe your setters really are that good. Sit with QA for a few callbacks and make sure all four checks get asked.
That's IFERROR doing its job: there's nothing to compare yet (it's the first week, last week says "none certified", or the baseline is empty).
Check the row numbers in its formulas match the row (row 7 uses B7 and D7, and F6 in H). $O$4 stays $O$4 in every row.
F has no numbers yet, or your weeks go past row 60. Change F60 to a bigger row number.
Tell it: "Use only the numbers I pasted. Don't compare my numbers to anybody else's."
That's fine. Everything it needs is in the walkthrough prompt. Tell it to keep going without the link.
What each page backs up is listed at the end of the full guide.
Pick the bottom number on purpose, count it the same way every week, and when it creeps up, find out why before you buy more dials. A giant green dial count only tells you the phones were busy.
It's how our own floor counts, and how the call floors we build count: an appointment counts once it passes QA.
Want help setting this up? Receipts Group builds these systems.
Open the full guide as a page (every file in full, plus the table of contents).