Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
I was annoyed; I’d already been waiting on my own in some run-down London pub for 15 minutes when I received a text saying, “sorry mate I’m just leaving… but get me a drink, I’ll only be 1 min”. I was still annoyed, but no longer because he was late; nor that the bar was terrible; not even that I had to buy him a drink. I was annoyed because the place we’d chosen to meet happened to be right by his house, despite being miles away from mine. I know you probably think this means I’m an awful person to go for a drink with (and you’d be right), but fortunately this isn’t about that; this is for anyone who has agreed and thought, “yes, friends are the worst”, and is about “How can we build something to solve it?”
Like any rational human analyst, I left the dingy bar after finishing my dingy pint with my dingy mate and set about thinking how this problem could be solved. Before tackling the challenge at hand, I wanted to understand what the specific problem was, and the possible constraints defining it. I summarised them as being:
- Problem: two (or more) people need to decide where they want to meet
- Constraints:
- It must be possible for both to reach this place within each person’s defined travel time (clearly my mate’s max travel was 1 minute, I should take a hint)
- Each person may choose to drive, walk, cycle, or take public transport to the location
- The place should be a balance between even travel times and quality of venue
- The place type can be pre-defined (café, bar, restaurant etc.)
- You should be able to see the place and reviews before you go
I determined this was a location problem, though I knew I’d need place information to solve it - and rather than pre-select thousands of venues, I’d want a way to dynamically access a huge volume of place data in any location, whenever the app was run.
So – with my obvious personality problems aside (there are some things even Alteryx can’t solve) – it was clear that this was a spatial and API dilemma that could be solved using Alteryx.
The rest of this blog is dedicated to the app’s user interface; how it’s built from a technical perspective; and my next steps with it. I’ve attached the app to the bottom of this post. Please feel free to download and try for yourself. All feedback is welcome!
How to Use the App
Like many APIs, you’ll need to generate an access token to use the Google API. You can do that by signing up here.
Be sure to check both routes and places:
I have chosen to associate a card with my API (as doing so gives you free credit to do lots of calls for free), but be sure to read through any documentation here.
The app’s UI sits across three tabs:
Results
Results are provided in a report, showing: where your travel polygons overlap, the top-rated places within that spot, and some other interesting points and images...
How Does It Work?
There are a few key tools that are integral to the app. Running the app in debug mode allows you to see exactly what each of these tools are doing.
- You dropped two pins onto a map at the start of the app; these pins will need to be turned into spatial objects, so we can run spatial analysis on each person’s location. This is done using the ‘Create Points’ tool, by taking the XY coordinates and appending a spatial object.
- The points dropped are fed into a Route 360 API macro; this API leverages the spatial point created in Step 1, to create a polygon(s) around each person showing the areas that can be reached by them within their chosen transport type and time.
(note the blobs decreasing in size as you get further away; this often the case with public transport, where each blob is (in this case) a tube stop and the rings around them is how far the person could walk from each station in the allotted time they have left to travel)
- Step 2 above is run separately for each person, but really what we want is to combine these two polygons to see if the two journeys can overlap (you will go to the ball) or not. The ‘Spatial Process’ tool allows you to create a polygon showing the overlapping regions, as is shown below:
- What if – like above, we have multiple areas that overlap? To stand the best chance of finding a good place (and to minimise the number of API calls we’ll have to make further downstream), we can use the ‘Spatial Info’ tool to calculate the area of these four polygons (shown below) and just take the one with the greatest area for our place analysis as shown below:
becomes
- Now we have our ‘perfect meeting point’ (or polygon with a centre point), but we still need to search for places within it that fit our criteria (in this case, a bar). The search radius you defined at the start of the app comes into play here, acting as a ‘distance from’ the centre point in our Google Maps API call.
This information, along with the X and Y of the centre point and place type, allows us to build a search URL, which will look something like this:
https://maps.googleapis.com/maps/api/place/nearbysearch/json?location=518746104,-0.110362243&radius=500&keyword=Bar&key=yourGoogleAPIkey
This URL is effectively the search you might have done in Google Maps. We need Alteryx to do the work beforehand to find the perfect meeting lat long, but also, doing it via Alteryx allows us to return many more results than we’d see directly in Google, not to mention, see a lot more information about each place.
We can input our newly built URL into a ‘Download’ tool, which, along with ‘JSON parse’ allows us to see all the data in a structured way:
- Additional reporting and blob convert tools allow us to build out tabs for the user, to click through a list of places ordered by Google rating, see a map showing their locations, and view some example pictures:
- Reporting tools are then used to generate the maps and tables you see in the final output:
Next Steps
Recent refusal to meet anyone anywhere other than ‘the middle’ means I’ve got a lot of free time on my hands to test, improve, and add features. A couple of features top of mind I’m looking to include are, but not limited to:
- User keyword search. E.g. you might want a restaurant, but a free text field which is added to the google URL would also allow you to state that the restaurant serves ‘fish and chips’
- Actual travel times for each person; the google maps API allows you to return travel time in minutes and miles, and can even show step by step directions
- Multiple (>2) person functionality. Apparently, some people have more than one friend, so it would be useful to be able to state where multiple people are in the UI
- Ranking by review is good in theory, but poor in practice. If I’m meeting friends from work (assuming everyone works central), the app will often suggest meeting for a pint at The Ritz…
- General tidying of the UI: e.g. in the dropdown selection you can see a range of business types that Google will search for you; naturally this lends itself well to bars and restaurants:
… I assume (and hope) that nobody would ever want to use this to find the best rated dentist or zoo between them. But who am I to judge.
Last Orders
I’ve found that though the use case here is clearly ridiculous, the logic itself has easily been applied to client work with more relevant and useful business use cases.
As you can see, this is a work in progress; I’d welcome any feedback on the app, or to hear from you if you want to work together on this or something similar. I’d be happy to meet at a nearby zoo with a rating above 4.7 and a travel time of less than 27 minutes.
