Table of Contents
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.
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.