Skip to main content

Integrate Microsoft Power BI with Google Analytics 4 through BigQuery.

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.

Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4

 

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

  1. Create a project in Google Cloud

    Click the triangle icon in the dropdown menu at the top to add a new project.
    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (1)

  2. Set up the link to BigQuery in GA4

    Go to Settings > BigQuery Linking > Link > Choose the project you just created.
    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (2)
    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (3)

  3. Simple setup to complete

    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (4)

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.

Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (6)

 

Linking PBI and BigQuery

  1. Get data > Database > Google BigQuery.
    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (7)
  2. 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.
    Blog 內文圖 - 透過BigQuery,串接微軟PowerBI與Google Analytics 4 (10)

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.

Leave a Reply