This report examines prescription (Rx) activity captured in the RCPA reporting form, benchmarking actual Rx against assigned brand targets, evaluating doctor conversion, and mapping competitive activity across regions.
Data Preparation & ETL (Power Query)
Import Data Sources
- Load all required files: RCPA Reporting Form, PRODUCT MASTER, and BRAND TARGETS.
- In Power BI, use Home > Get Data > Excel (or CSV/SQL) to import each dataset. RCPA & Competitor RCPA Transformation (Expected Transformation Sheet)
- Go to Home > Transform Data to enter Power Query.
- Merge/Append Queries: Combine datasets as needed (e.g., join product master to RCPA report by product or merge competitor info).
Data Cleanup and Transformation in Power Query
1.Splitting Tables Using Delimiters
- Line Feed Split: Start by splitting rows where multiple products/quantities are listed together by a line feed (carriage return) delimiter. In Power Query:
- Select the column to split. Use Home > Split Column > By Delimiter. Set delimiter to "Line feed" (often shown as #(lf) or with the Unicode line break symbol). This separates each product entry into individual rows.
- Further Comma Split: After splitting by line feed, some columns (e.g., those with product names and quantities combined, such as "Aspiris-Pain,100") need to be split again—this time by a comma.
- Select the target column. Split Column > By Delimiter, select comma (,). This gives separate columns for Product and Quantity.
2. Removing Duplicates
- To ensure data integrity (especially when merging or appending), remove duplicates based on a combination of columns: UniqueID (often doctor or chemist ID, or visit ID). Product (to catch repeated entries for the same product from the same source).
- In Power Query: With the cleaned-up table selected, Home > Remove Rows > Remove Duplicates.
- Select or highlight both the UniqueID column and Product column before removing—the operation keeps only unique combinations of UniqueID and Product.
3. Additional Clean-up Steps (as needed)
- Trim and Clean Text: Use Transform > Format > Trim and Clean to remove leading/trailing spaces and non-printable characters from all text columns.
- Data Type Correction: Change columns (like Quantity) to numeric type for calculations. Filter Invalid Data:
- Remove entries where critical columns (UniqueID, Product, Quantity) are blank or contain errors.
Build Summary Tables:
- RCPA Data Table with Rx counts per doctor/product/region.
- Competitor RCPA Table in similar structure.
- Apply Changes: Click Close & Apply to commit ETL steps.
Building Visualizations
Doctor Rx Performance per Brand, Med Rep, and Region
Use Stacked Bar Chart or Matrix Visual.
Axis: Doctor/Med Rep/Region; Values: Rx Quantity; Legend: Brand.
Set up slicers for Doctor, Med Rep, Chemist, Product for interactive filtering.
Key performance indicator(KPIs)
- Total prescription(Rx) Quantity(Qty)= 3,000, which shows the actual prescription volume achieved.
- Total Target Rx Qty =74. This is the baseline target.
- The performance rate is 37.78%.
- The company maintains a strong competitive position with 3000 Rx volume while the competitor's 2000 Rx representing a 50% volume advantage .
Reginal Performance Analysis
Regional analysis reveals significant variation in prescription volumes across territories.
Top Performing Regions
Nyeri
Nyeri is the highest performing region. It has the highest volume market. The focus product prescription is 981 prescriptions while the competitors product prescription is 954 prescriptions. Leads by 5% margin in the highest volume market. This shows that the competitor is highly active.
North Rift
The focus product prescription is 798 prescriptions and the competitive product prescription is very low, this shows that it has maintained a strong performance .
Meru
Meru region has a mid tier performance. The focus product prescription is 621 prescriptions and the competitors is 432 prescriptions. There are 189 doctors, this shows excellent per doctor productivity. The performance is solid with a consistent competitive edge.
Underperforming Regions
Nairobi B
Nairobi B is one of the underperforming regions with the total prescription being 18 despite having 251 doctors. This represents a dramatic underperformance which requires urgent investigation.
Coast
The total prescription quantity is very low against the competitor's which is 248. Marketing the focus product in coast will help the volume of prescription to increase.
Doctors and medical representatives per region
The analysis of doctors and medical representatives distributions reveals a near one to one ratio across the regions.
Nairobi A
Nairobi A has 365 doctors and medical representatives generating
252 prescriptions. this indicates low efficiency.
Nairobi B
Nairobi B shows 251 doctors and medical representatives producing 18 prescriptions, representing very low efficiency.
Nyeri
Nyeri has 171 doctors and medical representatives generating 981 prescriptions, demonstrates high efficiency.
North Rift has 117 doctors and medical representatives generating 798 prescriptions also indicates very high efficiency.
In regions like Nairobi A and B we can see that it has the highest number of doctors and medical representatives but generates low prescriptions volumes, while Nyeri and North Rift with fewer doctors and medical representatives generate the highest volumes. This suggests that urban market saturations creates intense competition where multiple pharmaceutical companies compete for the same prescribers. Better market penetrations exist in mid sized cities, this indicates that these markets may be less saturated allowing for deeper relationships and more effective sales execution. The doctors quantity does not matter, the relationship quality matters. The substantial potential exists for resources reallocation from low yield regions to high yield regions this will improve the overall organizational performance.
Product Performance Analysis
Pie chart was used to show the total prescription per sales division and the number of doctors per sales division as shown below;
Across the three product divisions there exists a critical misalignment. The Aspiris Pain division covers 32.82% of doctors but generates 78.86% of prescription volume which earns an excellent performing rate. The Aspiris Gyn division engages 28.28% of doctors and produces 16.63% of prescription volume. The Aspiris Chronic division has 38.9% of doctors but only generating 4.51% of prescription volume.
The Pain division represents the primary revenue driver and the organization's flagship product. It yields an efficiency ratio of 2.40. With only a few doctors it generates four fifths of all prescription volume. This performance indicates strong product market fit, effective sales execution and competitive advantage. This division serves as the revenue engine and requires maximum protection and continued investment.
The Gyn division shows below average but acceptable performance. The efficiency ratio of 0.588 indicates room for improvement. Market potential clearly exists, with strategic investment in sales , targeted continuing medical education programs and improved marketing materials this division could move from moderate to strong performance.
The Chronic division represents a critical business problem demanding immediate executive attention. Having the largest doctor coverage yet generating minimal prescription volume indicates fundamental problems. The possible root causes include pricing concerns, prescribing barriers, training gaps, markets misalignments or competitive displacements. The organization is deploying 38.9% of its sales to achieve only 4.51% of results this represents massive resource waste.
Approval Status and Workflow Analysis
1623 prescriptions remain in progress status representing 58.05% of the total prescriptions, while 1173 prescriptions have achieved approved status of 41.95% .
Approximately 60% of prescriptions in pending status represents significant business risk. Revenue delay occurs as unapproved prescriptions do not generate immediate revenue. Prescription abandonment might occur as patients may seek alternatives during lengthy waits.
Converting 1623 pending prescriptions represents potential 145% revenue increase from the approved base. Process optimization would improve customer satisfaction. Fast approvals could benefit the organization.
Immediate actions to be taken are; implementing daily tracking of pending prescriptions, assigning dedicated teams to expedite approvals. Short term actions should be digitized approval processes, implement automated notifications. Long term initiatives should be develop predictive analytics and integrate healthcare providers systems.
Top comments (0)