This template enables users to analyse their monthly Google Ads data on an overall, campaign, ad group and device level. The template can be used for any Ads account by adding all the relevant campaigns & ad group names to the template and copying the monthly Ads data from an Excel extract of their Ads account. The template includes a lot of automated calculations & charts which make it easy to spot trends in the data.
The following sheets are included in the template:
Set-up - enter a report date & maintain the list of devices. The report date determines the reporting periods which are included on the other sheets. You can therefore compile the reports on all the sheets in this template for any date range by simply changing the date at the top of this sheet. Devices can be added to the list boxes on the other sheets by inserting them in a new row above the "End of list" item.
Data - create a report by using the Reports feature in Google Ads 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 campaign & ad group data on this sheet is automatically updated in the calculations & charts on the other sheets in this template.
Campaigns - include all your Ads campaigns on this sheet by adding the campaign name in column A. All the calculations in the columns with light blue column headings are automated. You can select a device at the top of the sheet and select the values to display (cost, clicks, cost per click, click through rate, conversions, conversion rates, revenue, return on investment & more). The data on this sheet provides a monthly analysis of Ads campaign data for a 12-month period based on the report date which is entered on the Setup sheet.
AdGroups - include all your Ads ad groups in column A and link the ad groups to campaigns in column B. All the calculations in the columns with light blue column headings are automated. You can select a device at the top of the sheet and select the values to display (cost, clicks, cost per click, click through rate, conversions, conversion rates, revenue, return on investment & more). The data on this sheet provides a monthly analysis of Ads ad group data for a 12-month period based on the report date which is entered on the Setup sheet.
CamCharts - select a campaign & device at the top of the sheet in order to analyse your Ads campaign data. You can also clear these input cells in order to view all campaign or device data. All the calculations & charts on this sheet are fully automated. The campaigns which are included in the list box at the top of the sheet are based on the contents of the Campaigns sheet and the dates are based on the report date which is specified on the Setup sheet.
AgCharts - select an ad group & device at the top of the sheet in order to analyse your Ads ad group data. Only the ad groups that are associated with the campaign which is currently selected on the CamCharts sheet will be available for selection. You can also clear these input cells in order to view all ad group or device data (for the selected campaign). All the calculations & charts on this sheet are fully automated. The ad groups which are included in the list box at the top of the sheet are based on the contents of the AdGroups sheet and the campaign which has been selected on the CamCharts sheet. The dates are based on the report date which is specified on the Setup sheet.
Set-up
All the date ranges which are included on the reports in this template are based on the Report Date specified in cell B4 on the Setup sheet. The date entered here represents the end date of the date range whether it is a 12-month or other period of analysis.
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 in the list will be included automatically in the device selection cells on the other sheets.
Data
All monthly AdWords data needs to be included on this sheet. The easiest way of including your monthly data is by creating the appropriate report through the Google Ads 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 Google Ads fields which you need to add to your report will have the exact same field names as the column headings included on the Data sheet. The Month, 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 Ads 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.
Campaigns
All the campaign names that form part of your Google Ads account need to be included in column A on the Campaigns sheet. The easiest way of adding all the campaign names is by extracting the data from Ads and copying the names into column A. This is the only user input data that needs to be added to the sheet - all the columns with light blue column headings contain formulas which are automatically calculated.
Note: All the columns on the Campaigns sheet have been included in an Excel table. The table feature is extremely useful when entering or copying data in a table format because the calculated columns (columns with light blue column headings) are 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.
New campaign names can therefore be added to the Campaigns sheet by simply entering or copying the new campaign names into the first blank cell in column A. The Excel table will then automatically extend to include the new campaign name and the formulas in all the other columns will be included in the new row automatically.
The totals for the selected metric (the values selected from the list box at the top of the sheet) & device are included in row 4 and the reporting periods included in row 5 are based on the report date entered on the Setup sheet.
Note: If you want to extend the default 12-month reporting period, you can do so by copying the formulas from the last column - this includes the formulas which have been added to the Excel table as well as the formulas in the two rows above the column heading (totals and months). You also then need to add a new column heading.
You can select any of a number of value metrics to be displayed on the Campaigns sheet. These include clicks, impressions, click through rates (CTR), costs, cost per click (CPC), conversions, cost per conversion, conversion rates, revenue and return on investment (ROI). The appropriate metrics can be selected from the list box in cell L2 at the top of the sheet. If the contents of the list box are deleted, the calculations default to display costs.
You can also view the calculations for the selected metric for all devices or a selected device. The device can be selected from the list box in cell J2 and the contents of this list box can be cleared in order to display campaign data for all devices. The device list which is included in the list box can be maintained on the Setup sheet.
When you add new campaigns to your Ads account, the campaign names need to be added to the Campaigns sheet in order for the overall calculations to be accurate. If you neglect to add all the new campaign names to the sheet, the overall Ads data will not include all campaigns and therefore not be accurate.
We have therefore built in a completeness check which compares the overall costs on the Campaigns sheet to the calculated overall costs of all the campaigns added to the Data sheet. If the costs do not match, it means that there is a campaign name which has not been added to the Campaigns sheet. This calculation is not displayed on the sheet but has been added as a conditional formatting formula.
Note: If the completeness check reveals that there is a campaign name missing, the campaign name heading in cell A6 will be highlighted in red. This indicates that there is a campaign name missing and that you need to review the Data sheet to determine which campaign name has not been added to the Campaigns sheet. Once you add the campaign name(s), the red highlighting will be removed automatically.
We have also used conditional formatting rules to format the data on the Campaigns sheet with the appropriate number formatting. This means that where a metric is displayed which is a percentage, the values on the Campaigns sheet will automatically be displayed as percentages.
Note: If some rows are displayed as percentages and some as decimal values, it indicates that the conditional formatting rules are not correct. You then need to open the Conditional Formatting - Manage Rules feature from the Home tab on the ribbon and review the cell selections. The rules which relate to the data below the column headings need to be applied to all the rows that contain data. You therefore may need to extend the cell range to include all the rows.
Analysing Campaign Data
The data on the Campaigns sheet enables users to compare monthly Google Ads statistics for all campaigns by selecting the appropriate values that you want to compare from the list box at the top of the sheet. You can also view statistics for all devices by clearing the device list box or view statistics for a specific device by selecting the appropriate device from the list box.
If you then want to analyse the data for a specific campaign in more detail, you can go to the CamCharts sheet and select the appropriate campaign from the list box at the top of the sheet. If you want to analyse data for all campaigns, simply clear the Campaign list box.
Note: The 14-month reporting period which is included on the sheet is based on the report date specified on the Setup sheet. If you want to change this date range, all you need to do is enter a new date on the Setup sheet.
Note: If you want to extend the default 14-month period, we recommend adding the appropriate number of rows in the middle of the current data section (say in row 10). Then copy all the formulas from row 6 and paste them into the rows from row 7 and up to the row above the total row. This will ensure that all the formulas are added correctly and that you do not need to extend the chart source data ranges manually.
The CamCharts sheet also contains a device list box at the top of the sheet which can be used to analyse Ads data for a selected device. If this list box is cleared, the campaign data for all devices will be displayed.
Note: Aside from the list box selections at the top of the sheet, the CamCharts sheet requires no user input - all the calculations on this sheet as well as all the charts which have been included below the calculations are fully automated.
Note: The column headings in row 4 on the CamCharts sheet are used as the names of the value metrics on the other sheets in this template and should therefore not be changed. If you change any of these column headings, the affected cell will be highlighted in red in order to remind you that the column headings cannot be changed. Simply undo your changes in order to have the red highlighting removed automatically.
Ad Groups
All the ad group names that form part of your Google Ads account need to be included in column A on the AdGroups sheet and each ad group needs to be linked to a campaign by including the appropriate campaign name in column B. The easiest way of adding all the ad group & campaign names is by extracting the data from Ads and copying the data into columns A & B. This is the only user input data that needs to be added to the sheet - all the columns with light blue column headings contain formulas which are automatically calculated.
Note: All the columns on the AdGroups sheet have been included in an Excel table. The table feature is extremely useful when entering or copying data in a table format because the calculated columns (columns with light blue column headings) are 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 Excel table.
New ad groups can therefore be added to the AdGroups sheet by simply entering or copying the new ad groups into the first blank cell in column A. The Excel table will then automatically extend to include the new ad groups and the formulas in all the other columns will be included in the new row automatically.
The totals for the selected metric (the values that are selected from the list box at the top of the sheet) & device are included in row 4 and the reporting periods which are included in row 5 are based on the report date entered on the Setup sheet.
Note: If you want to extend the default 12-month reporting period, you can do so by copying the formulas from the last column - this includes the formulas which have been added to the Excel table as well as the formulas in the two rows above the column heading (totals and months). You also then need to add a new column heading.
You can select any of a number of value metrics to be displayed on the AdGroups sheet. These include clicks, impressions, click through rates (CTR), costs, cost per click (CPC), conversions, cost per conversion, conversion rates, revenue and return on investment (ROI). The appropriate metrics can be selected from the list box in cell K2 at the top of the sheet. If the contents of the list box are deleted, the calculations default to display costs.
You can also view the calculations for the selected metric for all devices or a selected device. The device can be selected from the list box in cell I2 and the contents of this list box can be cleared in order to display ad group data for all devices. The device list which is included in the list box can be maintained on the Setup sheet.
When you add new ad groups to your Ads account, the ad group names need to be added to the AdGroups sheet in order for the overall calculations to be accurate. If you neglect to add all the new ad group names to the sheet, the overall Ads data will not include all ad groups and therefore not be accurate.
We have therefore built in a completeness check which compares the overall costs on the AdGroups sheet to the calculated overall costs of all the ad groups that have been added to the Data sheet. If the costs do not match, it means that there is an ad group name which has not been added to the AdGroups sheet. This calculation is not displayed on the sheet but has been added as a conditional formatting formula.
Note: If the above completeness check reveals that there is an ad group name missing, the ad group & campaign name headings in cells A6 & B6 will be highlighted in red. This indicates that there is an ad group name missing and that you need to review the Data sheet to determine which ad group name has not been added to the AdGroups sheet. Once you add the appropriate ad group name(s), the red highlighting will be removed automatically.
We have also used conditional formatting rules to format the data on the AdGroups sheet with the appropriate number formatting. This means that where a metric is displayed which is a percentage, the values on the AdGroups sheet will automatically be displayed as percentages.
Note: If some rows are displayed as percentages and some as decimal values, it indicates that the conditional formatting rules are not correct. You then need to open the Conditional Formatting - Manage Rules feature from the Home tab on the ribbon and review the cell selections. The rules that relate to the data below the column headings need to be applied to all the rows that contain data. You therefore may need to extend the cell range to include all the rows.
Analysing Ad Group Data
The data on the AdGroups sheet enables users to compare monthly AdWords statistics for all ad groups by selecting the appropriate values that you want to compare from the list box at the top of the sheet. You can also view statistics for all devices by clearing the device list box or view statistics for a specific device by selecting the appropriate device from the list box.
If you then want to analyse the data for a specific ad group in more detail, you can go to the AgCharts sheet and select the appropriate ad group from the list box at the top of the sheet. If you want to analyse data for all ad groups linked to the selected campaign, simply clear the Ad Groups list box.
Note: The campaign name which is reflected at the top of the AgCharts sheet is based on the campaign which has been selected on the CamCharts sheet. Only the ad groups which have been linked to this campaign on the AdGroups sheet will be available for selection from the Ad Groups list box. If the selected campaign is indicated with an asterisk (*), it means that no campaign has been selected on the CamCharts sheet and that all ad groups will therefore be available for selection.
Note: We recommend reviewing campaign data on the CamCharts sheet first and to then drill down in more detail by selecting the appropriate campaign on the CamCharts sheet before opening the AgCharts sheet to review ad group data. Start by clearing the Ad Group list box at the top of the sheet to display data for the entire campaign and then select the available ad groups one at a time in order to view the individual ad group data.
Note: If the Ad Group list box at the top of the sheet is highlighted in red, it means that the currently selected ad group is not linked to the active campaign (the campaign that is currently selected on the CamCharts sheet). You can simply delete the ad group in order to remove the red highlighting and to display all ad group data for the active campaign before selecting the linked ad groups individually from the list box.
Note: The 14-month reporting period which is included on the sheet is based on the report date which is specified on the Setup sheet. If you want to change this date range, all you need to do is enter a new date on the Setup sheet.
Note: If you want to extend the default 14-month period, we recommend adding the appropriate number of rows in the middle of the current data section (say in row 10). Then copy all the formulas from row 6 and paste them into the rows from row 7 and up to the row above the total row. This will ensure that all the formulas are added correctly and that you do not need to extend the chart source data ranges manually.
The AgCharts sheet also contains a device list box at the top of the sheet which can be used to analyse AdWords data for a selected device. If this list box is cleared, the ad group data for all devices will be displayed.
Note: Aside from the list box selections at the top of the sheet, the AgCharts sheet requires no user input - all the calculations on this sheet as well as all the charts that have been included below the calculations are fully automated.
Note: The column headings in row 4 on the AgCharts sheet are used as the names of the value metrics on the other sheets in this template and should therefore not be changed. If you change any of these column headings, the affected cell will be highlighted in red in order to remind you that the column headings cannot be changed. Simply undo your changes in order to have the red highlighting removed automatically.
