How to Perform a Sitelinks Extension Analysis

Today we’re covering an important part of our Ad Words Reporting 101 series: How to Perform a Sitelinks Extension Analysis. Let’s dive in!

Why?

INFORM. Keep clients up to date on which ad extensions are performing best and worst.

DECIDE. Determine action plans based on observations.

OPTIMIZE. Ultimately optimize account performance.


How?

1. Download a sitelinks extensions report off Google Ads

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

3. Conditionally format and highlight top and poor performers


Skills

1. Excel calculation functions

2. Create a pivot table in Excel

3. Conditional and number formatting in Excel

4. Excel text to column feature


End product:

1. Download a sitelinks extensions report off Google Ads

Download report from predefined reports:

image2.png

Filters:

  • Dates: last 90 days up to yesterday

  • Campaign name contains “google-search”

  • Ad Status: all but removed

Columns:

  • Campaign (you need to add this column in this type of report)

  • Impressions

  • Clicks

  • Cost

  • Conversions

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

Modify the report:

REMOVE IMPRESSIONS =0. Filter the impressions column for impressions=0 and delete all those rows.

ISOLATE GEO. Create a column called “city” or “geo” and copy the campaigns into that column. Use the find and replace tool in excel to isolate the geo in the campaign so that it is the only text in the geo column.

ISOLATE CATEGORY. Create a column called “category” and copy the campaigns into that column. Repeat what you did in the previous step but this time isolating the category. Use the find and replace tool in excel so that category is the only text in the category column.

Pivot Table:

CREATE TOTALS PIVOT TABLE. Select all raw data and insert a pivot table in a new tab. First, pull the “Extension” 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. Copy the pivot table, excluding the grand total row and paste values, then paste format in the new tab.

REPEAT WITH GEO INFO. Go back to the pivot table and pull “geo” into the rows section of the pivot table, above “extension”. Copy this table under the first extension table in the new tab you created in the previous step.

REPEAT WITH CATEGORY INFO. Go back to the pivot table and pull “category” into the rows section of the pivot table, above “extension” and remove the “geo” field. Copy this table beside the two tables already pasted in the new tab you created.

REMOVE “SUM OF ”. Use the “find and replace” tool to remove all the “sum of ” text in the three tables. For example, “sum of impressions” will display as “impressions.”

BREAK OUT EXTENSIONS. In the extensions columns, there are multiple pieces of text separated by a “;”. Use the “text to column” tool in excel to break out those pieces of text into separate columns. You will first need to insert rows right before the impressions column.

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.

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.

NAME EACH TABLE. In each table, use the cell that says “Rows” in the top right to label the table. The first is called “Sitelink Extension Totals”, then “Sitelink Extensions By Geo”, and “Sitelink Extensions By Category”.

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 extension, identify which has the best overall performance: high CTR/CVR and low CPC/CPA.

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

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