Effective Analytics on Power BI Platform for an Online Store: How We Managed to Enhance Advertising Campaigns and Reduced CPO Multiple Times
- 4 min
- 12.04.2024
Our agency helped a major perfume store increase its monthly visitors to about 70,000 and orders to approximately 1,500 through targeted advertising campaigns.
Project Goal: Maximize the number of orders. We faced a significant challenge with decreasing order volumes and rising CPO, which needed quick resolution.
Spoiler Alert: We successfully reduced CPO, leading to an increase in orders by more than 150%.
Key Client KPIs:
- Order cost, defined CPO price.
- Only count orders that met the first-click attribution model in the ad account.
Promotion Process:
- We connected 85% of orders through Google Merchant’s “Smart Shopping Campaigns”.
- Google created ads based on the product feed and showed them to users visiting the website.
- 85% of orders generated through this method.
Advantage: Google uses machine learning to make advertising as effective as possible.
Main Challenge Identified: Initial efforts using Google Sheets required manual updates to track spending and CPO across channels weekly, which was inefficient and often inaccurate.
How Reports Were Managed: We gathered ad spending data from ad accounts and matched it with conversion information, but couldn’t see all the necessary details.
Three Main Issues with Manual Reports:
1. Lack of Daily Dynamics: Daily changes in buyer interest require daily reports to monitor spending and orders, which is crucial for timely campaign adjustments.
2. Missing Campaign and Product Data: The volume of data made it difficult to update reports frequently.
3. Inconsistent UTM Tags: Often, UTM tags did not match the actual campaign names, complicating data accuracy.
Detailed Analytics: Generally, we conducted in-depth analytics when sales dropped or CPO increased, otherwise relying on indirect metrics like user engagement and conversion rates.
Inconsistencies in Conversion Data: Specific products showed low conversions and high CPO, prompting us to deactivate them, though they performed well in other settings.
Solution: Automating Reports in Power BI
To overcome the challenges of manual reporting and enable quick changes to ad campaigns, we automated the data integration process.
Our Automation Process:
- Combined three software solutions: Power BI for business analytics, BigQuery for cloud database, and R programming language;
- Built a unique solution for the client’s needs;
- Extracted data from ad accounts via API;
- Retrieved conversion data;
- Applied UTM tags;
- Matched all information using R;
- Uploaded data to the BigQuery cloud database;
- Visualized data comprehensively using Microsoft’s Power BI.
This automation resulted in a robust reporting system that provides extensive data for proactive decision-making.
Parameters Used:
- Channel;
- Campaign;
- Product;
- Device;
- Geographic targeting;
- Other parameters.
Key Metrics:
- Impressions,
- Clicks,
- Spending,
- Conversions,
- CPO,
- Additional metrics.
Updated UTM Tags
We needed to reapply all UTM tags to match data from ad accounts correctly. Updated tags included dynamic parameters, like {campaignid}, previously absent in many campaigns.
Specifics of Google Smart Shopping Campaigns
Smart Shopping Campaigns have fewer optimization opportunities than regular ones, allowing changes only to:
- List of advertised products,
- Geographic targeting,
- Device type (desktop, mobile, tablet),
- Placement sites.
Significant Results
CPO decreased significantly, and orders grew by more than 150%, providing:
- Data extraction from ad accounts and analytics,
- Integration into Power BI we designed,
- Automated data matching,
- Hourly updates.
We optimized the process, eliminating the need for manual report formation, now controlling dynamics in real-time to monitor CPO drops and order changes.
Mobile Reporting
Now it’s possible to track ad campaign performance on your smartphone with our mobile Power BI reports, enabling quick monitoring and immediate data access.
Steps to Access Mobile Reports:
1. Download the Power BI app on your smartphone.
2. Link your email to the Power BI account.
3. Provide access to this account and email.
4. Get access to the report from us.
5. Start reviewing reports on the go.
Mobile Insights: Our mobile reports provide real-time insights into order dynamics and CPO changes, helping clients understand ad effectiveness and make necessary adjustments.
Blog 