This article describes the setup steps for Google BigQuery → Google Analytics 4 (via SFTP) pipeline. OWOX BI will automatically create and maintain an SFTP server by providing secure and private connections.
Supported data types
- Cost data: 3rd party (non-Google) ad network clicks, cost, and impression data;
- Item data: product metadata like size, color, style, or other product-related dimensions;
- User data: user metadata, e.g., a loyalty rating or lifetime customer value, that you can use to create segments and remarketing lists;
- Offline events: offline events from sources that don't have an internet connection or that otherwise don’t support real-time event collection.
Limits
On the Google Analytics 4 side:
- You can upload data for a maximum of the last 90 days;
- Flat structure in source table;
- The source data should not contain duplicate keys (e.g. two
user_idfields); - GA4 does not allow the keywords data to be uploaded;
- To pass GA4 validation, CSV files must not contain empty rows.
On the OWOX BI side:
- The maximum size for a BigQuery table, measured in total logical bytes, is 100 MB.
Getting started requirements
- You are using the Google Cloud Platform and have an active project. And you have a BigQuery Admin or BigQuery Data Owner role (how to set up GCP).
- You tag your traffic with utm_id (CampaignId). It's critical for matching your cost data with events in Google Analytics 4.
- You have prepared a BigQuery table with the required data format for GA4. Need help with this? Read Import non-Google costs into Google Analytics 4 solution.
Step-by-step setup
Step 1. On the Workspace page, click the ‘New’ button and select the 'Pipeline' option in a drop-down menu:
Step 2. As a source, select 'Google BigQuery':
Step 3. Choose pipeline 'Google BigQuery → Google Analytics 4 (via SFTP)':
And click the 'Create & Setup' button:
Step 4. The new pipeline has been successfully created in ‘Draft’ status. To start data import, you'll need to configure pipeline settings, including the source of your data, SFTP server configuration, and destination data format.
Step 5. Set up source data:
5.1. Click on the ‘Source data’ section.
5.2. In the opened dialog, click on the ‘Select a dataset’ section.
5.3. Then you will see another dialog, where you can choose one of the shared datasets in the list. If this list is empty, or you didn’t find the desired dataset, you can click the ‘Grant access…’ button and add a new dataset following the instructions.
5.4. Click the ‘Save’ button to close the second dialog.
5.5. Specify the BigQuery table, where you have prepared data for importing to Google Analytics 4.
5.6. Click the ‘Save’ button to apply your changes.
5.7. If all is done correctly, the dialog will close, and in the "Source data" section you will see a green marker with the name project.dataset.table.
Step 6. Configure SFTP-server in combination with creating a new Data source in Google Analytics 4.
6.1. Please, open two browser tabs: the first one with the OWOX BI pipeline page, and the second one with the GA4 Admin page.
6.2. In the OWOX BI, click on the ‘SFTP server…’ section.
6.3. In the opened 'Setting up Data source in Google Analytics' dialog, you will see three sections:
- Required steps in the Google Analytics 4 interface.
- SFTP server username, that you need to copy here and paste into GA4 Data source settings
- SFTP server URL
Proceed required steps to create a new Data source in Google Analytics 4.
Follow official Google Help instructions and specify the required settings for the new Data source:
- Name;
- Data type (for example, Cost data);
- SFTP upload option (with 'Username' and 'Server URL' fields);
- Schedule the daily import between 9 and 10 AM, following the completion of Pipelines and Transformation, as recommended based on the scheduling of data import in Pipelines.
Then, in the Google Analytics 4 interface, click the 'Next' button. And, in the OWOX BI interface, click the ‘Next’ button.
6.4. Next dialog in OWOX BI, ‘Reviewing CSV file data schema’ displays information that allows you to set up the ‘Mapping’ step in the GA4 interface. Here you will see the list of existing fields in your CSV file (based on the previously chosen BigQuery table). Map these fields with existing Analytics fields in the GA4 interface. And then, in the OWOX BI interface, click the ‘Next’ button.
Critical important: In GA4 'Campaign ID' field is used as a key to blending the data. So, we recommend duplicating the 'Campaign Name' value in utm_id tag, to get a Match rate of 100% in GA4. Read more
In the Google Analytics 4 interface, click the ‘Create & generate key’ button.
6.5. After mapping fields, in the Google Analytics 4 interface you will see the Public key.
Copy and paste it into the OWOX BI interface. And then click the ‘Save’ button to apply your changes and close the dialog.
You will see a green marker with the phrase ‘Public key is set’. You are all set now.
Step 7. In the OWOX BI interface, activate this pipeline by clicking on the ‘Activate’ button.
The pipeline will change status from ‘Draft’ to ‘Active’.
You are done. And now you can go to Google Analytics 4 and in the row for an existing data source, click the ‘Import now’ button.
It's not necessary, but it allows you to get reports in Google Analytics without waiting for auto data import.
In Google Analytics you will see Import History with Statuses and Match rate:
Note: Please note that the percentage value is only available for query-time widening data types only (Cost and Item data).
User and offline events data, after successful uploading, will have the next appearance:
How to open new reports in Google Analytics 4
After importing cost data you will be able to open a ‘Reports — Acquisition — Non-Google cost’ report.
You can also create a custom report in the Explore section and add campaign parameters as well as cost, impression, click, cost-per-click, and return on spend metrics that are not related to Google.
0 Comments