Product Profit & Loss
What is Product Profit & Loss (P&L) Data?
Once you have integrated your Amazon Seller Central account with DataHawk, your financial performance data is collected daily via Seller Central reports and synthesize this data into an intuitive format. Profit and Loss reporting is provided at a unit economics scale - with metrics accompanying each SKU with financial events.
How can I access my Product P&L Data?
You can access your Profit & Loss data through any of the following:
- The fully-customizable Seller Finances & Supply Chain Report Template via Power BI
- In-depth Profit & Loss data found in your managed database.
Data
Dataset
Product P&L analytics are built around thefinance.finance_product_profit_loss
dataset. See its full list of fields with descriptions on the public schema doc under the finance
schema.
Key Profit and Loss Metrics
The following P&L metrics are found on finance.finance_product_profit_loss
and in the dynamic functions explained later in this document. The metrics facilitate a full financial analysis - as seen on the Seller Finances and Supply Chain report template. See them below along with their accompanying definitions and formulas:
- Earned Gross Sales: Earned sales in currency.
earned_gross_sales
- Earned Orders: Number of earned orders made.
earned_orders
- Earned Units Sold: Number of earned units sold.
earned_units_sold
Earned and Realized Sales
- Earned metrics are reported upon customer order. This data matches the Sales
- Realized metrics are reported upon collection of revenue. This data matches the P&L reporting from Amazon and is referenced in calculating other P&L metrics as described below.
Sales and Taxes
- Earned and Realized Sales include taxes on principal such as VAT except for Amazon marketplaces in North and South America.
- Gross Sales: Realized sales in currency.
gross_sales
- Gross Units Sold: Realized sales in units.
units_sold
- Average Selling Price: Average price of units sold.
gross_sales / units_sold
- Gross Sales Growth: Growth rate of gross sales.
- (Current period
gross_sales
- Previous periodgross_sales
) / Previous periodgross_sales
- (Current period
- Weight in Gross Sales: Share of total gross sales for the filtered selection.
gross sales
over the sum ofgross_sales
for the period
- Refunded Units: Refunds among gross units sold.
reimbursed_product_unit
- Return Rate: Rate of rufunded units to gross units sold.
reimbursed_product_unit / units_sold
- Net Units Sold: Gross units sold net of refunded units.
gross_units_sold
-reimbursed_product_unit
- Refunds: Refunds among gross sales.
reimbursed_product
- Net Sales: Gross sales net of refunds in currency.
gross_sales + reimbursed_product + reversal_reimbursed
- CoGS: Costs of goods sold sourced and processed from user-provided product expenses. The expenses are multipled by net units sold.
net_cogs
- Referral Fees: Marketing fees paid to referrers.
total_referral_fees
consisting of:referral_fee + refund_commission + refunded_referral_fees
- Warehousing Costs: Incurred costs relating to product storage.
total_warehousing_costs
consisting of:dist_disposal_complete + dist_inbound_transportation + dist_removal_complete + dist_fba_storage_fee + dist_fba_inventory_placement_service + warehouse_damage + warehouse_lost_manual + restocking_fee
- Shipping Costs: Incurred costs relating to transport from warehouse to customer.
total_shipping_costs
consisting of:fba_per_unit_fulfilment_fee+shipping+shipping_chargeback+reimbursed_shipping+refund_shipping_chargeback+ refund_shipping_promotion + shipping_promotion
- Other Amount: Other costs reported by Amazon.
dist_other_amount
- Gross Profit: Net sales net of CoGS, referrals, warehousing, and shipping.
net_sales + cogs + total_referral_fees + total_shipping_costs + total_warehousing_costs + dist_other_amount
- Gross Margin: Rate of gross profit to gross sales.
gross_profit / net_sales
- Advertising Costs: Total advertising costs.
total_advertising_costs
consisting of:sponsored_products_cost + dist_sponsored_brands_cost + dist_sponsored_brands_video_cost + dist_sponsored_display_cost
- Advertising Sales: Total advertising sales.
total_advertising_sales
consisting of:sponsored_products_sales + dist_sponsored_brands_sales + dist_sponsored_brands_video_sales + dist_sponsored_display_sales
- RoAS: Return on Ad Spend in currency.
total_advertising_sales / total_advertising_costs
- ACoS: Advertising Cost of Sales as a rate.
total_advertising_costs / total_advertising_sales
- TRoAS: Total Return on Ad Spend in currency.
gross_sales / total_advertising_costs
- TACoS: Total Advertising Cost of Sales as a rate
total_advertising_costs / gross_sales
- Contribution Profit I: Direct profitability on products sold.
gross_profit + total_advertising_costs
- Contribution Margin I: The rate of profitability of products in relation to their selling price.
contribution_profit_one / net_sales
- Other Marketing Costs: A total of remaining marketing related costs.
total_other_marketing_costs
consisting of:promotion + refund_promotion+gift_wrap + goodwill + gift_wrap_chargeback + subscription_fee
- Contribution Profit II: A broader measure of profitability net of other marketing costs.
contribution_profit_one + total_other_marketing_costs
- EBITDA: A measure of overall performance, equivalent to Contribution Profit II.
contribution_profit_two
- EBITDA Margin: The broader rate of profitability of products in relation to their selling price indicating sales efficiency.
EBITDA / net_sales
How it Works
Required Sources
The Product P&L requires the following sources in order to be populated with data:
History
The Product P&L will have data up to:
- For each Amazon Seller Central Account: up to two years prior of the date connected.
- For each Amazon Ads Account: up to four months prior of the date connected.
Cost Distribution from Account-level to SKU-level
Certain financial events are not directly linked to an individual SKU but are instead associated with the overall account. To ensure a comprehensive Product P&L report, these costs are distributed across all SKUs related to that account.
Distribution Methods:
- Weighted Distribution Based on Gross Sales:
- For SKUs with positive gross sales on a given day, we calculate the SKU's sales as a percentage of the total account gross sales.
- Example: If the total account gross sales are $10, and a SKU has sales of $1, it accounts for 10% of the gross sales.
- The calculated percentage is then used to allocate the proportionate account-level costs to that SKU.
- For SKUs with positive gross sales on a given day, we calculate the SKU's sales as a percentage of the total account gross sales.
- Equal Division Among SKUs:
- If there's no sales data for the day of the cost distribution, costs are evenly split among all SKUs that had any financial event that day.
- Example: If there are 10 SKUs with financial events on a given day, each SKU will bear 1/10th of the account-level costs.
- If there's no sales data for the day of the cost distribution, costs are evenly split among all SKUs that had any financial event that day.
Identification:
- Distributed account-level costs can be found in columns with a 'dist_' prefix. These columns might include both directly associated SKU costs and distributed costs.
Application to Advertising and Warehousing:
- The described distribution logic is also applicable to advertising costs and sales related to sponsored brands, brand videos, and displays.
- It is also applicable to several warehousing-related costs:
dist_disposal_complete
,dist_inbound_transportation
,dist_removal_complete
,dist_fba_storage_fee
,dist_fba_inventory_placement_service
.
Marketplace Association
Some financial events lack a linked marketplace.
Marketplace Inference:
- If a seller account operates in just one marketplace, it is assumed that unassociated events belong to that marketplace.
- If historical data indicates that an SKU has only been associated with one marketplace, that marketplace is inferred for unassociated financial events related to that SKU.
When No Marketplace Can Be Deduced:
- In instances where a marketplace cannot be inferred, the 'marketplace_key' column will display "No Marketplace Associated".
Future Solutions:
- Efforts are being made to develop methods for distributing these unassociated events to relevant marketplaces. The approach may resemble the distribution method used for account-level costs, ensuring that data visualizations offer a complete view.
ASIN labeling
Our Profit and Loss data originates from Amazon financial events, which typically provide SKUs but not ASINs. To display ASIN-level data, we map ASINs to SKUs using Amazon orders, advertising, and FBA reports. However, in certain cases, we may not locate an ASIN for an event, resulting in the display of an "Unknown" ASIN. We continually refine our data processing to reduce such occurrences and prioritize delivering accurate and transparent insights for your Amazon business.
Further Data: Dynamic Functions
The following functions found in the finance
schema are useful for producing contextual product profit and loss data given a dynamic set of parameters. They work well in dashboards where parameters are interacted with using filters.
product_profit_loss_supply_chain_overview
The finance.product_profit_loss_supply_chain_overview
function produces a P&L report combined with inventory and restock recommendations. Use it to get an understanding of profitability up to a unit scale and inform your restocking decisions.
Used in:
Output Fields
- Seller Name
- ASIN
- SKU
- Currency
- Gross Units Sold
- Average Selling Price
- Gross Sales
- Weight in Gross Sales
- Refunded Units
- Return Rate
- Net Units Sold
- Refunds
- Net Sales
- CoGS
- Referral Fees
- Warehousing Costs
- Shipping Costs
- Other Costs
- Gross Profit
- Gross Margin
- Advertising Costs
- RoAS
- ACoS
- TRoAS
- TACoS
- Contribution Profit I
- Contribution Margin I
- Other Marketing Costs
- Taxes on Sales
- Contribution Profit II
- EBITDA
- EBITDA Margin
- AFN Quantity
- AFN WoH
- OOS Forecast
- Lead Time
- Reco Replenishment Date
- Target WoH
- Reco Replenishment Qty
Input Parameters
- SelectBaseStartDate: The start date of the P&L coverage period in yyyy-MM-dd.
- SelectBaseEndDate: The end date of the P&L coverage period in yyyy-MM-dd.
- SelectMarketplaces: Marketplaces to include, semicolon-separated e.g. "Amazon-US;Amazon-CA".
- SelectCurrency: Code for currency to convert all monetary fields to e.g. "USD".
- SelectEntity: Choose between "Overall", "Account", "ASIN", or "SKU" for the level of analysis.
- SelectSellerName: Sellers to include, semicolon-separated e.g. "Seller1;Seller2".
- SelectASINs: ASINs to include, semicolon-separated e.g. "ASINs1;ASINs2".
- SelectSKUs: SKUs to include, semicolon-separated e.g. "SKU1;SKU2".
- SelectTags: Tagged products to include, semicolon-separated e.g. "Tag1;Tag2".
- SelectProjects: Project-assigned products to include, semicolon-separated e.g. "Project1;Project2"
- SelectInventoryFields: Inventory fields to include in a sum for total inventory, semicolon-separated e.g. "available;fc_transfer". Choose from:
available
fc_transfer
fc_processing
working
shipped
receiving
- SelectLeadTime: The number of days it takes to for a restock to complete.
- SelectTargetWoH: The target number of weeks for inventory to be available before running out-of-stock.
- SelectAnonymize: Display entities in an anonymized fashion e.g. "Seller 1", "ASIN 1", and "SKU 1".
Note: All input parameters are in varchar format.
product_profit_loss_history
The finance.product_profit_loss_history
function produces a time series P&L report. Use it to get an understanding of profitability up to a unit scale with a flexible dimension.
Used In
Output Fields
- Seller Name
- ASIN
- SKU
- Period
- Currency
- Gross Units Sold
- Average Selling Price
- Gross Sales
- Growth in Gross Sales
- Weight in Gross Sales
- Refunded Units
- Return Rate
- Net Units Sold
- Refunds
- Net Sales
- CoGS
- Referral Fees
- Warehousing Costs
- Shipping Costs
- Other Costs
- Gross Profit
- Gross Margin
- Advertising Costs
- RoAS
- ACoS
- TRoAS
- TACoS
- Contribution Profit I
- Contribution Margin I
- Other Marketing Costs
- Taxes on Sales
- Contribution Profit II
- EBITDA
- EBITDA Margin
Input Parameters
- SelectPeriodInterval: The period interval to use for the period-on-period comparison. Choose between "day", "week", "month", "quarter", and "year".
- SelectBaseStartDate: The start date of the P&L coverage period in yyyy-MM-dd.
- SelectBaseEndDate: The end date of the P&L coverage period in yyyy-MM-dd.
- SelectMarketplaces: Marketplaces to include, semicolon-separated e.g. "Amazon-US;Amazon-CA".
- SelectCurrency: Code for currency to convert all monetary fields to e.g. "USD".
- SelectEntity: Choose between "Overall", "Account", "ASIN", or "SKU" for the level of analysis.
- SelectSellerName: Sellers to include, semicolon-separated e.g. "Seller1;Seller2".
- SelectASINs: ASINs to include, semicolon-separated e.g. "ASINs1;ASINs2".
- SelectSKUs: SKUs to include, semicolon-separated e.g. "SKU1;SKU2".
- SelectTags: Tagged products to include, semicolon-separated e.g. "Tag1;Tag2".
- SelectProjects: Project-assigned products to include, semicolon-separated e.g. "Project1;Project2"
Note: All input parameters are in varchar format.
product_profit_loss_comparison
The finance.product_profit_loss_comparison
function produces a side-by-side P&L report for two periods with measured changes in each metric. Use it to get an understanding of changes in profitability between two specific periods.
Used In
Output Fields
- Gross Units Sold
- Average Selling Price
- Gross Sales
- Weight in Gross Sales
- Refunded Units
- Net Units Sold
- Return Rate
- Refunds
- Net Sales
- CoGS
- Referral Fees
- Warehousing Costs
- Shipping Costs
- Other Costs
- Gross Profit
- Gross Margin
- Advertising Costs
- RoAS
- ACoS
- TRoAS
- TACoS
- Contribution Profit I
- Contribution Margin I
- Other Marketing Costs
- Taxes on Sales
- Contribution Profit II
- Contribution Margin II
- EBITDA
- EBITDA Margin
Input Parameters
- SelectBaseStartDate: The start date of the first P&L coverage period in yyyy-MM-dd.
- SelectBaseEndDate: The end date of the first P&L coverage period in yyyy-MM-dd.
- SelectComparisonStartDate: The start date of the second P&L coverage period in yyyy-MM-dd.
- SelectComparisonEndDate: The end date of the second P&L coverage period in yyyy-MM-dd.
- SelectComparisonStrategy: Choose between "Actual Values" and "Adjust Base to Days in Comparison Period".
- "Actual Values" provides the actual observed values to compare.
- "Adjust Base to Days in Comparison Period" aims to provide a fairer comparison through normalization when the days in each period differ. A daily average of each metric is taken from the base values then multiplied with the number of days in the comparison period.
- SelectMarketplaces: Marketplaces to include, semicolon-separated e.g. "Amazon-US;Amazon-CA".
- SelectCurrency: Code for currency to convert all monetary fields to e.g. "USD".
- SelectEntity: Choose between "Overall", "Account", "ASIN", or "SKU" for the level of analysis.
- SelectSellerName: Sellers to include, semicolon-separated e.g. "Seller1;Seller2".
- SelectASINs: ASINs to include, semicolon-separated e.g. "ASINs1;ASINs2".
- SelectSKUs: SKUs to include, semicolon-separated e.g. "SKU1;SKU2".
- SelectTags: Tagged products to include, semicolon-separated e.g. "Tag1;Tag2".
- SelectProjects: Project-assigned products to include, semicolon-separated e.g. "Project1;Project2"
Note: All input parameters are in varchar format.
Updated 8 months ago