Analytics Tips & Tricks

Identify CRO Opportunities by comparing Page Types with Pivot Tables

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.

Final Data Sheet

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 Containsthen the Category column will be
/category-page/Product Category
/προϊόν/Product Page
if the url is equal to “/”Homepage
everything elseOthers

At the end data should be something like this:

The data sheet with the new categorization

*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.

  1. Go to Rows and add the Category
  2. Go to Values and add the Sessions
  3. Go to Values and add the Transactions
  4. Go to Values and add the Revenue
Add the Rows & Values in the Pivot Table.

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:

  1. Conversion Rate%. In order to see which Page Type converts better
  2. 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)
The Process of Adding the 2 Custom Columns in Pivot Tables

Final Pivot Table

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
  1. Drive more traffic to Brands Pages
  2. Find ways to improve the user experience in Product and Product Category Pages
Homepage has Great CR and Avg. Basket Value
  1. Find ways to increase your brand presence
Others” have suspiciously high CR
  1. 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.