In this guide you will learn how to use basic data analysis to find optimization opportunities using your analytics data and pivot tables from spreadsheets.
I will compare the website page types (namely product pages, product categories & brands) in order to find which type performs best.
Now let’s dive in.
Step #1: Download your Landing Page data from Google Analytics
That’s the easy part. Go to your Google Analytics > Behavior > Site Content > Landing Pages and export them as .csv or export them to Google Sheets (I prefer google sheets over Excel but it’s just my personal preference)
Step #2: Load your data into Google Sheets to modify them
After you load your data on your spreadsheets, it’s time to clean them. At the end the only columns you should have will be:
Keep it nice and simple.
Step #3: Use the landing Page URL structure to put your landing pages into Category Groups
This is very important, because it will group together landing pages with similar attributes (products with products, brands with brand and product categories with product categories).
The process is simple: If the URL contains XXX then the Category Column Should have the YYY value
if the Url Contains
then the Category column will be
if the url is equal to “/”
At the end data should be something like this:
*Below you can find a video on how I did it and use the same process for your own analysis:
In order to create a Pivot Table get all your cleaned data with “CTR+A” and go to Data > Pivot Tables
Then do Pivot Table > New Sheet
After that you will be presented with a new empty sheet like the one below.
Now we need to add the data.
Go to Rows and add the Category
Go to Values and add the Sessions
Go to Values and add the Transactions
Go to Values and add the Revenue
Now we have a nice and small table.
As we said before keep it simple.
We have our table but still it’s not very clear which page type performs best. So we need to add:
Conversion Rate%. In order to see which Page Type converts better
Average Order Value. In order to see which Page Type creates higher value transactions
To do this we need to add those metrics as custom columns (see the gif below).
The Conversion Rate formula is Sessions (column B) / Transactions (column C)
The Average Order Value formula is Revenue (Column D) / Transactions (Column C)
Final Pivot Table
All righty! Now we have all the data we need and with just one look we can understand which page type performs best.
Brands Pages out perform Product & Product Category Pages both in CR and in Avg. Basket Value
Drive more traffic to Brands Pages
Find ways to improve the user experience in Product and Product Category Pages
Homepage has Great CR and Avg. Basket Value
Find ways to increase your brand presence
“Others” have suspiciously high CR
Find which pages are in the “Others” bucket and determine why the CR is so high (my experience says that they are the Cart Pages that by default have very high CR% because their traffic is usually from remarketing
Now it’s your turn to find and answer deep and meaningful business questions.
In this piece we will talk about how to optimize the black box that Smart Shopping Campaigns are.
As we all know Smart Shopping Ads (SSA) are very profitable and very easy to implement. All you need is a Google Merchant Center, a product feed and you are ready to go.
When you set them up the only thing you need to setup is Budget and ROAS target (keep in mind that if you don’t put anything on it it will automatically target 200% ROAS.
In Smart Shopping Ads there are no search terms (thus you can’t use negative keywords) and you also don’t have visibility on which products you sold. It’s basically a black box, you give it a feed, a budget and a target it tries to reach the goal.
The only thing you can is exclude products or product groups much like a normal shopping ad. But since you have no visibility in the product’s performance you don’t know what to exclude and what to keep.
Oh but you can! You just need another tool…
You need… Google Analytics (or whatever analytics tool you are currently using).
Do the following steps:
You go to Behavior > Site Content > Landing Pages
Have as Primary Dimension: Landing Page
Put the Secondary Dimension: Campaign
Then go to the Advance Filter and use Include > Campaign > *The smart shopping campaign name*
You should have a view like the one below
Now you are ready to do some serious analysis that will also come some serious optimization.
Short by Sessions and identify the landing pages that have high volumes and 0% or very low Conversion Rate
These landing pages are the first candidates to exclude/remove from my feed. Why? Because if these landing pages have traffic that means that you pay for this traffic but it does not convert so you are losing money.
After finding these landing pages, find which product they have and from there you can:
Go to Google Ads > Campaigns > Smart Shopping Campaign > Product Group and exclude them from the product group
or Ask your developers to remove this product from your feed.
Why you need to do this: By cutting off the high spending low performing products you give more room to the ones that perform. If you spend 15% of your budget on non converting/high spending items with this technique you force google to re-allocate your spending into more profitable items.
Now I go to my Google Analytics Account to spot the bad performers that waste my budget in order to exclude them from my catalog.
Go to Behavior > Site Content > Landing Pages
Then on the page that will load go to Secondary Dimension and choose Campaign
After that go to Advanced > Include > Campaign > containing > DPA
And press Apply
And now we have the gold report :D!
So what do we see here?
We see landing pages that we send traffic through our Product Catalog Ads (“DPA” stands for “Dynamic Product Ads”).
We also see the Sessions, Transactions, Revenue & Conversion Rate.
(Exercise for you)In this report can you spot the worst performing landing page (row number)?
Yes! It’s the line 8.
Why? Because we have 205 Sessions but 0% conversion Rate.
Meaning that we spend budget to drive traffic to that page but they do not convert!
We need to investigate why this is happening and either fix the landing page or exclude this product from our FacebookProduct Catalog.
Amazing stuff! That’s the power of the UTMs
(Exercice for you #2)Can you spot some, less obvious, landing pages that underperform?
Yeap! You guessed it again!
Line 1 & Line 4
Why? Because the overall Conversion Rate for this campaign is 2.10% and they have 1.29% & 0.96%. So we need to investigate why this is happening. Since we have conversions the approach here is different, one way to proceed is to check if the pricing is correct and if the offer is easy to understand for the customer.
Now is your Turn! Harness the power of UTMs and Google Analytics!
Share with us what you found in the comments below.