l: How to improve your analytical skills

Advertisers must have the ability to analyze changes in funnel performance and identify optimization opportunities, due to automation becoming more common in campaign management platforms.

During my presentation for SMX Next, I highlighted functions and formulas within Microsoft Excel that can be used to aid advertisers in data manipulation.

It is important to spend meaningful time creating the data sets in order to create impactful analysis. These techniques will allow you to create data sets that are easily aggregated and enable you to examine performance changes at the granularest levels.

CONCATENATE


=CONCATENATE(Cell1,Cell2,Cell3)


Although this function will not necessarily speed up the number crunching process, CONCATENATE can be used to build bulk sheets and maintain consistency in your URLs and naming conventions.

CONCATENATE “glues together” specified cells or text strings in Excel to create one text string. There are many uses for it, including:

• Conventions for naming campaigns/ad groups.
• Add URLs to Parameters
• Building keyword lists.

Consistent taxonomy in naming conventions enhances the granularity and analysis. You can also use Text to Columns to create filters in your data set.

Functions of IF

IF functions are a way to add dimension to your data set. An IF statement simply evaluates a cell against a logical situation.

If the answer is true, one value will be returned. If it is false, another value will be returned. Multiple logic tests can be stacked one on top of the other. This is called a nested IF sentence.

IF statements allow you to quickly evaluate whether data meets certain criteria and start your analysis at the highest level.

Transform the date into a week

This formula allows data to be segmented by date and categorized by week in a separate columns, providing higher-level trends.

This formula can be tailored to fit with any reporting format your business uses. For example, reporting week begins on Saturday.

Text-based categorizing


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


You can search text strings using the formula to add filters to the data set.

It is useful for analysing text-heavy data (e.g., keywords, campaigns, ads) at large scale and for expediting label creation.

VLOOKUP

VLOOKUP returns data for a specific value from a separate vertically organized data set. This is one of the most important features PPC professionals can include in their toolbox.

VLOOKUP simplifies the process of joining two data sets together on the basis of a shared value. This can be a powerful tool for speeding up analysis for advertisers who:

• May use internal platforms to find truth.
• In their naming taxonomy, use a lot abbreviations.

The example shows how a separate database with offline conversion data is added to a standard keyword performance reporting using VLOOKUP.

This function can be used to create partial matches. However, it is not recommended for exact matches in search engine marketing as partial matches are often inaccurate.

Calculations to improve analysis: Deltas, incremental impact

Once you have the data set to your liking, it is possible to analyse funnel performance. You can use two calculations to do this: incremental impact and deltas.

Both incremental impact and deltas help to contextualize the magnitude change across two segments (think dates/devices/geos, audiences, etc. ).

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

Because they can be applied across both volume and efficiency metrics, deltas are more universally useful.

However, incremental impact measures the effect of a metric (commonly CTR or CPC), on the volume flowing through the funnel.

You cannot use incremental impact and deltas interchangeably.

• A delta simply refers to a rate of change.
• The incremental impact calculation shows the positive and negative effects of changes to your funnel. This assumes that all other factors remain constant. This is an important point because we all know that changes rarely occur in a vacuum.

Therefore, it is important to consider how incremental impact calculations are used.

Nevertheless, this calculation allows us to quantify “so what” which is particularly helpful when communicating the performance narrative to key stakeholders.

Automated tasks require you to use your analytical skills effectively

These techniques can be integrated into your daily Excel operations to save time.

You will gain confidence in your team and your stakeholders because of your unrivalled performance.

Excel: Excel can help you improve your analytical skills

Here is the entire video of my SMX Next presentation.

Search Engine Land first published the post Excel: How to improve your analytical skills