DataHawk provides a reporting of financial activity on Amazon Seller accounts. This article details the data sources used to provide holistic reports and the data models that deliver them.

🚧

Experimental

This feature is currently experimental. Behaviors, details, and specifications are subject to change as we craft a valuable and well-rounded product.

The profit (& loss) data is built from several data sources. It first comes up as an events ledger

Data Sources

Financial Events

The majority of records in the Seller Profit Ledger are financial events sourced from the Amazon Finance API. This includes all transactions made on an Amazon Seller Account. Some events like account-level advertising costs and storage fees, are replaced by more detailed events sourced from enrichment reports.

Financial events are complete with a 2 year history up to T-2d from today.

Enrichment Reports

There are particular financial events that lack richness in accompanying information. These enrichment reports are used to replace these events, providing additional relevant information at no loss.

Advertising Reports

Advertising cost events are replaced with events sourced from Advertising Reports to provide sponsored type and ASIN annotation on otherwise account-level events.

Advertising events are complete with a 2 year history up to T-2d from today.

FBA Removal Order and Detail Report

Removal and disposal fee events are replaced with events sourced from this report to provide ASIN and SKU annotation on otherwise account-level events.

FBA reports are produced by Amazon once a month. Events can take up to one month to be present in the dataset.

FBA Storage Fee Charges Report

Storage Fee events are replaced with events sourced from this report to provide ASIN annotation on an otherwise account-level event.

FBA reports are produced by Amazon once a month. Events can take up to one month to be present in the dataset.

FBA Longterm Storage Fee Charges Report

Longterm Storage Fee events are replaced with events sourced from this report to provide ASIN and SKU annotation on an otherwise account-level event.

FBA reports are produced by Amazon once a month. Events can take up to one month to be present in the dataset.

Cost of Goods

User-submitted product expenses are applied to items sold and returned to get Cost of Goods events which are appended to the profit ledger.

Data Dictionaries

All datasets are described on the DataHawk Datasets Schema Docs. View the page for an inventory and descriptions of finance profit datasets and fields.

https://docs.google.com/spreadsheets/d/1gYGqKQP9Z71nz1lOb4y0wJ15i27NvsUzdliIJeIx0HY/edit#gid=175439716: Use this as a reference while reading the rest of this doc.

Datasets

Profit

finance.finance_profit

A daily summary of the profit ledger. Events are aggregated to improve performance when connecting to dashboards.

Events are aggregated according to the following grain:

• Posted Date
• Seller Account
• Marketplace
• ASIN
• SKU
• Event Signature

Profit Ledger

finance.finance_profit_ledger

A ledger of profit events compiled from a comprehensive selection of seller finance data sources. Events are annotated with timestamp, category, subcategory, event description, and other additional fields.

Categories: High-level grouping of events.

• Sales: Sales revenue including charges to customers (Tax/VAT, Giftwrap, Shipping).
• Refunds: Returns, Guarantees, Chargebacks, and related fees.
• Cost of Goods: Cost of goods sold and returned.
• Amazon Fees: Fulfillment fees, Service Fees, Warehousing, and more.
• Marketing: Advertising, Coupons, and Promotion.
• Adjustments: Made to sales charges and fees.
• Tax Expenses: Taxes withheld and paid.
• Tax Adjustments: Made to tax charges and expenses.

Subcategories: Low-level grouping of events.

Description:  Individual event descriptions.

Profit Metrics

finance.finance_profit_metrics

A collection of profit metrics built from the profit ledger that provide a business-level summary of profit.

Metrics are aggregated according to the following grain:

• Posted Date
• Seller Account
• Marketplace
• ASIN
• SKU

Metrics

• Gross Revenue: Sales including Tax Charges (VAT)
• Net Revenue: Gross Revenue - Refunds (Returns, Guarantees, and Chargebacks)
• Gross Profit: Net Revenue - Cost of Goods
• Operating Profit: Gross Profit - Marketing (Advertising, Coupons, and Promotion)
• Adjusted Operating Profit: Gross Profit - Adjustments
• Net Profit: Adjusted Operating Profit - Net Taxes (Tax Charges, Tax Expenses, Tax Adjustments, and Estimated Tax Liability)

Use Case Queries

Exec Summaries

• How do I summarize my Amazon Profit this year?
  SQL

  select 
  	category,
  	subcategory,
  	sum(amount_usd) as amount,
  	category_path_order
  from finance.finance_profit_ledger
  where year(posted_date) = year(current_date())
  group by all 
  order by category_path_order
  

• How do I breakdown my profit down to the event level?
  SQL

  select 
  	category,
  	subcategory,
  	description,
  	sum(amount_usd) as amount,
  	category_path_order
  from finance.finance_profit_ledger
  where year(posted_date) = year(current_date())
  group by all 
  order by category_path_order, sum(amount) desc
  

• How do I summarize my monthly Amazon Profit this year?
  SQL

  select
  	date_trunc(month, posted_date) as posted_month 
  	category,
  	subcategory,
  	sum(amount_usd) as amount,
  	category_path_order
  from finance.finance_profit_ledger
  where year(posted_date) = year(current_date())
  group by all 
  order by posted_month, category_path_order
  

• What is my Amazon Profit in Euros?
  SQL

  select 
  	l.category,
  	l.subcategory,
  	sum(l.amount_usd * r.rate) as amount,
  	l.category_path_order
  from finance.finance_profit_ledger l 
  left join referential.referential_currency_rate r 
  on 
  	l.posted_date = r.posted_date and 
  	l.currency = r.currency
  where 
  	year(l.posted_date) = year(current_date()) and 
  	r.base = 'USD'
  group by all 
  order by l.category_path_order
  

Exploration

• How do I explore SKU profitability?
  SQL

  select 
  	asin,
  	sku,
  	sum(amount_usd) as amount
  from finance.finance_profit_ledger
  where year(posted_date) = year(current_date())
  group by all 
  order by sum(amount) desc 
  

• How do I explore brand profitability?
  SQL

  select 
  	brand,
  	sum(amount_usd) as amount
  from finance.finance_profit_ledger
  where year(posted_date) = year(current_date())
  group by all 
  order by sum(amount) desc