Connecting GA4 to Tableau using Big Query

Recently you may have been told that the older version of Google Analytics is going to be decommissioned. For marketing and UX analysts out there we are being forced onto Google’s ‘amazing’ new GA4 platform. At this current time Tableau’s ‘Google Analytics’ connector has no support for GA4. Since there has been no response from Tableau developers regarding when a possible GA4 connector may be made available our best option is to utilize the existing BigQuery connector to get access to this data.

When connecting the GA4 property to Google BigQuery within the property settings in Google Analytics, I recommend setting Frequency to Daily unless your specific data use case requires streamed data. This will have zero cost within your Google Cloud Billing and create tables with batched daily event tables.

As stated in the Google BigQuery Docs a wildcard table represents a union of all the tables that match the wildcard expression. For example, the following FROM clause uses the wildcard expression gsod* to match all tables in the noaa_gsod dataset that begins with the string gsod.

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

SELECT `events_*`.`event_date` AS `event_date`,
  `events_*`.`event_timestamp` AS `event_timestamp`,
  `events_*`.`event_name` AS `event_name`,
  `events_*`.`event_previous_timestamp` AS `event_previous_timestamp`,
  `events_*`.`event_value_in_usd` AS `event_value_in_usd`,
  `events_*`.`event_bundle_sequence_id` AS `event_bundle_sequence_id`,
  `events_*`.`event_server_timestamp_offset` AS `event_server_timestamp_offset`,
  `events_*`.`user_id` AS `user_id`,
  `events_*`.`user_pseudo_id` AS `user_pseudo_id`,
  `events_*`.`user_first_touch_timestamp` AS `user_first_touch_timestamp`,
  `events_*`.`stream_id` AS `stream_id`,
  `events_*`.`platform` AS `platform`,
 -- `events_*`.`event_params`.`key` AS `event_params_key`,
  e.key AS `event_params_key`,
  e.value AS `event_params_value`,
  `events_*`.`privacy_info`.`analytics_storage` AS `privacy_info_analytics_storage`,
  `events_*`.`privacy_info`.`ads_storage` AS `privacy_info_ads_storage`,
  `events_*`.`privacy_info`.`uses_transient_token` AS `privacy_info_uses_transient_token`,
  --`events_*`.`user_properties`.`key` AS `user_properties_key`,
   u.key AS `user_params_key`,
  u.value AS `user_params_value`,
  `events_*`.`user_ltv`.`revenue` AS `user_ltv_revenue`,
  `events_*`.`user_ltv`.`currency` AS `user_ltv_currency`,
  `events_*`.`device`.`category` AS `device_category`,
  `events_*`.`device`.`mobile_brand_name` AS `device_mobile_brand_name`,
  `events_*`.`device`.`mobile_model_name` AS `device_mobile_model_name`,
  `events_*`.`device`.`mobile_marketing_name` AS `device_mobile_marketing_name`,
  `events_*`.`device`.`mobile_os_hardware_model` AS `device_mobile_os_hardware_model`,
  `events_*`.`device`.`operating_system` AS `device_operating_system`,
  `events_*`.`device`.`operating_system_version` AS `device_operating_system_version`,
  `events_*`.`device`.`vendor_id` AS `device_vendor_id`,
  `events_*`.`device`.`advertising_id` AS `device_advertising_id`,
  `events_*`.`device`.`language` AS `device_language`,
  `events_*`.`device`.`is_limited_ad_tracking` AS `device_is_limited_ad_tracking`,
  `events_*`.`device`.`time_zone_offset_seconds` AS `device_time_zone_offset_seconds`,
  `events_*`.`device`.`browser` AS `device_browser`,
  `events_*`.`device`.`browser_version` AS `device_browser_version`,
  `events_*`.`geo`.`continent` AS `geo_continent`,
  `events_*`.`geo`.`country` AS `geo_country`,
  `events_*`.`geo`.`region` AS `geo_region`,
  `events_*`.`geo`.`city` AS `geo_city`,
  `events_*`.`geo`.`sub_continent` AS `geo_sub_continent`,
  `events_*`.`geo`.`metro` AS `geo_metro`,
  `events_*`.`app_info`.`id` AS `app_info_id`,
  `events_*`.`app_info`.`version` AS `app_info_version`,
  `events_*`.`app_info`.`install_store` AS `app_info_install_store`,
  `events_*`.`app_info`.`firebase_app_id` AS `app_info_firebase_app_id`,
  `events_*`.`app_info`.`install_source` AS `app_info_install_source`,
  `events_*`.`traffic_source`.`name` AS `traffic_source_name`,
  `events_*`.`traffic_source`.`medium` AS `traffic_source_medium`,
  `events_*`.`traffic_source`.`source` AS `traffic_source_source`,
  `events_*`.`event_dimensions`.`hostname` AS `event_dimensions_hostname`,
  `events_*`.`ecommerce`.`total_item_quantity` AS `ecommerce_total_item_quantity`,
  `events_*`.`ecommerce`.`purchase_revenue_in_usd` AS `ecommerce_purchase_revenue_in_usd`,
  
FROM `prospaanalytics-235700.analytics_264677210`.`events_*` `events_*`, UNNEST(`events_*`.`event_params`) e, UNNEST(`events_*`.`user_properties`) u