Categories
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
/brands/Brands
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.

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

Categories
Analytics Facebook Ads PPC Tips & Tricks

How to Combine UTMs with your Google Analytics to get some super tricks to optimize your PPC efforts

How to Combine UTMs with your Google Analytics to get some super tricks to optimize your PPC efforts

 

When I run Product Catalog Ads I use the prefix “DPA when I name the Campaigns. You will se why in a minute.

 

 

On your Dynamic Facebook Campaign Name always use an indicator (mine is "DPA")
On your Dynamic Facebook Campaign Name always use an indicator (mine is “DPA”)

 

 

In these ads the UTM parameters I use is this:

?utm_source=Facebook&utm_medium=cpc&utm_campaign={{campaign.name}}&utm_content={{ad.name}}

So the final URL where the prospect lands is this:

 

https://example.com/?utm_source=Facebook&utm_medium=cpc&utm_campaign={{campaign.name}}&utm_content={{ad.name}}

 

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

 

 

google analytics report behavior site content landing pages
Google analytics > Behavior > Site Content > Landing Pages

 

 

Then on the page that will load go to Secondary Dimension and choose Campaign

 

 

Landing Page Report > Secondary Dimension
Landing Page Report > Secondary Dimension

 

 

After that go to Advanced > Include > Campaign > containing > DPA

And press Apply

 

 

Put "DPA" on your Advanced Filtering
Put “DPA” on your Advanced Filtering

 

 

And now we have the gold report :D!

So what do we see here?

 

 

landing page report filtered by "DPA"
Landing Pages Report report filtered by “DPA”

 

 

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 Facebook Product 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.

👇 👇 👇

Categories
Facebook Ads Tips & Tricks

How to create UTMs for Facebook Ads

How to create UTMs for Facebook Ads

In this article I will show you how to create, the correct way, UTMs on Facebook Ads Manager

I have determined that the best way to use them while I run PPC ads is this!

https://www.example.com/?utm_source=Facebook&utm_medium=cpc&utm_campaign={{campaign.name}}&utm_content={{ad.name}} 

Meaning that this parameter should always be at your ads.

utm_source=Facebook&utm_medium=cpc&utm_campaign={{campaign.name}}&utm_content={{ad.name}}

You need to login into your Facebook Ad Account and go to the Ads level find the ad you want and press Edit.

 

 

pasted image 0 6

 

Got to the "Ad" level and press "Edit"
Got to the “Ad” level and press “Edit”

 

 

When the Ad Page pops up scroll down until you find the URL Parameters section. Then click on Build a URL Parameter.

 

 

Section where the UTM builder is
Section where the UTM builder is

 

 

Then the URL Creation Page will appear. Add the following values in the fields.

Campaign Source: Facebook

Campaign Medium: cpc

Campaign Name: {{campaign.name}}

Campaign Content: {{campaign.content}}

Press Apply and your are good to go

 

 

Facebook UTM Builder
Facebook UTM Builder

 

 

Next we will see 2 reasons why UTMs are important

 

Categories
Analytics PPC Tips & Tricks

2 Reasons to have UTMs in your ads

2 Reasons to have UTMs in your ads

Reason #1: It shows you what marketing channel works and what doesn’t.

Then you can draw insights, and then you can do actions to fix or scale.

Let’s look at the data below.

 

 

source / medium Report on Google Analytics
source / medium Report on Google Analytics

 

 

This is the data from 15/11/2020.

Since we used UTMs in our ads, every Session, Transaction and Revenue that came from the Ads is attributed in the Facebook / cpc

Everything else (organic posts for example) is attributed into the other sources / mediums that end with the /referral.

This gives us clarity on data and we can draw insights from this report.

  • Why is the ads conversion rate so low? Maybe we need to check where we send traffic (landing pages)
  • Why is our revenue so high on organic posts (referral)? Should we ramp up our efforts there and create more content?

On the other hand let’s look at an account that does not uses proper UTMs

 

 

source / medium Report on Google Analytics only Facebook filter
source / medium Report on Google Analytics only Facebook filter

 

 

As you can see everything in the Source / Medium is attributed to referral (although the client runs ads).

With this we are not sure if these 2 Transactions came from ads or organic posts thus we are unable to take actions and as a result we cannot fix or scale anything.

Reason #2: Enables you to some high level Performance Marketing Tactics that are only able when you use UTMs

Let me give you an example from one of my campaigns:

If you run Catalog Sales Campaigns on Facebook you can argue that they are some of the most profitable campaigns you can run.

You upload a Facebook Product Catalog and the algorithm shows the products to prospects (or Website Visitors if you can installed the Facebook Pixel and used Remarketing Audiences)

But the downside is that you don’t know which products are shown!😨

So if the campaign has profitable ROAS that’s great news 👍! (still you don’t know which products sell better, so you can’t optimize)

But if the campaign has un-profitable ROAS you still don’t know which products perform poorly in order to exclude them 👎.

So what do you do?

You guessed it! You use UTMs

Let me show how.

How to Combine UTMs with your Google Analytics to get some super tricks to optimize your PPC efforts

 

Categories
PPC Tips & Tricks

📈 Validate your Business Idea with Google Keyword Planner

Time to read: 4minutes

Step #1 Access the Keyword Planner from Google Ads

Step #2 Prepping the Analysis with Google Sheets

Step #3 Getting the total volume per month

Step #4 Getting Insights from your data

In this guide we will discuss how you can validate a business idea using tools from Google.

Specifically we will discuss:

  1. How to find a good market using competitor’s data
  2. See the seasonality trends
  3. Find if the market is growing or not

The tools we are going to use are Google Keyword Planner Google Sheets.

You will also need:

  • Access to a Google Ads Account
  • Google Sheets

So let’s dig!

Access the Keyword Planner from Google Ads

You log in into the account go to “Tools” > “Keyword Planner”

image5 1

Tools > Keyword Planner

Then a page will load. Choose “Start With a Website” ,choose an industry leader’s website and press “Get Results”

image8 1

Don’t forget to choose the country you are targeting and the language

After that the page with the results will appear. In order to do a good analysis we need at least 24 months old data (we need this to compare monthly trends).

image10 1

Also make sure you have excluded your competitors brand from the keyword ideas

image1 1

After that your just need to press the “Download Keyword Ideas”

Prepping the Analysis with Google Sheets

After you upload the .csv file into Google Sheets (or Excel) you will get the data that look like this:

image12 1

Raw data from Keyword Planner

After cleaning the file you should have a more concrete dataset

image11 1

The columns you need are “Keyword” and volumes by month (e.x. “Nov 2018”, “Dec 2019”, etc)

Now we need to make sure that the numbers are actually numbers and not text.

To do that we need:

  • Get all the numbers
  • Click “Format”
  • Click “Number”
  • And then again “Number”

image9 1

Making sure that our numbers are actual numbers

Getting the total volume per month

For our next step we need to find the total volume for each month.

In order to this we do the following:

  • Add a new row below the “Keyword” row
  • Use the “=SUM()” formula
  • Do it for all the months

image2 1

Creating the Total Volume Column

Getting Insights from your data

Now we have the Month, the Year and the total search volume for that specific website

image6 1

We have the full search volume that the competitor is eligible to show for each month

Now we will create percentages in order to see emerging patterns

YoY Growth (per month)

For example in the following table we compare the search volume growth for each month. We see that from November to February we do not see much growth. Things do not look good 😞.

image4 1

But from May to Oct we see a growth in search volume at least 30% each month 😃!!

This means that Shoes seasonality starts when the weather get warmer.

image7 1

Finally if we get the volume for each year we will get the Year over Year Search Volume Growth.

We see here that from 2019 to 2020 the searches for shoe related keywords increased to 21.30%.

That means that going into this business is a good idea.

image3 1

Now try that for yourself and comment below to share any insights 😃