Google Shopping Reporting: How to Export Product-Level Data
Updated January 2021
I’ve been asked several times now by clients for a single report that consists of a list of all their Google Shopping products and their corresponding performance data such as number of clicks, sales transactions, revenue, cost-per-sale and ROAS etc.
Whilst you’re able to see this data(ish) within Google Ads manager, there are some drawbacks. You can view product-level performance data (with sales conversions) but only within an individual Campaign or Ad Group rather than being able to see ALL products from ALL campaigns which would give you a full list of products and corresponding sales in a single view.
As of January 2021, the lowest level you can get to and still see product sales data is Campaigns > Ad Groups > Product Groups
Even if do go into an individual Campaign or Ad Group, the "DOWNLOAD" button is only available up to the Product Group level:
If you then go to Product level you'll see the "DOWNLOAD" button disappears:
You might think of creating this same table of data in a custom report, using “Product Title” and a range of performance metrics which YES you can download… but strangely there’s no option to add “Conversions” to the table. You CAN however use "Product ID" and your range of performance metrics, but if your product ID's are random SKU numbers
This is still the case as of January 2021.
I also asked within the PPC community if they know of a way but they've all said my solution below is the closest to achieving a single list of individual products with corresponding sales performance, so keep reading...
So all these restrictions means I’ve either not been able to provide clients with the level of reporting they’re asking for, or I’ve had to talk them through how to find the data in Google Ad manager themselves, which is less than ideal if A) they’ve no interest in learning how to navigate around an unfamiliar tool, and B) they want to be able to share and discuss the data amongst their in-house team with ease.
Why Google simply won’t allow you to download data at product level I’ve no idea, but I've finally found a work-around that gets us all close!
The solution at a glance
Basically, we want to create a spreadsheet with a complete list of individual shopping products and their ad performance.
In order to do that, we need to combine the “Item ID” from a custom report created in Google Ads with the “id” and “title” attributes in the product feed that gets imported into Google Merchant Center. Then use a VLOOKUP formula to match up the Item ID with product titles.
- Download a custom report in Google Ads which includes “Item ID” and your performance metrics (this will download as an Excel CSV).
- Create a new blank Google Sheet and copy across all the data from the Excel CSV into your Sheet1 tab. Then with “Item ID” in column A, insert a new column headlined “Product Title” to the right of this which will be your new (empty) column B.
- Open your Google Shopping product feed spreadsheet (this will either be a Google Sheet, Excel file or an XML file converted into a CSV using this free converter) then copy the “id” and “title” columns into columns A and B in a separate Sheet2 tab on your new Google Sheet.
- On the Sheet1 tab of your new Google Sheet, copy this formula into the empty cell B2: =VLOOKUP(A2,Sheet2!A:B,2,FALSE) - the cell should now be populated with the correct product title corresponding to the Item ID.
- Drag the corner of cell B2 with the newly populated product title and copy all the way down to the end of your Item ID list - you’ll now have a fully populated list of products each with their own performance data.
And that's it!
For more a in-depth step-by-step guide with screenshots, please follow the steps below…
1. Download a custom report in Google Ads
Whilst within your Google Ad account, click on “REPORTS” in the top right menu, then click “Reports” in the drop down menu.
Create a custom report by clicking “+ Custom” on the left, then “Table” in the drop down menu.
From the menu on the left, select “Item ID” and drag it over to the table, then add your chosen performance metrics eg, cost, impressions, clicks, conversions etc.
Set your reported time period.
Click “DOWNLOAD” in the top right and save as .CSV.(Remember to save your report and name it something like “Shopping Report by Product” in case you need to use it again in the future).
2. Create a new Google Sheet
Create a new blank Google Sheet and copy across all the data from the Excel CSV into the Sheet1 tab.
With “Item ID” in column A, insert a new column headlined “Product Title” to the right of this which will be your new (empty) column B.
3. Copy data from your Google Shopping feed
Open your Google Shopping product feed spreadsheet (this will either be a Google Sheet or Excel file - sorry won’t work if using an XML feed) then copy the “id” and “title” columns into columns A and B in a separate Sheet2 tab on your new Google Sheet.
4. Insert the formula (the magic part!)
On the Sheet1 tab of your new Google Sheet, copy this formula into the empty cell B2:
You should now see cell populated with the correct product title corresponding to the Item ID:
5. Populate the full list of product titles
Drag the corner of cell B2 with the newly populated product title and copy all the way down to the end of your Item ID list - you’ll now have a fully populated list of products each with their own performance data. Result!
This simple solution should now enable you to review how your shopping campaigns are performing at a more granular product-level, such as:
- Which products are using up most of your budget (cost)
- Which products are being shown most in Google (impressions)
- Which products are generating most visitors to your website (clicks)
- Which products (or variants) are people engaging with most (CTR = click through rate)
- Which products are generating most sales on your website (conversions)
- Which products are achieving a healthy and profitable ROI for your business (cost/conv.)
This should make life much easier for you when trying to identify ways to optimise and improve the performance of your Google Shopping campaigns.
Hope this guide was useful. Please reach out to me at firstname.lastname@example.org with any specific questions and I'll do my best to help.
Now all we've gotta do is find a way to automate the damn thing! Perhaps for another day...