Skip to content

Instantly share code, notes, and snippets.

@elipousson

elipousson/how-to-socrata-twitter-bot.md

Last active November 18, 2017 00:57
Show Gist options
  • Save elipousson/0ad4aa8a1e92d386e123e010209ebe95 to your computer and use it in GitHub Desktop.
Save elipousson/0ad4aa8a1e92d386e123e010209ebe95 to your computer and use it in GitHub Desktop.
Download ZIP
How to make a simple Twitter bot with open data
Raw
how-to-socrata-twitter-bot.md

How to make a simple Twitter bot using data from a Socrata open data portal, Google Sheets, and Zapier?

This is how I created two simple Twitter bots (@bmore_vacant and @bmore_demo) using demolition permit and vacant building notice data published on Open Baltimore – a Socrata-powered data portal. This how to uses a similar approach to the process I described in this tutorial about creating a property information system with Socrata, Google Sheets, and CARTO.

See also:

  1. Set up a filtered view in Socrata.
  • Are there problems with the data provided by the city, such as missing or inaccurate information? Consider contacting them to inform them about the issue and ask for the problem to be corrected.
  1. Copy the URL for exporting the filtered view to CSV.
  2. Import the CSV download into Google Sheets via URL. Learn more about how to import CSV or JSON data from a URL into Google Sheets.
  3. Clean up the data in Google Sheets as necessary. For @bmore_demo and @bmore_vacant, the clean up in Google Sheets included:
  • Split the location field into latitude and longitude
  • Generate URLs for Google Street View (see this stackoverflow thread for information on how to build your URL) and for the Google Street View Image API (requires an API key)
  • Convert the address and neighborhood from all capital letters to proper case (aka camel case)
  • Reformat the dates
  1. Create a filtered/sorted view in Google Sheets.
  • If too many rows are updated at the same time, Zapier cannot push the updates to Twitter. For this project, we created a "recent" filtered view and a "recent_sorted" view on separate sheets of the same Google Sheet where we imported the data from Socrata.
  1. Set up a new account on Zapier.
  2. Set up a new Twitter account. Learn more about rules and best practices for automation on Twitter.
  • Add a relevant profile and background image.
  • Add a link to the Google Sheet or filtered view of the data in Socrata.
  • Add a location and description that tells people what the Twitter account does.
  • Add a contact to the description so people can contact you with questions or concerns.
  1. Set up new Zap in Zapier.
  • Select Google Sheets (Updated Spreadsheet Row) as the trigger. Learn more about Zapier's Google Sheets integrations.
  • Select Twitter (Create Image Tweet) as the action. Learn more about Zapier's Twitter integrations.
  • Connect the Zap to the appropriate Google Sheet. Make sure whatever Google Account is the "owner" for your spreadsheet is linked to your Zapier account.
  • Create a template for your Image Tweets. Consider using emoji to convey additional meaning. For example, I used the 💰 emoji to highlight the estimated cost for demolition permits.
  • Test your Zap.
  1. Promote your Twitter bot to people you think might be interested.
  • For @bmore_demo and @bmore_vacant, I shared links to the bots on Twitter and on local Facebook groups for people interested in housing, historic preservation, and local politics.
  1. Monitor the Twitter bot for future problems.

Questions

What is a Twitter bot?

Learn more from Wikipedia or check out this list of The 17 best bots on Twitter on Quartz. Check out Cheap Bots, Done Quick! for another tool for building a different kind of simple twitter bots.

What is the point of a creating a Twitter bot with this kind of data?

These Twitter bots serve two goals:

  • informing local residents about specific properties that are subject to demolition permits or vacant building notices
  • raising broader awareness about the issues around vacancy, abandonment, and demolition

Does my city have demolition permit data available online? Or vacant building notices?

Check the U.S. City Open Data Census list of cities publishing construction permit data. Different cities keep track of vacant buildings in different ways – let me know if you know of a list I can reference here.

Do these Twitter bots stigmatize neighborhoods where demolition and vacant buildings are concentrated?

I hope not but I am concerned about this issue. If you have any suggestions for how I should address this questions, please get in touch with me on Twitter @elipousson or by email: pousson (at) baltimoreheritage.org

Screenshot of the @bmore_vacant Twitter account.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment