Past Analytics Excellence Awards

Suggest an idea

Catalyst logo.pngAuthor: 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:

  • Trade Area – have user select target area to analyze
  • Spatial Match – combine multiple geospatial objects,
  • Intersection – cut objects from each other to create subject area
  • Grid tool – sub-divide the trade blocks to determine complete coverage of trade ring
  • Distance – use drivetime calculation to score and rank retailers in the vicinity

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.catalyst1b.png

 

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.

 

catalyst2-A.png catalyst2-B.png catalyst2-C.png

 

3) Using a Map Input, the user selected an area to profile and candidate retailers were output for further review.

catalyst3.png

 

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.

 

catalyst4.png

 

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.

QralGroupLogo.jpgAuthor: 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:

  • Traditional approaches to this business problem in the life sciences typically require purchase of third party healthcare drug/procedures utilization data for specific therapeutics areas or markets that represent a significant investment. Leveraging data published by CMS, we were able build a completely flexible model that can optimize territory alignment design for any market basket (i.e. any combination of drugs or healthcare procedures) without purchasing additional data.
  • Traditional approaches also typically require weeks or months to complete (as opposed to minutes or hours with our approach in Alteryx). The approach eliminates time spent purchasing/acquiring data, loading data, preparing/summarizing data, loading data into an alignment tool, manually defining and refining territory boundaries, and summarizing alignment results – these steps are fully-automated with our Alteryx workflow.

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:

  • Market sizing & value concentration curve
  • Physician segmentation by patient volume & specialty
  • Sales force sizing to determine optimal # of sales representatives for a given targeting strategy
  • Territory-level call plans to physicians/accounts

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 SizeQral1.png

 

 

Describe the working solution

The working solution integrates ~12 GB of data representing ~12 billion healthcare claims from the following data sources:

  • Flat files (.csv)
    • Healthcare provider universe
    • Medicare provider utilization & payment data for inpatient procedures, outpatient procedures, Part D prescribers, and Part B services
    • Demographic data for population by age, gender by ZIP Code
  • Shape files (.shp)
    • S. primary roads geodatabase
    • Cartographic boundary shape data for ZIP Code Tabulation Areas (ZCTA)

The alignment optimization tool consists of two repeatable workflows.

  • The first workflow integrates the data sources described above into a database that can be consumed by the alignment optimization algorithm, and is only run when raw data sources need to be updated (typically twice per year)
  • The second workflow connects to an excel-based user input form and leverages several custom-built Alteryx macros to execute the geographic territory alignment optimization. This workflow is run on-demand to create the territory alignment output.

The excel user input form allows a user to specify:

  • Drugs, services, and provider types to be considered markers for sales representative workload
  • Planned workload/call frequency by provider segment
  • Target workload range (min/max) for each territory for workload balancing optimization
  • Number of sales representatives, number of first-line sales managers, and number of second-line sales managers

The optimization workflow also includes macros to accomplish certain complex operations, batch processes, iterative processes, and optimizations, such as:

  • Clustering algorithm macro using native R-based clustering tool to identify territory workload centers
  • Adjustment of territory alignment to consider geographic constraints, such as US primary roads, driving time, and state boundaries
  • Batch macro to split heavy geographies into equal territories
  • Location optimization macro to optimally rebalance workload across neighboring territories by reassigning ZIP codes
  • Iterative macro to define locations for 1st line and 2nd line managers, and determine sales force hierarchy (assignment of reps to districts and regions)

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:

  • Time savings: Reduced time needed to complete analysis from 1-2 months to 1 day (savings across each analysis step from data acquisition, loading, preparation, alignment optimization, and results visualization)
  • Cost savings: For clients who do not already own prescriber-level data, reduced cost by eliminating need for a costly one-time data purchase from third party vendors to complete territory alignment analysis.

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.

andy_moncla_avatar.pngAuthor: Andy Moncla ( @AndyMoncla ), Chief Operating Officer & Alteryx ACE In-2CRev-28px-R.png

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. 

 

journal.png

 

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: 

  • Sort 
  • Tile 
  • Multi-row Formula 
  • Calgary Join and Input 
  • Formula 
  • Create Points 
  • Trade Area 
  • Distance 

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. 

 

trade area with ring.png

 

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. 

Tableau - trade area.png

 

Build Zip Code maps. 

 

Tableau - zip code contribution.png

 

For our clients that use Experian’s Mosaic or PopStats demographics, Alteryx allows us to attach the trade area attributes. 

Tableau - mosaic bubbles.png

Tableau - PopStats.png

 

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?” 

K-LOVE_logo.pngAuthor: 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).

 

Figure 1Figure 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)

Figure 2Figure 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)

 

Figure 3Figure 3

But Maxmind returned a significantly higher number of unknown locations, both within the US, and even identifying the country. (Figure 4)

 

Figure 4Figure 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:KLOVE-5.png

 

Supporting macros:KLOVE-6.png

 

 

An Alteryx Server scheduled app then builds Calgary databases of the IP geolocation data.KLOVE-7.png

 

 

Next, another Alteryx Server scheduled app applies that geocoding to the streaming log data.KLOVE-8.png

 

 KLOVE-9.png

 

 

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

  • Business Challenge: Property tax filings must be made with the appropriate jurisdiction for the location of the property. With normal property, the street address easily identifies that jurisdiction. However, radio transmitter sites are frequently in very remote locations where there is no street address, and frequently on tops of mountains, within a few feet of jurisdictional boundaries. Historically, property tax accountants manually used transmitter location geographic coordinates to search maps to identify state and county with which to file property tax forms. This very laborious process took a team of 3 or 4 people up to 8 weeks each year, and was fraught with error.
  • Solution: Alteryx Server scheduled app performs spatial match between transmitter geographic coordinates and Alteryx spatial data, precisely and accurately identifying and coding transmitter location state and county. Run time: about 15 seconds per day, automatically. This simple workflow took only a couple of hours to build and deploy.KLOVE-10.png

     

  • Benefit: Savings of up to 8 man-months of manual labor per year. Reduction in errors (this process identified more than 200 instances where the location was either undocumented or in the wrong jurisdiction; 2 were even in the wrong state).

 

2. Log file FTP download

  • Business Challenge: The system downloading new log files from content delivery network (CDN) daily was very fragile, requiring manual checks and restarts every few days.
  • Solution: Alteryx workflow app, scheduled to run daily, downloads list of available files, compares list to list of previously downloaded files, downloads new files, updates list of files downloaded.K-LOVE-11.png

     

     KLOVE-12.png

     

 

 

  • Benefit: Alteryx job has run without error for 8 months. Saves time (about an hour per week) monitoring and maintaining each week, but it is mostly a huge reduction in the "hassle factor." Time to develop was less than a couple months’ worth of manual corrections.

 

3. User import of Excel into SQL Server

  • Business Challenge: Data files from mobile app vendors come each month in Excel files and need to be imported to SQL Server. This import required a DBA to manually import, and was consequently a year behind.
  • Solution: Gallery app allows users to upload files themselves, automatically removes duplicate data, reports duplicates ignored, structure errors, and data imported.KLOVE-13.png

     

     

 

  • Benefit: Self-service of data import relieves workload of DBAs and allows users to have immediate reporting of data in Tableau. This process also revealed that the supplier had duplicate records that overlapped between months. This had created erroneous data of which we had not previously been aware.

 

4. Tealium reporting

  • Business Challenge: Connecting Tableau directly to Redshift was slow.
  • Solution: In-Db tools query Redshift database, filter, aggregate, and download to Tableau Server Data Source Extract. App is scheduled in Alteryx Server. 

     

    KLOVE-14.png
  • Benefit: Faster Tableau reports

 

5. Studio automation logs

 

  • Business Challenge: Log files have been inconsistent and incomplete, with gaps and overlaps, making downstream reports unreliable.
  • Solution: Download tool connects directly to REST API of studio automation software, parses the JSON, and inserts into SQL Server data warehouse. Scheduled in Alteryx Server daily.KLOVE-15.png

     

     

  • Benefit: Reliable data for reporting.

 

6. Record of donor communication

 

  • Business Challenge: Producers call donors to record their stories, logging that call in Google Sheets. Donors call back, talking to communicators in the Listener Services department who have no visibility to the Google Sheets, and there was no record in the donor system. Awkward conversations ensued.
  • Solution: Alteryx Server app scheduled to run every 5 minutes connects to Google Sheet, downloads the call records and insert records into the SQL Server donor system of record.KLOVE-16.png

     

  • Benefit: Listener Services communicators can now intelligently communicate with donors.