Need your help to figure out the best way to automatically handle discount structure for our re-sellers. The idea is like this:
Year 1 (For example September 1 2024 - September 1 2025)
1-7 products sold = 20% discount
8-14 products sold = 25%
15-21 products sold = 30%
Year 2 (Discount threshold commences according to what was sold in Year 1.)
1 to 12 - 20%
13 to 24 - 25%
25 to 36 - 30%
Year 3 (Discount threshold commences according to what was sold in Year 2.)
1 to 12 - 20%
13 to 24 - 25%
25 to 48 - 30%
I have a table with all resellers set up in my app. I have a table with all sales orders that has sales date, qty etc. Now I need a column in the re-sellers table to show which discount step they are currently at. I also want to see how many products are left until next discount step. I also want a notification when a partner reaches the next discount step for them.
My question is, how would I structure any additional tables and relations to make this work? I started setting up a new table called “Commission Structures” and created the following columns. However, I realised that this might not be the correct way to get this to work.
Thank you very much for your reply. I realise that I worded the title of this topic poorly by using the word “rebate”. The program is simply a way for our dealer to grow steady in their sales and get more discount as they grow. The tiers are never based on the previous year sales but always on the current sales. If they reach the highest tier on Year 1, than that is great and they program is basically “finished” since they unlocked the highest discount already. If they did not reach it during year 1, they can reach it during year 2 but the target for them will be higher.
When reaching each sales target, a credit will be issued for the discount balance of the
filter systems sold. For example, when filter system 8 is purchased, a credit note of 5%
per sold filter will be issued for filter systems 1 to 7. Credit on discount balance for sold
filter systems will be issued until the maximum discount of 30% has been reached.
I was working on this a bit more yesterday and recorded a video of where I am at the moment. I am sure there is a better way to approach this though and I would be happy for any ideas
I think if you’re only basing it on this year’s sales, you only need to calculate the current year’s sales instead of having to get columns for each year, unless you’re using it for something other than this task.
From a query + rollup, you would be able to get the current year’s sales count.
Then, you have also calculated the current amount of years they are into the program, so you have the thresholds. I would do separate relations/queries + lookups if needed, to get thresholds for each partner.
Then, add 3 timestamp columns for each threshold.
Every time a sale is recorded, you would check:
If sales rollup > threshold 1 and threshold 1 notification timestamp is empty, then send notification.
If sales rollup > threshold X and threshold X notification timestamp is empty, then send notification.
Setup a scheduled workflow to clear those timestamps on Jan 1st to get you ready for the new year.
This looks like an increment to a number column right?