top of page

Use Cases

Improve Commercial Effectiveness

Deliver Provider Next-Best-Action

Global Customer 360

Deduplication on C360 Cofig Target Tables

Customer 360 – Enhance Aggregated View Logic for Customer–Brand–Payer Claims Reporting (US Aggregation)

Customer 360 – Claim update on Brand “Grazette” on Materialized views

Customer 360 - Add Missing Brands (BRYSTEXA, TEPEZA, EPLAZNA)

Rectify the error and add file header to the query

Partitioned parquet backup of customer 360 data

Building SQL UDF Functions for Customer 360 Data

Protecting customer 360 PII data

Next-Best-Action Recommendations / Omnichannel

To Calculate the On-time Delivery percentage of supply chain partner to ensure delivery of life saving medicines

Generate Real World Evidence

Clinical Data Abstraction

Develop pact_case_patient and Case History with its logic

Load the copay_claim_pharmacy table from attached mapping sheet

Load the copay_claim_medical table from attached mapping sheet

Build PACT_BR_PATIENT table with its logic from mapping specification in SQL

Build PACT_BR_PATIENT table with its logic from mapping specification

Building logics for patient_insights script as per PDF

Compute is_notified and notification_date Columns

Calculate the code logic from derived column list with column directly from source

Implement Product-Therapeutic Area Join Logic in patient_study table

Real-Time Ingestion of Patient Data from S3 to Delta Using Auto Loader

Automate Data Type Standardization for patient_data_bronze via S3 Mapping File

Taking Backup of Onboarded S3 Files

Migrating Patient Files From Vendor's S3 Files to Databricks Volume

SQL logic update in metric master table

DQ_Rule_Check with hcp interactions against medical inquiry and medical inquiry response

Refactor 'Filler_1' to 'DELETE_FLAG' with Constraints in Outbound FIA ODS Tables

CSS for insurance claims and customer 360 data

Fraud insurance claim detection

To find the claim data validation checks

Excel Integration for Account Table: Add 4 New Fields

Data Standardization with OMOP

SQL for Metric Categorization and Sales Forecast Summarization

Non English to English word conversion

Standardize data for delivery_dt column in f_order table

Patient Cohorting & Propensity Score Matching

Customer_360 - Derive Interaction-Level KPI Aggregations with Classification and Sales Team Mapping

Customer360 - Aggregate Brand-Payer Claims Data for 6-Month & 12-Month Cohort Views at Site & Parent Levels (CHILD & PARENT)

Customer_360 - Enrich Sales Data with Representative–Territory Hierarchy

Customer 360 - Load the static csv file and create a view

Develop Function Result Matching Between Branches

Configuration Master of Raw tables

Pharmacovigilance (Drug Safety & Adverse Event Detection)

Analyzing Cold Chain Temperature Data: Violation Detection and Insights

Calculate the Reorder_stock, based on current inventory stock

Personalize Patient Engagement

Digital Patient 360

Develop pact_case_patient and Case History with its logic

Load the copay_claim_medical table from attached mapping sheet

Build PACT_BR_PATIENT table with its logic from mapping specification in SQL

Build PACT_BR_PATIENT table with its logic from mapping specification

Building logics for patient_insights script as per PDF

Compute is_notified and notification_date Columns

Calculate the code logic from derived column list with column directly from source

Implement Product-Therapeutic Area Join Logic in patient_study table

Update the logic in sql script of existing table based on source_target_mapping_specification excel

Table creation on patient_account based on source_target_mapping_specification excel

Real-Time Ingestion of Patient Data from S3 to Delta Using Auto Loader

Automate Data Type Standardization for patient_data_bronze via S3 Mapping File

Taking Backup of Onboarded S3 Files

Migrating Patient Files From Vendor's S3 Files to Databricks Volume

Enhancing Onboarded Table with Additional Fields

SQL logic update in metric master table

DQ_Rule_Check with hcp interactions against medical inquiry and medical inquiry response

Life science product recommendation

Life science product recommendation

CSS for insurance claims and customer 360 data

Fraud insurance claim detection

To find the claim data validation checks

Excel Integration for Account Table: Add 4 New Fields

Automating Table Creation Using Source-Target Mapping in Excel

Optimize Production

Create E2E Supply Chain Visibility

Automate Reporting (eg. OTIF)

Net Sales Variance Analysis between published view and base table.

Create Weekly KPI Rollup at Market level With Customer and Product Filters

Aggregate REP_SHARE_OF_VOICE Metrics by Reporting Date for Repatha

Total_CPD Anomaly Detection using PySpark

Customer_360 - Fix CPD Value Inflation Issue Across Brands for BLUE BALL Sales Team

Customer_360 - Calculate and Compare “Calls Per Day (CPD)” Across FES and Execution Sapphire Sources for ONC SAPPHIRE OBU Team

Customer_360 - Derive Interaction-Level KPI Aggregations with Classification and Sales Team Mapping

Customer_360 - Sales Team Call Performance & Interaction Classification Aggregation

Enhance Weekly Field Reporting Metrics Reporting Comparison for All Brands and Territories

Customer360 - Aggregate Brand-Payer Claims Data for 6-Month & 12-Month Cohort Views at Site & Parent Levels (CHILD & PARENT)

Customer_360 -Compute Brand-Level Sales Metrics & Growth Calculations for C360 Report

Customer_360 - Enrich Sales Data with Representative–Territory Hierarchy

Customer_360 - Restrict Sales Data by Valid Market–Product–BU and Filter Non-Restricted Customers

Calculate Average Force Fields Per Territory

Creating c360_combined_ell_reporting_us View

Develop c360_vision_usage_report view

Customer 360 – Enhance Aggregated View Logic for Customer–Brand–Payer Claims Reporting (US Aggregation)

Add Newly Introduced Entry Column from Source Mapping Sheet to field_usage_report and Synchronize with Downstream View

Customer 360 - Load the static csv file and create a view

Customer 360 – Claim update on Brand “Grazette” on Materialized views

Data validation for Platform Migration

Create a new field Unique_Id in "pat_income_fax_transaction" table

Update SQL Logic for Biosimilar Brand name and competitor flag

Update the logic in sql script of existing table based on source_target_mapping_specification excel

Table creation on patient_account based on source_target_mapping_specification excel

Update SQL Logic for Biosimilar Brands Based on KAISER GPO Flag

Email Notifications for Failed or Incomplete Ingestions

Study Duration analysis on clinical trials

Enhancing Onboarded Table with Additional Fields

Core Layer - Clinical Study Data Maintaining the History Records (SCD2) without timestamp

Core Layer - Clinical Study Data Maintaning the History Records (SCD2) with timestamp

Data profiling on metric tables

MSL-HCP Interactions on Sales with Therapeutic area mappings

TRF DQ Rules Validation

Preparing unified view of HCP interactions

Multi-Dimensional KPI Calculation on Sales

HCP engagement with Medical Inquiry and Medical Inquiries Response

DQ rule for Inventory management

Automating Table Creation Using Source-Target Mapping in Excel

Creating data quality table using pyspark

Flattening json strings columns

Calculate revenue based on category and customer purchase columns

File quality check

Implement Data Quality Checks for Tepezza Product Sales Data

Data quality check to check null values and valid format on d_product table

Development of d_company

Populate Inventory Transaction table using Data Mapping xls

Calculate Days on Hand (DOH) usage for inventory

Withdraw the data from facts table which is not qualified for KPI

Demand Forecasting

Build the 6 week average incoming demand faxes from pact_incoming_fax_transaction table

Applied Quantity: A Key Metric for Inventory Transaction Optimization

SQL for Metric Categorization and Sales Forecast Summarization

Populate product plant table using Data Mapping xls

Monthly-Yearly Category wise Revenue analysis.

Churn analysis on customer 360 data

Implementation of Volume logic in pre_t1 query table.

Remove Column 'wholesaler_file_Name' from all table,views and query

Populate a new logic in view d_product

Update the fact table based on t1_pricing

Addition of new column in d_product

Inventory Management

Applied Quantity: A Key Metric for Inventory Transaction Optimization

Use the look-up table to build the logic for flag_usage, flag_receipt and flag_shipment fields

Pharmaceutical Inventory Calculations

Pharmaceutical Sales and Inventory Monitoring

Populate product plant table using Data Mapping xls

Unsold Batches Analysis and Financial Impact Assessment

Inventory Management: Financial Impact Analysis of Expired Stock with Country-Wise Insights

Inventory Management with Expiry Risk category and Days-to-Expiry Analysis

DQ rule for Inventory management

Calculate the Reorder_stock, based on current inventory stock

Populate a new logic in view d_product

Inventory at Risk (IAR) Calculation in Drug Substance Not As Specified (DNSA)

Addition of new column in d_product

Populate Inventory Transaction table using Data Mapping xls

Calculate Days on Hand (DOH) usage for inventory

Calculate primary allocated qty of a plant's location for the drug sales

Pipelines

ETL

Data Engineering / ETL

Build pact_address table with its logic from the mapping specification

Dynamic insert/update of File Ingestion config tables using excel sheet

Metric Column Calculation Using Config Tables

Dynamic Management of Metric Config & Master Tables: Insert/Update Based on Excel

Partition C360 Config Queries Output

Construct SCD2-Based Historical Data on Indication Level Metric

Implement Dynamic Data Quality Scorecard for Weekly Metric Pipelines

Data value validation quality check with indication_config to target

DQ Validation on Queries in C360 Config Table

Implement Hash-Based Data Sync for Total NBRx Indication Metrics

Executing Queries in c360 Configuration Table

Derive Territory-Level CPD Variance Between Planned and Actual HCP Calls for BCBU Blue Ball Sales Team

Quarterly Analysis of REPATHA Product

Customer_360 - Data Unavailability for Metric ‘Rep Share of Voice’ in C360 PMR Consolidation View

TRF_TABLE Data Mapping

Sales Metrics Aggregation for Latest Week Ending for C360 Table

Pyscript code into classes and functions

Create a new field "HCC_Account_Id" in user table and logic update for "HCC_Account_name" field

Build agent_log Table as per Mapping Specification

Create "activity_final_log" Table Using Mapping Excel Sheet

Incorporate TAT Calculation for New Enrolment Review as per PDF Document

Generate Calculated Fields in profile_case table Using Excel Sheet

Evaluate Competitor Brand Penetration by Channel

Analyze Channel-Level Sales Distribution for Biosimilar Oncology Brands

Integrated Sales Calculation for Targeted Brands and Markets

Excel-Driven Insert & Update for Metric Config & Master Tables

Renaming Files in S3 with Date-Based pattern in the Suffix

Dosage-Response Curve Analysis for Drug Discovery

Drug Discovery on Compound Screening Analysis

Therapeutic Area Level of Clinical Study Analysis

Product Level Analysis of Clinical Study Trials

Migrating Vendor S3 File to Purgo S3 Folder

Delta Table-Based Metric Column Validation Using MINUS Query

DQ Checks from volume file to onboarded table

Update S3 Paths in ingest_config_master1 Based on Location and Source System

DQ_Check for Metric calculation

Dynamic Volume path Replacement in PySpark Using Mapping Sheet

Develop Function Result Matching Between Branches

Update the logic with Pyspark script

Prepare query to load purchase order table

Data Reconciliation after DBR Upgrades

Update f_intry_bal_dly_trf SQL script

Load f_intry_bal_dly_trf table

Update Logic in f_invntry_bal_dly_hist SQL

Configuration Master of Raw tables

Create f_invntry_bal_dly_hist using mapping Excel

Automated DDL Synchronization for Evolving CSV Structures

Archive Historical Data from customer_file_data_bronze_clone to customer_file_data_bronze_ar

Migrating to DBR 15.3+: Replacing Old PySpark Functions with New

Medical Inquiry Responses: Tracking Key Message Delivery Performance

Upgrading Scripts for Databricks Runtime 15.3 Compatibility

Advance Analytics on Sales data

Medical Inquiry - Urgent Inquiries are unfulfilled by threshold, summary of therapeutic with urgency

Medical inquiry and Medical Inquiry Response Insights - Urgency vs Response Status Analysis and Summary of Specialities

Medical Inquiry and Medical Inquiry Responses Insights

Dynamic achievement and rank calculation for terittory_sales_achievement

calculating actual sales achievement value

Data-Driven Customer Segmentation: Empowering Sales and Marketing Teams

Analyzing Cold Chain Temperature Data: Violation Detection and Insights

Batch Quality Analysis and Production Summary

Product ranking based on revenue

Analyzing Therapeutic Area-Wise Sales Growth Comparison: Q1 vs. Q2

Insights into Therapeutic Area Sales: Top and Underperforming Medicine Products

Generate outbound data

Anomaly analysis in claims Data

Add a new entry in config_master using the file in volume

Analyzing HCP Interactions: Revenue Conversion, Non-Conversion, and Overall Performance Metrics

Identifying High-Performing HCPs: Sales Per Minute and Conversion Rate Analysis Based on Interactions

Revenue and Interaction Metrics: HCP Conversion Analysis

Rectify the error and add file header to the query

To find the sales analysis, focusing on month and year-wise trends.

To Calculate the On-time Delivery percentage of supply chain partner to ensure delivery of life saving medicines

Partitioned parquet backup of customer 360 data

To Analyze the low performance region of medicine category by revenue threshold

Churn analysis on customer 360 data

Protecting customer 360 PII data

To handle claim rejection analysis

Ingestion of Staging Files Using Databricks Auto Loader

Change Data Feed(CDF) for stg_us_wholesaler table

Building data profiling table

Querying the CSV files in Databricks volume

Total revenue for delivered shipments by monthwise

Total shipment cancellation percentage by monthwise on client basis

Vaccum and backup - stg_nl_wholesaler

Inserting new records to config master table.

Data growth summary table - stg_nl_wholesaler

Non English to English word conversion

SCD2 for d_product table

Update the sql query in t1_master_replica

Masking last 4 digits of invoice number

Extract dimensions from product description

Shipment Cancellation rate

Display the required columns from d_product and d_product_hist

Build combined_agreement table from agreement and deal tables

Data Purging and Retention Strategy for Snapshot Tables

Partitioned data transfer from a Databricks table to S3 location

Standardize data for delivery_dt column in f_order table

Health checkup of RAW table 'items' to ensure data quality

Synch-up the daily incremental load with other downstream tables

Metadata system to capture pipelines health status

bottom of page