Data structure to handle complex discount and rebate structure

Hi everyone,

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.

Any ideas that could help pointing me in the right direction is much appreciated :pray:

I have some questions here.

1/Is this assumption correct?

  • In Year 1, the discount tiers are based on a fixed set of numbers you determine (because there’s no previous data).
  • In Year 2, the discount tiers are based on the number of products sold in Year 1.
  • In Year 3, the discount tiers are based on the number of products sold in Year 2.

So at this stage, I think what you should care about is:

  • Which year they are in.
  • What they sold last year so you can calculate this year’s discount.
  • What they sold this year so you can calculate next years discount, and how many are left until the next step.
  • Timestamp columns for each threshold, for the current year. This can be cleared with a scheduled workflow on 1st January each year.

What do you think? Does it work with your structure?

2 Likes

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

Drop · Glide - Google Chrome
Watch Video

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?

2 Likes

Thank you, this seems like a great approach. I will try to build this out now :grinning:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.