This post is about the tool me and my team designed for our HS2 campaign in the "Build the news" event and how we came out with the the Fusion table-to-Twitter map with which we won the "Crowd" category. And, as the title suggests, the combination of tools I used are free and really easy to use.
The tool
What we had in mind was to create a map showing the votes of MPs in the recent HS2 Preparation Bill by constituency, then allowing the users to tweet their opinion about the vote to their MP.
There is already
a way to contact your MP, but Twitter is quicker and the most used among social media UK.
The product we wanted was clear in our mind, but having no developer in our team and very basic knowledge of HTML and CSS coding, we had no clue on how to reach our target.
There is already
a way to contact your MP, but Twitter is quicker and the most used among social media UK.
The product we wanted was clear in our mind, but having no developer in our team and very basic knowledge of HTML and CSS coding, we had no clue on how to reach our target.
But we made it and this the result:
The data we collected
First of all what we needed was the series of MPs' votes on the Preparatory Bill set in a spreadsheet. Through "The Public Whip" website we were able to access the official document, which includes the list of MPs' names and their votes - aye (yes), no.
This can be done by simply copy and paste the list, but to do a more precise job I'd suggest to use a Google Docs' =importXml function.
You can learn how to do it in 5 minutes reading the Onlinejournalism.com tutorial.
Then we scraped the MPs' Twitter accounts from TweetMinster using the software OutWit Hub - the free version of the software can resolve a lot of your problems when it comes to scraping.
Then we scraped the MPs' Twitter accounts from TweetMinster using the software OutWit Hub - the free version of the software can resolve a lot of your problems when it comes to scraping.
I did this without writing a line of coding and just using - what I call - the second level of scraping with Outwit Hub. I wrote about how to scrape with Outwit Hub without using coding and the three levels of scraping.
Time is always precious in journalism and not anybody has enough of it to spend learning how to code in REGEX - which I strongly recommend.
Outwit is perfectly designed to scrape several pages with its "forward" function.
But be careful, it seems that it stops when arrives at the ninth - any clue about the reason?
So we had to manually write 10 in the url of the ninth page in order to keep the scraper going and the same each 10 pages, for no apparent reason.
![]()
Fortunately the urls are in a good format, with the number of the page at the end.
Then we exported, cleaned combined the list of names and combined it with the one containing the votes using Excel's function VLOOKUP. But before of this, we got rid of the "@" before the MPs' Twitter names.
I'll explain this later.
Use
=right(the cell, len (the cell)-1)
or
text to column with "@" as a separator.
Also some of the two datasets' names were different, so it was a matter of cleaning them, one by one.
The time was not on our side and we had yet to find a solution that would allow us to integrate a Twitter interface to each pop up of the Fusion Table's map. So we searched in the Research Fusion Table search browser a map containing the constituencies' geometry. We were lucky enough to find a map already in use with constituencies, names and even pictures of each single MP.
On the Twitter Developers page it is stated that Web intents
Below on the right, there is an example of window with the intent.
and this the url of the window showed in the image above
As it can be seen there is a "in_reply_to="a code". As we do not want to reply but to directly send a tweet, we cancelled all that part until "related=an Twitter name", which is what interest us. The rest is can be deleted as well, so to have
https://twitter.com/intent/tweet?in_reply_to=32579487338074112&related=SteveMartinToGo
Look at the Twitter name of the example after "related=". Understand why the "@" should be taken off?
Opened our Google Docs dataset, we created another column and in the first cell wrote "https://twitter.com/intent/tweet?related="&the cell where the Twitter account is&"%23HS2".
The last part - "%23HS2" - is to indicate the #HS2 hashtag in the tweet.
Pasted the same formula along the column we'll have all the Twitter intent for any account.
Another column will host the colour of the geometry by vote of MPs, as in the Fusion Table we took we were unable to modify the colours because was created by another user.
Choose the HEX code to represent each kind of vote - yes, no, absent - and write the colour's code in each cell of the new column, after #. We simply called the column "Vote".
We then merged the Fusion Table we found earlier with the Fusion Table version of the Google Docs spreadsheet by names of the constituencies, to realise the map. On the Change feature style of Fusion Table, we then go on Fill colour/ Column/Use a specific column and select the "Vote" one.
And basically that's all.
Just one thing.
I have personally modified the format of the urls showed in the Fusion Table window that connect to the intents, transforming it in the nicer "Tweet me", instead of an ugly and dry url.
It is possible to do that by tweaking the HTML of the window.
Here the tutorial from Google on how to customise the Fusion Table's windows.
Let me know if and how you can do better in the comment below!
Time is always precious in journalism and not anybody has enough of it to spend learning how to code in REGEX - which I strongly recommend.
Outwit is perfectly designed to scrape several pages with its "forward" function.
But be careful, it seems that it stops when arrives at the ninth - any clue about the reason?
So we had to manually write 10 in the url of the ninth page in order to keep the scraper going and the same each 10 pages, for no apparent reason.
Fortunately the urls are in a good format, with the number of the page at the end.
Then we exported, cleaned combined the list of names and combined it with the one containing the votes using Excel's function VLOOKUP. But before of this, we got rid of the "@" before the MPs' Twitter names.
I'll explain this later.
Use
=right(the cell, len (the cell)-1)
or
text to column with "@" as a separator.
Also some of the two datasets' names were different, so it was a matter of cleaning them, one by one.
The time was not on our side and we had yet to find a solution that would allow us to integrate a Twitter interface to each pop up of the Fusion Table's map. So we searched in the Research Fusion Table search browser a map containing the constituencies' geometry. We were lucky enough to find a map already in use with constituencies, names and even pictures of each single MP.
Creating a Twitter intent with MPs account names
How is it possible to create a tab in a Fusion Table map window? Well we still don't know, but it is probably possible with JQuery. Learning how to code in JQuery would not have been time effective, so we asked Google about any way to at least have a Twitter interface on a page reachable through a url, and we found the Web intents.On the Twitter Developers page it is stated that Web intents
"Make it easy to bring interactivity to Tweets that you display on the Web"They are basically those little windows that appear sometimes when you are on a website and want to share something from it. They are in fact used as a tool to share contents from websites.
Below on the right, there is an example of window with the intent.
and this the url of the window showed in the image above
"https://twitter.com/intent/tweet?in_reply_to=32579487338074112&related=SteveMartinToGo,twitterapi&original_referer=https://dev.twitter.com/docs/intents#tweet-intent"Now it is just a matter of making it work for all the MPs' accounts we have.
As it can be seen there is a "in_reply_to="a code". As we do not want to reply but to directly send a tweet, we cancelled all that part until "related=an Twitter name", which is what interest us. The rest is can be deleted as well, so to have
https://twitter.com/intent/tweet?in_reply_to=32579487338074112&related=SteveMartinToGo
Look at the Twitter name of the example after "related=". Understand why the "@" should be taken off?
Opened our Google Docs dataset, we created another column and in the first cell wrote "https://twitter.com/intent/tweet?related="&the cell where the Twitter account is&"%23HS2".
The last part - "%23HS2" - is to indicate the #HS2 hashtag in the tweet.
Pasted the same formula along the column we'll have all the Twitter intent for any account.
Another column will host the colour of the geometry by vote of MPs, as in the Fusion Table we took we were unable to modify the colours because was created by another user.
Choose the HEX code to represent each kind of vote - yes, no, absent - and write the colour's code in each cell of the new column, after #. We simply called the column "Vote".
We then merged the Fusion Table we found earlier with the Fusion Table version of the Google Docs spreadsheet by names of the constituencies, to realise the map. On the Change feature style of Fusion Table, we then go on Fill colour/ Column/Use a specific column and select the "Vote" one.
And basically that's all.
Just one thing.
I have personally modified the format of the urls showed in the Fusion Table window that connect to the intents, transforming it in the nicer "Tweet me", instead of an ugly and dry url.
It is possible to do that by tweaking the HTML of the window.
Here the tutorial from Google on how to customise the Fusion Table's windows.
Let me know if and how you can do better in the comment below!
