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:-
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.
Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest product GA4 Auditor and discover all my products here.
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.
Hello,
Thanks for your comment. Guest posting is not available at the moment, but it will be soon. Will let you know 🙂
Hi,
I got the one error. can you please check it for this script. http://prntscr.com/mogrgi
check this Error > http://prntscr.com/mogrnp
please replay as soon as possible.
it’s urgent….
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?
Hi Paul,
There are many Google sheets add-ons that can allow you to schedule a cost import from most of the ads platforms. At the same time, you schedule your script to push the imported cost to GA.
The most common used add-on is “Supermetrics”, please find the link below and let me know if you need any help.
https://chrome.google.com/webstore/detail/supermetrics/bnkdidgbiidpnohlnhmkehlimlnfhgce?hl=en
Dear Ali,
thank you for your sharing 😀
have a nice day~
…but supermetrics only allows you to import from Google Analytics on the free version.
Yes, you need the paid version.
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.
Thanks Tony for your inputs. The extra brace has been removed.
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.
Hi Mariano,
Can you please share with me the sheet URL through the “Contact Me” page?
Thanks,
Ahmed
I’m getting the following error after running the script:
Cannot call SpreadsheetApp.getUi() from this context. (line 19, file “Code”)
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?
Hi Ahmad,
I want to automate cost data import from Linkedin to Google Analytics, Do you have any comments on that.
Hi,
I have a error message on the google sheet “Cannot upload: Failed”
Can you help me to fix this issue ?
Thanks
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
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
I got the one error. can you please check it for this script. http://prntscr.com/mogrgi
check this Error > http://prntscr.com/mogrnp
please replay as soon as possible.
it’s urgent….
Hi,
I got the one error. can you please check it for this script. http://prntscr.com/mogrgi
check this Error > http://prntscr.com/mogrnp
please replay as soon as possible.
it’s urgent….
Hi! I think that if you have a small number of advertising systems, it is better to use tools for automatic cost data import. For example this one https://www.owox.com/products/bi/pipeline/google-analytics-cost-data-import/