For our first day of dashboard week we were given this data on service requests for The City of Melbourne and were given a brief to add spatial and supplementary data. I used this supplementary data on property prices.
The first thing I realised was that the service request data lists each request by suburb, however some suburbs are divided between multiple LGA’s so I cannot simply use spatial data for each suburb as it would also include other LGA’s.
To get around this I used this spatial data where The City of Melbourne has grouped suburbs into “small areas”. For example only a small part of Carlton North is part of The City of Melbourne LGA so they have combined their part of Carlton North with Carlton to create the small area “Carlton”.
I then used Alteryx to filter relevant data, combine data for required suburbs and also combine some special objects to match the rest of the datasets.
And here is my dashboard:
From the map and the “request efficiency vs property price” chart we can see that Melbourne has the highest median property price and Kensington the lowest, there doesn’t appear to be any correlation with request efficiency so let’s move onto analysing the service requests.
The “Avg Days to complete” chart shows the “Waste and street cleaning” category is the fastest to be completed with an average of 5 days, whereas the “roads and traffic” category is the slowest, averaging almost 20 days to be completed. If we click on the category we can drill down to see the sub-categories and see that this is because the traffic management sub-category is averaging over 90 days!
The most interesting insight comes from the requests over time chart. I would of assumed that efficiency would decrease if requests were increasing over time. However, the opposite is occurring, average days to complete requests are decreasing over time while amount of requests are increasing. So efficiency is improving, this is impressive!