Optimization Up

Google Analytics allows importing cost data for non-Google campaigns where you can monitor performance across all your channels in one place. In this article, we will learn how to upload the cost data for your Facebook and Bing campaigns into Google Analytics automatically using Google apps script. Google apps script is a scripting language based on JavaScript that lets you do new and cool things with Google Apps like Docs, Sheets, and Forms. There’s nothing to install — we give you a code editor right in your browser, and your scripts run on Google’s servers.

Good to mention before we start that we’ll review only the push part automation to Google Analytics API, this means that you need also to automate pulling the data from whatever data source (Facebook, Bing) you are going to submit to GA. There are many spreadsheet add-ons that allow you to get your metrics from various sources like Facebook and Bing into one spreadsheet, moreover, you can schedule automatic daily refresh for your data and this is what you actually need to apply this method. So, let’s get down to business:-

1- Create the Data Set through GA

Go to the Admin panel of the GA account that we’re trying to upload the data to. Under the “Property” column, we’re going to select “Data Import” and follow the steps below:-

Click “+New Data Set”

Select “Cost Import” and Select one or more views to associate with this Data Set.

Define the Data Set schema to include dimensions and metrics, in this example, we only picked cost as a metric and source/medium & campaign as a dimension. You can add clicks and impressions if you need.

Finally, save and then click “Get Custom Data Source ID (for API Users)“, copy the ID, will be using it later on.

2- Format the data per Google Analytics requirements

As mention before, in this article we only cover how to push the cost data automatically to GA, you need also a connector or a spreadsheet add-on to pull the data from Facebook or Bing automatically. After you have the data you want to import to GA in a Google spreadsheet, you need to reformat this data using Google schema, copy and replace the header title with the respective metric/dimension below:-

ga:date,ga:medium,ga:source,ga:adCost,ga:campaign

To format the data extracted from Facebook and Bing, you can use the formula below:-

=if(Source!$A$2="",Source!G1,(left(Source!$A$2,4)&mid(Source!$A$2,6,2)&right(Source!$A$2,2)))

3- Connect Google app script to your spreadsheet.

Once you have the data correctly formatted, it’s time to connect Google app script to your spreadsheet. Click tools then “Script Editor”:-

Now, the script editor is open:-

Delete any code in the script editor and paste in the code below

 function uploadData() {
 var accountId = "xxxxxxx";
 var webPropertyId = "UA-25xxxxx";
 var customDataSourceId = "CAuCSza1SXCifs79teXIEw";
 var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var maxRows = ss.getLastRow();
 var maxColumns = ss.getLastColumn();
 var data = [];
 for (var i = 1; i < maxRows;i++) {
 data.push(ss.getRange([i], 1,1, maxColumns).getValues());
 }
 var newData = data.join("\n");
 var blobData = Utilities.newBlob(newData, "application/octet-stream", "GA import data");
 try {
 var upload = Analytics.Management.Uploads.uploadData(accountId, webPropertyId, customDataSourceId, blobData);
 SpreadsheetApp.getUi().alert("Uploading: OK");
 }
 catch(err) {
 SpreadsheetApp.getUi().alert("Cannot upload: Failed");
 }

}

Please note that you need to replace what’s in red with your relevant data, the account id, the property id and finally the API id we generated in step 1 (above).Then, click “resource” and choose “Advanced Google Services”:-

Turn on the Google Analytics API:-

Finally, try to run your script:-

A small window will pop up asking you to authorize permission to access your Google drive and Google Analytics. Once you do so, the data will get uploaded successfully to Google analytics as long as it’s in the correct format:-

Now, you can schedule an upload to happen on an hourly, daily or weekly basis, Google will run the script and import the data exists your spreadsheet through their management API. On the other side, you should also have a pull schedule to get your data from Facebook and Bing. Let’s say, you will automate a trigger through  your spreadsheet add-on to refresh your data at midnight, in this case, it’s recommended to schedule Google apps script to run at least one hour after to push the data to GA.

Once the cost, clicks, and impressions data have been imported and have finished syncing with the existing traffic data at the source/medium, campaign, and keyword levels. You have the ability to see this data in two important places within GA. Firstly, the “Cost Analysis” section of “Acquisition” under the “Campaigns” parent tab displays campaign and e-commerce information similar to what you’d see under the “AdWords” section of “Acquisition”. Looking forward to your comments below.

Ahmed Ali

Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest products Marketing Auditor & GA4 Auditor – discover all my products here.