Case study · Pharmaceutical sector

Automated pharmacy sell-out: real demand, ready for AI

You see what actually sells. On a single governed source. With the foundation ready for AI.

Validated and governed data

Automated sell-out

Foundation ready for AI

Where it all started

Sell-in was growing.
Sell-out was an unknown.

A multinational pharmaceutical company in generics and biosimilars, operating in Spain, measured its business by sell-in: what enters the channel. But sell-out (what the pharmacy actually dispenses to the patient, real demand) arrived late, scattered and by hand: each pharmacy sent its file by email, in its own format, with the anonymized pharmacy code, the national product code and the units, and someone consolidated it.

With no real demand in view, you cannot tell overstock in the channel (low sell-through) from a drop in demand, nor calculate real share on sell-out (In-Market Share), nor see when the Evolution Index falls below 100.

The challenge

Sell-in was rising, but without reliable sell-out you could not tell overstock in the channel from a real drop in demand.

Sell-out files by email, in different formats (Excel, CSV, TXT), consolidated by hand.

No control over the national product code or the consent (opt-in / opt-out) of each pharmacy.

No real share on demand (In-Market Share) and no timely read of the Evolution Index by brand and territory.

No foundation on which to apply AI to forecast demand and feed the S&OP.

What we proposed

Automate the capture of sell-out end to end: from the pharmacy's email to a governed SQL database on Azure, validated and available in Power BI. Every file passes automatic checks (structure, national product code and consent) and, if something fails, a proactive alert is triggered. With that foundation of real demand, In-Market Share, sell-through and Evolution Index, the ground is ready for the pharmaceutical advanced analytics AI layer.

The shift

Day and night

Going from reading the business only by what enters the channel to reading it by what actually sells in the pharmacy.

Before

The business was read by sell-in; sell-out, blind

Pharmacy files consolidated by hand

Product code errors and pharmacies without consent slipping through

No foundation to apply AI

Now

Real demand (sell-out) comes in on its own, validated and governed

From email to Power BI, with nothing consolidated by hand

Every file is validated (structure, national code and consent); if something fails, a proactive alert

A foundation ready for forecasting, In-Market Share and anomaly detection with AI

formats captured and normalized (Excel, CSV, TXT)

automatic checks (structure, national code, consent)

single governed source of sell-out

The value generated

What changes when you see real demand

Real demand, in view

You see what the pharmacy actually dispenses, not just what enters the channel.

Sell-out end to end

From the pharmacy's email to Power BI, with nothing consolidated by hand.

Clean and compliant data

Validation of structure, national product code and consent (opt-in / opt-out), with an anonymized pharmacy code.

Sell-in and sell-out, finally together

The foundation for seeing overstock (sell-through), demand drops and share loss (In-Market Share, Evolution Index).

Alerts before it hurts

Proactive warnings for faulty files, invalid codes or missing consent.

Ready for AI and S&OP

On this foundation, advanced analytics forecasts demand, detects sell-in/sell-out anomalies and feeds the S&OP.

What the client says

For years we looked at sell-in and took the rest for granted. Now we see the pharmacy’s real demand, clean and on time, and we know where there is overstock and where we are losing share. Best of all: the data is now ready for AI to tell us where to act.

Commercial Excellence Management

Multinational pharmaceutical company

F&Q

Frequently asked questions

What is automatic capture of pharmacy sell-out?

It is a system that automatically collects, validates and unifies the sales data pharmacies send to the laboratory, turning it into a single source of real demand usable in Power BI. At BertIA, an AI consultancy based in Barcelona, we build it on Azure so the data comes in on its own, clean and governed, with no manual consolidation.

How is sell-out different from sell-in, and why does it matter?

Sell-in is what the laboratory sells to the channel (distributors, pharmacies); sell-out is what the pharmacy sells to the patient, that is, real demand. Looking only at sell-in hides overstock in the channel and demand drops. Crossing the two lets you calculate real market share (In-Market Share) and the Evolution Index, and anticipate a loss of position.

Is the data reliable and compliant?

Yes. Every file passes three automatic checks before it is accepted: structure and format, national product code and pharmacy consent (opt-in / opt-out). If something fails, an automatic alert is triggered. The result is traceable, governed data, compliant with the information-sharing policy.

How does this leave you ready for AI?

Because AI needs a reliable, governed database to work. With sell-out unified, that foundation already exists: on top of it, pharmaceutical advanced analytics applies AI and machine learning to forecast demand, detect anomalies between sell-in and sell-out, and recommend which product has the most potential and which pharmacy to target. The data project stops being the end and becomes the starting point for AI.