Loyalty Points System

Loyalty Points System

Overview

A loyalty points program rewards repeat customers with points earned on purchases and other actions (reviews, referrals, social shares), which they can redeem for discounts at checkout. Well-designed programs include tier progression (Bronze → Silver → Gold → Platinum) that unlocks perks like free shipping or higher point multipliers as customers spend more. Most merchants should start with a dedicated loyalty app — the major platforms have excellent options — rather than building from scratch.

When to Use This Skill

• When adding a customer retention mechanism to increase repeat purchase rate
• When launching a tiered VIP program where high-value customers unlock benefits like free shipping or early access
• When replacing an ad-hoc discount system with a structured loyalty program that customers can track
• When integrating points into a mobile app where customers check their balance and redeem at checkout
• When running promotional campaigns that award bonus points for specific actions (reviews, referrals, first purchase)

Core Instructions

Step 1: Determine the merchant's platform and choose the right tool

Step 2: Configure the loyalty program

---

Shopify

Using Smile.io (most popular Shopify loyalty app):

1. Install Smile: Loyalty & Rewards from the Shopify App Store (free tier available; paid plans from ~$49/month) 2. In the Smile dashboard, configure Ways to earn:

• Points for purchase (e.g., 1 point per $1 spent)
• Bonus points for account creation, birthday, product reviews
• Referral points for referring a friend

3. Configure Ways to redeem:

• Points for a discount code (e.g., 100 points = $1 off)
• Set minimum points required for redemption
• Set maximum redemption per order (e.g., max 50% of order value in points)

4. Configure Tiers (VIP program, available on paid plans):

• Define tier thresholds (e.g., Bronze: 0–$500 spend, Silver: $500–$1,000, Gold: $1,000+)
• Set tier benefits: point multipliers, exclusive products, free shipping

5. Configure the Customer-facing widget: the floating points widget appears on your storefront automatically; customize colors and placement

Testing the integration:

• Create a test customer account, place a test order, and verify points are awarded after the order status changes to "fulfilled"
• Attempt a redemption at checkout to verify the discount applies correctly

---

WooCommerce

Using WooCommerce Points and Rewards:

1. Install WooCommerce Points and Rewards from WooCommerce.com 2. Go to WooCommerce → Points and Rewards → Settings:

• Earn points: set the earning ratio (e.g., 1 point per $1 spent)
• Earn points for: purchases, reviews, account registration
• Redeem points: set the redemption ratio (e.g., 100 points = $1 discount)
• Maximum discount: cap the amount that can be redeemed per order
• Points expire: optionally set an expiration period

3. Manage individual customer balances under WooCommerce → Points and Rewards → Manage Points 4. Points are displayed in the customer's account page automatically

For tiers with WooCommerce: The official plugin does not include tier management. Options:

• YITH WooCommerce Points & Rewards: includes tier/VIP functionality
• Gamification for WooCommerce: adds tier levels and achievement badges
• Manually assign WooCommerce customer groups (roles) based on lifetime spend and give group-specific prices or shipping rates

---

BigCommerce

Using Smile.io on BigCommerce:

1. Install Smile.io from the BigCommerce App Marketplace 2. Follow the same Smile.io setup as the Shopify section above — the interface is nearly identical 3. Smile.io integrates with BigCommerce's checkout to apply loyalty discounts as coupon codes

Using LoyaltyLion:

1. Install LoyaltyLion from the BigCommerce App Marketplace 2. Configure earning rules and redemption options in the LoyaltyLion dashboard 3. The app adds a customer loyalty panel to account pages and integrates with BigCommerce checkout

---

Custom / Headless

For headless storefronts or when app capabilities are insufficient, build a ledger-based points system. The ledger pattern (append-only transactions, no balance column) ensures accuracy, enables full audit trails, and makes point reversals trivial.

CREATE TABLE loyalty_accounts (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id  UUID NOT NULL UNIQUE,
  tier         VARCHAR(16) NOT NULL DEFAULT 'bronze',
  lifetime_spend_cents INTEGER NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE loyalty_ledger (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id   UUID NOT NULL REFERENCES loyalty_accounts(id),
  points       INTEGER NOT NULL,  -- positive = earned, negative = redeemed/expired
  type         VARCHAR(32) NOT NULL CHECK (type IN ('purchase', 'bonus', 'referral', 'redemption', 'expiration', 'adjustment')),
  reference_id UUID,              -- order_id, review_id, etc.
  description  TEXT NOT NULL,
  expires_at   TIMESTAMPTZ,       -- NULL = never expires
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Points balance (excluding expired transactions):

async function getPointsBalance(accountId: string): Promise<number> {
  const result = await db.raw(`
    SELECT COALESCE(SUM(points), 0) AS balance
    FROM loyalty_ledger
    WHERE account_id = ?
      AND (expires_at IS NULL OR expires_at > NOW())
  `, [accountId]);
  return Math.max(0, parseInt(result.rows[0].balance, 10));
}

Award points after order fulfillment (not at purchase — prevents points fraud from returns):

const TIER_MULTIPLIERS = { bronze: 1, silver: 1.25, gold: 1.5, platinum: 2 };
const POINTS_PER_DOLLAR = 1;
const EXPIRY_MONTHS = 12;

async function awardPurchasePoints(customerId: string, orderId: string, subtotalCents: number) {
  const account = await getOrCreateAccount(customerId);
  const multiplier = TIER_MULTIPLIERS[account.tier];
  const points = Math.round((subtotalCents / 100) * POINTS_PER_DOLLAR * multiplier);
  const expiresAt = new Date();
  expiresAt.setMonth(expiresAt.getMonth() + EXPIRY_MONTHS);

  await db.loyaltyLedger.insert({
    account_id: account.id, points, type: 'purchase',
    reference_id: orderId, description: `Points for order ${orderId}`, expires_at: expiresAt,
  });

  // Update lifetime spend and recalculate tier
  await db.loyaltyAccounts.update(account.id, {
    lifetime_spend_cents: account.lifetime_spend_cents + subtotalCents,
  });
  await recalculateTier(account.id);
}

Redeem points at checkout:

const REDEMPTION_RATE = 0.01; // 100 points = $1

async function redeemPoints(customerId: string, orderId: string, pointsToRedeem: number): Promise<{ discountCents: number }> {
  const account = await db.loyaltyAccounts.findByCustomerId(customerId);
  const balance = await getPointsBalance(account.id);
  if (pointsToRedeem > balance) throw new Error('Insufficient points');

  const discountCents = Math.floor(pointsToRedeem * REDEMPTION_RATE * 100);
  await db.loyaltyLedger.insert({
    account_id: account.id, points: -pointsToRedeem, type: 'redemption',
    reference_id: orderId, description: `Redeemed for order ${orderId}`,
  });
  return { discountCents };
}

Best Practices

• Award points after fulfillment, not at purchase — prevents customers from earning points on orders they plan to return; most loyalty apps have a configurable "pending period" for this
• Display points in dollar value on the UI — "You have $5.00 in rewards" converts better than "You have 500 points"; show the dollar value prominently
• Send expiration reminder emails — email customers 30 days before their points expire; this is a proven re-engagement trigger that also drives purchases
• Cap maximum redemption per order — allow redeeming at most 50% of order value in points to protect margin
• Reverse points when orders are refunded — insert a negative adjustment transaction tied to the refund event; do not rely on manual corrections
• Display upcoming tier thresholds — "Spend $47 more to reach Gold" drives incremental spend more than just showing the current tier
• Start simple — launch with a flat earn rate before adding tiers; tiers add complexity and customer service burden; prove the program works first

Common Pitfalls

Related Skills

• @coupon-management
• @gift-cards
• @ab-testing-pricing
• @customer-segmentation
• @discount-engine

Loyalty Program Database Schema

Problem/Feature Description

A mid-sized online retailer is launching a customer loyalty program and needs a solid database foundation before any application code is written. The engineering team wants a schema that can support points earned on purchases, bonus actions, redemptions, and automatic expiry — without ever losing the history of how a customer's balance changed over time. Auditing is critical: the finance team needs to be able to trace every points event back to its source. The schema also needs to support tiered membership levels that unlock different earning rates.

The existing database is PostgreSQL and already has a customers table with an id UUID primary key. Your task is to design the tables, constraints, and indexes that will underpin the loyalty system.

Output Specification

Produce a single SQL migration file named migration.sql containing:

• All CREATE TABLE statements
• All CREATE INDEX statements
• Any necessary CHECK constraints

The file should be runnable against a PostgreSQL database that already has a customers(id UUID) table. Do not include any application code — only SQL.

Loyalty Points Earning Engine

Problem/Feature Description

A fashion e-commerce brand wants to reward their best customers with accelerated points earning as they reach higher spending milestones. The loyalty program should automatically move customers through membership tiers — Bronze, Silver, Gold, and Platinum — as their total lifetime purchases grow, and each tier should give them a higher earn rate on future purchases. When a customer crosses a tier threshold, they should receive a congratulatory email.

The database schema is already in place with loyalty_accounts and loyalty_transactions tables. The engineering team needs TypeScript functions to handle the earn flow: calculating the correct points for a completed order, updating the customer's lifetime spend, recalculating their tier, and recording the transaction. Points should expire after a set period.

The existing database ORM exposes:

• db.loyaltyAccounts.findByCustomerId(customerId) → account or null
• db.loyaltyAccounts.findById(id) → account
• db.loyaltyAccounts.insert(data) → account
• db.loyaltyAccounts.update(id, data) → void
• db.loyaltyTransactions.insert(data) → void
• db.transaction(callback) → runs callback in a DB transaction
• sendTierUpgradeEmail(customerId, newTier) → Promise<void>

Output Specification

Produce a TypeScript file named loyalty-earn.ts containing:

• All constants at the top of the file
• getOrCreateLoyaltyAccount(customerId: string) function
• awardPurchasePoints(customerId, orderId, orderSubtotalCents) function that returns the number of points awarded
• recalculateTier(accountId: string) function
• getPointsBalance(accountId: string) function (uses db.raw with a SQL query)

Include a brief // Usage example comment at the bottom showing how to call awardPurchasePoints.

Loyalty Points Redemption and Expiration Maintenance

Problem/Feature Description

A retail platform has a working loyalty points earning system and now needs two more pieces: the ability for customers to apply their points as a discount at checkout, and a nightly maintenance job that cleans up expired points.

The checkout team has reported that customers are occasionally charged twice due to payment gateway retries, so the redemption function must be safe to call multiple times for the same order without double-deducting points. The finance team also wants a safeguard so that customers cannot apply so many points that the company loses its entire margin on an order — there should be a ceiling on how much of an order can be paid with points.

The database ORM is the same as the earning system:

• db.loyaltyAccounts.findByCustomerId(customerId) → account or null
• db.loyaltyTransactions.insert(data) → void
• db.loyaltyTransactions.findOne(filter) → transaction or null
• db.raw(sql, params) → query result with .rows array
• getPointsBalance(accountId) → Promise<number> (already implemented elsewhere)

Output Specification

Produce a TypeScript file named loyalty-redeem.ts containing:

• All constants at the top of the file
• redeemPoints(customerId, orderId, pointsToRedeem, orderSubtotalCents) function returning { discountCents: number }
• expirePoints() function (the daily maintenance job)

Add a // Notes: comment block at the top of the file explaining the key design decisions made (idempotency approach, expiration logic, the redemption cap, and the conversion rate used).