Billing event data feed

This data feed provides information about billing events, including invoicing and disbursements.

For example, you can use this data feed to learn when and what a buyer is invoiced. You can also use the example SQL queries to analyze the data from this data feed.

This data feed contains information associated with billing events for which you are the seller of record. For agreements made through channel partners, this data feed contains information about billing events between the manufacturer and seller of record.

The billing event data feed is refreshed every 24 hours, so new data is available daily.

Billing event data is immutable.

The following table explains the names and descriptions of the data feed's columns.

Column name	Description
billing_event_id	An identifier for a billing event. This ID is unique in the seller's environment.
from_account_id	The account that initiated the billing event. If transaction_type is SELLER_REV_SHARE, it is the buyer's payer account. This is a foreign key to the account data feed. Can be used to join to the Account data feed on the account_id field.
to_account_id	The account that receives the transaction amount for the product. This is a foreign key to the account data feed. Can be used to join to the Account data feed on the account_id field.
end_user_account_id	The account that uses the product. This account may be different from the buyer and payer accounts. Can be used to join to the Account data feed on the account_id field.
product_id	The identifier of the product. This is a foreign key to the product data feed. Can be used to join to the Product data feed on the product_id field.
action	The type of action for this event. Possible values are as follows: INVOICED – The buyer was invoiced for the amount. FORGIVEN – The buyer was invoiced for the amount, and AWS reverted the charge. DISBURSED – The seller was paid this amount. This can include a month of invoices, or be an on-demand disbursement.
transaction_type	The type of transaction. For examples, see Taxing scenarios. Possible values are as follows: SELLER_REV_SHARE – A positive amount; this is the price that the seller set in the agreement with the buyer. SELLER_TAX_SHARE – A positive amount; this is the amount added to SELLER_REV_SHARE to cover taxes that the seller owes. AWS_REV_SHARE – A negative amount; this is the listing fee. AWS_TAX_SHARE – A positive amount; this is the amount of taxes AWS collected in addition to SELLER_REV_SHARE. This amount doesn't affect the seller's balance. This amount is not disbursed and is provided for the seller's awareness of taxes invoiced to the buyer and remitted to authorities on the seller's behalf. transaction_type_REFUND – The amount of refund requested by the buyer. transaction_type_CREDIT – The amount AWS credits the buyer. BALANCE_ADJUSTMENT – An adjustment made by AWS to resolve invoicing issues. DISBURSEMENT – If the value of action is DISBURSEDand the value of balance_impacting is 1, this is the amount paid to the seller. If the value for action is INVOICED, this record negates the parent_billing_event_id record either in full or in part. In this case, the related disbursement disbursement_billing_event_id is shown and the value of balance_impacting is 0. DISBURSEMENT_FAILURE – Negates the transaction.
parent_billing_event_id	When the value of broker_id is AWS_INC, the value of action is DISBURSED or FORGIVEN, and the value of transaction_type is DISBURSEMENT, the parent_billing_event_id refers to the original billing_event_id that initiated this billing event. If action has another value, this field is null. When the value of broker_id is AWS_EUROPE, the parent_billing_event_id refers to the original billing_event_id that initiated this billing event for the following scenarios: The value of action is FORGIVEN or INVOICED and the value of transaction_type is AWS_REV_SHARE, AWS_REV_SHARE_REFUND, or SELLER_REV_SHARE_REFUND. The value of action is DISBURSED and the value of transaction_type is ANY (excluding DISBURSEMENT_FAILURE). The value of transaction_type is AWS_TAX_SHARE, AWS_TAX_SHARE_REFUND, SELLER_TAX_SHARE, or SELLER_TAX_SHARE_REFUND. The value of action is DISBURSED and the value of transaction_type is DISBURSEMENT_FAILURE. When the value of broker_id is AWS_EUROPE, the parent_billing_event_id refers to the original billing_event_id of the previous successful disbursement billing event for the following scenario: The value of action is DISBURSED and the value of transaction_type is DISBURSEMENT_FAILURE. When the value of broker_id is AWS_EUROPE, this field is null for all remaining scenarios.
disbursement_billing_event_id	The related disbursement when the value of action is DISBURSED and one of the following is true: transaction_type like ('SELLER%') transaction_type like ('AWS%') In all other scenarios, this value is null.
amount	The billing event amount.
currency	The ISO 639 currency code.
balance_impacting	Whether the amount is taken into account in calculating seller disbursements. A value of 0 indicates the amount is shown for informational purposes and has no effect on the balance. A value of 1 indicates that this amount takes into account in determining seller disbursements.
invoice_date	The date the invoice was created.
payment_due_date	When the value of action is INVOICED, the due date for the invoice.
usage_period_start_date	The start date for the period in the record.
usage_period_end_date	The end date for the period in the record.
invoice_id	The AWS invoice ID.
billing_address_id	The payer's billing address reference in the address data feed. Can be used to join to the Address data feed on the address_id field.
transaction_reference_id	An identifier that allows you to cross-reference data from the following reports: Disbursement report Monthly billed revenue report Sales compensation report
bank_trace_id	For disbursement transactions (transaction_type =is DISBURSEMENT and action is DISBURSED), the trace ID assigned by the bank. The trace ID can be used to correlate with bank-provided reports from the seller bank.
broker_id	An identifier of the business entity which facilitated the transaction. Possible values are as follows: AWS_INC – The identifier for AWS, Inc. (based in the United States). AWS_EUROPE – The identifier for Amazon Web Services EMEA SARL (based in Luxembourg). NULL – Previous transactions without an explicit broker_id were facilitated by AWS_INC.
buyer_transaction_reference_id	An identifier that groups all related records in the billing feed together using GROUP BY or the window functions construct in SQL. These related records can contain the buyer invoice, seller invoice, and value added taxes (VATs) on the listing fee.

Taxing scenarios

The taxation model that is in place for the country and state of the buyer and seller dictates how taxes are collected and remitted. Following are the possible scenarios:

• Taxes are collected and remitted by AWS. In these cases, the transaction_type is AWS_TAX_SHARE.

• Taxes are collected by AWS, disbursed to the seller, and remitted by the seller to the tax authorities. In these cases, the transaction_type is SELLER_TAX_SHARE.

• Taxes are not collected by AWS. The seller must calculate the taxes and remit them to the tax authorities. In these cases, AWS Marketplace doesn't perform tax calculations or receive tax information. The seller pays the taxes from the revenue share.

Examples of billing event data feed

This section shows examples of the billing event data period at the time of invoicing and one month later. Note the following for all tables in this section:

• In data feeds, billing_event_id values are 40-character alphanumeric strings. They're shown here as two-character strings for readability.

• In the data feed, this information is presented in a single table. For readability, the data is shown in multiple tables here, and all columns aren't shown.

For the examples in this section, assume the following:

• Arnav is the buyer.

  • His account ID is 737399998888.

  • He's located in France, which is subject to marketplace facilitator laws. For more information, see Amazon Web Service Tax Help.

  • He purchased prod-o4grxfafcxxxx and was invoiced $120.60 for his monthly usage of that product.

  • He paid the invoice within the month.

• Jane is the manufacturer.

  • Her account ID is 111122223333.

• Paulo is the seller of record.

  • His account ID is 777788889999.

  • He lives in Kansas, which is not subject to market facilitator laws.

Billing event data feed for seller of record

As the seller of record, Paulo invoices the buyer, Arnav.

The following tables show the relevant information in Paulo's data feed when he invoices Arnav.

billing_event_id	from_account_id	to_account_id	end_user_account_id	product_id	action	transaction_type
I0	737399998888	777788889999	737399998888	prod-o4grxfafcxxxx	INVOICED	SELLER_REV_SHARE
I1	737399998888	AWS	737399998888	prod-o4grxfafcxxxx	INVOICED	AWS_TAX_SHARE
I2	777788889999	111122223333	737399998888	prod-o4grxfafcxxxx	INVOICED	SELLER_REV_SHARE
I3	777788889999	AWS	737399998888	prod-o4grxfafcxxxx	INVOICED	AWS_REV_SHARE

parent_billing_event_id	disbursement_billing_event_id	amount	currency	invoice_date	invoice_id
		100	USD	2018-12-31T00:00:00Z	781216640
		20.6	USD	2018-12-31T00:00:00Z	781216640
		-80	USD	2018-12-31T00:04:07Z	788576665
		-0.2	USD	2018-12-31T00:04:07Z	788576665

The following tables show the relevant information in Paulo's data feed at the end of the month, after Arnav pays the invoice.

billing_event_id	from_account_id	to_account_id	end_user_account_id	product_id	action	transaction_type
I10	737399998888	777788889999	737399998888		DISBURSED	SELLER_REV_SHARE
I12	777788889999	111122223333	737399998888		DISBURSED	SELLER_REV_SHARE
I13	777788889999	AWS	737399998888	prod-o4grxfafcxxxx	DISBURSED	AWS_REV_SHARE
I14	AWS	777788889999			DISBURSED	DISBURSEMENT

parent_billing_event_id	disbursement_billing_event_id	amount	currency	invoice_date	invoice_id
I0	I14	-100	USD	2018-12-31T00:00:00Z	781216640
I2	I14	80	USD	2018-12-31T00:04:07Z	788576665
I3	I14	0.2	USD	2018-12-31T00:04:07Z	788576665
		19.8	USD

Billing event data feed for manufacturer

The following tables show the relevant information in the Jane's data feed when Paulo invoices Arnav.

billing_event_id	from_account_id	to_account_id	end_user_account_id	product_id	action	transaction_type
I5	777788889999	111122223333		prod-o4grxfafcxxxx	INVOICED	SELLER_REV_SHARE
I6	777788889999	111122223333		prod-o4grxfafcxxxx	INVOICED	SELLER_TAX_SHARE
I7	111122223333	AWS		prod-o4grxfafcxxxx	INVOICED	AWS_REV_SHARE

parent_billing_event_id	disbursement_billing_event_id	amount	currency	invoice_date	invoice_id
		73.5		2018-12-31T00:04:07Z	788576665
		6.5		2018-12-31T00:04:07Z	788576665
		-7.35		2018-12-31T00:04:07Z	788576665

The following tables show the relevant information in Jane's data feed at the end of the month, after the invoice is paid.

billing_event_id	from_account_id	to_account_id	end_user_account_id	product_id	action	transaction_type
I30	777788889999	111122223333		prod-o4grxfafcxxxx	DISBURSED	SELLER_REV_SHARE
I31	777788889999	111122223333		prod-o4grxfafcxxxx	DISBURSED	SELLER_TAX_SHARE
I32	111122223333	AWS		prod-o4grxfafcxxxx	DISBURSED	AWS_REV_SHARE
I33	AWS	111122223333			DISBURSED	DISBURSEMENT

parent_billing_event_id	disbursement_billing_event_id	amount	currency	invoice_date	invoice_id
I5	I33	-73.5	USD
I6	I33	-6.5	USD
I7	I33	7.35	USD
		72.65	USD

Example queries

As described in Using data feeds, you can use Athena to run queries on the data that's collected and stored as data feeds in your managed Amazon S3 bucket. This section provides some examples of common ways you might do this. All examples assume that a single currency is used.

Example 1: Amount invoiced, including taxes

To find out how much buyers were invoiced, including taxes, you can run a query as shown in the following example.

SELECT sum(amount) FROM billing_event 
WHERE 
  action = 'INVOICED'
  AND
  (
    (transaction_type in ('SELLER_REV_SHARE', 'SELLER_TAX_SHARE')
      -- to discard SELLER_REV_SHARE from Manufacturer to Channel Partner, aka cost of goods
      AND to_account_id='seller-account-id'
    )
  OR transaction_type= 'AWS_TAX_SHARE'
  );

Example 2: Amount invoiced to buyers on seller's behalf

To find out how much buyers were invoiced on a seller's behalf, you can run a query as shown in the following example.

SELECT sum(amount) FROM billing_event 
WHERE
  action = 'INVOICED'
  AND transaction_type in ('SELLER_REV_SHARE', 'SELLER_TAX_SHARE')
  AND to_account_id='seller-account-id'
;

Example 3: Amount AWS can collect on seller's behalf

To find out how much AWS can collect on a seller's behalf, minus any refunds, credits, and forgiven accounts, you can run a query as shown in the following example.

SELECT sum(amount) FROM billing_event 
WHERE
  -- what is invoiced on behalf of SELLER, incl. refunds/ credits and cost of goods
  transaction_type like 'SELLER_%' 
  -- FORGIVEN action records will "negate" related INVOICED
  and action in ('INVOICED','FORGIVEN') 
;

Example 4: Amount seller can collect

To find out how much sellers can collect, you can run a query as shown in the following example. This example removes listing fees and taxes that AWS collects, and adds any exceptional balance adjustments.

SELECT sum(amount) FROM billing_event
WHERE
  (transaction_type like 'SELLER_%' -- what is invoiced on behalf of SELLER
  or transaction_type like 'AWS_REV_%' -- what is owed to AWS
  or transaction_type = 'BALANCE_ADJUSTMENT' -- exceptionnal case
  )
  and action in ('INVOICED','FORGIVEN')
;

You can also use the following query to collect the same information, as shown in the following example.

SELECT sum(amount) FROM billing_event
WHERE
  balance_impacting = 1
  and action in ('INVOICED','FORGIVEN')
;

The following example shows the same information, but is restricted to 2018 transactions and assumes all buyers paid their invoices.

SELECT sum(amount) FROM billing_event
WHERE
  invoice_date between '2018-01-01' and '2018-12-31'
  and balance_impacting = 1
  and action in ('INVOICED','FORGIVEN')
;

Example 5: Amount of disbursements

To find out the amount that's been disbursed, you can run a query as shown in the following example.

select sum(amount) FROM billing_event
WHERE
  action ='DISBURSED'
  and transaction_type like 'DISBURSEMENT%'
;

Example 6: Amount pending disbursement

To find out the amount that's pending disbursement, you can run a query as shown in the following example. This query removes amounts that have already been disbursed.

SELECT sum(amount) FROM billing_event targeted 
WHERE
   (transaction_type like 'SELLER_%'  -- what is invoiced on behalf of SELLER
    or transaction_type like 'AWS_REV_%'  -- what is owed to AWS
    or transaction_type = 'BALANCE_ADJUSTMENT' -- exceptionnal case
   ) 
  -- DISBURSEMENT action records will "negate" 'INVOICED'
  -- but do not take into account failed disbursements
   AND 
    (not exists
      (select 1 
        from billing_event disbursement
          join billing_event failed_disbursement
           on disbursement.billing_event_id=failed_disbursement.parent_billing_event_id
        where
         disbursement.transaction_type='DISBURSEMENT'
         and failed_disbursement.transaction_type='DISBURSEMENT_FAILURE'
         and targeted.disbursement_billing_event_id=disbursement.billing_event_id
      )
    ) 
;

Another way to get the same information is to run a query to get the seller's balance, as shown in the following example.

SELECT sum(amount) FROM billing_event
WHERE
 balance_impacting = 1
;

The following query extends our example. It restricts the results to 2018 transactions and returns additional details about the transactions.

select sum(residual_amount_per_transaction)
from
 (SELECT
    max(billed_invoices.amount) invoiced_amount,
    sum(nvl(disbursed_invoices.amount,0)) disbursed_amount,
    -- Exercise left to the reader:
    -- use transaction_type to distinguish listing fee vs seller-owed money
    -- still pending collection
    max(transaction_type) transaction_type,
    max(billed_invoices.amount) 
      + sum(nvl(disbursed_invoices.amount,0)) residual_amount_per_transaction
  FROM billing_event billed_invoices
    -- find related disbursements
    left join billing_event disbursed_invoices
      on disbursed_invoices.action='DISBURSED'
      and disbursed_invoices.parent_billing_event_id=billed_invoices.billing_event_id
  WHERE
    billed_invoices.invoice_date between '2018-01-01' and '2018-12-31'
    and billed_invoices.transaction_type like 'SELLER_%' -- invoiced on behalf of SELLER
    and billed_invoices.action in ('INVOICED','FORGIVEN')
    -- do not take into account failed disbursements
    AND not exists
      (select 1 from billing_event failed_disbursement
       where disbursed_invoices.disbursement_billing_event_id = failed_disbursement.parent_billing_event_id
      )
   GROUP BY billed_invoices.billing_event_id
);

Example 7: Balance of set of invoices

To learn the sum of a set of invoices, you can run a query as shown in the following example.

SELECT invoice_id, sum(amount) FROM billing_event targeted
WHERE
  -- invoice_id is only not null for invoiced records AND disbursed records 
  -- linking them to related disbursement -> no need to filter more precisely
  invoice_id in ('XXX','YYY') 
  -- filter out failed disbursements 
  AND not exists
      (select 1 
        from billing_event disbursement
          join billing_event failed_disbursement
           on disbursement.billing_event_id=failed_disbursement.parent_billing_event_id
        where
         disbursement.transaction_type='DISBURSEMENT'
         and failed_disbursement.transaction_type='DISBURSEMENT_FAILURE'
         and targeted.disbursement_billing_event_id=disbursement.billing_event_id
      ) 
  group by invoice_id;