Accounting Profit and Loss

Table Name: payments_invoices_expanded

This data model provides accounting-grade, ledgerized transaction data for 1P vendors. It adds important metadata to Amazon’s payment details, such as agreement types and categorization, that make it easier to aggregate and visualize. It also automatically converts all financial amounts into USD, GBP, and EUR in the same row using the daily spot rate.

• Helps bridge derivative invoices (e.g. INVOICE12345SCRSCR) to the original invoice (e.g. INVOICE12345), making it much easier to track and sequence payment, deduction, and reimbursement activity.
• Includes a ready-to-go P&L structure: everything is categorized in multiple ways, e.g. as an income or expense, transaction type.
• This table powers BASIS Negotiation P&L and Cost of Business reports

Business questions & scenarios

What is this data useful for?

• Profit & loss accounting. “What was our true net profit last quarter, vs. the previous quarter?”
• Reconciliation and auditing. “Did Amazon deduct the correct amounts from our payments, or were we over/underbilled?”

Schema & update details

Historical data & change management

Data dictionary: fields in this table

How to query this table

The following query generates a categorized P&L at the monthly level, with transaction-level detail.

select 
date_trunc('Month', invoice_date)::date as invoice_date
, apl_cat_i
, apl_line_i
, credit_debit
, funding_type
, payment_type
, description
, invoice_currency
, sum(amount_paid) as amount_paid

from public.payments_invoices_expanded

group by 1,2,3,4,5,6,7,8

On this page

• Accounting Profit and Loss
• Business questions & scenarios
• Schema & update details
• Historical data & change management
• Data dictionary: fields in this table
• How to query this table

Related pages

Invoice Payments

Paid Invoices