This tutorial shows how to import Google Analytics cost data automatically from Google Sheets with the click of a button. Here is the sample sheet. Go to the File – Make a Copy to edit the sheet in your Google Drive.
Google Analytics Data Import File Configuration
In your Google Analytics Account click on the top navigation Admin then under the Property you’d like to import the data for, then click on the Data Import link. This will open another pane then click the button + New Data Set.
Choose Data Set Type to Import
In this example I’ll import non-Google traffic source cost data. Under Summary Data Import click on the radio button next to Cost Data. Then click on the Next step button on the bottom of the screen. Other types of data sets that you can import to Google Analytics include: refund data, user data, campaign data, geography data, content data, product data, and custom data.
Data set details
Give your data set a name. In the example I kept it simple and named my data set Cost Data Import. Then choose the Google Analytics Views within the property that you’d like to import the cost data.
Next setup the Data set schema with the dimensions and metrics that will be uploaded. The traffic source and medium are required keys to join the behavior and conversion data in Google Analytics to the cost data from your external source. Then choose the metrics included in the cost data set. In this example these metrics include: clicks, cost, and impressions. Then click Save.
For simplicity this example cost data import gives you an aggregate look at the cost for your marketing channels. So if you run Bing PPC, Facebook Ads, or Affiliate marketing you could see the aggregate cost. But beyond just an aggregate channel performance you can add cost data for each campaign name, keyword and ad content dimension. This assumes you have detailed cost data for each campaign and assumes you’ve tagged all your final destination URLs for your channels with Google Analytics utm parameters. To add more dimensions to the cost data import click the Select one drop down for each dimension under the Any number of these columns may be provided heading.
Get Google Analytics Cost Data Import Schema – Column Headers
Now that our cost data set is setup in Google Analytics we need the data schema. These are the column headers for our cost data file that will be imported to Google Analytics. You can either take the CSV header dimensions and metrics listed in the blue box or click the button to Download schema template to Excel and copy the column headers then paste them into the Google Sheet. We are going to use a Google Sheet to classify and upload the cost data directly to Google Analytics. If you were going to import the cost data manually you would use Excel to add cost data dimensions and metrics to the schema template then upload it as a CSV file directly in Google Analytics.
Use Google Sheet to Automatically Import Cost Data to Google Analytics
We are going to use the Google Sheet to automatically import cost data to Google Analytics directly from the Google Sheet. Make sure you are logged into the Google Account that you’ll be uploading your Google Analytics cost data to. Below is a screen shot of the Google Sheet that we’ll use in this tutorial. To save a copy of this Google Sheet to your Google Drive click on File > Make a Copy in the top navigation. You can click the link to the Sheet and paste in your cost data starting on Row 2.
Enable the Google Analytics API for the Google App Script
Once you have all your cost data included in the Google Sheet go to the top navigation and click on Tools > Script Editor… This will launch the Google Sheets script editor with a script called Google Analytics Cost Data Upload Script. Within the script editor in the top navigation click on Resources > Advanced Google Services… This will launch an overlay like the screen shot below. Make sure the Google Analytics API v3 is switched to on.
Then click on the Google Developer Console link at the bottom of the overlay shown above. This will launch the Google API Manager. Click on the Library link then in the search box under the Google APIs type in Google Analytics. Then click on Google Analytics API in the search results as shown in the screen shot below.
Next click the Enable link near the top of the screen in blue to enable the Google Analytics API for your Google Account.
Add Your accoundID webPropertyId and customDataSourceId to the Script
Back in the Script Editor you will need to replace the accountID, webPropertyId, and customDataSourceId on lines 2,3, and 4 with your Id’s. Below are screen shots and instructions on where to find all 3 of these ID’s in your Google Analytics account.
Account ID is found in the Google Analytics web interface by clicking on Admin then clicking Account Settings under the Account column.
Web Property ID is found in the Google Analytics web interface by clicking on Admin then clicking on Property Settings under the Property column.
Custom Data Source ID is found in the Google Analytics web interface by clicking on Admin then clicking on Data Import under the Property column. Copy the values under the Data Set ID column.
Make sure to add your 3 ID’s to your version of the App Script.
Run the Script from the Google Sheet to Automatically Upload Cost Data to Google Analytics
In the top navigation of the script editor click on Run > uploadData. Upload data is the name of the function in the App Script. You should see a popup saving Authorization required. Click on Review Permissions and another window outlining the permissions will popup as shown below. Click on the blue Allow button to give the script permission to view and manage your spreadsheets in Google drive and view and manage your Google Analytics data.
Finally your script will Run!
If the script has run properly and the cost data from the Google Sheet has been successfully uploaded to Google Analytics you’ll see an alert box in the Google Spreadsheet that says Uploading: OK. If there are any errors this alert box will show you the descriptive error message that should help you troubleshoot.
Report on Cost Data in Google Analytics
Now that you’ve uploaded your cost data you want to see this marketing communication cost data in Google Analytics. Be patient. It can take up to 24 hours before data becomes available in reports after you upload your cost data. Here is a link to the Google Analytics Custom Report shown below that you can save in your Google Analytics account as a simple report on the cost data you just imported.
Below is the Google App Script code used to automatically import the cost data from Google Sheets to Google Analytics.
If you have any questions about getting the script setup or have ideas about extending or improving the script please let me know in the comments below or on Twitter @ryanpraski. One possible direction to automate this process further is to setup a script to automatically pull the cost data from your marketing communication channels directly. Say you have cost data from Bing PPC, Facebook ads or other sources. You can setup a script that pulls this cost into the Google Sheet daily from the source, then you can update the existing script to automatically upload this cost data daily.