How to level up your analytical skills with Excel

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. 

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 you to dig into performance changes at the most granular levels. 

CONCATENATE

=CONCATENATE(Cell1,Cell2,Cell3)

While this function won’t necessarily expedite the number crunching, CONCATENATE is an extremely efficient way to build bulk sheets and ensure consistency in the structure of your URLs and naming conventions. 

CONCATENATE “glues together” specified cells or text strings to create a single text string in Excel. Its use cases can be far-reaching, including:

• Campaign/ad group naming conventions.
• Appending URLs to parameters.
• Building keyword lists.

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. 

IF functions

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.

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.

With IF statements, you can start your analysis at the most granular levels and evaluate if data meets specified criteria instantly.

Transform date to week

The above formula allows data segmented by date to be categorized by week in a separate column, providing you with higher-level trends.

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). 

Categorization based on text

=IF(ISNUMBER(SEARCH(“text”,A1)),“text if true”,“text if false”)

The formula above allows you to search strings of text to create additional filters in the data set.

It’s helpful for analyzing text-heavy data (i.e., ad copy, keywords or campaigns) at scale and expediting label creation.

VLOOKUP

VLOOKUP returns data from a separate, vertically organized data set for a specified value. It’s one of the most impactful features that PPC professionals can add to their toolbelt.

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:

• May leverage internal platforms as their source of truth.
• Use a lot of abbreviations in their naming taxonomy. 

In the example above, a separate database of offline conversion data is appended to a standard keyword performance report using VLOOKUP.

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.

Calculations to enhance analysis: Deltas and incremental impact

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. 

Deltas and incremental impact both help to contextualize the magnitude of a change across two segments (think dates, devices, geos, audiences, etc.). 

The delta formula calculates the change in a metric relative to the previous period or between two segments.

Deltas are more universally applicable, as they can apply across volume and efficiency metrics.

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.

Deltas and incremental impact cannot be used interchangeably. 

• A delta is simply a rate of change.
• Incremental impact calculation highlights the positive/negative impact of changes in your funnel, assuming all else remains constant. This is a critical point, as we know that changes rarely happen in a vacuum.

Thus, incremental impact calculation must be considered in the context of how they are being used.

That said, this calculation helps us to quantify the “so what,” which can be particularly useful when conveying the performance narrative to key stakeholders. 

Excel your analytical skills in the face of automation

By incorporating these techniques into your own daily operations, you will save time in Excel.

Most importantly, you will also gain the confidence of your team and stakeholders, as your grasp on performance will be unmatched.

Watch: Level up your analytical skills with Excel

Below is the complete video of my SMX Next presentation.