Live from SMX Advanced: Extreme Excel Excellence

By Janet Driscoll Miller | Jun 13, 2013
More Articles by Janet


Excel is a key tool for search marketers. This panel deeply delved into Excel functions and approaches for search marketers. It featured Annie Cushing of Annielytics.com, John Gagnon of Microsoft, AJ Kohn of Blind Five Year Old, Conrad Saam of Atticus Marketing and Brett Snyder of Nebo Agency.

Annie Cushing

Annie started off the panel.

First, let’s talk about motivation. Status quo bias is the idea that as humans we don’t like change. We need to overcome this. If you give something to someone, it will give someone happiness. But the loss of that thing makes people twice as sad as they were happy to get it. So you have to get people to take risk and make the value of that risk high.

She shared a study about exchange of items, and it can be difficult to even get people to give up something in an exchange. There are two systems in your brain. Automatic system is like Homer Simpson. It’s fast, unconscious, easy multitasking and associative. The reflective system is slow, consscious, serial, analytical and it consumes a lot of energy.

How does that involve us? When we create Excel documents and reports, we need to inspire action.

  1. Crawl.
    Conditional formatting can help with tabular data. This helps add visualization to highlight things like trends and pattern matches.
  2. Walk.
    Use charts. Change the colors to your branded colors — don’t just use the default Excel colors. Thin out the axis. Lighten up gridlines. Add an intuitive title. Move the legend to the top and bump up the font size.
  3. Run.
    Try combination charts, like a bar graph combined with a line graph.2013-06-12 21.09.12Add notes too. Show a correlation analysis, like clicks v. average position.  Try a pivot chart.

AJ Kohn

AJ was up next with a presentation entitled “Everyday I”m Excellin’”. Love it.

Excel is all about making a difficult task faster and easier. AJ recommended keeping a text file that keep track of all of your best formulas, etc. so that you can refer to it when you need it. When you get stuck, also search on Google — there’s always someone with an answer out there.

Today()-1

This formula tells you what yesterday was. This allows him to look at rolling reports and such. You can use this for running traffic reports, etc. And it will update automatically!

Use the last cell in the report as =today()-1, then every day as you open it up, it’s yesterday. AJ also highly recommended hlookup and vlookup functions.

AverageIf

Averageif allows you to average visits, for instance, if the day is Monday. Then you can see what your traffic on Mondays are generally like on average. The same can be done on an hourly basis. You could easily parse out hourly traffic by day.

You can also use this to look at an index of how traffic from organic should look on a given day, but you see variations that are great, then you can watch for an algorithm update.

Goal Seek

It’s under the data tab. .

Brett Snyder

Brett was up next and started off by sharing that 90 percent of the effort should be on the tool and 10 percent should be from the human effort.

At its core, Excel is a problem solving tool. He tries to make data simple, valuable and actionable.

If() function

If functions do a logical test and provide a result based on whether the test is true or false. Nested functions work along the old premise of “Please Excuse My Dear Aunt Sally”:

2013-06-12 21.08.21

First, identify the problem.

One example of how he uses this is “striking distance keywords” – ones that are ranking between 5 and 20 that they are not actively optimizing for. So in identifying the problem, we’re leaving value on the table.

Determine the criteria: where are you currently ranking? how many impressions did it get? how many conversions?

Then find the key. Label keywords as “striking distance” vs. “not striking distance” when the ranking is in the ranking criteria 5-20.

You can also next multiple functions as well. If functions can have multiple if functions nested.

Conrad Saam

Conrad was up next and focused on scatter graphs. He showed an example of the map graph from Google Analytics, and changed the data to a scatter graph, which was much more informative.

Calculate, gather, put together and plot, impact the variable and report on that change. Here’s how that’s done…

First, filter your Google Analytics data. Download the data. While GA only allows you to download 500 rows of data, you can edit the URL to 10,000 and download 10,000 rows of data. Gather the data in the Excel file and clean it up. For instance, if the data doesn’t match for vlookup purposes, you need to update it.

Some key functions:

  • LEN (length)
  • LEFT (define number of characters in a cell to display from left)
  • CONCATENATE (put cells together)
  • SEARCH

Standardize the data and merge it. Gather variables and put that data together in plot to show correlations in a scattergraph. Pick a variable, then impact it to see if it changes the pattern of the scattergraph.

John Gagnon

John wrapped up the session and reviewed GeoFlow, it is only available in Excel 2013 right now. It essentially combines the power of Bing maps with the data power inside of Excel. Fascinating!

20130612_155450

You can download the plugin for Excel 2013 for free at: http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx.

Share this article

Share on LinkedIn Share on Twitter

Receive Monthly Digital Marketing Tips

Subscribe to monthly updates from the Marketing Mojo Blog to get the latest digital marketing tips, best practices and insights - hot off the presses and straight to your inbox!

 

Blog Search