Hello! We could really use some help on a dynamic formula for our affiliates. We are open to other alternative suggestions if there’s a better way to solve our issue.
TL;DR: We would like to have a single table VIEW that we can share with our affiliate reps, but have the VIEW show different commission values, respective to the rep and their contracted comission percentage. We could easily have a different VIEW for each rep, respective to their commission rates, but it would be cleaner/easier if we could have all reps use the same VIEW, but the permalinks have their respective referral ID’s baked in.
Here’s an approach we came up with, but not sure how to execute it. Perhaps there’s a better way altogether.
- Our main table has all of our products and their variations, with over 20 columns/fields for specs
- We have an “Affiliate Comissions” table VIEW, which only shows:
- Product name
- Product permalink
- MSRP(retail price)
- Affiliate commissions
- We offer affiliate commissions to reps who bring us business.
- Right now, the affiliate commission is calculated based on a generic formula (same for all reps)
- Each rep will have a different commission percentages depending on several factors
- Each rep will have a difference
- We want to share this “Affiliate Comissions” table VIEW with each rep, and have it dynamically look at another table to find a customized percentage, respective to the commission % of each rep
- John makes an 8% commission
- Jacob makes a 10% commission
- Jingleheimer makes a 12% comission
- Each of our product variations has an associated permalink.
- We’d like the dynamic formula to somehow recognize which of our affiliates is viewing the table… grab the product permalink, and append their custom referral link “&ref=[username]” based on the field where we define their referral user id, at the end of the permalink.
This would allow us to not have to make a separate shareable VIEW with each rep. Instead we could give each rep the same link, but they see their respective commission rate, per product.
Thanks in advance for any ideas/suggestions, here!