How to create an easy-to-understand pie chart from backlink data
If you’re an in-house SEO Manager, your big words around the office are hopefully customer-centric ones like conversion and retention. But if you’re an SEO Manager at an agency, your customers are other businesses, and the more common big words are justification and acquisition. We must make certain our current clients see the value of SEO and add new clients to our roster regularly.
While my agency has a substantial team of sales managers, when a big client opportunity comes along, often the SEO team is asked to get involved in analyzing the prospect’s current state of SEO. More so than other digital products and services, selling SEO requires a mix of appropriate teaching and a good bit of insulting the prospect’s current website content and link profile. So we make pie charts and word clouds that give a big middle finger to the prospect’s current lack of organic mastery, which (mostly) eliminates the need for us to resort to more traditional rampant cursing in the written summary.
Given the need for SEO evaluations for sales purposes, I’ve developed some reports that my team or our sales reps can generate to make prospective clients see the light. About a year ago, I went with Raven for monthly client SEO reporting, and I realized that it was a great portal for democratizing and centralizing research. Can I do all of the research within the Raven platform without Raven? Yes, but our sales managers can’t, and it makes it a lot easier for my SEO team to be on the same page.
So without further rambling, I’d like to show you how to create a simple link profile in about 15-40 minutes (depending on your Excel prowess) using Raven’s Backlink Explorer tool and Microsoft Excel. Though I’m calling this a sales tool, it’s also something we can use for quarterly progress comparisons, so this is as well a simple and meaningful report for you in-housers to take to the top brass.
How to create a link profile from Raven data
You know Raven’s Backlink Explorer? It’s nifty. You can sort by source, ACRank and anchor text. You can group domains together and filter results by search—all from the nice Raven interface. You know what C-levels and people outside of SEO do when shown all that stuff? They launch into same three-step process I recommend anyone do if confronted with a Matchbox Twenty track: Stop listening, close eyes and contemplate the meaning of adult contemporary. So export the data and present this instead:
- Run a Backlink Explorer report and export the data to a CSV for Excel (the default export option).
- Delete the template data and paste your exported data into the “rawdata” tab (Columns A-F) on this backlink profile template.
- In the highlighted Column G, classify your links as: branded (1); keyword-included (2); or no text/unoptimized (blank). Classifying keywords is the real work in this entire process. Use filters in Row 1 to filter anchor text from a drop down and assign the classifications to your anchor text in Column G. Once you’ve done this, it’s all about determining how to communicate the data through visuals. By default I use just those three link classifications above because they are simple and easy to comprehend. You can use more classifications if that’s better for you. You’ll just need to alter the template a bit.
- When you have classified your links, move over to the Link Profile Worksheet tab. Your links should broken out by classification and ACRank. From here you can make several different combinations of visuals. Look at the data. Is there a big disparity between the number low and medium ACRank links? Maybe that’s something to show (especially if your prospect has used or currently uses another SEO company). Are there too many keyword-rich anchor text links, making for an unnatural link profile? Quickly assess what will hit home and make a chart. Below are a couple of pie charts I’ve made from this data recently.
- Make another worksheet with just the data from your keyword-included links. Then you can display visuals based on most used keywords and distribution of keyword-included anchor text by ACRank. For instance, I made the following chart for a prospective client that sold fitness equipment:
Just to be clear, you have to do a little work here. This isn’t a self-generating report. But it really can take only 15 minutes to generate a few meaningful charts for a sales presentation. This stuff isn’t complicated or all that pretty, but it’s better than tables filled with numbers that mean nothing to most decision makers. These charts can make an impact in a sales pitch. I’ve seen this information take over a meeting like wildfire with big e-commerce players.
But I advise you to remember the object of your SEO insults when using this as a sales tool. If your prospect is doing SEO in-house, and the person in charge of SEO will be in the meeting, the charts can still be effective, but be prepared for defensiveness. And wildfires — we can never be too careful.
Also, depending on the prospect, I will or will not choose to represent visuals with ACRank. If the concept of how links are valued will just distract from the information because the prospect hears “SEO” and thinks of the Asian pop music scene before digital marketing, well, we can just call it “Link Authority.”
What else can you do with Raven data?
Recently, Samir Balwani shared a traffic forecasting report that can act as an interesting report to present to a prospective client and also a nice internal tool for determining how to spend SEO resources. Every company is different and requires different types of information at differing levels of detail. I’ve found Raven to be a convenient jumping-off point for just about all analysis. The ability to create a client profile and store its data in one convenient place is an ideal way to begin more advanced analysis. Think critically about the information you really want to show, and try to harvest Raven data to present the exact report you want. All of my favorite reports I created myself.
When it comes down to my current clients, I spend a lot more time with their web analytics than any SEO tools. SEO tools are mostly indicators of success and sometimes identifiers of problems. They are not true measurements of either. SEO research tools like Backlink Explorer are mostly just important for we people in SEO. They are great for prospecting when we have no access to a site’s web analytics, great for reporting SEO activity progress internally and pretty good for finding new opportunities.
Just remember that outcomes are what you’re after. A 50 percent increase in linking domains is cool. A 4 percent increase in desired outcomes from organic traffic means something.