Build a loyalty ROI sheet in fifteen minutes

The sheet is fourteen rows and one column

A loyalty ROI sheet is fourteen rows in a single column. Twelve of them are arithmetic or numbers you can look up this afternoon. Two of them are guesses, and those two decide whether the answer you get is honest or flattering.

Rows 1 to 3 are reach. Monthly distinct customers: 600, for the small coffee shop in this example. Enrollment rate: 25%. Enrolled cardholders: 150, which is just row 1 times row 2.

Rows 4 to 7 are the gain. Incremental visits per cardholder per month: 0.4, and this is guess one. Average ticket: 22 SAR. Gross margin: 65%. Extra gross margin per month: 150 x 0.4 x 22 x 0.65 = 858 SAR.

Rows 8 to 12 are the cost of the rewards. Baseline visits per cardholder per month: 2.0, so cardholders make 2.4 visits each and trigger 150 x 2.4 = 360 stamps a month. At 6 stamps per reward that's 60 rewards earned. Redemption rate: 70%, and this is guess two, so 42 rewards get handed over at 8 SAR of goods each, or 336 SAR. Row 13 is the subscription, 85 SAR. Row 14 subtracts: 858 - 336 - 85 = 437 SAR a month.

Keep every guess in its own cell and never bake one into a formula. You are going to change them, repeatedly, and that is the point of building the sheet at all.

The rows you can look up today

Row 1 is distinct customers, not transactions. Most tills report transactions, so divide by your honest estimate of visits per customer per month and put that estimate in the cell beside it rather than hiding it inside the number.

Average ticket and gross margin come from your own books. Use goods cost only for margin: rent and salaries don't change when one person visits one extra time. A 22 SAR drink at 65% margin implies about 8 SAR of ingredients, which is conveniently also what the free reward costs you.

Enrollment rate is the only row you can move this week, because it's a counter behavior rather than a customer behavior. It depends almost entirely on whether your cashier asks. With Waya the customer scans a QR code, types a first name and a mobile number, and taps once to add the pass to Apple Wallet or Google Wallet, with no app and no account, so the ask is about ten seconds long. Start at 25% and replace it with your own figure by dividing new cardholders in the dashboard by your till's customer count for the same week.

Row 13 depends on the vendor. Waya's free tier is 0 SAR forever for up to 100 customers and 100 wallet messages a month, so any model that stays under 100 enrolled customers has 0 SAR in row 13; above that it's 85 SAR a month for Growth. For comparison, as of August 2026 Btaqa publishes tiers at 108, 250, and 380 SAR a month, OneCup is about 150 SAR, Watily is 149 SAR with loyalty bundled into a wider plan, and Niqati, the best-known name in the Saudi market, quotes per merchant. Check each vendor's live pricing page before you type a number into that cell.

Guess one: incremental visits, not repeat visits

This is the cell where most sheets quietly break. Owners fill it with their returning-customer count. A cardholder who came twice a month before the card and still comes twice a month has produced zero incremental visits, no matter how many stamps are on the pass.

The only defensible way to fill row 4 is a comparison. Record each cardholder's visits per month for the two months before you enrolled them and the two months after, then use the difference. If you have enough volume, leave a slice of customers unenrolled for a month and compare against them instead.

In this example the break-even sits at 0.182 incremental visits per cardholder per month. That is roughly one extra visit per enrolled customer every five and a half months. It sounds like a low bar, and for a lot of shops it genuinely is. That low bar is the real argument for running a stamp card, and it is much more convincing than any percentage a vendor quotes you.

Now set row 4 to zero and read row 14: minus 365 SAR a month. That isn't a broken sheet. It's 280 SAR of free drinks handed to people who were already coming, plus 85 SAR of subscription. A loyalty card that changes nobody's behavior is a permanent discount with extra steps.

Guess two: breakage booked as profit

Breakage is the share of earned rewards nobody ever claims. It's real money saved, and it's also the cell owners use to make a weak model look strong.

Leave the base case untouched and move redemption from 70% down to 40%. Reward cost falls from 336 SAR to 192 SAR and the monthly net jumps from 437 SAR to 581 SAR. Nothing improved inside the shop. You simply told the sheet that more of your customers gave up on the card.

That's the double-count. High breakage usually means the reward sits too far away, and a reward that's too far away also fails to change behavior. Run the honest version of that trade: push the card from 6 stamps to 10, which cuts redemption to 40% but also drags incremental visits from 0.4 down to 0.25. Reward cost drops by 228 SAR a month, and net still falls by 94 SAR, from 437 SAR to 343 SAR. The savings are real and smaller than the lift you gave away to get them.

So model breakage low in the column you actually make decisions on. A 20% to 30% breakage assumption is a safe planning range for a 6-stamp card. If the program clears its cost while most rewards get redeemed, it survives contact with reality. Replace the guess with your measured number after three months.

Run three columns, then decide

Copy the column twice and change exactly one cell: row 4. Pessimistic at 0.1 incremental visits, base at 0.4, good at 0.7.

The three nets are minus 165 SAR, plus 437 SAR, and plus 1,039 SAR a month. Same customers, same ticket, same reward, same subscription. The entire answer swings on one number you guessed, which is why a single-column sheet showing a big positive is worth nothing.

Here's the decision rule that keeps you out of trouble: launch if you can live with the pessimistic column for three months. In this example the downside is 165 SAR a month, less than one day of revenue for this shop, which is cheap enough to buy real data with. If the pessimistic column is a number that frightens you, shorten the card or cut the reward cost before you launch, not after.

Then update the sheet on the first of every month. By month three, rows 2, 8, and 11 have stopped being assumptions, and only row 4 is still an estimate. By then you have your own before-and-after numbers to anchor even that one.

What the sheet can't tell you

Waya has no POS integration and no hardware, so rows 1, 5, and 6 come off your till while rows 3, 9, and 10 come off the Waya dashboard. You divide them by hand once a month, which takes about five minutes. If a vendor does integrate with your till, use it, but don't pay for an integration whose output you were never going to read.

Three real things sit at zero in this model. The mobile numbers you now hold. The dashboard view of which regulars have gone quiet, and the wallet message you can send to their lock screen from the same screen. And the ten seconds per enrollment your cashier spends. None of them belong in row 14 until you can price them, and pretending otherwise is how you end up with a sheet you don't trust.

There's no SMS anywhere in this model either. Messages arrive as Apple Wallet and Google Wallet notifications, so the sheet needs no per-message cost row, only a monthly cap: 100 messages on Free, 5,000 on Growth.

One last thing the arithmetic hides. Most loyalty programs don't fail on economics. They fail because the cashier stops asking in week three, and that failure shows up in row 2, not row 14. Watch the enrollment rate weekly and the ROI mostly takes care of itself.