Past Analytics Excellence Awards

Excellence Awards 2017: Ryan Bruskiewicz - Best Use of Alteryx for Spatial Analytics

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.

7 Comments
LeahK
Alteryx Alumni (Retired)
Status changed to: Inspire 2017 Las Vegas Award Entry
 
ahadjini
5 - Atom

This is great!

RouzL
Alteryx Alumni (Retired)

This is fantastic Ryan. Very nice use case. 

LeahK
Alteryx Alumni (Retired)
TR
8 - Asteroid

@rbruskiewicz this is excellent, and thanks @LeahK for submitting. I'm interested in the specific piece:

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)

Can you delve into more about how you clustered locations and created territory alignment based on geographic constraints? I'm looking at 1,000+ store locations and interested in presenting optimized territories but have defined clusters (20 stores) and territories (~10). Any additional details you can provide would be awesome.

Thanks,
Todd

rbruskiewicz
5 - Atom

Hi Todd - 

 

The general approach to defining the initial territories is to use K means clustering using LAT/LON for each customer/store location, weighted appropriately by value/volume.  The output of the clustering is the "center" for each territory, with ZIP codes or customers aligned to the "closest" territory center based on straight line distance or drive time (if you have the data package).  

 

In order to consider geographic constraints (state lines, mountains, bridges, etc), you can incorporate "penalties" to the distances when determining closest territory.  For instance, imagine a case where a store is 50 miles from a territory center in a different state, but 60 miles from another territory center in the same state.  If you have preference to avoid crossing state boundaries, you can include a penalty factor of 2x for crossing state boarders.  The "distance" value for assigning stores to closest center then becomes 100 miles (2x50) for the territory another state vs. 60 miles (1x60) for distance to the territory center in the same state.  With this penalty factor, you are more likely to align territory boundaries to state borders.  Similar approach can be taken for other geographic constraints, with the penalty adjusted based on how strict you would like the rule to be.

 

Hope that helps - let me know if you have other questions.

 

lagueux_kerry
6 - Meteoroid

@rbruskiewicz - congrats on the win.  Well deserved.  

 

 

This looks like an amazing application.  Fun to see someone build a tool that incorporates cost of crossing states or other geographic features. 

 

Would it be possible to explain a bit about how you used the location optimizer to rebalance the territories?

 

Thanks for any insights here.

 

All the best,

 

Kerry