{"id":7816,"date":"2023-01-23T15:03:25","date_gmt":"2023-01-23T15:03:25","guid":{"rendered":"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/"},"modified":"2023-01-23T15:03:25","modified_gmt":"2023-01-23T15:03:25","slug":"how-to-level-up-your-analytical-skills-with-excel","status":"publish","type":"post","link":"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/","title":{"rendered":"How to level up your analytical skills with Excel"},"content":{"rendered":"<p>With the increased prevalence of automation in campaign management platforms, advertisers must possess analytical skills that will allow them to comprehend changes in funnel performance, as well as detect optimization opportunities.\u00a0<\/p>\n<p>During my SMX Next presentation, I highlighted formulas and functions within Microsoft Excel that can help advertisers with data manipulation.<\/p>\n<p>When it comes to creating impactful analysis, meaningful time needs to be invested in the creation of the dataset. Using these techniques, you will create data sets that can be aggregated at scale, while enabling you to dig into performance changes at the most granular levels.\u00a0<\/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\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#CONCATENATE\" >CONCATENATE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#IF_functions\" >IF functions<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#Transform_date_to_week\" >Transform date to week<\/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\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#Categorization_based_on_text\" >Categorization based on text<\/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\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#VLOOKUP\" >VLOOKUP<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#Calculations_to_enhance_analysis_Deltas_and_incremental_impact\" >Calculations to enhance analysis: Deltas and incremental impact<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/craighordlow.com\/index.php\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#Excel_your_analytical_skills_in_the_face_of_automation\" >Excel your analytical skills in the face of automation<\/a><\/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\/2023\/01\/23\/how-to-level-up-your-analytical-skills-with-excel\/#Watch_Level_up_your_analytical_skills_with_Excel\" >Watch: Level up your analytical skills with Excel<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"CONCATENATE\"><\/span>CONCATENATE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>=CONCATENATE(Cell1,Cell2,Cell3)<\/p>\n<p>While this function won\u2019t necessarily expedite the number crunching, CONCATENATE is an extremely efficient way to<strong> <\/strong>build bulk sheets and ensure consistency in the structure of your URLs and naming conventions.\u00a0<\/p>\n<p>CONCATENATE \u201cglues together\u201d specified cells or text strings to create a single text string in Excel. Its use cases can be far-reaching, including:<\/p>\n<p>Campaign\/ad group naming conventions.<\/p>\n<p>Appending URLs to parameters.<\/p>\n<p>Building keyword lists.<\/p>\n<div class=\"wp-block-image\">\n<\/div>\n<p>A consistent taxonomy in naming convention enhances the granularity of analysis, as you can use Text to Columns to create filters within your data set.\u00a0<\/p>\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<\/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\u2026Please 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\">SUBSCRIBE<\/button>\n\t\t\t\t\t\t<\/p><\/div>\n<\/div>\n<\/div>\n<\/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<\/div>\n<\/div>\n<\/div>\n<p><!-- END INLINE FORM --><\/p>\n<h2><span class=\"ez-toc-section\" id=\"IF_functions\"><\/span>IF functions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>IF functions can help create additional dimensions in your data set. At its core, an IF statement is simply an evaluation of a cell against a logical condition.<\/p>\n<p>If the answer is true, one value is returned and if the statement is false, a different value is returned. Multiple logic tests can be stacked on top of each other, known as a nested IF statement.<\/p>\n<p>With IF statements, you can start your analysis at the most granular levels and evaluate if data meets specified criteria instantly.<\/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\">\nIF statement to transform date to week<\/div>\n<p>The above formula allows data segmented by date to be categorized by week in a separate column, providing you with higher-level trends. <\/p>\n<p>One key feature of this formula is that it can be customized to align with whatever reporting cadence your business uses (i.e., reporting week starts on Saturday).\u00a0<\/p>\n<div class=\"wp-block-image\">\n<\/div>\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<p>=IF(ISNUMBER(SEARCH(\u201ctext\u201d,A1)),\u201ctext if true\u201d,\u201ctext if false\u201d)<\/p>\n<p>The formula above allows you to search strings of text to create additional filters in the data set. <\/p>\n<p>It\u2019s helpful for analyzing text-heavy data (i.e., ad copy, keywords or campaigns) at scale and expediting label creation.<\/p>\n<div class=\"wp-block-image\">\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"VLOOKUP\"><\/span>VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<div class=\"wp-block-image\">\nVLOOKUP formula<\/div>\n<p>VLOOKUP returns data from a separate, vertically organized data set for a specified value. It\u2019s one of the most impactful features that PPC professionals can add to their toolbelt.<\/p>\n<p>If simplified, VLOOKUP allows you to join two data sets together based on a shared value. It can be an incredibly powerful tool to speed up analyses for advertisers who:<\/p>\n<p>May leverage internal platforms as their source of truth.<\/p>\n<p>Use a lot of abbreviations in their naming taxonomy.\u00a0<\/p>\n<div class=\"wp-block-image\">\n<\/div>\n<p>In the example above, a separate database of offline conversion data is appended to a standard keyword performance report using VLOOKUP.<\/p>\n<p>While this function allows for partial matches, I recommend only using it for exact matches in search marketing since partial matches tend to be inaccurate.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Calculations_to_enhance_analysis_Deltas_and_incremental_impact\"><\/span>Calculations to enhance analysis: Deltas and incremental impact<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once the data set is built to your liking, you can proceed to analyze funnel performance. Two calculations you can use for this are deltas and incremental impact.\u00a0<\/p>\n<p>Deltas and incremental impact both help to contextualize the magnitude of a change across two segments (think dates, devices, geos, audiences, etc.).\u00a0<\/p>\n<p>The delta formula calculates the change in a metric relative to the previous period or between two segments.<\/p>\n<div class=\"wp-block-image\">\nDelta formula<\/div>\n<p>Deltas are more universally applicable, as they can apply across volume and efficiency metrics.<\/p>\n<div class=\"wp-block-image\">\n<\/div>\n<p>On the other hand, incremental impact quantifies the impact that a metric (commonly a rate like CTR or CPC) had on the volume flowing through a funnel.<\/p>\n<div class=\"wp-block-image\">\nIncremental impact formula<\/div>\n<p>Deltas and incremental impact cannot be used interchangeably.\u00a0<\/p>\n<p>A delta is simply a rate of change.<\/p>\n<p>Incremental impact calculation highlights the positive\/negative impact of changes in your funnel, assuming all else remains constant.\u00a0This is a critical point, as we know that changes rarely happen in a vacuum.<\/p>\n<div class=\"wp-block-image\">\nIncremental impact calculation for CTR<\/div>\n<p>Thus, incremental impact calculation must be considered in the context of how they are being used. <\/p>\n<p>That said, this calculation helps us to quantify the \u201cso what,\u201d which can be particularly useful when conveying the performance narrative to key stakeholders.\u00a0<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Excel_your_analytical_skills_in_the_face_of_automation\"><\/span>Excel your analytical skills in the face of automation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>By incorporating these techniques into your own daily operations, you will save time in Excel.<\/p>\n<p>Most importantly, you will also gain the confidence of your team and stakeholders, as your grasp on performance will be unmatched.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Watch_Level_up_your_analytical_skills_with_Excel\"><\/span>Watch: Level up your analytical skills with Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Below is the complete video of my SMX Next presentation.<\/p>\n\n<div><\/div>\n<p>The post <a href=\"https:\/\/searchengineland.com\/level-up-analytical-skills-excel-391940\" target=\"_blank\" rel=\"noopener\">How to level up your analytical skills with Excel<\/a> appeared first on <a href=\"https:\/\/searchengineland.com\/\" target=\"_blank\" rel=\"noopener\">Search Engine Land<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>With the increased prevalence of automation in campaign management platforms, advertisers must possess analytical skills that will allow them to comprehend changes in funnel performance, as well as detect optimization opportunities.\u00a0 During my SMX Next presentation, I highlighted formulas and functions within Microsoft Excel that can help advertisers with data manipulation. When it comes to creating impactful analysis, meaningful time needs to be invested in the creation of the dataset. Using these techniques, you will create data sets that can be aggregated at scale, while enabling [&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-7816","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\/7816","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=7816"}],"version-history":[{"count":0,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/posts\/7816\/revisions"}],"wp:attachment":[{"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/media?parent=7816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/categories?post=7816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craighordlow.com\/index.php\/wp-json\/wp\/v2\/tags?post=7816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}