Seller Profit Toolkit - User Manual

The Seller Profit Toolkit (SPT) gives Amazon Sellers much-needed insight into their profitability on the Amazon platform by delivering a complete picture of profit - by product/SKU, by unit and in total, and incorporating COGS and ad spend actuals. This guide is intended to help SPT clients get the most out of the toolkit.

Current version: 2.0

Introduction

The Seller Profit Toolkit combines Transactions data with Advertising spend and COGS, giving you a complete picture of profit by product/SKU, by unit, and in total. It:

• optimizes your existing Amazon transactions data for accounting use
• adds Amazon-matching P&L line classification for all transactions
• incorporates your retail media spend data (Sponsored Products/Brands/Display) automatically imported from your Reason Automation data tables
• imports COGS data (which you provide by SKU and date)

The SPT consists of two components:

1. SPT Report Suite - a Power BI-based reporting suite containing pre-built reports that give you new insight into total, SKU and unit-level profit at all P&L levels.
2. SPT Ledger Table - a table that can be accessed directly via SQL (and also serves as the primary data source for SPT Reports).

Data in SPT Reports are at the monthly and Settlement ID grain, while SPT Ledger Table maintains daily data grain.

This User Manual deals primarily with the SPT Report Suite, although some instruction for direct table access is included.

SPT Report Suite

You’ll find SPT Report Suite in your Reason Automation customer portal at app.reasonautomation.com, under the Dashboards menu. A full glossary is included at the end of this manual and also in the Report Suite on the Guide tab, but here are a few important definitions that should not be missed.

Profit/Amount Amount is referred to as Profit in this dashboard when all necessary P&L lines are included to give a complete profit measure. When looking at a subsection of a P&L, e.g. FBA fees in isolation, the term 'Amount' is used.

Net Units Sourced from the 'quantity' column in transactions data, Net Units is units ordered minus units returned. Per-unit calculations in SPT Reporting Suite use Net Units as the denominator, with included units filtered for reporting context (sometimes Net FBA units is used, e.g. for per-unit calculation of FBA fees in dedicated expenses reports).

CTT- Contribution to Total The share or contribution of a given row to the total amount of the column.

CTC - Contribution to Change A measure of a row's contribution to the total amount of change between two periods, shown in bps (basis points; 100 bps = 1 percentage point). All of the individual CTCs should add up to the total CTC, which should also equal the percentage change for the period, expressed in bps. For instance, if you had a +8.50% change in total profit between two time periods, that's 850 bps of change. In a simple portfolio of five products, you might see that the five products each drove change (had a CTC) as follows: + 1200 bps, + 300 bps, + 50 bps, -100 bps and -600 bps, bridging the total 850 bps of total change observed.

SPT Report Suite Organization

Here’s a rundown of the reporting tabs found in the Report Suite, and what they contain.

tab: Guide (Guide to Use)

Part usage guide/metrics glossary, part live data. Here are a few important takeaways from this tab:

1. Controls for time period and Account Type appear at the top of this page and most other report pages, and are vital to understand. The date control selects the active month. Account type has two possible values - Standard Orders (B2C orders, sourced from transactions_standard_orders table) or Invoiced Orders (B2B orders, sourced from transactions_invoiced table.) Select one or the other, or both simultaneously as needed for reporting.

2. Data Status. A table on the top right side shows the most recent data for the SPT Ledger Table and the transactions tables from which it is sourced. ‘Last Check’ is exactly that - “when were these dates last checked?” Ledger Table should not be any more than 48 hours out of date with source tables.

3. P&L Examples. SPT Reports features an “Expanded P&L”. It is based on the logic and contents of the Amazon P&L available via PDF download in Seller Central, but with some important differences, outlined below. Both “Amazon Matching” and SPT Expanded P&L examples are shown, with live data.
1. Improved advertising reporting. Your media spend actuals data appears in-line in SPT P&L reports, providing you have the necessary tables hosted with Reason Automation. The 'Cost of Advertising' P&L line in transactions data is removed from these reports, as it would be duplicative, and is also an unreliable source of advertising spend data.
2. Includes Cost of Goods Sold (COGS) data. Provided you have uploaded a COGS file to your schema, COGS per unit and total COGS will be incorporated into your profitability data.
3. Taxes and Transfers are generally removed from SPT P&L reports.
4. Glossary - metrics defined/explained.

tab: Exec (Executive Summary)

Shows a high-level view of profit in-total and per-unit for any available month, along with P&L trends over time. See at-a-glance metrics for the month along with P&Ls and trend charts. All the way at the bottom, view a “Movers” table that shows key profit stats by SKU. At the bottom of the page, see a full list of key profit stats by SKU.

Suggested Interactions:

• Click on any line in the Total or Per-Unit P&Ls, and the charts for Profit by Major P&L category and Total Profit/Profit Per Net Unit will be filtered to that P&L line/category (see note above about Profit vs. Amount - these reports would now be showing the filtered metric, not total profit).
• In the Movers table, sort by MoM Abs (month-over-month absolute change) or YoY Abs (same but for year over year) to see top drivers of profit increase and decrease period over period. Bonus - click on any SKU for a quick look at total and per-unit P&Ls and charts on the page filtered for that SKU (although you’ll likely find the SKU-focused tabs more useful).

tab: Movers

Literally, SKUs on the move - a set of tabular reports showing what products drove the largest gains and drops in absolute profit MoM and YoY. Also shown here are products driving the largest increase in returns by unit YoY, as well as SKUs by Ad Spend.

Suggested Interactions:

• The profit gain/drop reports are filtered to the largest 25 contributors, but if you want to see all SKUs, turn off that filter (export the results by clicking on the ellipses that appears on the top right of the table when you hover). Also check out the Exports tab for tabular reports designed and optimized exclusively for export.

tab: Bridge, Bridge/SKU

The Bridge tabs are so named because they allow you to bridge (explain) MoM or YoY changes in profit and ascribe an amount of change driven by each P&L line or SKU, down to the nearest basis point (bps) using Contribution to Change (CTC).

The two screenshots below show the top and bottom of the “sorting” table on the Bridge/SKU tab for a given month. SKUs are sorted by ‘CTC YoY’, ascending, meaning largest contributors to decline are at the top and to gain are at the bottom. The total change we are bridging is shown in the total row of the table, and is +775 bps YoY (note the 7.8% total YoY change - this would be 7.75% if another decimal point were shown).

The top declining product drove -576 bps of profit change for the period. If absolute profit for that product were flat YoY (had no change in profit, and therefore no contribution to change), the total profit change would have been +1,351 bps.

Meanwhile, our best performing SKU in terms of YoY absolute profit drove +651 bps of change. The data bars on CTC columns correspond to magnitude of CTC at the row.

The Bridge tab shows the same information organized by major P&L category and line_item. If you wanted, you could sort by YoY change as with the SKU table, but most users find keeping the P&L structure in order makes more sense.

Suggested Interactions:

• For SKUs or P&L line items of interest, click to see charts to the right filtered to that SKU or line (see notes above about Profit vs. Amount).
• Take note of major SKU drivers of MoM/YoY change, and investigate further using the SKU tabs.

tab: SKU YoY, SKU MoM

These reports provide a deeper understanding of what’s driving profit changes between periods at the SKU level. The reports to the right of the SKU Selector are grouped vertically - per SKU (i.e. all units of that SKU in aggregate) on the left, and per SKU unit (an average single unit of that SKU) on the right. Each group has a KPI block and three P&L views - by major category, a chart showing trend over time, and a detailed P&L.

Clicking on a SKU in the selector filters all of the reports to that SKU only:

Here are some observations we might make about this SKU:

• Total profit in this time period is $2,247, down -$4,373/-66.1% from the same period last year. In the context of change in total profit across all SKUs (+775 bps), that’s a -568 bps contribution to change.
• The largest driver of that change (by far) is Income (-1561 bps CTC YoY), which usually points to a YoY sales drop. The KPI block for the per-unit column confirms that units are down -60.9%. In the same block, note that return rate is rather high, although improved by -106 bps from last year.
• Looking at the per-unit major category P&L we see that ad expenditure for this SKU was -$12.04 last year and -$27.48 this year, an increase of -$15.45 per unit (these numbers are all negative because ad spend is of course an expense - any ad spend is negative in a P&L context, and an increase in ad spend is also a negative number).

Trend charts show how income, expenses, ads and COGS are playing out over time, alongside net units (hover on charts and click ‘Focus Mode’ in top right cornerto view them more closely).

Here’s the detailed P&L for total SKU and per SKU unit:

• The detailed P&L confirms the drop in income is driven by FBA Product sales (-2297 bps CTC), offset by the YoY reduction in in returns/refunds, an improvement that yields +776 bps CTC.
• The +537 bps CTC from total expenses is driven by reduction in FBA selling fees and transaction fees. Looking to the per-unit P&L, these fees have also improved on a per-unit basis, indicating it’s not just a matter of fewer units leading to lower aggregate fees (NOTE - this is demo data, and may not resemble real business conditions).

tab: No SKU

Some rows in Amazon transactions data are not attributed to a particular SKU, and can’t be included in per-SKU or per-SKU-unit measures of profit. The No SKU tab gives insight into the magnitude of these unattributed amounts in total and per unit, so that they can be considered as part of unit-level profitability. Note that these amounts are included in reports that do not filter to SKU.

In this example, a net per-unit expense of -$3.46 is seen for this particular month. The bulk of this is FBA-related inventory and inbound service fees. It would be appropriate to apply this averaged amount to any per-unit FBA P&L. We continue to research more precise ways of attributing these fees at the SKU level, but in the interim, the “peanut buttering” approach of applying an average amount per unit is preferable to ignoring these unattributed line items.

tab: Settlement (P&L View by Settlement ID)

These reports shows an Amazon-matching P&L by Settlement ID, which should match equivalent Seller Central reporting at the settlement ID grain. A per-net-unit version is also shown (Amazon does not provide a per-unit equivalent). Select settlement ID with the slicer on the right. Settlement periods usually span 14-15 days - view the exact settlement period start and end dates/times and account type of the selected settlement ID in the KPI bar.

tab: Fees and Expenses

This tab focuses on the expenses section of your P&L. One thing to note right away is that all of the charts on this page negate the expense value (flip it from negative to positive) to improve readability. The P&L section (table) retains negative values for expenses, so when looking at MoM/YoY change, a negative value is an increase in expenses, while a positive value is a decrease.

A chart at the bottom of the page shows expenses in a stacked-column chart with net units, giving a visual indication of fee movement over time.

Suggested Interactions:

• Click on rows of the Expenses P&L to see charts on the right filtered to that row
• Adjust the reporting month and account type to filter the Expenses P&L

tab: Ads

Reports on this tab give a high-level view of advertising in relation to both product sales and profitability with a YoY (when data is available). The well-known advertising performance metrics TACOS (total ad cost of sales) and TROAS (total return on ad spend) are shown alongside ad spend and profit at the total and per-unit level. The table on the left of the page shows all SKUs ranked by ad spend (you can sort by other columns as well). Charts on the right show trends in ad spend, net units, profit and return.

Suggested Interactions:

• Click on SKUs in the Ad Spend table filter charts on the right to that SKU
• Adjust the reporting month and account type to filter the Ad Spend table

tab: Ads - Profit on Ad Spend

Is your ad budget optimized for driving sales on profitable products? Are you directing ad funds to products that are losing money? If so, what’s the plan - do you a) have a concrete path to improving profitability, or b) a strategic reason for driving sales of unprofitable products? If the answer to both questions is no, then you (or your marketing department) are likely throwing good money after bad.

Total Profit on Ad Spend

This tab features a new metric, TPOAS (total profit on ad spend), designed to highlight how effectively ad spend is driving profit at the SKU or total. Like TROAS (total return on ad spend), TPOAS is expressed as a currency amount. It is calculated as profit/ad spend.

Note that the profit in the numerator of the TPOAS metric includes ad spend. To understand how TPOAS should be evaluated in low-to-negative profit scenarios, consider the following table, which shows TPOAS calculated for a fixed ad spend of $1 and a net profit that decreases in $1 increments. By the third row, we have a net profit of $1 without ad spend, and the $1 we spend on ads consumes that profit, for a TPOAS of $0.00.

If you were to calculate TPOAS without ad spend in the numerator of the TPOAS metric, the resulting “modified TPOAS” metric would always be $1.00 higher than as calculated above, no matter what the ad spend and profit involved. The reason for this is clear when the TPOAS formula is broken out as follows:

Note that the profit in the numerator of the TPOAS metric includes ad spend, which captures the true current profit. If you were to remove ad spend from the numerator of the TPOAS metric, it would always increase TPOAS by $1.00 no matter what the ad spend and profit involved. The reason for this is clear when the TPOAS formula is written as follows:

The table on the left of the page shows all SKUs ranked by ad spend (you can sort by other columns as well). Charts on the right show trends in ad spend, profit and return. Click on any SKU in the table to filter charts to that SKU.

Visualizations

Several new visualizations are introduced on this tab for the 2.0 release.

The below chart shows how ad spend is impacting per unit profit. Grey bars are per-unit profit without ad spend, and blue bars (below the zero axis) show ad spend actuals per unit. The orange line shows the sum of the two - actual net profit per unit. This chart makes it easy to see where ad spend is primarily responsible for driving profit issues at the SKU and total level.

These next two charts above put TROAS and TPOAS in context with their inputs - total return (product sales) and total profit respectively. Both charts show ad spend and resulting TROAS/TPOAS in orange.

Suggested Interactions:

• Click on SKUs in the Ad Spend table filter charts on the right to that SKU
• Adjust the reporting month and account type to filter the Ad Spend table

How to upload your COGS file

Vendor and Seller Profit Toolkit dashboards will automatically integrate your uploaded COGS data for use in profitability reporting.

1. Log in to our client portal and browse to Manage Your Data > Upload Data. You should see the following (please reach out to us to request this function be activated if you do not see it):

2. Click on the down arrow to download a sample template (CSV format). The column headers should be: date, sku, COGS.
3. Add your COGS data. You’ll want to have a COGS per unit value for every possible SKU-month combination in the data. You can use a SQL query to help you create a starter file if desired. Note that the file must contain only unique SKU-month combinations.
4. Save the completed file as a CSV (not an XLSX), and click or drag to upload to the portal.
5. Your COGS data will appear in your schema, in a table called seller_profit_cogs

Note for Vendors

In the SKU column of the upload template, please use the Amazon ASIN instead of the SKU number.

Notes for Excel users

1) Dates in the CSV need to be in YYYY-MM-DD format, but Excel will reformat them as mm/dd/yyyy when you open the file. Before saving your file as CSV for upload, make sure the dates column is switched back to YYYY-MM-DD format (in Excel ‘Format’ dropdown, select Category > Date and then '2012-03-14'

2) Excel often adds a blank row at the end of a CSV file, even if you delete blank rows. If you get upload errors, check for the presence of a blank row at the bottom of the file using a text editor such as Notepad (note - we will be launching an update soon that will allow for blank rows at the end of a file to eliminate this issue).

3) If you have SKUs that are long numbers, Excel might turn these into scientific notation, causing an error when you go to upload because of duplicative SKU-date combinations. To set the right format, select the Excel Format dropdown (it will likely say ‘General’), select More Number formats > Custom > 0 (see image below).

Product Catalog Upload

The Seller Profit Toolkit (SPT) now supports Product Catalog Upload, allowing you to associate a SKI with its own ASIN, Category or Subcategory, or additional custom tags, and then use those values as filters or reporting grains within the SPT. View more information on how to upload your own custom catalog here.

SPT Ledger Table

The Seller Profit Toolkit Ledger Table (pl_ledger_table in your schema) can be accessed directly via SQL query using your preferred SQL admin platform (we recommend pgAdmin).

Use cases for working directly with Ledger Table data include:

• Exporting data for further analysis in tools such as Excel
• Joining SPT data to other systems such as ERP, shipping data systems, other analysis tools

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 called {amt} (short for ”amount”) 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). View the Ledger Table schema here.

Simple Queries

query: Basic P&L

The following query generates a basic whole-business P&L by month:

select
	apl_category
	, apl_line_i
	, sum(amt)

from pl_ledger_table

where 
	date_trunc('month', date_time) = '2023-06-01'

group by 1,2
order by 1,2 asc

{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. Add a particular SKU to the “where” clause to generate a product-level P&L:

where 
	date_trunc('month', date_time) = '2023-06-01'
	and SKU = 'ABC_123'

query: Detailed P&L

If we wanted to pull profit data in a form suitable for further analysis in another tool (e.g. pivot table in Excel) we can extend the above query by pulling month as a column, and adding desc_short, which is a rolled-up version of the original {description} column from transactions source data. The full {description} column is also available in the ledger table if needed.

select
    date_trunc('month', date_time) as date
	, apl_category
	, apl_line_i
	, desc_short	
	, sum(amt)
	
from pl_ledger_table

group by 1,2,3,4

query: Starter COGS File

The Seller Profit Toolkit Report Suite automatically imports your per-unit COGS data for use in profitability reporting, but first you need to prepare and upload a COGS file to your Reason Automation portal. The following query gives you a jump-start on creating that file. It creates a row for every SKU-month combination and provides an estimate of cogs per-unit for that row, based on 0.25 * the ASP (selling price/units sold) of that row.

with precogs as

(SELECT 

	date_trunc('month', date_time)::date AS date
	, sku
	, sum(quantity) as units
	, sum(amt) as amount
	-- edit the percentage used for estimated COGS in the line below
	, 0.25*sum(amt)/sum(quantity) as COGS

from public.pl_ledger_table

	where TYPE in ('Order', 'Refund') 
	and line_item in ('product_sales_fba', 'product_sales_mfn', 'product_sales')
	
group by 1, 2
order by 2 desc)

select 

   date
	, sku
	, ROUND( COGS::numeric, 2 ) as COGS

from precogs 
where cogs > 0

group by 1, 2, 3

Advanced Topics & Queries

Units

Product ‘Units’ are captured in the {quantity} column of transactions data, and in the SPT Ledger Table. Because unit values will exist for many rows that are not direct sale or refund transactions, filters are needed when querying for units, as summing across all units will result in a large overcount. Examples:

Units Sold. To query units related to an original sales transaction, sum the quantity column filter to rows where {type} = ‘Order’ and line_item in (‘product_sales’, ‘product_sales_mfn, ‘product_sales_fba’)

Units Returned. As above, but where {type} = ‘Refund’

Net Units. To calculate net units, subtract returned units from sold units, as in the following query.

with units1 as

(select 

	date_trunc('month', date_time) as date
	, case when type = 'Order' then quantity else 0 end as quantity_ordered
 	, case when type = 'Refund' then quantity else 0 end as quantity_refunded
	
from pl_ledger_table	

where 
 	line_item in ('product_sales', 'product_sales_mfn', 'product_sales_fba')
 	and type in ('Order', 'Refund')
 
group by 1, 2, 3)
 
select

 	date
	, sum(quantity_ordered) as units_ordered
	, sum(quantity_refunded) as units_refunded
	, sum(quantity_ordered)-sum(quantity_refunded) as units_net
	
from units1
	
	group by 1

	order by 1 desc

Using Cost of Goods Sold (COGS) Data

The Seller Profit Toolkit Report Suite automatically imports your per-unit COGS data for use in profitability reporting (provided you have uploaded a COGS file to your Reason Automation portal). This same per-unit data is also brought into the SPT Ledger Table at the row level, making it available for further calculation without having to join to the COGS table. The per-unit value needs to be multiplied by {quantity} at the row level to calculate correct COGS for a given row. The formula is:

{cogs_per_unit} * {quantity} * X (where X is +1 or -1)

according to the following logic:

In the below example, the total {amt} for product sales and product refunds is calculated, along with the associated COGS for those sales and refund transactions.

select 

	date_trunc('month', date_time) as date
 	, sum(case when line_item = 'COGS' and Type = 'Order' then cogs_per_unit*quantity*-1 else 0 end) as COGS_orders
 	, sum(case when line_item = 'COGS' and Type = 'Refund' then cogs_per_unit*quantity else 0 end) as COGS_refunds
	, sum(case when line_item in ('product_sales', 'product_sales_mfn', 'product_sales_fba')  and Type = 'Order' then amt else 0 end) as sales_orders
 	, sum(case when line_item in ('product_sales', 'product_sales_mfn', 'product_sales_fba')  and Type = 'Refund' then amt else 0 end) as sales_refunds
 	
from pl_ledger_table
 
group by 1

order by 1 desc

Glossary

account_type - sourced from original transactions tables. Possible values are Invoiced Orders or Standard Orders. Denotes whether data is coming from 'transactions_invoiced' table (Amazon B2B sales) or 'transactions_standard_orders' table (all other Amazon sales).

'Amount' - numerical value ($) for a particular P&L line_item. Amazon P&Ls can have both positive and negative values (Credits and Debits) for Amount in any P&L section. The sum of Amount across all P&L lines gives "Profit", and so Amount is referred to Profit when the entire P&L is included. When looking at a subsection of a P&L, e.g. FBA fees in isolation, the term 'Amount' is used.

'Amount Per Net Unit' - $ amount for a given P&L item/category divided by net units for the period.

'Amount Per Net Unit FBA' - As above, but limited to FBA units only (used for calculation of per-unit FBA fees)

'Amount Per Net Unit No SKU' - correctly pulls net units for reporting even when no SKU is attached to line items

'apl_category' - P&L category as found in Amazon P&L reporting, such as income, taxes, expenses

'apl_line' - P&L line item as found in Amazon P&L reporting

'apl_line_i' - indexed version of apl_line

'cr_dr' - whether an item is a Credit or Debit within its P&L category (provided to match Amazon P&L reporting format)

CTT - Contribution to Total - the share or contribution of a given row to the total amount of the column.

CTC - Contribution to Change - a measure of a row's contribution to the total amount of change between two periods, shown in bps (basis points; 100 bps = 1 percentage point). All of the individual CTCs should add up to the total CTC, which should also equal the percentage change for the period, expressed in bps. For instance, if you had a +8.5% change in total profit between two time periods, that's 850 bps of change. In a simple portfolio of five products, you might see that the five products each drove change (had a CTC) as follows: + 1200 bps, + 300 bps, + 50 bps, -100 bps and -600 bps, bridging the total 850 bps of change observed at the product level.

'description' - full description of a transaction item, sourced from transaction tables

'desc_short' - a condensed/rolled-up version of 'description'

'LM' - appended to a measure, means for last (previous) month.

'LY' - appended to a measure, means for last year.

'MoM’ or ‘YoY' - appended to a measure, means Month over Month or Year over Year. The relative difference of measure in selected vs. previous period. E.g. $10K last year, $11K this year = +10% relative YoY difference.

'MoM Abs’ or ‘YoY Abs'- appended to a measure, means Absolute MoM or YoY difference. E.g. $10K last year, $11K this year = +$1,000 absolute YoY difference.

'neg' - Appended to measure, means Negated value, or (value * (-1)). Used for charts where expenses are shown in isolation, and flipping them to positive improves chart readability

'Net Units' - sourced from the 'quantity' column in transactions data, net units is units ordered minus units returned.

'Net FBA Units' - as above, but FBA units only (MFN units excluded)

TACOS - Total Cost of Advertising, calculated as ad spend/total product sales (product sales includes both ad-attributed and organic sales)

TPOAS - Total Profit on Ad Sales, calculated as total profit/ad spend (where total profit accounts for ad spend - see user manual for more information)

TROAS - Total Return on Ad Sales, calculated as total product sales/ad spend (product sales includes both ad-attributed and organic sales)

On this page

• Seller Profit Toolkit - User Manual
• Introduction
• SPT Report Suite
• SPT Report Suite Organization
• tab: Guide (Guide to Use)
• tab: Exec (Executive Summary)
• tab: Movers
• tab: Bridge, Bridge/SKU
• tab: SKU YoY, SKU MoM
• tab: No SKU
• tab: Settlement (P&L View by Settlement ID)
• tab: Fees and Expenses
• tab: Ads
• tab: Ads - Profit on Ad Spend
• How to upload your COGS file
• Product Catalog Upload
• SPT Ledger Table
• Simple Queries
• query: Basic P&L
• query: Detailed P&L
• query: Starter COGS File
• Advanced Topics & Queries
• Units
• Using Cost of Goods Sold (COGS) Data
• Glossary

Related products

Profit Toolkit →