7.5. A Deeper Understanding of Regional Differences
Rick's next charge is to determine whether there are regional differences in performance relative to sales of Pharma Inc.'s major product. He realizes that he has already gone a long way toward answering this question. Yes, there are statistical differences between the regions, as indicated by the Compare Means analysis illustrated in Exhibit 7.39 and reinforced by the model that accounts for the sales representative effect (Exhibit 7.44). Rick can say that in terms of total prescriptions written over the eight-month period the regions fall into six groups, as shown in Exhibit 7.42. The important questions now revolve around the practical extent of such differences and which chunk factors are related to the differences.
So at this point, Rick wants to go beyond the counts of written prescriptions. Again, he has already done some of the work to understand the relationship between the number of prescriptions and the number of visits. Now he wants to see if he can gain additional insight by seeing this from the perspective of the sales representatives and by taking into account behavior over time. In fact, when Rick thinks about this, he realizes that he would like to see, on a year-to-date basis, how the total number of prescriptions written by each sales representative's physicians is related to the total number of visits. But he would also like to be able to visualize the effect of the total number of physicians assigned to a sales representative and to easily identify the sales representatives' regions. In short, Rick wants a more informative version of the scatterplots presented in the bivariate reports in the previous section.
Rick has seen demos using the JMP bubble plot animation to show changes in the scatter of points over time. He quickly realizes that he must do some data preparation before using this visualization tool—he needs to summarize his data across sales representatives and to define year-to-date summaries of Prescriptions and Visits.
Rick does this aggregation in JMP. If you have the need to perform a similar aggregation in a project, you could use a spreadsheet, but generally using JMP will be faster, easier, and more flexible. We give the details of the JMP aggregation in the final section, "Additional Details." But for now it suffices to say that Rick writes a script for his aggregation and saves it to PharmaSales.jmp as Summary Table 2. You should run this script at this point.
The script constructs Rick's aggregated data table (Exhibit 7.53). For each month, the columns Visits YTD and Prescriptions YTD contain the year-to-date sums of the corresponding variables for each sales representative. Rick recalls that there are 103 sales representatives. There should be eight YTD values for each sales representative, and so his new table should contain 824 rows, which it does.
Figure 7.53. Partial View of Summary Table Giving YTD Aggregation
At this point, Rick is ready to construct an animated bubble plot. He decides to look at the relationship between Prescriptions YTD and Visits YTD, with bubbles sized by Number of Physicians and colored by Region Name over the time period defined by Date. Bubbles will be identified by either the Salesrep Name or the Region Name. He selects Graph > Bubble Plot, and fills in the launch dialog as shown in Exhibit 7.54.
Figure 7.54. Bubble Plot Launch Dialog for Prescriptions YTD versus Visits YTD
When Rick clicks OK, he obtains the bubble plot shown in Exhibit 7.55, which shows the picture for May 2008. He saves this script as Bubble Plot 1. There is one bubble per Region Name, since this was the first ID variable. By clicking Step at the bottom left of the plot, Rick can follow the relationship through the eight-month period. Exhibit 7.56 shows the relationship in December 2008.
Figure 7.55. Bubble Plot for Prescriptions YTD versus Visits YTD, May 2008
Figure 7.56. Bubble Plot for Prescriptions YTD versus Visits YTD, December 2008
Now, Rick needs to take a moment to figure out what is being plotted. The vertical center of each bubble is at the average of Prescriptions YTD for the given region. The horizontal center is at the average of Visits YTD for the given region. The sizes of the bubbles are proportional to the number of physicians in the regions.
Rick notices that he can animate the plot by clicking Go. He does this and finds it interesting that the two top regions, as shown in the December plot (Exhibit 7.56), achieve the same general average prescription totals, yet one requires many more visits on average than the other.
To find out which two regions these are, he selects All Labels from the red triangle. The labels appear on the plot (Exhibit 7.57), and Rick sees that the regions of interest are Midlands and Northern England. Ah, Midlands was part of the promotion while Northern England was not. He finds it striking that sales representatives in Midlands attained roughly the same mean number of prescriptions as did representatives in Northern England, but with far fewer visits.
Figure 7.57. Bubble Plot for Prescriptions YTD versus Visits YTD, December 2008, with Labels
Rick clicks Go again and notices that Midlands and Northern England tend to have the same general mean level of Prescriptions YTD over time. However, Rick notes that Midlands, whose bubble is smaller than Northern England's, evidently has fewer, perhaps about half as many, physicians as does Northern England. Perhaps, thinks Rick, the sales representatives in Northern England have a larger physician workload than do the sales representatives in Midlands and so have to make more visits. Perhaps he should be looking at year-to-date prescriptions and year-to-date visits per physician?
But first, Rick wants to see this plot with the bubbles split by Salesrep Name. He clicks on the red triangle and unchecks All Labels, then returns to the red triangle and clicks Split All. Now, for each month, he sees a single bubble for each sales representative. He animates the plot and observes what is happening. (Exhibit 7.58 shows the plot for December 2008.)
Figure 7.58. Bubble Plot for Prescriptions YTD versus Visits YTD, December 2008, Split by Salesrep Name
Viewing the plot over time, he finds it interesting that the sales representative bubbles within a region stay tightly clustered, indicating that the aggregated numbers of visits are fairly homogeneous within regions, as are the aggregated prescription totals. He also notices that the circle sizes differ greatly from region to region, but are fairly uniform within regions, meaning that the number of physicians assigned to sales representatives may differ dramatically for different regions, but that within regions the allocation is fairly consistent. It does appear that a typical sales representative in Northern England has more physicians than a typical sales representative in Midlands. This plot provides useful information and could help Rick in thinking about ways to realign the sales force to make it more effective.
Now Rick returns to his idea of normalizing by Number of Physicians. The two columns that Rick will need, Visits YTD per Physician and Prescriptions YTD per Physician, are already inserted in the summary table. He constructed these columns by creating new columns and using the formula editor. For example, the formula for the column Visits YTD per Physician is shown in Exhibit 7.59. To view it, in the data table, click on the plus sign to the right of Visits YTD per Physician in the columns panel.
Figure 7.59. Formula for Visits YTD per Physician
Next, Rick selects Graph > Bubble Plot and fills in the launch dialog using the new variables, Prescriptions YTD per Physician versus Visits YTD per Physician, as the Y and X variables, respectively, and the other variable choices as shown in Exhibit 7.60. He clicks OK and saves the script as Bubble Plot 2.
Figure 7.60. Bubble Plot Launch Dialog for Prescriptions YTD per Physician versus Visits YTD per Physician
As before, Rick selects All Labels. Rick also selects Trail Bubbles and Trail Lines from the red triangle options. Then, Rick animates the plot to see how the regions behave over the eight-month period. The plot for December 2008 is shown in Exhibit 7.61.
Figure 7.61. Bubble Plot for Prescriptions YTD per Physician versus Visits YTD per Physician, December 2008
Rick is struck by how similar the regions are in terms of Visits YTD per Physician. He realizes that the norm for visits to physicians is one visit per month. But the data show this happening with almost uncanny regularity.
He wonders if perhaps sales representatives make one visit per month to each practice, and then count this as a visit to all physicians at that practice even though they do not meet individually with all the physicians there. In other words, what does it mean to "make a visit to a physician?" Does it mean that the sales representative talks with the physician face to face? Or that the representative talks with a secretary or technician? Or that the representative simply drops in and leaves a card? And, is it possible that the data are not quite representative of reality? Rick notes that he needs to discuss this with the representatives when they next meet.
Rick considers the plot for 12/2008. By selecting various bubbles and holding down the shift key to select more than one region, Rick is able to select Midlands and Northern England (Exhibit 7.62). Since he has enabled Trail Bubbles and Trail Lines, the plot shows the bubbles for the preceding months. Rick sees that these two regions drift further apart over time, with Midlands greatly exceeding Northern England in mean number of Prescriptions YTD per Physician. So, even accounting for number of physicians, Midlands is ahead, providing more confirmation that the promotion enhanced sales.
Figure 7.62. Bubble Plot for Prescriptions YTD per Physician versus Visits YTD per Physician, December 2008, Two Regions Selected
Without the need for animation, the trail bubbles highlight that almost suspicious regularity in Visits YTD per Physician. This is a mystery that Rick needs to unravel.
Rick deselects these two regions, and then deselects All Labels and selects the Split All option to see the individual sales representative behavior. Once again, he animates the plot. He notes that there is a little less regularity in the Visits YTD per Physician for individual sales representatives, with Scotland showing the most variability. He stops the plot at 12/2008 and selects various sales representatives in order to view their trails. He sees some small amount of variability, but not nearly as much as he might expect. Again, the question of what the sales representatives are recording comes to Rick's mind. And are the representatives in Scotland using different criteria? (See Exhibit 7.63, where the plot on the left shows trails for six Scotland sales representatives, while the plot on the right shows trails for six sales representatives from other regions.)
Figure 7.63. Bubble Plots Showing Trails for Selected Sales Representatives, from Scotland Only on Left, from Other Regions on Right
