TL;DR:
Problem: The accounting team manually calculates employee sales commissions every month, which is time-consuming and prone to errors.
Opportunity: Automating this process could save 40 hours of work monthly and potentially reduce calculation mistakes.
Solution: Build a custom ETL pipeline that extracts raw sales data from the internal accounting software, transforms it for each employee’s specifications, and loads it into a database. The accounting team can then access the final report via our Excel add-in, flyline, keeping their familiar workflow.


The Problem

Every month, our client’s accounting team spends hours manually calculating sales commissions for each employee based on their contract. These contracts are complex, incorporating variables such as:

  • Commission draw
  • Different commission rates for various product categories
  • Escalators that increase commissions based on sales volume
  • Timing of when the sale was closed or paid

With each employee having a unique contract, manually processing all these factors is a massive time investment. Moreover, the current system, which relies on Excel spreadsheets to host and transform the data, is prone to data entry mistakes and is limited in its ability to handle the complexities required for automation.

The Opportunity

By automating the calculations, the company can save up to 40 hours of manual work per month. This also ensures consistency, reducing any errors stemming from Excel’s limitations.

Instead of manually calculating commissions, the accounting team can focus on more strategic tasks, knowing that the data is accurate and readily available through an Excel interface they’re already comfortable with.

The Solution

To address this challenge, a custom ETL (Extract, Transform, Load) pipeline was built. This pipeline will automate the data flow from the company’s internal accounting system to a standardized output accessible via Excel for easy reporting.

Step 1: Extract the raw data

The raw sales data is automatically output by the internal accounting software in a structured csv format, which is then dropped into a cloud storage bucket (such as Google Cloud Storage).

Step 2: Transform Data

The upload triggers our function that performs the necessary transformations of the data. This event-driven system ensures that as soon as the raw data is available, the ETL pipeline begins running automatically, without any need for manual initiation. For the user, the data transformation happens instantaneously. Behind the scenes, our function is applying the complex logic defined in a Python script to the raw data, leveraging the Python pandas library.

Step 3: Load the Data into a Database

After the data is transformed, it needs to be loaded into a database that can be easily accessed by the accounting team. Since the team prefers working in Excel and has no programming expertise, flyline is the perfect delivery tool. Flyline allows them to pull the transformed data directly from the database into their existing Excel workflow. We then built a custom Excel tool that pulls the transformed data into the proper format for the client’s end of month reporting. See how flyline works in action below:


Conclusion

By automating the sales commission calculation process with an ETL pipeline, our client will save significant time and reduce errors. Our solution extracts data from the existing internal systems, applies transformation rules behind the scenes, and delivers the results to the accounting team in a familiar format via flyline and Excel.

Looking for a customized data ETL pipeline for your business? Want to tranform your data processing with flyline? Contact our team and get started today.