Past Analytics Excellence Awards
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 Size
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.
