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.

22 Comments

  1. This blog is really cool. I have bookmarked it.
    Do you allow guest posting on your site ? I can provide high quality posts for you.
    Let me know.

  2. 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?

  3. 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.

  4. 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.

  5. I’m getting the following error after running the script:

    Cannot call SpreadsheetApp.getUi() from this context. (line 19, file “Code”)

  6. Hello Ahmed,

    This is one of the most helpful blogs i’ve read on this subject! Thanks a lot.

    I’m saying this and i haven’t even got it working yet.! 🙂

    I am receiving Timed out waiting for user response (line 19, file “Code”) when i go to upload. Do you have any advise?

  7. Hi Ahmad,

    I want to automate cost data import from Linkedin to Google Analytics, Do you have any comments on that.

  8. Hi,

    I have a error message on the google sheet “Cannot upload: Failed”

    Can you help me to fix this issue ?

    Thanks

  9. This post has been really helpful. Thanks.
    I’m missing one piece. If I have multiple data sources (i.e. Bing, Facebook, Yahoo) all in one Google Sheet (different tabs), how do I differentiate the data sources? In another words, how is the script tied to the Google Sheet? I see the var ss is set to SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); but I’m not sure what that means. Any clarification would be helpful.

    Thanks,
    Rich

  10. Hi Ahmed,

    A great blog I must say.

    This seems like a great method, I haven’t tried it yet but I do have one theoretical question.

    If you upload a new months data replacing the previous months data and upload to GA will this overwrite old months data in GA reports i.e. You will now only see the new months data in GA reports as this is the only data in the spreadsheet you uploaded.

    If this is the cases, is the only way around this to append the new months data to the last entry of the old months data in the spreadsheet and upload all this data together?

    Thanks,
    Jamie