{"id":232,"date":"2016-10-02T15:18:16","date_gmt":"2016-10-02T11:18:16","guid":{"rendered":"https:\/\/optimizationup.com\/?p=232"},"modified":"2016-10-02T15:18:16","modified_gmt":"2016-10-02T11:18:16","slug":"import-cost-google-analytics-automatically","status":"publish","type":"post","link":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/","title":{"rendered":"Automatically Import Cost Data into Google Analytics"},"content":{"rendered":"

Google Analytics<\/a><\/span> allows importing\u00a0cost data for non-Google campaigns where you can monitor performance across all your channels in one place.\u00a0In this article, we will learn how to upload the cost data for your Facebook and Bing campaigns into\u00a0Google Analytics automatically<\/strong>\u00a0using Google apps script. Google apps script<\/strong>\u00a0is 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 \u2014 we give you a code editor right in your browser, and your scripts run on Google’s servers.<\/p>\n

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:-<\/p>\n

1- Create the Data Set through GA<\/h5>\n

Go to the Admin panel of the GA account that we’re trying to upload the data to. Under the “Property”<\/strong> column, we\u2019re going to select “Data Import”<\/strong>\u00a0and follow the steps below:-<\/p>\n

\"data-import-google-analytics\"<\/p>\n

Click “+New Data Set”<\/strong><\/p>\n

\"data-set-cost-import-ga\"<\/p>\n

Select “Cost Import”<\/strong> and\u00a0Select\u00a0one or more views to associate with this Data Set.<\/p>\n

\"import-cost-automatically-ga\"<\/p>\n

Define the Data Set\u00a0schema 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\u00a0need.<\/p>\n

\"cost-import-google-analytics-data-schema\"<\/p>\n

Finally, save and then click\u00a0“Get Custom Data Source ID (for API Users)<\/strong>“, copy the ID, will be using it later on.<\/p>\n

\"cost-import-google-analytics\"<\/p>\n

2- Format the data per Google Analytics requirements<\/h5>\n

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<\/a><\/span> in a Google spreadsheet, you need to reformat this data using\u00a0Google schema<\/strong>,\u00a0copy and replace the header title with the respective metric\/dimension below:-<\/p>\n

ga:date,ga:medium,ga:source,ga:adCost,ga:campaign<\/code><\/p>\n

To format the data extracted from Facebook and Bing, you can use the formula below:-<\/p>\n

=<\/span>if<\/span>(<\/span>Source!$A$2<\/span>=<\/span>\"\"<\/span>,<\/span>Source!G1<\/span>,<\/span>(<\/span>left<\/span>(<\/span>Source!$A$2<\/span>,<\/span>4<\/span>)<\/span>&<\/span>mid<\/span>(<\/span>Source!$A$2<\/span>,<\/span>6<\/span>,<\/span>2<\/span>)<\/span>&<\/span>right<\/span>(<\/span>Source!$A$2<\/span>,<\/span>2<\/span>)<\/span>)<\/span>)<\/span><\/code><\/p>\n

\"format-google-schema<\/h5>\n
<\/h5>\n
3- Connect Google app script to your spreadsheet.<\/h5>\n

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

\"google-spreadsheet-google-analytics-cost-import\"<\/p>\n

Now, the script editor is open:-<\/p>\n

\"google-app-scirp-ga-cost-upload\"<\/p>\n

Delete any code in the script editor and paste in the code below<\/p>\n

 function uploadData() {<\/span><\/code>\n var accountId = \"xxxxxxx<\/span>\";<\/span><\/code>\n var webPropertyId = \"UA-25xxxxx<\/span>\";<\/span><\/code>\n var customDataSourceId = \"CAuCSza1SXCifs79teXIEw<\/span>\";<\/span><\/code>\n var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();<\/span><\/code>\n var maxRows = ss.getLastRow();<\/span><\/code>\n var maxColumns = ss.getLastColumn();<\/span><\/code>\n var data = [];<\/span><\/code>\n for (var i = 1; i < maxRows;i++) {<\/span><\/code>\n data.push(ss.getRange([i], 1,1, maxColumns).getValues());<\/span><\/code>\n }<\/span><\/code>\n var newData = data.join(\"\\n\");<\/span><\/code>\n var blobData = Utilities.newBlob(newData, \"application\/octet-stream\", \"GA import data\");<\/span><\/code>\n try {<\/span><\/code>\n var upload = Analytics.Management.Uploads.uploadData(accountId, webPropertyId, customDataSourceId, blobData);<\/span><\/code>\n SpreadsheetApp.getUi().alert(\"Uploading: OK\");<\/span><\/code>\n }<\/span><\/code>\n catch(err) {<\/span><\/code>\n SpreadsheetApp.getUi().alert(\"Cannot upload: Failed\");<\/span><\/code>\n }<\/span><\/code>\n\n}<\/span><\/code>\n<\/pre>\n
Please note that you need to replace what’s in red with your relevant data, the account id, the property\u00a0id and finally the API id we generated in step 1 (above).Then,\u00a0click “resource” and choose “Advanced Google Services”:-<\/p>\n
\"import<\/p>\n
Turn on the Google Analytics<\/a><\/span> API<\/strong>:-<\/p>\n
\"google-analytics-api\"<\/p>\n
Finally, try to run your script:-<\/p>\n
\"scirpt-upload-cost-ga-automatically\"<\/p>\n
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:-<\/p>\n
\"google-analytics-api-script\"<\/p>\n
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\u00a0your 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 \u00a0your 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.<\/p>\n
\"google-app-scirpt-google-analytics-cost\"<\/p>\n
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<\/a><\/span>” section of “Acquisition”. Looking forward to your comments below.<\/p>\n","protected":false},"excerpt":{"rendered":"
Google Analytics allows importing\u00a0cost data for non-Google campaigns where you can monitor performance across all your channels in one place.\u00a0In this article, we will learn how to upload the cost data for your Facebook and Bing campaigns into\u00a0Google Analytics automatically\u00a0using Google apps script. Google apps script\u00a0is a scripting language based on JavaScript that lets you…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":252,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[41],"class_list":["post-232","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-measure","tag-analytics"],"yoast_head":"\nAutomatically Import Cost Data into Google Analytics | Optimization Up<\/title>\n<meta name=\"description\" content=\"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automatically Import Cost Data into Google Analytics | Optimization Up\" \/>\n<meta property=\"og:description\" content=\"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/\" \/>\n<meta property=\"og:site_name\" content=\"Optimization Up\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/A7med.3lii\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-02T11:18:16+00:00\" \/>\n<meta name=\"author\" content=\"Ahmed Ali\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@A7med_3ly\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ahmed Ali\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/\"},\"author\":{\"name\":\"Ahmed Ali\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/#\\\/schema\\\/person\\\/75b8aaa058bc4d0630cc7e138840f768\"},\"headline\":\"Automatically Import Cost Data into Google Analytics\",\"datePublished\":\"2016-10-02T11:18:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/\"},\"wordCount\":730,\"commentCount\":22,\"image\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#primaryimage\"},\"thumbnailUrl\":\"\",\"keywords\":[\"Analytics\"],\"articleSection\":[\"Measure\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/\",\"url\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/\",\"name\":\"Automatically Import Cost Data into Google Analytics | Optimization Up\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#primaryimage\"},\"thumbnailUrl\":\"\",\"datePublished\":\"2016-10-02T11:18:16+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/#\\\/schema\\\/person\\\/75b8aaa058bc4d0630cc7e138840f768\"},\"description\":\"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#primaryimage\",\"url\":\"\",\"contentUrl\":\"\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/import-cost-google-analytics-automatically\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/optimizationup.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automatically Import Cost Data into Google Analytics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/#website\",\"url\":\"https:\\\/\\\/optimizationup.com\\\/\",\"name\":\"Optimization Up\",\"description\":\"Advanced Optimization & Analytics Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/optimizationup.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/optimizationup.com\\\/#\\\/schema\\\/person\\\/75b8aaa058bc4d0630cc7e138840f768\",\"name\":\"Ahmed Ali\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g\",\"caption\":\"Ahmed Ali\"},\"description\":\"Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest products Markifact,\u00a0 Marketing Auditor & GA4 Auditor - discover all my products here.\",\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/A7med.3lii\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/a7med-ali\\\/\",\"https:\\\/\\\/x.com\\\/A7med_3ly\"],\"url\":\"https:\\\/\\\/optimizationup.com\\\/author\\\/a7med\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Automatically Import Cost Data into Google Analytics | Optimization Up","description":"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/","og_locale":"en_US","og_type":"article","og_title":"Automatically Import Cost Data into Google Analytics | Optimization Up","og_description":"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.","og_url":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/","og_site_name":"Optimization Up","article_author":"https:\/\/www.facebook.com\/A7med.3lii","article_published_time":"2016-10-02T11:18:16+00:00","author":"Ahmed Ali","twitter_card":"summary_large_image","twitter_creator":"@A7med_3ly","twitter_misc":{"Written by":"Ahmed Ali","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#article","isPartOf":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/"},"author":{"name":"Ahmed Ali","@id":"https:\/\/optimizationup.com\/#\/schema\/person\/75b8aaa058bc4d0630cc7e138840f768"},"headline":"Automatically Import Cost Data into Google Analytics","datePublished":"2016-10-02T11:18:16+00:00","mainEntityOfPage":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/"},"wordCount":730,"commentCount":22,"image":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#primaryimage"},"thumbnailUrl":"","keywords":["Analytics"],"articleSection":["Measure"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/","url":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/","name":"Automatically Import Cost Data into Google Analytics | Optimization Up","isPartOf":{"@id":"https:\/\/optimizationup.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#primaryimage"},"image":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#primaryimage"},"thumbnailUrl":"","datePublished":"2016-10-02T11:18:16+00:00","author":{"@id":"https:\/\/optimizationup.com\/#\/schema\/person\/75b8aaa058bc4d0630cc7e138840f768"},"description":"Learn how to automatically import cost data from Facebook, Bing and other sources into Google Analytics using Google apps script.","breadcrumb":{"@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#primaryimage","url":"","contentUrl":""},{"@type":"BreadcrumbList","@id":"https:\/\/optimizationup.com\/import-cost-google-analytics-automatically\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/optimizationup.com\/"},{"@type":"ListItem","position":2,"name":"Automatically Import Cost Data into Google Analytics"}]},{"@type":"WebSite","@id":"https:\/\/optimizationup.com\/#website","url":"https:\/\/optimizationup.com\/","name":"Optimization Up","description":"Advanced Optimization & Analytics Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/optimizationup.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/optimizationup.com\/#\/schema\/person\/75b8aaa058bc4d0630cc7e138840f768","name":"Ahmed Ali","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/68727300a58d1891a3dda2e17e66e7ebd42c80226d3006db77b4814bc14d4d43?s=96&d=mm&r=g","caption":"Ahmed Ali"},"description":"Entrepreneur focused on building MarTech products that bridge the gap between marketing and technology. Check out my latest products Markifact,\u00a0 Marketing Auditor & GA4 Auditor - discover all my products here.","sameAs":["https:\/\/www.facebook.com\/A7med.3lii","https:\/\/www.linkedin.com\/in\/a7med-ali\/","https:\/\/x.com\/A7med_3ly"],"url":"https:\/\/optimizationup.com\/author\/a7med\/"}]}},"_links":{"self":[{"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/posts\/232","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/comments?post=232"}],"version-history":[{"count":0,"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/posts\/232\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/optimizationup.com\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/media?parent=232"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/categories?post=232"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/optimizationup.com\/wp-json\/wp\/v2\/tags?post=232"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}