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.

Doctor's Name
Doctor ID
Statin Market Rx (2002)
Statin Team Calls (2002)
Market Called on
Perfect Targeting at 40% Reach
Bob Gilbertson
00284860
1,123
0
0
1,123
Katy Pendy
10298989
743
24
743
743
Donna Meyer
11059821
424
5
424
424
Erica Elba
12790234
209
13
209
209
Peter Herr
00234821
101
0
0
0
David Oakley
01658882
51
0
0
0
Claudia Bonilla
01133327
47
0
0
0
Harold Duffy
01777934
35
0
0
0
Jim Hoexter
01736452
23
0
0
0
Rob Hoover
10994472
17
9
17
0
 
 
2,773
 
1,393
2,499
 
Reach = 40%
 
Random targeting = 40% * 2,773 = 1,109
Targeting Effectiveness = (1,393 - 1,109) / (2,499 - 1,109) = 20%

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)