Problem we are solving:
Running an influencer campaign and want to know whom to target? For this you might need to quickly get the Followers from 100s of Twitter Profiles. You could scrape this but usually that would need a developer to help. Below is a super simple solution using Excel’s Power Query that gets this done in just a few seconds. You dont need any programming experience. Just follow the simple steps below.
What you need?
- Excel 2013 or Above
- In Excel 2016 Power Query comes pre-installed but for 2013 you can download Power Query from here.
Skip to Content
Let’s get started
The first step is to write a Custom Function in Power Query that will go to any Twitter URL and pull out the Followers. You can equally also get other information on the Twitter Profile pages also, if that was your requirement.
For this we shall be using the following super simple Functions in Power Query
- Contents() – this pulls the Data from any URL. Think of this like look at the Page Source for any page. In Web.Content we can also pass Headers and User Agent. We shall use this in the Code
- FromBinary() – by Combining this with Web.Contents, we get the Page Source in Text Format, so we can now look for the Data we want
- Finally we shall use Text.BetweenDelimiters. Think of this like Excel’s Mid Function. This does exactly that
NOTE – We use Excel 2013. For more recent versions, the screenshots might differ a little
In order to write a custom Function, follow these steps:
- Under the Power Query Tab click on From Other Sources and choose Blank Query
- This will open the Query Editor Screen. Rename the Query Name to fnTwitter. I would highly recommend using sensible function names, else with multiple functions one can easily get lost.
- After this go to the Advanced Editor section and Copy the below Code. This is using a combination of the above functions to get the Twitter Followers. The key here is using the Text.BetweenDelimiters smartly and exactly as you would in the Mid Function in Excel.
(twitURL as text)=> let Source = try if Text.BetweenDelimiters(Text.BetweenDelimiters(Text.FromBinary(Web.Contents(twitURL,[Headers=[#”accept-language”=”en-GB,en-US;q=0.9,en;q=0.8,da;q=0.7″, #”User-Agent” = “Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)”]])),”Followers</span>”,”</a”),”false”&””””&”>”,”</span”) = “” then Text.BetweenDelimiters(Text.BetweenDelimiters(Text.FromBinary(Web.Contents(twitURL)),”Followers</span>”,”</a”),”true”&””””&”>”,”</span”) else Text.BetweenDelimiters(Text.BetweenDelimiters(Text.FromBinary(Web.Contents(twitURL)),”Followers</span>”,”</a”),”false”&””””&”>”,”</span”) otherwise “”, temp = if Text.Contains(Source,”K”) then Text.Replace(Text.Replace(Source,”K”,”00″),”.”,””) else Source, temp2 = if Text.Contains(temp,”M”) then Text.Replace(Text.Replace(Source,”M”,”0000″),”.”,””)
temp in temp2
- In the code above, when crawling the Twitter page we are also passing some Headers for Language and the User-Agent. This makes it less likely for Twitter to block us
- Once done, you will see an Input section. Test your Code by pasting the following the following Twitter Page URL in the Invoke box: https://twitter.com/robhargava. This should give you the followers for my Profile.
- That’s it. Now we need to create a List for the 100s of Twitter pages we want the Follower Data for. For this we first make a list and Load this List in Power Query. This we need to do with Power Query every time. To Load this this into Power Query, select the List, click on the Power Query tab and select ‘From Table/Range’
- This would automatically open the Query Editor screen. As before Rename the Query to something sensible like Twitter Followers
- Now Under Add Column, goto Invoke Custom column and call the Function ‘fnTwitter’ we made above. And hit ok.
- That’s it. Now just go ahead and Load this Tables into a New Worksheet.
- And just like that you have Twitter Followers in seconds
Simple Enhancements Possible
- We can similarly get Other Twitter Information
- We can even built a script that would automatically detect Twitter URLs from a list of Blogs and then get the Twitter Followers. This save hours of manual effort
- If you are looking for sample Excel Sheet please do get in touch with email@example.com.