The final step of the promotion is to give customers a reward after they have met the promotion criteria.
The promotion criteria are as follows:
Example 1: Tom has spent $3500 in February. He is eligible to receive ($3500 - $3000) * 0.03 = $15.
Example 2: Susan has spent $15000 in February. She is eligible to receive ($15000 - $4000) * 0.03 = $550. However, the maximum rebate applies and she will only receive $200.
Below is the code to calculate the rebate for each customer:
if tot_spend >3000 and flag = 'Target';
if 3000 < tot_spend <= 4000 then do;
r_rate = 0.03;
amount = r_rate*(tot_spend-3000);
else if tot_spend > 4000 then do;
r_rate = 0.04;
amount = r_rate*(tot_spend-4000);
Rebate = min(amount, 200);
keep custno rebate;
The rebate is calculated only for those in the target group, who spent more than $3000:
The rebate rate and rebate amount are calculated based on the spending level:
Finally, the rebate is capped at $200.
The final output data set contains two columns:
We will now export the data set to a CSV file where the finance team can handle the rebate:
proc export data=fulfillment outfile = '/folders/myfolders/Projects/Credit Card/target1_ful.csv'
Done! The CSV file is created with the rebate details: