Search

reasonautomation.com

Vendor Profitability

Vendor Profitability

Vendor Profitability Dataset

icon
Table Name: basis_vendorprofit

This data model is a transaction-grain SQL table that consolidates vendor P&L activity across sell-in and sell-out channels into a single, flat model.

Each row represents a single financial or quantity event — an invoice, a co-op charge, a chargeback, etc. — tied to an ASIN, PO, invoice and date (sell-in) or ASIN and date (sell-out).

The key fields on every row are amount (the financial value), quantity (units), invoice_date (the event date), and invoice_date_first (the first invoice date for the PO, useful for grouping all downstream activity related to a purchase order to a single time period). How it's organized:

Rows are classified via a three-level hierarchy: pl_category → transaction_type / transaction_subtype. These tell you where in the P&L a row belongs.

The source and source_group columns identify the originating column/table from the raw data, which is critical for filtering — for example, source = 'Rebate in Agreement Curr' isolates the co-op billing amount within a distributor agreement.

What types of activity it covers:

  • Sell-in: invoiced amounts, distributor co-op rebates, invoice shortages, chargebacks
  • Sell-out: customer co-op, net sales
  • PO-level metadata: vendor code, distributor, agreement ID/title, currency fields

FX support:

Amounts are stored in original currency (amount) plus converted versions in USD, EUR, and GBP (amount_in_usd, amount_in_eur, amount_in_gbp), all using spot rates at the invoice date.

Important Caveats:

  • Expenses are stored as negative values (additive inverse of source data). So co-op charges, chargebacks, and shortages will be negative numbers — you sum them directly into a P&L without flipping signs.
  • Amazon provides no connection between sell-in and sell-out activity in vendor data - when a product unit is sold, the precise economic history of that unit from the sell-in side is not known. Building a comprehensive unit P&L therefore requires modeling techniques. For instance, a modeled unit P&L could be built by calculating per-unit expenses for sell-out and applying those costs to a sell-in P&L that offsets expenses against income from invoicing activity. Or, per-unit sell-in expenses could be applied to a sell-out P&L that uses shipped COGS as income.

Business questions & scenarios

What is this data useful for?

  • Vendor P&L by ASIN, PO, time period, or agreement
  • Per-unit economics (sell-in units from transaction_subtype = 'Invoiced Amount', sell-out units from 'Shipped COGS'). See above for caveats.
  • Co-op analysis by agreement, broken out by sell-in vs. sell-out
  • Multi-currency reporting with pre-built FX conversion

Schema & update details

UI Report path
N/A
Amazon update frequency
Daily
Reason update frequency
Daily
Time Zone
Country local time zone
Granularity [?]
1 row per: -Partner UUID -ASIN -Purchase Order Number -Invoice Number -Agreement ID -Invoice Date

Historical data & change management

Time Period
History available from Amazon
3 years
Reason lookback period
30 days

Data dictionary: fields in this table

How to query this table

Sell-In Co-Op Rebate and Net Receipts by Agreement

This query filters results based on ‘source’ , which usually is a reference to the column from which data was sourced. In the case of distributor co-op tables, amount where source = ‘Rebate in Agreement Curr’ is the co-op amount that was billed, and amount where source = ‘Net Receipts’ is the total value of inventory used in determining rebate for the particular agreement. Transaction_subtype is a commonly used aggregation level in the AVN dashboard.

Sell-Out Co-Op Rebate by Agreement

This query filters results based on ‘source’ , which usually is a reference to the column from which data was sourced. In the case of distributor co-op tables, ‘Rebate in Agreement Curr’ is the co-op amount that was billed, and ‘Net Receipts’ is the total value of inventory used in determining rebate for the particular agreement.

Per-Unit Calculations

The ‘Unit Economics P&L’ in BASIS Negotiation uses different values for units as the denominator for per-unit calculations, depending on whether the expense relates to sell-in or sell-out activity. The following query shows how to compute similar per-unit values directly from the model table.