Google Analytics Cost Data Import from Google Sheets – Automated

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.

cost_data_import_admin_google_analytics

 

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.

cost_data_import_admin_google_analytics_2

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.

cost_data_import_admin_google_analytics_3

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.

cost_data_import_admin_google_analytics_get_schema_4

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.

cost_data_import_google_analytics_google_sheet_6

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.

cost_data_script_google_analytics_advanced_services_8

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.

cost_data_script_google_analytics_api_manager_console_9

Next click the Enable link near the top of the screen in blue to enable the Google Analytics API for your Google Account.

cost_data_script_google_analytics_api_manager_console_enable_10

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.

google_analytics_account_id

Web Property ID is found in the Google Analytics web interface by clicking on Admin then clicking on Property Settings under the Property column.

google_analytics_property_id

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.

google_analytics_cost_data_import_data_set_id_api

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.

google_api_authorize_sheets_app_script

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.

cost_data_report_google_analytics_7

Below is the Google App Script code used to automatically import the cost data from Google Sheets to Google Analytics.

Thanks to Chip Oglesby for creating this script. Be sure to check out more of his Google Apps Scripts on his website.

Next Steps

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.