Challenge OverviewGiven a large amount data of the relationship of underwriter assignments to broker locations, we would like to gain insights through nice, neat visualizations.
BackgroundWe have been working to understand the relationship of underwriter assignments to broker locations as a way to improve new business results. While intuitive, we have been able to quantify that an underwriter is more successful at writing new business when they have a renewal book at that same location. Given the volume of data and the many intersections across underwriters and broker locations, we are struggling to find ways to visualize the data to make recommendations. We are looking for a new way to visualize the data we have.
Task DetailWe provide the data in an Excel file. Specifically, the columns are:
- Source – Data Source Name
- Master ID – Numerical ID assigned to broker specific cities
- Broker – Anonymized Broker Name
- BU – Organization Level 1 (Highest)
- SBU – Organization Level 2
- MU – Organization Level 3
- PC – Organization Level 4 (Lowest)
- cal_mth – Calendar Month
- cal_yr – Calendar Year
- Zone – Broker Geographic Zone (Highest)
- Region – Broker Geographic Region
- Area – Broker Geographic Area
- State – Broker State
- mpl_city – Broker City (Lowest)
- BDL Name – Anonymized Business Leader
- Segment – Broker location designation
- UW – Anonymized Underwriter Name
- UWM – Anonymized Underwriter Manager
- Subs – Count of new business opportunities submitted by broker to carrier
- Quotes – Count of new business opportunities quoted by carrier
- Binds – Count of new business opportunities bound by carrier
- Lookup Node – Anonymized code combining UW Name with Broker City
- Retention Lookup – Flag detailing whether UW has renewals at given Broker City
- NB GWP Lookup – Flag detailing whether UW has new business at given Broker City
- Retention Status – Duplicate of Retention Lookup
In this challenge, we are looking for any insights that can be gathered from the relationship between Underwriter Name and Broker City. Performance can be measured in terms of Subs, Quotes,Binds and Ratios:
- Hit Ratio - Binds/Quotes;
- Quote ration - Quotes/Subs;
- Conversion ratio - Binds/Subs.
Some potential questions:
- What is the ideal number of locations per underwriter?
- Does that vary by organization unit?
- Are the underwriters more effective for new business if they have a renewal book?