Author: Jason Claunch - President
Company: Catalyst
Business Partner: Slalom Consulting - Sean Hayward & Marek Koenig
Awards Category: Best Use of Alteryx for Spatial Analytics
The developed solution used many of the Spatial Analytics components available within Alteryx:
Describe the problem you needed to solve
Retail site analysis is a key part of our business and was taking up too much time with repetitive tasks that could have been easily automated.
Describe the working solution
To support selection of best-fit operators, Catalyst partnered with Slalom Consulting to develop a tool to identify potential uses to target for outreach and recruitment. Previously, we would have to manually build demographic profiles using tools like qGIS, ESRI, and others, but found the process to be cumbersome and quite repetitive. Demographic data was acquired at the trade bloc level, which was too granular for identify target locations and would not mesh well with the retail data.
Alteryx and its spatial capabilities was used in a few ways:
1) Minimize our retail data selection from the entire US to a selected state using the Spatial Match tool.
2) Create a demographic profile for each retail location that consisted of data points such as median income, population, daytime employees, and others. The data was aggregated around a 3 mile radius of the specific retail location with an Alteryx Macro composed of a Trade Area, Grid Tool, Spatial Match, and Summarization tool.
3) Using a Map Input, the user selected an area to profile and candidate retailers were output for further review.
4) After selecting specific retailers to do an in-depth analysis on, Alteryx would score all possible locations by distance (Drivetime Analysis) and by score (proprietary weighting of various demographic attributes). The profiled results were then used to build a client presentation; the automated profiling tool saved us countless hours and allowed us to deliver more detailed analysis for our clients.
Describe the benefits you have achieved
Using Alteryx was a massive time saver, the tool that we built took a process that normally required at least 8 hours of manual work down to merely a few minutes. This has directly benefited our bottom line by allowing us to focus on more key tasks in our client outreach and recruitment. A return-on-investment was immediately realized after we were able to close a deal with a major client using our new process.
Author: Ryan Bruskiewicz (@rbruskiewicz) - Management Consultant
Company: Qral Group
Awards Category: Best Use of Alteryx for Spatial Analytics
I am using spatial analytics in Alteryx, in combination with healthcare utilization data for drugs and procedures published by Centers for Medicare & Medicaid Services (CMS) and shape files on data.gov (ZCTA, US primary roads) to optimize geographic territory alignment for sales representatives in the life sciences industry.
The process leverages several spatial analytics tools in Alteryx, including Distance/Driving Distance, Find Nearest, Create Points, SpatialObjCombine in Summarize Tool, Spatial Match, and Location Optimization Macros. The Alignment Optimization workflow outputs data files for visual mapping, analysis and summary reporting in Tableau, and outputs files to a tool called TerritoryMapper for manual refinement of territory zip code boundaries.
Describe the problem you needed to solve
The Initial Business Problem
The business problem solved is optimization of sales force territory alignments. The objective is to create territories that are balanced in terms of workload and sales potential and consider geographic constraints and travel time. The approach developed in Alteryx efficiently optimizes geographic alignments in a matter of minutes without costly purchases of third party data sources:
Additional use cases solved
Leveraging the alignment optimization workflow as an initial platform, solutions to address other related business problems have been developed and integrated into the workflow:
Together, this set of solutions provides a suite of tools to automate and optimize field sales force deployment.
Drivers and applications
These solutions are used on projects to support Business Development and/or Commercial Operations teams within life sciences companies. The alignment optimization workflow has also been generalized to enable Sales Operations teams in any industry to design sales territories by taking an individual company’s customer target list and demand/sales history as an input.
Internally, Qral Group has leveraged this tool to create territory alignments for many combinations of sales force size and therapeutic area. With this broad set of alignment scenarios, we can quantify how much of territory alignment is effectively “objective” due to population distribution vs. variable for specific therapeutic areas due to regional differences in disease incidence and prevalence. We found that there is typically an 80-85% overlap of territory alignment, regardless of therapeutic area!
Optimal Territory Alignment by Therapeutic Area and Sales Force Size
Describe the working solution
The working solution integrates ~12 GB of data representing ~12 billion healthcare claims from the following data sources:
The alignment optimization tool consists of two repeatable workflows.
The excel user input form allows a user to specify:
The optimization workflow also includes macros to accomplish certain complex operations, batch processes, iterative processes, and optimizations, such as:
The ZIP-Territory alignment and sales force hierarchy is output to Tableau to visualize the geographic alignment and report on summary statistics (e.g. number of customers, sales, workload) for each territory and span of control for managers.
Describe the benefits you have achieved
Alteryx has had a significant impact on our consulting project work through time savings and cost reduction. For this geographic territory alignment use case, specifically:
At Qral Group, we have realized many benefits of leveraging Alteryx Designer for other use cases as well. We regularly analyze large volumes of healthcare claims data. We can use Alteryx to process hundreds of millions and billions of records without heavy investment in infrastructure. This capability allows to better understand treatment pathways and patient journey, patient utilization, referral networks, physician and patient segmentation, payer cost impacts, and patient identification for rare diseases.
Author: Andy Moncla ( @AndyMoncla ), Chief Operating Officer & Alteryx ACE
Company: B.I. Spatial
Awards Category: Best Use of Spatial
With Spatial in our company name we use Spatial analytics every day. We use Spatial analytics to better understand consumer behavior, especially relative to the retail stores, restaurants and banks they use. We are avid proponents and users of customer segmentation. We rely on Experian's Mosaic within ConsumerView. In the last 2 years we have invested heavily in understanding the appropriate use of Mobile Device Location data. We help our clients use the mobile data for better understanding their customers as well as their competitors' customers and trade areas.
Describe the problem you needed to solve
Among retail, restaurant and financial services location analysts, one of the hottest topics is using mobile device location data as a surrogate for customer intercept studies. The beauty of this data, when used properly, is that it provides incredible insight. We can define home and work trade areas, differentiate between a shopping center’s trade areas versus its anchors, understand shopping preferences, identify positive co-tenancies, and, perform customer segmentation studies.
The problem, or opportunity, we wanted to solve was to:
1. Develop a process that would allow us to clean/analyze each mobile device’s spatial data in order to determine its most probable home location
2. Build a new, programmatic trade area methodology that would best represent the mall/shopping center visitors’ distribution
3. Easily deliver the trade areas and their demographic attributes
And, it had to scale. You see, our company entered into a partnership with UberMedia and the Directory of Major Malls to develop residence-based trade areas for every mall and shopping center in the United States and Canada – about 8,000 locations. We needed to get from 100 billion rows of raw data to 8,000 trade areas.
Describe the working solution
Before I get into the details I’d like to thank Alteryx for bringing Paul DePodesta back as a Keynote Speaker this year at Inspire. Paul spoke at a previous Inspire and his advice to keep a journal was critical to the success of this project. I actually kept track of CPU and Memory usage as I was doing my best to be the most efficient. Thanks for the advice Paul.
Using only Alteryx Spatial, we were able to accomplish our goal. Without giving away the secret sauce, here’s what we did. We divided the task into three parts which I will describe below.
1. Data Hygiene and Analysis (8 workflows for each state and province) – The goal of this portion was to identify the most likely home location for each unique device. It is important to note that the raw data is fraught with bad data, including common device identifiers, false location data and location points that could not be a home location. To clean the data, nearly all of the 100 billion rows of data were touched dozens of times. Here are some of the details.
a. Common Device Identifiers
i. The Summarize tool was used to determine those device ID’s, which were then used within a Filter tool
ii. Devices with improper lengths were also removed using the Filter tool
b. False Location Data – every now and again there is a lat/long that has an inexplicably high number of devices (think tens or hundreds of thousands). These points were eliminated using algorithms utilizing the Create Points, Summarization and Formula tools, coupled with spatial filtering.
c. Couldn’t be a Home Location – For a point to be considered as a likely home location, it had to be within a populated Census Block and not within other spatial features. We downloaded the Census Blocks from the Census and, utilizing the TomTom data included within Alteryx Spatial, built a series of spatial filter files for each US state and Canadian province. To build the spatial filters (one macro with 60+ tools), we used the following spatial tools:
i. Create Points
ii. Trade Area
iii. Buffer
iv. Spatial Match
v. Distance
vi. Spatial Process Cut
vii. Summarize - SpatialObj Combine
Once the filters were built all of the data was passed through the filters, yielding only those points that could possibly be a home location.
Typically, there are over one thousand observations per device, so even after the filtering there was work left to be done. We built a series of workflows that took advantage of the Calgary tools so that we could analyze each device, individually. Since every device record was timestamped, our workflows were able to identify clusters of activity over time and calculate the most likely home location. Tools critical to this process included:
The Hygiene portion of this process reduced 100 billion rows of raw data to about 45 million likely home locations.
2. Trade Area Delineation (4 workflows/macros for each mall and shopping center, run iteratively until capture rate was achieved) – We didn’t want to manually delineate thousands of trade areas. We did want a consistent, programmatic methodology that could be run within Alteryx. In short, we wanted the trade area method to produce polygons that depicted concentrations of visitors without including areas that didn’t contribute. We also didn’t want to predefine the extent of the trade areas; i.e. 20 minutes. We wanted the data to drive the result. This is what we did.
a. Devised a Nearest Neighbor Methodology and embedded it within a Trade Area Macro – Creates a trade area based on each visitor’s proximity to other visitors. Tools used in this Macro include:
i. Calgary
ii. Calgary Join
iii. Distance
iv. Sort
v. Running Total
vi. Filter
vii. Find Nearest
viii. Tile
ix. Summarize – SpatialObj Combine
x. Poly-Split
xi. Buffer
xii. Smooth
xiii. Spatial Match
b. Nest the Trade Area Macro within an Iterative Macro – By placing the Trade Area Macro within the Iterative Macro Alteryx allow the Trade Area Macro to run multiple scenarios until the trade area capture rate is achieved
c. Nest the Iterative Macro within a Batch Macro – Nesting the Iterative Macro within the Batch Macro allows us to run an entire state at once
The resultant trade areas do a great job of depicting where the visitors live. Although rings and drive times are great tools, especially when considering new sites, trade areas based on behavior are superior. For the shopping center below, a ring would have included areas with low visitor concentrations, but high populations.
3. Trade Area Attributed Collection and Preparation (15 workflows) – Not everyone in business has mapping software but many are using Tableau. We decided that we could broaden our audience if we’d simply make our trade areas available within Tableau.
Using Alteryx, we were able to easily export our trade areas for Tableau.
Build Zip Code maps.
For our clients that use Experian’s Mosaic or PopStats demographics, Alteryx allows us to attach the trade area attributes.
Describe the benefits you have achieved
The benefits we have achieved are incredible.
The impact to our business is that both our client list and industry coverage have more than doubled without having to add headcount. By year end, we expect our clients’ combined annual sales to top $250 billion. Our own revenues are on pace to triple.
Our clients are abandoning older customer intercept methods and depending on us.
Operationally, we have repeatable processes that are lightning fast. We can now produce a store or shopping center’s trade area in minutes. Our new trade methodology has been very well received and requested.
Personally, Alteryx has allowed me to harness my nearly 30 years of spatial experience and create repeatable processes and to continually learn and get better. It’s fun to be peaking almost 30 years into my career.
Since we have gone to market with the retail trade area product we have heard “beautiful”, “brilliant” and “makes perfect sense.” Everyone loves a pat on the back, but, what we really like hearing is “So, what’s Alteryx?” and “Can we get pricing?”
Author: Bill Lyons - Principal Data Scientist
Team Members: Trudy Fuher, Alana Welz, Arlyn Baggot
Company: Educational Media Foundation
Awards Category: Best ‘Alteryx For Good’ Story
The initial project has the potential to save this non-profit organization up to $2.2 million per year in streaming costs when recommendations are fully implemented. Other use cases improve internal efficiencies, communication, and productivity.
Awards Category: Best Use of Alteryx Server for Analytics Deployment
Alteryx Server automatically processes daily file downloads, weekly file downloads with decompression, decryption and bulk insertion, and monthly zip code DMA assignments. Other use cases support self-service imports, exports and reporting.
Awards Category: Best Use of Alteryx for Spatial Analytics
Alteryx spatial tools combined with Alteryx data is driving optimization of regional streams associated with DMAs.
Awards Category: Best Value Driven with Alteryx
Optimizing regional streams has resulted in at least $500,000 in savings since July 2016, with recommendations implemented so far. When all recommendations are fully implemented, savings could be $2.2 million per year or more.
Awards Category: From Zero to Hero
Even though we purchased our first Designer license in June 2015, as of early March 2016, we had not created a single workflow with Alteryx. We were considering not renewing our license. At that time, we got a new rep, Nick Glassner, who arranged for a couple of WebEx sessions with Alteryx Solutions Engineer Ali Sayeed to get us started on a real project. Within a few weeks, I recognized many more potential applications for Alteryx, and was off and running. I changed from a skeptic to an enthusiastic user. Analysis for this project began in mid-April and was completed in mid-May. We acquired Alteryx Server in June, and had the first phase of the implementation of this project running on a daily schedule by August. Other phases came online in November and in January 2017.
At that point, I was still the only person using Alteryx heavily in analysis and production. So, I began some internal workshops showing how to solve real-world problems with Alteryx. We now have 3 more internal users becoming productive with Alteryx, and are looking to hire another. Some of these users are also taking advantage of the “Enablement Series” offered by our new rep, Tim Cunningham.
Describe the problem you needed to solve
Initial business problem: Recent regulatory changes caused our national internet radio streaming costs to more than double, from less than $1 million to over $2 million annually. The goal was to find ways to optimize our streams to move usage from the national stream to our underutilized regional streams, and thus reduce our costs.
Other use cases, including their business challenges, solutions, and benefits, follow the solutions and benefits of this initial business problem.
Describe the working solution
Alteryx played a major role in analysis of the streaming data. Some of the regional streams were underutilized, while others exceeded their cost effective limits, so the first phase was to analyze the accuracy of IP address geolocation software to see what would be causing this. The website systems and the log analytic systems used different IP geolocation software (the websites used IP2Location, and the analytic systems used Maxmind) so we needed to know if one was better than the other, or if neither was adequate. However, these system are isolated from each other by firewalls, making direct comparisons impossible. Alteryx Designer allowed me to connect to three different SQL Server database systems and compare their data with a .csv file from another vendor being evaluated (NetAcuity).
This analysis made extensive use of Alteryx spatial matching and Alteryx spatial data, visualizing results with Tableau. It revealed some disturbing facts, including that the geolocation was very inconsistent between the systems. As an example, we found that less than half of the listeners to the New York City stream were even in the NYC DMA (Figure 1).
Additionally, we learned that only a little more than half of the listeners in the NYC DMA were listening to the NYC stream. (Figure 2)
The analysis also compared actual registered listener locations to the location reported by the various services. This showed that IP2Location was clearly inferior. (Figure 3)
But Maxmind returned a significantly higher number of unknown locations, both within the US, and even identifying the country. (Figure 4)
The analysis concluded with 16 recommended changes to systems, software, programming and contracts.
One of those recommendations was to unify both the websites and the analytics on the same and most consistently accurate IP address geolocation provider: NetAcuity. Alteryx supports the updates to the NetAcuity database by downloading the data from NetAcuity, decompressing, decrypting, and bulk inserting it into SQL Server. It does this on a weekly schedule in Alteryx Server, each time moving roughly 40 million rows of data in about an hour.
Primary workflow:
Supporting macros:
An Alteryx Server scheduled app then builds Calgary databases of the IP geolocation data.
Next, another Alteryx Server scheduled app applies that geocoding to the streaming log data.
Alteryx spatial data also supports Server scheduled monthly updates to keep zip to DMA to stream assignments up to date.
Describe the benefits you have achieved
4 of the 16 recommendations have been implemented to date, saving over $500,000 since last July, and an estimated $700,000 for 2017. More steps are in development, with a goal of saving $2 million per year.
Never before did we have a reliable and up-to-date zip code to DMA assignment process. We previously bought zip code to DMA data from Nielsen, but it was incomplete and quickly out-of-date.
Other Significant Alteryx Use-Cases
1. Transmitter location identification
2. Log file FTP download
3. User import of Excel into SQL Server
4. Tealium reporting
5. Studio automation logs
6. Record of donor communication
Benefit: Listener Services communicators can now intelligently communicate with donors.