How To Manage Big Data With Pivot Tables. How To Make Awesome Ranking Charts With Excel Pivot Tables. The author's posts are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.
On the "So You Want to Test SEO? " panel at this year's SMX Advanced Seattle, Branko Rihtman from SEO Scientist presented some spiffy looking ranking charts, measuring positioning by keyword, over time. A few people asked me how exactly you make a chart like that. Being something of an Excel fan, I was instantly inspired to share the approach with my fellow SEOmozzers. Here's a step by step on how to create a rankings chart using Excel. Collect the data To be able to produce a chart like my example below, you're going to need Microsoft Excel, and a rankings checker that will export ranking data, by search engine and by date. Create a pivot chart Pivot tables were designed for exactly this type of application, and making them is heaps of fun.
You should see a window appear. Drag and drop your legend, axis and value fields Filter by search engine The end result. Advanced Excel For PPC: Using Regular Expressions To Add Dimensions To Data. Regular expressions are a powerful computing tool, with near-mythical status in some programming circles (here is one of my favorite online comic series’ take on the topic: This article demonstrates some useful ways to use regular expressions with PPC, by adding new dimensions to our typical Account/Campaign/AdGroup hierarchy of data.
Another use for regular expressions would be manipulating URLs – perhaps in a future article… First, a quick note: Getting up to speed with regular expressions in general can be a daunting adventure. There are a lot of resources online for getting started - is a good one. While this article assumes a working knowledge of Regular expressions, the examples do work without your needing to know anything about them. Enriching Data With Campaign & AdGroup Names It is sometimes useful in PPC to group data together in more ways than the standard hierarchy of Account, Campaign, AdGroup, etc.
Use A Strong Naming Convention {Dimension}({Value}), e.g.: Product(Road Bike) A Marketer's Guide To Table Formatting In Excel. If there’s one task most marketers share — whether their focus is SEO, paid search, or social media — it’s collecting and interpreting data.
Being able to slice and dice the data to find actionable insights is key to effective analysis. Yet, one of the biggest mistakes marketers make is trying to wrangle static data instead of taking advantage of Excel’s table formatting, which basically turns your data range into an interactive database. I hope to rectify that injustice against innocent data with this guide. Microsoft introduced table formatting in its 2007 version (2011 for Mac). For me, it was love at first sight. I’ll demonstrate using a data dump from SEMRush for a shoe website I checked into after seeing the coolest shoe store in Manhattan last week, Shoe Mania. SEMRush is a great jumping-off point for competitive analysis because it lets you know the keywords that the site is ranking for on the first two pages of Google.
The original data dump is in the first tab. Table Setup.