How to Build a Conversion Funnel Report With the BigQuery GA4 Dataset and Visualize Your Data With Looker Studio or Power BI

We love visualization apps at Calibrate Analytics. Clients often ask us which visualization app is the best for their organization, and in many cases, we suggest multiple applications based on the different stakeholder requirements. For example, a digital agency may decide it's best to establish a Power BI reporting server for customer-facing reporting, but they may find that their media planners and campaign manager are power users in Looker Studio and prefer to use that for their own reports.

If you like the idea of being able to support multiple visualization apps, you will need to select a data warehouse provider that your desired applications can connect to. Major visualization applications like Looker Studio, Microsoft Power BI, and Tableau (there are many more) can connect to data housed in Google, Azure, AWS or Snowflake data warehouses.

This article will demonstrate how you can connect multiple visualization apps to a GA4 dataset in Google BigQuery, and we'll also show you how to build a funnel report with the standard GA4 ecommerce events.

Stream your GA4 data to BigQuery
Unnest the conversion funnel events (with sample query)
Configure a scheduled query and flattened table
Connect to your table with Looker Studio
Connect to your table with Microsoft Power BI

Stream Your GA4 Data to BigQuery

You can follow Google's setup guide for streaming your GA4 data to BigQuery here: https://support.google.com/analytics/answer/9823238?hl=en#zippy=%2Cin-this-article

Unnest the Standard E-commerce Funnel Events in GA4

Copy the following query and paste it into your query editor in Google BigQuery. Update the project ID and dataset ID with the IDs from your project and dataset. Run the query and verify that you can see data populating in all of the columns.

    
    SELECT
        PARSE_DATE("%Y%m%d", event_date) as date,
        traffic_source.medium as medium,
        traffic_source.source as source,
        device.category as device,
        
        COUNT(DISTINCT(
          SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_id'
        )) AS sessions,
      
      
        COUNT(DISTINCT(
          SELECT value.int_value
          FROM UNNEST (event_params)
          WHERE event_name = 'view_item_list' AND key = 'ga_session_id'
        )) as view_item_list,
      
      
        COUNT(DISTINCT(
          SELECT value.int_value
          FROM UNNEST (event_params)
          WHERE event_name = 'add_to_cart' AND key = 'ga_session_id'
        
        )) as add_to_cart,
      
      
        COUNT(DISTINCT(
          SELECT value.int_value
          FROM UNNEST (event_params)
        
          WHERE event_name = 'begin_checkout' AND key = 'ga_session_id'
          )) as begin_checkout,
        
        COUNT(DISTINCT ecommerce.transaction_id) as purchase
      
      
    FROM `your-project-id.your-ga4-dataset.events_202*` 
      
      
    GROUP BY
        date,
        medium,
        source,
        Device

Configure a Scheduled Query and Flattened Table

After you've confirmed that the query is working, click on the scheduled option at the top of the query editor and then click "create scheduled". Complete the scheduled query form and be sure to select overwrite as the write preference for your destination table.

Note: We suggest optimizing this query with date logic after you've finished the initial build, so you're only updating the table with new/yesterday's data. If you're dealing with very large GA4 datasets, you'll want to make sure to optimize the query so that the table only pulls new data and appends it to your destination table.

After your initial build of the table, you can add the following WHERE clause to your query and set the scheduled query to append as the write preference. This will append the data from two days prior to your destination table.

    
        WHERE PARSE_DATE("%Y%m%d", event_date) = CURRENT_DATE() - 2

Connect to Your Table With Looker Studio

Choose "add data" in the Looker Studio menu, and navigate to the Google BigQuery source option. Select your project name, dataset, and table in the user interface.

Connect the Data to a Funnel Chart

Choose a funnel chart and follow the chart instructions. In our example, these are the funnel steps included:

Total sessions: Orange
Distinct view_item_list events: Blue
Distinct add_to_cart events: Purple
Distinct begin_checkout events: Green
Distinct purchase events: Pink

Connect Microsoft Power BI to Your Google BigQuery Project and Dataset

Choose "Get data" in the main menu and navigate to "Get data to get started". Select "Database" and navigate to "Google BigQuery" in the selection box on the right. Authenticate with your Google Cloud Console account that has access to the dataset your funnel table is stored in.

Connect Power BI to the Conversion Funnel Report Table

Search for "funnel" and select your table from the corresponding project and dataset folders.

Connect Your Data to a Funnel Chart

Select the funnel chart in the system chart menu and add the corresponding metrics as values. Use date filtering and styling to achieve the same visualization we created in Looker Studio.