1. Parsing in Alteryx
Let's explore a parsing example in Alteryx.
Our goal is to identify the most popular email domains among our customers, which will guide our decision when choosing an email marketing tool.
We'll start by importing the customer tables. As showned in the previous chapter, we'll use the wildcard import to load all customer tables simultaneously.
Upon reviewing the customer data, we notice the email field, which contains the customers' email addresses. We're particularly interested in the domain part, which is located after the "@" symbol in the email address, for example, msn.com or yahoo.com.
This is where string parsing becomes useful. We'll navigate to the Parse section in our tool container and select the Text To Columns tool. We'll connect this tool to our customer data input and configure its options.
We want to split the email field, so we'll select that field as our column to split. Our delimiter is the "@" symbol, which we'll input in the delimiter field. We're okay with splitting the field into two separate fields: one for the unique email prefix and another for the domain.
The option to increase the number of columns is very useful when we expect there to be multiple inputs in a single field. For example, a field that contains a list of where each item is split by a delimiter, or if we would want to discard the information after the delimiter.
Since we don't expect any extra characters after the domain, we'll choose the Error option as a precaution for any unexpected data. For the output root name, we'll use "email_".
After configuring the Text To Columns tool, we'll run a quick test to check the output fields. We see two new fields, email_1 and email_2, with email_1 containing the unique email prefix and email_2 containing the part we were looking for, the domain.
Next, we'll add a Summarize tool to group by the email_2 field and count the distinct number of customer_ids. We'll then use a Sort tool to order the results by the number of distinct customer IDs, attach a Browse tool, and run the workflow.
The results reveal that yahoo.com and gmail.com are tied for the most popular domains among our customers. Information that would be valuable when selecting our email marketing tool!
Now it’s your turn to try a few parsing examples in Alteryx!
2. Let's practice!