Published Aug 13, 2026 · Updated Nov 19, 2026 · 12 min read
Track Cost Per Resolution in Google Sheets
A copy-pasteable Google Sheets build for cost per verified resolution: a ticket log, a monthly cost sheet, a live dashboard, a budget-variance template and a worked example that shows why the vendor rate understates what you pay.

To track cost per verified resolution (CPVR) in Google Sheets, log one row per ticket with its resolver, verification status and AI fee, keep monthly fixed costs on a second sheet, and divide total cost by the count of verified rows: =total cost / COUNTIFS(status,"Verified"). Count every AI fee you were billed in the numerator, including attempts that reopened or escalated, and only verified tickets in the denominator. In the sample below that turns a $0.99 headline rate into $1.49 per verified AI resolution.
The build has four tabs, uses only native functions (SUMIFS, COUNTIFS, FILTER, QUERY, ARRAYFORMULA, IFERROR, SPARKLINE, EDATE), and needs no add-ons or scripts.
What is the core formula?
Cost per verified resolution = total support cost ÷ verified resolutions
A verified resolution is a ticket handled start to finish with no human handoff, where the customer did not come back about the same issue inside your verification window. RTCI uses 72 hours.
For AI-only CPVR in August 2026, with the ticket log on a sheet named Ticket Log:
=SUMIFS('Ticket Log'!G:G,'Ticket Log'!A:A,">="&DATE(2026,8,1),'Ticket Log'!A:A,"<"&DATE(2026,9,1)) / COUNTIFS('Ticket Log'!D:D,"AI",'Ticket Log'!E:E,"Verified",'Ticket Log'!A:A,">="&DATE(2026,8,1),'Ticket Log'!A:A,"<"&DATE(2026,9,1))
The numerator sums every AI fee in the month (column G), whatever happened to the ticket. The denominator counts only verified AI resolutions.
Sheet 1: how should the ticket log be laid out?
Name the first sheet Ticket Log. Row 1 holds headers and every row below is one ticket, so a single formula can filter by month, category, resolver and status.
| Column | Header | Example | Purpose |
|---|---|---|---|
| A | Date | 2026-08-01 | Ticket creation date |
| B | Ticket ID | TK-1001 | Unique identifier |
| C | Category | Billing | Ticket type for segmentation |
| D | Resolver | AI | AI, Agent, or Escalated |
| E | Verified | Verified | Verified, Reopened, Escalated, or Pending |
| F | Resolution Time (hrs) | 0.3 | Time from open to closure |
| G | AI Cost ($) | 0.99 | AI fee billed for this ticket, if any |
| H | Human Cost ($) | 0.00 | Per-ticket human cost allocation (optional) |
| I | Total Cost ($) | 0.99 | AI plus human cost |
In I2, enter this once; it fills every row that has a date and leaves blank rows empty:
=ARRAYFORMULA(IF(A2:A="",,G2:G+H2:H))
Add Data → Data validation to column D (AI, Agent, Escalated) and column E (Verified, Reopened, Escalated, Pending). A typo such as verifed silently drops a row from every COUNTIFS.
What sample data should I start with?
Paste these 12 rows into Ticket Log from A2. They are made up to exercise every status, and the formulas below assume this layout.
| Date | Ticket ID | Category | Resolver | Verified | Resolution Time (hrs) | AI Cost ($) | Human Cost ($) | Total Cost ($) |
|---|---|---|---|---|---|---|---|---|
| 2026-08-01 | TK-1001 | Billing | AI | Verified | 0.2 | 0.99 | 0.00 | 0.99 |
| 2026-08-01 | TK-1002 | Password | AI | Verified | 0.1 | 0.99 | 0.00 | 0.99 |
| 2026-08-02 | TK-1003 | Refund | Agent | Verified | 1.5 | 0.00 | 8.00 | 8.00 |
| 2026-08-02 | TK-1004 | Billing | AI | Reopened | 0.3 | 0.99 | 0.00 | 0.99 |
| 2026-08-03 | TK-1005 | Bug | Escalated | Escalated | 2.0 | 0.99 | 8.00 | 8.99 |
| 2026-08-03 | TK-1006 | Password | AI | Verified | 0.2 | 0.99 | 0.00 | 0.99 |
| 2026-08-04 | TK-1007 | How-to | AI | Verified | 0.4 | 0.99 | 0.00 | 0.99 |
| 2026-08-04 | TK-1008 | Refund | Agent | Verified | 1.2 | 0.00 | 8.00 | 8.00 |
| 2026-08-05 | TK-1009 | Billing | AI | Pending | 0.5 | 0.00 | 0.00 | 0.00 |
| 2026-08-05 | TK-1010 | Password | AI | Verified | 0.1 | 0.99 | 0.00 | 0.99 |
| 2026-08-06 | TK-1011 | Bug | Escalated | Escalated | 3.0 | 0.99 | 8.00 | 8.99 |
| 2026-08-06 | TK-1012 | How-to | AI | Verified | 0.3 | 0.99 | 0.00 | 0.99 |
The escalated rows carry both an AI fee for the attempt and a human cost for the handoff. Whether that AI fee is real depends on your contract (see the vendor table below), so set column G from the invoice, not from the vendor's headline.
Sheet 2: where do monthly fixed costs go?
Name the second sheet Costs. It holds the costs that do not belong to a single ticket: AI platform fees and the human team's monthly cost. The figures are sample values.
| A | B | C | D |
|---|---|---|---|
| Month | AI Base Cost ($) | AI Per-Resolution Fees ($) | Human Fully Loaded Cost ($) |
| 2026-08-01 | 500.00 | 495.00 | 32,000.00 |
| 2026-09-01 | 500.00 | 660.00 | 32,000.00 |
| 2026-10-01 | 500.00 | 420.00 | 30,000.00 |
| 2026-11-01 | 500.00 | 540.00 | 31,000.00 |
| 2026-12-01 | 500.00 | 380.00 | 30,000.00 |
Column C is the vendor's invoice. In E2 (label it Reconciled AI Fees), total the AI fees from the ticket log for the same month and fill down:
=SUMIFS('Ticket Log'!G:G,'Ticket Log'!A:A,">="&A2,'Ticket Log'!A:A,"<"&EDATE(A2,1))
If E and C disagree, either the log is missing rows or the vendor is billing events you did not record. Both are worth chasing before renewal.
Human fully loaded cost is salary, benefits, tooling, QA and a share of management for the month. Put the whole pool here rather than a per-ticket average, so the fully loaded CPVR divides real spend by real verified outcomes.
Sheet 3: what goes on the dashboard?
Name the third sheet Dashboard. Put this KPI block in A1:B11; row numbers matter because later cells refer to them.
| A | B |
|---|---|
| Metric | Value |
| Total tickets | =COUNTA('Ticket Log'!B2:B) |
| Verified AI resolutions | =COUNTIFS('Ticket Log'!D:D,"AI",'Ticket Log'!E:E,"Verified") |
| Verified agent resolutions | =COUNTIFS('Ticket Log'!D:D,"Agent",'Ticket Log'!E:E,"Verified") |
| Reopened or escalated | =COUNTIF('Ticket Log'!E:E,"Reopened")+COUNTIF('Ticket Log'!E:E,"Escalated") |
| AI verified resolution rate | =B3/B2 (format as %) |
| Overall verified resolution rate | =(B3+B4)/B2 (format as %) |
| Total cost (fully loaded) | =SUM('Ticket Log'!G2:G)+SUM(FILTER(Costs!B2:B,Costs!A2:A>=EOMONTH(MIN('Ticket Log'!A2:A),-1)+1,Costs!A2:A<=MAX('Ticket Log'!A2:A)))+SUM(FILTER(Costs!D2:D,Costs!A2:A>=EOMONTH(MIN('Ticket Log'!A2:A),-1)+1,Costs!A2:A<=MAX('Ticket Log'!A2:A))) |
| CPVR (fully loaded) | =B8/(B3+B4) |
| AI CPVR | =SUM('Ticket Log'!G2:G)/B3 |
| Agent cost per verified resolution | =SUMIF('Ticket Log'!D:D,"Agent",'Ticket Log'!H:H)/B4 |
B8 adds every AI fee in the log, the AI base fee and the human pool for the months the log covers. It leaves out the per-ticket human allocations in column H, because the pool already contains that cost. B10 divides all AI fees, including fees on reopened and escalated attempts, by verified AI resolutions. B11 uses the per-ticket allocation, so it is a marginal figure for deciding which categories to automate, not a reporting number.
How do I build a monthly trend table?
Put this in Dashboard!A14. It returns one row per month with ticket count and cost totals:
=QUERY('Ticket Log'!A:I,"SELECT YEAR(A), MONTH(A)+1, COUNT(B), SUM(I), SUM(G), SUM(H) WHERE A IS NOT NULL GROUP BY YEAR(A), MONTH(A)+1 ORDER BY YEAR(A), MONTH(A)+1 LABEL YEAR(A) 'Year', MONTH(A)+1 'Month', COUNT(B) 'Tickets', SUM(I) 'Total Cost', SUM(G) 'AI Cost', SUM(H) 'Human Cost'",1)
MONTH() inside QUERY counts from 0, hence the +1. The output fills A14:F, with headers in row 14. Type Verified in G14 and put this in G15 to count verified tickets per month:
=ARRAYFORMULA(IF(A15:A="",,COUNTIFS('Ticket Log'!A:A,">="&DATE(A15:A,B15:B,1),'Ticket Log'!A:A,"<"&DATE(A15:A,B15:B+1,1),'Ticket Log'!E:E,"Verified")))
Then type Ticket-level CPVR in H14 and put =ARRAYFORMULA(IF(A15:A="",,IFERROR(D15:D/G15:G,))) in H15. This column uses ticket-level costs only; add the month's Costs row when you need the fully loaded figure.
Which charts update on their own?
Select the trend table and insert a combo chart: verified resolutions as columns and ticket-level CPVR as a line on the secondary axis. It redraws as rows are added to the log.
For an in-cell trend of cost per ticket, use:
=SPARKLINE(QUERY('Ticket Log'!A:I,"SELECT SUM(I)/COUNT(B) WHERE A IS NOT NULL GROUP BY YEAR(A), MONTH(A)+1",0),{"charttype","line";"color","#4285f4"})
For a resolver split, chart =COUNTIF('Ticket Log'!D:D,"AI"), =COUNTIF('Ticket Log'!D:D,"Agent") and =COUNTIF('Ticket Log'!D:D,"Escalated"). Read it next to B6: a large AI slice with a low verified rate means the AI is attempting volume, not resolving it.
Sheet 4: how do I track budget against actual?
Name the fourth sheet Monthly Template and lay out 12 rows, one per month. Use it before a quarter starts or ahead of a renewal.
| Month | Forecast tickets | Forecast verified | AI cost budget | Human cost budget | Budget CPVR | Actual CPVR | Variance |
|---|---|---|---|---|---|---|---|
| 2026-08-01 | 1,200 | 720 | 1,250 | 32,000 | =(D2+E2)/C2 | (formula below) | =G2-F2 |
Actual CPVR in G2 divides the month's Costs row (AI base, AI invoice and human pool) by the month's verified tickets, so it measures the same thing as the budget column:
=IFERROR(SUM(FILTER(Costs!B:D,Costs!A:A=A2))/COUNTIFS('Ticket Log'!A:A,">="&A2,'Ticket Log'!A:A,"<"&EDATE(A2,1),'Ticket Log'!E:E,"Verified"),"No data")
Positive variance means each resolution cost more than planned. Copy row 2 down for 12 months; future months return No data until their costs and tickets arrive.
How do I enter per-resolution vendor pricing?
Enter the fee your contract actually charges on each row. These are the published terms RTCI records for three common vendors:
| Vendor | Published pricing | How to enter it |
|---|---|---|
| Intercom Fin | $0.99 per outcome; Procedure handoffs, disqualifications and self-serve routings are also billed outcomes | Put 0.99 on every row the invoice bills, including billed handoffs, not only on verified resolutions. Seats go in Costs!B. |
| Zendesk AI | $1.50 committed or $2.00 pay-as-you-go per automated resolution, with 5 (Support Team, Suite Team) or 10 (Suite Professional) included per agent per month | Enter 0 for resolutions inside the allowance and $1.50 or $2.00 above it. Seats and Copilot go in Costs!B. |
| Gorgias AI | $0.90 (annual) or $1.00 (monthly) per automated interaction inside the plan allowance, $1.50 above it; each interaction also counts as a helpdesk ticket | Put the plan fee plus the allowance in Costs!B (Pro on annual billing: $300 + 190 × $0.90 = $471); enter 1.50 on interactions above the allowance. |
| Human agents | Monthly team cost | Put the pool in Costs!D. Fill Ticket Log!H only if you want per-ticket marginal analysis. |
If you pay a flat monthly AI fee with no per-ticket charge, leave column G at zero and put the fee in Costs!B. Then compute AI CPVR as the AI base fee ÷ B3, because B10 will show zero.
Escalated AI attempts are where trackers and invoices drift apart. If your vendor bills an escalated attempt, keep the fee in column G; if it does not, set it to zero. Match the contract.
Which spreadsheet mistakes distort CPVR?
- Counting contacts as resolutions. A chat that ended in a handoff or silence is not a resolution. The
Verifiedcolumn keeps it out of the denominator.
- Dropping fees on failed attempts. Dividing only the fees on verified rows by verified rows reproduces the vendor's headline and hides what reopened and escalated attempts cost you.
- Mixing AI and human denominators. If the numerator includes human cost, the denominator must include agent resolutions too. Dividing total cost by AI resolutions alone makes AI look cheaper than it is.
- Skipping the re-contact window. A ticket closed today and reopened tomorrow is not verified. Pick 24, 48 or 72 hours, write it down and apply it every month.
- Using the pool average for automation decisions. To decide whether AI should take a category, compare AI CPVR with the marginal cost of one more human ticket in that category. The pool CPVR is for reporting.
- Hard-coding prices. Keep each vendor rate in one named cell so a price change is a one-cell edit.
- Closed ranges. Use
A2:A, notA2:A1000, so formulas keep up as the log grows.
Worked example: one sample, three CPVR numbers
From the 12 sample rows: 6 verified AI resolutions (TK-1001, 1002, 1006, 1007, 1010, 1012), 2 verified agent resolutions (TK-1003, 1008), 1 reopened (TK-1004), 2 escalated (TK-1005, 1011) and 1 pending (TK-1009).
- AI fee on verified rows only = 6 × $0.99 ÷ 6 = $0.99. This is the vendor's headline rate and the number the tracker exists to correct.
- AI CPVR (
B10) = 9 billed attempts × $0.99 = $8.91 ÷ 6 verified AI resolutions = $1.49. The three attempts that reopened or escalated were still billed, so each verified resolution costs 50% more than the headline.
- Fully loaded CPVR for August. Twelve tickets cannot carry a month of fixed cost, so this one needs the whole month. From the
Costssheet, August spend is $500 + $495 + $32,000 = $32,995. If the full August log holds 720 verified resolutions (the forecast in theMonthly Template), CPVR is $32,995 ÷ 720 = $45.83, against a budget CPVR of ($1,250 + $32,000) ÷ 720 = $46.18.
The gap between $1.49 and $45.83 comes from the human pool, which is still paid while the AI handles part of the queue. The AI CPVR tells you what the vendor costs per outcome; the fully loaded CPVR tells finance what each outcome costs the business. Report both, labelled.
How do I keep the tracker current?
Export a monthly CSV from your helpdesk (ticket ID, created date, status, assignee, resolution time, tags) and paste it into Ticket Log, mapping columns to the layout above. A connector such as Zapier, Make or Coupler.io can append rows on a schedule if you prefer not to paste.
Set the Verified status by hand or by a scheduled audit after the re-contact window has passed. An export rarely shows re-contact reliably, because a customer may open a new ticket rather than reopen the old one.
FAQ
What is cost per verified resolution? Total support cost divided by tickets fully resolved with no human handoff and no re-contact inside your verification window.
Why Google Sheets instead of a BI tool? It is quick to set up, easy to share with finance, and every formula is visible to whoever checks it.
How do I pick a verification window? RTCI uses 72 hours. Whatever you choose, write it down, apply it every month, and note when your vendor's definition differs.
Should I include escalated tickets in CPVR? Not in the denominator, because they are not verified resolutions. Their AI fees and human cost do belong in the numerator, because you paid them.
How do I handle flat-fee or per-seat AI pricing? Put the fee in AI Base Cost and leave per-ticket AI cost at zero. Compute AI CPVR as the fee ÷ verified AI resolutions.
Can I track CPVR by ticket category? Yes. Add a category condition to each SUMIFS and COUNTIFS, for example COUNTIFS('Ticket Log'!C:C,"Billing",'Ticket Log'!E:E,"Verified").
What is a good CPVR benchmark? For the vendor fee, RTCI's November 2026 index puts the midpoint AI fee per resolution at $0.13 to $3.27 across 20 platforms. For fully loaded CPVR, compare against your own pre-AI baseline: total support cost ÷ resolved tickets in the months before launch.
How do I show ROI to finance? Compare fully loaded CPVR before and after launch on the same definitions and the same verification window.
What if my vendor counts silences as resolutions? Use your own Verified column. Count only tickets that passed your re-contact window.
Related tools and reading
For the measurement method behind verified resolution, read How to Measure AI Resolution Rate. For vendor pricing side by side, see AI Customer Support Pricing Models Compared. To model CPVR without building the sheet, use RTCI's Cost per Verified Resolution Calculator.
Change log
- Fixed the Total Cost, monthly CPVR and Actual CPVR formulas, made the worked example count every billed AI attempt, corrected Fin's billing note (handoffs can be billed outcomes), and replaced unsourced cost benchmarks with RTCI index figures.
- Published.
Read next
Keep reading
Picked for this article: the pieces it cites and the ones that answer the next question buyers usually ask.

Most related
AI Customer Support Pricing Models Compared: Per-Seat, Per-Resolution, and Usage
AI support vendors price by seat, by resolution or by usage. A worked example at 10,000 conversations a month shows the same 5,000 resolutions costing $874 to $7,200 depending on the price card.

How to Measure AI Resolution Rate Internally
Vendor dashboards report resolution rates that no two vendors calculate the same way. This is a practical, vendor-agnostic methodology to measure your own AI resolution rate using your own ticket data, built on RTCI's 72-hour verified resolution standard.

AI Support Cost Per Resolution by Industry (2026)
How fully loaded cost per verified resolution changes by industry, built from BLS wage data for customer service representatives, published AI pricing and stated assumptions you can replace with your own.

Outcome-Based vs Seat-Based Pricing for AI Support: Which Model Should You Buy In 2026?
A buyer-side guide to choosing between outcome-based and seat-based AI support pricing in 2026, with current rates for Intercom Fin, Salesforce Agentforce, HubSpot Breeze, Zendesk AI and Freshdesk Freddy, a worked cost per verified resolution example, and a contract checklist.

Implementation Checklist: AI Support Contract to Go-Live
A buyer-side checklist from signed contract to the first 30 days of production: seven gated phases, owners, a risk matrix, a go/no-go scorecard and a sample 12 to 16 week plan.