We were excited to show off all the new features of Looker 7. And we couldn’t think of a better way to do that than by using a little data for good. So, we started brainstorming useful things we could do with data and a big crowd.
Eventually, we hit on the idea of mitigating JOIN’s climate impact by offsetting the carbon footprint of attendees’ travel to the conference. But in order to accurately calculate that footprint and measure our environmental impact, we needed to do some data collection.
We created a little demo for our keynote, where I asked attendees to fill out a quick survey about where they’d traveled from and what mode of transport they’d used. We got more than 750 responses in under 4 minutes and analyzed the results onstage.
We found that the vast majority of respondents had flown to get to JOIN, but of the local folks, public transport was the most popular option. The total carbon footprint of their travel was 234,630 kg, with 98% of that from air travel. With that calculated, we doubled the footprint (since attendees eventually need to get home), and used a carbon offset app built-in Looker’s new application framework to purchase Renewable Energy Credits (RECs)1.
Finally, we wanted to let everyone in the audience know about their footprint, so we sent everyone a personalized message about how far they’d traveled, how much carbon they’d emitted, and that Looker had offset it. We used the Twilio action in Looker’s Action Hub to send hundreds of messages in just a few seconds.
To add a bit of extra spice, we also tweaked two random participants’ messages to let them know that they’d won a raffle, awarding one a Looker bomber jacket and the other a “zero-emissions vehicle” (i.e. a Looker skateboard).
The demo was a lot of fun and a great way to lessen our community’s impact on the planet. You can watch the whole thing below, or keep reading for the technical details on how it all worked.
At a high level, the setup for measuring the environmental impact from travel for JOIN was pretty straightforward. First, we needed a survey tool that we could grab data from very quickly. We could then insert that data into a database, put Looker on top, and build out the extra functionality we needed in Looker using the Cloverly and Twilio APIs.
Here’s what it looked like:
I’m a big fan of Typeform, primarily because it’s easy to set up surveys that look great, and, critically for my use case, it works beautifully on mobile. If you want to try out the survey yourself, it’s still available at www.join2019.live
Typeform also has a nice set of connectivity tools, including an easy-to-use API. Someone was kind enough to create a handy Python SDK, so that made my life even easier.
Because I wasn’t going to process a ton of data, I decided to write a simple Python script to get the data and insert it into a Postgres database on AWS RDS. Typeform has a bunch of good options where it’ll automatically send responses to a webhook, but I was nervous about the latency and what would happen if my little script went down for some reason.
So I opted to poll Typeform’s responses API instead, hitting it roughly twice each second (their rate limit) to check for new responses. The Python script ran continuously on a little EC2 instance, and flattened the JSON responses and inserted them into the Postgres database any time it received responses. I didn’t need to worry about deduping or out-of-order delivery because I was using a newer version of Postgres, which can ignore rows that already exist.
Once I had the survey data flowing into a database, I put Looker on top of it and built a simple model for the data. I also added tables to the database with information about global airports (including their lat/long and International Air Transport Association code) and US ZIP codes (with the latitude and longitude of their centroid).
Since I knew the lat/long of the venue, I was able to use Looker’s built-in functions to calculate the distance from each individual’s departure location to the venue. With a little research on the carbon emissions per mile per passenger of different modes of transport, I was able to calculate the carbon footprint of each attendee easily.
Looker’s new dashboards provided a beautiful way to visualize the data in real-time as it came in and served as an efficient method for measuring carbon footprint. Looker’s dashboards have long had the ability to refresh tiles automatically, but the new dashboards look way better doing the refreshes. On the dashboard, I set tiles to refresh every second or two. And since the underlying database was more than powerful enough to handle this data, it was able to return fresh data to Looker in milliseconds.
With the data in and processed, the next step was to actually buy offsets. The new application framework coming in Looker 7 was critical here since it allows us to build mini-apps and include them right inside Looker, where they have full access to Looker’s data via API.
Notifying each attendee about the calculations we’d done on their data and the offsets we’d bought on their behalf was the last piece of the demo. Since we’d already collected users’ phone numbers in the survey, we knew how to get in touch.
We took one of the existing Twilio actions that Looker provides in our Action Hub and tweaked it to send a unique, dynamically generated message for each user instead of sending the same message text to all users. This functionality takes advantage of the open nature of the Action Hub, where anyone can create their own actions and deploy them to their own little Action Hub. (But don’t worry, we’ll be releasing this tweaked version of the Twilio action soon, as well).
Since we were still inside the Looker platform when using the little offset app, we added a button to the app that uses Looker’s API to trigger the action. After a useful warning pop-up that we were about to text hundreds of people, we triggered the text messages.
The final fun piece was our JOIN raffle. Since every Typeform response comes with a unique, pseudorandom token, I took the middle 64 bits of each response’s token and awarded the prizes to the responses with the maximum and minimum values. This was simple to do in a derived table in Looker, which allowed me to automatically replace these attendees’ text messages with directions on how to claim their prizes.
That’s it. A little Python, a bunch of ecosystem partners’ tools, and a powerful data platform (plus a lot of help from my talented colleagues), and we had a demo on measuring environmental impact with data that was both fun and good.
1 Because business travel is a Scope 3 emission, the EPA recommends that it be offset with carbon offsets, rather than RECs (which should primarily be used to offset Scope 2 emissions). However, the “Sustainability as a Service” API from Cloverly we used currently only sells RECs, not carbon offsets. So to be on the safe side, instead of purchasing 470,000 kg of offsets, we purchased 2.2 million kg of RECs.