This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Web Applications Developer, Innovation Manager, Chief Analytics Officer
City of Tallahassee
Overview of Use Case
The city of Tallahassee is leveraging an advanced analytics ecosystem to join the ranks of "smart cities" by providing new services to citizens, replacing manual processes with automated programs, and fostering a culture of self-service business intelligence. The transformations highlighted include a partnership with the Community Redevelopment Agency to create new, spatially aware dashboards crafted from a series of disparate data sources, an algorithm to detect water leaks and automatically notify customers and field technicians of continuous water consumption, and a reporting component that allows engineering staff to focus on analysis instead of busywork, saving hundreds of hours per year.
Describe the business challenge or problem you needed to solve
The mission of Tallahassee's Community Redevelopment Agency, or CRA, is to implement strategies that facilitate the redevelopment of the central urban districts of Tallahassee through the allotment of both public and private resources. Typical projects funded by the CRA include commercial and residential housing improvements, infrastructure, transportation enhancements, they even hold special events in our neighborhoods.
A Map for the People!
In the past, the CRA has had difficulty reaching businesses and organizations that might benefit from the grants they provide. The general public's knowledge of the CRA was somewhat limited, and my goal was to change that by giving the CRA an internet presence makeover with an interactive map on their website.
Pipe Dreams in Tallahassee!
We needed to streamline the assessment of mains and hydrants prior to road resurfacing and to determine the health of the pipes and the fire hydrants in one area by looking up facilities and work orders.
Save Our Water!
Automating water leak detection and notification processes were a must for us. In the past, every day our analysts were getting a four, five, six-hundred-page PDF and manually looking, eyeballing, monthly totals to see if a customer has a leak. Not to mention, they had to look up the customer in the system, manually open a work order, find a technician, schedule an appointment, etc.
Describe your working solution
A Map for the People!
I immediately knew that Alteryx was the tool that I was going to need to solve this. With the capabilities of Alteryx at hand, I was able to execute this outline and accomplish this task much faster. After bringing the data together and prepping it, I grouped the good addresses into one file and the bad addresses into another one that can be reviewed later by the CRA. So, the file of addresses that passed the previous checks were then geocoded via an app hosted in our city Alteryx gallery.
I took advantage of several tools within the address and spatial tool categories, but specifically Cass to standardize the addresses, straight Geocoder to get the lat/long, Find Nearest and Spatial Match to ensure accuracy. I very quickly realized that this data lent itself to the creation of not only a map, but an interactive online dashboard. Using this dashboard, the public can analyze where, when, how the CRA was using funds allocated within our city. I weighed my options on how to best present the data and I considered both Esri product and Tableau.
Pipe Dreams in Tallahassee!
I pulled up Alteryx, and I connected to one of our Esri databases, geo database files, and pulled out all the mains and saved it all as a YXDB file, Alteryx database. In another canvas I dropped the Map Input tool on it and then used the database file I'd just created as a reference file. I did a little polygon and had the output tool showing all the pipes on a specific street. So, if the map is zoomed out you can see the City of Tallahassee is completely covered in blue, but you can quickly zoom in you and create a lasso. I used the reporting tools to get the layout done to deliver the report in a Word format.
Manage your inputs
Get your report - (“copy n’ paste” no more!!)
Save Our Water!
I had a database that understood about meters (Oracle), a database that understood about people’s addresses (Oracle), and a database that understood when the last time was we communicated with somebody (MySQL). And, I had 1,000 random Excel spreadsheets.
Below is the latest build of my workflow. It’s the longest one that I’ve built, but it does work. Also below is an email that an actual customer got out of the tool. I tried to give the customers details about their leakage, so they don’t think it’s a spam e-mail.
Not only they get this email with a text, but I also give them this graph that I built out of Tableau. Essentially, I did a webs service to do an export of a PNG out of Tableau, and then attached it to the email at go-time.
We also provided our field technicians and staff more information about the households they were visiting. Below is a dashboard that I built in Tableau. But basically, what that is, is against an aggregation of billing system data and meter data. Some other people in the organization heard about this, and now we've got a dedicated set of people who just deal with large accounts; hospitals, universities and state government buildings.
Describe the benefits you have achieved
Before our Alteryx solution we had an average of 55 days of continuous consumption BEFORE the customer received a paper notification about the leakage! And now it's on average about seven days between something starts happening and they get the email. Between 15 and 40 new leaks get detected a day, and even still, there's at least at any given time in Tallahassee, 8 or 10 people with leaks of 5 gallons a minute or more, and sometimes people with 15 or 20 gallons a minute!
Using Alteryx you can make a difference just by one unit of work, and that kind feel good sometimes, especially working in government or education, we don't always get some of the respect that maybe we deserve, but in this situation, thank you! I went to talk to this landlord guy and he's like "Yeah, renter's will always tell you something's wrong with the place. My tenant keeps on telling me you're emailing her.” They had a 10 gallon a minute leak, going for six weeks back in some back part of her lot, and the landlord was refusing to acknowledge basically didn't even want to answer the phone. So, this was kind of my way of being able to give people some information that was a little bit more meaningful, and that spoke to them visually.
The entire PowerPoint presentation can be found here.