Client:
A global e-commerce store specializing in shoulder carriers and related products like backpacks and sunshades.
Challenge:
The client, a global e-commerce company, faced several challenges with tracking and analyzing their performance across different countries. They needed to:
- Consolidate disparate data: Their performance data was scattered across multiple platforms, including Google Ads, Facebook Ads, and Google Sheets, with separate ad accounts for each market (e.g., BeNeLux, France, Spain, Germany/Austria, USA, UK, Norway, etc.).
- Manual reporting inefficiencies: Stakeholders had to manually aggregate data from various platforms to calculate key metrics such as marketing cost, conversion rates, and return on ad spend (ROAS). This process was time-consuming and error-prone.
- Currency conversion complexities: Different ad accounts used different currencies, making it difficult to accurately calculate total marketing costs across countries.
- Performance visibility: They lacked real-time, comprehensive insights into their global performance, making it difficult for stakeholders to make informed decisions quickly.
- Dashboard speed issues: With multiple data sources and large datasets, the initial dashboard was slow to load, taking up to two minutes to render charts, which hindered usability.
Solution:
We created a global dashboard in Looker Studio to streamline and automate the client’s reporting process, allowing them to track performance across all markets in real time. Here’s how we addressed their challenges:
- Data Blending: We blended data from multiple sources (Google Ads, Facebook Ads, Google Sheets) to calculate key metrics such as total marketing cost, cost per action (CPA), conversion rates, and ROAS across different markets. Given the complexity of multiple ad accounts per market, we created specific blends for each market, ensuring accurate tracking across platforms.
- Currency Conversion: To handle different currencies, we created calculated fields in Looker Studio that converted the Norwegian Krone (NOK) to Euros, British Pounds, and US Dollars using exchange rates fetched via Google Sheets’ GOOGLEFINANCE function. This ensured accurate marketing cost calculations across regions.
- Null Value Handling: We implemented a method to convert NULL values to zero in blended metrics, ensuring that calculations like total marketing cost didn’t break when data from one platform was missing. This was done using calculated fields that checked for NULL and replaced them with zero.
- Extracted Data for Speed: To solve the performance issues, we used Looker Studio’s Extract Data connector, which allowed us to pre-fetch and store data for the past 28 days. This reduced the load time of the dashboard from two minutes to near-instantaneous, while still providing stakeholders with the timely insights they needed.
- Automated Order Data: Order data was stored in Google Sheets, which was automatically populated via Zapier and later Integromat. This ensured that order details were always up-to-date in the dashboard, and we applied filters to show country-specific data when needed.
Results:
The implementation of the global dashboard led to significant improvements in the client’s ability to manage their e-commerce business:
- Efficiency Gains: The new dashboard eliminated the need for manual data aggregation, saving the client significant time and reducing the risk of errors in their reporting. Stakeholders now had a consolidated, real-time view of their global performance across all markets and platforms.
- Improved Decision-Making: With real-time access to key performance metrics like ROAS, CPA, and conversion rates, the client’s stakeholders were able to make faster, more informed decisions about their marketing spend and strategies.
- Accurate Currency Conversion: By implementing calculated fields to convert currencies, the client gained accurate insights into their total marketing costs across different regions. This was particularly crucial for calculating ROAS and ensuring that budget allocations were correct.
- Faster Load Times: The Extract Data feature drastically improved the performance of the dashboard, reducing load times from two minutes to nearly instantaneous. This allowed stakeholders to quickly analyze data, adjust strategies, and make decisions without delays.
- Scalable Reporting: The dashboard was flexible and scalable, allowing the client to add new markets or products without the need for significant changes to the existing reporting setup.