Report Optimization and Forecasting in Power BI for an Affiliate Management Agency
- P2H helped a leading affiliate management company improve its data reporting efficiency, reduce costs in AWS, and enhance data-driven decision-making.
Client: Acceleration Partners, LLC
Industry: Affiliate marketing
* An example report, this is not a real project.
Project
Acceleration Partners has helped many brands grow with innovative affiliate marketing services. They design and manage promotional campaigns, build influencer programs, and nurture business relationships. As a professional agency, their service offering is a strategy supported by relevant data.
The company’s analytics platform uses Microsoft Power BI to collect business metrics across the affiliate network space. They rely on its robust reporting functions to benchmark various KPIs, including metrics related to marketing campaigns and partner’s performance. Some of their clients required monthly and even weekly reviews of their affiliate campaigns, so the system had to work perfectly.
As Acceleration Partners expanded its client base, its databases grew from thousands to several hundred thousand records. Eventually, generating a single report in Power BI might take more than five hours.
While the client initially asked us to analyze and optimize their reporting system, in the course of further cooperation with AP, we exposed other areas for improvement. Ultimately, it soon became apparent that we had to reconfigure most of the back end.
Another part of the client’s task was to solve the issues caused by the exponentially growing infrastructure costs. Our Power BI analyst reviewed the inefficiencies and greatly improved both performance and computation costs, thus reducing costs for AP.
Key Takeaways:
-
P2H helped a leading affiliate management company improve its data reporting efficiency, reduce costs in AWS, and enhance data-driven decision-making.
-
Acceleration Partners has helped many brands grow with innovative affiliate marketing techniques.
-
They initially asked us to analyze their reporting systems and consult on possible solutions to their challenges.
Challenges
-
Many Power BI analysts don’t devote enough attention to optimization and scalability. This may not pose a problem until a platform starts trying to handle the increased load. But it can be difficult to see exactly what’s causing the platform’s performance issues, which is why we needed to study the data source and model layers first.
-
We found that all of the data for Power BI was in very large files. Data for metrics that the company required continuously and data that required only infrequent access were bundled together, putting a strain on the system and decreasing the system’s performance.
-
Most of the calculations in reports and the requests to back-end sources were excessive. Many operations weren’t pre-calculated, meaning that calculations had to be calculated at runtime. This affected computing performance negatively and increased AWS computing costs. In most cases, we had to manually rework the reports.
-
Our consultant also ran into data limitations of the Power Query online service from Power BI. The company’s cloud storage plan limited single file size to 1 GB. So, we had to find ways to reduce file sizes so they wouldn’t have to upgrade to a more expensive subscription.
Solution
Data grouping
Our Power BI expert reorganized large tables that accumulated all the business metrics and thus became unmanageable. Some of the data were needed only once or weren’t relevant after a specific period. We decoupled those data into separate tables and archived them to reduce file sizes.
Then, we divided the rest of the table into several smaller tables based on the data type. Instead of one gigantic file, the client had tables categorized by parameters like the creation date, customer location, and other variables. This alone made report generation faster and alleviated the computing strain on AWS.
Back-end and front-end optimization
We modified the back end in Power Query to improve the system’s performance and stability. Here are some of the steps we took:
Minimized calculations
We removed about 70% of queries that affected the system and were excessive. This required us to tune DAX expressions and RLS rules and rewrite logic to avoid performance-expensive formulas. We also reduced the number of rows, columns, and measures performed at runtime.
Optimized data source
We fully rewrote a significant number of the Power Query parameters. Our Power BI analyst applied appropriate indexes, defined table partitions, and materialized summarized data with indexed views.
Reduced queries
Our engineer replaced expensive processes with streaming operations that don’t read all the data before returning results. In addition, P2H kept transformations in query design to a minimum to improve performance.
Used preload
We also applied preload to ensure that scripts for embedding Power BI content were downloaded before navigating the file. This greatly improved content display speed in embedded reports and dashboards.
Configured table relationships
Bidirectional filtering often leads to performance issues, so we applied single-direction relationships and referential integrity in models wherever possible.
Used binning, clustering, and compression
The files were already lighter after we divided tables across files and minimized calculations. On top of that, by using binning, clustering, and compression techniques, we ensured all the files are under 1 GB.
Forecasting module
During further collaboration with the client’s team, it was decided to create a forecasting system, as it would provide benefits across the organization. Specifically, this would allow Acceleration Partners to estimate marketing expenses and channel performance and determine which investment possibilities would be the most profitable for their clients.
As part of the initiative, the team developed custom machine learning models. Our engineer trained the models with the client’s historical business data, used AutoML to refine their accuracy, and implemented them in Power BI. The final model could predict affiliate marketing performance based on historical data and follow customizable variables like:
- Company size;
- Product group;
- Client base;
- Target location;
- Industry;
- Marketplace type (online or offline);
- Business type.
Acceleration Partners could use machine learning models to predict the profitability of their clients’ campaigns and test potential partnerships.
Tech stack and processes
Project duration
Team composition
Our Power BI engineer carried out the project under the supervision of the project manager and with the indirect participation of the client’s CTO and business analyst.
Schedule a kickoff call with our Lead BISchedule a kickoff callResults
We fulfilled the client’s core requirements and continue to enhance their analytical capabilities. Our achievements so far include the following:
-
Power Query optimization accelerated the report generation capabilities and solved critical bugs.
-
The improved Power BI performance made the platform more convenient for the client’s analytics and accounting teams.
-
We decreased the time needed to generate analytics reports in Power BI from five hours to less than ten minutes.
-
The data grouping and minimized calculations led the company to save cost for computing calculations in AWS.
-
The client was pleased with the analytics module, which allowed them to forecast income from different marketing campaigns, predict the performance of future partners, and prioritize the most profitable channels.
Future plans
-
Acceleration Partners wants us to optimize the rest of their reports. At the same time, we take necessary measures to help the client avoid similar issues in the future. Additional work will include back-end and front-end development for the Power Query platform.
-
Acceleration Partners is currently reworking its input data setup for AWS. We aim to optimize Power BI for the new setup, further reducing technical overhead and processing costs.
-
On top of this project, we’re working to transform clients’ designs into high-performing applications, websites, and automated solutions.
Next case study
Schedule a kickoff call with our Lead BA
Thank you for getting in touch!
We appreciate you contacting us. One of our colleagues will get back in touch with you soon!
Have a great day!