Can you use ETL tools to manage sales compensation?

Pranshu Kacholia

  1. Apr 22, 2014
  2. 4 min read
 

Step 1 - Input profiling and validations

Open source ETL software Talend Data Integration offers a powerful alternative to excel for running sales compensation management systems. Featuring a simple drag and drop interface and flexibility to build complex logic using Java custom code, Talend is not only simple to learn and easy to use but also beats many other tools when it comes to processing performance.

The first step is to load various inputs into the system. Loading data in Talend is as simple as it can get. Just drop the component which corresponds to the type of the data source, configure that component, and Talend will do the rest. Talend imports data directly from databases and from a wide variety of file formats such as Excel, CSV, EBCDIC, XML, Text Delimited, HL7, JSON, etc. It also integrates with popular third-party applications such as salesforce.com, Marketo, and SugarCRM.

Once data has been imported, it is most important to ensure the data integrity of inputs. This is where the tremendous potential of Talend can be realized. Checks, as simple as ensuring that the Employee ID is always an 8-digit number to as complex as isolated cases in which sales of an employee exceed 10 times his previous month's sales, can be built easily in the Talend process flow. Various checks can be designed and automated as part of your regular sales compensation process. Some of these are highlighted below so that you can start using such checks more effectively.

Referential integrity/Check for missing data

First, define a master list or identify a master file for each of the key dimensions, such as Roster for Employee_ID, Hierarchy for Geography_ID, etc. Loaded inputs during every run are then compared against these master files to identify any missing or extra data in the input files. For example, If the sales data contains no record of sales from territory x, their kick-out reports would indicate that perhaps data for territory x has not been received (see fig 1). Ideally, a master file for each field must be defined at the start of the process to ensure data completeness and correctness.

Similarly, there also are checks for missing data among different types of files. For example, if the roster contains a record of employee 'Adam' and the sales data doesn’t, this would be a feature in a validation report. Such checks have to be built once and then run every month automatically.

Fig 1. Checking if a particular territory is not present in the sales data.

Technical details: Sales data is grouped by region using the AggregateRow_3 component, and this list is matched with the existing Territories list in tMap_3. Inside the tMap_3, an inner join is done, and the matches are flagged.

Correct format and structure of data

You can predefine the schema (format) of all your input files and use Talend to generate errors where the file does not follow the pre-defined format. For example, you might define that the Employee ID will always be a 6-digit number or that the date of the sale will always lie in the month for which data is being processed. Such checks help in detecting manual data entry errors. These checks are extremely simple to build as they require only pre-defining the format.

Fig 2 shows a job that rejects all records which have an incorrect format and stores them in a file, and processes the remaining valid data.

Check for duplicates

Checking for duplicates is important to ensure the accuracy of data. Depending on the nature of the business and type of business process, we can define the duplicate check at any pre-defined combination level (for, eg. One record should exist for each Data Month-Employee ID-Role combination). Simple uniqueness checks, such as validating that a single sale record exists per OrderID, can be built in Talend to either remove duplicate records or highlight such instances. Here is a simple Talend job that de-duplicates data:

Fig 3. Checking duplicate records. In the UniqRow component, we specify the combination of columns that constitute a unique record.

Control totals

Checks can be built to ensure that number of records loaded matches with the number of records actually processed by the upstream system. This ensures that the data is complete and has not been corrupted during transmission.

Sanity checks

These checks highlight specific records with changes that defy historical trends. We can define thresholds to ensure that abnormal values are at least highlighted and are reviewed for consistency before processing them. For example, let’s say that the average sales per month for the last 12 months is 90k. However, sales data received this month drops to 30k. There is a real possibility of this data being incomplete or some other issue with the input file or the upstream system, and detecting this in the validation stage can help prevent the processing of incorrect data. Such analyses are particularly easy to perform for Pharma companies since they receive historical data every month along with the current month’s data. Row-level validations, such as thresholding the revenue of a single order between $100 and $5k can also be automated. Additionally, each of these inputs is summarized using various levels of descriptive statistics and visualizations to identify any other anomalies in the data. A summary report with figures such as maximum revenue of any order, highest contract length, average rep order volume, etc., can easily be generated after the data is processed, and abnormalities will pop out in summary.

Business logic checks

Checks specific to the business process can also be built in a similar way as sanity checks. For example, a check that kicks out whenever the roster file shows an employee from a certain region reporting to a manager of a different region can be easily built. An analysis of the span of control for each of the managers can be built to highlight the manager who is particularly over-leveraged.

In this post, we focused on Incentius’ validation framework and how validations can be designed and implemented on Talend so as to ensure the accuracy of input data before processing final sales compensation. This approach helps increase the accuracy of sales compensation, reducing reruns and saving your analysts valuable time.

Watch out for our subsequent blog posts on this topic. They will include techniques to actually build a sales compensation plan on Talend and build effective output validations and analysis.

If you got this far, we think you’d like our future blog content, too. Please subscribe on the right side.

 

About Author
Pranshu Kacholia

See What Our Clients Say

Mindgap

Incentius has been a fantastic partner for us. Their strong expertise in technology helped deliver some complex solutions for our customers within challenging timelines. Specific call out to Sujeet and his team who developed custom sales analytics dashboards in SFDC for a SoCal based healthcare diagnostics client of ours. Their professionalism, expertise, and flexibility to adjust to client needs were greatly appreciated. MindGap is excited to continue to work with Incentius and add value to our customers.

Samik Banerjee

Founder & CEO

World at Work

Having worked so closely for half a year on our website project, I wanted to thank Incentius for all your fantastic work and efforts that helped us deliver a truly valuable experience to our WorldatWork members. I am in awe of the skills, passion, patience, and above all, the ownership that you brought to this project every day! I do not say this lightly, but we would not have been able to deliver a flawless product, but for you. I am sure you'll help many organizations and projects as your skills and professionalism are truly amazing.

Shantanu Bayaskar

Senior Project Manager

Gogla

It was a pleasure working with Incentius to build a data collection platform for the off-grid solar sector in India. It is rare to find a team with a combination of good understanding of business as well as great technological know-how. Incentius team has this perfect combination, especially their technical expertise is much appreciated. We had a fantastic time working with their expert team, especially with Amit.

Viraj gada

Gogla

Humblx

Choosing Incentius to work with is one of the decisions we are extremely happy with. It's been a pleasure working with their team. They have been tremendously helpful and efficient through the intense development cycle that we went through recently. The team at Incentius is truly agile and open to a discussion in regards to making tweaks and adding features that may add value to the overall solution. We found them willing to go the extra mile for us and it felt like working with someone who rooted for us to win.

Samir Dayal Singh

CEO Humblx

Transportation & Logistics Consulting Organization

Incentius is very flexible and accommodating to our specific needs as an organization. In a world where approaches and strategies are constantly changing, it is invaluable to have an outsourcer who is able to adjust quickly to shifts in the business environment.

Transportation & Logistics Consulting Organization

Consultant

Mudraksh & McShaw

Incentius was instrumental in bringing the visualization aspect into our investment and trading business. They helped us organize our trading algorithms processing framework, review our backtests and analyze results in an efficient, visual manner.

Priyank Dutt Dwivedi

Mudraksh & McShaw Advisory

Leading Healthcare Consulting Organization

The Incentius resource was highly motivated and developed a complex forecasting model with minimal supervision. He was thorough with quality checks and kept on top of multiple changes.

Leading Healthcare Consulting Organization

Sr. Principal

US Fortune 100 Telecommunications Company

The Incentius resource was highly motivated and developed a complex forecasting model with minimal supervision. He was thorough with quality checks and kept on top of multiple changes.

Incentive Compensation

Sr. Director

Most Read
Building a Simple E-Invoicing Solution with AWS Lambda and Flask

In today’s fast-moving distribution industry, efficiency is everything. Distributors need quick, reliable tools to handle tasks like generating invoices and e-way bills. That’s why we created a serverless e-invoicing solution using AWS Lambda and Flask—keeping things simple, cost-effective, and secure. Here’s how we did it and the benefits it brought to distributors.

Yash Pukale

  1. Nov 13, 2024
  2. 4 min read
Scaling Data Analytics with ClickHouse

In the modern data-driven world, businesses are generating vast amounts of data every second, ranging from web traffic, IoT device telemetry, to transaction logs. Handling this data efficiently and extracting meaningful insights from it is crucial. Traditional databases, often designed for transactional workloads, struggle to manage this sheer volume and complexity of analytical queries.

Kartik Puri

  1. Nov 07, 2024
  2. 4 min read
From Pandas to ClickHouse: The Evolution of Our Data Analytics Journey

At Incentius, data has always been at the heart of what we do. We’ve built our business around providing insightful, data-driven solutions to our clients. Over the years, as we scaled our operations, our reliance on tools like Pandas helped us manage and analyze data effectively—until it didn’t.

The turning point came when our data grew faster than our infrastructure could handle. What was once a seamless process started showing cracks. It became clear that the tool we had relied on so heavily for data manipulation—Pandas—was struggling to keep pace. And that’s when the idea of shifting to ClickHouse began to take root.

But this wasn’t just about switching from one tool to another; it was the story of a fundamental transformation in how we approached data analytics at scale.

Chetan Patel

  1. Oct 28, 2024
  2. 4 min read
Designing Beyond Aesthetics: How UI Shapes the User Experience in Enterprise Solutions

UI design in enterprise solutions goes beyond aesthetics, focusing on enhancing usability and user satisfaction. By emphasizing clarity, visual hierarchy, feedback, and consistency, UI improves efficiency and productivity, allowing users to navigate complex tasks seamlessly.

Mandeep Kaur

  1. Oct 23, 2024
  2. 4 min read