How to Combine Four Data Sources into Convenient Reports
Our partner actively attracts users through paid traffic channels, including Meta Ads and Google Ads. However, there was a problem: each tool provided separate reports, and staff had to manually combine them. The actual number of leads was visible only in the CRM system, lead sources were in Google Analytics 4 (GA4), and expenses were shown in advertising dashboards.
Our task was to combine all sources into one automatic
report to minimize manual work.
We used primarily Google tools and services to build
end-to-end analytics, which allowed us to automate data collection, processing, and visualization. Below is the architecture diagram for the solution.
The solution is long-term and stable, and using all tools within the Google Cloud infrastructure costs no more than five dollars per month for this volume of data.
After receiving all the necessary information from the client, we created report templates in Miro and approved them before starting the core dashboard work. This saved time and avoided misunderstandings in further dashboard development.
We used DBT (Data Build Tool), which helps manage projects containing multiple SQL scripts. DBT structures SQL code, significantly easing the process of transformation and orchestration.
Orchestration is the automated process of building tables in the database according to a schedule and in a specific sequence.
This reduces manual labor and the risk of errors.
Requests from the site are sent to GA4 without an ID, making it difficult to match data from GA4 with the CRM system. It's not possible to implement ID transfer.
Requests from the site are sent to GA4 without an ID, making it difficult to match data from GA4 with the CRM system. It's not possible to implement ID transfer.
Finally, we connected the data marts to the Looker Studio visualizer, where we prepared the final dashboards. Below are some examples from the finished dashboard.
