Data Acquisition 101

Here's a guide to data integration strategies for utilization, financial, and compliance reporting.

In the last issue, we reviewed the implementation of a Specialty Pharmacy Data Warehouse project from a 10,000-foot perspective. Specifically, we reviewed requirements gathering, hardware and software considerations, cloud computing, and vendor selection. Taking our data warehouse implementation discussion a step further, we will now focus on data integration. The scope of this article includes data integration strategies for utilization, financial, and compliance reporting.

For most pharmacies, the bulk of the data populating your data warehouse will come directly from your pharmacy management system. In support of this practice, most systems offer canned data extracts primarily utilized for finance and accounting purposes. While these data extracts are geared toward accounts receivable and member copay collections, they can also serve as a baseline for populating your data warehouse.

While the center of your data warehouse project will focus on receiving prescriptions filled/claims data extracts, special considerations should be in place for obtaining additional data elements such as Rxs (patient prescription information), client information, and medication and physician data. Some of the challenges typically observed in obtaining this data may include:

Defining Your Client Hierarchy: Information used to identify a particular client/payer can be especially challenging to implement. Quite often the pharmacy management system will not identify or group clients/payers via the same coding methods as the finance/ accounting system. As a result, the data warehouse will probably need cross reference tables linking and grouping to manage client hierarchy across systems.

Assigning Physician Specialties: Information used to align a physician with their specialty is usually unreliable, as inconsistencies may exist across multiple data sources. As a result, special initiatives may be needed to gather and correct this information.

Assigning Physician Offices: Information used to group physicians into offices or identify a physician across multiple offices is often a requirement, however may prove difficult to implement and support due to the shifting nature of this information.

A critical data integration point for your data warehouse is shipping information. The timeliness of your deliveries is critical to your clients and your company’s Utilization Review Accreditation Commission (URAC) requirements. Essential shipping information data typically includes shipping costs, tracking numbers, and delivery notifications. If your pharmacy management software package already contains this information, integrating these data into your data warehouse will be fairly straightforward and should be included in your nightly extracts. If these data is not in your pharmacy management software, you may be able to retrieve it from your manifesting system or via an Application Programming Interface (API) from the shipping carrier. Utilizing an API route may be complex, however will provide near-real-time information regarding the status of a shipment and help to reduce the resources currently expended to search for a package status via an Internet application.

Another critical component for managing and measuring operational performance is call volume statistics. Common metrics include Average Speed to Answer (ASA), Call Abandonment Rate, Agent Uptime or Utilization, and Issue Resolution Percentage (percentage of customer issues resolved in a single call, ie, “One and Done”). Depending on the capabilities of your Private Branch Exchange (PBX)/Call Center Reporting Tools, this information may be accessed in near real-time or downloaded daily to your data warehouse. Keeping in mind that call center statistics may need to be reported back to your clients, upfront consideration for dedicated 800 numbers and optimal call flows may be necessary to support your reporting needs given your particular hardware and software PBX solution.

Quite often you will want to integrate external data within your environment to track the effectiveness of your sales staff or track changes in prescriber habits associated with office visits from your sales force. External metrics such as this are typically accomplished through daily extracts obtained from established interfaces between your data warehouse and your contact management software (for example, tools such as Salesforce.com). In order to utilize external data to support integrated metrics, you will need to map to your prescription utilization via the prescriber or the prescriber’s practice. You can then correlate changes in prescribing with office visits by your sales team. Depending on the contact management software you are using, you may need to upgrade software licenses to achieve a streamlined extract. Planning for this is key, as this can be pricey.

Lastly, margin calculations will be more complete if they include actual payroll information. In order to accomplish this, the data warehouse will need to know the actual payroll dollars associated with each operation. It is important to understand how payroll data correlates to specific operations, as there may be limitations as to how this data is gathered. Payroll tracking may occur through time clocks and software, which can be interfaced to or through manual entry by payroll clerks. In addition, payroll dollars may be summarized by hour, by day, or by week. Finally, consider how individuals and their functions will be identified— for example, you will probably want to identify pharmacists as part of your margin calculation, but not include accounting clerks. SPT

Jim Maguire is the chief executive officer of BioMed Intelligence Inc, a firm specializing in health care information technology support solutions. Contact Jim at jmaguire@biomedintelligence.com or at (347) 847-3570.