Measuring impact
Market access and analytic teams want to measure the impact their sales team is having on health systems and large networks. To measure impact, customers roll up sales and interaction data to each level of the hierarchy.
How does Network help in this situation? In most cases, the sales and interaction data are not stored in a Master Data Management (MDM) solution. However, even though Network does not store this data, MDMs play an important role in the sales roll-up process. Network can help you prepare and manage your hierarchies so that you can then send this data to your data warehouse to perform roll-ups. Every customer has their own methods and hierarchy levels to consider when rolling up the sales. Here are some of the methods we’ve heard:
-
Roll up sales from HCP to hospital and to the health system (3-level hierarchy).
-
Roll up sales at a 4-level hierarchy from the incoming HCO, hospital, regional health system and IDN.
-
Roll up sales to the health system directly from the HCP.
To do this, customers have approached this in two different ways:
-
Custom hierarchies - Define a custom hierarchy to roll up sales and interactions.
-
Reporting - Leverage Network's flat_hierarchy reporting table to query hierarchies and easily roll up the sales data.
Let's discuss these approaches in more detail.
Custom Hierarchies
In essence, custom hierarchies allow you to create your own version of the hierarchy. You can take the data from Veeva OpenData and manipulate it to how you see the health system or IDN. Some of the main use cases for customers have been to create sales hierarchies and therapeutic area-based hierarchies. The therapeutic area hierarchies can be leveraged when your organization has several business units that focus on different therapeutic areas (TA) and different accounts. For example, customers with different business units such as Neurology and Oncology divisions, may create separate TA hierarchies: Neurology Hierarchy and an Oncology Hierarchy.
In terms of the data model, these hierarchies are driven based on a custom field on the parent HCO relationship. For example, in the diagram below, our sales hierarchy and our neurology hierarchy are based on two different fields. Since we are using custom fields, as a customer, you have full control of how the custom hierarchies are built. To update these hierarchies, you can use a source subscription, data updater or DCR. Custom hierarchies are a part of your data model, so this data can be pushed to CRM as well as to your data warehouse.
Network can help you with custom hierarchy preparation, management, and visualization using Hierarchy Explorer. Instead of creating SQL queries to understand if you loaded the data correctly, you can use Hierarchy Explorer to do your checks. Custom hierarchies display as tabs so you can toggle between them to view the different hierarchies.
Reporting on flat hierarchies
The second approach that customers are using to roll up sales and interactions is via the flat_hierarchy reporting table. Using flat hierarchies is an easier approach to querying hierarchies; the data in the table contains direct and indirect active/valid relationships to each record in your Network instance. This allows us to skip levels in the hierarchy and be flexible when querying the data. This table and the reports generated from reporting can be pushed to your data warehouse via transformation queries as part of your target subscription.
To roll up sales and interactions, you might want to create a 3-level hierarchy. However, the structure of each health system is very different. For example a hospital can be on level 1 in one health system and level 2 in another. This can make querying by levels fairly difficult due to inconsistencies in hierarchy across health systems. Network can help with this, as the flat hierarchy table allows you to skip levels in the hierarchy. The query below allows for a 3-level hierarchy where the following can be defined.
Example: 3-level hierarchy
The query below allows for a 3-level hierarchy where the following can be defined.
-
Level 1: HCPs in your network instance
-
Level 2: Hospitals (directly or indirectly) connected to the HCP
-
Level 3: Health system (directly or indirectly) connected to the hospital
SELECT
flat_hospital.entity_vid__v as "HCP VID",
formatted_name__v as "HCP Name",
flat_hospital.ancestor_vid__v as "Hospital VID",
hospital.corporate_name__v as "Hospital",
flat_hospital.path_distance as "HCP to Hospital Distance",
flat_healthsys.ancestor_vid__v as "Health System VID",
healthsystem.corporate_name__v as "Health System",
flat_healthsys.path_distance as "Hospital to Health System Distance"
FROM
flat_hierarchy flat_hospital JOIN hco hospital
ON hospital.vid__v = flat_hospital.ancestor_vid__v JOIN hcp
ON flat_hospital.entity_vid__v = hcp.vid__v LEFT JOIN flat_hierarchy flat_healthsys
ON flat_healthsys.entity_vid__v = flat_hospital.ancestor_vid__v LEFT JOIN hco healthsystem
ON healthsystem.vid__v = flat_healthsys.ancestor_vid__v
WHERE
flat_hospital.entity_type__v = 'HCP'
AND hospital.hco_type__v IN (
'4:6',
'4:35',
'32_23',
'32_22',
'1_7',
'1_3',
'32_11',
'4_56',
'1_21',
'1_10',
'1_1'
)
and healthsystem.hco_type__v = '4:37'
Example: 4-level hierarchy
Another more complicated example of the first query is a 4-level hierarchy. This could be useful when you have sales data at the HCO level and you want to roll up sales to the health system level.
Rules for the 4-level hierarchy query:
-
Level 1: All HCO records in your instance
-
Level 2: Closest hospital to the record or repeat level 2
-
Level 3: Regional health system or health system/IDN. If that does not exist, repeat level 2.
-
Level 4: Health system/IDN or repeat level 3.
The query, of course, is complex, but it gives you an idea of the possibilities with this new table.
WITH hierarchy_breakdown AS (
SELECT
h1.vid__v as entity_vid,
h1.corporate_name__v as entity_name,
h1.hco_type__v as entity_type,
h1.major_class_of_trade__v as entity_mcot,
a1.formatted_address__v as entity_address,
substring(a1.postal_code__v,1,5) as entity_postal,
rollupcounthco1.count_of_hcp as entity_hcp_count,
-- ancestor details
h2.vid__v as ancestor_vid,
h2.corporate_name__v as ancestor_name,
h2.hco_type__v as ancestor_type,
h2.major_class_of_trade__v as ancestor_mcot,
a2.formatted_address__v as ancestor_address,
substring(a2.postal_code__v,1,5) as ancestor_postal,
rollupcounthco2.count_of_hcp as ancestor_hcp_count,
path_distance,
(
CASE
WHEN h2.major_class_of_trade__v = '45' -- medical outpatient rank 2
THEN 2 WHEN h2.major_class_of_trade__v = '32' -- hospital is rank 1
THEN 1 WHEN h2.hco_type__v = '4:11' -- admin is rank 3
THEN 3 WHEN h2.hco_type__v = '4:37' -- hs is rank 4
THEN 4
ELSE 99 -- anything else is 99 and will not show up other than at level 1 and may be repeated all the way through
END
) AS "categorization_hco_type",
(case when a2.postal_code__v = a1.postal_code__v then 1 else 99 end) as zip_code_check
FROM
hco h1 left JOIN flat_hierarchy
ON h1.vid__v = flat_hierarchy.entity_vid__v
left Join hco h2 on h2.vid__v = flat_hierarchy.ancestor_vid__v
left join (select vid__v, entity_vid__v, formatted_address__v, postal_code__v from address where address_ordinal__v = 1) a1 on h1.vid__v = a1.entity_vid__v
left join (select vid__v, entity_vid__v, formatted_address__v, postal_code__v from address where address_ordinal__v = 1) a2 on h2.vid__v = a2.entity_vid__v
left join (select ancestor_vid__v, count(*) as count_of_hcp from flat_hierarchy where entity_type__v = 'HCP' group by ancestor_vid__v ) rollupcounthco1 on h1.vid__v = rollupcounthco1.ancestor_vid__v
left join (select ancestor_vid__v, count(*) as count_of_hcp from flat_hierarchy where entity_type__v = 'HCP' group by ancestor_vid__v ) rollupcounthco2 on h2.vid__v = rollupcounthco2.ancestor_vid__v
and h1.hco_status__v = 'A'
)
-- Level 4 Calculation and final field output
select
lv1_vid,
lv1_name,
lv2_vid,
lv2_name,
lv3_vid,
lv3_name,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_vid else lv3_vid end) as "lv4_vid",
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_name else lv3_name end) as "lv4_name",
lv1_entityType,
lv2_entityType,
lv3_entityType,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_type else lv3_entityType end) as "lv4_entityType",
lv1_entityMcot,
lv2_entityMcot,
lv3_entityMcot,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_mcot else lv3_entityMcot end) as "lv4_entityMcot",
lv1_address,
lv2_address,
lv3_address,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_address else lv3_address end) as "lv4_address",
lv1_postal,
lv2_postal,
lv3_postal,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_postal else lv3_postal end) as "lv4_postal",
lv1_hcp_count,
lv2_hcp_count,
lv3_hcp_count,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.ancestor_hcp_count else lv3_hcp_count end) as "lv4_hcp_count",
lv1_lv2_distance,
lv2_lv3_distance,
(case when lv4_rank_table.ancestor_vid is not null then lv4_rank_table.path_distance else 0 end) as "lv3_lv4_distance"
from
(-- Start of Level 3 Calculation
select
lv1_vid,
lv1_name,
lv1_entityType,
lv1_entityMcot,
lv1_address,
lv1_postal,
lv1_hcp_count,
lv2_vid,
lv2_name,
lv2_entityType,
lv2_entityMcot,
lv2_address,
lv2_postal,
lv2_hcp_count,
lv1_lv2_distance,
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_vid else lv2_vid end) as "lv3_vid",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_name else lv2_name end) as "lv3_name",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_type else lv2_entityType end) as "lv3_entityType",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_mcot else lv2_entityMcot end) as "lv3_entityMcot",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_address else lv2_address end) as "lv3_address",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_postal else lv2_postal end) as "lv3_postal",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.ancestor_hcp_count else lv2_hcp_count end) as "lv3_hcp_count",
(case when lv3_rank_table.ancestor_vid is not null then lv3_rank_table.path_distance else 0 end) as "lv2_lv3_distance"
from
(
-- Finalize Level 2 Definition
select
lv1_vid,
lv1_name,
lv1_entityType,
lv1_entityMcot,
lv1_address,
lv1_postal,
lv1_hcp_count,
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_vid else lv1_vid end) as "lv2_vid",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_name else lv1_name end) as "lv2_name",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_entityType else lv1_entityType end) as "lv2_entityType",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_entityMcot else lv1_entityMcot end) as "lv2_entityMcot",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_address else lv1_address end) as "lv2_address",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_postal else lv1_postal end) as "lv2_postal",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then lv2_hcp_count else lv1_hcp_count end) as "lv2_hcp_count",
(case when categorization_hco_type <= 2 and zip_code_check = 1 then path_distance else 0 end) as "lv1_lv2_distance"
from (
-- Order the best level2 matches
SELECT entity_vid as lv1_vid,entity_name as lv1_name,entity_type as lv1_entityType, entity_mcot as lv1_entityMcot, entity_address as lv1_address, entity_postal as lv1_postal,entity_hcp_count as lv1_hcp_count,
ancestor_vid as lv2_vid, ancestor_name as lv2_name, ancestor_type as lv2_entityType, ancestor_mcot as lv2_entityMcot,ancestor_address as lv2_address, ancestor_postal as lv2_postal,ancestor_hcp_count as lv2_hcp_count, path_distance, categorization_hco_type,
ROW_NUMBER()OVER(PARTITION BY entity_vid ORDER BY zip_code_check, categorization_hco_type, path_distance) as level2_rank,zip_code_check
FROM hierarchy_breakdown) ranked_levl2 where level2_rank = 1) level2_summary -- End of Level 2 calculation
-- Order the best level 3 matches
left join (select *, ROW_NUMBER()OVER(PARTITION BY entity_vid ORDER BY categorization_hco_type, path_distance) as level3_rank from hierarchy_breakdown where categorization_hco_type in (3,4)) as lv3_rank_table on lv2_vid = entity_vid
where level3_rank = 1 or level3_rank is null) level3_summary -- End of Level 3 Calculation
-- Order the best level 4 matches, prioritize health system over admin only hcos
left join (select *, ROW_NUMBER()OVER(PARTITION BY entity_vid ORDER BY categorization_hco_type desc, path_distance) as level4_rank from hierarchy_breakdown where categorization_hco_type in (3,4)) as lv4_rank_table on lv3_vid = entity_vid
where level4_rank = 1 or level4_rank is null
Demo
The following video shows how you can easily report on different levels of your hiearchy by leveraging the flat_hiearchy reporting table.