This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Question I have a list of zip codes, can I use Alteryx to determine the city they're in?
Answer The answer is yes! But with a few caveats. Zip Codes can be notoriously difficult to pair with cities they belong in because they exist in two forms; points and polygons. Point Zip Codes are generally associated with buisnesses or universities, while polygons generally encompass residential areas. Alteryx data does have Zip Codes with Points data; however, it is not immediately accessible and will require some configuration on the part of the user to get access to that level of data (See Related Links for an article on this process).
Zip Codes are also frequently adjusted, deprecated, and consolidated by the USPS, so depending on the age/vintage of the zip codes in your data as compared to that in the Alteryx Spatial Database, there may be some slight variation there as well. All said, users should still expect a reasonably high match rate.
To start, make sure you either have the Alteryx Data Package installed (available with a license), or you have the 2010 US Census data installed (available for free at http://downloads.alteryx.com/data.html).
With the the data installed, the first thing you will bring down is an Allocate Input Tool. Here you will choose the relevant dataset (Experian data or US Census) from the drop down, then check the box for Zip Codes under Pick Geography. If you have followed the advice from the related link, you can also choose Zip Codes w/ Points to include the expanded dataset.
From here you will use the Join Tool to join your data to the Allocate Input data based on your Zip Code field and the Key field of the Allocate Input data. [NOTE: the Zip Code and Key fields will both need to be either String fields or numeric fields. Either is fine as long as it is consistent]
The resulting data that comes from the J output anchor of the Join tool will contain all of your Zip Codes that matched those in the dataset. The field "Name" that comes from the Allocate Input is formatted as the 5 digit Zip Code, followed by the City name. From here a simple Text to Columns tool configured to create 2 columns and parse on the space, will create a field specifically for the City and an extra Zip code field that can be deselected and discarded as the data moves down your workflow.
See an example of the process in the attached v10.5 workflow below.
Calgary Regression Test Module Business Problem: If you have recurring updates on Calgary files then this module will likely be useful to you. When a Calgary fiIe is updated, it is imperative to be able to check the file for consistency. Depending on the number of columns and indices contained in the file, this can sometimes pose a challenge. Actionable Results:
Compare two versions of a Calgary file to determine if columns and indices remain constant
Complete regression testing through a simple application interface
Easily identify indices where counts have changed over 10%
Overview: Have you ever found yourself in a situation where you want to compare a newly built Calgary file with the previous iteration, in order to see if all the columns are still there, the indices are still there and that they give similar results as the last time? This Module does just that, and gives you a nicely edited document showing the results. For the querying, the app goes through all the indices that are in the Calgary directory, selects a random value from the file to use as the query value, and compares counts from the new and the old dataset, highlighting in red if the counts are more than 10% different. Vertical: Any Required Input: Calgary file with indices (previous and updated versions you wish to test) Application Process: For best results, run as a .yxwz instead of a .yxmd. In the application interface, just navigate to the two files and press Start. Be aware that if you have a lot of indices and a lot of data, it may take a while to run, so if you just want to try it out, start with something small.
Household Level Analytics Module
Business Problem: Businesses investing in new customer acquisition will be more successful in reaching prospects if they know which consumer profiles best describe their current customers. Compiling customer databases through marketing or loyalty card programs allows businesses to know who their customers are, as well as where they are located. When correctly leveraged, this type of information enables strategic and focused spending of marketing funds. Actionable Results :
Understand the demographic attributes of your customer base
Target new customers that fit the profile of your current customers
Ensure that your advertising and marketing funds are spent in the most effective way possible
Overview: Would you like to identify key demographic traits of your target customers? By appending household-level characteristics to a customer file, you can achieve the most accurate Consumer Profiling of both existing and prospective cstomers. This analysis allows business owners to target households that are not in their customer database, but are in their trade area and match the demographics of current customers. Customer acquisition using targeted households is a more efficient way to direct spending on advertising and marketing programs. Vertical: Retail Data Utilized: Customer file containing the following fields:
Customer Address containing street number, street name, city, state
Customer ZIP Code
Alteryx Data: Experian Household File Application Process:
The selected customer file is run through the Calgary Join tool using Experian household data to isolate the Experian records that match the customer records.
Fuzzy Matching is then performed to eliminate all duplicate records.
Finally, the wizard outputs the customer file with appended household-level data.