Find the street crime in your area: Macros and APIs in Alteryx

Living in London, a city that's a dream destination for many, is an incredible experience. Its beauty and charm are undeniable, but like any big city, it has its challenges, one of which is the crime rate. Whether you're considering a move to/within London or planning a vacation, taking the time to research the crime rate of your chosen neighbourhood is always a good idea. While some areas may be safer than others, staying informed can ensure you have a pleasant and worry-free experience.

So today we are going to build a workflow in Alteryx that provides a quick report on the street crime of the area you are planning to stay. All you need is the postcode!

Let's begin!

We will use Police API which is an open-source API through which we can get data related to crime in the UK. For those who don't know, API is an acronym for Application Programming Interface. It acts like a messenger between different software applications. It allows them to communicate and share information or functionality, similar to how a delivery person (middleman) brings us food from our favourite restaurant (the website we want info from) without us having to go there ourselves.

Using API in Alteryx

To get the street crime data in Alteryx, we need a URL.

As you can see above, to access the street crime API URL, we require the coordinates of the postcode and the month/year. I've retrieved the coordinates for my London residence and adjusted the date to fetch street crime data for my area in January 2024.

I gave this URL in a text input tool and connected it to a download tool (found under developers tab) and configured it as below.

The download tool downloads the data from the URL in a JSON format. It also returns the status of the API.

Now we need a way to read the downloaded data. Luckily, Alteryx has an easy solution to read JSON data - the JSON Parse tool (found under developers tab).

Let's configure it and look at the results.

It returns the status showing our API was able to fetch records successfully. It also returns the name and value which contains information about street crimes in my area.

Let's perform some more data transformations so that our data looks like the one below.

Split the JSON_name column by using the Text to Columns tool. Then, Cross-Tab the data to get columns with each row representing a crime.

Yay! We successfully got the data for street crimes. But hey, who would make the effort to find the coordinates each time and then form a new URL.

Let's make this workflow a bit more configurable.

Make your APIs configurable with Macros

We will use macros to take input from the user for the postcode, year and month. The macros will find the coordinates on its own and create a URL for us saving us the effort each time we want to look up a new postcode.

This is what our macros would look like.

Let me break it down for you.

1. The text input contains a random postcode, year and month.

2. We join it with a list of UK postcodes containing latitude and longitude information. You can download the file from here. This matches the postcode and returns coordinates for the entered postcode.
3. Then, we use the formula tool to replace the parameters and form a customized URL

The result looks something like this:

Now let's include interface tools to be able to take input from the user.

4. Drag and drop the text box tool and action tool from the interface tab and configure them as shown below. The text input will allow the user to enter postcode and the action would update the value of that postcode in the text input tool.

5. Now bring in the dropdown interface tool and action tool. Configure them to allow the user to select a year from the dropdown.

6. Likewise, configure the month dropdown.

7. Add a macro output tool at the end.
8. Save the macro
9. Now, add the macro to a new workflow by right-clicking in the blank workspace, insert, macro, and select saved macro.
10. The altered workflow should look the below. The text input tool has been replaced by the macro. The macro prompts the user to enter postcode, year and month.

Awesome! You did it! You built a workflow to find street crimes in your area using Macros and API in Alteryx.

Bonus Section

Let's create a quick PDF report showing total crimes and the top 10 categories of crime that are most prevalent in the area.

To do this, we will use reporting tools.

The text in the report has been added with the help of the Report Text tool. The chart has been built using the Interactive Chart Tool. The reporting elements are then brought together using the Union tool and then, the layout tool places them all together nicely. Lastly, the Render tool at the end generates a pdf report for us.

Conclusion

In this tutorial, we learnt how to use APIs in Alteryx and how to make them more configurable using Macros. This unlocks endless possibilities for fetching data using APIs and getting as creative with the fetched data as you want.