![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture1.png\")
<\/p>\n
Step 1:<\/strong> The first step is to add a formula into cell D2, where we multiply cells B2 & C2:<\/p>\n In the screenshot above, we can see the formula shown\u00a0in cell D2 and see the result in cell D3 for the next row. If you had more than two keywords you would simply copy this formula down for each keyword.<\/p>\n Step 2: <\/strong>Now we need to add up the values for column B and column D. We do this by using a =sum() formula:<\/p>\n Step 3:<\/strong> All that is left to do here is divide the value in cell D4 by the value in cell B4:<\/p>\n In this case we get 2.02 as the answer to the question \u2018what is the average position of the impressions?\u2019<\/p>\n You can apply this to other things like \u2018competition\u2019 of \u2018keyword difficulty\u2019 metrics for keywords. If you have a set of keywords where one of those keywords contains say 80% of the total search volume; when working out the average competition it would be more accurate to weight it by search volume. In this example, we are looking at either the competition of the keywords or the actual searches being performed.<\/p>\n Depending on what your business calls ROI (Return on Investment) or ROAS (Return on Ad Spend), you may be targeted on this or be aiming for a specific ROI. Although you will need to test and measure the effectiveness of this technique, on very large accounts you will often need to a formulaic approach to bidding.<\/p>\n As with almost anything in AdWords there are usually a load of dependant or derived metrics from any one metric. ROI is a great example of this, to calculate ROI, we divide the revenue by the cost, which in AdWords is often the \u2018conversion value\u2019 and ad spend also known as \u2018cost\u2019. It’s important to note that the ROI will not be changed by adjusting the \u2018Clicks\u2019, regardless of the number of clicks the ROI will remain the same. Let\u2019s assume you have an ROI target of 150% and you are achieving an ROI of 180%, meaning you can afford to raise bids where the average position can be improved. How do we calculate this increase in CPC? There are few important things to note before going ahead and applying this to your whole account:<\/p>\n What you will also find is that the higher your average position the lower the returns will be in Clicks when increasing CPC. When your average position is 5 a\u00a0small increase in CPC can sometimes produce a disproportionate increase in traffic. Conversely, when your average position is 1.8, you might need a large increase in CPC to move up a few 10ths of a position.<\/p>\n You need to monitor the changes in ROI as well as profit (revenue – cost). You may find that the increase in Clicks is insignificant but the increase in cost isn\u2019t!<\/p>\n Below, we show two examples; in the first, we see an increase in CPC that results in an increase in profit and a decrease in ROI. This is because the Clicks increased sufficiently to deliver enough conversions to increase profits. CPC increased to 150% but Clicks increased to 200%, resulting in +33% profits.<\/p>\n In the second example (the bottom rowa) we see that the increase in Clicks was only 20%, whereas the increase in CPC was 50%, which resulted in profits reducing by 20%.<\/p>\n Labels are one of the least used, most underrated and often poorly utilized features of AdWords. You can add labels to each keyword, ad, ad group and campaign in order to make your labeling as granular as you like. Moreover, you can add multiple labels to any of those components!<\/p>\n This is incredibly valuable when reporting on or analyzing performance of the account and its constituent parts as it allows for easy segmentation.<\/p>\n There are many reasons you might want to label components that are not already accounted for, such as brand vs non-brand or channel (remarketing, RLSA, Search, Display, Video, etc).<\/p>\n If you have an account with a range of different products or product categories, or indeed a varied range of any offerings, you can label components with those products.<\/p>\n You may also want to label your \u2018head terms\u2019 and your \u2018low value terms\u2019 based on the commercial intent or search volume. In short, there are too many labels to mention, but you should get the idea.<\/p>\n We will do this in Excel with a range of formulas, I will also show you how to do this in a very structured way that means you can simply export new data into a tab, and the analysis\/segmentation will update automatically.<\/p>\n There are 4 tabs in the spreadsheet:<\/p>\n In this case, I have exported the data for one campaign, but you can easily modify the Data tab for any export by adding the required columns. The screenshot below shows how this data is divided into three sections:<\/p>\n Green:<\/strong> AdWords exported data In the close up look at the formula below, what we see is a formula that looks for the character string (cell P1) in the labels column (cell O1). If that formula finds the character string, in this example \u2018brand\u2019, it returns the value \u201cYes\u201d, if it doesn\u2019t find it then it returns \u201cNo\u201d. The dollar sign \u2018$\u2019 simply fixes that aspect of the cell reference, so that when you copy and paste, it does not change.<\/p>\n This formula is replicated across all columns and down all rows, for example:<\/p>\n The data in this tab looks like this:<\/p>\n What we see here is the result of the formulas within the cells containing data, this is all based on the SUMIF or SUMIFS formula. In the example below, we can see how this is structured:<\/p>\n It then follows logically that the \u2018head term\u2019 formulas in the analysis tab look at the \u2018head term\u2019 column (column Q in the above screenshot) in the \u2018data\u2019 tab. Following on from the above example, we can create much more advanced segmentation using multiple labels to look at specific aspects of the data. In the above example, we show you how to do this with one label, but we deliberately used the SUMIFS formula rather than SUMIF, to make this an easier transition. The closeup of the formula bellows should look familiar, as it is the same as the one shown in the previous section of this guide, but with an extra bit on the end:<\/p>\n This \u2018extra bit\u2019 is an additional criterion, which says that we also need to find a \u201cYes\u201d in the corresponding cell in column V of the data tab. Column V (see below) is the \u2018search\u2019 label column:<\/p>\n This formula is essentially saying that we need a \u201cYes\u201d in cells in both columns R and V in order to sum the cells in column F: which in this case is the number of clicks that keywords with both the labels \u2018product a\u2019 and \u2018search\u2019 have received. If you\u2019re familiar with AdWords and you\u2019re looking to learn some techniques that can help you to more fully\u00a0analyze your PPC data, this is the guide for you!<\/p>\n","protected":false},"author":5,"featured_media":367,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"off","_et_pb_old_content":"\n\t\t\t\tIf you\u2019re familiar with AdWords and you\u2019re looking to learn some techniques that can help you to more fully\u00a0analyze your PPC data, this is the guide for you!\n\n\n\nOne of the many advantages that Paid Advertising provides in general is the data. Compared to SEO data, PPC data is often more accurate, larger in quantity and more complete. However, as a consequence, you may find yourself in \u2018analysis paralysis\u2019, where you have so much data that you don\u2019t know what to look at.\n\nMy experience over the last ten years in Search has provided me with a set of analytical tools that have proved invaluable when addressing a problem or question. The more tricks and tools you have in your analytical arsenal the easier you will find it to address your data.\n\nIn this guide you will learn a few simple tricks and techniques to help you analyze your PPC data.\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture2.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture3.png\")
\nWe have copied this formula to cell D4 in the above example. This looks like the following without the formulas:<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture4.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture5.png\")
<\/p>\n3. ROI \/ ROAS Bidding Formula<\/h2>\n
ROI<\/h3>\n
\nCost is derived from the cost per clicks (CPCs) of your clicks and revenue is derived from the value of the conversions received. Therefore, broadly speaking, the higher the CPC the lower the ROI (if we assume all other variables stay the same). In the table below, we show an example of increasing CPC that results in increased traffic from improved position:<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture6.png\")
<\/p>\n
\nNow, in reality, there are other variables like the higher your position often your conversion rate may increase, which will need to be analyzed and considered when using formulaic bidding methods.<\/p>\nExample<\/h3>\n
\nActual ROI \/ Target ROI
\n1.8 \/ 1.5 = 1.2
\nThis means that we need to multiply the current bid by 1.2 (or add 20%) in order to bring ROI down to target. The formula for this as a whole is as follows:
\n(Actual ROI \/ Target ROI) x Current Bid = New Bid (on ROI target)<\/p>\nThings to Note<\/h3>\n
\n
![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture7.png\")
<\/p>\n4. Labeling and Excel<\/h2>\n
Things you need to know:<\/h3>\n
\n
Segmentation<\/h3>\n
\n
![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture8.png\")
<\/p>\n
\nBlue<\/strong>: Labels
\nYellow:<\/strong> Formulas (and stuff added to but based on the exported AdWords data)<\/p>\nTab: Data<\/h4>\n
![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture9.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture10.png\")
<\/p>\nTab: Analysis<\/h4>\n
![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2020\/08\/Picture11.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/Picture12.png\")
\nWhat this formula is saying is; sum any cells in the range of data!F:F where the corresponding cell in the range data!$P:$P contain the word \u201cYes\u201d. The word \u2018data\u2019 in the formula is a reference to the \u2018data\u2019 tab, because these formulas look at the data tab values described in the above section.
\nIt is important to note that the \u2018brand\u2019 label formulas look at the brand column for the word \u201cYes\u201d in the data tab:<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/13.png\")
<\/p>\n
\nIt should also follow logically that the Clicks column sums data from the \u2018clicks\u2019 column in the \u2018data\u2019 tab and so on.<\/p>\n5. Advanced Segmentation (Labels)<\/h2>\n
\nTab: Advanced
\nThe screenshot of the \u2018advanced\u2019 tab shows that we have zeroed in on the product labels, but we have segmented them by the \u2018search\u2019 label and the \u2018rlsa\u2019 label in the bottom two tables. The top table shows the total for the product labels in the same way already described above.<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/14.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/15.png\")
<\/p>\n![\"\"](\"https:\/\/datatrue.com\/wp-content\/uploads\/2021\/08\/16.png\")
<\/p>\n
\nYou can keep adding criteria to these formulas if you want to segment even further. You can use filters in AdWords to look at custom combinations of labels and you can even save those filter.
\nBut setting it up in Excel like this means that you can just copy and paste the data for the whole account into the sheet and you get to see all of the tables and charts needed to report on or analyze the data. This is easy to send out to people and can be done offline rather than loading web pages.<\/p>\n","protected":false},"excerpt":{"rendered":"1. Excel is Your Friend!<\/h2>\nI cannot understate how important it is to get a firm grip on the formulas and functionality of Excel. Unless you are a programmer of some kind, Excel should be your best friend when considering PPC data. All the following tricks will use Excel, so by the end of this guide you should have some really useful formulas that you can start to think about applying to other tasks.\n
2. Weighting Data for Averages<\/h2>\nThis is something I use a lot, not just in PPC but in a range of data analysis. This is best explained with an example; if we assume you have a set of keywords and you want to know the average position for them, there are two ways you can address this:\n
\n \t
![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture1.png\")
\n\nStep 1:<\/strong> The first step is to add a formula into cell D2, where we multiply cells B2 & C2:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture2.png\")
\n\nIn the screenshot above, we can see the formula shown\u00a0in cell D2 and see the result in cell D3 for the next row. If you had more than two keywords you would simply copy this formula down for each keyword.\n\nStep 2: <\/strong>Now we need to add up the values for column B and column D. We do this by using a =sum() formula:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture3.png\")
\nWe have copied this formula to cell D4 in the above example. This looks like the following without the formulas:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture4.png\")
\n\nStep 3:<\/strong> All that is left to do here is divide the value in cell D4 by the value in cell B4:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture5.png\")
\n\nIn this case we get 2.02 as the answer to the question \u2018what is the average position of the impressions?\u2019\n\nYou can apply this to other things like \u2018competition\u2019 of \u2018keyword difficulty\u2019 metrics for keywords. If you have a set of keywords where one of those keywords contains say 80% of the total search volume; when working out the average competition it would be more accurate to weight it by search volume. In this example, we are looking at either the competition of the keywords or the actual searches being performed.\n3. ROI \/ ROAS Bidding Formula<\/h2>\nDepending on what your business calls ROI (Return on Investment) or ROAS (Return on Ad Spend), you may be targeted on this or be aiming for a specific ROI. Although you will need to test and measure the effectiveness of this technique, on very large accounts you will often need to a formulaic approach to bidding.\n
ROI<\/h3>\nAs with almost anything in AdWords there are usually a load of dependant or derived metrics from any one metric. ROI is a great example of this, to calculate ROI, we divide the revenue by the cost, which in AdWords is often the \u2018conversion value\u2019 and ad spend also known as \u2018cost\u2019.\nCost is derived from the cost per clicks (CPCs) of your clicks and revenue is derived from the value of the conversions received. Therefore, broadly speaking, the higher the CPC the lower the ROI (if we assume all other variables stay the same). In the table below, we show an example of increasing CPC that results in increased traffic from improved position:\n\n
![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture6.png\")
\n\nIt's important to note that the ROI will not be changed by adjusting the \u2018Clicks\u2019, regardless of the number of clicks the ROI will remain the same.\nNow, in reality, there are other variables like the higher your position often your conversion rate may increase, which will need to be analyzed and considered when using formulaic bidding methods.\nExample<\/h3>\nLet\u2019s assume you have an ROI target of 150% and you are achieving an ROI of 180%, meaning you can afford to raise bids where the average position can be improved. How do we calculate this increase in CPC?\nActual ROI \/ Target ROI\n1.8 \/ 1.5 = 1.2\nThis means that we need to multiply the current bid by 1.2 (or add 20%) in order to bring ROI down to target. The formula for this as a whole is as follows:\n(Actual ROI \/ Target ROI) x Current Bid = New Bid (on ROI target)\n
Things to Note<\/h3>\nThere are few important things to note before going ahead and applying this to your whole account:\n
\n \t
![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture7.png\")
\n\nIn the second example (the bottom rowa) we see that the increase in Clicks was only 20%, whereas the increase in CPC was 50%, which resulted in profits reducing by 20%.\n4. Labeling and Excel<\/h2>\nLabels are one of the least used, most underrated and often poorly utilized features of AdWords. You can add labels to each keyword, ad, ad group and campaign in order to make your labeling as granular as you like. Moreover, you can add multiple labels to any of those components!\n\nThis is incredibly valuable when reporting on or analyzing performance of the account and its constituent parts as it allows for easy segmentation.\n\nThere are many reasons you might want to label components that are not already accounted for, such as brand vs non-brand or channel (remarketing, RLSA, Search, Display, Video, etc).\n\nIf you have an account with a range of different products or product categories, or indeed a varied range of any offerings, you can label components with those products.\n\nYou may also want to label your \u2018head terms\u2019 and your \u2018low value terms\u2019 based on the commercial intent or search volume. In short, there are too many labels to mention, but you should get the idea.\n
Things you need to know:<\/h3>\n
![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture8.png\")
\n\nGreen:<\/strong> AdWords exported data\nBlue<\/strong>: Labels\nYellow:<\/strong> Formulas (and stuff added to but based on the exported AdWords data)\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture9.png\")
\n\nThis formula is replicated across all columns and down all rows, for example:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture10.png\")
\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture11.png\")
\n\nWhat we see here is the result of the formulas within the cells containing data, this is all based on the SUMIF or SUMIFS formula. In the example below, we can see how this is structured:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/Picture12.png\")
\nWhat this formula is saying is; sum any cells in the range of data!F:F where the corresponding cell in the range data!$P:$P contain the word \u201cYes\u201d. The word \u2018data\u2019 in the formula is a reference to the \u2018data\u2019 tab, because these formulas look at the data tab values described in the above section.\nIt is important to note that the \u2018brand\u2019 label formulas look at the brand column for the word \u201cYes\u201d in the data tab:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/13.png\")
\n\nIt then follows logically that the \u2018head term\u2019 formulas in the analysis tab look at the \u2018head term\u2019 column (column Q in the above screenshot) in the \u2018data\u2019 tab.\nIt should also follow logically that the Clicks column sums data from the \u2018clicks\u2019 column in the \u2018data\u2019 tab and so on.\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/14.png\")
\n\nThe closeup of the formula bellows should look familiar, as it is the same as the one shown in the previous section of this guide, but with an extra bit on the end:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/15.png\")
\n\nThis \u2018extra bit\u2019 is an additional criterion, which says that we also need to find a \u201cYes\u201d in the corresponding cell in column V of the data tab. Column V (see below) is the \u2018search\u2019 label column:\n\n![\"\"](\"https:\/\/blog.datatrue.com\/wp-content\/uploads\/2018\/01\/16.png\")
\n\nThis formula is essentially saying that we need a \u201cYes\u201d in cells in both columns R and V in order to sum the cells in column F: which in this case is the number of clicks that keywords with both the labels \u2018product a\u2019 and \u2018search\u2019 have received.\nYou can keep adding criteria to these formulas if you want to segment even further. You can use filters in AdWords to look at custom combinations of labels and you can even save those filter.\nBut setting it up in Excel like this means that you can just copy and paste the data for the whole account into the sheet and you get to see all of the tables and charts needed to report on or analyze the data. This is easy to send out to people and can be done offline rather than loading web pages.\t\t","_et_gb_content_width":"","footnotes":""},"categories":[24,21],"tags":[22,23,25,26],"class_list":["post-155","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-guide","category-marketing-analytics","tag-adwords-data","tag-analytics","tag-ppc-analytics","tag-sem-data"],"yoast_head":"\n