How to Get All Your Ad Data into Google Analytics
In the past, marketers and web analysts had to maintain Excel spreadsheets where they manually collected and joined all ad spend data with website conversion data. It was necessary to calculate ad performance metrics such as ROAS or Cost of Sale.
To do this, they had to go to each advertising account and export statistics on advertising campaigns such as ad impressions, clicks and costs, then export data from the web analytics system, and then manually combine all the data.
As it turned out, many still do not know that the functionality of Google Analytics allows you to analyze the performance not only of Google Ads but also other advertising sources such as Facebook, Bing, Twitter, Pinterest, LinkedIn, and many others.
In this article, I will explain in detail how to import costs from all advertising platforms into Google Analytics and how to set up all the necessary advertising performance metrics, such as ROAS, Cost of Sale, and others.
All ad costs and ad performance metrics in your Google Analytics
Step by step guide:
1. Create Data Set in Google Analytics
1.1. First, you need to create a dataset, which will then be loaded with advertising cost data. To do this, go to Google Analytics “Admin” section, select your account and the choose the “Property” into which you want to import data, and select “Data Import” in it:
1.2. Click “Create” to create a new Data Set:
1.3. When choosing “Data Set type” select “Cost Data” and click on the “Continue” button:
1.4. Enter a name for your Data Set and select the required “Enabled Views”. Ad cost data will only be available for selected “Views”.
1.5. Next, in the settings, select the dimensions and metrics as shown in the screenshot. Set “Import Behavior” as “Overwrite” and click on the “Save” button.
Done! You have created a Data Set, which will then be loaded with ad cost data from any advertising platform. What’s next?
2. Prepare a file with advertising costs
To upload ad cost data, you first need to prepare a CSV file with a specific structure. To do this, create a new Google Sheets or Excel file with the following columns:
Detailed column description:
Now you can add data from all your advertising platforms to this file, such as Facebook, Bing, LinkedIn, Twitter Ads, and others. In the same way, you can add the costs of email marketing, SEO, and other marketing activities.
After the file is prepared, it needs to be exported in CSV format.
3. Import the file into the created Data Set
The prepared CSV file now needs to be imported into Google Analytics. To do this, go to the Admin → Property → Data Import section again, and click on “Manage Uploads” in the row with the created data set.
Click on the “Upload file” button and select the CSV file you have prepared for import.
Done, your ad cost data has loaded. Now you can start creating reports and analyzing ad performance across all marketing channels.
Is it possible to automate the process of importing ad costs into Google Analytics?
Yes, it is possible. There are several popular tools on the market that can automate the process of importing ad cost data on a daily basis.
Thanks to such tools, you do not need to go to each advertising account and manually export ad cost data for the selected period, in order to then prepare a CSV file in the format required for import into Google Analytics.
All you have to do is connect your Google Analytics account, select the ad platforms you use, and the system will automatically import your ad cost data on a daily basis.
An example of such tool is SegmentStream Cost Data Import. With SegmentStream you can automatically import all your advertising costs into Google Analytics without any hassle. Sign up for a trial and use the platform 14 days completely for free.
What should be considered when choosing such a tool?
- To what level of detail is ad cost analysis possible? Some tools allow you to import costs only up to the Source / Medium and Campaign level, without the ability to analyze at the Content and Term level. Make sure that the tool you want to choose supports the import of ad costs for all 5 UTM tags.
- Is the current UTM markup supported? Some tools support the ability to import cost data only if ad campaigns have been tagged in a special way. Make sure the tool you choose can use your current UTM markup so that you don’t have to re-configure the UTM tags for your current campaigns.
- Is parsing of dynamic URL parameters supported? Many advertising platforms allow you to pass dynamic parameters on click in the URL that the user follows to the site. For example, the location of the banner on the page, the region, the internal identifier of the advertising campaign, and other parameters. If you want to analyze the effectiveness of advertising campaigns down to the dynamic parameters transmitted by the advertising platform on click, make sure that the tool you choose supports the functionality of automatic parsing of dynamic URL substitutions.
- Is there an automatic currency conversion? If you buy ads in one currency (for example, dollars), and your view in Google Analytics is configured in a different currency (for example, euro), then the data in the reports may be incorrect. In this case, 100 U.S. dollars will be reported as 100 euros, which is incorrect. In order for the data in the reports to be correct, all ad costs must be converted to the currency in which you have configured a view in Google Analytics. Some tools have built-in functionality for automatic currency conversion on a daily basis.
- Is there a Google Sheets integration? As stated above, you can use Google Sheets to import cost data from the sources that do not have an API. Some tools have turn-key integration with Google Sheets, so you don’t have to export and import CSV-file manually. You just need to update the data in the spreadsheet and the tool will automatically upload it to Google Analytics.
- Is retrospective data upload supported? Many ad platforms often recalculate ad costs post-factum. Because of this, ad cost data for the same period may change. To accommodate these changes, some tools automatically import ad cost data for the entire previous week or month from today’s date. This ensures that the data in Google Analytics matches the cost data in the ad platform.
- Is it possible to import historical data? If you haven’t imported your ad cost data into Google Analytics before, you probably want to import this data from the past as well. Some tools allow you to import data for the previous 3-6 months at a time, which will save you a lot of time for manually importing data from each ad platform separately.
4. Check uploaded ad costs in Google Analytics report
Go to the Acquisition → Campaign → Cost Analysis report, where information on the cost of advertising campaigns will be available.
In this report, you can now see the costs not only for Google Ads, but also for your other ad platforms.
5. Add ad performance metrics to the report
To make the report more convenient to use, you can add additional columns with the advertising performance metrics. To do this, click on “Edit” in the report and select the required metrics on the page that opens.
You can choose standard Google Analytics metrics as performance metrics, for example:
- Transactions — the number of orders placed on the site;
- Revenue — revenue from placer orders on the site;
- ROAS — (revenue / cost data) * 100%;
- RPC — average revenue per click;
- and others.
Also, you can add to the reports your own metrics using the Calculated Metrics functionality, which allows you to add new metrics based on automatic formulas. As an example, I’ll show you how to add the Cost of Sale metric to your report.
To do this, in the “Admin” section, find “Calculated Metrics” in the menu, and click on this item.
A section with all Calculated Metrics will open. To create a new metric, click on the “+ New calculated metric” button.
In the settings, specify the name of your metric (in this case, Cost of Sale), in the Formatting Type select “Percent”. The formula is as in the screenshot below:
Click on the “Create” button. Now you can add the created metric to the report in the same way as shown above.
The final ad performance report might look like this:
It is worth noting that you can analyze ad performance not only at the Source / Medium level but also at a deeper level. To do this, you need to select the Secondary Dimension you are interested in, for example, Campaign, Ad Content, or Keyword.
You can save the report so that you do not have to configure it again in the future. It will be available in the Customization → Saved Reports section. To do this, you need to click Save and specify the name of your report.
Congratulations! Now you know how to import ad cost data into Google Analytics to analyze ad performance metrics such as ROAS, Cost of Sale, and more.
How to evaluate ROAS if the conversion on the site is a lead form application, not a sale?
For many businesses, a conversion on the site is a lead, registration, phone call, or other action that does not directly have any monetary value (unlike online stores, where conversion value = the amount of the placed order).
In this case, you can solve the ROAS analysis problem as follows:
- Method #1: It is not necessary to analyze the ROAS metric in Google Analytics at all. If the main conversion on your site is a lead form application, you can analyze ad performance using the Cost per Lead metric. Let’s say you know that you are ready to pay up to $50 for one lead. Thus, all channels and campaigns where Cost per Lead is less than $50 will be effective for you. If you want to calculate the return on investment in advertising (ROAS), then you can use the following two methods.
Method #2: The most basic and not entirely accurate way to analyze ROAS is to manually set a conditional conversion value when setting it. For example, you know that, on average, one lead turns into $250 in final sales. You can use this number as the conditional conversion value. For more information on how to set up conversion value, see the Google Analytics Help.
Thus, in the report in Google Analytics, you will see the following:
- You spend $2 500 on ad campaign;
- Thanks to this ad campaign, you received 50 leads. Average cost of one acquired lead (CPL) = $50.
- If the value of one lead for your business is $250, then 50 leads mean the total received value = $12,500.
- To calculate the ROAS formula, revenue must be divided by ad costs and multiplied by 100%. In this case, the ROAS will be = (12,500 / 2,500) 100% = 500% ROAS, and the Cost of Sale will be 20%. Cost of Sale is calculated using the formula (**cost / revenue 100%**).
- Method #3: If you are not comfortable with such calculation and want to combine data from Google Analytics with data on real sales from CRM, we recommend using Google BigQuery storage to combine all data and build accurate end-to-end analytics. Read on:
When is Google Analytics not enough?
Despite the fact that Google Analytics provides you with a possibility to import advertising costs and load offline data, this tool is not always enough for analyzing marketing performance.
In particular, if:
- You want to analyze a large number of dimensions and metrics, both online and offline. It is much easier to export data from Google Analytics for further analysis than trying to load all offline data into Google Analytics;
- Your reports require complex calculations and data transformations that are impossible or very difficult to accomplish in Google Analytics;
- You need visual marketing reports that you can’t get within the standard Google Analytics interface;
- You want to apply advanced or custom attribution models that are different from those available in Google Analytics;
- And so on.
In this case, companies begin to build marketing analytics based on their own data warehouse, where absolutely all data from all online and offline sources is collected without any restrictions. One of the most popular cloud storage solutions on the market is Google BigQuery.
By using Google BigQuery (or other data warehouses), businesses have the opportunity to:
- Collect raw data on a huge scale, without any limits or sampling;
- Transform and combine data from different sources according to any logic;
- Be able to build absolutely any report using SQL queries;
- Visualize reports in any BI system - Google Data Studio, PowerBI, Tableau, Looker or another;
- Apply advanced attribution models, including machine learning-based attribution;
- And do a lot more;
I hope the article was useful and now you know how you can analyze the performance of all your advertising campaigns right in the Google Analytics interface. I would be glad to hear your comments and answer questions!
This article was originally published on CXL.com by Constantine Yurevich, CEO & Co-founder of SegmentStream.
You might also be interested in
Data Monitoring Public Beta!Learn more
How Machine Learning Can Finally Solve “(direct)/(none)”Learn more
We've launched Data Streams!Learn more
Get started with SegmentStream
Request a personalized demo with our team or try SegmentStream for free today!