This template enables users to identify the search terms which are the most effective for converting customers who are searching for their products or services on Google. The exact match keywords which have been added to the user's Google AdWords account are also indicated and if invoicing is not incorporated into the user's AdWords account, the invoices can be included in the template in order to analyse the profitability of individual search terms. The template results can be used to measure the profitability of individual search terms or phrases and to then adjust keywords on AdWords.
The following sheets are included in the template:
Search - extract search term data from your Google AdWords account and paste onto this sheet. Keywords added as exact match keywords will automatically be indicated and if your invoicing is separate from AdWords, invoice values will also be displayed on the sheet. You can also use the Column Filters to display statistics for phrase match search terms. The columns with light blue column headings contain formulas which are calculated automatically.
Keywords - extract keyword data from your Google AdWords account and paste onto this sheet. Only the exact match keywords need to be retained on this sheet in order for the keywords to be indicated on the Search sheet.
Invoices - Copy your invoice data onto this sheet. Only the search terms and invoice amounts are required but you can add additional columns between the two existing columns if necessary. This sheet is only required if you do not measure invoice values in Google AdWords.
Search Term Data
All search term data needs to be included on this sheet. The easiest way of including your search term 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 Search sheet although you can add columns or move columns so that the sequence between your extract file and the columns on the Search 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 Search sheet in all the columns with yellow column headings. The Search Term column needs to be added to the Row section of the report and all 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 Search 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: The ROI, Keyword Status and Invoice Value columns contain formulas which should not be edited, amended or overwritten otherwise the template may not function as it should.
Note: All the columns on the Search sheet have been included in an Excel table. This feature is extremely useful when entering or copying data in a table format because the formulas in the calculated columns (with light blue column headings) are automatically copied when you add data to the table.
If you already track your invoice amounts in Google Adwords and the invoice amounts are included in the Total Conversion Value report column, you do not need to include invoice amounts in the template. The Invoice Value column (M) on the Search sheet can then be deleted and you can also delete the entire Invoices sheet.
If your invoices are not included in AdWords, you need to include all the appropriate invoices for the same date range as the search term report by adding the invoices to the Invoices sheet. The invoice values that are included on this sheet will then automatically be included in the Invoice Value column (column M) on the Search sheet.
Note: We recommend still including the Total Conversion Value column in your AdWords report although the column will have nil values if you don't track invoice values in AdWords. The nil values in this column can then be replaced by a formula which links to the Invoice Value column in order for the return on investment (ROI) to be calculated correctly. Clear all the nil values in the Total Conversion Value column and enter "=M5" as the formula in the first cell in the column to link the two columns.
The return on investment is calculated by dividing the Total Conversion Value (column H) by the total cost in column E. Any value above 1 means that the search term is profitable and the higher the value, the more profitable. Values below 1 indicate that the cost exceeds the invoice value.
The Keyword Status column indicates whether a search term has been added as an exact match keyword in your AdWords account. The formula in this column determines whether the search term has been included on the Keywords sheet and if included, displays a status of "exact match". If the search term is not found, the status is reflected as "no".
Keywords
One of the core principles of a profitable Google AdWords marketing campaign is to display your ads on the most profitable keywords - or to be more precise - the most profitable search terms. You also do not want to display your ads on search terms which are not profitable. In order to determine whether a search term is profitable, you need to measure invoicing against cost. You also then need to analyse which search terms need to be targeted with exact keyword matching.
The analysis of search term profitability for the purpose of this template is included on the Search sheet but in order to indicate which search terms have been added as exact match keywords, you need to include all exact match keywords in the template. These keywords therefore need to be included on the Keywords sheet.
Note: All the columns on the Keywords 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 which refers to the table is automatically extended when you add data to the table.
The easiest way of including your exact match keywords on the Keywords sheet is by exporting the data from Google Adwords to Excel. We recommend exporting all your enabled keywords and then deleting all your phrase & broad matched keywords before copying the data onto the Keywords sheet. The Match Type column indicates whether a keyword is an exact, phrase or broad match keyword.
Only the exact match keywords should be added to the Keywords sheet and it is only really the Keyword column which is used in the template. You can therefore add or delete any other columns as long as you retain the Keyword column in its current position. We also recommend only retaining enabled keywords on this sheet (Keyword Status of enabled) and therefore not including paused or deleted keywords.
Note: All the exact match keywords that are added to the Keywords sheet are indicated as "exact match" in the Keyword Status column on the Search sheet.
Invoices
As we've mentioned earlier on in these template instructions, the profitability of search terms can only be measured if invoice values are compared to marketing costs. If you do not include invoice values in AdWords, we have provided the Invoice sheet which can be used to include your invoice values and automatically populates the Invoice Values column on the Search sheet.
The invoice values that are included on the Invoices sheet should relate to the same date range as the search term data that is included on the Search sheet. If you include search term data for 6 months, the invoice values should also relate to the same 6 month period otherwise the data will not be comparable and may distort the measurement of profitability by individual search term.
Note: You need to be able to measure invoice values by search term in order to be able to include your invoice values in this template. We recommend using Google Analytics to measure your invoice values by individual search term and exporting the invoice totals per search term for inclusion on the Invoices sheet.
Note: If you already measure your invoice values in Google AdWords, the Invoices sheet is not required and can therefore safely be deleted. You also then need to delete the Invoice Values column on the Search sheet.
Search Term Analysis
Different types of businesses may measure their search term data by using vastly different approaches but we recommend the following general approach when reviewing the search term results that are included on the Search sheet:
- Once you have added all your data to the template, we recommend sorting the Search sheet by conversion (descending), conversion value (descending), cost (descending) and search term (ascending). All your search terms with the most conversions will then be included at the top of the sheet.
- Review the Keyword Status and ROI for the search terms with the highest number of conversions. Consider adding search terms with a keyword status of "no" as exact match keywords to your AdWords marketing campaign but also only if the ROI is higher than a minimum of 1 (or other profitability benchmark which is suitable for your business).
- Look for search terms with a ROI of less than 1 and where the keyword status is "exact match". Consider removing these keywords from your AdWords marketing campaign.
- Look for search terms with a zero ROI and where the Keyword Status is "exact match". These search terms basically resulted in no conversions and you should seriously consider removing these exact match keywords from your marketing campaign especially where significant costs have been incurred.
- Review your search term list for phrases (or even single words) that appear frequently and then use the Column Filter to search for these words or phrases. The Column Filter feature can be selected by clicking the filter selection arrow next to the column heading and the Search feature can be found above the individually listed search terms.
- When you search a word or phrase, the totals above the column headings display results for only the visible cells - this therefore provides an accurate indication of the profitability of targeting the searched word or phrase. Profitable searches can be added as phrase matched keywords and you can consider adding searches that are not profitable as negative keywords.
- Note: When performing filter searches, the sequence in which a phrase is entered is important. For example, if we search for "excel accounting", the search results will only include instances where the two words are found in this order. We therefore may also need to search for "accounting excel" in order to cover the search terms where the order of the words differs.
- Note: After completing all your searches, remember to clear the filter from the Search Term column so that data is not inadvertently copied over a filtered cell range.
- When analysing search terms, we recommend compiling an analysis based on different date ranges. Some keywords may not have been profitable over say a one month period but have been profitable over a three month period. It is therefore more prudent to consider search term statistics over different periods and we therefore recommend completing the search term analysis over a 1 month, 3 month and 12 month period.
Note: Different versions of the file can be saved in order to compile a search term analysis over different periods. If your invoice values are included in Google AdWords, you can also simply copy the Search sheet in order to compile an analysis for a different period because the Invoices sheet is not required. 
