Exporting Reward Points per Customer in Adobe Commerce

Adobe Commerce and Magento_Reward provide no native method of exporting reward points.

You can however export them with SQL:

SELECT
    ce.entity_id                                   AS customer_id,
    ce.email,
    TRIM(CONCAT(COALESCE(ce.firstname, ''), ' ', COALESCE(ce.lastname, ''))) AS customer_name,
    cg.customer_group_code                         AS customer_group,
    sw.name                                        AS website,
    ce.created_at                                  AS account_created,
    COALESCE(SUM(mr.points_balance), 0)            AS points_balance
FROM customer_entity ce
LEFT JOIN magento_reward mr ON mr.customer_id = ce.entity_id
LEFT JOIN customer_group  cg ON cg.customer_group_id = ce.group_id
LEFT JOIN store_website   sw ON sw.website_id = ce.website_id
GROUP BY ce.entity_id, ce.email, ce.firstname, ce.lastname,
         cg.customer_group_code, sw.name, ce.created_at
ORDER BY points_balance DESC, ce.entity_id;

That gives you every customer account, with a balance of 0 for the ones who have never earned a point, sorted with the biggest balances first.

¶Checking Balance History

If a specific balance looks wrong, the history table is the audit trail. Deltas for one customer:

SELECT h.created_at, h.action, h.points_delta, h.points_balance,
       h.is_expired, h.expired_at_static, h.expired_at_dynamic, h.comment
FROM magento_reward_history h
JOIN magento_reward mr ON mr.reward_id = h.reward_id
WHERE mr.customer_id = :customer_id
ORDER BY h.history_id;

points_balance on each history row is the running total as it stood after that event, so the last row should equal magento_reward.points_balance. When it does not, the usual cause is the expiry backlog above. Expiry itself is written as a reversing row with is_expired = 1 and is_duplicate_of pointing at the history row it cancels, so the original earn stays visible.

I checked the schema, the cron and the rate lookup against Adobe Commerce 2.4.8-p5 with magento/module-reward 101.2.8, and the same files are byte for byte identical on 2.4.7-p9 running 101.2.7-p5.