In order to import your tracking data from Google Analytics 4, it must first be transferred to a Google Spreadsheet. You can then link this to Channel Pilot Pro.
Activate Google Spreadsheet extension
There is an extension for Google Spreadsheets to import data from Google Analytics 4.
Log in to your Google account where you use Google Analytics 4.
Open an empty Google Spreadsheet.
Now call up the Google Analytics extension via "Extensions -> Add-ons -> Call up add-ons".
Search there for the extension “GA4 Magic Reports” and install it.
Configure Google Analytics 4 report
Now a report must be configured in Google Spreadsheets using the installed extension.
Click on "Extensions -> GA4 Magic Reports -> Create new report"
The following settings must be made in the “Create new report” window:
Name: Enter the name for your report
Select a view: Select your Google Account and your property for which you want to create the report.
Date Range:
Start Date: select today's date
End Date: select today's date
Metrics: Select the following:
Sessions
Items purchased
Item revenue
Dimensions: Select the following:
Session campaign
Item ID
Click on Create Report.
Enter “3daysAgo” as the start and end date in the report configuration.
Your configuration should now look like this:
Create the report by going to "Extensions -> GA4 Magic Reports -> Run reports".
To create the daily report, you can set a regular update under "Extensions -> GA4 Magic Reports -> Schedule reports".
Editing the report in the Google Spreadsheet
Your report should look like this:
The created report still needs to be edited so that it can be imported into Channel Pilot Pro. The following changes must be made:
First, the header rows of the report, up to the header columns of the latest report, must be removed.
In addition, a column with a date is required. You can add this at the beginning or at the end, for example.
This should contain the following formula so that it contains yesterday's date.
=IF(B2="";"";TO:DATE(TODAY()-1))
Finally, the format of the date column must be changed to the following format:
yyyy-mm-dd
You can also use the following macro. This contains the changes listed above:
/** @OnlyCurrentDoc */
function bearbeiten() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('1:14').activate();
spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
spreadsheet.getRange('A:A').activate();
spreadsheet.getActiveSheet().insertColumsBefore(ActiveRange().getColumn(), 1);
spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().setValue('Date');
spreadsheet.getRange('A2').activate();
spreadsheet.getCurrentCell().setValue('=IF(B2="";"";TO_DATE(TODAY()-1))');
spreadsheet.getRange('A3:A3000').activate();
spreadsheet.getRange('A2').copyTo(spreadsheet.getActieRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
spreadsheet.getRange('A:A').activate();
spreadsheet.getActiveRangeList().setNumberFormat('yyyy"-"mm"-"dd');
};
The final report should look like this:
Macro to automate the import
A macro for customizing the report helps you to automate the complete tracking data.
You can then run this macro automatically after your report has been updated. To do this, carry out these steps in the Google Spreadsheet with the report:
Go to Extensions -> Macros -> Manage Macros
Click on "Edit script" to the right of your macro.
Select Trigger (clock symbol) in the left menu and then "Add trigger".
“Time-controlled” should be selected for the date source and the execution should be started after the report has been created.
The report is now ready and can be imported.
A regular import can now also be created in Channel Pilot Pro, which is always started after the report has been generated, and the macro has been executed.