This template enables users to analyse Google AdWords online marketing performance by ad position which then enables business owners and ad managers to determine which ad positions are optimal in order to ensure maximum profitability. Users are required to copy daily AdWords statistics by campaign, ad group and device into the template and the automated analysis can then be viewed for any user defined period. All ad position calculations are based on the daily average position for each device.

The following sheets are included in the template:
Set-up - specify your reporting dates in the input cells at the top of the sheet. These date settings determine which dates are set for the current, previous1 and previous 2 periods in the analysis on the Positions sheet. You also need to add your devices, campaigns and ad groups to the lists on this sheet.
Data - create a report by using the Reports feature in Google AdWords and export your report data to Excel before copying & pasting your data onto this sheet. The columns that need to be included in the report are exactly the same as our column headings and the columns need to be included in the report in the same sequence as they are included on this sheet. The data on this sheet is automatically updated to the analysis on the Positions sheet.
Positions - specify your ad position parameters by entering the value parameters in the High columns before selecting the appropriate campaign, ad group and device from the list boxes at the top of the sheet. All the calculations in the columns with light blue column headings are automated and provide AdWords statistics that are based on the daily average positions per device. Statistics for the current period which is specified on the Setup sheet can also be compared to 2 previous user defined periods.

Set-up

The date ranges that are used for the ad position analysis on the Positions sheet need to be specified at the top of the Setup sheet. The Positions sheet includes a current period, previous 1 period and a previous 2 period. All of these periods are dependent upon the from and to dates that are specified in cells B5 and B6 on the Setup sheet.

In terms of the current period for the analysis, the from and to dates that are entered can be for any date range and does not have to include full calendar months. You can even specify a single day in both the from and to dates to view an analysis for only that day. The date range can also span multiple calendar months by simply entering a from and to day that span multiple months.

Note: If you clear the contents of the two input cells, the first day of the current month will be used as the from date and the last day of the current month will be used as the to date. Also, if you enter a to date which is before the from date, the to date cell will be highlighted in red to indicate the error.

The previous 1 and previous 2 from and to dates will always include full calendar months and the number of calendar months that are included is determined by the value that is entered in cell B8. The input in this cell does not influence the current period, only the two previous periods. The previous period 1 is further determined as the month which immediately precedes the from date that is specified in cell B5 and the previous 2 period will be the period that precedes this period.

Example: If the number of calendar months that are specified in cell B8 includes say 3 months, both the previous 1 and previous 2 from and to dates will span 3 months with the previous 1 period being the 3 full calendar months immediately before the from date which has been entered in cell B5 and the previous 2 period being the 3 month period before that.

Note: The previous 1 and previous 2 from and to dates are determined by the formulas which have been entered in these cells. If you replace these formulas with your own dates, the previous 1 and 2 periods on the Positions sheet will not function as per the above design but you will then be able to enter user defined date ranges for these two sections on the Positions sheet.

The Setup sheet also includes a list of devices which can be edited by simply inserting a new row anywhere above the "End of list" row and adding a new entry in column A. All the entries that are added to the list will automatically be included in the device selection cell on the Position sheet.

The next section on the Setup sheet contains the Campaign list which is populated in the campaign selection list box on the Positions sheet. All the campaigns that have been created in your AdWords account should be included in this list. Missing campaigns will not be available for selection on the Positions sheet. New campaigns can be included by inserting a new row anywhere above the "End of list" row and adding a new entry in column A.

The final section on the Setup sheet contains the Ad Group list which is populated in the ad group selection list box on the Positions sheet. Each ad group also needs to be linked to a campaign in column C. All the ad groups (and their linked campaigns) that have been created in your AdWords account should be included in this list. Missing ad groups will not be available for selection on the Positions sheet. New ad groups can be included by inserting a new row anywhere above the "End of list" row and adding a new entry in column A with the appropriate campaign in column C.

Note: The easiest way of maintaining your Campaign and Ad Group lists is by exporting the data from Google AdWords to Excel and then copying the data into the appropriate sections on the Setup sheet. You just need to ensure that there are sufficient rows in the appropriate section before copying the data.

Data

This template requires the inclusion of daily AdWords data by campaign, ad group and device. This data should be copied onto the Data sheet. The easiest way of including your data is by creating the appropriate report through the Google AdWords account interface and then exporting the data to Excel before copying the data from the Excel extract onto this sheet. The report needs to be created so that the columns are included in exactly the same sequence as the columns on the Data sheet although you can add columns or move columns so that the sequence between your extract file and the columns on the Data sheet are the same.

Note: The AdWords fields that you need to add to your report will have the exact same field names as the column headings that we have included on the Data sheet. The Day, Campaign, Ad Group and Device columns need to be added to the Row section of the report and the other fields need to be added to the Columns section.

The AdWords report can be extracted as an Excel or CSV file and downloaded. When you then open the report, you will be able to copy the data onto the Data sheet in this template. Note that some columns may contain a currency symbol which may result in the data in that column being interpreted as text instead of values by Excel. We therefore recommend removing all currency symbols from your Google data (Control + H for Replace and then replace currency text with nothing for the entire data selection).

Note: All the columns on the Data sheet have been included in an Excel table. This feature is extremely useful when entering or copying data in a table format because the cell range to which the table refers is automatically extended when you add data to the table. The table also extends automatically to include the copied data as long as you copy the data into the first blank row below the table.

Ad Positions

The Positions sheet enables users to view AdWords statistics for a user defined range of ad positions. All the calculations on this sheet are automated and based on the daily average positions per campaign, ad group and device which is included on the Data sheet. You can also select individual campaigns, ad groups and devices form the list boxes at the top of the sheet in order to view only the statistics for the selected items.

Note: All the calculations on the Positions sheet are based on daily average positions. It is unfortunately not possible to compile a report in AdWords which includes specific ad positions and we therefore use daily averages instead in order to highlight optimal ad positions which result in maximum profitability.

Note: The list boxes at the top of the sheet are populated based on the device, campaign & ad group lists which are included on the Setup sheet. If the contents of the appropriate list box are cleared, all the data for the appropriate metric will be included in the calculations.

Note: When a campaign is selected in the Campaign list box, only the ad groups that are linked to the selected campaign will be included in the Ad Group list box. Also, if the ad group that is currently included in the list box cell is not linked to the selected campaign, the ad group list box content will be formatted in red to indicate that a new ad group needs to be selected.

There are three sections which can be compared in order to view trends in ad positions - the first section displays the current date range, the second the previous 1 date range and the third the previous 2 date range. The from and to dates for each of these sections need to be specified on the Setup sheet. If you therefore want to change any of these date ranges, you need to do so by editing the dates on the Setup sheet.

Aside from the list boxes at the top of the sheet, the only user input that is required on the Positions sheet is to specify your preferred ad position parameters. The low values in column B be should be left unchanged but the high values in column C can be changed according to requirements.

Note: All the user input cells on the Positions sheet have a yellow cell background. The cells with light blue cell backgrounds contain formulas which should not be replaced otherwise the automated calculations on the sheet may become inaccurate.

The template contains 9 default position ranges but you can add additional rows if necessary by simply inserting new rows anywhere above the last row in the section (the one with a position and a plus sign), copying one of the existing rows (excluding the first and last rows) and entering new high values for the rows that have been added. If you add additional rows to any of the 3 sections, you should also do so in the other two sections.

Note: If you make changes to the high values in column C in any of the three sections, the high values for the affected section should be copied to the other two sections in order to ensure that the calculations are performed based on the same position ranges and therefore remain consistent & comparable.

Note: If any of the high values in column C are highlighted in red, it means that the value is invalid because it is less than the low value which has been included in column B. If you enter a valid value, the red highlighting will be removed automatically.

Note: The high values that are specified in each row are included in the formulas with a less than (<) operator and not a less than or equal to operator (<=). This means that even though the high value may be displayed as say 2.0, it actually means that the row includes all values from say 1.5 to 1.99. The next row will contain the high value of the previous row as the low value and therefore include all values from 2.0 to say 2.49 (if the high value for this row is 2.50).

The purpose of the calculations on the Positions sheet is to identify the ad positions that are the most profitable and which can then be targeted in your AdWords campaign. In order to identify optimal ad positions, we recommend reviewing the click through rate (CTR), cost per click (CPC), number of conversions, conversion rate and the return on investment measurements (if they are all used). The way in which data is reviewed & interpreted may however differ from business to business and depend on for example the way in which conversions are recorded in Google AdWords. You therefore need to formulate & apply an approach which is suitable for your type of business.

If you need guidance on any of the metrics (column headings) that have been included in our calculations, we recommend accessing the Help feature from within Google AdWords in order to do so. The Spread metric is the only metric that is not a typical AdWords metric and is calculated as a volume based indication of the percentage of clicks that fall within a position range.

This is an important metric which when compared between the three sections is also an indication of trends in competition especially when considering the movement in the average cost per click metric. For example, in one month a cost of click of say $5 could have resulted in 50% of clicks coming from ads which are positioned between 1 and 1.5 but in a subsequent month, the same cost per click could have resulted in only 30% of clicks coming from ads in this range with 20% dropping down to the next ad position range. If the most profitable range is the 1 to 1.5 range, this trend would indicate that the cost per click may need to be increased in order to get the same percentage of clicks from within the optimal position range.

Another consideration when determining optimal positions is the volume of conversions. It is a generally accepted principle that the most conversions will occur in the higher ad positions. When reviewing stats like the cost per conversion, the conversion rate and return on investment, it may appear that a lower position may be the most profitable but if the volume in this position is substantially lower than the volume for the positions at the top of a page, it may not be the most profitable position. Volume of conversions is therefore just as important a metric as conversion rate, cost per conversion and return on investment.