One of my customers had gotten an urgent question from his Real Estate Department : two of his leases were lapsing soon, and they wanted his approval on their proposal to move the business units using those locations. One of the proposed places was somewhat surprising: it was far from the current location, and seemed more remote from the customer catchment area, on the face of it. His unease made him call me, on a Friday afternoon: “Can you tell me, objectively, if I’m making a mistake?“. We discussed it over and came up with a plan that should reassure him. “When do you have to answer? – by next Friday, so having the stuff on Thursday would be ideal. – That should work, let me think it over and if you don’t hear from me on Monday, it means I’m on it.“
I knew his data very well, having worked with it many times before. The topic was a bit complicated, though, but not for the usual reasons : the problem was very well defined! But Excel was not going to cut it, that was clear.
What he needed was an estimate of the driving time he would loose, both for his employees to come to the office in the mornings and leave after their day, but also for them to travel to the building sites they were following. He wanted first to avoid loosing people by unwittingly making their life difficult – yes, the places were in a metropolitan area, and yes, there could be some nasty trafic jams! – and second not to loose too much in productivity, so that the lower rent would translate in a net gain.
The proper way to do this was to simulate those trips. What days, at what time, and where to. the simulation tool was going to have to be Google’s, as I did not have time to go find a clever way to obtain traffic-dependent journey times somewhere else, and that was a purely technical hurdle that I had fun overcoming. The where to part was more complicated.
I had gotten the list of all projects served by the two business units, with some address information. Feeding those to the French Address Database was the first order of business, as I knew the addresses were not clean. There was no address validation tool in the client’s system, and I was privately agreeing it was not a straightforward exercise, not when you sometimes build somewhere there was no street yet… I needed to know right away if the quality would be good enough, that would condition the whole project.
Well, it wasn’t. Not by that much, one could say : 3% of the addresses in the table were bad enough that I could not even link it to a city.
But a 3% uncertainty in productivity was more than enough to disrupt the analysis – I needed to be at or below 1%, and that level wrong locations could impact my calculation by more than 3%. So it would have to be the hard way…
In the end, I managed to deliver a meaningful report of my results on that Webnesday. I was satisfied that what I was writing was solid, and perversely happy about my conclusion: the moves were clearly worth it. There was almost no loss in productivity and the employees were going to be quite happy overall with their drive times. If I had been asked to answer without the support of a data analysis, I would have resisted the move, quite stubbornly. My client did not, and I was confident he was right.
project summary
- delivery time: 5 days – 3 business days.
- Probable delivery time for someone whose data I wouldn’t have known : 12 days
- Data used:
- Employee adresses (anonymous) from HR
- Building project addresses from the ERP system
- Locations for the current and projected premises
- French Base d’Adresse Nationale, database for all French postal addresses
- Google’s travel time calculator
- Main takeways
- progressive geocoding of the addresses, depending on the quality of data
- statistical estimate of driving times at significant days and times – 10 per trip
- Averaged and maximum effects for the employees
- Outils utilisés
- Python
- Excel
- Marktext