In SEO, we look at many pools of data to find how we can get our client to rank better and increase revenue from search. But we usually look at them individually -- exploring competitor backlinks, Twitter users, ect. What if we mashed those pools of data together to find linking opportunities? That was a question Wil posed to me several months ago and asked that I develop a tool in Excel.
The fundamental goal of the tool is to mash Twitter and backlink data together to find out who is following your client but does not link to them. This can be very powerful because it leverages existing relationships to get links that scale. After we answered that question, we then began using the data in different ways to answer new questions. Although we are only going to be looking at one way to mash data together, there are endless possibilities once you learn the Excel functions VLOOKUP and COUNTIF and using pivot tables to consolidate and sort data.
Let's begin with how to find people that follow your client but do not link back to them.
Pull the Twitter Data
Export your client's Twitter followers using Export.ly. If they have under 10,000 followers you can get the Twitter data with a tweet about Export.ly, but if you're getting over 10,000 followers, you'll have to pay a $10 fee.
Creating a New Excel Spreadsheet
We are going to do this with Wil's Twitter data as an example. Paste the raw Export.ly data into column A in a new Excel workbook.
Pulling OSE Backlinks
Export your client's external backlinks from Open Site Explorer or Majestic SEO. We are going to export SEER Interactive's external backlinks from the root domain, pulled from Open Site Explorer. Now paste this data into into column B in a separate sheet in the workbook.
Cleaning URLs
One of the most challenging parts of this exercise is cleaning the data. We need to remove the http://, www., and everything that comes after the backslash in both sets of data. That way we can compare apples to apples from our backlinks and Twitter data to pivot. There is a brain-numbing formula to do this using IF, ISERROR and SUBSTITUTE functions, but even though it's not as scalable, its much easier to use find and replace than write complex formulas in Excel (that'll be for another day).
In our Twitter spreadsheet, copy and paste column E (the URL column) into column N. Now select column N, find and replace http:// with nothing and do the same for www., /* and "/" -- in that order. If you do it out of order you could end up erasing your URLs!
We now have to do the same for our backlinks. Copy and paste column B (URL), to column A. Repeat the process above to clean the URLs.
Find Who Has and Has Not Linked To You
After weve cleaned our URLs, we can use the COUNTIF statement to determine whether our followers have linked to us. Label column O in the Twitter data sheet, Link: Yes / No, and write the function in O2:
=IF(N2="","No",COUNTIF([select column A in the backlinks], [select N2 in the Twitter Sheet])>0,"Yes","No"))
Copy and paste this formula to the bottom of the Twitter data set. This function will count the number of times the clean URL in the Twitter data appears in the backlinks and account for users that do not have a URL in their Twitter profile. You should now have a column that is filled with Yes and No.
Creating a Pivot Table
Let's take this new data and pivot off of it to see who is following our client but not linking to them. Select column A through O and click on the pivot table button. When you select OK, it should open a new spreadsheet.
Adding Pivot Table Filters
Drag
- "Link: Yes / No" to the "Report Filter"
- "Screen Name" and "URL" to "Row Labels"
- "Friends" to "Values"
Now we need to refine these settings a bit. Click "Count of Friends" and select "Value Field Settings." Now, select "Sum" and OK.
Modifying the Pivot Table
We have all of Wil's Twitter followers in our pivot table. We want to only look at those that are not linking to SEER Interactive, so we need to select the "Link: Yes / No" filter in the pivot table and click "Yes" and "(blank)." We now have a list of Wil's Twitter followers that do not link to SEER. But it's very disorganized.
Right-click on one of the "Sum of Friends" numbers, "Sort" then "Largest to Smallest."
Now you can DM to the bloggers that you have a good relationship with and maybe guest post or ask for a link.
Other Things You Can Do With The Data
This is only one way to slice the data. For more advanced Excel junkies, you can:
- Use the rank function to weigh "listed," "followers" and "friends" to create an influencer rank, because sorting the number of followers a user has isn't the best indication of influence.
- Sort Twitter users by largest to smallest number of followers and eliminate URL duplicates. If there are multiple followers from one domain, it could inflate your friend count. This only applies if you are pivoting off the website.
- Add RTs to figure out other users that are usually tweeted with your client. This can help you better understand your client's "Twitter social circle."
- Use domain and page metrics from the OSE export to sort Twitter users. At the end of the day, it doesn't matter how influential your prospects are on Twitter if you're trying to get links from authoritative domains.
- Bring in the Google SERPs to see who is ranking ahead of you and if there is any way to get a link from those people in their social circle.
- Pivot on your Twitter follower's location to find local linking opportunities.
Now you can build a dashboard that helps you find linking opportunities from your client's Twitter users.
If you get a chance to try out this tool for your client, we'd love to get feedback. Also, if you find other ways to slice and dice Twitter data to get links for your client, please add them to the comments section.
Thanks and enjoy!
