Avadel
Automated AWS Data Pipeline for Avadel Pharmaceuticals Helps to Cut Data Processing Time by 95%
95% faster processing
Reduced processing time from multi-day manual cycles to ~30 minutes per run.
Automated weekly ingestion & reporting
Scheduled pipeline replaced Excel-heavy workflows and manual checks.
Cost-effective alternative to an EDW
Automation without a large enterprise data warehouse investment.
Provided Services
Data engineering
AWS pipeline development
DevOps/containerization
Tech Stack
Python / Amazon Web Services (EMR, S3) / Hive / Hadoop / RabbitMQ / Docker / Splunk / Apache POI / Jython
Project duration
1 year
[ client & product overview/ ]
Avadel Pharmaceuticals is a specialty pharmaceutical company based in the US and Europe. In 2017, Avadel partnered with MEV to automate the processing of sales and market datasets used by their sales operations team
Need to eliminate manual reporting cycles?
Book a Free Consultation
Tap the button
Executive Summary
Avadel’s sales operations team used Excel-heavy, manual processing to manage territory datasets and produce reports. As data volumes grew, manual handling increased the risk of inaccuracies and created delays and rework.
MEV built an automated ingestion and processing pipeline on AWS, using EMR and Hive/Hadoop for scalable processing, with automated validation to replace manual checking. The pipeline generates structured XLSX outputs and supports downstream delivery (including a Salesforce path where applicable).
As a result, Avadel reduced data processing time by 95% and freed up internal time previously spent on repetitive manual work.
[ challenges/ ]
The Core Challenges Solved
An 8-hour manual Salesforce CRM refresh cycle that did not scale.
- Updating prescriber targeting data in Salesforce required a long, repetitive workflow.
- The process depended on exporting Salesforce contacts, using that list as a filter, downloading 1–34 IMS files one-by-one, running pivots per product/market basket, then manually copying Contact IDs + NPI into a separate import file.
Complex filtering rules (activity window, targets, rep changes) that lived in people’s heads and spreadsheets
- Which records to keep/update depended on rules like target lists, recent activity windows (last ~1.5 years), and rep changes (left/terminated/territory changes).
- The workflow also required removing zero-activity prescribers per market basket and keeping the dataset consistent across quarters/products.
Data volume and access constraints
- Weekly IMS prescription data arrived as multiple large files and needed to be merged with mapping tables (e.g., Zip-to-territory).
- Data access was constrained by US-only portal restrictions, so ingestion had to be designed around that constraint.
- Quality checks had to move upstream to prevent bad inputs from contaminating Salesforce.
Salesforce loading requires safe, repeatable mechanics
- The existing approach relied on exporting an object, deleting outdated records via Data Loader, then uploading the new dataset.
- Automation needed to replicate this reliably, without breaking downstream reporting.
Manual Workflow of Sales & Market Data Processing
7–8 hours total
[ how we did it/ ]
Solution & Implementation
We implemented an automated pipeline that takes weekly IMS prescriber data, applies Avadel’s targeting and territory rules, and refreshes the relevant objects in Salesforce without manual Excel actions.
S1:
Mapped the manual process into a repeatable data contract
- Documented the exact “inputs → filters → outputs” flow used by Sales Ops (Salesforce contact export criteria, activity window rules, target lists, rep changes, product/market basket logic).
- Defined the required output schema for Salesforce import (Contact ID, NPI, product/script metrics per reporting period).
- Aligned on which Salesforce object(s) get updated and what “refresh” means in practice.
S2:
Automated ingestion of the IMS dataset and reference files
- Built a scheduled ingestion flow for the weekly IMS feed and supporting reference tables, including Zip-to-territory.
- Designed around the US portal access constraint.
- Landed raw files in a controlled storage layer (e.g., S3 landing bucket) to keep every run reproducible.
S3:
Implemented scalable transformations for large multi-file inputs
- Parsed and normalized dozens of source files per cycle into a consistent intermediate format.
- Applied territory mapping and market basket/product logic.
- Processed large CSV datasets using a scalable compute approach (EMR + Hive/Hadoop).
S4:
Encoded business rules as automation
- Filtered the population based on the same rules Sales Ops used:
- target contacts for reps
- contacts with activity in the last ~1.5 years
- contact lists adjusted for rep changes
- Removed zero-activity prescribers where applicable (per product/market basket rules).
S5:
Added automated QA and anomaly checks before Salesforce is touched
- Validated file completeness, schema consistency, and key integrity (Contact ID/NPI mapping).
- Flagged abnormal deltas (e.g., unexpected drops/spikes in counts per territory/product) to prevent bad uploads.
S6:
Automated the Salesforce refresh step
- Generated a Salesforce-ready import file from the processed dataset.
- Implemented a repeatable load mechanism.
S7:
Scheduled weekly execution, implemented run-state signaling between steps, and ensured each run is repeatable from raw input.
[ results/ ]
Not only was the manual process automated, but we also built an inexpensive product compared to a large data warehouse, saving Avadel money and time.
R1:
Eliminated a 7–8 hour manual Salesforce refresh workflow
R2:
Reduced sales data processing time by 95%
R3:
Get rid of manual handling of large volumes of sales data in Excel
R4:
Avoid errors and inaccuracies in sales data
R5:
Enabled weekly updates from the IMS data feed
R6:
Automation without committing to a full enterprise data warehouse program
[ portfolio/ ]
Related Case Studies
[ contact us/ ]
No pitch-happy salesperson here.
Thank You!
The ball's in our court now.
We'll get back to you within 24 hours.
By submitting, you agree to our Privacy Policy
Preferences
Privacy is important to us, so you have the option of disabling certain types of storage that may not be necessary for the basic functioning of the website. Blocking categories may impact your experience on the website. More information
