System and method for product level projections of pharmacy prescriptions within product therapy classes

A system for product level projections of pharmacy prescriptions within therapeutic classes, the pharmacy prescriptions dispensed or sold by a plurality of pharmaceutical outlets and channels or stores over a subject time interval, the universe of stores comprising sample stores and non-sample stores, the system comprising a processing arrangement configured to:

• extract wholesale dollar sales amounts (DDD$) for all outlets in the store universe at product group level (CMF-10) from a wholesale product sales history file;
• wherein the wholesale amounts are categorized by outlet and/or drug product levels for all product channels and outlets in the subject store universe;
• apply product and outlet cross-reference data to reflect changes to outlet and/or product information and consolidating wholesale amounts under current outlet and product numbers;
• compute running six-month average wholesale amounts using the consolidated wholesale amounts (DDD$) for a number of months preceding the latest month for which sales data is available for all outlets;
• list the averaged DDD$ by product/therapy class (CFM7/USC5) level for each outlet in a DDD file, wherein for each outlet the DDD file includes a data record having a channel identifier, an outlet identifier;
• a product identifier (CFM7 descriptor), class identifier (USC5 descriptor), and the average DDD$ value;
• extract all good and imputed total prescriptions store data from a total prescription data file;
• accordingly, adjust wholesale amounts at one or more of channel/outlet/product/and therapy class levels, wherein adjusting wholesale amounts comprises at least one of (1) retaining total prescription data for stores that were in the sample every week in the extracted time period, (2) running a cross-reference to pick up the latest drug product codes and proprietary therapy classes, (3) summing the total prescription counts to the channel/outlet/product/therapy class level for this time period;
• and merging on the average wholesale product dollar amount for these records only;
• whereby a TRx/DDD$ file is generated in which the data records have following fields:
• channel, outlet, CMF7/USC5, TRx and DDD$;
• generate an adjusted DDD$ amounts for all outlets having total prescriptions greater than zero,
• merge the “total prescription/stores adjusted DDD$” file and “the monthly universe with average wholesale amounts” file to create a Final Monthly Universe File With Average Wholesale Product Dollar Amount file
• add the outlet's state, size and type data to the Final Monthly Universe File With Average Wholesale Product Dollar Amount file to generate a Total Universe File With Adjusted Wholesale Product Dollar Amount file;
• sum the adjusted wholesale product dollar amount at the following levels:
• Channel/Size/Type/State/Product (CMF7)/Therapy Class (USC5);
• Channel/Size/Type/Product/Therapy Class;
• Channel/Size/Product/Therapy Class;
• and Channel/Product/Therapy Class;
• sum the adjusted wholesale product dollar amount to channel level at the following levels:
• channel/Size/Type/State/;
• Channel/Size/Type;
• and Channel/Size;
• merge the total universe file having adjusted wholesale product dollar amount summed to channel with the total universe file having adjusted wholesale product dollar amount summed to product/therapy class/channel;
• generate a named product file (CFM7 file) that includes the following fields:
• channel/product (CMF7)/therapy class (USC5);
• product/therapy class adjusted wholesale product dollar amount (CMF7/USC5 Adjusted DDD$);
• total adjusted wholesale product dollar amount (DDD$);
• and total outlet count;
• and
• based on the named CMF7 file, generate a product/therapy class distribution variable for each channel/product/therapy class.

A computer-readable medium for product level projections of pharmacy prescriptions within therapeutic classes, the pharmacy prescriptions dispensed or sold by a plurality of pharmaceutical outlets and channels or stores over a subject time interval, the universe of stores comprising sample stores and non-sample stores, the computer-readable medium having a set of instructions operable to direct a processing system to perform the steps of:

• extracting wholesale dollar sales amounts (DDD$) for all outlets in the store universe at product group level (CMF-10) from a wholesale product sales history file;
• wherein the wholesale amounts are categorized by outlet and/or drug product levels for all product channels and outlets in the subject store universe;
• applying product and outlet cross-reference data to reflect changes to outlet and/or product information and consolidating wholesale amounts under current outlet and product numbers;
• computing running six-month average wholesale amounts using the consolidated wholesale amounts (DDD$) for a number of months preceding the latest month for which sales data is available for all outlets;
• listing the averaged DDD$ by product/therapy class (CFM7/USC5) level for each outlet in a DDD file, wherein for each outlet the DDD file includes a data record having a channel identifier, an outlet identifier;
• a product identifier (CFM7 descriptor), class identifier (USC5 descriptor), and the average DDD$ value;
• extracting all good and imputed total prescriptions store data from a total prescription data file;
• accordingly, adjusting wholesale amounts at one or more of channel/outlet/product/and therapy class levels, wherein adjusting wholesale amounts comprises at least one of (1) retaining total prescription data for stores that were in the sample every week in the extracted time period, (2) running a cross-reference to pick up the latest drug product codes and proprietary therapy classes, (3) summing the total prescription counts to the channel/outlet/product/therapy class level for this time period;
• and merging on the average wholesale product dollar amount for these records only;
• whereby a TRx/DDD$ file is generated in which the data records have following fields:
• channel, outlet, CMF7/USC5, TRx and DDD$;
• generating an adjusted DDD$ amounts for all outlets having total prescriptions greater than zero,
• merging the “total prescription/stores adjusted DDD$” file and “the monthly universe with average wholesale amounts” file to create a “Final Monthly Universe file with Average wholesale product dollar amount”;
• adding the outlet's state, size and type data to the Final Monthly Universe File With Average Wholesale Product Dollar Amount file to generate a Total Universe File With Adjusted Wholesale Product Dollar Amount file;
• summing the adjusted wholesale product dollar amount at the following levels:
• Channel/Size/Type/State/Product (CMF7)/Therapy Class (USC5);
• Channel/Size/Type/Product/Therapy Class;
• Channel/Size/Product/Therapy Class;
• and Channel/Product/Therapy Class;
• summing the adjusted wholesale product dollar amount to channel level at the following levels:
• Channel/Size/Type/State/;
• Channel/Size/Type;
• and Channel/Size;
• merging the total universe file having adjusted wholesale product dollar amount summed to channel with the total universe file having adjusted wholesale product dollar amount summed to product/therapy class/channel;
• generating a named product file (CFM7 file) that includes the following fields:
• channel/product (CMF7)/therapy class (USC5);
• product/therapy class adjusted wholesale product dollar amount (CMF7/USC5 Adjusted DDD$);
• total adjusted wholesale product dollar amount (DDD$);
• and total outlet count;
• and
• based on the named CMF7 file, generating a product/therapy class distribution variable for each channel/product/therapy class.

A monthly and weekly store sizing method for product level projections of pharmacy prescriptions within therapeutic classes, the pharmacy prescriptions dispensed or sold by a plurality of pharmaceutical outlets and channels or stores over a subject time interval, the universe of production stores comprising sample stores and non-sample stores, the method comprising:

• monthly, calculating average wholesale dollar amounts (DDD$) for outlets in the current universe of production stores based on data in a history file excluding those outlets that have nonviable wholesale dollar amount values, wherein the average wholesale dollar amounts are calculated at channel/outlet/product-therapy levels
• adjusting the average DDD$ using total prescription (TRx) data for outlets where such TRx data is available, and storing the adjusted DDD$ in a file;
• computing average DDD$ for outlets and entities not included in the stored adjusted DDD$ file;
• combining the adjusted DDD$ and the computed average DDD$ for all outlets and entities in a combined monthly universe file;
• weekly, in response to updated reporting databases, extracting weekly total prescription (TRx) data from the updated reporting databases;
• evaluating the weekly total prescription (TRx) data and the combined monthly universe file to generate formulas to convert wholesale dollar amounts to TRx values;
• applying the formulas to convert all wholesale dollar amounts to TRx values an d storing the results in a weekly sizing file;
• comparing the current week's sizing file with the prior weeks file and accordingly adjusting the current week's TRx values to update the current week's sizing file;
• and
• rolling up product/therapy class level data to higher levels.

A computer-readable medium for product level projections of pharmacy prescriptions within therapeutic classes, the pharmacy prescriptions dispensed or sold by a plurality of pharmaceutical outlets and channels or stores over a subject time interval, the universe of stores comprising sample stores and non-sample stores, the computer-readable medium having a set of instructions operable to direct a processing system to perform the steps of:

• monthly, calculating average wholesale dollar amounts (DDD$) for outlets in the current universe of production stores based on data in a history file excluding those outlets that have nonviable wholesale dollar amount values, wherein the average wholesale dollar amounts are calculated at channel/outlet/product-therapy levels
• adjusting the average DDD$ using total prescription (TRx) data for outlets where such TRx data is available, and storing the adjusted DDD$ in a file;
• computing average DDD$ for outlets and entities not included in the stored adjusted DDD$ file;
• combining the adjusted DDD$ and the computed average DDD$ for all outlets and entities in a combined monthly universe file;
• weekly, in response to updated reporting databases, extracting weekly total prescription (TRx) data from the updated reporting databases;
• evaluating the weekly total prescription (TRx) data and the combined monthly universe file to generate formulas to convert wholesale dollar amounts to TRx values;
• applying the formulas to convert all wholesale dollar amounts to TRx values an d storing the results in a weekly sizing file;
• comparing the current week's sizing file with the prior weeks file and accordingly adjusting the current week's TRx values to update the current week's sizing file;
• and
• rolling up product/therapy class level data to higher levels