How to automate specialty pharma commercial reporting

Build a weekly report from reconciled distributor resale activity. Normalize products, package units, customer accounts, and period coverage before calculating changes. Preserve returns and late corrections so the commercial team can reproduce the totals and see what changed.

Define the sales basis and reference data

Decide whether the report shows shipments, distributor resales, inventory, or another agreed measure. Keep these streams distinct to avoid counting one movement twice. X12 identifies the 867 transaction set as product transfer and resale reporting and the 852 as product activity data. Use trading partner specifications to interpret the fields and use the FDA package directory where appropriate for package identifiers. Preserve identifier segments as text and version any conversion between formats. X12 transaction sets · FDA NDC package definitions

Turn weekly partner files into a comparable report

What to automate

Track trading partner coverage

Build a trading-partner calendar of expected business weeks, document types and arrival cutoffs. On intake, preserve the raw files with sender, control number, business period, checksum and correction sequence. Detect exact replays and apply a correction only under the agreed partner rules. Send an incomplete or late source to commercial operations and label its report coverage.

Normalize products and units

Keep distributor package identifiers as source text. Join to the approved package and conversion mapping effective for the resale date. Convert signed quantities, with returns retaining their direction, and preserve both packs and normalized units. Route unmapped codes and inconsistent conversion factors to the product-data owner before the affected totals are signed off.

Resolve account identities

Link partner account keys to an effective-dated customer and territory crosswalk. Flag aliases with zero or multiple eligible links and keep them in a separate review queue. AI can propose candidates from approved business-name fields, with reasons, for reference-data staff. An approved mapping change creates a new version and has an auditable effective date.

Reconcile before publishing

Compare source and normalized totals at the partner and week grain. Account for returns and revised documents and record all included and held rows. Compare periods over the same source cohort when coverage changes, or hold the period per the approved materiality rule. Have Commercial Finance approve a basis that can be rebuilt from the source manifest.

Publish and review changes

Publish the signed-off week with net units, partner coverage and period basis visible. Compute material variance using agreed thresholds and attach affected product, account and source rows. Route the review to the commercial owner and track disposition. If a late correction changes the period, publish a new revision and preserve the prior version and adjustment reason.

Test a return and an incomplete week

In a synthetic fixture, a distributor reports 15 packs sold and two returned. With four reporting units per pack, the net is 52 units. A duplicate file should leave the total at 52. If a second distributor is missing this week, label coverage incomplete and compare only the same available sources across weeks or hold the report under the approved rule. Track source coverage, unassigned account share, correction latency, and report reissues. Resales describe channel activity and should retain that label in downstream reports.

Build a report that separates resale changes from source changes

Register feed_manifest(partner, feed, period, revision, received_at, expected_count, status) for each approved EDI 867 or equivalent feed. Normalize transaction identity, correction references, unit of measure and signed quantities at the agreed detail grain. Keep product, pack and territory rules effective-dated.

1. Confirm that each required partner delivered the correct schema and period. Flag a missing or stale partner separately from a partner that delivered zero valid rows. Track completeness by comparable partner coverage before calculating a trend.
2. Apply the partner’s contracted correction convention. Some partners send full replacement versions and others send adjustment transactions. Preserve both and use the corresponding rule. Convert each accepted quantity using the approved product and unit map. Hold unknown conversions or a change that straddles an unresolved effective date.
3. Group accepted signed quantities at the approved product, territory and period grain. Reconcile raw, rejected and normalized volumes by partner. Keep a return separate from a duplicate or a corrected source version and explain any changed comparison population in the report.
4. Test a corrected file, an unmatched product and a territory move. The report owner approves significant changes or restricted releases. An AI assistant may draft a source-linked explanation from approved aggregates.

WITH input(partner, transaction_id, revision, territory, quantity, units_per_pack) AS (
  VALUES ('P1', 'T1', 1, 'West', 100, 10),
         ('P1', 'T1', 2, 'West', 120, 10),
         ('P2', 'T2', 1, 'West', 40, 1)
),
current AS (
  SELECT *, row_number() OVER (
    PARTITION BY partner, transaction_id ORDER BY revision DESC
  ) AS priority FROM input
)
SELECT territory, sum(quantity * 1.0 / units_per_pack) AS normalized_packs
FROM current WHERE priority = 1 GROUP BY territory;

The result is West | 52.0 normalized packs. This fixture uses a full replacement correction. In production use fixed-precision conversion under the approved pack-rounding rule and verify corrections separately from any additive returns.

Implement the work step by step

The commercial analytics lead decides whether a report has comparable partner and account coverage and can support an investigation of channel changes. The workflow begins at the partner file cutoff for a product group. Ends with a reconciled report and owned review flags. Excludes patient demand, clinical use, or prescribing inference.

Ingest and stage

• Check expected partner coverage. Capture sender, document control number, version, checksum and effective week. Read Partner calendar, SFTP, EDI intake dashboard. Record Included and missing partner manifest. Owner Commercial ops. Escalate missing files to partner owner. Check Status distinguishes no reported sales from missing source.
• Interpret resale and correction transactions. Parse version-specific envelope and retain original/corrected links. Deduplicate document identity. Read Trading partner 867 specification and raw envelope. Record Typed partner ledger to commercial analytics. Owner EDI specialist. Approve partner-specific interpretation changes. Check Separate transfers, resales and returns under agreed reporting basis.

Normalize and reconcile

• Map product and normalize units. Join product/pack map by effective date and enforce valid units. AI can suggest candidate mapping with source labels. Read Product master, FDA identifiers, contract conversion. Record Canonical package and signed quantities. Owner Commercial analyst. Approve new or ambiguous product codes. Check No unapproved unit conversion. Preserve original package identifier.
• Resolve account and territory. Use approved identifiers and effective dates. Retain unmatched aliases. AI can rank candidate business accounts from approved fields. Read Business-account master, partner aliases, territory roster. Record Canonical account and territory with unresolved items flagged. Owner Reference-data specialist. Authorize additions to account or territory map. Check No patient identifiers used.
• Tie file and product control totals. Reconcile source quantities and standardized totals by product and feed. Build matching period set. Read Partner totals, canonical quantities and exclusions. Record Signed comparable report basis to owner. Owner Commercial finance analyst. Approve rule for incomplete period comparison. Check Display included feed coverage with each variance.

Publish and review

• Resolve report-blocking partner break. Issue keyed by sender, version and reason. Retain decision history. AI can summarize exception with source controls. Read Quarantine and partner ticket, raw control data. Record Accepted correction or explicit report hold. Owner Commercial ops. Obtain approved partner correction. Check Keep prior published basis traceable.
• Publish comparable report and assign reviews. Compute approved thresholds and snapshot report basis. Apply recipient controls. AI can draft aggregate movement explanation only. Read Review pack, variance thresholds and approved recipients. Record Released view and assigned investigation list. Owner Commercial analytics lead. Approve release and any commercial action. Check Test comparable denominator and confirm acknowledged delivery.

Parallel run. Shadow four weeks including at least one late partner and one corrected or duplicate batch. Require package and account parity and protect incompatible coverage from variance comparisons.

Build it yourself with AI

Create sample_partner.csv, packages.csv, accounts.csv and weekly_policy.md using fictional partner, package and account IDs. Use AI to draft a local pipeline that writes validated_units.csv, holds.csv and a report version with coverage.

1. Specify the data contract and mapping

   From the sample and policy, propose fields for the original control number, revision, business period, package code, signed pack quantity, approved unit factor and source account. Keep the package identifier as text. Flag ambiguous account links or package conversions for a data steward to approve.

2. Build reproducible weekly checks

   Write a Python command that computes standardized units and publishes a draft weekly report. Use 15 packs sold, two returned and four units per pack. Assert 52 net units after a duplicate file. Remove a required second partner from the fixture and show incomplete coverage plus a matched-cohort comparison or hold under the configured rule. Test a corrected document with a new revision.

3. Prepare a change summary

   Use only reconciled totals and the hold register. Summarize material changes with source row references and label which partners were included in each comparison. Put possible explanations in the commercial owner's follow-up list.

Run in parallel across a week with a return and a late file. Have Commercial Finance tie back the totals and inspect a held mapping before accepting a release. Save the exact inputs and approvals alongside the published report.

Need help automating this?

See how Mage reconciles distributor inputs and prepares comparable commercial reporting for weekly review. See Mage's specialty pharma commercial reporting solution.