SEER Blog

  • http://www.genuineinternetmarketing.com/ Shane Eubanks

    Wow, that’s pretty slick for doing competitive research! Thanks for the thorough tutorial! Very easy to follow and helped me locate some potential backlinks within minutes.

    Thanks!!

  • http://twitter.com/anthonydnelson anthonydnelson

    Love the idea of this and the great instructions on how to do it. If only my client’s Twitter followers list wasn’t so full of junk/spam accounts.

  • http://www.aunesty.com Aunesty Janssen

    Again, great tutorial and in depth directions. Thanks!

  • http://www.seo.com/ Malmborg

    This is brilliant. I feel this could be the beginning of game changing back link analysis.

    Thanks for the tutorial.

    David

  • Ethan Lyon

    @Shane – Thanks!

    @Anthony – You can use the Rank() function on followers and listed metrics to weed out the spam. We used it and had pretty solid results.

  • http://www.bradsdeals.com Rebecca L.

    Where do you see a $10 fee for exports with more than 10K followers? I see a contact form promising a free trial if I let a salesperson call and pitch me an expensive product, but no paid one-shot export solutions.

  • Preston Van Dyke

    I can’t seem to get the CTRL + Replace to work for the “/*.” Have you experienced and issues with this as well?

    At first I thought it may be a problem with the Mac version of Excel, but tried it on a PC and got the same error.

  • Ethan Lyon

    @Rebecca – It seems that they got rid of that option and now you have to go through Simply Measured. My apologies.

  • Ethan Lyon

    @Preston – I have not experienced that issue before. When I find and replace “/*” with nothing, it will strip off all of the directories on the selected URLs. If it still doesn’t work, let me know and I can send you a formula that will do the same thing.

  • http://www.seo.com Preston Van Dyke

    That’s weird. I must be missing something, but can’t figure what it may be. I would love the formula if you are willing.

    Plan on using this strategy for quite a few clients this month. Love it.

  • Ethan Lyon

    @Preston – Here ya go: =LEFT(SUBSTITUTE(SUBSTITUTE(A1,”http://”,””),”www.”,””),FIND(“/”,SUBSTITUTE(SUBSTITUTE(A1,”http://”,””),”www.”,””))-1). It’s a pretty clunky formula, but it should do the trick. Just make sure your URLs are in column A. Then drag the formula to the bottom of the URLs and you should be all set. Let me know if it doesn’t work out.

  • http://www.seo.com Preston Van Dyke

    Thanks Ethan. For some reason that Formula was throwing an error too. Our resident Excel Guru hooked me up with this formula to simply extract the root domain. Works like a charm so I thought I would share:

    =IFERROR(MID(A2,IFERROR(FIND(“//www.”,A2)+6,FIND(“//”,A2)+2),IFERROR(FIND(“/”,A2,9),LEN(A2)+1)-IFERROR(FIND(“//www.”,A2)+6,FIND(“//”,A2)+2)),””)

  • Ethan

    @Preston I’ve used a similar formula before, but have found the substitute function to be a bit faster to write. Thanks for passing it along! Let me know how everything goes.

    - Ethan

  • Ethan

    @Rebecca – I just found where you pay $10. Log in and try to export a Twitter users followers and you should see a message like this: http://screencast.com/t/t1nXMCS0P

  • http://www.climaxmedia.com/ Elijah

    Fantastic tutorial. Super detailed :) I just printed it out for later reference…

  • Purple Trout

    Slick! Looking forward to using Excel for something other than Fantasy Football. Thanks!

  • Robin DeCato

    Super helpful. Thank you for sharing!

  • Brian Freire

    What a great way to get backlinks, Thanks for the tutorial, I will be trying out this out.

  • http://www.tek.com Luke

    What qualifies as a link? They have just linked from their Twitter account at some point? Maybe I did it wrong but out of our 2k followers I got 0 links.

  • http://www.bhomes.com/ Better Homes

    Lovely post! simply love the idea you’ve portrayed. But don’t you think that i’ll irritate your followers?? as in like if you pitch them or mail them regarding thanks for following us please show some link love by giving us link?
    This tool is awesome no doubt but as an cooperate business site dont you think it’ll look pretty awkward asking a link from you followers.
    This could a good topic to discuss and i would really like to learn more on it.

  • http://www.the3best.net Staci Smith

    This is fabulous on so many levels from the intent to the execution. There is so much Twitter clutter that aside from your message getting lost amid the junk masses, it can be hard to tell who is really processing your information and has the potential to grow into a good relationship. This gives you a good starting point for lasering in on those people… Plus you get to have fun with EXCEL! Thanks once again for the insight!

  • http://www.inceptdesign.com Alex Adekola

    This is an awesome idea…so glad I went to LinkLove in NO….It’s still paying back dividends.

  • http://blog.attachmedia.com Carlo Rodriguez

    Hi, I try to make a Influence Rank but I dont understand very well the Rank Function.

    If a use followers like a number and his row followers, friends and listed like a reference the number is not usefull.

    For other side, if a use followers like a number and his colum like a reference the number is not usefull too.

    I think that I need create a rank for followers and other for friends and other more for listed and them combined but I’m not sure.

    Any help is welcome.

    Reggards from Peru :)

  • Ethan

    Hey Carlo,

    You’ll want to compare the user against all other users for that given metric. So if I’m ranking Twitter followers, I’d rank the specific user’s followers against everyone else’s followers. Once you do that for all of the metrics that are important to you, you can add them up to get a score. Sort the score and you have your influencer rank.

    Make sense?

    - Ethan

  • http://blog.attachmedia.com Carlo Rodriguez

    Thanks Ethan, I ‘m not sure if I do the things well. For example I create a rank for followed, friend and listed. I have the 3 ranks, but the result are not usefull.

    I supose that I need combine the 3 for create an Adrank. I think that I can divide followers/friends, if the number is high is better. But I’m dont know how use the list in that equation.

    For other Side in the equation that I did, maybe I don’t need the Rank() function. Honestly I don’t see the utility from Rank() function in this case.

  • http://www.probiotixfoods.com Probiotix

    Ethan,

    The is a very inspiring post which opens new opportunities to all those stuck to old fashioned link building methods. Thanks for sharing the export.ly tool, really handy.

    Keep up the good work.

  • http://webboomm.com/nl Herwin Wevers

    Wow, Ethan, this is a great way to find some easy backlinks from loyal (hopefully) followers. Great explanation how to get these. Let them coming these great tips.

  • http://www.mavenface.com/ Andre

    I like the reasoning behind the idea, but I think if your twitter links out using bitly or twitter’s automatic shortner, it will be harder to recognize which followers link to you.

  • Sandeep

    Hi @Ethan..thanks for the amazing post..just a quick note where can we get a example of the above spreadsheet..as a reference

  • http://www.boxcarmarketing.com Monique Sherrett

    For Preston Van Dyke and Ethan on the find/replace for /*

    Sometimes Excel tries to be helpful and in the case where /* won’t replace, it’s because the cell actually contains a formula noting that this is a hyperlink.

    Here’s what you need to do.

    Select the column.
    Edit > Copy
    Edit > Paste Special (select Values)

    Then you can carry on with find/replace for /*

  • http://www.DomainerCash.com Sammy

    Great,,,
    thank for nice posting…
    I’m following you…
    How can i get that excel file…

  • Nikos Eleftheriou

    Fantastic method! My customers will love this tactic of extracting data and information! Thanks for sharing the knowledge!

  • http://www.seoverflow.com Alex Juel

    Anyone have experience doing this on Excel for Mac? Pivot Tables seem to be set up a bit differently on Mac, and I just don’t get it.

  • http://www.seoverflow.com Alex Juel

    Nevermind, I got it! It’s just not as clear on Excel 2008 for Mac. Excel 2008 on Mac doesn’t have the Report Filter, Column Labels, Row Labels or Value windows in the field list, so I just had to drag the buttons to the right areas on the spreadsheet. This is an awesome tutorial, thanks so much for putting it together.

  • http://This1That1Whatever.com/ David Wong

    In my tweets, I include links back to my website. However, my Google Webmaster Tools only shows 1 backlink from my Twitter profile. I do get retweeted. Does anyone see Twitter backlinks show up in GWT?

  • Spook SEO

    Collecting data from twitter is very easy by using your mentioned tool and you really well arranged data on the excel sheet and transform it in the exact desired information form. Its very compulsory to collect record of backlinks for checking the progress of SEO strategies.

  • kamaldws

    A really Nice post! I am going to do this on immediate basis. Thanks for sharing the such a information.

  • http://www.pcgamesyard.com/ Ali Haider Shah

    Thanks man what an awesome post this was:)