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:
- Landing Pages
- Sessions
- Transactions
- and Revenue
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 |
| /category-page/ | Product Category |
| /προϊόν/ | Product Page |
| /brands/ | Brands |
| if the url is equal to “/” | Homepage |
| everything else | Others |
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:
—–> VIDEO <—-
Step #4: Use Pivot Tables to find insights
The biggest hidden secret for data-driven marketing is the use of Pivot Tables. With Pivot Tables you can find very useful insights.
If you master Pivot Tables you will be ahead of 99% of the competition.
Keep in mind that if you want to learn the art of data-driven marketing you need to focus on how to read the data you have.
Here is a video explaining why Pivot Tables are sooooooo important
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.
| Insights | Actions |
| Brands Pages out perform Product & Product Category Pages both in CR and in Avg. Basket Value |
|
| Homepage has Great CR and Avg. Basket Value |
|
| “Others” have suspiciously high CR |
|
Now it’s your turn to find and answer deep and meaningful business questions.
