Intro and inspiration
I have a small problem fixating. There isn’t usually much of a thought process that goes into these fixations, it is typically something like “Ah, I would like to get in better shape or I would like to eat less meat”, which spirals into a complete lifestyle change. I am in one of these fixations currently with data visualizations and analyses. I want to learn every shortcut, function, predictive model, and piece of technology I can use as tools in a tool belt. This fixation on data has me counting people getting on and off each subway stop, tracking my own sleep and exercises, counting macro nutrients; you name it, I am trying to find a data application for it. Driving insights and understanding data fundamentals is and will be, in my opinion, a critical function in every business. So thinking about data, finding fun insights, and creating a narrative have a functional benefit of investing in my own personal development.
So there I was one Saturday evening watching the movie, “You were never really here” (which is a definite recommend. Super dark and heavy, but really good). At one point in the movie Joaquin Phoenix informs his bosses via a payphone, he has just killed the man he was sent to kill. The second I saw him use the payphone I was fixated on it. I had to find out how many payphones still existed and ingest that into Tableau.
What started as an initial search to figure out and visualize how many payphones were remaining in the US, ended up evolving into a look at data privacy and security from a huge tech company and the power that holds, the benefit of wifi for low income neighborhoods, and a look at NYC infrastructure expenses and tax revenues, and a pit stop in Houston, TX for a modern day Robin Hood.
The exploration for insights
I first went to google to search payphone listings, which took me to the FCC website. After taking a look at the search UI, I didn’t feel that I would get the right return on my time fumbling in the dark here, so I went back to my google search. I did find this incredible story out of Houston, TX about a man making between $2.4M over 10 years (that is $240k/year!!) by having a bot make automated calls from the payhones he owned to federal agencies and some private businesses with collect phone numbers. I would love to have learned that the man was donating half of it to his local community like a modern day Robin Hood. Earning $240k in TX, that has no state income tax, feels to me like living the dream. The man owned up to his mistake and referred to his operation as “simple people”. I hope this story has a very office space type ending and this guy manages to keep some of the money he made from Uncle Sam
Anyways after striking out on the FCC website, I finally stumbled upon NYC Open data. This site is a dream. It has a very friendly search UI. I was able to find and search for various different tidbits of NYC data (i.e. tax revenue, graduation rates, etc.) which I am extremely excited to dive into. I recently went to a training session provided by the NYC Open Data team and they gave some great insight into the policy and the software platform, Socrata, that powers government data. It has inspired me to widen my search and data projects to not just this city, but others as well. I think it will be very interesting to compare similar data sets (911 calls, 311 calls, etc.) and see if there is potential to establish “KPI’s” or better understand which municipalities are serving their constituents the best. Using NYC Open data I was able to download data sets for public payphones, their conversion status to Link wifi stations, and Link Wifi usage data. I used the data to build 5 visualizations showing. I spent quite a bit of time exploring the data (first downloaded in November 2018, and the refreshed the data set in February 2019) and contemplating the “Story” I wanted to tell. Like most things the story is a little complex, but I will do my best to dom it down. Payphones in the US There are 100k payphones remaining in the entire US and 20% are in New York. The data set for Manhattan, has just under 10k payohones (9,133), which would leave another 10k floating around the state somewhere. After driving through Albany, NY to Killington, VT I am having a hard time imagining where the other 10k payphones could possible be, but I will leave that itch to scratch for another day. Don’t be evil In November 2014, New York City awarded the bid for deploying Link Wifi to a group consisting of Qualcomm, Titan, Control Group, and Comark. By June 2015, Titan and Control Group merged into one company known as Intersection, which is being led by investors from Sidewalk labs, which happens to be an Alphabet (formerly Google) subsidiary. Titan is especially of note, as they were found to be embedding bluetooth tracking devices into consumer phones without their consent. They ultimately removed them, but not exactly a warm and fuzzy data point. The New York Civil Liberties Union, has expressed their concerns over the content and volume of data collected, to our elected represenatives. As one local critic points out, Google already has access to our entire browsing history and will now consume physical tracking locations as well. Link has been ammicable to update privacy policy, but in 2018, a New York City College of Technology undergraduate student, Charles Myers, found that LinkNYC had published folders on GitHub titled “LinkNYC Mobile Observation” and “RxLocation”. Link has issued an order to GitHub to take this content down, but again not a warm and fuzzy. The viz around usage is where this gets interesting. Despite its slower than anticipated roll out, the sessions, unique users, and usage here is staggering. I have questions about what happened in April 2016 (odd gap in the bar chart), but over 2 plus years, these numbers are worth keeping an eye on. Services to those in need Despite the obvious issues regarding policy (and these obviously can’t be understated), there is a positive to be taken here. There has been a push to embed apps such as Aunt Bertha, which is a Yelp for social services. There has also been analysis on the lack of wifi in poor neighborhoods. I tried layering the income per capita map directly into my Tableau viz to show that the concentration of stations was not in the lower income neighborhoods, but I ultimately dropped it as I felt I was making the data fit a narrative, and the pre-loaded map. Return on Investment Summary I had a ton of fun analyzing this data, writing this story, and publishing this post. It took me way longer than I originally thought it would because of the depth of reading material on public payphone data and public utilities. For sake of reference, I watched the movie for inspiration here back in the fall of 2018 and it is the end of March. As an optimist, I am hopeful that those in low income neighborhood have access to this incredible utility and that our elected officials and system have the right checks and balances in place to keep us a free society. I also understand our system is run by humans, and as recent studies have shown, we are a predictably irrational species and no one is perfect. Key Learning’s 1.) Timelines – I need to give myself better estimates of time on data exploration and data analysis. In my planning for this I had written I would index on speed and content creation vs. perfection. I am hoping over time the effort on analyis and data exploration will condense with the thought pattern and writing all converging into great content that is easy (ish) to produce. 2.) WordPress – Really simple to use thus far. I am probably just scratching the surface of functionality but really loving the amount of “how-to’s” out there. I think I prefer video how-to’s vs. embedded and photo step by step’s which is something I may consider for future content. 3.) Tableau – Super dissapointing, but Tableau Public does not allow you to maintain motion in your vizzes. I spent a bit of time working on this aspect, but was bummed to not be able to directly embed it. I will take the positive here of having to learn ANOTHER new application/skill and am generally happy with having a solution for animating vizzes in the future. Wrap up At some point I would love to come back to this and layer in additional insights and information (getting a good income map or potentially the 311 calls for these would be interesting). Check out my full tableau analyses here. If you have a tableau license, I would recommend downloading it and checking out the motion in each dashboard. I really thought that was a cool function and plan on using it for the 9-5 at some point. Gotta crawl before I can run, but leave any comments or feedback! I am adding a google doc with most of the links I used to pull this together if you want to read some more write ups on this topic! Like I said at the beginning, I have a funny way of getting fixated. This is a learning space for me and I know there are some brilliant folks out there that are wired differently than myself so go nuts nerds (insert ogre nerd meme)
This could probably use a second look on who is paying expenses and collecting revenue, but I found it slightly humorous the expected revenue for the city. The total CAPEX, assuming most of the costs are a one time liability to setup infrastructure and you can depreciate labor as part of the overall fixed asset. This does bring up a decent accounting question, if you consider the “network” as one asset, as some of the original material suggests, can you depreciate the full $200M across, lets say 15-20 years? The total expected revenue is set at $1B, with the city getting $500M over a 10 year time period, which would be a 250% return on investment. Early estimates say the city is bringing just above $42M in the first two years each. I would be curious if these funds have already been dedicated to additional community services. To put this in context, NYC was expecting to give Amazon $1.7B in tax incentives, with estimated returned revenue of $27.5 over 25 years, with Amazon committing to an investment of $5B. The $5B was to be split between Virgina and NYC, so a little unclear if that was a 50/50 split, but assuming it was, let’s bring $27.5 up to a full $30B over 25 years. That is over a $1B in city revenue. It is a reach to say that all of this funding could have been used to support those most in need, but having additional capital, in my opinion, never hurts.