How to Perform a Placement Analysis

In the next installment of our Ad Words Reporting 101 series: How to Perform a Placement Analysis. Let’s dive in!


Why?

INFORM. Keep clients up to date on where their ads are performing best and worst.

DECIDE. Observe which are most and least successful and determine action plans.

OPTIMIZE. Ultimately optimize account performance.


How?

1. Download a placement report off Google Ads

2. Create pivot tables and calculate CTR, CPC, CPA, and CVR

3. Create a performance summary of placements and placement URLs


Skills

1. Excel calculation functions

2. Create a pivot table in Excel 

3. Conditional and number formatting in excel


End product:

image2.png



1. Download a placement report off Google Ads

Filters:

  • Dates: last 30 days up to yesterday

  • Impressions: >0

  • Ad Status: all but removed


Columns:

  • Impressions

  • Clicks

  • Cost

  • Conversions

2. Create a pivot table that sums impressions, clicks, cost, and conversions and calculate CTR, CPC, CVR, and CPA

Pivot Tables:

CREATE PLACEMENT PIVOT TABLE. Select all raw data and insert a pivot table in a new tab. Pull the “Placement” field in the “rows” pivot section and the impressions, cost, clicks, and conversions into the “value” field. On a new tab copy the pivot table and paste values, then paste format. 

REPEAT WITH PLACEMENT URL INFO. Go back to the pivot table and pull “Placement URL” into the rows section of the pivot table, and remove “Placement”. Copy this table into a new tab.

image6.png

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

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

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. 

3. Create a performance summary of placements and placement URLs

Performance Evalutation Metrics:

TOP PERFORMANCE.

  • CPA sorted ascending, clicks > 0, conversions > 1

  • Conversions sorted descending, cost sorted ascending

POOR PERFORMANCE.

  • Conversions = 0, cost sorted descending

  • Clicks = 0, impressions sorted descending

  • CPA sorted descending

  • Conversions sorted ascending, cost sorted descending

PERFORMANCE SUMMARY. For both placements and placement URLs, sort and filter the data according to the evaluation metrics listed above. Pull a few placements/placement URLs for each and paste them in a performance summary. The summary should look like this:

Google Ads 101Claire Forgan