{"id":38773,"date":"2022-01-28T15:08:30","date_gmt":"2022-01-28T14:08:30","guid":{"rendered":"https:\/\/clicktrust.be\/blog\/non-classifiee\/tutorial-linking-google-analytics-4-to-google-bigquery\/"},"modified":"2023-07-07T15:21:40","modified_gmt":"2023-07-07T13:21:40","slug":"tutorial-linking-google-analytics-4-to-google-bigquery","status":"publish","type":"post","link":"https:\/\/clicktrust.be\/fr\/blog\/analytics\/tutorial-linking-google-analytics-4-to-google-bigquery\/","title":{"rendered":"Introduction to GA4 linking with BigQuery and connecting with R"},"content":{"rendered":"[vc_row type=”in_container” full_screen_row_position=”middle” column_margin=”default” column_direction=”default” column_direction_tablet=”default” column_direction_phone=”default” scene_position=”center” top_padding=”50″ text_color=”dark” text_align=”left” row_border_radius=”none” row_border_radius_applies=”bg” overflow=”visible” advanced_gradient_angle=”0″ overlay_strength=”0.3″ gradient_direction=”left_to_right” shape_divider_position=”bottom” bg_image_animation=”none” gradient_type=”default” shape_type=””][vc_column column_padding=”no-extra-padding” column_padding_tablet=”inherit” column_padding_phone=”inherit” column_padding_position=”all” column_element_direction_desktop=”default” column_element_spacing=”default” desktop_text_alignment=”default” tablet_text_alignment=”default” phone_text_alignment=”default” background_color_opacity=”1″ background_hover_color_opacity=”1″ column_backdrop_filter=”none” column_shadow=”none” column_border_radius=”none” column_link_target=”_self” column_position=”default” gradient_direction=”left_to_right” overlay_strength=”0.3″ width=”1\/1″ tablet_width_inherit=”default” animation_type=”default” bg_image_animation=”none” border_type=”simple” column_border_width=”none” column_border_style=”solid”][vc_column_text]In one of our previous posts, we already explained what Google BigQuery is and why you should use it as a markete<\/a>r. In this post, we will explain how you can create your very first BigQuery project with Google Analytics data.<\/span><\/p>\n

1) Create a Google Cloud project<\/h2>\n

Google organizes your account with different projects. Each has its own scope, rights, and governance.<\/span><\/p>\n

You\u2019ll need to set up a project. It\u2019s free if you stay on the free tier.\u00a0<\/span><\/p>\n

Step 1<\/b>: Navigate to the <\/span>Google BigQuery Platform<\/span><\/a><\/p>\n

Step 2<\/b>: Click on the drop-down menu and create a new project.<\/span><\/p>\n

\"\"\"\"<\/p>\n

 <\/p>\n

Step 3<\/strong>: Give your project a name, and an organization if relevant. Click on CREATE, you should see your project in the list.\u00a0<\/span><\/p>\n

\"\"<\/span><\/p>\n

 <\/p>\n

If you\u2019re in an organization, check that you have proper rights, and your project fits in the organization\u2019s governance. You may need additional rights to go further (for example, if you want to enable the billing).<\/span><\/p>\n

When you created the project, if you don\u2019t see it in the list, and you didn\u2019t set an organization, check on the drop-down list above: \u201cNo Organization\u201d.<\/span><\/p>\n

\"\"<\/p>\n

 <\/p>\n

Step 4<\/b>: Enable the BigQuery API on this project : on the left menu, click API & Services, then Library. Activate BigQuery.<\/span><\/p>\n

 <\/p>\n

2) Link GA4 with the BigQuery project<\/h2>\n

Step 5<\/b>: Go to your analytics account (https:\/\/www.google.com\/analytics) and click on Admin. Then Big Query Linking under \u201cproduct linking\u201d<\/span><\/p>\n

\"\"<\/p>\n

 <\/p>\n

Step 6<\/b>: Then click on <\/span>Link<\/span>. You\u2019ll see Google projects you have access to. It\u2019s possible to link with a Google project you don\u2019t own. But you\u2019ll need admin rights on the Google Analytics properties and editing on the google project.<\/span><\/p>\n

\"\"<\/p>\n

 <\/p>\n

Step 7<\/b>: Then, on Choose a Big Query project and click on <\/span>Confirm<\/span><\/p>\n

\"\"\"\"<\/p>\n

For longer explanations, check the documentation<\/a>.<\/span><\/p>\n

If you go to the settings of the link, there are two options of updates: Daily (each night) or streaming (continuously). You\u2019ll need to enable billing for streaming.\u00a0<\/span><\/p>\n

 <\/p>\n

3) Add the User ID (optional) in the Google Analytics Setup.<\/h2>\n

To do multidevice analysis on GA4\/BQ, you\u2019ll need a user ID, that is valid across all devices. That\u2019s not the case of Google Analytics (id), called user_pseudo_id, which lies in the GA cookie.<\/p>\n

If you pass a UserID to GA4, it will be present in the BigQuery under the field \u201cUser ID\u201d and therefore enable multi-device\/session analytics, without being dependent on cookies.<\/p>\n

There are several ways to do it. It will depend on the implementation of your Google Analytics on your website. The simplest is to call add a code snippet on your site, after the gtag function<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0gtag('config', '**YOUR GA property ID**, { 'user_id': 'User_ID'<\/span>\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0, 'ad_storage': 'denied' });\u00a0 <\/span><\/pre>\n
Be careful: from now on, you track the behavior of your consumers with a personal\u00a0 identifier. Therefore, you are a processor of personal data and must fill your legal duties like getting consent. Act with caution: it is considered as server-to-server tracking, so may be out of your cookie policy. You can set limits to the usage of this user_id, by example setting the parameter \u201cad_storage : \u2018denied\u2019).<\/span><\/p>\n
4) Play with data on the UI<\/h2>\n
Go to the Big Query platform<\/a><\/span><\/p>\n
You\u2019ll see on the left the projects: expand and click on events_ (intraday) if you chose streaming)<\/span><\/p>\n
\"\"<\/p>\n
 <\/p>\n
Tip: a day of data is valid for only 60 days. You can expand by going in the DETAILS tab, then EDIT and specify a new expiration date. You\u2019ll need to make it for each day, one by one, though.<\/span><\/p>\n
\"\"<\/p>\n
 <\/p>\n
You can preview the data on the tab Preview. If you\u2019re used to MS SQL, you\u2019ll see something new: nested tables.\u00a0 It\u2019s like a table in a cell.<\/span><\/p>\n
\"\"<\/span><\/p>\n
If you add the CRM id in the customer data ID of Google (User ID in Big query), you\u2019re able to merge the online behavior of your customers with their sales and offline behavior.<\/span><\/p>\n
If you click on Query, you can write your first query<\/span><\/p>\n
SELECT<\/span> *<\/span> FROM<\/span> `mediadata-249609.analytics_249502555.events_20220104`<\/span> LIMIT<\/span> 1000<\/span><\/pre>\n
In the upper right corner, you\u2019ll see how much data the query will process.\u00a0<\/span><\/p>\n
\"\"<\/span><\/p>\n
(you won\u2019t be charged for the first TB per month).<\/span><\/p>\n
You can read all the days by setting an asterisk on the table name :\u00a0<\/span><\/p>\n
SELECT<\/span> *<\/span> FROM<\/span> `mediadata-249609.analytics_249502555.events_*`<\/span> LIMIT<\/span> 1000<\/span><\/pre>\n
Beware of data consumption! Try your queries on a specific day, then expand it on the whole data set when you\u2019re sure.<\/span><\/p>\n
If you click on run, you should see your data<\/span><\/p>\n
\"\"<\/p>\n
 <\/p>\n
You can also visualize your data in DataStudio.<\/p>\n
You may review your SQL to use this platform. Useful tips to consider :<\/p>\n