DocumentationWhat's newStatusAppGo to datahawk.co

Product Profit & Loss

Suggest Edits

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:

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 period gross_sales) / Previous period gross_sales
  • Weight in Gross Sales: Share of total gross sales for the filtered selection.
    • gross sales over the sum of gross_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:

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:

  1. 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.
  2. 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.

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 3 months ago