Sales force automation (SFA) data refers to both information that is collected and information that assists a firm's sales force in making sales calls. There are several SFA integration issues in the pharmaceutical industry, including those involving corporate mergers and the integration of legacy systems. Pharmaceutical companies rely on external market research services to gauge their products' activity and performance (i.e., the number of prescriptions written for the product, who prescribed them, etc.). This external data is large and is frequently updated. This data provides the greatest value when compared and analyzed against the effort invested in promoting the product to the physicians. This analysis is crucial for sales planning and market research; firms use it for various purposes including rep compensation, doctor targeting and sales force alignment. Bayer Pharmaceuticals, for example, uses this integrated data extensively to evaluate the effectiveness of promotions and for calculating the ROI on their products.

The effort invested is captured by the reps after their visits to doctors' offices primarily using three measures ­ sales call, product sample and detail (CSD) information, normally referred to as SFA information. A sales call represents a rep visit to a doctor; sample represents the number of sample product packs the doctor received; a detail is an interaction with the doctor about a specific product. Often for a single call, there will be several "details" as the rep promotes various products in his portfolio. To enable reps to call on doctors, the firm, typically in conjunction with an SFA vendor, provides targeted lists containing demographic and other ancillary information on doctors within the reps' calling areas. If a rep has outdated information on a doctor, he updates his database accordingly, which updates the main data warehouse.

Integration Issues

Integrating prescription and SFA information is a challenging process that requires detailed planning and an understanding of the company's business. Some factors to consider are:

Bridging keys. The keys commonly used to bridge the prescription and SFA data are the doctor identifier, product code and period. Integrating the period keys is relatively straightforward. The product keys sometimes require recoding as they are often labeled differently by the various companies. The doctor key integration is the most complex. In addition to the fact that unique national identification numbers for doctors do not exist, the doctor keys from the SFA systems are not always "clean" ­ they often contain inconsistent or invalid data or are even entirely missing. The keys must, therefore, be scrubbed thoroughly before adding them to the warehouse.

Sales force alignments. The merged prescription and CSD data is useful only if it contains information on the company's sales force and on the doctors' practice locations. Firms can use it in various ways including targeting doctors and redeploying their sales force.

To begin, we must use the firm's "alignment files," which contain the relationship between the sales reps and the doctors' practice locations. From now on, the terms "rep" and "geography" will be used synonymously.

The second consideration is the limitations of the star and the snowflake data models in the pharmaceutical industry. Many companies have multiple sales reps call on the same doctor to promote their products vigorously, which creates a many-to-many relationship between the two. This relationship has to be modeled either as a single dimension with a many-to-many relationship between doctor and geography or as separate dimensions, in which case the geography key has to be incorporated in the fact table. Most OLAP tools have difficulty with the former approach, whereas the latter double counts the data for the doctor at higher geography levels in addition to increasing the size of the fact table. The issue of double counting also impacts the building of aggregate tables. In most cases, it boils down to the client choosing an approach and training its end users to run queries accordingly. Bayer Pharmaceuticals, for example, addresses this issue by having its end users select the sales force that the rep belongs to in every query so that the doctor is counted only once for that sales force/geography combination.

Other considerations when integrating sales force alignments include:

  • Apportioning of prescription and CSD data (i.e., distributing credit among all the reps that called on a doctor).
  • Doctors with multiple practice locations which affects reps' targeting lists and possibly their compensation.
  • Maintaining both historical and current alignments; targeting requires current alignments whereas compensation requires historical ones.

Incorporating SFA data in the data warehouse is a complex process. It could be simplified by addressing two broad aspects ­ accurately capturing information and streamlining the integration issues. Capturing SFA information should become more structured with advances in data extraction and cleansing technology. With cleaner input data and standardization, we can handle integration issues with greater ease.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access