The Challenge

Our client required a consolidated reporting system that combined analytical data from GA4 with detailed user-centric information stored in Sanity. However, the data were scattered across platforms with no relational information to bridge the gap. Specifically:

• Sanity stored user-centric data, such as user profiles and preferences
• GA4 contained analytical data, such as user behaviour, session details and engagement metrics
• There was no common identifier between the two systems to link the data

This lack of relational information made it impossible to create a unified report that combined user-centric data with analytical insights.

The Solution

We implemented a three-step solution to overcome this challenge:

1. Bridging Sanity and Firebase

The first step was to establish a common identifier between Sanity and GA4. To achieve this, we made adjustments to the Flutter app:

• We modified the app to replace the GA4 user ID with the Sanity user ID
• This modification ensured that every user interaction tracked in GA4 could be linked to the corresponding user data in Sanity

This bridge was crucial in creating a relational connection between the two systems.

2. Centralising Data in BigQuery

Once we had established the bridge, the next step was to centralise the data for analysis. We used Google BigQuery as the data warehouse:

• GA4 Data: We imported GA4 data into BigQuery using its native integration
• Sanity Data: We exported and imported Sanity data into BigQuery

By centralising the data in BigQuery, we created a unified dataset where user-centric data from Sanity could be joined with analytical data from GA4 using the common user ID.

3. Visualising Data in Looker Studio

With the data centralised in BigQuery, the final step was to create a visual representation of the consolidated reports. We chose Looker Studio (formerly Google Data Studio) for this purpose:

• We connected Looker Studio to BigQuery to pull the unified dataset
• We created custom dashboards to display key metrics, such as user behaviour, engagement and preferences, alongside detailed user-centric information
• The client gained access to holistic reports that combined analytical insights with user-specific data

Results

The solution provided our client with a powerful, consolidated reporting system that offered:

1. Unified Insights: The ability to view user-centric data alongside analytical metrics, enabling deeper insights into user behaviour and preferences
2. Custom Dashboards: Intuitive and visually appealing Looker Studio dashboards for data exploration
3. Scalability: A BigQuery-based system capable of handling large volumes of data and scaling with the client's needs

Key Takeaways

This project highlights the importance of creating relational connections between disparate data sources to enable consolidated reporting. By bridging Sanity and GA4 through Firebase, centralising data in BigQuery, and visualising it in Looker Studio, we delivered a robust solution that met our client's needs.

Lessons Learned:

• Data Integration is Critical: Establishing a common identifier between systems is essential for unified reporting
• Centralised Data Warehousing: BigQuery proved to be an excellent choice for handling and joining large datasets
• Visualisation Matters: Looker Studio's flexibility enabled us to create custom dashboards tailored to the client's requirements

Conclusion

This project demonstrates how thoughtful integration and data engineering can overcome the challenges of scattered data. By leveraging tools such as Sanity, GA4, Firebase, BigQuery and Looker Studio, we delivered a solution that empowered our client with actionable insights and a consolidated view of their data.

If you are facing similar challenges with scattered data and need a unified reporting solution, please contact us. Together, we can build something remarkable.