{"id":5706,"date":"2022-08-24T13:19:06","date_gmt":"2022-08-24T13:19:06","guid":{"rendered":"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/"},"modified":"2022-08-24T13:19:06","modified_gmt":"2022-08-24T13:19:06","slug":"7-useful-excel-formulas-and-functions-for-ppc","status":"publish","type":"post","link":"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/","title":{"rendered":"7 useful Excel formulas and functions for PPC"},"content":{"rendered":"<p>My approach to PPC is analytical. I realized early in my career that if I focused on developing my ability to manipulate and interpret performance data, the optimizations become obvious.<\/p>\n<p>Leaning into Excel skills can help you become a more efficient worker and improve the quality of your analyses.<\/p>\n<p>One of my favorite parts about Excel is that I continuously find ways to be more efficient.\u00a0Several functions and formulas can help expedite PPC tasks.<\/p>\n<p>What follows are seven Excel tips to help you quickly identify high-impact PPC optimizations that will move the needle for your brand or client.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_81 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Excel_101_Foundational_formulas\" >Excel 101: Foundational formulas<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Delta\" >Delta<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Incremental_impact\" >Incremental impact<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Concatenate\" >Concatenate<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Excel_201_Preparing_datasets_for_analysis\" >Excel 201: Preparing datasets for analysis<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#VLOOKUP\" >VLOOKUP<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Transform_date_to_week\" >Transform date to week<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Excel_301_Tools_for_large-scale_datasets\" >Excel 301: Tools for large-scale datasets<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Categorization_based_on_text\" >Categorization based on text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Joining_complex_datasets\" >Joining complex datasets<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/craighordlow.com\/index.php\/2022\/08\/24\/7-useful-excel-formulas-and-functions-for-ppc\/#Happy_number_crunching\" >Happy number crunching!<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Excel_101_Foundational_formulas\"><\/span>Excel 101: Foundational formulas<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>These are the Excel skills I first introduce to new hires who are expected to take on <a href=\"https:\/\/searchengineland.com\/guide\/ppc\/tracking-and-measurement\" target=\"_blank\" rel=\"noopener\">performance reporting<\/a> and <a href=\"https:\/\/searchengineland.com\/ppc-forecasting-free-modeling-tools-excel-378676\" target=\"_blank\" rel=\"noopener\">bulk sheet builds<\/a>.\u00a0<\/p>\n<p>If you find yourself in a similar role or feel that those tasks are cumbersome, try incorporating the following into your process.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Delta\"><\/span>Delta<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"800\" height=\"450\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Deltas-1-800x450.png\" alt=\"Delta formula and use cases.\" class=\"wp-image-387431\" title=\"| Craig Hordlow\"><\/figure>\n<p>Simply put, a delta is a rate of change. For performance marketers, that is half the battle associated with our jobs. We need to have an acute understanding of the changes we see to develop effective optimizations.\u00a0<\/p>\n<p>Notice how large the Click Conversion Delta is in the example calculation above. Nominally, a drop from 5% to 3% could be overlooked. However, using the delta calculation, it becomes clear that click conversion needs to improve, to enhance the campaign CPA.\u00a0<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Incremental_impact\"><\/span>Incremental impact<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img decoding=\"async\" width=\"800\" height=\"448\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Incremental-impact-800x448.png\" alt=\"Incremental impact formula and use cases.\" class=\"wp-image-387432\" title=\"| Craig Hordlow\"><\/figure>\n<\/div>\n<p>The insights received from the incremental impact calculation will align with the insights from the performance deltas \u2013 big deltas will have big incremental impacts. Don\u2019t expect to get any new insights when using these formulas in conjunction.<\/p>\n<p>This formula is handy when developing a performance narrative. It helps answer the \u201cso what\u201d question that all stakeholders understand. Incorporate callouts such as <em>\u201can X% drop in CTR results in Y fewer clicks\u201d <\/em>or <em>\u201can X% increase in CPCs drove $Y more spend\u201d <\/em>to add more color to the narrative.\u00a0<\/p>\n<p>One important caveat is that this formula assumes that all else is constant. As marketers, we know that these metrics are all interconnected. <\/p>\n<p>If impressions increase by 50%, CTR will likely decrease. Use deltas first to understand performance changes comprehensively, and only leverage this calculation when it makes logical sense.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Concatenate\"><\/span>Concatenate<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img decoding=\"async\" width=\"800\" height=\"450\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Concatenate-800x450.png\" alt=\"CONCATENATE function and use cases.\" class=\"wp-image-387433\" title=\"| Craig Hordlow\"><\/figure>\n<\/div>\n<p>In terms of impact on day-to-day tasks, if CONCATENATE is not a part of your process when creating bulk sheets, this likely will be the most impactful takeaway from this article. This function allows users to combine the contents of multiple cells together in a single text string. Think of it like a glue that can be used to connect cells.\u00a0<\/p>\n<p>The applications of this function are far-reaching. One important note \u2013 the function is not limited to cells. Text\/characters can be incorporated as fields within the formula using quotation marks around the text. Example 2 illustrates the incorporation of text into a CONCATENATE formula.\u00a0<\/p>\n<hr class=\"wp-block-separator has-text-color has-background has-cyan-bluish-gray-background-color has-cyan-bluish-gray-color\">\n<p><!-- START INLINE FORM --><\/p>\n<div class=\"nl-inline-form border py-2 px-1 my-2\">\n<div class=\"row align-items-center\">\n<div class=\"col-12 col-lg-3 col-xl-4 pe-md-0 pb-2 pb-lg-0\">\n<p class=\"inline-form-text text-center mb-0\">Get the daily newsletter search marketers rely on.<\/p>\n<\/p><\/div>\n<div class=\"col-12 col-lg-7 col-xl-6  pe-lg-0\">\n<div class=\"form-nl-inline\">\n<div class=\"row g-0\">\n<div class=\"form-group col-12 col-lg-8 mb-0\"><\/div>\n<div class=\"col-12 col-lg-4 col-xl-4 mb-0\">\n<div class=\"d-none\">\n<p>Processing&#8230;Please wait.<\/p>\n<\/div>\n<p>\t\t\t\t\t\t\t<button class=\"btn btn-dark inlineButton w-100 rounded-0 font-weight-bold\" type=\"submit\" name=\"submit_button\">SUBSCRIBE<\/button>\n\t\t\t\t\t\t<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div class=\"col-12 col-lg-2 col-xl-2 ps-1\">\n<p class=\"text-center mb-0\"><a class=\"nl-terms\" href=\"https:\/\/searchengineland.com\/termsofuse\" target=\"_blank\" rel=\"noopener noreferrer\">See terms.<\/a><\/p>\n<\/p><\/div>\n<\/p><\/div>\n<\/div>\n<p><!-- END INLINE FORM --><\/p>\n<hr class=\"wp-block-separator has-text-color has-background has-cyan-bluish-gray-background-color has-cyan-bluish-gray-color\">\n<h2><span class=\"ez-toc-section\" id=\"Excel_201_Preparing_datasets_for_analysis\"><\/span>Excel 201: Preparing datasets for analysis<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Pivot tables are a PPC marketer\u2019s best friend. However, to best leverage pivot tables, you must invest time to ensure the integrity and granularity of the dataset. <\/p>\n<p>The following functions\/formulas included here are most impactful when preparing a dataset for analysis, but the applications are far-reaching.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"VLOOKUP\"><\/span>VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"446\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/VLOOKUP-800x446.png\" alt=\"VLOOKUP function and use cases.\" class=\"wp-image-387434\" title=\"| Craig Hordlow\"><\/figure>\n<\/div>\n<p>Some may argue that this is the most useful function for search marketers, and I would not put up a huge fight. VLOOKUP is a critical function for search marketers to master, as it\u2019s common that datasets need to be augmented or modified with data from other platforms. <\/p>\n<p>If \u201csource of truth\u201d reporting sits outside of the PPC platforms, you are likely familiar with the challenges of combining datasets.\u00a0In addition, VLOOKUP is an easy way to add macro-level filters to a granular dataset. <\/p>\n<p>A best practice I recommend is pulling data at the most granular level possible and using VLOOKUP to add filters easily. Start your analysis at higher levels and drill down to the biggest drivers of a change.\u00a0<\/p>\n<p>For example, if stakeholders ask for a breakdown of geographic performance, pull at the city level, build a table associating the cities to states and regions, and add two columns for State and Region to the dataset using VLOOKUP.\u00a0<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Transform_date_to_week\"><\/span>Transform date to week<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"450\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Transform-date-to-week-800x450.png\" alt=\"Transform date to week formula and use cases.\" class=\"wp-image-387435\" title=\"| Craig Hordlow\"><\/figure>\n<\/div>\n<p>This IF statement is particularly helpful for PPC because weekly reporting options can be limited when pulling reports directly from the platform. <\/p>\n<p>For brands or clients that do not have a traditional Monday to Sunday schedule, pull data at the day\/date level. Then add a column to the dataset for Week using this formula.<\/p>\n<p>Note that standard settings in Excel indicate 1=Sunday, 7=Saturday. In Example 2, I have used Tuesday as the start of the week. <\/p>\n<p>Note that the day of week indicator needs to be updated in both the IF statement logic, as well as the returned value if false.\u00a0<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Excel_301_Tools_for_large-scale_datasets\"><\/span>Excel 301: Tools for large-scale datasets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Categorization_based_on_text\"><\/span>Categorization based on text<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"444\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Categorization-based-on-text-800x444.png\" alt=\"Categorization based on text formula and use cases.\" class=\"wp-image-387436\" title=\"| Craig Hordlow\"><\/figure>\n<p>While it might be complex at first glance, this formula is very practical for search marketers. It allows users to search a cell of text for a particular word or phrase.\u00a0<\/p>\n<p>If the cell meets the criteria, the formula will return text inputted in the \u2018text if true\u2019 field. Based on the previous formula we covered, I bet you can guess what happens if it does not match.<\/p>\n<p>The formula has many applications within search marketing. However, categorization is rarely binary \u2013 meaning you are not categorizing entities into one of 2 options.<\/p>\n<p>Example 2 shows how multiple logical tests can be stacked on each other. This is referred to as a nested IF statement. To create a nested IF statement, you repeat the IF statement logic in the \u2018text if false\u2019 field with different search criteria.<\/p>\n<p>Essentially, this tells Excel to search for the first phrase\/text. If it does not find it, search for the second phrase\/text. Using nested IF statements, advertisers can quickly categorize data at scale.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Joining_complex_datasets\"><\/span>Joining complex datasets<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"450\" src=\"https:\/\/searchengineland.com\/wp-content\/seloads\/2022\/08\/Joining-complex-datasets-800x450.png\" alt=\"Joining complex datasets process and use cases.\" class=\"wp-image-387437\" title=\"| Craig Hordlow\"><\/figure>\n<\/div>\n<p>Finally, I want to highlight how the techniques described previously can be leveraged collectively to enhance the quality of analysis. <\/p>\n<p>If you\u2019re following the best practice of pulling platform data at the most granular levels, it\u2019s common for data to contain multiple segmentations (a.k.a., attribute columns in the dataset).\u00a0<\/p>\n<p>When combining data between two platforms, the datasets must have the same level of granularity. Otherwise, data that you pull into your original dataset will be inaccurate.\u00a0<\/p>\n<p>To join complex datasets, use CONCATENATE to create an additional column that joins together all segmentations in the original dataset. This should be replicated in the second dataset, using the same order in the concatenation. See Step 1 for an example.\u00a0<\/p>\n<p>The newly formed column now serves as the connector between the two datasets. Using the VLOOKUP function, search for this newly created column in the second dataset and designate which column of data you\u2019d like to append to dataset #1. Double check the VLOOKUP worked correctly by comparing the sum of the newly imported data to the original table it was imported from.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Happy_number_crunching\"><\/span>Happy number crunching!<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>There\u2019s a lot of information to digest here. Yet, we\u2019ve only scratched the tip of the iceberg here. <\/p>\n<p>Don\u2019t give up if you\u2019re struggling with Excel. It takes time to learn the functions and their capabilities. Search for some videos that walk you through live examples \u2013 those were particularly effective for me.\u00a0<\/p>\n<p>The post <a rel=\"nofollow noopener\" href=\"https:\/\/searchengineland.com\/excel-formulas-functions-ppc-387428\" target=\"_blank\">7 useful Excel formulas and functions for PPC<\/a> appeared first on <a rel=\"nofollow noopener\" href=\"https:\/\/searchengineland.com\/\" target=\"_blank\">Search Engine Land<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>My approach to PPC is analytical. I realized early in my career that if I focused on developing my ability to manipulate and interpret performance data, the optimizations become obvious. Leaning into Excel skills can help you become a more efficient worker and improve the quality of your analyses. One of my favorite parts about Excel is that I continuously find ways to be more efficient.\u00a0Several functions and formulas can help expedite PPC tasks. What follows are seven Excel tips to help you quickly identify high-impact [&hellip;]<\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"h5ap_radio_sources":[],"cybocfi_hide_featured_image":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-5706","post","type-post","status-publish","format-standard","hentry","category-best-seo-blogs"],"featured_image_src":null,"featured_image_src_square":null,"author_info":{"display_name":false,"author_link":"https:\/\/craighordlow.com\/author\/"},"_links":{"self":[{"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/posts\/5706","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"replies":[{"embeddable":true,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/comments?post=5706"}],"version-history":[{"count":0,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/posts\/5706\/revisions"}],"wp:attachment":[{"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/media?parent=5706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/categories?post=5706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/tags?post=5706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}