This is a tutorial on how to manually import Google Analytics data into Salesforce. It is designed for customers of the CloudAmp Analytics Dashboards, who might want to import historical data beyond the last 30 days that is automatically imported when you first install the application (soon to be customizable for different time periods), but it will also work for any Salesforce setup.
If you are not a CloudAmp customer, keep in mind that you would need to create a custom object and fields to hold your Google Analytics data inside of Salesforce. You could always try CloudAmp Analytics Dashboards free for 7 days, since it takes just a few minutes to set up. It might be just the thing you are looking for, since it gives you all of the data and dashboards preconfigured, not to mention automatically loading your Google Analytics data nightly into Salesforce. But obviously I am biased here. 😉
Anyway, with that plug out of the way, how can you import Google Analytics data into Salesforce, either as a permanently manual process or to supplement data from an existing integration?
Here is a step by step tutorial. Please note that steps 1-3 are optional. If you like to live on the edge, you can simply export data from Google Analytics and then match up the columns when importing into Salesforce, rather than create an import template first.
1. Find the prebuilt report called “CloudAmpGA Metrics Export Report” (or create your own).
2. Export to CSV
3. Prepare the CSV file to become an import template. The main reason we exported was to get the appropriately named header row, and some of the profile data.
1. Delete all rows of data except for the header and first row
2. Delete these columns, since they will be auto-generated by Salesforce when new data is imported
CloudAmpGA Metrics: Metrics Number
CloudAmpGA Metrics: ID
3. Delete all other data from first sample row, except for
(you want to save these values to match with the Google Analytics profile(s) that you will be importing).
Log into Google Analytics, select the profile you want, and set the date range to the periods of data that you wish to import into Salesforce. Export the relevant data from Google Analytics for the metrics and date ranges you want into a CSV file.
This step can be the most difficult, as different metrics are stored differently of Google Analytics. Google Analytics limits which dimensions can be related to which metrics, so creating exportable reports that will be formatted in a useable way can be a challenge for some of the data. For the CloudAmpGA Metrics custom object in Salesforce, for example, where daily records are stored for the CloudAmp Analytics Dashboards app, not all of the metrics are cleanly exportable as columns in a CSV file.
1. Go to the “Customize” button and create a Custom Report.
2. Select “Date” for the dimension
3. Select the metrics that you wish to export.
4. Save the report and export to CSV
Only 500 rows of Analytics data can be exported at a time into CSV format. Google has some tips for exporting larger data sets in the Google Analytics help documentation.
Note that not all metrics are available in custom reports, or at least not in an easy-to-export way. You many need to create additional reports and manually move data around, and/or use some formulas in certain columns of your spreadsheet to calculate some metrics. In the Custom Report example below, for example, adding “Traffic Type” as a second dimension produces some of the data we want (Organic Visits, Referral Visits, Direct Visits) but as multiple rows per date instead of a column.
1. To prepare the CSV file, make sure column headings are as close to Salesforce field names as possible. Either use the CSV template created in the first part of this tutorial, or manually update the column headings to match the Salesforce field names.
2. (If importing into the CloudAmpGA Metrics object): Ensure that the Profile Number is populated, and the Metrics Date column is populated by the dates from Google Analytics, so the new records will match up with existing data in Salesforce.
3. You may need to change the dates into a valid date format for Salesforce:: MM/DD/YYYY since Google sometimes formats the dates as YYYYMMDD, and this can cause an import error.
4. Double check the rest of the file for accuracy and any remaining cleanup issues.
1. Open a data loading app. I personally like MuleSoft’s Dataloader.io, though there are others as well. API access to Salesforce is required for all of these tools, but if you are using CloudAmp Analytics Dashboards you need to have Enterprise Edition of Salesforce, so that includes API access.
2. Log into Dataloader.io using your Salesforce credentials
3. Click on “New Task” on the top left
4. Choose the type of job (most likely “insert” for creating new records) and select the object in Salesforce where your Google Analytics data will be store (“CloudAmpGA Metrics” for CloudAmp customers)
5. Select the CSV file you wish to import.
6. Check to see that the column headings in your file were properly matched to the Salesforce fields. Unmapped fields will be indicated and give you a chance to select a mapping, or ignore and they will not be imported.
7. Proceed to run the import. We recommend importing a small test file of 5-10 records initially, in case there are any issues.
8. When the import has finished, Dataloader will update you as to the number of successes. If there are any errors, click to see what the issue was. You can always update the CSV and run the job again if records did not import due to a formatting issue or other problem.
9. Spot check some of the imported records for accuracy and completeness. Refresh the appropriate dashboard(s) to see the changes from the imported data
So that is a lot of steps, but once you import data this way into Salesforce a few times, it will become an easier process. Combine that with a tool like CloudAmp Analytics Dashboards which automatically imports the previous day’s Google Analytics data into Salesforce every night, and you will have the best of both worlds.
Questions, or problems not addressed in the tutorial? Let me know in the comments below and I’ll do my best to address them.