Revenue Leak Detector

Finds the money that slipped through the cracks. Every Monday at 7am Eastern the flow compares the work you did (from a Google Sheet or Acuity Scheduling) with the payments you collected (from a Google Sheet or QuickBooks Online) and, once the grace period has passed (14 days by default), flags two kinds of revenue leak: completed work with no payment, and work that was paid short by more than your tolerance ($1.00 by default), so a deposit on recent work is not flagged. With QuickBooks for payments the flow also reads your invoices and flags a third kind: an overdue invoice, one whose open balance is more than your tolerance at least a day after its due date (the invoice date when it has none). Payments are matched to the same customer by email when both sides have one, and otherwise by name (case, punctuation and spacing ignored). Each customer's payments are applied to their jobs oldest first, and a payment counts toward a job from 7 days before the job date, so deposits are credited. Only jobs and invoices dated in the lookback window (90 days by default) are flagged. A job is not flagged as not collected or paid short when the same customer has an open QuickBooks invoice dated no earlier than 7 days before the job whose open balance covers what the job is missing: that work was billed, and the invoice is either still within its terms or flagged as an overdue invoice instead (each invoice covers one job). For each leak the flow first records it in the Leak Log tab, then queues a polite follow-up email to the customer on the Approvals page, citing the date, the work and the amount due (for an overdue invoice: the invoice number, the due date and the open balance); nothing goes to a customer until you approve it, and a job or invoice already in the Leak Log is never raised again. Up to 25 follow-ups per run (you can change this), largest gap first. When at least one leak is found you also get an email summary: the number of leaks, the total dollars at risk split into not collected, paid short and overdue invoices, and the 10 largest leaks. All amounts are added up by code, never by an AI model. Privacy: each follow-up carries only the customer's name, the date, the work description or invoice number and the amounts; replies go to your connected Gmail inbox. Spreadsheet tabs, header in row 1: Leak Log (written by the flow): A logged on, B job ID (invoice:<QuickBooks invoice ID> for an overdue invoice), C customer, D issue (not collected, paid short or overdue invoice), E expected, F collected, G gap, H status (the flow writes queued for approval; you can update it). With Google Sheets for work, also Jobs, one row per completed job: A job ID, B date, C customer name, D customer email, E description, F amount (rows without a job ID or date are ignored, and so are amounts of 0). With Google Sheets for payments, also Payments, one row per payment received: A payment ID, B date, C customer name, D customer email, E amount, F reference (for your records). Setup: create the tabs, connect Google (Sheets and Gmail) and your work and payment systems, and set the business name, owner email, grace days, short tolerance, lookback days and maximum follow-ups on the setup screen; all but the owner email land in the Settings node, where you can change them later (the owner email is the To field of the summary email node). Save the flow once after importing it to arm the schedule. Limitations: days are calendar days. A leak whose job has no customer email is listed in the summary but not drafted or logged, so it is listed again each week until you add the email or collect. Leaks past the per-run maximum are drafted in later runs. A payment is matched to jobs only, not to a specific invoice, so a customer's overpayment on one job covers their other jobs. Acuity: completed appointments from the lookback window are read (up to 5000), each appointment's price is the amount due, and cancelled and free appointments are ignored; payments taken inside Acuity count only if they also reach your payments system, and the job ID is the Acuity appointment ID. QuickBooks: the most recent 1000 payments, the most recent 1000 invoices and up to 1000 customers are read, since the QuickBooks lists cannot be filtered by date yet, so a company with more payments than that may see false leaks; check each draft before approving. A customer's email comes from the QuickBooks customer record. Invoices are matched to jobs by customer and amount, not by invoice line, and a job ID is never linked to a specific invoice. With Google Sheets for payments no invoices are read, so there are no overdue invoice flags. ServiceTitan, HubSpot and GoHighLevel (for work) and Stripe and Square (for payments) are coming soon; Stripe's payment search needs a customer email or thread, so it cannot list every payment yet.