September 2003

TUTORIAL: Data Management

Calculating the targeting effectiveness metric requires managers to integrate two data sources:

·      Call data for the sales team and products being measured
·     Sample prescribing data for the relevant therapeutic classes

The first step in calculating targeting effectiveness is deciding what period to measure—for optimal results, we suggest six months or a year.  Both the prescription and call data must be collected for the same period.  Once the data is gathered, it must be formatted and “matched” to create a unified database.  Microsoft Excel and Microsoft Access are common tools for managing and analyzing this data.

Consider a specialty sales team calling on cardiologists with a statin drug.  The targeting strategy is to call on the 40% of cardiologists who prescribe the most statins.  Figure 3a illustrates an appropriate format for prescription data for the statin class.  The prescription file must include a matching ID (usually a physician number or name) and each physician’s market potential.  In this simple example, potential is defined by total prescriptions for a single therapeutic class.  Representative samples may vary from 500 physicians to 20,000 physicians depending on the geographic market and specialties covered by the sales team.

Figure 3a.   Figure 3b. Doctor's Name Doctor ID Statin Market Rx (2002) Doctor's Name Doctor ID Statin Team Calls (2002) Bob Gilbertson 00284860 1,123       Jim Hoexter 01736452 23       Peter Herr 00234821 101       David Oakley 01658882 51       Harold Duffy 01777934 35       Claudia Bonilla 01133327 47       Rob Hoover 10994472 17 Rob Hoover 10994472 9 Donna Meyer 11059821 424 Donna Meyer 11059821 5 Katy Pendy 10298989 743 Katy Pendy 10298989 24 Erica Elba 12790234 209 Erica Elba 12790234 13       Ray Gandolf 15429688 7       Colleen Rickle 00243276 11       John Boston 02164590 1       Elena Martin 01432985 5       Jane Kolb 19937286 1       Val Wolfgram 19930067 18

Next the sales call data must be formatted.  Each record should represent a physician that received at least one relevant product detail during the period.  This file must include a common identifier that can be matched to physicians in the sample prescription data (see figure 3b).  An analyst must then match call data with sample prescription data to create one single call and prescription database file.  The final targeting effectiveness database should look like the one in figure 4. It includes all doctors that prescribed in the relevant therapeutic class and lists the sales team’s calls on them.  This file should not include:

• physicians called on but not listed in the sample prescribing data
• planned calls that were not executed
• sales calls made by other sales teams 

Figure 4.

In this case, the sample prescribing data includes information for 10 physicians.  The call data indicates that the sales team called on 10 physicians during the period, of whom four are included in the sample prescribing data for a reach of 40% in the sample.  In essence, the sales team called on three of the top four prescribing doctors but did not call on the doctor (Bob Gilbertson) with the greatest market potential.

(back to the article)