How We Built a System That Hands Out Prizes on Its Own, Every Month, Without Spreadsheets
A system that, every month, counts the valid uses of each referrer's code, assigns prizes by TIER, checks stock and leaves the gift order ready in the ERP. A person only approves it.
In the Chilean eCommerce business we built this for (a retail store and a wholesale one, with two warehouses of their own), around three hundred people recommend the store with their own discount code. Rewarding them was manual work: checking who had sold the most, hunting for gifts that were in stock, putting together each order and chasing addresses over chat.
We rebuilt it as a system that runs itself. The core decision was this: the rules are written down before the month starts, the system does the math, and a person steps into each prize at a single point: approving it.
First, define what counts
The whole system rests on one definition: the valid use. Count it wrong and you reward wrong, so we wrote it before anything else. An order counts if it meets all of this:
- It's paid and has the code applied.
- It wasn't voided or fully refunded.
- It isn't a self-purchase: it doesn't match the code holder's tax ID, email, phone or address.
- It's the only one from that customer in the month: a customer who buys four times adds one use, not four.
The calculation lives in the online store, where the orders are, and the prize system queries it in batches. If an order is refunded after the count, it's deducted the following month: we decided a closed month is never touched.
Why we count on day 8, not day 1
The first version of the plan counted on day 1. We moved it to day 8: that week leaves room for end-of-month orders to be voided or refunded before counting. Counting on day 1, we would have rewarded orders that later don't exist. On day 8, an automatic process:
- Freezes the previous month's count, with its orders and each referrer's TIER.
- Creates a prize for every winner who accepted the program's terms.
- Sends them the "You won a prize!" email, with 5 days to confirm their shipping address. If they don't, the address on file is used.
No automatic process runs without the terms accepted. Anyone who hasn't accepted them still adds up uses but doesn't win, and the prizes screen flags them so someone can let them know.
TIERS with fixed thresholds, not a ranking
The first design ranked participants by number of uses and handed out TIERS by position. We switched it to fixed thresholds for two reasons: each referrer knows which goal to chase, and stock can be estimated before the month ends.
- TIER1: 20 valid uses or more.
- TIER2: 15 to 19.
- TIER3: 10 to 14.
Everyone who reaches a threshold gets that TIER's prize; they don't compete with each other. Thresholds can be adjusted in the settings. On top of that there's an extra prize for whoever had the most uses that month.
Two rules settle the edge cases:
- A tie for first place: everyone tied gets the extra prize, and the system checks there's enough stock for all of them.
- Nobody reached the minimum: the extra prize isn't lost; it rolls over to the next month.
Gifts come from the stock worth moving
Choosing gifts was the slowest part. Now the person in charge sets up the month on a single screen: the products for each TIER, the extra prize, and an equivalent backup prize in case stock runs short. They search by name or by SKU (each product's internal code) and add each one as a chip with its quantity. We use the same picker on every screen of the system where products are chosen.
Next to it, suggestions show up from the system that flags what will expire before it sells. We read them straight from its tables, read-only. They're products with free stock in both warehouses that meet any of these conditions:
- they expire in 120 days or less;
- they arrived at zero cost;
- they're flagged as "expires with stock", "slow mover" or "excess stock".
A product that was going to sit in the warehouse ends up in the hands of someone who recommends it. While the month is being set up, the system estimates how many winners there will be, works out the units needed for each SKU and compares them with real stock in the ERP.
The quote builds itself in the ERP
Once the address is confirmed, or the deadline passes, the system builds the quote in the ERP: the winner's contact with their shipping address, each product at retail price with a 100% discount, and the prize tags, the TIER and "Extra prize" when it applies. That way every prize has a value attached and the program can be measured.
Before creating it, it checks stock again, because a quote doesn't reserve units. If the extra prize is short, it uses the backup and lets the person in charge know. If a TIER product is short, the quote is created anyway and a notice goes out to sort it.
Two technical decisions hold this up:
- Nothing gets duplicated. Each prize leaves a unique mark on the quote, and the system looks for it in the ERP before creating another. Running the process twice doesn't create two orders or send two emails.
- The address lives in one place. Each referrer edits it in their profile and the change is also made in the ERP, as their contact's shipping address. Nobody copies it by hand.
A single approval, with deadlines
The quote shows up in the "Gifts to approve" inbox. An administrator reviews and approves it, and whoever authorizes spending confirms it in the ERP. From there the system carries on by itself:
- If the quote is confirmed in the ERP, the prize moves to "Authorized"; if it's cancelled, to "Rejected".
- When the shipment has a tracking number, the winner gets an email with it.
- After 48 hours without authorization a reminder goes out; at 96, the notice escalates to administration and management.
Each referrer sees their prize's status in their profile, without amounts.
What we left manual on purpose
Automating everything wasn't the goal. We left to a person whatever needs judgment or a relationship:
- Approving the gift. The system builds it, but someone signs off on the spending.
- Accepting each participant. They apply on their own, but get in with an administrator's approval.
- Sorting out a stock shortage. Only when the system flags it.
- Posting to the WhatsApp group. The system builds two graphics a month with the product's real photo (on the 15th, the extra prize; on the last business day, each TIER's prizes) and AI only generates the background, with no products, no text and no one's data. The person in charge reviews it, downloads it and posts it. The system reminds them when, but it never writes on WhatsApp for anyone.
What the business gains
Rules known before the month starts and math nobody does by hand. Each referrer knows which goal to chase, the person in charge goes from building orders to approving them, and the gifts come from the stock most worth moving. Every prize is measured at retail price and at cost, alongside the sales and new customers each code brought in, just like in the system where every discount code is held to account.
If your referral program is still rewarded by hand, start with an AI diagnosis for your company.
Contact me