CASE STUDIES

Client:

Multiple clients under one account, ranging from service-based businesses to eCommerce companies.

Challenge:

Managing over 20 clients presented significant challenges in terms of tracking and reporting performance metrics. Each client had unique requirements and data sources, including GA4, CallRail, Meta Ads, and various eCommerce platforms. Specific challenges included:

• Multiple Data Sources: For service-based clients, we tracked sessions, users, calls (GBP and organic), and form submissions (leads). For eCommerce clients, we also tracked transactions and revenue. These metrics were sourced from multiple platforms including GA4, CallRail, and Google Sheets.
• Data Fragmentation: Clients used different platforms to collect data. Some eCommerce clients opted for native platform data (like BigCommerce) for orders and revenue, while others relied on GA4.
• Cross-Platform Inconsistencies: Not all clients used GA4 for lead tracking, while some used third-party platforms like Book Like A Boss. This required us to consolidate and blend data from various sources, including Google Sheets.
• Connector Limitations: Looker Studio’s native connectors couldn’t handle multiple GA4 or CallRail accounts under one dashboard, necessitating custom solutions for fetching and blending data.
• Discrepancies Between GA4 and BigQuery: The raw GA4 data in BigQuery sometimes showed discrepancies compared to the GA4 interface, which posed challenges in aligning expectations with clients.
• Performance: With so many data sources and custom scripts pulling data into BigQuery, the performance of the dashboard was critical to ensure that insights were delivered in a timely manner.

Solution:

We created a universal dashboard in Looker Studio that consolidated all the necessary data for each client into a single unified view. Here’s how we approached the solution:

• Data Aggregation via BigQuery: Since the native GA4 connector in Looker Studio couldn’t handle multiple properties, we pushed all clients’ GA4 data to BigQuery using GA4’s native BigQuery connector. This allowed us to handle and process the data centrally, enabling more flexible reporting.
• Custom Scripts for Data Integration: We wrote Apps Script code to pull data from CallRail and Meta Ads APIs and push this data to BigQuery. This helped us work around Looker Studio’s connector limitations, especially for CallRail and Meta Ads, ensuring that we could track key metrics like calls and ad performance.
• Custom Views in BigQuery: Given the variability in client requirements, we created multiple views in BigQuery tailored to the specific needs of each client. For instance, some clients wanted to pull orders and revenue data from GA4, while others preferred to use data from BigCommerce or Google Sheets. We then used SQL to join these views into a unified table for the dashboard.
• Unified Reporting: The final dashboard was structured to provide a single table for all clients, showing metrics such as organic users, calls, form submissions, orders, and revenue. We included dropdown filters so stakeholders could toggle between SEO and PPC data, as well as date selectors for custom time ranges.
• Google Ads and Meta Ads Pages: We added separate pages to the dashboard that provided detailed insights into Google Ads and Meta Ads performance. Google Ads data was fed into BigQuery via a native data transfer setup, while Meta Ads data was integrated through our custom Apps Script solution.
• Handling Discrepancies: We managed client expectations by explaining that the raw data in BigQuery might not match 100% with the GA4 interface due to Google Signals and other adjustments made by Google. This transparency helped mitigate concerns about data discrepancies.

Results:

The implementation of this universal dashboard delivered significant benefits for both the agency and its clients:

• Streamlined Reporting: The dashboard consolidated data from GA4, CallRail, Google Ads, Meta Ads, BigCommerce, and Google Sheets into one unified view. This eliminated the need for manual reporting or switching between individual client dashboards and provided clients with real-time, actionable insights.
• Scalable Solution: By centralizing data in BigQuery and using custom scripts to pull data from various sources, the solution was highly scalable. We could easily add new clients or data sources without disrupting the existing setup.
• Client-Specific Views: We created customized reporting views for each client, allowing us to cater to the specific metrics that mattered most to them, whether they were service-based or eCommerce clients.
• Improved Decision-Making: The dashboard provided stakeholders with accurate and up-to-date data, enabling them to make faster, data-driven decisions across both SEO and PPC campaigns. For example, the client was able to see at a glance which accounts were improving or declining in performance compared to the previous period.
• Transparency and Trust: By addressing the discrepancies between the GA4 interface and the raw BigQuery data upfront, we were able to build trust with clients and provide them with a clearer understanding of their data.
• Custom Solutions: Our decision to build custom scripts for data integration rather than relying on expensive ETL solutions allowed us to maintain control over the data and ensure that it was processed exactly as needed. This also reduced costs for the agency and clients alike.