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.
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 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.
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.
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.
What are my takeaways from building Macros?
- 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.
- 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.
- 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!