25. January 2017 · Comments Off on Pitfalls in analyzing pharma sales force effectiveness – Part 1 · Categories: Data model, Data Warehouse, Specials · Tags: , , , , , ,

Pitfalls in analyzing pharma sales force effectiveness – Part 1

Sales Force Effectiveness - The ultimate goal

Sales Force Effectiveness – The ultimate goal

One of the main goals of this blog is to clarify special real-life demands on business intelligence systems. Academic sources and textbooks often do not fully address these special requirements. This also applies to many of the common BI solutions on the market.
I have encountered, and fulfilled, plenty of these respective demands in designing and building systems for analyzing pharma sales force effectiveness.

Unlike many other sectors, pharma is up against special data structures, data constellations, and hierarchies. I can’t say it enough that way too often decision-makers ultimately drop important business requirements, just because the BI system cannot fulfill them.

I will describe some of these special requirements in a special series parallel to the regular path of this blog. Even if your focus is not on pharma, you will eventually encounter some of the following aspects in other sectors too. I had to deal with them in projects for insurance companies, financial service providers, banks, and companies from consumer sectors.

These are the subjects I will address in the special series:

  • Hierarchies in sales force and regional structures
  • Product hierarchies
  • Combining and aggregating data from different sources with different granularity
  • Raw data on different hierarchy levels
  • Temporal variability of
    • locations and relationships of doctors and institutions
    • doctor’s specialities and characteristics
    • target groups and segmentation
    • sales force constellation
    • sales rep’s objectives and implications on sales force compensation

 Hierarchies in sales force and regional structures

Regularly, the sales force is divided into several sales lines. Different lines are dedicated to different products, product groups or indications, different types of clientele, or different business models (like “traditional” sales force or key account management).  All these lines can have different internal structures. They can be entirely independent or they can mix somewhere in the overall hierarchy. Regardless of that, in most cases a thorough analysis requires summaries, measures, and KPIs on corporate level over all of the sales lines.

The hierarchies of traditional sales force models are derived from geographic entities, where the companies strive for an optimal alignment of territories. The smallest geographic entities are often determined by legal requirements and by the granularity of data delivered by external sources. The hierarchies are made up of typically a handful of levels (e.g. brick or segment, subterritory, territory, region, area, country, etc.).

The following images depict simplified versions of a sales force structure. A typical structure frequently has more than four hierarchy levels. However, for the sake of clarity, I will show and discuss only four levels in the upcoming examples.

The entities on the different hierarchy levels are:

  • Brick or Segment
    A brick is usually the smallest geographic unit, defined according to legal restrictions for anonymized prescription data. Bricks represent the highest regional granularity in data from external sources like IMS Health Xponent etc.. In the best case, bricks correspond to ZIP codes, whereas national laws often dictate larger areas. Bricks don’t have persons from the sales force assigned to them. On the other hand, target persons like doctors and institutions like physician’s offices, hospitals, or pharmacies are relevant.
  • Territory
    The territory is directly assigned to a sales rep. It is the area where the rep operates and where the relevant target persons and institutions reside.
  • Region
    A region combines several territories under the responsibility of a regional manager.
  • Country or Organization
    The upper level or all-encompassing node of the hierarchy is often a country, a busniess unit, or an entire corporation.

The “nice and easy” hierarchy

In the best case, the hierarchy looks similar to this:

Simplified diagram of a "nice and easy" sales force structure

Simplified diagram of a “nice and easy” sales force structure – Click to enlarge

Whenever you encounter a structure like the above, calculating and aggregating measures should be a piece of cake. All of the current BI solutions on the market are able to deliver the correct results just out of the box.

The main characteristics of a “nice and easy” sales force structure are:

  • Just one sales line
  • Each node of the hierarchy has a well-defined level
  • Each node has exactly one parent on the next upper level
  • For each node, there is only one distinct path to the top node


The team-oriented sales force

The team-oriented sales force structure

The team-oriented sales force structure – Click to enlarge

Now it’s starting to get a bit nasty. In almost every data warehouse or BI project in the pharmaceutical sector I’ve seen team-oriented sales forces. The operational area of a rep is not restricted to a distinct part of a region. Instead, all the reps form a team, which is sharing and spreading activities over the entire region.

The main characteristics of a team-oriented sales force structure are:

  • Nodes (especially bricks) have multiple parents on the next upper level
  • For some nodes, there is more than one distinct path to the top node

The above mentioned aspects form constellations that are commonly called “diamond shapes”.

The consequences of a team-oriented structure are clearly identifyable in the following table:

Aggregating sales volume in a team-oriented sales force structure

Aggregating sales volume in a team-oriented sales force structure – Click to enlarge

This is a snippet from a Power BI report based on sales data for a fictional german pharmaceutical company. Like in a real-world scenario, raw prescription data are present at the bricks level. The data (in this case sales volume and sales volume market share for one specific product) are aggregated for each node of the hierarchy.

Whereas “Region 111700” belongs to a sales line with a conventional structure, the other regions are part of a sales line with a team-oriented structure. It can easily be seen that each territory (Gebiet) of “Region 111700” has different values, whereas all the territories of the other regions have the same values. Plus, the values for each territory in the team-oriented line are identical to the values of the respective region.
This is correct, because in the conventional structure each brick belongs to only one distinct territory, while in a team-oriented sales force a brick belongs to all of the territories inside a region. Nevertheless, the sales volume of a brick must not be included more than once in the total of the region.

BI systems are ususally not able to deliver correct aggregations for team-oriented sales force structures without special interventions.

In the next post of this special series I will discuss some more “peculiar” pharma sales force structures and I will also give some examples from other sectors.