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.

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

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.

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

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)

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.

Now it’s your turn to find and answer deep and meaningful business questions.