Macro Magic | DS23 Alteryx Macro Project

by Harry Osborne

This week's project for DS23 was to create a Macro tool on Alteryx specifically for working with data from the UK Government and Parliament Petition website (https://petition.parliament.uk/), format the data and then create a viz with the output on Tableau. This was our second day of working with Macros, so it's probably best that I start by explaining what they actually are.


What is a Macro?

In Alteryx, a Macro is a workflow or group of tools built into a single tool that can be inserted into another workflow. They are beneficial both by making repeatable processes easier and less time-consuming, but also allow users unfamiliar with the inner workings of Alteryx to use these tools without building them.

In my project, I decided to work with the petition data surrounding the "Right to Peaceful Protest" petition. The data is kept in a JSON (JavaScript Object Notation) file, downloadable straight from the site. Thanks to a brilliant blog post by a member of DS22, Simon Evans, this was made easier for me, as he outlined a method to bring in JSON data through a download, and then work with the data there.

Fun looking JSON data

Whilst I used Simon's method originally (you can see it here), I will outline a slightly different method, which allows you to go through the "Download" tool inside your Macro, instead of using the "Macro Input" tool. In this alternative method, the "Download" tool uses the URL for the JSON file (through a simple text input) to bring the data in directly from the site, making it a slightly more up-to-date way of extracting the data. Once downloaded, the data will bring in a "DownloadData" field and a "DownloadHeaders" field, both as truncated strings.

From L-R: Text Input, Download, JSON Parse, Filter

From here, it becomes a case of manipulating your data in the way most beneficial to you. As I was aiming to look at constituency-level data, it was important for me to be able to separate out anything relating to constituencies, numbers of signatures and population counts. This was best achieved by filtering away any data not relevant (as shown on the far right in the image above) via a CONTAINS function, and then cleaning the data step-by-step. This involved splitting and removing fields using the "Text to Columns" tool, before cleaning things up with the "Select" and "Data Cleansing" tools. Then, a handy trick with "Record IDs" and the CEIL function allowed me to use the "Cross Tab" tool to group all the data by their constituency.

From L-R: Filter, Text to Columns, Select, Data Cleansing, Record ID, Formula

For the final few manoeuvres in Alteryx, it came down to me wanting to use geographic data in conjuction with the cleaned JSON petition data. This was easily achievd via a join with a UK constituency postcode dataset I found through a quick internet search, before outputting the data to my finalised Macro.

L-R: Formula, Cross Tab, Join with Postocde data, Join, Select, Output

An often-neglected but crucial part of the Macro process is designing your own tool image - a step I took as seriously as the rest of the flow, inputting a protest-themed image to make my macro a bit easier to spot. All that was left was then to output the data into a .hyper file (so I could use it in Tableau), and save all my work! My final product in Tableau is pictured below, but this KPI dashboard was less the focus, and more crucial was understanding the process behind an Alteryx Macro.

My viz (link embedded)

What are my takeaways from building Macros?

  1. Build (and test) your flow as you work - if you don't trial the workflow as you progress, it will make the inevitable troubleshooting step far longer and more laborious. With each tool, run the flow and see if everything is working normally.
  2. Decide what your outcome is going to be beforehand - building a flow without an aim is pointless; know what your end functionality is supposed to do, and then decide whether you need dynamic actions embedded within the flow. My design didn't require any, so I never ended up trialling actions, and didn't lose any time as a result.
  3. SAVE YOUR MACRO WORK - this step is infuriating, and also the most important thing. Any changes you make to your Macro in its separate flow will not be enforced unless you save the flow first. Everyone will forget this step at some point, meaning everyone spends some time troubleshooting an issue that doesn't exist. Save your work!