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.

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:

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:

For the examples in this section, assume the following:

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.

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 data feed for manufacturer

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

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

Example queries

As described in Collecting and analyzing data with 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;