WhatsApp Automation for Small Businesses in 2026: AI Replies, Lead Capture & Tiered Commissions

A developer built a WhatsApp sales automation system for small businesses using Google Sheets and Apps Script, featuring AI replies, lead capture, and tiered commissions. The key innovation is a commission engine that correctly handles multi-tier calculations across sales and refunds, avoiding common spreadsheet errors.

Your customers would rather message you on WhatsApp than fill in a contact form. That's fine at ten conversations a day. At a hundred, messages get missed, nobody knows which rep is on which deal, and at month-end somebody rebuilds the commission sheet by hand and gets it wrong. The usual answer is a $49–$499/month WhatsApp SaaS platform, priced per seat, with your customer data living in someone else's database. This post is the other answer: the same workflow on Google Sheets + Apps Script — and the one piece I see teams get wrong every single time, with the code to fix it. It isn't the messaging. Wiring a WhatsApp webhook into a sheet is a couple of hours of work, and I've written that build up separately — the webhook, the AI reply, and the lock that stops two reps chasing the same lead are all in Build a WhatsApp Sales Inbox in Google Sheets https://magesheet.com/blog/automating-whatsapp-sales-google-sheets . I won't repeat it here. The part that breaks is the commission math . Someone writes =IF revenue 10000, revenue 0.08, revenue 0.05 into a column, and three things kill it: So that's what this post builds: a tiered commission engine that survives rule changes and refunds. This is the whole trick. Make a Commission Rules tab, one row per rule: rule id | rep id | effective from | effective to | tier 1 cap | tier 1 pct | | | | | tier 2 cap | tier 2 pct | tier 3 pct --------+----------+----------------+--------------+------------+------------+----------- R1 | ALL | 2026-01-01 | | 10000 | 0.05 | | | | | 50000 | 0.08 | 0.10 R2 | rep ayse | 2026-06-01 | | 10000 | 0.06 | | | | | 50000 | 0.09 | 0.12 rep id is either a specific rep or ALL the house default . Percentages are decimals — 0.05 is 5%. When the plan changes, you close the old rule with an effective to date and add a new row. History keeps calculating at the rate that was live on the day of the sale. js function findRule rules, repId, saleDate { const d = new Date saleDate ; const matches = rules.filter r = r.rep id === repId || r.rep id === 'ALL' && d = new Date r.effective from && r.effective to || d <= new Date r.effective to ; // A rule written for this rep always beats the "ALL" fallback. matches.sort a, b = a.rep id === 'ALL' ? 1 : 0 - b.rep id === 'ALL' ? 1 : 0 ; return matches 0 || null; } Here's the bug in every hand-written commission column. A rep has booked $45,000 this quarter and closes another $10,000. Tier 2 ends at $50,000. So $5,000 of that sale earns 8% and $5,000 earns 10% — not $10,000 at one rate. // Commission for ONE sale, given how much the rep already booked this period. // A single sale can span two or three tiers — that's what formulas get wrong. function commissionForSale bookedBefore, amount, rule { const bands = { upTo: Number rule.tier 1 cap , pct: Number rule.tier 1 pct }, { upTo: Number rule.tier 2 cap , pct: Number rule.tier 2 pct }, { upTo: Infinity, pct: Number rule.tier 3 pct }, ; let from = bookedBefore, left = amount, commission = 0; for const band of bands { if left <= 0 break; const room = Math.max band.upTo - from, 0 ; const take = Math.min left, room ; commission += take band.pct; from += take; left -= take; } return commission; } With the R1 rule above: commissionForSale 45000, 10000, R1 returns 900 — $5,000 at 8% plus $5,000 at 10%. The naive formula returns either $800 or $1,000, and your rep notices. Never edit or delete the original row. A refund is its own row with a negative amount and a reverses transaction id pointing back at the sale. Then the engine reverses exactly the commission that sale earned — not a recalculated guess. js function runCommissions transactions, rules { const sorted = transactions.slice .sort a, b = new Date a.date - new Date b.date ; const booked = {}; // rep id - revenue booked so far const earned = {}; // transaction id - commission it earned const out = ; sorted.forEach t = { if t.reverses transaction id { // refund row const original = earned t.reverses transaction id || 0; booked t.rep id = booked t.rep id || 0 + Number t.amount ; // negative out.push { id: t.transaction id, rep: t.rep id, commission: -original } ; return; } const rule = findRule rules, t.rep id, t.date ; if rule { out.push { id: t.transaction id, rep: t.rep id, commission: 0, note: 'no rule' } ; return; } const before = booked t.rep id || 0; const c = commissionForSale before, Number t.amount , rule ; booked t.rep id = before + Number t.amount ; earned t.transaction id = c; out.push { id: t.transaction id, rep: t.rep id, commission: c } ; } ; return out; } Because it walks transactions in date order and keeps a running total per rep, the tier boundaries land where they actually landed in real life. I unit-tested this before shipping it — the cases that matter are a sale spanning all three tiers, a rule that isn't effective yet falling back to ALL , and a refund netting to exactly zero. At small-business volume, the running cost is LLM tokens plus Meta's conversation fees — roughly $0.005–$0.08 per conversation depending on category and country, which for most SMBs lands near $50/month all-in. Compare that to $49–$499/month per platform, plus per-seat fees. The bigger difference isn't the invoice, though: the sheet is yours, and so is the commission logic. WhatsApp's often-quoted ~98% open rate is a vendor benchmark rather than a controlled study — but you don't need the exact number to know the channel outperforms email for a reply-now conversation. reverses transaction id .The messaging half of WhatsApp automation is easy and well covered. The commission half is where teams quietly lose money and trust — and it's solved by two ideas: keep the tiers in a dated rules table, and split each sale across the bands it actually crosses. The production version — multi-agent splits, clawback windows, and the payout approval flow — is written up on the MageSheet blog https://magesheet.com/blog/whatsapp-automation-small-businesses-2026 . Built by the MageSheet team.