Whatsapp Automation For Small Businesses In 2026: Ai Replies, Lead Capture & Tiered Commissions

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.

Where DIY WhatsApp automation actually breaks

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. 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:

1. A single sale spans two tiers — the formula charges the whole amount at one rate.
2. The tiers change in July, and now every historical row recalculates at the new rate.
3. A customer refunds in August on a sale from June, and nobody can unwind it without breaking the audit trail.

So that's what this post builds: a tiered commission engine that survives rule changes and refunds.

1. Put the tiers in a table, never in a formula

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.

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; 
} 

2. Split one sale across tiers

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.

3. Handle refunds without breaking the audit trail

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.

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.

What the rest of the stack costs

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.

Pitfalls

• Don't hard-code tiers in formulas. Rules go in the rules table with effective dates, or your July change silently rewrites June's payouts.
• Don't recalculate refunds. Reverse the commission the original sale actually earned, via reverses_transaction_id.
• Process in date order. Tier boundaries depend on running totals; out-of-order rows produce wrong bands.
• Use an official WhatsApp API (Twilio, 360dialog, Meta Cloud). Unofficial libraries get business numbers banned.
• Apps Script limits: 6-minute execution cap and ~20k UrlFetchApp calls/day (100k on Workspace). Fine for SMB volume; above that, move the compute out and keep Sheets as the store.

Wrap-up

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.

Built by the MageSheet team.