The instruction covers all steps to import non-Google costs to Google Analytics 4 for Free plan users. 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.
Preparing data to import
Step 1. First of all, you have to prepare your data to import. Please, use Google Sheets to create the table with data. We use cost data as an example in this article. If you want to import other data types, you have to change the table's schema.
Please, copy the Google Sheet template by this link. It contains columns with names according to the GA4 cost data schema.
Fill the cells with data following the rules below.
Date format: YYYY-MM-DD or MMM DD, YYYY
Example: 2023-05-01 or May 01, 2023
Daily cost format:
- set up the cell format as Plain text (Format > Number > Plain text);
- use “dot” as a decimal mark;
- do not use a thousand separator;
- do not use spaces in numbers.
Example: 422.01
Note:campaign_id must be equal to utm_id of traffic data. Please, check the blog article to know more.
Step 2. To proceed, kindly follow the instructions in the provided link to set up the free Google Sheets↔BigQuery Reports (Google Sheets Extension).
Step 3. In this part, you import data in the Google BigQuery project. Navigate to Extensions > OWOX BI BigQuery Reports and choose the Upload data to BigQuery option. Then, proceed to Steps 4-5 of the detailed instruction.
Step 4. Go to your Google BigQuery project to ensure the table is successfully uploaded.
Setting up the Google BigQuery → Google Analytics 4 (via SFTP) pipeline
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 a 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:
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