Learn · Calculators

Earn rate modeller

An earn rate modeller calculates points a member earns from spend by multiplying qualifying spend by the base earn rate and any tier multiplier, then adding capped bonus points. It shows the effective points per dollar for a member or cohort.

How it works

An earn rate modeller computes the points a member earns for a given amount of qualifying spend. The core formula is points earned equals qualifying spend multiplied by base earn rate multiplied by tier multiplier, plus any bonus points. The base earn rate is the points per dollar set in the programme terms. The tier multiplier comes from the tier chart, such as 1.25 for silver or 1.5 for gold. The bonus earn rate is any extra points per dollar from a campaign and is usually capped at a maximum spend or time period. Qualifying spend is the dollar amount that actually earns points after removing exclusions such as taxes, delivery fees or gift card purchases. The reader gets each input from the programme rules, the tier chart, the campaign brief and the transaction export from the point of sale or CRM. This model is built once in a spreadsheet so that changing an input updates the total points.

Worked example

This worked example is illustrative arithmetic. Set qualifying spend at 250 dollars, base earn rate at 1 point per dollar, tier multiplier at 1.5 for gold, and a bonus rate of 2 extra points per dollar on the first 100 dollars of qualifying spend. Base tier points equal 250 times 1 times 1.5, which is 375 points. Bonus points equal 100 times 2, which is 200 points. Total points equal 375 plus 200, which is 575 points. The effective earn rate equals 575 divided by 250, which is 2.3 points per dollar. If the same gold member spends only 80 dollars, bonus applies to all 80 dollars, so bonus points equal 80 times 2, which is 160 points. Base tier points equal 80 times 1 times 1.5, which is 120 points. Total points equal 120 plus 160, which is 280 points, and the effective rate equals 280 divided by 80, which is 3.5 points per dollar. This shows how caps and spend amounts change the result.

What to do

Build a spreadsheet with columns for member ID, qualifying spend in dollars, base earn rate, tier multiplier, bonus rate, bonus eligible spend, and total points. Put programme inputs in separate cells so you can change them without rewriting formulas. The base earn rate and qualifying spend rules come from the programme terms. The tier multiplier comes from the tier chart. The bonus rate and cap come from the campaign plan or promotion terms. Use a formula such as total points equals qualifying spend multiplied by base rate multiplied by tier multiplier plus bonus rate multiplied by the smaller of qualifying spend and bonus cap. Test the formula on one member, then copy it down a member list. Model different spend levels from 0 to your highest expected monthly spend to see the range of earn rates. Use the result to check whether the effective points per dollar stays within your liability and reward cost limits.

Common mistakes

  • Using total revenue instead of qualifying spend, because exclusions such as taxes and fees do not earn points under the terms.
  • Forgetting to apply the tier multiplier after the base points, which undercounts higher tier members.
  • Applying the bonus rate to the entire spend when the campaign caps the bonus at a set dollar amount or time window.
  • Treating bonus points as if they count toward tier qualification or as if they are exempt from caps, when the terms say otherwise.

Related