If you’ve ever found yourself deep within Google Analytics data wishing you had additional data from other sources, you’re not alone! No matter how powerful the data in Google Analytics may be, widening the data set will increase your understanding of the data, enhance your ability to analyze, and enable you to make better business decisions.
Perhaps you want to import cost data for a campaign run in Facebook or Bing. Or maybe your organization wishes to further analyze a campaign with additional data.
Whatever the need, it can be tedious to manually update data imports on a regular basis. One of the ways to ease this process is by automating it, using a tool like Analytics Canvas. This powerful ETL tool allows you to connect to various data sources, manipulate the data, and output it to dashboards or databases, all without needing to write any code.
By using a combination of Google Sheets and Analytics Canvas, learn how to easily automate your Data Import uploads to Google Analytics:
1. Google Analytics Configuration
In Google Analytics, you will first need to define the Data Set that you wish to import against. Details on how to do so are provided in the Google Help Centre.
Depending on how you wish to expand your Google Analytics data, you may choose to upload additional campaign, geographical, or cost data. If you are an Analytics 360 customer, you will also have the option for Query Time Import, which allows you to retroactively expand upon existing data.
Once you have configured the Data Set, you will be able to download the schema for the data set as a .csv file. We will require this file for the next step.
2. Organizing Your Data in Google Sheets
With the data set configured in Google Analytics, import the .csv file from step 1 into Google Sheets. The downloaded template from step 1 will provide the column headers for your data. If you wish to add a more user-friendly title for the columns, you may enter an additional row above the schema, as we have done below:
Once set up, enter the data in the rows below that you wish to import to Google Analytics.
3. Analytics Canvas Configuration
With your data in Sheets, you can now set up your Canvas:
i. Add your data
You will first need to add the data from Google Sheets to your canvas. In the toolbar, select Add Data > Google Spreadsheet:
If you have not done so before, you will be prompted to Authorise the Google Sheets API, selecting the Google Account you wish to connect to:
Once completed, select the file you wish to connect to in your Google Drive. You will be prompted to select the specific cells within the Google Sheet to import. Ensure to select additional blank rows below where the data currently ends, as you will add additional data to the sheet over time.
If you have added more user-friendly titles as column headers in your sheet, do not select these cells. The first row of your imported data will be the schema titles defined from Google Analytics.
In the example from above, we will select cells A2 through E50, ensuring we have plenty of empty cells below the current data for future use:
ii. Filter your data
As you have selected blank rows below your data, you will need to add a filter to exclude the blank rows when the data gets uploaded. This ensures that there are no issues with the data import process. Right click on your canvas, Add Data Block > Filter:
You can now connect your input data to the filter block by clicking and dragging to connect the two dots:
Next, click the filter block to add a filter that removes the empty rows. In the input box, enter the following expression, ensuring you replace the dimension in red with Analytics Canvas’ column A header for your data set:
In our example, we will replace gadimension with gadate, the column A name as seen in the data:
iii. Add an Export Block
Next, we will want to load our data to Google Analytics by adding an Export Block—Analytics Canvas allows you to add an Export Block directly to Google Analytics Data Upload. Simply right click on the canvas, Add Export Block > Google Analytics Data Upload:
Once you have added this block, connect it to your Pass data (i.e. the data that meets the filter conditions) from the filter block.
If you have not already done so, you will need to connect Analytics Canvas to Google Analytics. Note that you will need to grant Read/Write access for the Data Import.
Next, open the Data Upload block by clicking on it. You will be prompted to Select custom data source. Select the appropriate account, property, and associated GA Data Import set previously created in Google Analytics.
You may also need to update the schema mapping if it has not automatically been detected.
iv. Automate your Canvas
Once your Canvas has been configured, you can now automate the process by uploading it to the cloud. If you do not already have one, you will need to create a Cloud User account.
In the toolbar, select Cloud Automation and log in to your account. Select +New Package to create a new automation sequence.
You will now be guided through the process of adding your canvas from your computer, providing the needed credentials to Google Sheets, and selecting when you want the canvas to automatically run. At this point, we recommend setting up an email notification to send if there are any failures when this process runs. Simply enter your email address, and toggle to the Error Handling and Logging tab to change the settings to ensure any issues are handled appropriately:
Now all that’s left to do is select Update to run your automation. You can now navigate back to Google Analytics and see that your data has been automatically imported.
Let us know in the comments below what you are automating using Data Import and Analytics Canvas!