The single decision that determines whether a loyalty migration goes quietly or turns into a week of angry DMs: export the transaction ledger, not the balance column. Almost every team that sets out to migrate a loyalty program to Shopify pulls a CSV of customer emails and current point totals, imports it, and calls it done. Then someone with 4,000 points discovers they have 3,600, posts a screenshot, and you spend launch week doing forensics on a system you have already switched off.
The balance is a derived number. The ledger is the evidence. If you only have the balance, you have no way to answer "why is mine wrong" after go-live, and no way to honour expiry dates correctly.
Freeze earning before you export anything
Pick a cutover window and stop the old programme from writing new rows. On a Magento extension or a WooCommerce plugin that usually means disabling the earn rules; on a hosted loyalty tool it means turning off the campaigns and, if the API allows it, revoking write access. On an in-store POS that awards points offline, it means telling the store staff, in writing, on the day.
Skipping the freeze is what causes the classic drift: you export on Tuesday, import on Friday, and the 900 orders in between earned points into a system nobody is going to look at again. Either freeze earning, or plan a delta export and a second reconciliation pass. Both are fine. Doing neither is not.
Take a snapshot at the same moment — full ledger, customer records, tier assignments, active reward redemptions, and any pending point grants. Keep it as raw files, untouched, somewhere you can go back to in three months. We have needed the raw export a year after cutover more than once.
What the outstanding balance actually costs
Before you spend developer hours on this, price the liability. Suppose the old system reports 12.6 million points outstanding across your member base, and your redemption rate is 100 points to ₹25 off. That is ₹31.5 lakh of face value.
Now apply the number that matters, which is historical redemption. If only 18% of issued points have ever been redeemed, your realistic exposure is closer to ₹5.7 lakh. That figure decides how much effort the reconciliation deserves and how generous you can afford to be when the data disagrees with itself.
Run the same calculation on the top decile separately. In most programmes we have audited, a small group of accounts holds a disproportionate share of the balance — and those are exactly the customers who will notice a discrepancy of 200 points. Precision matters at the top of the file. The long tail of accounts holding 40 points does not need the same care.
Sum the ledger, compare to the balance, then bucket the gaps
Add up every ledger row per customer. Compare that total to the balance the old system reports. They will not match. They never do.
A worked example from a reconciliation we ran on a fashion catalogue: ledger sum came to 12.18 million points against a reported 12.6 million. Gap of 420,000 points, or ₹1.05 lakh at face value, spread across about 2,900 accounts. Every one of those gaps fell into one of four buckets.
- Expired points that were deducted from the balance but never written as a ledger row. Common in older extensions that ran expiry as a batch UPDATE on the balance field. The points are legitimately gone; the ledger just doesn't say so.
- Manual adjustments made in an admin panel — support staff topping someone up after a complaint. Often logged with no reason code, sometimes not logged at all.
- Points awarded on orders that were later cancelled or refunded, where the clawback hit the balance and not the ledger, or hit the ledger twice.
- Duplicate accounts that were merged at some point, leaving orphaned rows attached to a customer ID that no longer exists.
Bucket first, then rule. Our default rule is to favour the customer wherever the difference is under a threshold you set in rupees, not points — say ₹150 of face value. Above that threshold, look at the account individually. It is a smaller list than you think, and it is cheaper than the support tickets.
Expiry is where earned value disappears quietly
This is the part that gets missed, and it is the part that generates the loudest complaints six months later.
Old systems and new apps rarely agree on how points expire. If the legacy programme expired points 12 months after they were earned, points are held in dated lots. If the Shopify loyalty app you have chosen expires points 12 months after the customer's last activity, importing a single lump balance resets everyone's clock — you have just extended your liability, possibly by years. Go the other way and customers lose points they thought had another eight months to run.
Check what your chosen app can actually accept before you design the import. Some accept only a flat balance with no expiry data. If that is the case, you have three honest options: import the balance and reset expiry for everyone, import only the unexpired portion and communicate that clearly, or split the import into batches with staggered manual expiry dates. Pick one deliberately. What you cannot do is import a lump sum, say nothing, and hope nobody does the arithmetic.
Tier mapping: nobody gets demoted on launch day
Tier logic is almost never portable. Legacy programmes tend to assign tiers on lifetime spend. Modern Shopify loyalty apps lean towards rolling 12-month spend or points earned in a period. Those produce different answers for the same customer, and the difference is not random — it hits your older, previously-loyal customers hardest.
Map tiers by name and by threshold, side by side, and then run the new rules against your Shopify order history to see who moves. Two things to do with the output:
- Grandfather every customer into their current tier for one full cycle, regardless of what the new rules say. Set an explicit expiry on the grandfathering, and tell them when it ends.
- Where the new rules promote someone, let it happen immediately and send them an email about it. Free goodwill.
If your old programme had four tiers and the new app supports three, decide which two merge before launch, not after. Merging upward is the only version that doesn't cost you trust.
Phone numbers, emails, and the duplicate problem
Indian loyalty programmes are usually keyed on mobile number, because that is what customers give at the till. Shopify customer records are keyed on email. That mismatch is where a large chunk of migration effort goes.
Expect three failure modes. A member with a phone number and no email, so there is nothing to attach points to. A member whose phone number maps to two Shopify customer records, one from a guest checkout and one from an account. And the reverse — one email, three phone numbers, three separate point balances that should be one.
Normalise phone numbers to E.164 (+91XXXXXXXXXX) before matching. Strip the leading zeros and the country-code duplication that creeps in from POS entry. Then match on phone first, email second, and put everything that fails both into a manual review list. On a mid-size programme that list is typically a few hundred rows, and it is worth an afternoon of somebody's attention.
Where a customer has multiple records, merge in Shopify first, then import the combined balance. Doing it the other way round means importing points onto a record you are about to delete. This gets messier if you run Shopify POS across several locations, because point-earning events can arrive from more than one till and the timestamps drift. Reconcile per location, then combine.
COD cancellations and the clawback question
If a meaningful share of your orders are cash on delivery, decide when points are earned. Awarding at order creation is friendlier and looks better in the customer's account; it also means every RTO leaves points in circulation that were never paid for. On a catalogue with 30% RTO, that is a real number.
Award on fulfilment, or better, on delivery confirmation from your shipping aggregator. If the legacy programme awarded on order creation and never clawed back, you are importing points against orders that never delivered. You can either eat that as a one-time cost — usually the pragmatic call — or exclude points tied to cancelled and returned orders and be ready to explain it. Eating it is faster and generates zero tickets.
One more thing worth raising with your CA rather than guessing: whether a point redemption appears on the tax invoice as a discount on the taxable value or as a payment against the total changes what GST is charged on. Get the invoice template right before launch, not during the festive rush.
Dry run into a development store, then import for real
Import into a dev store or a sandbox first. Every time. The order we use:
- Load the reconciled file into a development store with the same loyalty app installed on a trial plan.
- Verify the total imported balance matches the reconciled total to the point. Not roughly. Exactly.
- Hand-check the top 20 accounts by balance and a random sample of 50 more against the raw export.
- Place test orders that earn, redeem, partially refund and fully refund, and watch what the ledger does each time.
- Then run the production import, in batches, with a single adjustment row per customer carrying a memo like "Migrated balance as at 14 Nov".
That memo is worth the effort of adding it. When a customer queries their balance in February, one line in the ledger answers the question in ten seconds instead of forcing an archaeology expedition. Bulk imports also hit API rate limits on larger member bases, so batch the job and build in retries rather than firing 40,000 requests at once and losing track of which ones failed.
Keep the old system readable, not writable, for at least 60 days after cutover. Read-only access has settled more disputes for us than any amount of pre-launch testing.
Sometimes the right call is not to migrate the loyalty program at all
If your ledger is genuinely unrecoverable — no transaction history, no expiry dates, a balance field that has been edited by hand for four years — reconciliation can cost more than the liability. Do the arithmetic before you commit.
Take a programme with 6,800 members holding an average of 240 points, at ₹0.25 per point. That is 1.63 million points, ₹4.08 lakh of face value. Apply a 20% historical redemption rate and the real exposure is around ₹82,000. If a clean reconciliation is going to take three weeks of developer and analyst time, you are spending more than the liability to protect it.
The alternative: close the old programme, issue store credit to everyone with a balance above a floor you can defend, and launch the new programme fresh with a joining bonus. Be careful with gift cards specifically — they are redeemable without a purchase and typically carry no expiry, so a blanket conversion can create a larger liability than the points did. Store credit or a percentage-off code tied to a minimum order value is usually the safer instrument.
Whatever you choose, email the members before launch day rather than after. "Your 240 points have become ₹60 of store credit, valid until 31 March" is a message people accept. Discovering the same thing from an empty rewards widget is not.
Launch week
Watch three things. Total point balance across the new app, checked daily against the imported figure plus expected earning. The support inbox, filtered for the word "points". And the redemption flow at checkout, tested on mobile, on a COD order, with a discount code already applied — because point redemption stacking with existing discounts is where loyalty apps break most often.
If the wider replatform is still being planned, the loyalty ledger belongs in the data audit alongside customers and orders, not as an afterthought two days before cutover. Our notes on sequencing a Shopify migration cover where it fits, and if you would rather have someone else own the reconciliation, that is the kind of work a Shopify developer working on your data can take end to end.
Start by exporting your point ledger today and running one query: sum the rows per customer and compare it to the balance the system reports. The size of that gap tells you what kind of migration you are about to have.