Transactions P&L

Table Name: transactions_pl

The Transactions P&L table expands the functionality of the Amazon Seller transactions table as follows:

Adds P&L categorization patterned on Amazon’s Seller Central P&L reports (not provided by Amazon in the source data)
Adds an allocation model to assign P&L fees/costs with no SKU association to SKUs based on cost structure of the fee (e.g. per unit, or revenue based),
Imports Seller COGS data and calculates full COGS values based on customer shipments (requires COGS file upload)
Imports ad spend from sponsored ads tables (Sponsored Display and Sponsored Product), allowing substitution of transactions ads data with ad spend actuals
Enables simple P&L scenario modeling - e.g. see standard or allocated P&L model, with COGS and either transactions ad cost or ad spend actuals, as desired

Business questions & scenarios

What is this data useful for?

Profit & loss reporting: What is my net profit by product, category, or time period — including fees, COGS, and ad spend?
Ad efficiency analysis: Which products are being advertised at the expense of profit? Where is ad spend efficient relative to margin?
Fee analysis: What am I paying in FBA fees, storage, and Amazon commissions — and which SKUs are bearing the most cost?
Refund impact: Which products have the highest refund rates, and how are they affecting net revenue?
Unassigned charge allocation: How does SKU-level allocation of account-level fees (storage, subscriptions, ad costs) impact SKU-level P&L?

Schema & update details


UI Report path	N/A
Amazon update frequency	Daily, with ~24-hour latency
Reason update frequency	Daily
Time Zone	`date_time` in PST · `maturity_date` in UTC
Granularity	1 row per `_partneruuid` ×`transaction_id` × `breakdown_type`× `item_index`

Historical data & change management


History available from Amazon	2 years
Reason lookback period	30 days

Data dictionary: fields in this table

Table & data nuances

P&L Scenario Modeling

The transactions P&L table supports P&L scenario modeling. Each row carries six boolean columns that control which reporting scenarios it participates in. To avoid possible duplication, filter queries to where a single one of the following flags is always true.

Flag	What's included
---	---
`in_standard`	Source rows only. No COGS, no allocated charges, no ad actuals.
`in_standard_cogs`	Source rows + COGS. Account-level gross profit.
`in_standard_cogs_adactuals_nocoa`	Source rows + COGS + ad actuals. `ProductAdsPayment` excluded to avoid double-counting.
`in_allocated`	Allocated rows replace unassigned charges. SKU-level P&L without COGS.
`in_allocated_cogs`	Allocated rows + COGS. SKU-level gross profit.
`in_allocated_cogs_adactuals_nocoa`	Allocated rows + COGS + ad actuals (COA excluded). Full SKU-level P&L.

P&L classification

Source rows are classified into category, transaction (transaction label), and line_item that maps transaction_type × breakdown_type combinations from source data.

Transaction labels (`transaction` field)

Transaction label	Category	Notes
`Product charges`	Sales	`OurPricePrincipal`, tax lines, giftwrap, promos
`Shipping`	Sales	Shipping principal
`Inventory Reimbursements`	Sales	FBA reimbursements for lost/damaged inventory
`Refunded sales`	Refunds	`OurPricePrincipal` and `OurPriceDiscount` refunds
`Refunded expenses`	Refunds	Commission, shipping, and fee refunds
`FBA Fees`	Expenses	Storage, long-term storage, removal, disposal, inbound fees
`Amazon Fees`	Expenses	Commissions, fulfillment fees, subscriptions, coupons
`Cost of Advertising`	Expenses	`ProductAdsPayment` — excluded in `_adactuals_nocoa` scenarios
`Promo rebates`	Expenses	Shipping discounts on shipments
`COGS`	COGS	Unit cost rows from `product_cogs`
`Advertising`	Ad Spend Actuals	SP and SD actual spend (negative amounts)

Allocation model

Amazon's transaction data includes account-level charges with no associated SKU — FBA storage fees, subscription fees, advertising payments, etc. The allocation model redistributes these to SKUs so that SKU-level P&L reports carry a fair share of overhead.

Identify unassigned charges — rows where sku = 'no sku', aggregated by partner, transaction type, breakdown, marketplace, and month.
Determine allocation basis (alloc_bucket):

fba_qty — FBA fees and inventory reimbursements. Weight = SKU's AFN shipped quantity ÷ total AFN quantity.
sales_amt — advertising costs, Amazon fees, all other expenses. Weight = SKU's sales revenue ÷ total revenue.

Rolling 60-day weights — for each charge month, weights are computed over the 60 days ending on the last day of that month. Falls back to all-time partner weights if no 60-day data is available.
Output — allocated rows appear with category = '4. Allocated', is_allocated = TRUE, and allocation_method of either rolling_60d or partner.

COGS model

COGS rows are produced by joining shipment and refund quantity data to product_cogs (unit costs by partner, SKU/ASIN, month).

Shipment quantities are signed negative (cost of selling).
Refund quantities are signed positive (cost recovery on returns).
Where no matching cost exists, the row is included with cogs_status = 'missing' and amount = NULL — visible in counts but not in sums.

How to query this table

As with the source table (transactions) use the deferred_transaction_id filter to control how deferred transactions are handled. Two common patterns:

Use Case 1 — All sales and costs (deferred status doesn't matter):

select
...
from public.transactions_pl
where transaction_type = 'Shipment'
    and deferred_transaction_id is null
    and date_time >= '2025-01-01'
order by date_time desc

Use Case 2 — Only transactions that have been paid:

select
   ...
from public.transactions_pl
where transaction_status in ('Released', 'DEFERRED_RELEASED')
    and deferred_transaction_id is null
    and date_time >= '2025-01-01'
order by date_time desc

Standard P&L by category (account-level):

select
    date_trunc('month', date)   as month,
    category,
    transaction,
    sum(amount)                 as net_amount
from transactions_pl_detail
where _partneruuid = 'your-partner-uuid'
  and in_standard = true
  and deferred_transaction_id is null
  and date_time >= '2025-01-01' 
  and transaction_type is not 'Transfer' --excludes batch payments made by Amazon to Seller
group by 1, 2, 3
order by 1 desc, 2

SKU-level P&L with allocated fees, COGS, and ad spend:

select
    date_trunc('month', date)   as month,
    sku,
    asin,
    category,
    transaction,
    sum(amount_in_usd)          as net_amount_usd
from transactions_pl_detail
where _partneruuid = 'your-partner-uuid'
  and in_allocated_cogs_adactuals_nocoa = true
  and deferred_transaction_id is null
  and date_time >= '2025-01-01'
  and transaction_type is not 'Transfer' --excludes batch payments made by Amazon to Seller
group by 1, 2, 3, 4, 5
order by 1 desc, 6 asc

Category	Description
`1. Sales`	Product revenue, shipping, giftwrap, marketplace tax pass-throughs, inventory reimbursements
`2. Refunds`	Refunded sales and expenses — commissions, shipping, tax pass-throughs
`3. Expenses`	FBA fees, storage, commissions, advertising charges (ProductAdsPayment), subscriptions, debt recovery, misc adjustments
`4. Allocated`	Unassigned charges redistributed to SKUs
`5. Ad Spend Actuals`	Real ad spend from Sponsored Products and Sponsored Display
`6. COGS`	Cost of goods sold from `product_cogs` unit costs × signed shipped quantity
`7. Transfers`	Fund transfers — excluded from P&L totals