AnalystIQby Algoscale

Customer story · Oncology

A leading global oncology biotech: 100k+ HCP engagements answered in plain English.

A cross-listed cancer-drug company with a portfolio spanning hematology and solid tumors, 10,000+ employees, and a commercial footprint across 70+ markets connected their Redshift warehouse to AnalystIQ. Their commercial, medical affairs, and analytics teams now ask 65+ business questions a week — from “list top 5 HCPs by payment” to “HCPs who got payments from 5+ companies, published >1 paper in 2020, and had no events in 2021” — without writing SQL.

Industry

Oncology / hematology biotech

Footprint

70+ markets · 10,000+ employees · cross-listed

Source

Amazon Redshift

Scale

100k+ HCP engagements & events

Users

Commercial · Medical Affairs · Analytics

Result

65+ business questions / week, plain English

Why they came to us

One ask box, every HCP domain.

The customer's HCP data lake on Redshift — spanning 100k+ engagements across payments, publications, trials, events, detailing, and sales — had been queryable only by their analytics team. A typical commercial question (“HCPs in our therapeutic area who got >$10k from competitor X, were PIs in a trial, and published >2 papers”) sat in the backlog for days. They wanted the same Redshift, same RBAC, same governance — just a natural-language layer on top.

How they set it up

Four steps. Four Analysts.

  1. Step 01

    Connected Redshift

    One read-only role, scoped to seven tables: HCPs, payments, publications, events, trials, detailing, sales.

  2. Step 02

    Built the semantic layer

    AnalystIQ profiled the schema and drafted column descriptions; their team curated join paths between HCPs and activity tables.

  3. Step 03

    Hired four Analysts

    Commercial, Medical Affairs, Trials, and Engagement — each bundling the right subset of tables for that team.

  4. Step 04

    Rolled out

    Commercial and medical affairs leads got the link. The analytics ticket queue dropped within a week.

The range of questions

From simple counts to ten-table joins.

Real questions the customer's team asks every week, grouped by SQL complexity. Each one was answered without a human writing the query.

Tier 1

Lookups & counts

Seconds · simple SELECT
  • “List top 5 HCPs with highest payment”
  • “What are the products for Amgen company?”
  • “How many HCPs spoke at ASCO?”
  • “How many publications related to cardiovascular?”
  • “Total detailing count by month”
  • “Show sales data for Lipitor”

Tier 2

Filters, conditionals, ranges

Seconds · WHERE + CASE
  • “List the events happened in January”
  • “Which HCP has the most number of journal titles?”
  • “What is the most frequently observed phase across all trials?”
  • “List the year and month where there were no publications”
  • “Show HCPs where payment from Pfizer in 2023 was above 10,000 and label them as high_value or low_value using CASE WHEN”
  • “How many alphabets are there in re_terr codes?”

Tier 3

Cross-table joins

< 10s · multi-table FROM
  • “Any HCP from Novartis who was involved in publications in 2021?”
  • “What are different event roles the HCP with highest detailing in 2024 has exhibited in 2024?”
  • “Show HCPs who participated in events and received more than $10,000 in payments”
  • “Identify the HCP with the highest number of completed trials who also has at least one publication”
  • “Show HCPs who spoke at events in 2024 but didn't publish any article in 2023”
  • “Show first author who also spoke at events and received no payment from Pfizer”

Tier 4

Window functions, CTEs, period-over-period

< 15s
  • “What is the percent increase of total payments by Pfizer over the years?”
  • “RANK HCPs by total payments within each year”
  • “Find HCPs who received payments larger than their own average across all years”
  • “For HCPs who presented in events, show detailing counts 3 months before and 3 months after the event”
  • “List HCPs who published in 3 consecutive years, but didn't speak at any events since 2020 and received payment from more than 2 companies”

Tier 5

Multi-source joins with nested conditions

< 25s
  • “List HCPs who received money from 3+ pharma companies but never from us — high-priority outreach targets”
  • “How many HCPs received more than 5 detailing and also attended more than 1 event in 2021?”
  • “HCPs who spoke at virtual events in 2024, authored publications containing 'lymphoma' and did not receive any payment from Pfizer”
  • “HCPs with >3 publications, ≥2 events, and payments from ≥3 companies in 2023”
  • “Determine the HCPs who received payments from 5 or more different companies in 2023 and 2024 and have more than one publication in 2020 and no events in 2021. For these HCPs, show the average payment amount by company.”

What changed

The analytics queue stopped being the bottleneck.

Backlog cleared

Routine HCP / payments / publications questions stopped reaching the analytics team — they refocused on modeling work the natural-language layer can't do.

Commercial self-serve at scale

Reps, brand managers, and medical affairs leads ask their own questions across 100k+ HCP engagements (“Show HCPs who got >$20k from competitor X and are PIs in trials”) and act on the answer the same day.

Same governance

Redshift's read-only role + table-level scoping enforce who sees what. AnalystIQ never copies data out — every query runs in their warehouse.

See if AnalystIQ fits your stack.

We've shipped Redshift, Snowflake, Postgres, MySQL, SQL Server, Fabric, Athena, Google Analytics, HubSpot, and MCP today.