Pharma field force: call average, doctor coverage and frequency

The decision this feeds

A regional or zonal sales manager in a pharmaceutical company uses these numbers to decide where to spend coaching time, which territories need re-planning, and whether the doctor list reflects the brand strategy. The list itself is the plan: marketing classifies doctors (often A, B and C by potential) and sets how many times each class should be met per month. The question for the analyst is whether field effort is reaching that plan, not merely whether effort exists.

Data from the SFA system

Most companies capture calls through a sales force automation app. You will usually get:

Freeze doctor_list at the start of the cycle. If a doctor is added mid-month, decide whether they count this cycle, and apply the rule to everyone.

Three formulas, one denominator each

• Call average (all) = distinct (doctor, day) calls ÷ field working days
• Call average (listed) = distinct (listed doctor, day) calls ÷ field working days
• Coverage % = listed doctors with at least one call ÷ listed doctors × 100
• Frequency compliance % = listed doctors with calls ≥ planned_visits ÷ listed doctors × 100

Worked example in SQLite

Two representatives, five listed doctors each, four calendar days. MR1 has a meeting day and logs many calls to unlisted doctors (IDs starting with X). MR2 has a leave day and one duplicate entry. All data is synthetic.

CREATE TABLE doctor_list (mr_id TEXT, doctor_id TEXT, doctor_class TEXT, planned_visits INTEGER);
CREATE TABLE dcr_call (mr_id TEXT, call_date TEXT, doctor_id TEXT, joint_with TEXT);
CREATE TABLE field_day (mr_id TEXT, work_date TEXT, day_type TEXT);
INSERT INTO doctor_list VALUES
('MR1','D01','A',2),('MR1','D02','A',2),('MR1','D03','B',1),('MR1','D04','B',1),('MR1','D05','C',1),
('MR2','D11','A',2),('MR2','D12','A',2),('MR2','D13','B',1),('MR2','D14','C',1),('MR2','D15','C',1);
INSERT INTO field_day VALUES
('MR1','2026-09-01','field'),('MR1','2026-09-02','field'),('MR1','2026-09-03','meeting'),
('MR1','2026-09-04','field'),
('MR2','2026-09-01','field'),('MR2','2026-09-02','field'),('MR2','2026-09-03','field'),
('MR2','2026-09-04','leave');
INSERT INTO dcr_call VALUES
('MR1','2026-09-01','D01',NULL),('MR1','2026-09-01','D03',NULL),('MR1','2026-09-01','X90',NULL),
('MR1','2026-09-01','X91',NULL),('MR1','2026-09-02','D01',NULL),('MR1','2026-09-02','X92',NULL),
('MR1','2026-09-02','X93',NULL),('MR1','2026-09-02','X94',NULL),('MR1','2026-09-04','D02',NULL),
('MR1','2026-09-04','X95',NULL),('MR1','2026-09-04','X96',NULL),('MR1','2026-09-04','X97',NULL),
('MR2','2026-09-01','D11','ABM1'),('MR2','2026-09-01','D12',NULL),('MR2','2026-09-01','D13',NULL),
('MR2','2026-09-02','D11',NULL),('MR2','2026-09-02','D14',NULL),('MR2','2026-09-02','D15',NULL),
('MR2','2026-09-03','D12',NULL),('MR2','2026-09-03','D12',NULL),('MR2','2026-09-03','D13',NULL);

WITH calls AS (
  SELECT DISTINCT mr_id, call_date, doctor_id FROM dcr_call
),
days AS (
  SELECT mr_id, COUNT(*) AS field_days FROM field_day WHERE day_type = 'field' GROUP BY mr_id
),
listed_calls AS (
  SELECT c.mr_id, c.doctor_id
  FROM calls c
  JOIN doctor_list l ON l.mr_id = c.mr_id AND l.doctor_id = c.doctor_id
),
visits AS (
  SELECT l.mr_id, l.doctor_id, l.planned_visits, COUNT(lc.doctor_id) AS visits
  FROM doctor_list l
  LEFT JOIN listed_calls lc ON lc.mr_id = l.mr_id AND lc.doctor_id = l.doctor_id
  GROUP BY l.mr_id, l.doctor_id
)
SELECT v.mr_id,
       (SELECT COUNT(*) FROM calls c WHERE c.mr_id = v.mr_id) AS all_calls,
       d.field_days,
       ROUND(1.0 * (SELECT COUNT(*) FROM calls c WHERE c.mr_id = v.mr_id) / d.field_days, 2) AS call_avg_all,
       ROUND(1.0 * SUM(v.visits) / d.field_days, 2) AS call_avg_listed,
       ROUND(100.0 * SUM(v.visits > 0) / COUNT(*), 0) AS coverage_pct,
       ROUND(100.0 * SUM(v.visits >= v.planned_visits) / COUNT(*), 0) AS frequency_pct
FROM visits v
JOIN days d USING (mr_id)
GROUP BY v.mr_id;

MR1 would top a call-average league table. Two-thirds of those calls went to doctors outside the approved list, two listed doctors were never met, and one class A doctor was met once against a plan of two. MR2 met every listed doctor as planned. The duplicate D12 entry on 3 September was removed by the DISTINCT, which is why MR2 shows 8 calls, not 9.

Where DCR data misleads

Unlisted calls. They are not always bad: a new hospital in the patch may deserve attention. But they should be visible as a separate column so the manager can ask why, instead of being blended into the average.

Denominator days. Counting meeting, training and leave days as working days punishes people who attended the cycle meeting. Counting half-days as full days does the opposite. Agree the day_type rules with sales operations.

Joint working. When an area manager works with a representative, both may log the same call. Company-level totals then double-count doctor contact. Keep the call against the representative and report manager joint-working days separately.

Duplicate and back-dated entries. Apps that allow late entry produce clusters of calls logged at night. Keep the entry timestamp and geo-tag where available, and report the share of calls entered after the day ended rather than silently accepting or deleting them.

List quality. A territory with 20 class A doctors against a peer's 8 is not comparable on frequency. Show list size and class mix beside every percentage.

Showing it to a zonal manager

Present a quadrant rather than a ranking: coverage % on one axis, frequency compliance % on the other, bubble size for list size. Representatives in the low-coverage, high-call-average corner are the coaching conversation; territories where everyone struggles on frequency are a planning problem, often too many A-class doctors for the days available. A short note under the chart should say what the data cannot show: call quality, prescription behaviour and whether the doctor list is right.

Keep individual names out of any deck circulated beyond line managers. The point is to fix plans and coaching, not to publish a leaderboard.

Learning the pattern

Deduplicating before counting, freezing a reference list and choosing a denominator are habits that transfer to any field-sales dataset, from pharma to FMCG beat productivity. The Data Analyst programme drills them in SQL over 3–4 months. If you already work in pharma sales operations, the free skill check will show which analytics skills your resume already evidences.