Microsoft Power BI (PBI) is a powerful business data analysis tool, recognized as a leader in the Garner research report, just like Tableau. When PBI is integrated with Google Analytics4 (GA4) data, it becomes more efficient in providing insights into website performance. However, as of now, PBI does not have a native GA4 data connector, so you need to first store GA4 data in Google Cloud’s BigQuery before connecting it to PBI. In fact, you can build your own connector to link PBI to GA4, but if you want to sustain your website and store data, BigQuery is a must.
重點整理
What is BigQuery?
BigQuery is a cloud-based data warehousing and analytics platform provided by Google Cloud. It can rapidly process massive amounts of data, even at the PB scale, and export data in various formats or connect with other data analysis software and platforms, such as Google Data Studio.
Advantages of Using BigQuery
Cost-effective, even free
BigQuery offers free storage for up to 10GB of data and up to 1TB of monthly query volume. Even if you exceed these limits, the cost is as low as $0.01 or $0.02 per GB, making it much more economical than the older commercial version of Google Analytics, which can cost nearly $5 million TWD. Additionally, GA4 only retains data for up to 14 months.
Complete capture of visitor browsing history
You can upload data from CRM systems or offline conversions, allowing you to label users, segment them, and implement various marketing strategies based on different marketing goals.
Linking GA4 and BigQuery
Create a project in Google Cloud
Click the triangle icon in the dropdown menu at the top to add a new project.
Set up the link to BigQuery in GA4
Go to Settings > BigQuery Linking > Link > Choose the project you just created.
Simple setup to complete
The original data will come in the next day, and connecting is straightforward. Your entire website will become more integrated into the Google ecosystem, potentially saving you money.
Linking PBI and BigQuery
- Get data > Database > Google BigQuery.
- Sign in > Connect, choose the GA4 BigQuery project > Load. Connection settings can be Import (copy data into PBI) or DirectQuery (real-time connection and data updates), as both are acceptable.
Integrate data for all dates
Since GA4 uploads a new data table every day, you’ll need to use PBI’s append query function to merge all the data into one table.