WooCommerce Product Recommendations with Custom Scoring Algorithms

WooCommerce product recommendations with your own scoring

WooCommerce upsells and cross-sells are curated by hand: the shop owner links related products one by one. On a store with hundreds of products nobody keeps that up to date. A simple algorithm based on what customers actually bought together, weighted by category, gives a relevant “Customers also bought” block without manual work and without a third-party service.

Problem: A store with a large catalog wants a “Customers also bought” section on product pages, based on real orders, without curating cross-sells for every product.

Solution: Count co-purchases from WooCommerce’s analytics lookup table, add a bonus for shared categories, keep only purchasable products, cache the result per product, and render it with the standard product template.

The data source

The analytics table wc_order_product_lookup has one row per product per order, with the order date and status-independent IDs. It is maintained by WooCommerce for the Analytics reports and works the same with HPOS and with orders stored as posts, so it is a better source than joining woocommerce_order_items with wp_posts.

The recommender

<?php
class My_Product_Recommender {

    public static function for_product( $product_id, $limit = 4 ) {
        $product_id = (int) $product_id;
        $cache_key  = 'my_recs_' . $product_id;
        $cached     = get_transient( $cache_key );
        if ( false !== $cached ) {
            return $cached;
        }

        global $wpdb;
        $lookup = $wpdb->prefix . 'wc_order_product_lookup';

        // Products bought in the same orders as this one, in the last 90 days
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT other.product_id AS pid, COUNT( DISTINCT other.order_id ) AS freq
             FROM $lookup this
             JOIN $lookup other ON other.order_id = this.order_id AND other.product_id <> this.product_id
             WHERE this.product_id = %d
               AND this.date_created >= %s
             GROUP BY other.product_id
             ORDER BY freq DESC
             LIMIT 40",
            $product_id,
            gmdate( 'Y-m-d H:i:s', strtotime( '-90 days' ) )
        ) );

        $source_cats = wc_get_product_term_ids( $product_id, 'product_cat' );
        $scored      = array();

        foreach ( $rows as $row ) {
            $candidate = wc_get_product( (int) $row->pid );
            if ( ! $candidate || 'publish' !== $candidate->get_status() || ! $candidate->is_purchasable() || ! $candidate->is_in_stock() ) {
                continue;
            }
            $shared = count( array_intersect( $source_cats, wc_get_product_term_ids( $candidate->get_id(), 'product_cat' ) ) );
            $scored[ $candidate->get_id() ] = (int) $row->freq * 3 + $shared * 2;
        }

        arsort( $scored );
        $result = array_slice( array_keys( $scored ), 0, $limit );

        set_transient( $cache_key, $result, 6 * HOUR_IN_SECONDS );
        return $result;
    }
}

The score is deliberately simple: three points per order in which both products were bought, two per shared category. The weights are a starting point; check the results for a few well-known products and adjust them. Products without orders get an empty list, and the section is not shown.

Render on the product page

<?php
add_action( 'woocommerce_after_single_product_summary', function () {
    global $product;
    $ids = My_Product_Recommender::for_product( $product->get_id(), 4 );
    if ( ! $ids ) {
        return;
    }

    echo '<section class="related products my-recommendations"><h2>' . esc_html__( 'Customers also bought', 'my-plugin' ) . '</h2>';

    wc_set_loop_prop( 'name', 'my-recommendations' );
    woocommerce_product_loop_start();
    foreach ( $ids as $id ) {
        $post_object = get_post( $id );
        setup_postdata( $GLOBALS['post'] = $post_object ); // phpcs:ignore WordPress.WP.GlobalVariablesOverride
        wc_get_template_part( 'content', 'product' );
    }
    woocommerce_product_loop_end();
    wp_reset_postdata();

    echo '</section>';
}, 15 );

This is the same loop WooCommerce uses for related products, so the cards look and behave like the rest of the shop (prices, add-to-cart buttons, sale badges).

Keep the cache fresh

The transients expire after six hours. When an order is completed, clear the cache of its products so popular combinations show up sooner:

<?php
add_action( 'woocommerce_order_status_completed', function ( $order_id ) {
    $order = wc_get_order( $order_id );
    if ( ! $order ) {
        return;
    }
    foreach ( $order->get_items() as $item ) {
        delete_transient( 'my_recs_' . $item->get_product_id() );
    }
} );

NOTE: The lookup table is filled by Action Scheduler jobs; if the recommendations are empty on a store with orders, check that Analytics has imported the historical data (Analytics → Settings → Import historical data). Caching with transients is described in the Transients API.