Part of the Seller Profit Toolkit, this table is designed to make it easier to leverage Amazon Seller transactions data for profitability analysis at the product/SKU or whole business level. It combines data from the two Seller transactions reports (transactions_standard_orders and transactions_invoiced), transforming this data into a ledger-style table that is well-suited for financial and business analysis of profit performance. It also incorporates ad spend actuals derived from marketing reports (ads_sponsored_products_advertised_product, ads_sponsored_brands_campaign, ads_sponsored_display_advertised_product), allowing Sellers to include advertising costs in profit analysis.
Statistics
Updated: Daily
Backfill: 2 Years
How to use:
The SPT ledger table is easier to use than the transaction tables from which it is sourced. First, all monetary values are contained in a single column (‘amt’) rather than spread across multiple columns. Second, all transaction rows are categorized as to their position on an Amazon P&L report (available via Seller Central export). For instance, one can create a basic whole-business P&L by month using the following query.
select
apl_category, apl_line_i, sum(amt)
from pl_ledger_table
group by 1,2
‘apl_category’ is the major P&L category, e.g. income or expenses, while apl_line_i is the individual P&L line item, with a letter prefix to allow for sorting that matches the order of the Amazon P&L. The above query can be extended in several ways. You can of course add or filter by date, or by SKU for product-level profitability.
‘Units’ data can be confusing to use within transaction data, and you will generally need to filter for your specific needs when querying for units. For instance, if you wanted to calculate “Net Units Sold” to use as a denominator for per-unit calculations, you could define “units ordered” as [sum(quantity) where TYPE = ‘Order’ and line_item = ‘product_sales’], and subtract from that “units returned” [sum(quantity) where TYPE = ‘Refund’ and line_item = ‘product_sales’].
Data Dictionary
