Automatically Import Cost Data into Google Analytics

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:-

data-import-google-analytics

Click “+New Data Set”

data-set-cost-import-ga

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

import-cost-automatically-ga

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.

cost-import-google-analytics-data-schema

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

cost-import-google-analytics

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)))

format-google-schema cost data
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”:-

google-spreadsheet-google-analytics-cost-import

Now, the script editor is open:-

google-app-scirp-ga-cost-upload

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”:-

import cost data

Turn on the Google Analytics API:-

google-analytics-api

Finally, try to run your script:-

scirpt-upload-cost-ga-automatically

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:-

google-analytics-api-script

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.

google-app-scirpt-google-analytics-cost

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.

mm

Ahmed launched his first digital campaign in 2013 through participating in Google Online Marketing Challenge. He competed with over 12,000 students from 80 countries to win the first place in Africa & MENA region and one of the best five campaigns in the world. Today, he works as an Associate Digital Director at Keyade Middle East.

11 Comments

  1. Paul

    The Article is cool. I also have bookmarked it.
    However can explain more about this statement?
    you need also a connector or a spreadsheet add-on to pull the data from Facebook or Bing automatically.

    How can we do that?

  2. Tony

    Great stuff. Thank you!

    Two notes:

    I got an error that there was an extra closing } in the code. Works fine with the extra bracket deleted.

    This may be helpful to others. Setting the date for data importing is tricky. This is the method I use in google sheets to populate the date of yesterday: =TEXT(TODAY()-1,”yyyymmdd”)

    Simply setting it as =TODAY()-1 won’t import.

  3. Hi Ahmed,

    thanks a lot for your script, but i’ve a problem with it: doesn’t work at the moment of upload data.
    I’ve follow all the steps, i’ve create the sheet data with supermetrics, the data set in GA and the Google Script that i’ve configured with my data.
    Can you help me? i can send the screen about the debug or give you a access.

    Thanks and bye.

Leave a Reply

Your email address will not be published. Required fields are marked *