Guide to Replicating SEOmoz’s Graphs in Excel
Posted by Annie Cushing
This post was originally in YouMoz, and was promoted to the main blog because it provides great value and interest to our community. The author’s views are entirely his or her own and may not reflect the views of SEOmoz, Inc.
If you run reports from the SEOmoz toolset, you may find it difficult to make your data look as sexy as the graphs you see on the site. I hope to close that gap with this post and give you the tools to format your data like a pro. To accomplish this objective, I've provided several resources:
- A video walkthrough of how to format your data like one of the SEOmoz charts for you DIY'ers out there.
- Chart template files for all of the charts on the SEOmoz site that have export options (there are three in total).
- Instructions on how to load the template files.
- A video walkthrough of the data prep and cleanup steps required with the templates.
Here's a screenshot of the three charts you'll be able to replicate:
Download the Excel file
You can download the Excel file I worked from to create these charts to follow along. Whether you watch the video to learn how to
steal all of SEOmoz's formatting ideas format like an SEOmoz data scientist or take the shortcut and download the chart template files, you may want to check out the Excel file for tips.
Watch over my shoulder as I create one
The report I’ll be demonstrating is the Historical Domain Analysis chart, found under Pro Dashboard (or Campaigns) > [choose your campaign] > Link Analysis > History. Here's a before/after picture of what I show you how to do:
I could write out all of the steps to replicate the Historical Domain Analysis chart, but most people find it easier to watch over someone’s shoulder. Plus, it takes a fraction of the time to watch the steps as opposed to reading and interpreting all of the screenshots. I go through the steps on my Mac, but provide the steps for PC each step of the way. They really are variations on a theme.
Load chart template files
I went through all the SEOmoz reports that a) offer an export, and b) include a graph of some type on the site and created templates, so you don’t have to format the data yourselves. It’s pretty easy to use them. However, some of the raw data needs to be massaged a bit before you can apply the templates.
To load them on your computer, unzip and drop these template files in the chart templates folder on your hard drive.
- PC: C:/Users/[Your User Name]/AppData/Roaming/Microsoft/Templates/Charts
- Mac: Users/[Your User Name]/Library/Application Support/Microsoft/Office/Chart Templates
Note: If your Charts directory isn't in the default location and you're on a PC, you can click the Manage Templates button by navigating to Insert > Charts > Other Charts > All Chart Types.
Data prep and chart cleanup
Each report has its own steps that you have to take to prepare the export for charting and a few cleanup steps you have to take afterwards. I don't normally do two videos in one post because I don't have a death wish; however, after writing out all the steps and taking the screenshots, I realized it looked scarier than it is. So I fired off another video to hopefully allay some of the intimidation.
Make your own
If you want to modify a chart(s) to your liking, no worries. It won't hurt my feelings. When you're finished with your changes, you can save it as your own template. On a PC, you would just choose Chart Tools > Design > Save As Template. On a Mac, the easiest way is to right-click on the chart and choose Save as Template. But if you prefer using the Ribbon, go to Charts > Change Chart Type > Other > Save as Template. (Like I said, use the contextual menu.)
Here are links to the two posts I referenced in the second video for a deeper explanation:
If you need help
So, if you want to regale your boss, client, or mom by replicating SEOmoz’s charts, hopefully this will give you the tools you need to do that. If you’ve picked up a few data visualization tips in the process, even better. But if you get stuck along the way or run into bugs I didn't run into (I didn't test these on older versions of Excel), feel free to comment below or hit me up on Twitter. Thanks for reading!
Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!
- How To Make Awesome Ranking Charts With Excel Pivot Tables
- Excel Statistics for SEO and Data Analysis
- Diagnosing Google Crawl Allowance Using Webmaster Tools & Excel
- How to Export Google Analytics Data to Excel via the API
- Delicious Updates: Search Refinements, iPhone Optimization, Graphs and Sharing