Automating dealer rebate calculation, from raw data to personalised letter
In brief
An international manufacturer runs a rebate programme for its dealer network, based on their digital marketing activity and commercial performance. The calculation, previously manual, took five working days each time and relied on data from three different systems.
The challenge
The rebate amount depended on several separate factors: whether a tracking module was present on the dealer's website, their social media activity, their actual turnover and how regularly they submitted their sales figures. Each element lived in a different system: web tracking, a survey tool for social media activity and the commercial management system for turnover. Collecting and cross-referencing this data manually, dealer by dealer, took several days per calculation, with the risk of errors increasing as the number of dealers grew.
The approach
The calculation was fully automated, from data extraction through to the final letter. Website activity and social media posts are pulled automatically from their respective systems. Sales figures are integrated as soon as they become available, with a progressive calculation logic that rewards regular submission rather than a single late submission at the end of the year.
A specific rule was introduced for dealers belonging to the same commercial group. They share the group's best performance rather than being assessed individually, an important business rule for maintaining fair treatment between related entities.
Once the calculation is complete, a personalised letter is generated automatically for each dealer, in their language, detailing the amount earned and the factors behind it. A review interface was also built so that a specific case can be adjusted manually and the corresponding document regenerated immediately, without rerunning the entire calculation.
Sending the letters to dealers was integrated directly into the company's email system, with the option to save them as drafts for review before final sending.
The result
A calculation that used to require five days of manual work now runs in about fifteen minutes. Commercial teams can still adjust a specific case without relying on IT, and each dealer receives a clear, personalised letter explaining how their rebate was calculated.
What this shows
This project shows how to automate a calculation that depends on several disparate data sources without giving up the ability to make a one-off manual adjustment. Treating fine-grained business rules, such as sharing performance between entities in the same group, as proper scenarios rather than ignored exceptions. Delivering an output that remains readable and personal for the final recipient, rather than just producing a raw calculation file.
Five days of manual calculation can sometimes become fifteen minutes. If a task combines several data sources, business rules and repeated manual checks, it may be worth looking at what can be automated.