Septic Tank Service KPI Scorecard in Google Sheets

Ask a septic pumping contractor how last month went and you will usually get a story about one bad Saturday. Ask for the numbers and most of them reach for a job book, a treatment-plant invoice and a bank balance. The Septic Tank Service KPI Scorecard in Google Sheets replaces that with one screen: ten septic-service KPIs, each with an actual, a target, the gap in units and in percent, an arrow, a red / amber / green light and a twelve-month mini bar strip. In the December sample that ships with the file, four KPIs are green, three amber and three red – and the reds are the ones costing money.Septic Tank Service KPI Scorecard in Google Sheets

It is a month-picker scorecard built on eight Google Sheets tabs, four of which you actually look at, plus 120 rows of editable monthly data. It is not an analytics dashboard with slicers and pivot pages, and it is not the KPI Dashboard template that carries a similar name in the shop. You pick a month, and the board tells you where the business stood that month against its own targets.

One thing to know before you start: in this build the month dropdown on the Scorecard spells two months in full – “June” and “July” – while the data page stores every month as three letters (Jun, Jul). Pick either of those two and all ten cards read zero, because the lookup matches month names as exact text. The other ten months are fine, and the fix takes about thirty seconds inside your own copy. Full instructions are in the “What we found when we checked this build” section below. We would rather you read it here than discover it in a management meeting.

Key Features of the Septic Tank Service KPI Scorecard

• Ten septic-service KPIs in five groups. Pumping & Service Operations (Tanks Pumped Per Truck Day, First-Visit Completion Rate, Average On-Site Service Time), Compliance & Inspections (Inspection Pass Rate, Health Department Violation Notices), Fleet & Disposal (Vacuum Truck Utilisation, Disposal Cost per 1000 Gallons, Truck Breakdown Downtime), Emergency Response (Emergency Response Time) and Customer & Financial (Average Revenue per Job).
• Three header cells run the whole board. A month cell, an MTD / YTD basis cell and a Target / PY comparison cell. Change one and all ten cards, arrows, percentages and traffic lights recalculate together.
• Direction-aware scoring. Five KPIs are tagged UTB (upper is better) and five LTB (lower is better). On an LTB metric such as Emergency Response Time, a fall is a green arrow, not a red one – which is the difference between a scorecard and a coloured spreadsheet.
• Traffic lights with published bands. For an upper-is-better KPI: at or below target is red, up to 10 percent above target is amber, more than 10 percent above is green. For a lower-is-better KPI the bands are mirrored.
• MTD and YTD side by side in the data. Each KPI carries actual, target and prior year for both bases, all twelve months.
• A twelve-month bar strip on every card, so you can see the shape of the year without leaving the board.
• A KPI Trend page with its own dropdown, two twelve-month charts and the KPI’s formula and definition printed above them.
• Formulas only. SUMIFS over named ranges, no macros, no add-on, no permission prompt on first open.

The Four Pages, Explained

Page 1 – the Scorecard

The board itself. A dark header carries the template name and the three pickers – Select Month, MTD or YTD, and Vs. Target or PY – and below it sit ten cards in two rows of five. Every card gives you the KPI name, a coloured dot, the number in large type, the target value, the change in units, the change as a percentage with an arrow, and a twelve-bar strip of the year.

In the December sample that ships with the file: Tanks Pumped Per Truck Day 8.5 against a 7.3 target (+1.2, +16.4%, green); First-Visit Completion Rate 84.1 against 72.3 (+11.8, +16.3%, green); Average On-Site Service Time 1.7 hours against 1.6 (+0.1, +6.2%, amber – because on a lower-is-better metric, up is the wrong way); Inspection Pass Rate 95.9 against 82.5 (+13.4, +16.2%, green); Health Department Violation Notices 1.5 against 1.3 (+0.2, +15.4%, red); Vacuum Truck Utilisation 77.1 against 74.0 (+3.1, +4.2%, amber); Disposal Cost per 1000 Gallons 62.2 against 53.5 (+8.7, +16.3%, red); Truck Breakdown Downtime 21.2 hours against 20.4 (+0.8, +3.9%, amber); Emergency Response Time 3.3 hours against 3.6 (-0.3, -8.3%, green); and Average Revenue per Job 403.6 against 427.8 (-24.2, -5.7%, red). Four green, three amber, three red – we recomputed every one of those changes and percentages from the underlying monthly rows before writing this.

Page 2 – KPI Trend

One KPI at a time, for when a card turns red and somebody asks whether it is a trend or a bad month. Pick the metric from the dropdown and the page fills in its group, its unit, its UTB / LTB type, its formula and its definition, then draws two clustered column charts: Actual vs Target vs PY on an MTD basis and the same on a YTD basis, January to December, with Actual and prior-year columns and a target line across the top.

Page 3 – the KPI definitions

A ten-row table with the number, the KPI group, the KPI name, the unit, the formula written out in words, a plain-English definition and the UTB / LTB type. This is the page that stops the monthly meeting turning into an argument about what a number means: Tanks Pumped Per Truck Day is total tanks pumped divided by truck working days, Disposal Cost per 1000 Gallons is treatment plant tipping fees divided by gallons disposed, times a thousand, and so on. It is also the page you edit when you want your own KPIs – change the name, the unit, the wording and the direction flag, and the Scorecard and Trend pages follow.

Page 4 – the monthly data

The only page you type in. Ten blocks labelled KPI-1 to KPI-10, each with the KPI name repeated down the rows, a Month column running Jan to Dec, then MTD Actual / Target / PY and YTD Actual / Target / PY. 120 data rows in total. Replace the sample numbers with yours, keep the month labels as they are, and every other page updates.

What We Found When We Checked This Build

We open every file before we write about it. Four things in this one are worth your attention. None of them are structural – the ten KPIs are consistent across the Scorecard, the definitions page and the data blocks, every change figure recomputes correctly, and all ten direction flags match their metric – but you should know them going in.

1. June and July are mis-labelled in the month picker. The dropdown offers Jan, Feb, Mar, Apr, May, June, July, Aug, Sep, Oct, Nov, Dec, while the Input Data page stores Jun and Jul. Because each card looks its month up as exact text, picking June or July matches no rows: all ten cards drop to zero, the percentage changes vanish and the traffic-light cells error out. The fix: in your own copy, select the month cell on the Scorecard, go to Data > Data validation, and edit the list so it reads Jun and Jul. Or go the other way and rename Jun to June and Jul to July in the Month column on the Input Data page – just make both sides agree. Either edit takes under a minute and the two months work from then on.
2. The Read Me tab ships blank. The tab is there; nothing was written into it. Nothing else depends on it – the definitions page carries the substance – but if you are handing the file to a colleague, write your own two lines into it before you do.
3. Color Settings is documentation, not a control panel. That tab prints the RAG variance table, but the numbers the cards actually read live on the Support tab, off to the right of the calculation block. Change the values on Color Settings and the board does not move. If you want different thresholds, edit the RAG table on the Support tab. (The shipped bands – 10 percent either side of target – are reasonable defaults, so most people will not need to.)
4. The bar strip colours by size, not by direction. On each card the tallest month is picked out in dark green and the shortest is marked in red. On Tanks Pumped Per Truck Day that reads correctly. On Health Department Violation Notices, Truck Breakdown Downtime, Emergency Response Time or Disposal Cost per 1000 Gallons – all lower-is-better – the dark green bar is the worst month, not the best. The bar heights are accurate; only the highlight colour ignores direction. Take the verdict from the arrow and the dot, which are both direction-aware, and read the strip for shape.

Septic Tank Service KPI Scorecard vs. Excel vs. Paid Field-Service Software – Feature Comparison

How the Google Sheets and Excel Editions Differ

An Excel edition of this scorecard is being prepared and is not on sale yet, so there is nothing to link to here. It is worth saying clearly, though, that it is not a conversion of this file. The two editions were built separately and they carry different KPIs.

This Google Sheets edition runs Tanks Pumped Per Truck Day, First-Visit Completion Rate, Average On-Site Service Time, Inspection Pass Rate, Health Department Violation Notices, Vacuum Truck Utilisation, Disposal Cost per 1000 Gallons, Truck Breakdown Downtime, Emergency Response Time and Average Revenue per Job, in five groups, across eight tabs, with 120 rows of monthly data and two comparison bases.

The Excel edition runs Tanks Pumped, Jobs Completed, Avg Job Duration, On-Time Arrival %, First-Time-Fix %, Callback Rate %, Inspection Pass %, Revenue per Job, Disposal Cost/Job and Customer Retention %, in four groups, across nine sheets, with twenty numbered KPI blocks in its data sheet, a third comparison basis (prior month) and an extra KPI Analysis page carrying a red / amber / green counter, achievement by group and top-and-bottom-five rankings.

So: this edition leans on fleet and emergency-response metrics; the Excel one leans on service-desk and retention metrics. Where a theme repeats, the arithmetic still differs – disposal cost per thousand gallons here, disposal cost per job there. Pick the one whose vocabulary matches your own, or run both if you want the wider coverage.

Who Should Use This Template

It fits owner-operators and small-to-mid septic pumping, portable-sanitation and liquid-waste companies running one to ten vacuum trucks; office managers who rebuild the same monthly summary by hand; and operations leads who want throughput, fleet, emergency-response and money numbers on one page for a monthly review.

It does not fit anyone who needs live data from a dispatch or accounting system – nothing here connects or imports; anyone who wants alerts when a KPI turns red – there are none; anyone who needs per-truck, per-driver or per-customer drilldown – this reports at company level by month; or anyone looking for a regulatory record.

That last point deserves a paragraph of its own. Two of the ten KPIs have regulatory-sounding names – Inspection Pass Rate and Health Department Violation Notices – and it would be easy to read more into them than is there. They are management summary numbers that you calculate and type in yourself. Neither is an inspection record, a permit or manifest register, a disposal or effluent log, or evidence for a regulator, a health department or a certifying body. This template offers no environmental, permitting, wastewater or public-health guidance of any kind. Whatever record-keeping your local rules require, keep doing exactly what you do today; this sheet is a management review board and nothing more.

Real-World Use Cases

The monthly review at a three-truck company. December reads four green, three amber, three red. Production is fine – 8.5 tanks per truck day against a 7.3 target, first-visit completion at 84.1 against 72.3. The red that matters is Average Revenue per Job: 403.6 against a 427.8 target, down 5.7 percent. The crews are not the problem; pricing and job mix are, and the board says so in about four seconds.

The fleet argument, settled. Vacuum Truck Utilisation is amber at 77.1 against 74.0 – above target, but by only 4.2 percent, so the light stops short of green. Next to it, Truck Breakdown Downtime is amber at 21.2 hours against 20.4, and Disposal Cost per 1000 Gallons is red at 62.2 against 53.5, up 16.3 percent. All three sit in the Fleet & Disposal group, and read together they say the trucks are busy and expensive rather than idle – which points at maintenance scheduling and tipping fees, not at buying another truck.

The service-quality read. Emergency Response Time is green at 3.3 hours against a 3.6 target, down 8.3 percent – the one KPI on this board that improves by falling, and a good example of why the direction flag matters. Average On-Site Service Time is amber at 1.7 hours against 1.6, up 6.2 percent. Together they say crews are arriving faster and staying longer, which is a scheduling question. The KPI Trend page then shows whether that is a trend or one December.

Advantages of This Scorecard

• One screen replaces a stack of monthly reports, and it opens in a browser on any device.
• The scoring is direction-aware, so lower-is-better metrics are not read backwards – a mistake that is easy to make in a hand-built spreadsheet.
• Every KPI carries a written formula and definition, which is what keeps a monthly meeting about the business rather than about the arithmetic.
• The MTD / YTD switch means the same file answers both “how was last month” and “how is the year”.
• Nothing is a macro or a script, so there is no security prompt and nothing to trust.
• A one-time price with no seat count, against a subscription that charges per technician.

Opportunities for Improvement

• The June / July dropdown labels need the thirty-second correction described above. This is the one that would actually catch you out.
• The Read Me tab is empty and worth filling in before you share the file.
• The RAG bands are editable but live on the Support tab rather than the Color Settings tab where you would look for them.
• The card bar strips highlight the biggest month regardless of whether big is good for that KPI.
• There is no automatic red / amber / green counter or ranking page – you read the ten dots yourself. (The Excel edition adds one.)
• Ten KPI card slots is the ceiling on the Scorecard page; you can rename them freely, but not add an eleventh card without rebuilding the layout.

Best Practices

• Fix the month list first, before you type a single figure. It is the one edit that everything else depends on.
• Set honest targets. The traffic lights are only as meaningful as the target column; a soft target buys you a green board that tells you nothing.
• Fill the prior-year columns if you have the history. The Vs. PY switch and both trend charts become far more useful once they have something to compare against.
• Change the direction flag whenever you rename a KPI. If you swap a cost metric into a slot that was tagged UTB, the light and the arrow will both lie to you.
• Review on a fixed day. The board is built for a monthly rhythm – first Monday, same three questions, ten numbers.
• Share view-only with anyone who is not maintaining the data, so nobody edits a target in the middle of a meeting.
• Keep one copy per year. Twelve months is the shape of the file; roll to a fresh copy in January and keep the old one as history.

Explore Relevant Templates

• Septic Tank Service KPI Scorecard in Google Sheets – the template this article describes.
• Swimming Pool Service KPI Scorecard in Google Sheets – the closest neighbour: same board, another route-based service trade.
• Plumbing Business KPI Scorecard in Google Sheets – a natural companion if septic work sits beside a plumbing arm.
• HVAC Contractor KPI Scorecard in Google Sheets and Roofing Contractor KPI Scorecard in Google Sheets – for multi-trade operators.
• Fencing Contractor KPI Scorecard in Google Sheets, Paving Contractor KPI Scorecard in Google Sheets and Concrete Contractor KPI Scorecard in Google Sheets – the same board across the contracting trades.Septic Tank Service KPI Scorecard in Google Sheets

Frequently Asked Questions

Is this the KPI Scorecard, the KPI Dashboard or the analytical Dashboard?

The KPI Scorecard. It is a month-picker board of ten KPI cards with traffic lights, plus a trend page, a definitions page and a typed data page. The analytical Dashboard family is chart-and-slicer driven over a transaction table, and the KPI Dashboard family is a third layout again. The names are similar in the shop, so check that the title ends in KPI Scorecard in Google Sheets.Septic Tank Service KPI Scorecard in Google Sheets

What do I actually download?

A PDF carrying the link to the live Google Sheets template. Open the link, then use File > Make a copy, and the sheet lands in your own Google Drive as your copy. There is no ZIP and nothing to install.Septic Tank Service KPI Scorecard in Google Sheets

Are June and July really broken?

In the file as shipped, yes – the picker spells them in full while the data page uses Jun and Jul, so those two months match nothing and every card reads zero. It is a labelling mismatch rather than a broken formula, and you correct it in your own copy in well under a minute through Data > Data validation on the month cell.Septic Tank Service KPI Scorecard in Google Sheets

Does it connect to my dispatch or accounting software?

No. No integrations, no imports, no API, no sync. You type or paste monthly totals into the Input Data page. That trade-off is what keeps it a one-time 13.99 sheet instead of a subscription.

Will it alert me when a KPI goes red?

No. There are no alerts, emails or reminders. The lights are visual, on the sheet, while you are looking at it.Septic Tank Service KPI Scorecard in Google Sheets

Is the Inspection Pass Rate a compliance record?

No. It, and the Health Department Violation Notices count beside it, are summary figures you work out and type in. They are not inspection records, permit or manifest registers, disposal logs or evidence for any regulator or health department, and this template gives no environmental or permitting guidance. Keep your statutory records exactly as you keep them now.

Can I swap in my own KPIs?

Yes. Name, group, unit, formula text, definition and UTB / LTB type are all editable cells on the KPI Definition page, with a matching twelve-month block per KPI on the data page. Ten card slots is what the Scorecard shows.Septic Tank Service KPI Scorecard in Google Sheets

Do I need a paid Google Workspace account?

No. A free Google account is enough, and the copy is yours with no expiry and no seat count.

How long does setup take?

Minutes if you have your numbers to hand: make the copy, correct the two month labels, then paste twelve months of figures per KPI into the data page. The rest is formulas.Septic Tank Service KPI Scorecard in Google Sheets

About the Author

Written by PK – Microsoft Certified Professional with 15+ years of Excel, Google Sheets and Power BI experience, founder of NextGenTemplates and NeoTechNavigators, with 300K+ subscribers across the YouTube channels. Every template is hand-built, opened and checked before it is written about.Septic Tank Service KPI Scorecard in Google Sheets

Conclusion

The Septic Tank Service KPI Scorecard in Google Sheets does one job well: it turns a month of septic-service work into ten numbers with targets, arrows and traffic lights, in a file that opens in a browser and costs less than a tank of fuel. Fix the two month labels, replace the sample data with your own, set targets you actually believe in, and you have a monthly review that takes ten minutes instead of an afternoon. It will not dispatch a truck or raise an invoice – buy field-service software for that – but for the question “how did last month go, and against what”, it answers on one screen.Septic Tank Service KPI Scorecard in Google Sheets