How to Run an Effective Ad Test Analysis

Next up in our Ad Words Reporting 101 series: How to Run an Effective Ad Test Analysis! This is an extremely useful report that will tell you a lot about general performance, but more importantly about the performance of individual headlines, descriptions, and their combinations. Let’s dive in!

Why?

INFORM. Keep clients up to date on how their ads are performing.

DECIDE. Make decisions about which ads are most successful and which ads are least successful and should be paused.

OPTIMIZE. Ultimately, as certain ads are paused, optimize account performance.

How?

1. Download an ad report off Google Ads

2. Create a pivot table that sums impressions, clicks, cost, and conversions by ad.

3. Calculate CTR, CPC, CVR, and CPA for each ad and conditionally format these fields

4. Identify best and worst performing ads

Skills

1. Excel concatenate function

2. Excel text to column feature

3. Excel calculation functions

4. Create a pivot table in Excel

5. Conditional and number formatting in excel

6. Find and replace tool in Excel

1. Download an ad report off Google Ads


In Google Ads, click under the account you are working with and then hit the “ads & extensions” tab in the left bar:

image8.png

Next, set the filters by clicking into the bar that looks like this:

image3.png

Filters:

  • Campaign Name: contains “google-search” (or use whichever designator you use for designating google search ads)

  • Impressions: >0

  • Ad Status: all but removed

Date range:

  • 30 days up to yesterday

image15.png
image10.png

Columns:

  • Impressions

  • Clicks

  • Cost

  • Conversions

2. Modify the report and create a pivot table that sums impressions, clicks, cost, and conversions by ad

Modify the report:

REMOVE CUSTOMIZATIONS. If there are any customizations in the headlines or descriptions remove them. For example, all city names will be replaced with {city}, all prices with {price}, and all services with {service}.

To do this, use Excel's Find and Replace tool.


CONCATENATE AD INFO. In a new column, concatenate the headline, and description, all separated by semicolons. That formula would look something like this: =[headline cell]&”;”&[description cell].


Pivot Table:

CREATE PIVOT TABLE. Select all raw data and insert a pivot table in a new tab. Go to the “insert” tab in Excel and hit “PivotTable”.

Drag the concatenated ad info field in the “rows” pivot section and the impressions, cost, clicks, and conversions into the “value” field.

COPY TO A NEW TAB. On a new tab, remove all lines by selecting the entire sheet and hitting the white color under the paint bucket. This makes the sheet presentable to clients.

Next, copy the pivot table, excluding the grand total row. Paste the table in the new tab by right-clicking and selecting “Paste Special” and paste values only. Next, right clicking on the same cell, hit “Paste Special” and select paste format.

PARSE CONCATENATED FIELD. Insert 2 rows before the impressions column. Then, select the concatenated ad info column, and choose “text to columns” under the Data tab in excel. Select “delimited with” and then “semicolon” and “finish”. This should separate the headlines and descriptions into multiple columns.


3. Calculate CTR, CPC, CVR, and CPA for each ad and conditionally format these fields


INSERT CTR, CPC, CVR, CPA ROWS. Insert a row before cost, before conversions, and 2 after conversions labeled respectively: CTR, CPC, CVR, CPA.

image5.png

CALCULATE CTR, CPC, CVR, CPA. Using a formula, calculate the CTR (clicks/impressions), CPC (cost/click), CVR (conversions/click), and CPA (cost/conversion) of each ad.


TIP. If you get errors because you are dividing by 0, wrap your calculation with the IFERROR function


FORMAT NUMBERS. Format all rates to the % format with two digits after the decimal point and all dollars to the $ format.

CONDITIONALLY FORMAT. Add color scales to the CTR, CPC, CVR, and CPA columns by using the conditional format option in the Home tab in excel. CTR and CVR are scaled with larger numbers in green and CPC and CPA scaled with larger numbers in red.

Identify best and worst performing ads

BEST PERFORMANCE. For each category, identify which ad has the best overall performance: high CTR/CVR and low CPC/CPA.

WORST PERFORMANCE. For each category, identify which ad has the worst overall performance: low CTR/CVR and high CPC/CPA.

REPEAT THE PROCESS: In the pivot table, pull the headline and category data into the rows section with the category on top. This will show performance by headline in each category. Copy the table to a new tab and repeat the previous steps. Do the same for description. The end result should look like this:

PRESENT TO CLIENT. Either present the high level summary of the best and worst performing ads to the client directly, or convey the information to the account manager succinctly so they can easily communicate the report to their contacts.

Communicating Results

Use the conditional formatting to determine which headlines and descriptions are performing well. A good metric to use to determine overall performance is CTR. When ads have high CTR and a good number of conversions, they should perform better when more of the budget is dedicated to those ads.