Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 39 ยท Real-World Projects

Project: Loyalty Points

Build a WooCommerce loyalty points plugin: earn points on completed orders, keep a ledger table with dbDelta, redeem as a cart fee, show an account page and reverse points on refunds.

A loyalty program rewards repeat customers: spend money, earn points, spend points as a discount. It looks like a simple counter, and a simple counter is exactly the wrong way to build it. A single "points" number on the user cannot tell you why the balance changed, cannot be audited when a customer complains, and breaks silently when an order is refunded twice or an action runs twice. The robust design is a ledger: an append-only table of signed entries, where the balance is the sum.

In this project you design that ledger, award points when an order completes, let customers redeem points in the cart, show a "My points" page in the account area, and reverse points when a refund happens. The WordPress and WooCommerce code is read-only; the ledger rules run for real in SQLite below. Prerequisites: you should know Order Statuses and the Order Lifecycle, Dynamic Pricing, Fees and Discounts and the idea of Idempotency.

Design the rules first

Write the business rules in plain words before you write code:

  • Earning: 1 point per 1.00 of the order's merchandise total after discounts, excluding shipping and tax, rounded down. Points are awarded when the order reaches completed, not when it is placed (so cancelled or unpaid orders never give points).
  • Redeeming: 100 points are worth 1.00. A customer can redeem up to their balance, but never more than the cart's merchandise total, and the order then earns points only on what was actually paid.
  • Refunds: a refund removes the points earned for the refunded part, and gives back points that were redeemed for a fully cancelled order.
  • Everything is in integers. Points are whole numbers; money inside the logic is in cents (see Representing Money Correctly).
flowchart LR
    A["Order completed"] --> B["EARN entry<br/>(+points)"]
    C["Checkout with<br/>points applied"] --> D["REDEEM entry<br/>(-points)"]
    E["Order refunded"] --> F["REVERSE entry<br/>(-earned, +redeemed)"]
    B --> L[("Ledger<br/>append only")]
    D --> L
    F --> L
    L --> S["Balance = SUM(points)"]

Nothing is ever edited: new facts become new rows.

The ledger table

Create it with dbDelta() from your activation or upgrade routine (see Compatibility, Upgrades and Distribution). The unique key is the safety net that makes awarding idempotent:

PHP
<?php
function acme_points_install(): void {
    global $wpdb;
    $table   = $wpdb->prefix . 'acme_points_ledger';
    $charset = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE {$table} (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        order_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
        ref VARCHAR(40) NOT NULL,
        type VARCHAR(20) NOT NULL,
        points INT NOT NULL,
        note VARCHAR(255) NOT NULL DEFAULT '',
        created_at DATETIME NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY user_event (user_id, type, ref),
        KEY order_id (order_id),
        KEY user_created (user_id, created_at)
    ) {$charset};";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $sql );
}

dbDelta() is picky: two spaces after PRIMARY KEY, each column on its own line, and KEY rather than INDEX. Details about custom tables are in Using $wpdb and Custom Tables. Use $wpdb->prepare() for every query that includes a variable.

Why UNIQUE (user_id, type, ref)? The ref column names the event that caused the entry: order-1001 for an earn or redeem, refund-55 for a reversal. One event can produce one entry of each type. If WooCommerce fires the "completed" hook twice (a double click, a retry, a plugin that re-saves the order), the second insert fails instead of doubling the points. This is the same idea as an idempotency key in a payment API. Partial refunds can happen many times per order, which is why each refund gets its own ref.

Earning on completion

PHP
<?php
add_action( 'woocommerce_order_status_completed', function ( int $order_id ) {
    $order = wc_get_order( $order_id );
    $user_id = $order->get_customer_id();
    if ( ! $user_id ) {
        return;                                    // guests do not earn
    }

    $paid_cents = (int) round( ( $order->get_subtotal() - $order->get_total_discount() - $order->get_total_fees_discount() ) * 100 );
    $points     = Acme_Points_Rules::points_for_cents( max( 0, $paid_cents ) );

    Acme_Ledger::add( $user_id, 'earn', $points, 'order-' . $order_id, 'Order #' . $order->get_order_number() );
}, 20 );

(The total used above is an example; decide exactly which amounts count and test the formula. get_subtotal() is before discounts, so the discounts must be subtracted.) Acme_Ledger::add() inserts a row and treats the "duplicate key" error as "already awarded", returning false without raising a fatal. Use the order completed hook, not processing, if you only want to reward orders that were shipped; use processing if you want points as soon as payment arrives. That is a product decision, not a technical one.

Redeeming: as a fee or as a coupon

There are two common ways to turn points into a discount:

Approach How Pros Cons
Negative fee woocommerce_cart_calculate_fees, $cart->add_fee( 'Points', -$amount ) Simple, no coupon records Taxes on negative fees need care; some reports treat it as a fee
Virtual coupon woocommerce_get_shop_coupon_data returns coupon data for a code like points-1234 Fits WooCommerce's discount reports and tax handling More code, coupon restrictions can interfere

The fee version:

PHP
<?php
add_action( 'woocommerce_cart_calculate_fees', function ( WC_Cart $cart ) {
    if ( is_admin() && ! defined( 'DOING_AJAX' ) ) {
        return;
    }
    $user_id = get_current_user_id();
    $wanted  = (int) ( WC()->session ? WC()->session->get( 'acme_points_to_use', 0 ) : 0 );
    if ( ! $user_id || $wanted <= 0 ) {
        return;
    }

    $balance  = Acme_Ledger::balance( $user_id );
    $max_cents = (int) round( $cart->get_subtotal() * 100 );
    $points   = Acme_Points_Rules::clamp_redemption( $wanted, $balance, $max_cents );

    if ( $points > 0 ) {
        $cart->add_fee( __( 'Loyalty points', 'acme-points' ), -Acme_Points_Rules::cents_for_points( $points ) / 100, false );
    }
} );

The cart only displays the discount. The points must leave the balance when the order is created, in woocommerce_checkout_order_processed (or woocommerce_store_api_checkout_order_processed for blocks), by writing a redeem row with a negative amount. If you subtract points from the balance when the shopper merely presses the button, an abandoned cart would burn their points. Also re-check the balance at that moment, because two browser tabs may have used the same points: the insert should fail if the balance is not enough.

Never trust the number of points the browser sends. The fee code above reads the requested points from the session and clamps them against the server-side balance and cart total.

The account page

WooCommerce's My Account page supports custom endpoints (URLs like /my-account/points/):

PHP
<?php
add_action( 'init', function () {
    add_rewrite_endpoint( 'points', EP_ROOT | EP_PAGES );
} );

add_filter( 'woocommerce_get_query_vars', function ( array $vars ): array {
    $vars['points'] = 'points';
    return $vars;
} );

add_filter( 'woocommerce_account_menu_items', function ( array $items ): array {
    $items['points'] = __( 'My points', 'acme-points' );   // placed after the logout item by default
    return $items;
} );

add_action( 'woocommerce_account_points_endpoint', function () {
    $user_id = get_current_user_id();
    printf( '<p>%s <strong>%d</strong></p>', esc_html__( 'Your balance:', 'acme-points' ), Acme_Ledger::balance( $user_id ) );

    echo '<table class="woocommerce-orders-table"><tbody>';
    foreach ( Acme_Ledger::latest( $user_id, 20 ) as $row ) {
        printf(
            '<tr><td>%s</td><td>%s</td><td>%+d</td></tr>',
            esc_html( wp_date( get_option( 'date_format' ), strtotime( $row->created_at ) ) ),
            esc_html( $row->note ),
            (int) $row->points
        );
    }
    echo '</tbody></table>';
} );

Flush rewrite rules once on activation (flush_rewrite_rules() in the activation hook, never on every request), or the new URL returns a 404. Each user sees only their own rows because the query is keyed by the logged-in user id; never take a user id from the URL.

Refunds reverse points

PHP
<?php
add_action( 'woocommerce_order_refunded', function ( int $order_id, int $refund_id ) {
    $order  = wc_get_order( $order_id );
    $refund = wc_get_order( $refund_id );
    $user_id = $order->get_customer_id();
    if ( ! $user_id ) {
        return;
    }

    $refunded_cents = (int) round( abs( (float) $refund->get_amount() ) * 100 );
    $reverse        = Acme_Points_Rules::points_for_cents( $refunded_cents );

    Acme_Ledger::add( $user_id, 'reverse', -$reverse, 'refund-' . $refund_id, 'Refund #' . $refund_id );
}, 10, 2 );

Reversal can push a balance below zero if the customer already spent the points. Decide and document the policy: allow a negative balance (the debt is repaid by future orders), or cap the reversal at the current balance and record the difference. The ledger makes both easy to audit.

The ledger rules (runnable)

The following stand-in uses SQLite instead of $wpdb, but the table design and every rule are the same as above, including the duplicate-protection key.

PHP Points ledger with earn, redeem and reverse

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The duplicate earn and the duplicate reverse both return false and leave the balance untouched, which is exactly what you want when WooCommerce hooks fire twice. The exact balances follow from the rules: 84 points earned, 84 redeemed in total (the redemption was clamped to the balance), then -20 for the refund. Note the last number is negative: the policy question from the refunds section in action.

A second look: expiry and rounding

Many programs expire points after a period. With a ledger, expiry is just another row type (expire), written by a daily Action Scheduler job that sums entries older than the cutoff that were not spent. The next stand-in shows why the order of spending matters: spending oldest points first (FIFO) is the fair, common rule.

PHP Expiring points with oldest-first spending

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is a ledger of entries better than a single points number on the user?

    A counter hides history. A ledger explains each change, supports unique keys against duplicates, and allows reversals and expiry as new rows.
  2. 2. What does the unique key on the ledger protect against?

    Inserting the same (user, type, reference) twice fails, so hook retries cannot double the points.
  3. 3. When should redeemed points leave the balance?

    Cart changes are tentative. The deduction is final only when the order exists, and the check must be repeated then because the balance may have changed.
  4. 4. What should the server do with the number of points sent by the browser?

    Browser data is a request, not a fact. Limits come from the ledger balance and the cart.
  5. 5. Why flush rewrite rules in the activation hook rather than on every request?

    Rebuilding rewrite rules writes to the database and is slow. Do it once when the rules change, not on every page load.

Summary

  • Model loyalty points as an append-only ledger and compute the balance as a sum.
  • Award on a chosen order status, protect every event with a unique key, and use integers for points and cents.
  • Redeem through a fee or a virtual coupon, but deduct points only when the order is created, after a server-side recheck.
  • Add a My Account endpoint with add_rewrite_endpoint(), woocommerce_account_menu_items and woocommerce_account_{endpoint}_endpoint.
  • Reverse points on refunds and decide the negative-balance and expiry policies explicitly.

Next: Project: Product Bundles