Make Raw Data Meaningful: Excel Formulas and Data Visualization

In this SMX West liveblog coverage of the session “Expert Excel Essentials,” we’re reminded that Excel is a versatile problem-solving tool that nearly everyone has access to. Let’s turn raw data into something that people understand and can make decisions using!

Moderator Chris Sherman says that an Excel-focused session has been running at SMX for a few years. It started as an experiment. Search marketers know Excel but there are different levels of knowledge and skill. If used properly, then Excel is a Swiss Army Knife, so it pays to invest in your Excel chops. After hearing the Excel tips shared here, you’ll be inspired to go back to your job and do things in a tool (Excel) that most people have access to, so you’ll feel empowered to make a difference.

Introducing our speakers:

• Brett Snyder (@brettasnyder), Knucklepuck
• Timothy Gillman (@TimGillmanDrums), Analytics Strategist at Portent Inc.

Brett Snyder: Excel Formulas to Solve Problems

SEO is a tactic that supports an overall strategy with many mediums and channels, says Brett Snyder. The goal of all the media is to be where people are looking when they search, and to be where they are when they’re looking so you can join their conversation.

Some are overwhelmed by Excel, a raw export of numbers. Instead of feeling overwhelmed, you should feel excited about the opportunity to manipulate data.

He’ll share some simple formulas that will help you look at the data in the way you want, save time, and focus on the right information. How you collect and assess data can only be as good as your understanding of the problem you’re facing and the kind of solution you want.

The data you collect must be:

1. Simple.
2. Valuable.
3. Actionable.

You should use Excel to better understand why something happened and what’s next.

He shares some Excel formulas that help you solve specific problems next:

Discover Canonical Tag Inconsistencies

When two pages of near duplicate content have to exist for the user, you tell the search engine to direct the signals to a single page using the canonical tag. In an audit you may discover canonical tag misuse. The Excel function you can use for this is the =IF function.

=IF(Logical Test, Value if Yes, Value if No)

He uses =IF function logical tests as nested functions. This allows for qualitative analysis.

For more on nested functions, check out Brett’s presentation from 2014 SMX West.

Discover Which Pages on Your Site Have No Visits

To discover which pages on your site are getting zero visits use this Excel formula:

=CONCATENATE("http://www.brettasnyder.com",C3)

Use a raw block of text like the domain name that will be the same every time or data from an individual cell.

This formula is combined with Screaming Frog, and together the two tools tell you all the things you can find from crawling your links. You’ll get a list of all the URLs on the website.

Use a vlookup function to merge the data set with the Google Analytics visits export. Now you’ll cut through the noise and be able to see the pages on your site that have no visits.

Remove Duplicate Domains from a Backlink Analysis

You want to know the best link targets and see a list of 50 different domains, not a bunch of results of links from the same domain.

Use this formula:

=LEFT(C3, FIND("/",C3,9))

Find the first slash after the ninth character and it strips out all the extensions beyond the root domain. He wrote more about how to use this function here: Remove Duplicate Domains for Competitive Backlink Analysis.

To conclude he shares a mindset to approach Excel: “It’s the most versatile problem solving tool you have.” Determine what solution you need, then back into the problem.

Timothy Gillman: 3 Steps to Visualizing Data

Timothy Gillman is an analytics strategist at Portent Inc. He says he’s an “Excel wizard” and specializes in building tools. According to Timothy, the goal of data presentation is to have no questions at the end, and an epic moment of understanding.

He’ll cover how to:

1. Get the data.
2. Organize it.
3. Visualize it.

Bonus: he’ll share some high-end stuff.

He walks through a process he uses to get data from Omniture, and how to get data from Google Analytics. In this liveblog coverage, I’ve skipped coverage of the Omniture process;  I cover how the process he outlines for getting data from Google Analytics in the section titled “Using Google Analytics,” below.

Tim prompts us: why bother with Excel if the analytics platform already gives you a visualization? He says that being able to visualize raw data is something you can practice and then you can go across all your raw data sources and make them unified.

ow to visualize the data:

1. Highlight the cells in Excel.
2. Go to Tables tab and pick a look and style.
3. Next highlight the part of the table you’re looking to drill into with a chart.
4. Click the Chart tab.
5. Pick a style. He recommends staying with 2D.
6. Clean up! Tufte’s Rule: Minimize the ratio of ink to data. Take away the legend and the lines.

Using Google Analytics (It’s Free and Easy!)

Here Timothy Gillman walks us through the process he uses to get data from Google Analytics. Should you need it, here is a How to Set Up Google Analytics guide.

An example scenario: You want to see all the channels of Q4 2015 based on sessions and goal conversions.

In Google Analytics (GA), go to Audience Overview,then  search for channels. Now, GA will give you what you want plus some other stuff, which you’ll clean up in Excel. Export your data to CSV to work with the data in Excel.

The data you see in GA will be labeled differently than what you see in Excel. Here’s how it translates:

• GA → Excel
• Dimensions → Categories
• Metrics → Data Series

Click the Data tab in Excel to create a pivot table. Click the pivot table icon. Select Manual. You will have a black box. Put dimensions in row labels and metrics in the values area. Then… Voi la! Pivot table! 

Highlight the part of the table you’re looking to drill into with a chart. Click the Chart tab. Make the chart. All good, but uh oh! Conversions are in the .X percent and sessions are in the thousands so you can’t see conversions on the chart at all. In this scenario, you’ll need to add a secondary axis.

Here are some recommended places where you can learn more about power Excel use:

• lynda.com Excel courses (paid)
• Excel for Dummies (paid)
• YouTube tutorials (free)
• Microsoft Office Support site has examples and is interactive (free)

The Executive Dashboard was built in Excel primarily off pivot tables, vlookups, and logic like =IF statements.

Virginia Nussey is the director of content marketing at MobileMonkey. Prior to joining this startup in 2018, Virginia was the operations and content manager at Bruce Clay Inc., having joined the company in 2008 as a writer and blogger.

See Virginia's author page for links to connect on social media.