# Seller delivery data feeds in AWS Marketplace Seller delivery data feeds AWS Marketplace provides data feeds as a mechanism to send structured, up-to-date product and customer information from AWS Marketplace systems to seller Amazon S3 buckets for ETL (extract, transform, and load) between seller-owned business intelligence tools. Data feeds collect and deliver comma-separated value (CSV) files to an encrypted Amazon S3 bucket that you provide. Data feeds are generated within a day, and contain 24 hours of data from the previous day. The following sections provide an overview of data feeds and explain how to access and use them. Subsequent sections describe each data feed. The transactional data is delivered and appended in a bi-temporal structure so sellers can store and query data along two timelines with timestamps for both + valid time: when a fact occurred in the real world (“what you knew”) + system time: when that fact was recorded to the database (“when you knew it”). Data feeds are delivered daily at midnight UTC following an update from the prior day containing 24 hours of data from the previous day. An update can be defined by a customer subscribing, a customer being invoiced, or AWS disbursing payment. **Topics** + [ # Storage and structure of AWS Marketplace data feeds ](data-feed-details.md) + [ # Accessing data feeds ](data-feed-accessing.md) + [ # Collecting and analyzing data with data feeds ](data-feed-using.md) + [ # Data feed tables overview ](data-feed-joining.md) + [ # Data feed query examples ](data-feed-full-examples.md) + [ # Data feeds ](data-feeds.md) # Storage and structure of AWS Marketplace data feeds Storage and structure of data feeds AWS Marketplace provides data feeds as a mechanism to send structured, up-to-date product and customer information from AWS Marketplace systems to seller Amazon S3 buckets for ETL (extract, transform, and load) between seller-owned business intelligence tools. This topic provides more information about the structure and storage of data feeds. Data feeds collect and deliver comma-separated value (CSV) files to an encrypted Amazon S3 bucket that you provide. The CSV files have the following characteristics: + They follow the [4180 standards](https://tools.ietf.org/html/rfc4180). + Character encoding is UTF-8 without BOM. + Commas are used as separators between values. + Fields are escaped by double quotation marks. + `\n` is the line feed character. + Dates are reported in the UTC time zone, are in ISO 8601 date and time format, and are accurate within 1 second. + All `*_period_start_date` and `*_period_end_date` values are inclusive, which means that `23:59:59` is the last possible timestamp for any day. + All monetary fields are preceded with a currency field. + Monetary fields use a period (`.`) character as a decimal separator, and don't use a comma (,) as a thousands separator. Data feeds are generated and stored as follows: + Data feeds are generated within a day, and contain 24 hours of data from the previous day. + In the Amazon S3 bucket, data feeds are organized by month using the following format: `bucket-name/data-feed-name_version/year=YYYY/month=MM/data.csv` + As each daily data feed is generated, it is appended to the existing CSV file for that month. When a new month starts, a new CSV file is generated for each data feed. + Information in data feeds is backfilled from 2010/01/01 to 2020/04/30 (inclusive) and is available in the [CSV file](#data-feed-details) in the `year=2010/month=01` subfolder. You may notice cases where the current month's file for a given data feed contains only column headers, and no data. This means that there were no new entries for that month for the feed. This can happen with data feeds that are updated less frequently, like the product feed. In these cases, data is available in the backfilled folder. + In Amazon S3, you can create an [Amazon S3 lifecycle policy](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-lifecycle.html) to manage how long to keep files in the bucket. + You can configure Amazon SNS to notify you when data is delivered to your encrypted Amazon S3 bucket. For information on how to configure notifications, see [Getting started with Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html) in the *Amazon Simple Notification Service Developer Guide*. ## Historization of the data Each data feed includes columns that document the history of the data. Except for `valid_to`, these columns are common to all data feeds. They're included as a common history schema and are useful in querying the data. | Column name | Description | | --- | --- | | valid\$1from | The first date that the value for the primary key is valid for in relation to values for other fields. | | valid\$1to | This column is only shown on the [Address](data-feed-address.md) data feed and is always blank. | | insert\$1date | The date a record was inserted into the data feed. | | update\$1date | The date the record was last updated. | | delete\$1date | This column is always blank. | The following shows an example of these columns. | valid\$1from | valid\$1to | insert\$1date | update\$1date | delete\$1date | | --- | --- | --- | --- | --- | | 2018-12-12T02:00:00Z | | 2018-12-12T02:00:00Z | 2018-12-12T02:00:00Z | | | 2019-03-29T03:00:00Z | | 2019-03-29T03:00:00Z | 2019-03-29T03:00:00Z | | | 2019-03-29T03:00:00Z | | 2019-03-29T03:00:00Z | 2019-04-28T03:00:00Z | | The `valid_from` and `update_date` field together form a *bi-temporal data model*. The `valid_from` field, as it is named, tells you when the item is valid from. If the item was edited, it can have multiple records in the feed, each with a different `update_date`, but the same `valid_from` date. For example, to find the current value for an item, you would find the record with the most recent `update_date`, from the list of records with the most recent `valid_from` date. In the example above, the record was originally created 2018-12-12. It was then changed on 2019-03-29 (for example, if the address in the record changed). Later, on 2019-04-28, the address change was corrected (so the `valid_from` didn't change, but the `update_date` did). Correcting the address (a rare event) retroactively changes the record from the original `valid_from` date, so that field didn't change. A query to find the most recent `valid_from` would return two records, the one with the latest `update_date` gives you the actual current record. # Accessing data feeds With AWS Marketplace, you can use data feeds as a mechanism to send structured, up-to-date product and customer information from AWS Marketplace systems an Amazon S3 buckets for ETL (extract, transform, and load) between seller-owned business intelligence tools. You need to configure your environment to receive data feeds to an encrypted Amazon S3 bucket. This topic shows you how to access and unsubscribe from data feeds. **Topics** + [ ## Access a data feed ](#data-feed-accessing-procedure) + [ ## Data feed policies ](#data-feed-policies) + [ ## Unsubscribe from data feeds ](#data-feed-unsubscribing) ## Access a data feed 1. Allocate a business intelligence or data engineer with SQL and ETL (extract, transform, load) experience. This person also needs experience setting up APIs. 1. Set up an Amazon Simple Storage Service bucket and a subscription to the data feeds. Use the AWS seller account ID associated with your Marketplace product listings. To do so, you can [watch this YouTube video](https://www.youtube.com/watch?v=heCsZdOT-hw) or follow the steps below. The video and the steps explain how to use a [CloudFormation template](https://s3.amazonaws.com/aws-marketplace-reports-resources/DataFeedsResources.yaml) that helps simplify configuration. 1. Open a web browser and sign into the [AWS Marketplace Management Portal](https://aws.amazon.com/marketplace/management/), then go to [Set up customer data storage](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration). 1. Choose **Create resources with CloudFormation template** to open the template in the CloudFormation console in another window. 1. In the template, specify the following and then choose **Next**: + Stack name – The collection of resources you're creating to enable access to data feeds. + Amazon S3 bucket name – The bucket for storing data feeds. + (Optional) Amazon SNS topic name – The topic for receiving notifications when Amazon Simple Storage Service bucket. 1. On the **Review** page, confirm your entries and choose **Create stack**. This will open a new page with the CloudFormation status and details. 1. From the **Resources** tab, copy Amazon Resource Names (ARNs) for the following resources from the CloudFormation page into the fields on the AWS Marketplace [Set up customer data storage](https://aws.amazon.com/marketplace/management/reports/data-feed-configuration) page: + Amazon S3 bucket for storing data feeds + AWS KMS key for encrypting the Amazon S3 bucket + (Optional) Amazon SNS topic for receiving notifications when AWS delivers new data to the Amazon S3 bucket 1. On the **Set up customer data storage** page, choose **Submit**. 1. (Optional) Edit the policies created by the CloudFormation template. See [Data feed policies](#data-feed-policies) for more details. You are now subscribed to data feeds. The next time data feeds are generated, you can access the data. 1. Use an ETL (extract, transform, load) operation to connect the data feeds to your data warehouse or relational database. **Note** Data tools have different capabilities. You must involve a business intelligence engineer or data engineer to set up the integration to match your tool’s capabilities. 1. To run or create SQL queries, configure the data feeds to enforce primary and foreign keys in your data tool. Each data feed represents a unique table, and you must set up all data feeds in data schema with the entity relationships. For more information about the tables and entity relationships, see [Data feed tables overview](data-feed-joining.md) in this guide. 1. Setup Amazon Simple Notification Service to automatically refresh your data warehouse or relational database. You can configure Amazon SNS notifications to send alerts when data from each unique feed is delivered to an Amazon S3 bucket. These notifications can be leveraged to automatically refresh seller data warehouse when new data is received via data feeds, if seller data tool supports this capability. See [Getting started with Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/sns-getting-started.html) in the *Amazon Simple Notification Service Developer Guide*. Example notification: ``` { "mainExecutionId": "1bc08b11-ab4b-47e1-866a-9c8f38423a98", "executionId": "52e862a9-42d2-41e0-8010-810af84d39b1", "subscriptionId": "27ae3961-b13a-44bc-a1a7-365b2dc181fd", "processedFiles": [], "executionStatus": "SKIPPED", "errors": [], "feedType": "[data feed name]" } ``` Notifications can have the following `executionStatus` states: + `SKIPPED` – The seller has no new data for the day. + `COMPLETED` – We delivered the feed with new data. + `FAILED` – The feed delivery has an issue. 1. Validate the setup by running SQL queries. You can use the [sample queries in this guide](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-full-examples.html), or the queries on GitHub, at [ https://github.com/aws-samples/aws-marketplace-api-samples/tree/main/seller-data-feeds/queries](https://github.com/aws-samples/aws-marketplace-api-samples/tree/main/seller-data-feeds/queries). **Note** The sample queries in this guide were written for AWS Athena. You may need to modify the queries for use with your tools. 1. Determine where business users want to consume data. For example, you can: + Export .csv data from your data warehouse or SQL database. + Connect your data to a visualization tool such as PowerBI or Tableau. + Map data to your CRM, ERP, or financial tools, such as Salesforce, Infor, or Netsuite. For more information about CloudFormation templates, see [Working with CloudFormation templates](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/template-guide.html) in the *AWS CloudFormation User Guide*. ## Data feed policies When your Amazon S3 bucket is created by the CloudFormation template, it will create policies for access attached to that bucket, the AWS KMS key, and the Amazon SNS topic. The policies allow the AWS Marketplace reports service to write to your bucket and SNS topic with the data feed information. Each policy will have a section like the following (this example is from the Amazon S3 bucket). ``` { "Sid": "AwsMarketplaceDataFeedsAccess", "Effect": "Allow", "Principal": { "Service": "reports.marketplace.amazonaws.com" }, "Action": [ "s3:ListBucket", "s3:GetObject", "s3:PutObject", "s3:GetEncryptionConfiguration", "s3:GetBucketAcl", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*" ] }, ``` In this policy, AWS Marketplace uses the `reports.marketplace.amazonaws.com` service principal to push data to the Amazon S3 bucket. You specified the *amzn-s3-demo-bucket* in the CloudFormation template. When the AWS Marketplace reports service calls Amazon S3, AWS KMS, or Amazon SNS, it provides the ARN of the data it intends to write to the bucket. To ensure that the only data written to your bucket is data written on your behalf, you can specify the `aws:SourceArn` in the condition of the policy. In the following example, you must replace the *account-id* with the ID for your AWS account. ``` { "Sid": "AwsMarketplaceDataFeedsAccess", "Effect": "Allow", "Principal": { "Service": "reports.marketplace.amazonaws.com" }, "Action": [ "s3:ListBucket", "s3:GetObject", "s3:PutObject", "s3:GetEncryptionConfiguration", "s3:GetBucketAcl", "s3:PutObjectAcl" ], "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket", "arn:aws:s3:::amzn-s3-demo-bucket/*" , "Condition": { "StringEquals": { "aws:SourceAccount": "account-id", "aws:SourceArn": ["arn:aws:marketplace::account-id:AWSMarketplace/SellerDataSubscription/DataFeeds_V1", "arn:aws:marketplace::account-id:AWSMarketplace/SellerDataSubscription/Example-Report"] } } }, ``` ## Unsubscribe from data feeds Open a web browser and sign in to the [AWS Marketplace Management Portal](http://aws.amazon.com/marketplace/management/). Then, go to the [Contact us page](https://aws.amazon.com/marketplace/management/contact-us/) to submit an unsubscribe request to the AWS Marketplace Seller Operations team. The unsubscribe request can take up to 10 business days to process. # Collecting and analyzing data with data feeds Using data feeds AWS Marketplace provides data feeds as a mechanism to send structured, up-to-date product and customer information from AWS Marketplace systems to seller Amazon S3 buckets for ETL (extract, transform, and load) between seller-owned business intelligence tools. When data is available in your Amazon S3 bucket, you can use data feeds in the following ways: + Download the .CSV files from the Amazon S3 bucket you created in [Accessing data feeds](data-feed-accessing.md) so that you can view the data in a spreadsheet. + Use ETL (extract, transform, and load), SQL query, business analytics tools to collect and analyze the data. You can use AWS services to collect and analyze data, or any third-party tool that can perform analysis of .CSV-based datasets. For more information about data feeds to collect and analyze data, see the following example. ## Example: Use AWS services to collect and analyze data The following procedure assumes that you've already configured your environment to receive data feeds to an Amazon S3 bucket and that the bucket contains data feeds. **To collect and analyze data from data feeds** 1. From the [AWS Glue console](https://console.aws.amazon.com/glue), [create a crawler](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html) to connect to the Amazon S3 bucket that stores the data feeds, extract the data you want, and create metadata tables in the AWS Glue Data Catalog. For more information about AWS Glue, see the [https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html). 1. From the [Athena console](https://console.aws.amazon.com/athena), [run SQL queries on the data in the AWS Glue Data Catalog](https://docs.aws.amazon.com/athena/latest/ug/querying-athena-tables.html). For more information about Athena see the [https://docs.aws.amazon.com/athena/latest/ug/what-is.html](https://docs.aws.amazon.com/athena/latest/ug/what-is.html). 1. From the [Quick console](http://quicksight.aws.amazon.com), [create an analysis](https://docs.aws.amazon.com/quick/latest/userguide/creating-an-analysis.html) and then [create a visual](https://docs.aws.amazon.com/quick/latest/userguide/creating-a-visual.html) of the data. For more information about Quick, see the [https://docs.aws.amazon.com/quick/latest/userguide/welcome.html](https://docs.aws.amazon.com/quick/latest/userguide/welcome.html). For a detailed example of one way to use AWS services to collect and analyze data in data feeds, see [Using Seller Data Feed Delivery Service, Amazon Athena, and Quick to create seller reports](https://aws.amazon.com/blogs/awsmarketplace/using-seller-data-feed-delivery-service-amazon-athena-and-amazon-quicksight-to-create-seller-reports/) at the AWS Marketplace Blog. # Data feed tables overview AWS Marketplace provides data feeds as a set of tables that you can join together to provide more context for your queries. AWS Marketplace provides the following general domains, or categories of interest, in your data feeds: + **Catalog** – Includes information about the products and offers in your account. + **Accounts** – Includes information about the accounts that provide or purchase products on AWS Marketplace (your own accounts or accounts of parties that you work with such as channel partners or buyers). + **Revenue** – Includes information about billing, disbursements, and taxes. + **Procurement** – includes information about the agreements for the product offers you have created as the seller of record. This diagram shows the tables in the Catalog, Accounts, and Revenue domains. ![\[Entity relationship diagram showing how data feeds relate to each other.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-overview.png) ## Catalog-related tables The following diagram shows the relationships between tables in the Catalog domain, as well as the fields within the tables. ![\[Relationships between the Product, Offer_Product, Offer, Offer_Target, and Legacy_id_mapping tables in the Catalog domain.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-catalog-details.png) The `Product`, `Offer_Product`, `Offer`, `Offer_Target`, and `Legacy_id_mapping`\$1tables are in the Catalog domain. The `Offer_Target` table includes a value field for the `account_id` of the target, but only when the `target_type` value is `account`. The `Legacy_id_mapping` table is not used for current data. **Note** For more information about these tables, including a description of each field in the table and the joins that can be created, see the following topics: [Product data feed](data-feed-product.md) [Offer product data feed](data-feed-offer-product.md) [Offer data feed](data-feed-offer.md) [Offer target data feed](data-feed-offer-target.md) [Legacy mapping data feed](data-feed-legacy-mapping.md) ## Accounts-related tables The following diagram shows the relationships between the `Account` and `Address` tables in the Accounts domain, as well as the fields within the tables. ![\[Relationship between the Account and Address tables in the Accounts domain, and fields within each table.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-accounts-details.png) **Note** For more information about these tables, including a description of each field in the table and the joins that you can create, see the following topics: [Account data feed](data-feed-account.md) [Address data feed](data-feed-address.md) ## Revenue-related tables The following diagram shows the relationships between the `Billing_Event` and `Tax_Item` tables in the Revenue domain, as well as the fields within the tables. The `Billing_Event` table includes information about disbursements, as well as billing events. ![\[Relationships between the Billing_Event and Tax_Item tables in the Revenue domain, and the fields within each table.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-revenue-details.png) **Note** For more information about these tables, including a description of each field in the table and the joins that you can create, see the following topics: [Billing event data feed](data-feed-billing-event.md) [Tax item data feed](data-feed-tax-item.md) ### Procurement related tables The following diagram shows the fields within the Agreement table in the Procurement domain. **Note** For more information about these tables, including a description of each field in the table and the joins that can be created, see [Agreements data feed](data-feed-agreements.md), in this guide. The following sections provide *entity relationship* (ER) diagrams for each domain. Each ER diagram shows the tables and the fields within each table, as well as the fields that you can use to join the tables. **Note** The ER diagrams in this section do not include the common fields for all data feeds. For more information about the common fields, see [Storage and structure of AWS Marketplace data feeds](data-feed-details.md). The following table describes the symbols that are used in the ER diagrams. | Symbol | Description | | --- | --- | | ![\[An image of the letters "PK" as a symbol.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-primary-key.png) | **Primary key** – A primary key for the table. When used with the `valid_from` and `update_date` fields, it is unique. For more details about using these fields together, see [Historization of the data](data-feed-details.md#data-feed-historization). If more than one field is marked as primary key, then the fields together form the primary key. | | ![\[An image of the letters "FK" as a symbol.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-foreign-key.png) | **Foreign key** – A field that represents a primary key in a different table. Not necessarily unique in the table. In some cases, the foreign key can be blank if the record in the current table does not have a corresponding record in the foreign table. | | ![\[An image of the letters "AK" as a symbol.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-alternate-key.png) | **Alternate key** – A key that can be used as a key in the table. Follows the same uniqueness rules as the primary key. | | ![\[An image of a line with a cross at one end and circle and fork at the other.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-one-to-many.png) | **Connector** – Lines between fields represent a connection, which is two fields that can be used to join tables. The ends of the line represent the type of connection. This example represents a one-to-many connection. | **Connector types** The following table shows the types of ends that each connector can have. | Connector type | Description | | --- | --- | | ![\[An image of a line with a cross at one end.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-one-to-n.png) | **One to n** – A connector with this end represents a join that has exactly one value on this side of the join. | | ![\[An image of a line with a cross and circle at one end.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-zero-or-one-to-n.png) | **Zero or one to n** – A connector with this end represents a join that has zero or one values on this side of the join. | | ![\[An image of a line with a circle and fork at one end.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-optional-many-to-n.png) | **Zero or more to n** – A connector with this end represents a join that has zero, one, or many values on this side of the join. | | ![\[An image of a line with a cross and fork at one end.\]](http://docs.aws.amazon.com/marketplace/latest/userguide/images/datafeeds-one-or-more-to-n.png) | **One or more to n** – A connector with this end represents a join that has one or many values on this side of the join. | # Data feed query examples This section gives examples of complex queries using the data feeds provided by AWS Marketplace. These examples are similar to the [Seller dashboards](dashboards.md) that you get from the AWS Marketplace Management Portal. You can customize these queries to create other reports that you need. **Topics** + [ ## Agreements and renewals ](#data-feed-example-agreements) + [ ## Billed revenue ](#data-feed-example-billed-revenue) + [ ## Uncollected or disbursed invoices ](#data-feed-example-collections) + [ ## Taxed invoices ](#data-feed-example-tax) + [ ## Disbursements by product ](#data-feed-example-disbursement-by-product) + [ ## Sales compensation report ](#data-feed-example-sales-compensation) ## Agreements and renewals To find your agreement and renewal data, you can run a set of queries like the following example. The queries build on each other to create the **Agreements and renewals** dashboard, granular data section. You can use the example as shown, or customize it for your data and use cases. Comments in the queries explain what the queries do, and how to modify them. ``` Query currently under development. ``` ## Billed revenue To find your invoice data, you can run a set of queries like the following example. The queries build on each other to create the **Billed revenue** report. You can use the example as shown, or customize it for your data and use cases. Comments in the queries explain what the queries do, and how to modify them. ``` -- Billed revenue report -- General note: When executing this query we are assuming that the data ingested in the database uses -- two time axes (the valid_from column and the update_date column). -- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details -- An account_id has several valid_from dates (each representing a separate revision of the data) -- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) with accounts_with_uni_temporal_data as ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, from_iso8601_timestamp(valid_from) as valid_from, tax_registration_number from ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, delete_date, tax_registration_number, row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from accountfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), accounts_with_history as ( with accounts_with_history_with_extended_valid_from as ( select account_id, -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be) substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because: -- ... in tax report transformations, some tax line items with invoice_date cannot -- ... fall into the default valid time range of the associated account case when lag(valid_from) over (partition by account_id order by valid_from asc) is null then cast('1970-01-01 00:00:00' as timestamp) else valid_from end as valid_from from accounts_with_uni_temporal_data ) select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, coalesce( lead(valid_from) over (partition by account_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to from accounts_with_history_with_extended_valid_from ), -- An address_id has several valid_from dates (each representing a separate revision of the data) -- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) address_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_num from ( select valid_from, update_date, delete_date, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_number() over (partition by address_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from addressfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), -- We are only interested in the most recent tuple (BTW: a given address is not supposed to change over time but when bugs ;-) so this query mainly does nothing) address_with_latest_revision as ( select valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_num_latest_revision from ( select valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_number() over (partition by address_id order by valid_from desc) as row_num_latest_revision from address_with_uni_temporal_data ) where row_num_latest_revision = 1 ), accounts_with_history_with_company_name as ( select awh.account_id, awh.aws_account_id, awh.encrypted_account_id, awh.mailing_address_id, awh.tax_address_id, coalesce( --empty value in Athena shows as '', change all '' value to null case when address.company_name = '' then null else address.company_name end, awh.tax_legal_name) as mailing_company_name, address.email_domain, awh.valid_from, -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually -- enter a backdated acceptance date, which might predate the point in time when the account was created. -- To work around this, we need to adjust the valid_from of the account to be -- earlier than the earliest possible backdated BYOL agreement acceptance date. case when lag(awh.valid_from) over (partition by aws_account_id order by awh.valid_from asc) is null then date_add('Day', -212, awh.valid_from) -- 212 is the longest delay between acceptance_date of the agreement and the account start_Date else awh.valid_from end as valid_from_adjusted, awh.valid_to from accounts_with_history as awh left join address_with_latest_revision as address on awh.mailing_address_id = address.address_id and awh.mailing_address_id is not null ), -- An agreement_id has several valid_from dates (each representing an agreement revision) -- but because of bi-temporality, an agreement_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) agreements_with_uni_temporal_data as ( select agreement_id, origin_offer_id, proposer_account_id, acceptor_account_id, agreement_revision, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(start_date) as start_date, from_iso8601_timestamp(end_date) as end_date, from_iso8601_timestamp(acceptance_date) as acceptance_date, agreement_type, previous_agreement_id, agreement_intent from ( select --empty value in Athena shows as '', change all '' value to null case when agreement_id = '' then null else agreement_id end as agreement_id, origin_offer_id, proposer_account_id, acceptor_account_id, agreement_revision, valid_from, delete_date, start_date, end_date, acceptance_date, agreement_type, previous_agreement_id, agreement_intent, row_number() over (partition by agreement_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from -- TODO change to agreementfeed_v1 when Agreement Feed is GA'ed agreementfeed ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), agreements_with_history as ( with agreements_with_window_functions as ( select agreement_id, origin_offer_id as offer_id, proposer_account_id, acceptor_account_id, agreement_revision, start_date, end_date, acceptance_date, -- The start time of agreement valid_from is extended to '1970-01-01 00:00:00', because: -- ... in usage report transformations, some usage line items with usage_date cannot -- ... fall into the default valid time range of the associated agreement case when lag(valid_from) over (PARTITION BY agreement_id order by valid_from asc) is null then timestamp '1970-01-01 00:00:00' else valid_from end as valid_from, coalesce( lead(valid_from) over (partition by agreement_id order by valid_from asc), timestamp '2999-01-01 00:00:00' ) as valid_to, rank() over (partition by agreement_id order by valid_from asc) version, agreement_type, previous_agreement_id, agreement_intent from agreements_with_uni_temporal_data ) select agreement_id, offer_id, proposer_account_id, acceptor_account_id, agreement_revision, start_date, end_date, acceptance_date, valid_from, case when version=1 and valid_from= cast('2021-04-01' as timestamp) then date_add('Day', -1460, valid_from) --after 2021 for the two offers we need to adjust for 2 more years else valid_from end as valid_from_adjusted, coalesce( lead(valid_from) over (partition by offer_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp)) as valid_to from offers_with_uni_temporal_data ), -- provided for reference only if you are interested into get "current" offer name -- (ie. not used afterwards) offers_with_latest_revision as ( select offer_id, offer_revision, name, opportunity_name, opportunity_description, valid_from, null valid_to from ( select offer_id, offer_revision, name, opportunity_name, opportunity_description, valid_from, null valid_to, row_number() over (partition by offer_id order by valid_from desc) as row_num_latest_revision from offers_with_uni_temporal_data ) where row_num_latest_revision = 1 ), -- An offer_target_id has several valid_from dates (each representing an offer revision) -- but because of bi-temporality, an offer_target_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) offer_targets_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, offer_target_id, offer_id, offer_revision, target_type, polarity, value from ( select valid_from, update_date, delete_date, offer_target_id, offer_id, offer_revision, target_type, polarity, value, row_number() over (partition by offer_target_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from offertargetfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), offer_target_type as ( select offer_id, offer_revision, substring( -- The first character indicates the priority (lower value means higher precedence): min( case when offer_target.target_type='BuyerAccounts' then '1Private' when offer_target.target_type='ParticipatingPrograms' then '2Program:'||cast(offer_target.value as varchar) when offer_target.target_type='CountryCodes' then '3GeoTargeted' -- well, there is no other case today, but rather be safe... else '4Other Targeting' end ), -- Remove the first character that was only used for th priority in the "min" aggregate function: 2 ) as offer_target from offer_targets_with_uni_temporal_data as offer_target group by offer_id, offer_revision ), offers_with_history_with_target_type as ( select offer.offer_id, offer.offer_revision, -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future max( case when off_tgt.offer_target is null then 'Public' else off_tgt.offer_target end ) as offer_target, min(offer.name) as name, min(offer.opportunity_name) as opportunity_name, min(offer.opportunity_description) as opportunity_description, offer.valid_from, offer.valid_from_adjusted, offer.valid_to, offer.opportunity_id from offers_with_history as offer left join offer_target_type as off_tgt on offer.offer_id = off_tgt.offer_id and offer.offer_revision = off_tgt.offer_revision group by offer.offer_id, offer.offer_revision, offer.valid_from, offer.valid_from_adjusted, offer.valid_to, offer.opportunity_id ), -- provided for reference only if you are interested into get "current" offer targets -- (ie. not used afterwards) offers_with_latest_revision_with_target_type as ( select offer.offer_id, offer.offer_revision, -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future max( distinct case when off_tgt.target_type is null then 'Public' when off_tgt.target_type='BuyerAccounts' then 'Private' when off_tgt.target_type='ParticipatingPrograms' then 'Program:'||cast(off_tgt.value as varchar) when off_tgt.target_type='CountryCodes' then 'GeoTargeted' -- well, there is no other case today, but rather be safe... else 'Other Targeting' end ) as offer_target, min(offer.name) as name, min(offer.opportunity_name) as opportunity_name, min(offer.opportunity_description) as opportunity_description, offer.valid_from, offer.valid_to from offers_with_latest_revision offer -- left joining because public offers don't have targets left join offer_targets_with_uni_temporal_data off_tgt on offer.offer_id=off_tgt.offer_id and offer.offer_revision=off_tgt.offer_revision group by offer.offer_id, offer.offer_revision, -- redundant with offer_revision, as each revision has a dedicated valid_from (but cleaner in the group by) offer.valid_from, offer.valid_to ), -- A product_id has several valid_from dates (each representing a product revision), -- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) products_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, product_id, manufacturer_account_id, product_code, title from ( select valid_from, update_date, delete_date, product_id, manufacturer_account_id, product_code, title, row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from productfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), products_with_history as ( select product_id, title, valid_from, -- Offerv2 can have upto 50 years and Offerv3 is upto 5 years of past date case when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from= cast('2021-04-01' as timestamp) then date_add('Day', -2190, valid_from) --after 2021 for the two offers we need to adjust for 2 more years else valid_from end as valid_from_adjusted, coalesce( lead(valid_from) over (partition by product_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to, product_code, manufacturer_account_id from products_with_uni_temporal_data ), legacy_products as ( select legacy_id, new_id from legacyidmappingfeed_v1 where mapping_type='PRODUCT' group by legacy_id, new_id ), -- A given billing_event_id represents an accounting event and thus has only one valid_from date, -- but because of bi-temporality, a billing_event_id (+ its valid_from) can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) billing_events_with_uni_temporal_data as ( select billing_event_id, valid_from, update_date, delete_date, invoice_date, transaction_type, transaction_reference_id, parent_billing_event_id, bank_trace_id, broker_id, product_id, disbursement_billing_event_id, action, from_account_id, to_account_id, end_user_account_id, billing_address_id, amount, currency, balance_impacting, --empty value in Athena shows as '', change all '' value to null case when agreement_id = '' then null else agreement_id end as agreement_id, invoice_id, payment_due_date, usage_period_start_date, usage_period_end_date, buyer_transaction_reference_id, row_num from ( select billing_event_id, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, delete_date, from_iso8601_timestamp(invoice_date) as invoice_date, transaction_type, transaction_reference_id, parent_billing_event_id, -- casting in case data was imported as number cast(bank_trace_id as varchar) as bank_trace_id, broker_id, product_id, disbursement_billing_event_id, action, from_account_id, to_account_id, end_user_account_id, billing_address_id, -- casting in case data was imported as varchar cast(amount as decimal(38,6)) as amount, currency, balance_impacting, agreement_id, invoice_id, case when payment_due_date is null or payment_due_date = '' then null else from_iso8601_timestamp(payment_due_date) end as payment_due_date, from_iso8601_timestamp(usage_period_start_date) as usage_period_start_date, from_iso8601_timestamp(usage_period_end_date) as usage_period_end_date, buyer_transaction_reference_id, row_number() over (partition by billing_event_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from billingeventfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), -- Here we select the account_id of the current seller (We identify this by looking for the to_account_id related to revenue transactions). -- We will use it later to distinguish own agreements from agreements generated by channel partners. seller_account as ( select from_account_id as seller_account_id from billing_events_with_uni_temporal_data bill where -- Assumption here is only seller will pay listing fee. As of 12/21/2021, there are cases that Channel partner have 0 listing fee for CPPO, so the amount could be 0. bill.transaction_type like 'AWS_REV_SHARE' and amount <= 0 and action = 'INVOICED' group by -- from_account_id is always the same for all those "listing fee" transactions == the seller of record himself. -- If this view returns more than 1 record, the overall query will fail (on purpose). Please contact AWS Marketplace if this happens. from_account_id ), billing_event_with_business_flags as ( select bl.billing_event_id, bl.end_user_account_id, bl.agreement_id, aggrement.proposer_account_id, aggrement.offer_id, aggrement.acceptor_account_id, case -- For AWS and BALANCE_ADJUSTMENT, the billing event feed will show the "AWS Marketplace" account as the -- receiver of the funds and the seller as the payer. We are not interested in this information here. -- Null values will be ignored by the `max` aggregation function. when bl.transaction_type like 'AWS%' then null -- For BALANCE_ADJUSTMENT, payer is seller themselves when bl.invoice_id is null then bl.to_account_id -- We get the payer of the invoice from *any* transaction type that is not AWS and not BALANCE_ADJUSTMENT (because they are the same for a given end user + agreement + product). else bl.from_account_id end as payer_account_id, bl.product_id, bl.action, bl.transaction_type, bl.parent_billing_event_id, bl.disbursement_billing_event_id, bl.amount, bl.currency, bl.balance_impacting, bl.invoice_date, bl.payment_due_date, bl.usage_period_start_date, bl.usage_period_end_date, bl.invoice_id, bl.billing_address_id, bl.transaction_reference_id, bl.buyer_transaction_reference_id, case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.bank_trace_id end as bank_trace_id, case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.invoice_date end as disbursement_date, disbursement.billing_event_id as disbursement_id, -- We will use disbursement_id_or_invoiced as part of the PK, so it cannot be null: coalesce( --empty value in Athena shows as '', change all '' value to null case when disbursement.billing_event_id = '' then null else disbursement.billing_event_id end, '') as disbursement_id_or_invoiced, bl.broker_id, case when bl.invoice_id is null /* transaction_type = 'BALANCE_ADJUSTMENT' */ then (select seller_account_id from seller_account) ||':'|| cast(bl.invoice_date as varchar) else bl.buyer_transaction_reference_id ||'-'|| case when bl.agreement_id is null or bl.agreement_id = '' then ' ' else bl.agreement_id end ||'-'|| case when bl.end_user_account_id is null or bl.end_user_account_id = '' then ' ' else bl.end_user_account_id end ||'-'|| coalesce(cast(bl.usage_period_start_date as varchar),' ') ||'-'|| coalesce(cast(bl.usage_period_end_date as varchar),' ') end as internal_buyer_line_item_id, bl.buyer_transaction_reference_id <> bl.transaction_reference_id as is_seller_invoice, case when bl.transaction_type = 'SELLER_REV_SHARE' and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog, case when bl.transaction_type in('SELLER_REV_SHARE_CREDIT', 'SELLER_REV_SHARE_REFUND') and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog_refund, --TODO: replace below logic once we can create a logic the identify reseller/manufacturer without depending on agreement feed case when aggrement.proposer_account_id <> (select seller_account_id from seller_account) then true else false end as is_manufacturer_view_of_reseller from billing_events_with_uni_temporal_data as bl left join billing_events_with_uni_temporal_data as disbursement on disbursement.transaction_type like 'DISBURSEMENT%' and disbursement.action = 'DISBURSED' and disbursement.transaction_type IN ('DISBURSEMENT', 'DISBURSEMENT_FAILURE') and bl.disbursement_billing_event_id = disbursement.billing_event_id left join agreements_with_history as aggrement on bl.agreement_id = aggrement.agreement_id and bl.invoice_date >= aggrement.valid_from_adjusted and bl.invoice_date= awh.valid_from and bl.invoice_date '' these are disbursed amounts for _this_ specific disbursement_id sum(gross_revenue) as gross_revenue_this_disbursement_id_or_invoiced, sum(gross_refund) as gross_refund_this_disbursement_id_or_invoiced, sum(cogs) as cogs_this_disbursement_id_or_invoiced, sum(cogs_refund) as cogs_refund_this_disbursement_id_or_invoiced, sum(aws_rev_share) as aws_rev_share_this_disbursement_id_or_invoiced, sum(aws_refund_share) as aws_refund_share_this_disbursement_id_or_invoiced, sum(aws_tax_share) as aws_tax_share_this_disbursement_id_or_invoiced, sum(aws_tax_share_listing_fee) as aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, sum(aws_tax_share_refund) as aws_tax_share_refund_this_disbursement_id_or_invoiced, sum(aws_tax_share_refund_listing_fee) as aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, sum(seller_tax_share) as seller_tax_share_this_disbursement_id_or_invoiced, sum(seller_tax_share_refund) as seller_tax_share_refund_this_disbursement_id_or_invoiced, sum(balance_adjustment) as balance_adjustment_this_disbursement_id_or_invoiced, sum(seller_rev_credit) as seller_rev_credit_this_disbursement_id_or_invoiced, sum(aws_ref_fee_credit) as aws_ref_fee_credit_this_disbursement_id_or_invoiced from billing_event_with_categorized_transaction as billing_categorized group by internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, broker_id, -- The following columns are included the in group by but they are intentionally omitted from the PK. -- These columns should have the _same_ values for each record in the PK. product_id, currency, agreement_id, proposer_account_id, acceptor_account_id, offer_id, end_user_account_id, usage_period_start_date, usage_period_end_date, buyer_transaction_reference_id, bank_trace_id, disbursement_date ), -- listagg function in athena does not support partitioning, grouping here and then joining to the main query disbursement_list as ( select internal_buyer_line_item_id, listagg(cast(disbursement_date as varchar),',') within group (order by cast(disbursement_date as varchar)) as disbursement_date_list, listagg(bank_trace_id,',') within group (order by cast(disbursement_date as varchar)) as disburse_bank_trace_id_list from ( -- listagg function in athena does not support ordering by another field when distinct is used, -- here we first select distinct bank_trace_ids and then do the listagg order by disbursement_date select distinct internal_buyer_line_item_id, disbursement_date, bank_trace_id from billing_event_with_business_flags) distinct_disbursements group by internal_buyer_line_item_id order by internal_buyer_line_item_id ), line_items_with_window_functions as ( --add flag next step compare gross_revenue and gross_revenue_disbursed or gross_refund and gross_refund_disbursed select line_item.internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, product_id, broker_id, currency, agreement_id, proposer_account_id, acceptor_account_id, -- when there's aws_rev_Share adjustment/refund to a seller_rev_share invoice, it can happen that for the same aws_rev_share invoice_id, there are multiple disbursement events, -- using windows function to map payer_account_id of seller_rev_share to all corresponding aws_rev_Share max(payer_account_id) over (partition by line_item.internal_buyer_line_item_id) as payer_account_id, offer_id, end_user_account_id, usage_period_start_date, usage_period_end_date, payment_due_date, bank_trace_id, disbursement_date, billing_address_id, -- Buyer/seller columns: max(buyer_invoice_id) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_id, seller_invoice_id, max(buyer_invoice_date) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_date, seller_invoice_date, -- When disbursement_id_or_invoiced = '', these are actually invoiced amounts -- When disbursement_id_or_invoiced <> '' these are disbursed amounts for _this_ specific disbursement_id gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, -- IMPORTANT: All window functions partitioned by internal_buyer_line_item_id: -- Invoiced amounts, categorized by transaction type: sum(case when disbursement_id_or_invoiced = '' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end)over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_invoiced, sum(case when disbursement_id_or_invoiced = '' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_invoiced, sum(case when disbursement_id_or_invoiced = '' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_invoiced, sum(case when disbursement_id_or_invoiced = '' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_invoiced, sum(case when disbursement_id_or_invoiced = '' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_invoiced, sum(case when disbursement_id_or_invoiced = '' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_invoiced, sum(case when disbursement_id_or_invoiced = '' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_invoiced, sum(case when disbursement_id_or_invoiced = '' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_invoiced, sum(case when disbursement_id_or_invoiced = '' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_invoiced, -- Total disbursed amounts (for all disbursement_id values), categorized by transaction type: sum(case when disbursement_id_or_invoiced <> '' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_disbursed, sum(case when disbursement_id_or_invoiced <> '' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_disbursed, sum(case when disbursement_id_or_invoiced <> '' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_disbursed, sum(case when disbursement_id_or_invoiced <> '' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_disbursed, sum(case when disbursement_id_or_invoiced <> '' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_disbursed, sum(case when disbursement_id_or_invoiced <> '' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_disbursed, sum(case when disbursement_id_or_invoiced <> '' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_disbursed, sum(case when disbursement_id_or_invoiced <> '' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_disbursed, sum(case when disbursement_id_or_invoiced <> '' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_disbursed, -- aggregate multiple disbursement max(disbursement_date) over (partition by line_item.internal_buyer_line_item_id) as last_disbursement_date, first_value(case when disbursement_id_or_invoiced = '' then null else disbursement_id_or_invoiced end) over(partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disbursement_id, first_value(bank_trace_id) over (partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disburse_bank_trace_id, disb_list.disbursement_date_list, disb_list.disburse_bank_trace_id_list from line_items_aggregated as line_item left join disbursement_list disb_list on line_item.internal_buyer_line_item_id = disb_list.internal_buyer_line_item_id ), cppo_offer_id as ( select -- Channel partner offers do not exist in offertargetfeed_v1 table (as per legal requirement), causing cppo offer be defined as 'Public' in previous step, we will convert them back to 'Private' in next step offer_id from offers_with_uni_temporal_data where -- seller_account_id is null means the ISV owns the offer seller_account_id is not null and seller_account_id <> (select seller_account_id from seller_account) group by offer_id ), line_items_with_window_functions_enrich_offer_product_address as ( select internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, line.product_id, legacy_product.legacy_id as legacy_product_id, products.title as product_title, line.broker_id, line.currency, line.end_user_account_id, acc_enduser.encrypted_account_id as end_user_encrypted_account_id, acc_enduser.aws_account_id as end_user_aws_account_id, acc_payer.aws_account_id as payer_aws_account_id, acc_payer.encrypted_account_id payer_encrypted_account_id, line.agreement_id, agreement.agreement_revision, line.proposer_account_id, case when offer.offer_id like 'aiqoffer-%' then null else agreement.start_date end as Agreement_Start_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.end_date end as Agreement_End_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.acceptance_date end as Agreement_Acceptance_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.valid_from end as agreement_updated_date, case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_start_date end as Usage_Period_Start_Date, case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_end_date end as Usage_Period_End_Date, line.acceptor_account_id, acc_subscriber.aws_account_id as subscriber_aws_account_id, acc_subscriber.encrypted_account_id as subscriber_encrypted_account_id, offer.offer_id, case when offer.offer_id in ( select distinct offer_id from cppo_offer_id) then 'Private' else offer.offer_target end as offer_target, offer.name offer_name, offer.opportunity_name offer_opportunity_name, offer.opportunity_description offer_opportunity_description, offer.opportunity_id, payment_due_date, line.bank_trace_id, disbursement_date, billing_address_id, buyer_invoice_id, seller_invoice_id, buyer_invoice_date, seller_invoice_date, gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, gross_revenue_invoiced, gross_refund_invoiced, cogs_invoiced, cogs_refund_invoiced, aws_rev_share_invoiced, aws_refund_share_invoiced, aws_tax_share_invoiced, aws_tax_share_listing_fee_invoiced, aws_tax_share_refund_invoiced, aws_tax_share_refund_listing_fee_invoiced, seller_tax_share_invoiced, seller_tax_share_refund_invoiced, balance_adjustment_invoiced, seller_rev_credit_invoiced, aws_ref_fee_credit_invoiced, gross_revenue_disbursed, gross_refund_disbursed, cogs_disbursed, cogs_refund_disbursed, aws_rev_share_disbursed, aws_refund_share_disbursed, aws_tax_share_disbursed, aws_tax_share_listing_fee_disbursed, aws_tax_share_refund_disbursed, aws_tax_share_refund_listing_fee_disbursed, seller_tax_share_disbursed, seller_tax_share_refund_disbursed, balance_adjustment_disbursed, seller_rev_credit_disbursed, aws_ref_fee_credit_disbursed, last_disbursement_date, last_disbursement_id, last_disburse_bank_trace_id, disbursement_date_list, disburse_bank_trace_id_list, products.product_code, acc_products.aws_account_id as manufacturer_aws_account_id, products.manufacturer_account_id, --add subscriber and payer addressID, payer address preference order: tax address > billing address > mailing address, subscriber address preference order: tax address > mailing address coalesce ( --empty value in Athena shows as '', change all '' value to null in order to follow the preference order logic above case when acc_subscriber.tax_address_id ='' then null else acc_subscriber.tax_address_id end, case when acc_subscriber.mailing_address_id = '' then null else acc_subscriber.mailing_address_id end) as subscriber_address_id, coalesce ( case when acc_payer.tax_address_id = '' then null else acc_payer.tax_address_id end, case when line.billing_address_id = '' then null else line.billing_address_id end, case when acc_payer.mailing_address_id = '' then null else acc_payer.mailing_address_id end) as payer_address_id, coalesce ( case when acc_enduser.tax_address_id = '' then null else acc_enduser.tax_address_id end, case when line.billing_address_id = '' then null else line.billing_address_id end, case when acc_enduser.mailing_address_id = '' then null else acc_enduser.mailing_address_id end) as end_user_address_id from line_items_with_window_functions as line left join agreements_with_history as agreement on line.agreement_id = agreement.agreement_id and line.buyer_invoice_date >= agreement.valid_from_adjusted and line.buyer_invoice_date= offer.valid_from and line.buyer_invoice_date= products.valid_from_adjusted and line.buyer_invoice_date= acc_payer.valid_from and line.buyer_invoice_date= acc_enduser.valid_from and line.buyer_invoice_date= acc_subscriber.valid_from and line.buyer_invoice_date= acc_products.valid_from and line.buyer_invoice_date0 and gross_revenue_invoiced = -1 * gross_revenue_disbursed) or (gross_refund_invoiced <> 0 and gross_refund_invoiced = -1 * gross_refund_disbursed) or (balance_adjustment_invoiced <> 0 and balance_adjustment_invoiced = -1 * balance_adjustment_disbursed) or (seller_tax_share_refund_invoiced <> 0 and seller_tax_share_refund_invoiced = -1 * seller_tax_share_refund_disbursed) or (gross_revenue_invoiced = 0 and gross_refund_invoiced = 0 and balance_adjustment_invoiced = 0 and seller_tax_share_refund_invoiced = 0 and last_disbursement_id is not null)) then 'Yes' when gross_revenue_disbursed = 0 and gross_refund_disbursed = 0 and balance_adjustment_disbursed = 0 and seller_tax_share_disbursed = 0 and seller_tax_share_refund_disbursed = 0 then 'No' else 'Partial' end as Disbursement_Flag from line_items_with_window_functions_enrich_offer_product_address as line left join accounts_with_history_with_company_name as acc_manu on line.manufacturer_account_id = acc_manu.account_id and line.buyer_invoice_date >= acc_manu.valid_from_adjusted and line.buyer_invoice_date <= acc_manu.valid_to left join accounts_with_history_with_company_name as acc_proposer on line.proposer_account_id = acc_proposer.account_id and line.buyer_invoice_date >= acc_proposer.valid_from and line.buyer_invoice_date180 then '180+' else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar) end, ' days' ) as payment_terms, buyer_invoice_id as Invoice_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when seller_invoice_id = '' then null else seller_invoice_id end, 'Not applicable') as Listing_Fee_Invoice_ID, --------------------------- --End user Information -- --------------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when End_User_Company_Name = '' then null else End_User_Company_Name end, 'Not available') as End_User_Company_Name, End_User_AWS_Account_ID, End_User_Encrypted_Account_ID, End_User_Email_Domain, End_User_City, End_User_State as End_User_State_or_Region, End_User_Country, End_User_Postal_Code, End_User_Address_ID, --------------------------- --Subscriber Information -- --------------------------- case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided' else Subscriber_Company_Name end as Subscriber_Company_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_AWS_Account_ID end as Subscriber_AWS_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_Encrypted_Account_ID end as Subscriber_Encrypted_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided' else Subscriber_Email_Domain end as Subscriber_Email_Domain, case when Agreement_id is null or Agreement_ID = '' then 'Not available' when Subscriber_City is null or Subscriber_City = '' then 'Not provided' else Subscriber_City end as Subscriber_City, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_State is null or Subscriber_State = '' then 'Not provided' else Subscriber_State end as Subscriber_State_or_Region, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided' else Subscriber_Country end as Subscriber_Country, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided' else Subscriber_Postal_Code end as Subscriber_Postal_Code, case when Agreement_ID is null or Agreement_ID = '' then 'Not available' when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided' else Subscriber_Address_ID end as Subscriber_Address_ID, ---------------------- -- Procurement Info -- ---------------------- -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id. coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_Title = '' then null else Product_Title end, 'Not provided') as Product_Title, -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id. case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable' else Offer_Name end as Offer_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_ID end as Offer_ID, -- offer target at time of invoice., case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_Target end as Offer_Visibility, coalesce( --empty value in Athena shows as '', change all '' value to null case when Agreement_ID = '' then null else Agreement_ID end, 'Not available') as Agreement_ID, Agreement_Start_Date, Agreement_Acceptance_Date, Agreement_End_Date, Usage_Period_Start_Date, Usage_Period_End_Date, ----------------------- -- Disbursement Info -- ----------------------- case when Disbursement_Flag = 'Yes' then 'Disbursed' when Disbursement_Flag = 'No' then 'Not Disbursed' else 'Other' end as Disbursement_Status, last_disbursement_date as disbursement_date, case when Disbursement_Flag = 'No' then 'Not applicable' when disburse_bank_trace_id_list is null or disburse_bank_trace_id_list = '' then 'Not available' else disburse_bank_trace_id_list end as disburse_bank_trace_id, -------------- -- Revenues -- -------------- -- We are rounding the sums using 2 decimal precision -- Note that the rounding method might differ between SQL implementations. -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output -- and the legacy report round(gross_revenue_invoiced,2) as Gross_Revenue, round(gross_refund_invoiced,2) as Gross_Refund, round(aws_rev_share_invoiced,2) as Listing_Fee, round(aws_refund_share_invoiced,2) as Listing_Fee_Refund, truncate( case when gross_revenue_invoiced != 0 then abs(aws_rev_share_invoiced/gross_revenue_invoiced) when gross_refund_invoiced != 0 then abs(aws_refund_share_invoiced/gross_refund_invoiced) else 0 end ,4) as Listing_Fee_Percentage, round(seller_tax_share_invoiced,2) as Seller_Tax_Share, round(seller_tax_share_refund_invoiced,2) as Seller_Tax_Share_Refund, round(aws_tax_share_invoiced,2) as AWS_Tax_Share, round(aws_tax_share_refund_invoiced,2) as AWS_Tax_Share_Refund, round(aws_tax_share_listing_fee_invoiced,2) as AWS_Tax_Share_Listing_Fee, round(aws_tax_share_refund_listing_fee_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee, round(cogs_invoiced,2) as Wholesale_cost, round(cogs_refund_invoiced,2) as Wholesale_cost_Refund, round(seller_net_revenue,2) as Seller_Net_Revenue, currency as Currency, substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID, broker_id as AWS_seller_of_record, ----------------- -- Resale info -- ----------------- case when Opportunity_Id is null or Opportunity_Id = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Opportunity_Id end as Resale_authorization_ID, case when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Name end as Resale_authorization_name, case when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Name end as Resale_authorization_description, case when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '') and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available' when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (opportunity_id is null or opportunity_id = '') then 'Not applicable' when (select seller_account_id from seller_account) <> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_Company_Name end as Reseller_Company_Name, case when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable' when (select seller_account_id from seller_account) <> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_AWS_Account_ID end as Reseller_AWS_Account_ID, ----------------------- -- Payer Information -- ----------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when Payer_Company_Name = '' then null else Payer_Company_Name end, 'Not available') as Payer_Company_Name, Payer_AWS_Account_ID, Payer_Encrypted_Account_ID, Payer_Email_Domain, Payer_City, Payer_State as Payer_State_or_Region, Payer_Country, Payer_Postal_Code, Payer_Address_ID, --------------------- -- ISV Information -- --------------------- manufacturer_aws_account_id as ISV_Account_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end, 'Not available') as ISV_Company_Name, --------------------- -- Products info -- --------------------- Legacy_Product_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_ID = '' then null else Product_ID end, 'Not provided') as Product_ID, Product_Code from line_items_with_window_functions_enrich_offer_product_address_name as line where disbursement_id_or_invoiced = '' ) select * from billed_revenue where invoice_date >= date_add('DAY', -90, current_date) --where invoice_date between cast('2023-01-01' as timestamp) and cast('2024-03-01' as timestamp) ``` ## Uncollected or disbursed invoices To find your uncollected or disbursed invoices, you can run a set of queries like the following example. The queries build on each other to create the **Collections and disbursements** report. You can use the example as shown, or customize it for your data and use cases. Comments in the queries explain what the queries do, and how to modify them. ``` -- Collections and disbursements report -- General note: When running this query, we assume that the data ingested in the database uses -- two time axes (the valid_from column and the update_date column). -- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details -- An account_id has several valid_from dates (each representing a separate revision of the data) -- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) with accounts_with_uni_temporal_data as ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, from_iso8601_timestamp(valid_from) as valid_from, tax_registration_number from ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, delete_date, tax_registration_number, row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from accountfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), accounts_with_history as ( with accounts_with_history_with_extended_valid_from as ( select account_id, -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be) substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because: -- ... in tax report transformations, some tax line items with invoice_date cannot -- ... fall into the default valid time range of the associated account case when lag(valid_from) over (partition by account_id order by valid_from asc) is null then cast('1970-01-01 00:00:00' as timestamp) else valid_from end as valid_from from accounts_with_uni_temporal_data ) select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, coalesce( lead(valid_from) over (partition by account_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to from accounts_with_history_with_extended_valid_from ), -- An address_id has several valid_from dates (each representing a separate revision of the data) -- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) address_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_num from ( select valid_from, update_date, delete_date, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_number() over (partition by address_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from addressfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), -- We are only interested in the most recent tuple (BTW: a given address is not supposed to change over time but when bugs ;-) so this query mainly does nothing) address_with_latest_revision as ( select valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_num_latest_revision from ( select valid_from, address_id, company_name, email_domain, country_code, state_or_region, city, postal_code, row_number() over (partition by address_id order by valid_from desc) as row_num_latest_revision from address_with_uni_temporal_data ) where row_num_latest_revision = 1 ), accounts_with_history_with_company_name as ( select awh.account_id, awh.aws_account_id, awh.encrypted_account_id, awh.mailing_address_id, awh.tax_address_id, coalesce( --empty value in Athena shows as '', change all '' value to null case when address.company_name = '' then null else address.company_name end, awh.tax_legal_name) as mailing_company_name, address.email_domain, awh.valid_from, -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually -- enter a backdated acceptance date, which might predate the point in time when the account was created. -- To work around this, we need to adjust the valid_from of the account to be -- earlier than the earliest possible backdated BYOL agreement acceptance date. case when lag(awh.valid_from) over (partition by aws_account_id order by awh.valid_from asc) is null then date_add('Day', -212, awh.valid_from) -- 212 is the longest delay between acceptance_date of the agreement and the account start_Date else awh.valid_from end as valid_from_adjusted, awh.valid_to from accounts_with_history as awh left join address_with_latest_revision as address on awh.mailing_address_id = address.address_id and awh.mailing_address_id is not null ), -- An agreement_id has several valid_from dates (each representing an agreement revision) -- but because of bi-temporality, an agreement_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) agreements_with_uni_temporal_data as ( select agreement_id, origin_offer_id, proposer_account_id, acceptor_account_id, agreement_revision, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(start_date) as start_date, from_iso8601_timestamp(end_date) as end_date, from_iso8601_timestamp(acceptance_date) as acceptance_date, agreement_type, previous_agreement_id, agreement_intent from ( select --empty value in Athena shows as '', change all '' value to null case when agreement_id = '' then null else agreement_id end as agreement_id, origin_offer_id, proposer_account_id, acceptor_account_id, agreement_revision, valid_from, delete_date, start_date, end_date, acceptance_date, agreement_type, previous_agreement_id, agreement_intent, row_number() over (partition by agreement_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from -- TODO change to agreementfeed_v1 when Agreement Feed is GA'ed agreementfeed ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), agreements_with_history as ( with agreements_with_window_functions as ( select agreement_id, origin_offer_id as offer_id, proposer_account_id, acceptor_account_id, agreement_revision, start_date, end_date, acceptance_date, -- The start time of agreement valid_from is extended to '1970-01-01 00:00:00', because: -- ... in usage report transformations, some usage line items with usage_date cannot -- ... fall into the default valid time range of the associated agreement case when lag(valid_from) over (PARTITION BY agreement_id order by valid_from asc) is null then timestamp '1970-01-01 00:00:00' else valid_from end as valid_from, coalesce( lead(valid_from) over (partition by agreement_id order by valid_from asc), timestamp '2999-01-01 00:00:00' ) as valid_to, rank() over (partition by agreement_id order by valid_from asc) version, agreement_type, previous_agreement_id, agreement_intent from agreements_with_uni_temporal_data ) select agreement_id, offer_id, proposer_account_id, acceptor_account_id, agreement_revision, start_date, end_date, acceptance_date, valid_from, case when version=1 and valid_from < timestamp '2023-03-03 06:16:08.743' then timestamp '1970-01-01' -- The following 60 minute adjustment is to handle special case where When Renewal happens for a contract when version=1 then date_add('minute',-60,valid_from) else valid_from end as valid_from_adjusted, valid_to, agreement_type, previous_agreement_id, agreement_intent from agreements_with_window_functions ), -- An offer_id has several valid_from dates (each representing an offer revision) -- but because of bi-temporality, an offer_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) offers_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, offer_id, offer_revision, name, expiration_date, opportunity_id, opportunity_name, opportunity_description, seller_account_id from ( select valid_from, update_date, delete_date, offer_id, offer_revision, name, expiration_date, opportunity_id, opportunity_name, opportunity_description, seller_account_id, row_number() over (partition by offer_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from offerfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), -- Here, we build the validity time range (adding valid_to on top of valid_from) of each offer revision. -- We will use it to get Offer name at invoice time. -- NB: If you'd rather get "current" offer name, un-comment "offers_with_latest_revision" offers_with_history as ( select offer_id, offer_revision, name, opportunity_id, opportunity_name, opportunity_description, valid_from, -- When we try to look up an offer revision as at the acceptance date of a BYOL agreement, we run into a problem. -- For BYOL, the agreement might be accepted (using some external non-AWS system or manual process) days before -- that BYOL agreement is entered into AWS Marketplace by the buyer. Therefore, the buyer is permitted to manually -- enter a backdated acceptance date, which might predate the point in time when the first revision of the offer -- was created. To work around this, we need to adjust the valid_from on the first revision of the offer to be -- earlier than the earliest possible backdated BYOL agreement acceptance date. case when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp) then date_add('Day', -3857, valid_from) -- 3857 is the longest delay between acceptance_date of an agreement and the first revision of the offer when lag(valid_from) over (partition by offer_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp) then date_add('Day', -1460, valid_from) --after 2021 for the two offers we need to adjust for 2 more years else valid_from end as valid_from_adjusted, coalesce( lead(valid_from) over (partition by offer_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp)) as valid_to from offers_with_uni_temporal_data ), -- provided for reference only if you are interested into get "current" offer name -- (ie. not used afterwards) offers_with_latest_revision as ( select offer_id, offer_revision, name, opportunity_name, opportunity_description, valid_from, null valid_to from ( select offer_id, offer_revision, name, opportunity_name, opportunity_description, valid_from, null valid_to, row_number() over (partition by offer_id order by valid_from desc) as row_num_latest_revision from offers_with_uni_temporal_data ) where row_num_latest_revision = 1 ), -- An offer_target_id has several valid_from dates (each representing an offer revision) -- but because of bi-temporality, an offer_target_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) offer_targets_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, offer_target_id, offer_id, offer_revision, target_type, polarity, value from ( select valid_from, update_date, delete_date, offer_target_id, offer_id, offer_revision, target_type, polarity, value, row_number() over (partition by offer_target_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from offertargetfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), offer_target_type as ( select offer_id, offer_revision, substring( -- The first character indicates the priority (lower value means higher precedence): min( case when offer_target.target_type='BuyerAccounts' then '1Private' when offer_target.target_type='ParticipatingPrograms' then '2Program:'||cast(offer_target.value as varchar) when offer_target.target_type='CountryCodes' then '3GeoTargeted' -- well, there is no other case today, but rather be safe... else '4Other Targeting' end ), -- Remove the first character that was only used for th priority in the "min" aggregate function: 2 ) as offer_target from offer_targets_with_uni_temporal_data as offer_target group by offer_id, offer_revision ), offers_with_history_with_target_type as ( select offer.offer_id, offer.offer_revision, -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future max( case when off_tgt.offer_target is null then 'Public' else off_tgt.offer_target end ) as offer_target, min(offer.name) as name, min(offer.opportunity_name) as opportunity_name, min(offer.opportunity_description) as opportunity_description, offer.valid_from, offer.valid_from_adjusted, offer.valid_to, offer.opportunity_id from offers_with_history as offer left join offer_target_type as off_tgt on offer.offer_id = off_tgt.offer_id and offer.offer_revision = off_tgt.offer_revision group by offer.offer_id, offer.offer_revision, offer.valid_from, offer.valid_from_adjusted, offer.valid_to, offer.opportunity_id ), -- provided for reference only if you are interested into get "current" offer targets -- (ie. not used afterwards) offers_with_latest_revision_with_target_type as ( select offer.offer_id, offer.offer_revision, -- even though today it is not possible to combine several types of targeting in a single offer, let's ensure the query is still predictable if this gets possible in the future max( distinct case when off_tgt.target_type is null then 'Public' when off_tgt.target_type='BuyerAccounts' then 'Private' when off_tgt.target_type='ParticipatingPrograms' then 'Program:'||cast(off_tgt.value as varchar) when off_tgt.target_type='CountryCodes' then 'GeoTargeted' -- well, there is no other case today, but rather be safe... else 'Other Targeting' end ) as offer_target, min(offer.name) as name, min(offer.opportunity_name) as opportunity_name, min(offer.opportunity_description) as opportunity_description, offer.valid_from, offer.valid_to from offers_with_latest_revision offer -- left joining because public offers don't have targets left join offer_targets_with_uni_temporal_data off_tgt on offer.offer_id=off_tgt.offer_id and offer.offer_revision=off_tgt.offer_revision group by offer.offer_id, offer.offer_revision, -- redundant with offer_revision, as each revision has a dedicated valid_from (but cleaner in the group by) offer.valid_from, offer.valid_to ), -- A product_id has several valid_from dates (each representing a product revision), -- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) products_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, product_id, manufacturer_account_id, product_code, title from ( select valid_from, update_date, delete_date, product_id, manufacturer_account_id, product_code, title, row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from productfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), products_with_history as ( select product_id, title, valid_from, -- Offerv2 can have upto 50 years and Offerv3 is upto 5 years of past date case when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp) then date_add('Day', -3857, valid_from) -- 3827 is the longest delay between acceptance_date of an agreement and the product -- we are keeping 3857 as a consistency between the offers and products when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp) then date_add('Day', -2190, valid_from) --after 2021 for the two offers we need to adjust for 2 more years else valid_from end as valid_from_adjusted, coalesce( lead(valid_from) over (partition by product_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to, product_code, manufacturer_account_id from products_with_uni_temporal_data ), legacy_products as ( select legacy_id, new_id from legacyidmappingfeed_v1 where mapping_type='PRODUCT' group by legacy_id, new_id ), -- A given billing_event_id represents an accounting event and thus has only one valid_from date, -- but because of bi-temporality, a billing_event_id (+ its valid_from) can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) billing_events_with_uni_temporal_data as ( select billing_event_id, valid_from, update_date, delete_date, invoice_date, transaction_type, transaction_reference_id, parent_billing_event_id, bank_trace_id, broker_id, product_id, disbursement_billing_event_id, action, from_account_id, to_account_id, end_user_account_id, billing_address_id, amount, currency, balance_impacting, --empty value in Athena shows as '', change all '' value to null case when agreement_id = '' then null else agreement_id end as agreement_id, invoice_id, payment_due_date, usage_period_start_date, usage_period_end_date, buyer_transaction_reference_id, row_num from ( select billing_event_id, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, delete_date, from_iso8601_timestamp(invoice_date) as invoice_date, transaction_type, transaction_reference_id, parent_billing_event_id, -- casting in case data was imported as number cast(bank_trace_id as varchar) as bank_trace_id, broker_id, product_id, disbursement_billing_event_id, action, from_account_id, to_account_id, end_user_account_id, billing_address_id, -- casting in case data was imported as varchar cast(amount as decimal(38,6)) as amount, currency, balance_impacting, agreement_id, invoice_id, case when payment_due_date is null or payment_due_date = '' then null else from_iso8601_timestamp(payment_due_date) end as payment_due_date, from_iso8601_timestamp(usage_period_start_date) as usage_period_start_date, from_iso8601_timestamp(usage_period_end_date) as usage_period_end_date, buyer_transaction_reference_id, row_number() over (partition by billing_event_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from billingeventfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), -- Here we select the account_id of the current seller (We identify this by looking for the to_account_id related to revenue transactions). -- We will use it later to distinguish own agreements from agreements generated by channel partners. seller_account as ( select from_account_id as seller_account_id from billing_events_with_uni_temporal_data bill where -- Assumption here is only seller will pay listing fee. As of 12/21/2021, there are cases that Channel partner have 0 listing fee for CPPO, so the amount could be 0. bill.transaction_type like 'AWS_REV_SHARE' and amount <= 0 and action = 'INVOICED' group by -- from_account_id is always the same for all those "listing fee" transactions == the seller of record himself. -- If this view returns more than 1 record, the overall query will fail (on purpose). Please contact AWS Marketplace if this happens. from_account_id ), billing_event_with_business_flags as ( select bl.billing_event_id, bl.end_user_account_id, bl.agreement_id, aggrement.proposer_account_id, aggrement.offer_id, aggrement.acceptor_account_id, case -- For AWS and BALANCE_ADJUSTMENT, the billing event feed will show the "AWS Marketplace" account as the -- receiver of the funds and the seller as the payer. We are not interested in this information here. -- Null values will be ignored by the `max` aggregation function. when bl.transaction_type like 'AWS%' then null -- For BALANCE_ADJUSTMENT, payer is seller themselves when bl.invoice_id is null then bl.to_account_id -- We get the payer of the invoice from *any* transaction type that is not AWS and not BALANCE_ADJUSTMENT (because they are the same for a given end user + agreement + product). else bl.from_account_id end as payer_account_id, bl.product_id, bl.action, bl.transaction_type, bl.parent_billing_event_id, bl.disbursement_billing_event_id, bl.amount, bl.currency, bl.balance_impacting, bl.invoice_date, bl.payment_due_date, bl.usage_period_start_date, bl.usage_period_end_date, bl.invoice_id, bl.billing_address_id, bl.transaction_reference_id, bl.buyer_transaction_reference_id, case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.bank_trace_id end as bank_trace_id, case when disbursement.bank_trace_id = 'EMEA_MP_TEST_TRACE_ID' then null else disbursement.invoice_date end as disbursement_date, disbursement.billing_event_id as disbursement_id, -- We will use disbursement_id_or_invoiced as part of the PK, so it cannot be null: coalesce( --empty value in Athena shows as '', change all '' value to null case when disbursement.billing_event_id = '' then null else disbursement.billing_event_id end, '') as disbursement_id_or_invoiced, bl.broker_id, case when bl.invoice_id is null /* transaction_type = 'BALANCE_ADJUSTMENT' */ then (select seller_account_id from seller_account) ||':'|| cast(bl.invoice_date as varchar) else bl.buyer_transaction_reference_id ||'-'|| case when bl.agreement_id is null or bl.agreement_id = '' then ' ' else bl.agreement_id end ||'-'|| case when bl.end_user_account_id is null or bl.end_user_account_id = '' then ' ' else bl.end_user_account_id end ||'-'|| coalesce(cast(bl.usage_period_start_date as varchar),' ') ||'-'|| coalesce(cast(bl.usage_period_end_date as varchar),' ') end as internal_buyer_line_item_id, bl.buyer_transaction_reference_id <> bl.transaction_reference_id as is_seller_invoice, case when bl.transaction_type = 'SELLER_REV_SHARE' and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog, case when bl.transaction_type in('SELLER_REV_SHARE_CREDIT', 'SELLER_REV_SHARE_REFUND') and (select seller_account_id from seller_account) <> bl.to_account_id then true else false end as is_cog_refund, --TODO: replace below logic once we can create a logic the identify reseller/manufacturer without depending on agreement feed case when aggrement.proposer_account_id <> (select seller_account_id from seller_account) then true else false end as is_manufacturer_view_of_reseller from billing_events_with_uni_temporal_data as bl left join billing_events_with_uni_temporal_data as disbursement on disbursement.transaction_type like 'DISBURSEMENT%' and disbursement.action = 'DISBURSED' and disbursement.transaction_type IN ('DISBURSEMENT', 'DISBURSEMENT_FAILURE') and bl.disbursement_billing_event_id = disbursement.billing_event_id left join agreements_with_history as aggrement on bl.agreement_id = aggrement.agreement_id and bl.invoice_date >= aggrement.valid_from_adjusted and bl.invoice_date < aggrement.valid_to left join accounts_with_history awh on bl.to_account_id = awh.account_id and bl.invoice_date >= awh.valid_from and bl.invoice_date < awh.valid_to where bl.transaction_type not like 'DISBURSEMENT%' and (bl.agreement_id is null or bl.agreement_id = '' or aggrement.agreement_id is not null) ), -- listagg function in athena does not support partitioning, grouping here and then joining to the main query seller_invoice_list as ( select internal_buyer_line_item_id, listagg(case when not is_seller_invoice then null else invoice_id end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_id_or_null, listagg(case when not is_seller_invoice then null else cast(invoice_date as varchar) end,',') within group (order by case when not is_seller_invoice then null else cast(invoice_date as varchar) end) as seller_invoice_date_or_null from ( -- listagg function in athena does not support ordering by another field when distinct is used, -- here we first select distinct invoices and then do the listagg order by invoice_date select distinct internal_buyer_line_item_id, is_seller_invoice, invoice_id, invoice_date from billing_event_with_business_flags) distinct_invoices group by internal_buyer_line_item_id order by internal_buyer_line_item_id ), billing_event_with_categorized_transaction as ( -- Use the flags that were created in the previous transformation in more calculated columns: -- NOTE: This transformation has no joins and no window functions select billing_event_id, end_user_account_id, agreement_id, proposer_account_id, offer_id, acceptor_account_id, case when is_cog or is_cog_refund then null else payer_account_id end as payer_account_id, product_id, action, transaction_type, parent_billing_event_id, disbursement_billing_event_id, amount, currency, balance_impacting, invoice_date, payment_due_date, usage_period_start_date, usage_period_end_date, invoice_id, billing_address_id, transaction_reference_id, buyer_transaction_reference_id, bank_trace_id, disbursement_date, disbursement_id, disbursement_id_or_invoiced, broker_id, bl.internal_buyer_line_item_id, is_seller_invoice, is_cog, is_cog_refund, is_manufacturer_view_of_reseller, -- Buyer/seller columns: case when is_seller_invoice then null else invoice_id end as buyer_invoice_id_or_null, seller_invoices.seller_invoice_id_or_null, case when is_seller_invoice then null else invoice_date end as buyer_invoice_date_or_null, seller_invoices.seller_invoice_date_or_null, -- Categorized amounts by transaction type: case when transaction_type = 'SELLER_REV_SHARE' and not is_cog then amount else 0 end as gross_revenue, case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and not is_cog_refund then amount else 0 end as gross_refund, case when transaction_type = 'SELLER_REV_SHARE' and is_cog then amount else 0 end as cogs, case when transaction_type in ('SELLER_REV_SHARE_REFUND','SELLER_REV_SHARE_CREDIT') and is_cog_refund then amount else 0 end as cogs_refund, case when transaction_type = 'AWS_REV_SHARE' then amount else 0 end as aws_rev_share, case when transaction_type in ('AWS_REV_SHARE_REFUND','AWS_REV_SHARE_CREDIT') then amount else 0 end as aws_refund_share, case when transaction_type = 'AWS_TAX_SHARE' and not is_seller_invoice then amount else 0 end as aws_tax_share, -- AWS tax share from _buyer_ invoice case when transaction_type = 'AWS_TAX_SHARE' and is_seller_invoice then amount else 0 end as aws_tax_share_listing_fee, -- AWS tax share from _seller_ invoice case when transaction_type = 'AWS_TAX_SHARE_REFUND' and not is_seller_invoice then amount else 0 end as aws_tax_share_refund, case when transaction_type = 'AWS_TAX_SHARE_REFUND' and is_seller_invoice then amount else 0 end as aws_tax_share_refund_listing_fee, case when transaction_type = 'SELLER_TAX_SHARE' then amount else 0 end as seller_tax_share, case when transaction_type = 'SELLER_TAX_SHARE_REFUND' then amount else 0 end as seller_tax_share_refund, case when transaction_type = 'BALANCE_ADJUSTMENT' then amount else 0 end as balance_adjustment, case when transaction_type = 'SELLER_REV_SHARE_CREDIT' then amount else 0 end as seller_rev_credit, case when transaction_type = 'AWS_REV_SHARE_CREDIT' then amount else 0 end as aws_ref_fee_credit from billing_event_with_business_flags as bl left join seller_invoice_list as seller_invoices on bl.internal_buyer_line_item_id = seller_invoices.internal_buyer_line_item_id ), line_items_aggregated as ( -- This transformation has the only "group by" in all of these transformations. -- NOTE: This transformation has no joins and no window functions select internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, product_id, broker_id, currency, agreement_id, proposer_account_id, acceptor_account_id, max(payer_account_id) as payer_account_id, offer_id, end_user_account_id, usage_period_start_date, usage_period_end_date, max(payment_due_date) payment_due_date, buyer_transaction_reference_id, bank_trace_id, disbursement_date, max(billing_address_id) as billing_address_id, -- Buyer/seller columns: max(buyer_invoice_id_or_null) as buyer_invoice_id, max(seller_invoice_id_or_null) as seller_invoice_id, max(buyer_invoice_date_or_null) as buyer_invoice_date, max(seller_invoice_date_or_null) as seller_invoice_date, -- Categorized amounts by transaction type: -- When disbursement_id_or_invoiced = '', these are invoiced amounts -- When disbursement_id_or_invoiced <> ''' these are disbursed amounts for _this_ specific disbursement_id sum(gross_revenue) as gross_revenue_this_disbursement_id_or_invoiced, sum(gross_refund) as gross_refund_this_disbursement_id_or_invoiced, sum(cogs) as cogs_this_disbursement_id_or_invoiced, sum(cogs_refund) as cogs_refund_this_disbursement_id_or_invoiced, sum(aws_rev_share) as aws_rev_share_this_disbursement_id_or_invoiced, sum(aws_refund_share) as aws_refund_share_this_disbursement_id_or_invoiced, sum(aws_tax_share) as aws_tax_share_this_disbursement_id_or_invoiced, sum(aws_tax_share_listing_fee) as aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, sum(aws_tax_share_refund) as aws_tax_share_refund_this_disbursement_id_or_invoiced, sum(aws_tax_share_refund_listing_fee) as aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, sum(seller_tax_share) as seller_tax_share_this_disbursement_id_or_invoiced, sum(seller_tax_share_refund) as seller_tax_share_refund_this_disbursement_id_or_invoiced, sum(balance_adjustment) as balance_adjustment_this_disbursement_id_or_invoiced, sum(seller_rev_credit) as seller_rev_credit_this_disbursement_id_or_invoiced, sum(aws_ref_fee_credit) as aws_ref_fee_credit_this_disbursement_id_or_invoiced from billing_event_with_categorized_transaction as billing_categorized group by internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, broker_id, -- The following columns are included the in group by but they are intentionally omitted from the PK. -- These columns should have the _same_ values for each record in the PK. product_id, currency, agreement_id, proposer_account_id, acceptor_account_id, offer_id, end_user_account_id, usage_period_start_date, usage_period_end_date, buyer_transaction_reference_id, bank_trace_id, disbursement_date ), -- listagg function in athena does not support partitioning, grouping here and then joining to the main query disbursement_list as ( select internal_buyer_line_item_id, listagg(cast(disbursement_date as varchar),',') within group (order by cast(disbursement_date as varchar)) as disbursement_date_list, listagg(bank_trace_id,',') within group (order by cast(disbursement_date as varchar)) as disburse_bank_trace_id_list from ( -- listagg function in athena does not support ordering by another field when distinct is used, -- here we first select distinct bank_trace_ids and then do the listagg order by disbursement_date select distinct internal_buyer_line_item_id, disbursement_date, bank_trace_id from billing_event_with_business_flags) distinct_disbursements group by internal_buyer_line_item_id order by internal_buyer_line_item_id ), line_items_with_window_functions as ( --add flag next step compare gross_revenue and gross_revenue_disbursed or gross_refund and gross_refund_disbursed select line_item.internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, product_id, broker_id, currency, agreement_id, proposer_account_id, acceptor_account_id, -- when there's aws_rev_Share adjustment/refund to a seller_rev_share invoice, it can happen that for the same aws_rev_share invoice_id, there are multiple disbursement events, -- using windows function to map payer_account_id of seller_rev_share to all corresponding aws_rev_Share max(payer_account_id) over (partition by line_item.internal_buyer_line_item_id) as payer_account_id, offer_id, end_user_account_id, usage_period_start_date, usage_period_end_date, payment_due_date, bank_trace_id, disbursement_date, billing_address_id, -- Buyer/seller columns: max(buyer_invoice_id) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_id, seller_invoice_id, max(buyer_invoice_date) over (partition by line_item.internal_buyer_line_item_id) as buyer_invoice_date, seller_invoice_date, -- When disbursement_id_or_invoiced = ''', these are actually invoiced amounts -- When disbursement_id_or_invoiced <> ''' these are disbursed amounts for _this_ specific disbursement_id gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, -- IMPORTANT: All window functions partitioned by internal_buyer_line_item_id: -- Invoiced amounts, categorized by transaction type: sum(case when disbursement_id_or_invoiced = ''' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end)over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_invoiced, sum(case when disbursement_id_or_invoiced = ''' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_invoiced, sum(case when disbursement_id_or_invoiced = ''' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_invoiced, sum(case when disbursement_id_or_invoiced = ''' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_invoiced, sum(case when disbursement_id_or_invoiced = ''' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_invoiced, sum(case when disbursement_id_or_invoiced = ''' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_invoiced, sum(case when disbursement_id_or_invoiced = ''' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_invoiced, sum(case when disbursement_id_or_invoiced = ''' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_invoiced, sum(case when disbursement_id_or_invoiced = ''' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_invoiced, -- Total disbursed amounts (for all disbursement_id values), categorized by transaction type: sum(case when disbursement_id_or_invoiced '<> ''' then gross_revenue_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_revenue_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then gross_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as gross_refund_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then cogs_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then cogs_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as cogs_refund_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_rev_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_rev_share_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_refund_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_refund_share_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_tax_share_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_listing_fee_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_tax_share_refund_listing_fee_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then seller_tax_share_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then seller_tax_share_refund_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_tax_share_refund_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then balance_adjustment_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as balance_adjustment_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then seller_rev_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as seller_rev_credit_disbursed, sum(case when disbursement_id_or_invoiced '<> ''' then aws_ref_fee_credit_this_disbursement_id_or_invoiced else cast(0 as decimal(38,6)) end) over (partition by line_item.internal_buyer_line_item_id) as aws_ref_fee_credit_disbursed, -- aggregate multiple disbursement max(disbursement_date) over (partition by line_item.internal_buyer_line_item_id) as last_disbursement_date, first_value(case when disbursement_id_or_invoiced = ''' then null else disbursement_id_or_invoiced end) over(partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disbursement_id, first_value(bank_trace_id) over (partition by line_item.internal_buyer_line_item_id order by coalesce(disbursement_date,cast('1900-01-01' as timestamp)) desc rows between unbounded preceding and unbounded following) as last_disburse_bank_trace_id, disb_list.disbursement_date_list, disb_list.disburse_bank_trace_id_list from line_items_aggregated as line_item left join disbursement_list disb_list on line_item.internal_buyer_line_item_id = disb_list.internal_buyer_line_item_id ), cppo_offer_id as ( select -- Channel partner offers do not exist in offertargetfeed_v1 table (as per legal requirement), causing cppo offer be defined as 'Public' in previous step, we will convert them back to 'Private' in next step offer_id from offers_with_uni_temporal_data where -- seller_account_id is null means the ISV owns the offer seller_account_id is not null and seller_account_id '<> (select seller_account_id from seller_account) group by offer_id ), line_items_with_window_functions_enrich_offer_product_address as ( select internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, line.product_id, legacy_product.legacy_id as legacy_product_id, products.title as product_title, line.broker_id, line.currency, line.end_user_account_id, acc_enduser.encrypted_account_id as end_user_encrypted_account_id, acc_enduser.aws_account_id as end_user_aws_account_id, acc_payer.aws_account_id as payer_aws_account_id, acc_payer.encrypted_account_id payer_encrypted_account_id, line.agreement_id, agreement.agreement_revision, line.proposer_account_id, case when offer.offer_id like 'aiqoffer-%' then null else agreement.start_date end as Agreement_Start_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.end_date end as Agreement_End_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.acceptance_date end as Agreement_Acceptance_Date, case when offer.offer_id like 'aiqoffer-%' then null else agreement.valid_from end as agreement_updated_date, case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_start_date end as Usage_Period_Start_Date, case when offer.offer_id like 'aiqoffer-%' then null else line.usage_period_end_date end as Usage_Period_End_Date, line.acceptor_account_id, acc_subscriber.aws_account_id as subscriber_aws_account_id, acc_subscriber.encrypted_account_id as subscriber_encrypted_account_id, offer.offer_id, case when offer.offer_id in ( select distinct offer_id from cppo_offer_id) then 'Private' else offer.offer_target end as offer_target, offer.name offer_name, offer.opportunity_name offer_opportunity_name, offer.opportunity_description offer_opportunity_description, offer.opportunity_id, payment_due_date, line.bank_trace_id, disbursement_date, billing_address_id, buyer_invoice_id, seller_invoice_id, buyer_invoice_date, seller_invoice_date, gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, gross_revenue_invoiced, gross_refund_invoiced, cogs_invoiced, cogs_refund_invoiced, aws_rev_share_invoiced, aws_refund_share_invoiced, aws_tax_share_invoiced, aws_tax_share_listing_fee_invoiced, aws_tax_share_refund_invoiced, aws_tax_share_refund_listing_fee_invoiced, seller_tax_share_invoiced, seller_tax_share_refund_invoiced, balance_adjustment_invoiced, seller_rev_credit_invoiced, aws_ref_fee_credit_invoiced, gross_revenue_disbursed, gross_refund_disbursed, cogs_disbursed, cogs_refund_disbursed, aws_rev_share_disbursed, aws_refund_share_disbursed, aws_tax_share_disbursed, aws_tax_share_listing_fee_disbursed, aws_tax_share_refund_disbursed, aws_tax_share_refund_listing_fee_disbursed, seller_tax_share_disbursed, seller_tax_share_refund_disbursed, balance_adjustment_disbursed, seller_rev_credit_disbursed, aws_ref_fee_credit_disbursed, last_disbursement_date, last_disbursement_id, last_disburse_bank_trace_id, disbursement_date_list, disburse_bank_trace_id_list, products.product_code, acc_products.aws_account_id as manufacturer_aws_account_id, products.manufacturer_account_id, --add subscriber and payer addressID, payer address preference order: tax address>billing address>mailing address, subscriber address preference order: tax address> mailing address coalesce ( --empty value in Athena shows as '', change all '' value to null in order to follow the preference order logic above case when acc_subscriber.tax_address_id ='' then null else acc_subscriber.tax_address_id end, case when acc_subscriber.mailing_address_id = '' then null else acc_subscriber.mailing_address_id end) as subscriber_address_id, coalesce ( case when acc_payer.tax_address_id = '' then null else acc_payer.tax_address_id end, case when line.billing_address_id = '' then null else line.billing_address_id end, case when acc_payer.mailing_address_id = '' then null else acc_payer.mailing_address_id end) as payer_address_id, coalesce ( case when acc_enduser.tax_address_id = '' then null else acc_enduser.tax_address_id end, case when line.billing_address_id = '' then null else line.billing_address_id end, case when acc_enduser.mailing_address_id = '' then null else acc_enduser.mailing_address_id end) as end_user_address_id from line_items_with_window_functions as line left join agreements_with_history as agreement on line.agreement_id = agreement.agreement_id and line.buyer_invoice_date >= agreement.valid_from_adjusted and line.buyer_invoice_date < agreement.valid_to left join offers_with_history_with_target_type as offer on line.offer_id = offer.offer_id and line.buyer_invoice_date >= offer.valid_from and line.buyer_invoice_date < offer.valid_to left join products_with_history as products on line.product_id = products.product_id and line.buyer_invoice_date >= products.valid_from_adjusted and line.buyer_invoice_date < products.valid_to left join legacy_products as legacy_product on line.product_id = legacy_product.new_id left join accounts_with_history_with_company_name as acc_payer on line.payer_account_id = acc_payer.account_id and line.buyer_invoice_date >= acc_payer.valid_from and line.buyer_invoice_date < acc_payer.valid_to left join accounts_with_history_with_company_name as acc_enduser on line.end_user_account_id = acc_enduser.account_id and line.buyer_invoice_date >= acc_enduser.valid_from and line.buyer_invoice_date < acc_enduser.valid_to left join accounts_with_history_with_company_name as acc_subscriber on line.acceptor_account_id = acc_subscriber.account_id and line.buyer_invoice_date >= acc_subscriber.valid_from and line.buyer_invoice_date < acc_subscriber.valid_to left join accounts_with_history_with_company_name as acc_products on products.manufacturer_account_id = acc_products.account_id and line.buyer_invoice_date >= acc_products.valid_from and line.buyer_invoice_date < acc_products.valid_to ), line_items_with_window_functions_enrich_offer_product_address_name as ( select line.internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, product_id, legacy_product_id, product_title, broker_id, currency, end_user_address_id, end_user_account_id, end_user_encrypted_account_id, end_user_aws_account_id, add_enduser.company_name end_user_company_name, add_enduser.email_domain end_user_email_domain, add_enduser.city end_user_city, add_enduser.state_or_region end_user_state, add_enduser.country_code end_user_country, add_enduser.postal_code end_user_postal_code, payer_aws_account_id, payer_encrypted_account_id, payer_address_id, add_payer.company_name payer_company_name, add_payer.email_domain payer_email_domain, add_payer.city payer_city, add_payer.state_or_region payer_state, add_payer.country_code payer_country, add_payer.postal_code payer_postal_code, agreement_id, agreement_revision, agreement_start_date, agreement_end_date, agreement_acceptance_date, agreement_updated_date, case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end as reseller_aws_account_id, case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end as reseller_company_name, usage_period_start_date, usage_period_end_date, proposer_account_id, acc_proposer.aws_account_id as proposer_aws_account_id, acceptor_account_id, subscriber_aws_account_id, subscriber_encrypted_account_id, subscriber_address_id, add_subscriber.company_name subscriber_company_name, add_subscriber.email_domain subscriber_email_domain, add_subscriber.city subscriber_city, add_subscriber.state_or_region subscriber_state, add_subscriber.country_code subscriber_country, add_subscriber.postal_code subscriber_postal_code, offer_id, offer_target, offer_name, offer_opportunity_name, offer_opportunity_description, opportunity_id, payment_due_date, bank_trace_id, disbursement_date, billing_address_id, max(buyer_invoice_id)as buyer_invoice_id, max(seller_invoice_id)as seller_invoice_id, max(buyer_invoice_date)as buyer_invoice_date, max(seller_invoice_date)as seller_invoice_date, gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, (gross_revenue_this_disbursement_id_or_invoiced + gross_refund_this_disbursement_id_or_invoiced + aws_rev_share_this_disbursement_id_or_invoiced + aws_refund_share_this_disbursement_id_or_invoiced + seller_tax_share_this_disbursement_id_or_invoiced + seller_tax_share_refund_this_disbursement_id_or_invoiced + cogs_this_disbursement_id_or_invoiced + cogs_refund_this_disbursement_id_or_invoiced + aws_tax_share_listing_fee_this_disbursement_id_or_invoiced + aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced) as seller_net_revenue_this_disbursement_id_or_invoiced, gross_revenue_invoiced, gross_refund_invoiced, cogs_invoiced, cogs_refund_invoiced, aws_rev_share_invoiced, aws_refund_share_invoiced, aws_tax_share_invoiced, aws_tax_share_listing_fee_invoiced, aws_tax_share_refund_invoiced, aws_tax_share_refund_listing_fee_invoiced, seller_tax_share_invoiced, seller_tax_share_refund_invoiced, balance_adjustment_invoiced, seller_rev_credit_invoiced, aws_ref_fee_credit_invoiced, gross_revenue_disbursed, gross_refund_disbursed, cogs_disbursed, cogs_refund_disbursed, aws_rev_share_disbursed, aws_refund_share_disbursed, aws_tax_share_disbursed, aws_tax_share_listing_fee_disbursed, aws_tax_share_refund_disbursed, aws_tax_share_refund_listing_fee_disbursed, seller_tax_share_disbursed, seller_tax_share_refund_disbursed, balance_adjustment_disbursed, seller_rev_credit_disbursed, aws_ref_fee_credit_disbursed, (gross_revenue_invoiced + gross_revenue_disbursed) as uncollected_gross_revenue, -- net revenue = gross revenue - listing fee - tax - cogs (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced) as seller_net_revenue, (gross_revenue_invoiced + gross_refund_invoiced + aws_rev_share_invoiced + aws_refund_share_invoiced + seller_tax_share_invoiced + seller_tax_share_refund_invoiced + cogs_invoiced + cogs_refund_invoiced + aws_tax_share_listing_fee_invoiced + aws_tax_share_refund_listing_fee_invoiced + gross_revenue_disbursed + gross_refund_disbursed + aws_rev_share_disbursed + aws_refund_share_disbursed + seller_tax_share_disbursed + seller_tax_share_refund_disbursed + cogs_disbursed + cogs_refund_disbursed + aws_tax_share_listing_fee_disbursed + aws_tax_share_refund_listing_fee_disbursed) as uncollected_seller_net_revenue, last_disbursement_date, last_disbursement_id, last_disburse_bank_trace_id, disbursement_date_list, disburse_bank_trace_id_list, product_code, manufacturer_aws_account_id, manufacturer_account_id, acc_manu.mailing_company_name as manufacturer_company_name, cast(null as varchar) as AR_Period, case when ( (gross_revenue_invoiced '<>0 and gross_revenue_invoiced = -1 * gross_revenue_disbursed) or (gross_refund_invoiced '<> 0 and gross_refund_invoiced = -1 * gross_refund_disbursed) or (balance_adjustment_invoiced '<> 0 and balance_adjustment_invoiced = -1 * balance_adjustment_disbursed) or (seller_tax_share_refund_invoiced '<> 0 and seller_tax_share_refund_invoiced = -1 * seller_tax_share_refund_disbursed) or (gross_revenue_invoiced = 0 and gross_refund_invoiced = 0 and balance_adjustment_invoiced = 0 and seller_tax_share_refund_invoiced = 0 and last_disbursement_id is not null)) then 'Yes' when gross_revenue_disbursed = 0 and gross_refund_disbursed = 0 and balance_adjustment_disbursed = 0 and seller_tax_share_disbursed = 0 and seller_tax_share_refund_disbursed = 0 then 'No' else 'Partial' end as Disbursement_Flag from line_items_with_window_functions_enrich_offer_product_address as line left join accounts_with_history_with_company_name as acc_manu on line.manufacturer_account_id = acc_manu.account_id and line.buyer_invoice_date >= acc_manu.valid_from_adjusted and line.buyer_invoice_date <= acc_manu.valid_to left join accounts_with_history_with_company_name as acc_proposer on line.proposer_account_id = acc_proposer.account_id and line.buyer_invoice_date >= acc_proposer.valid_from and line.buyer_invoice_date < acc_proposer.valid_to left join address_with_latest_revision as add_payer on line.payer_address_id = add_payer.address_id left join address_with_latest_revision as add_subscriber on line.subscriber_address_id = add_subscriber.address_id left join address_with_latest_revision as add_enduser on line.end_user_address_id = add_enduser.address_id group by line.internal_buyer_line_item_id, disbursement_id, disbursement_id_or_invoiced, product_id, legacy_product_id, product_title, broker_id, currency, end_user_address_id, end_user_account_id, end_user_encrypted_account_id, end_user_aws_account_id, add_enduser.company_name, add_enduser.email_domain, add_enduser.city, add_enduser.state_or_region, add_enduser.country_code, add_enduser.postal_code, payer_aws_account_id, payer_encrypted_account_id, payer_address_id, add_payer.company_name, add_payer.email_domain, add_payer.city, add_payer.state_or_region, add_payer.country_code, add_payer.postal_code, agreement_id, agreement_revision, case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.aws_account_id end, case when proposer_account_id = (select seller_account_id from seller_account) then null else acc_proposer.mailing_company_name end, agreement_start_date, agreement_end_date, agreement_acceptance_date, agreement_updated_date, usage_period_start_date, usage_period_end_date, acceptor_account_id, subscriber_aws_account_id, subscriber_encrypted_account_id, subscriber_address_id, add_subscriber.company_name, add_subscriber.email_domain, add_subscriber.city, add_subscriber.state_or_region, add_subscriber.country_code, add_subscriber.postal_code, offer_id, offer_target, offer_name, offer_opportunity_name, offer_opportunity_description, opportunity_id, payment_due_date, bank_trace_id, disbursement_date, billing_address_id, gross_revenue_this_disbursement_id_or_invoiced, gross_refund_this_disbursement_id_or_invoiced, cogs_this_disbursement_id_or_invoiced, cogs_refund_this_disbursement_id_or_invoiced, aws_rev_share_this_disbursement_id_or_invoiced, aws_refund_share_this_disbursement_id_or_invoiced, aws_tax_share_this_disbursement_id_or_invoiced, aws_tax_share_listing_fee_this_disbursement_id_or_invoiced, aws_tax_share_refund_this_disbursement_id_or_invoiced, aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced, seller_tax_share_this_disbursement_id_or_invoiced, seller_tax_share_refund_this_disbursement_id_or_invoiced, balance_adjustment_this_disbursement_id_or_invoiced, seller_rev_credit_this_disbursement_id_or_invoiced, aws_ref_fee_credit_this_disbursement_id_or_invoiced, gross_revenue_invoiced, gross_refund_invoiced, cogs_invoiced, cogs_refund_invoiced, aws_rev_share_invoiced, aws_refund_share_invoiced, aws_tax_share_invoiced, aws_tax_share_listing_fee_invoiced, aws_tax_share_refund_invoiced, aws_tax_share_refund_listing_fee_invoiced, seller_tax_share_invoiced, seller_tax_share_refund_invoiced, balance_adjustment_invoiced, seller_rev_credit_invoiced, aws_ref_fee_credit_invoiced, gross_revenue_disbursed, gross_refund_disbursed, cogs_disbursed, cogs_refund_disbursed, aws_rev_share_disbursed, aws_refund_share_disbursed, aws_tax_share_disbursed, aws_tax_share_listing_fee_disbursed, aws_tax_share_refund_disbursed, aws_tax_share_refund_listing_fee_disbursed, seller_tax_share_disbursed, seller_tax_share_refund_disbursed, balance_adjustment_disbursed, seller_rev_credit_disbursed, aws_ref_fee_credit_disbursed, last_disbursement_date, last_disbursement_id, last_disburse_bank_trace_id, disbursement_date_list, disburse_bank_trace_id_list, product_code, manufacturer_aws_account_id, manufacturer_account_id, acc_manu.mailing_company_name, proposer_account_id, acc_proposer.aws_account_id ), invoiced_not_disbursed as( select --we will filter on rownum =1 in next step, -- means internal_buyer_line_item_id, there's only ' record, no disbursement_id linked *, max(case when disbursement_id_or_invoiced = ''' then 1 else 2 end) over (partition by internal_buyer_line_item_id) rownum from line_items_with_window_functions_enrich_offer_product_address_name as line_items ), collections_and_disbursements as ( select ------------------ -- Invoice Info -- ------------------ buyer_invoice_date as Invoice_Date, Payment_Due_Date as Payment_Due_Date, concat( 'Net ', case when abs(date_diff('Day', Payment_due_date, buyer_invoice_date))>180 then '180+' else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar) end, ' days' ) as payment_terms, buyer_invoice_id as Invoice_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when seller_invoice_id = '' then null else seller_invoice_id end, 'Not applicable') as Listing_Fee_Invoice_ID, --------------------------- --End user Information -- --------------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when End_User_Company_Name = '' then null else End_User_Company_Name end, 'Not available') as End_User_Company_Name, End_User_AWS_Account_ID, End_User_Encrypted_Account_ID, End_User_Email_Domain, End_User_City, End_User_State as End_User_State_or_Region, End_User_Country, End_User_Postal_Code, End_User_Address_ID, --------------------------- --Subscriber Information -- --------------------------- case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided' else Subscriber_Company_Name end as Subscriber_Company_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_AWS_Account_ID end as Subscriber_AWS_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_Encrypted_Account_ID end as Subscriber_Encrypted_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided' else Subscriber_Email_Domain end as Subscriber_Email_Domain, case when Agreement_id is null or Agreement_ID = '' then 'Not available' when Subscriber_City is null or Subscriber_City = '' then 'Not provided' else Subscriber_City end as Subscriber_City, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_State is null or Subscriber_State = '' then 'Not provided' else Subscriber_State end as Subscriber_State_or_Region, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided' else Subscriber_Country end as Subscriber_Country, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided' else Subscriber_Postal_Code end as Subscriber_Postal_Code, case when Agreement_ID is null or Agreement_ID = '' then 'Not available' when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided' else Subscriber_Address_ID end as Subscriber_Address_ID, ---------------------- -- Procurement Info -- ---------------------- -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id. coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_Title = '' then null else Product_Title end, 'Not provided') as Product_Title, -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id. case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable' else Offer_Name end as Offer_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_ID end as Offer_ID, -- offer visibility at time of invoice., case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_Target end as Offer_Visibility, coalesce( --empty value in Athena shows as '', change all '' value to null case when Agreement_ID = '' then null else Agreement_ID end, 'Not available') as Agreement_ID, Agreement_Start_Date, Agreement_Acceptance_Date, Agreement_End_Date, Usage_Period_Start_Date, Usage_Period_End_Date, ----------------------- -- Disbursement Info -- ----------------------- case when Disbursement_Flag = 'Yes' then 'Disbursed' when Disbursement_Flag = 'No' then 'Not Disbursed' else 'Other' end as Disbursement_Status, last_disbursement_date as disbursement_date, case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end as Disbursement_Time, case when Disbursement_Flag = 'No' then 'Not applicable' when bank_trace_id is null or bank_trace_id = '' then 'Not available' else bank_trace_id end as disburse_bank_trace_id, -------------- -- Revenues -- -------------- -- We are rounding the sums using 2 decimal precision -- Note that the rounding method might differ between SQL implementations. -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output -- and the legacy report round(-1 * gross_revenue_this_disbursement_id_or_invoiced,2) as Gross_Revenue, round(-1 * gross_refund_this_disbursement_id_or_invoiced,2) as Gross_Refund, round(-1 * aws_rev_share_this_disbursement_id_or_invoiced,2) as Listing_Fee, round(-1 * aws_refund_share_this_disbursement_id_or_invoiced,2) as Listing_Fee_Refund, truncate( case when gross_revenue_this_disbursement_id_or_invoiced != 0 then abs(aws_rev_share_this_disbursement_id_or_invoiced/gross_revenue_this_disbursement_id_or_invoiced) when gross_refund_this_disbursement_id_or_invoiced != 0 then abs(aws_refund_share_this_disbursement_id_or_invoiced/gross_refund_this_disbursement_id_or_invoiced) else 0 end ,4) as Listing_Fee_Percentage, round(-1 * seller_tax_share_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share, round(-1 * seller_tax_share_refund_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share_Refund, round(-1 * aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Listing_Fee, round(-1 * aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee, round(-1 * cogs_this_disbursement_id_or_invoiced,2) as Wholesale_cost, round(-1 * cogs_refund_this_disbursement_id_or_invoiced,2) as Wholesale_cost_Refund, round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) as Seller_Net_Revenue, currency as Currency, substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID, broker_id as AWS_seller_of_record, ----------------- -- Resale info -- ----------------- case when Opportunity_Id is null or Opportunity_Id = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Opportunity_Id end as Resale_authorization_ID, case when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Name end as Resale_authorization_name, case when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Description end as Resale_authorization_description, case when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '') and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available' when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (opportunity_id is null or opportunity_id = '') then 'Not applicable' when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_Company_Name end as Reseller_Company_Name, case when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable' when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_AWS_Account_ID end as Reseller_AWS_Account_ID, ----------------------- -- Payer Information -- ----------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when Payer_Company_Name = '' then null else Payer_Company_Name end, 'Not available') as Payer_Company_Name, Payer_AWS_Account_ID, -- "Customer AWS Account Number" in legacy report Payer_Encrypted_Account_ID, Payer_Email_Domain, Payer_City, Payer_State as Payer_State_or_Region, Payer_Country, Payer_Postal_Code, Payer_Address_ID, --------------------- -- ISV Information -- --------------------- manufacturer_aws_account_id as ISV_Account_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end, 'Not available') as ISV_Company_Name, --------------------- -- Products info -- --------------------- Legacy_Product_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_ID = '' then null else Product_ID end, 'Not provided') as Product_ID, -- this is to get the legacy product id https://sim.amazon.com/issues/MP-INSIGHTS-2561 Product_Code, case when Disbursement_Flag = 'Yes' then round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Disbursed_Net_Revenue, case when Disbursement_Flag = 'No' then round(-1 * seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Undisbursed_Net_Revenue, case when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <= 0 then 'Not due' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=30 then '1-30 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=60 then '31-60 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=90 then '61-90 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=120 then '91-120 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end >=121 then '121+ days late' else null end as Disbursement_Period from line_items_with_window_functions_enrich_offer_product_address_name as line where disbursement_id_or_invoiced != ''' union select ------------------ -- Invoice Info -- ------------------ buyer_invoice_date as Invoice_Date, Payment_Due_Date as Payment_Due_Date, concat( 'Net ', case when abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) >180 then '180+' else cast(abs(date_diff('Day', Payment_due_date, buyer_invoice_date)) as varchar) end, ' days' ) as payment_terms, buyer_invoice_id as Invoice_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when seller_invoice_id = '' then null else seller_invoice_id end, 'Not applicable') as Listing_Fee_Invoice_ID, --------------------------- --End user Information -- --------------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when End_User_Company_Name = '' then null else End_User_Company_Name end, 'Not available') as End_User_Company_Name, End_User_AWS_Account_ID, End_User_Encrypted_Account_ID, End_User_Email_Domain, End_User_City, End_User_State as End_User_State_or_Region, End_User_Country, End_User_Postal_Code, End_User_Address_ID, --------------------------- --Subscriber Information -- --------------------------- case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Company_Name is null or Subscriber_Company_Name = '' then 'Not provided' else Subscriber_Company_Name end as Subscriber_Company_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_AWS_Account_ID end as Subscriber_AWS_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Subscriber_Encrypted_Account_ID end as Subscriber_Encrypted_Account_ID, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Email_Domain is null or Subscriber_Email_Domain = '' then 'Not provided' else Subscriber_Email_Domain end as Subscriber_Email_Domain, case when Agreement_id is null or Agreement_ID = '' then 'Not available' when Subscriber_City is null or Subscriber_City = '' then 'Not provided' else Subscriber_City end as Subscriber_City, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_State is null or Subscriber_State = '' then 'Not provided' else Subscriber_State end as Subscriber_State_or_Region, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Country is null or Subscriber_Country = '' then 'Not provided' else Subscriber_Country end as Subscriber_Country, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when Subscriber_Postal_Code is null or Subscriber_Postal_Code = '' then 'Not provided' else Subscriber_Postal_Code end as Subscriber_Postal_Code, case when Agreement_ID is null or Agreement_ID = '' then 'Not available' when Subscriber_Address_ID is null or Subscriber_Address_ID = '' then 'Not provided' else Subscriber_Address_ID end as Subscriber_Address_ID, ---------------------- -- Procurement Info -- ---------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_Title = '' then null else Product_Title end, 'Not provided') as Product_Title, -- offer name at time of invoice. It is possible that the name changes over time therefore there may be multiple offer names mapped to a single offer id. case when Agreement_Id is null or Agreement_ID = '' then 'Not available' when (Offer_Name is null or Offer_Name = '') and Offer_Target = 'Public' then 'Not applicable' else Offer_Name end as Offer_Name, case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_ID end as Offer_ID, -- offer visibility at time of invoice., case when Agreement_Id is null or Agreement_ID = '' then 'Not available' else Offer_Target end as Offer_Visibility, coalesce( --empty value in Athena shows as '', change all '' value to null case when Agreement_ID = '' then null else Agreement_ID end, 'Not available') as Agreement_ID, --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_Start_Date end as Agreement_Start_Date, --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_End_Date end as Agreement_End_Date, --case when Agreement_Id is null or Agreement_Id = '' then cast(null as timestamp) else Agreement_Acceptance_Date end as Agreement_Acceptance_Date, Agreement_Start_Date, Agreement_Acceptance_Date, Agreement_End_Date, Usage_Period_Start_Date, Usage_Period_End_Date, ----------------------- -- Disbursement Info -- ----------------------- case when Disbursement_Flag = 'Yes' then 'Disbursed' when Disbursement_Flag = 'No' then 'Not Disbursed' else 'Other' end as Disbursement_Status, last_disbursement_date as disbursement_date, case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end as Disbursement_Time, case when Disbursement_Flag = 'No' then 'Not applicable' when bank_trace_id is null or bank_trace_id = '' then 'Not available' else bank_trace_id end as disburse_bank_trace_id, -------------- -- Revenues -- -------------- -- We are rounding the sums using 2 decimal precision -- Note that the rounding method might differ between SQL implementations. -- The monthly revenue report is using RoundingMode.HALF_UP. This might create tiny discrepancies between this SQL output -- and the legacy report round(gross_revenue_this_disbursement_id_or_invoiced,2) as Gross_Revenue, round(gross_refund_this_disbursement_id_or_invoiced,2) as Gross_Refund, round(aws_rev_share_this_disbursement_id_or_invoiced,2) as Listing_Fee, round(aws_refund_share_this_disbursement_id_or_invoiced,2) as Listing_Fee_Refund, truncate( case when gross_revenue_this_disbursement_id_or_invoiced != 0 then abs(aws_rev_share_this_disbursement_id_or_invoiced/gross_revenue_this_disbursement_id_or_invoiced) when gross_refund_this_disbursement_id_or_invoiced != 0 then abs(aws_refund_share_this_disbursement_id_or_invoiced/gross_refund_this_disbursement_id_or_invoiced) else 0 end ,4) as Listing_Fee_Percentage, round(seller_tax_share_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share, round(seller_tax_share_refund_this_disbursement_id_or_invoiced,2) as Seller_Tax_Share_Refund, round(aws_tax_share_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Listing_Fee, round(aws_tax_share_refund_listing_fee_this_disbursement_id_or_invoiced,2) as AWS_Tax_Share_Refund_Listing_Fee, round(cogs_this_disbursement_id_or_invoiced,2) as Wholesale_cost, round(cogs_refund_this_disbursement_id_or_invoiced,2) as Wholesale_cost_Refund, round(seller_net_revenue_this_disbursement_id_or_invoiced,2) as Seller_Net_Revenue, currency as Currency, substring(internal_buyer_line_item_id,1,strpos(internal_buyer_line_item_id,'-')-1) as Transaction_Reference_ID, broker_id as AWS_seller_of_record, ----------------- -- Resale info -- ----------------- case when Opportunity_Id is null or Opportunity_Id = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Opportunity_Id end as Resale_authorization_ID, case when Offer_Opportunity_Name is null or Offer_Opportunity_Name = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Name end as Resale_authorization_name, case when Offer_Opportunity_Description is null or Offer_Opportunity_Description = '' then case when Offer_Target = 'Public' then 'Not applicable' when (Offer_Target is null or Offer_Target = '') and (Agreement_Id is not null and Agreement_Id != '') then 'Not applicable' else null end else Offer_Opportunity_Description end as Resale_authorization_description, case when (Reseller_AWS_Account_ID is not null and Reseller_AWS_Account_ID != '') and (Reseller_Company_Name is null or Reseller_Company_Name = '') then 'Not available' when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (opportunity_id is null or opportunity_id = '') then 'Not applicable' when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_Company_Name end as Reseller_Company_Name, case when (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') and (Opportunity_Id is null or Opportunity_Id = '') then 'Not applicable' when (select seller_account_id from seller_account) '<> manufacturer_aws_account_id and (Reseller_AWS_Account_ID is null or Reseller_AWS_Account_ID = '') then 'Not applicable' else Reseller_AWS_Account_ID end as Reseller_AWS_Account_ID, ----------------------- -- Payer Information -- ----------------------- coalesce( --empty value in Athena shows as '', change all '' value to null case when Payer_Company_Name = '' then null else Payer_Company_Name end, 'Not available') as Payer_Company_Name, Payer_AWS_Account_ID, -- "Customer AWS Account Number" in legacy report Payer_Encrypted_Account_ID, Payer_Email_Domain, Payer_City, Payer_State as Payer_State_or_Region, Payer_Country, Payer_Postal_Code, Payer_Address_ID, --------------------- -- ISV Information -- --------------------- manufacturer_aws_account_id as ISV_Account_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Manufacturer_Company_Name = '' then null else Manufacturer_Company_Name end, 'Not available') as ISV_Company_Name, --------------------- -- Products info -- --------------------- -- product title at time of invoice. It is possible that the title changes over time and therefore there may be multiple product titles mapped to a single product id. Legacy_Product_ID, coalesce( --empty value in Athena shows as '', change all '' value to null case when Product_ID = '' then null else Product_ID end, 'Not provided') as Product_ID, -- this is to get the legacy product id https://sim.amazon.com/issues/MP-INSIGHTS-2561 Product_Code, case when Disbursement_Flag = 'Yes' then round(seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Disbursed_Net_Revenue, case when Disbursement_Flag = 'No' then round(seller_net_revenue_this_disbursement_id_or_invoiced,2) else 0 end as Undisbursed_Net_Revenue, case when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <= 0 then 'Not due' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=30 then '1-30 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=60 then '31-60 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=90 then '61-90 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end <=120 then '91-120 days late' when case when Disbursement_Flag = 'Yes' then date_diff('DAY', date_trunc('DAY',payment_due_date), date_trunc('DAY',last_disbursement_date)) else null end >=121 then '121+ days late' else null end as Disbursement_Period from invoiced_not_disbursed where rownum = 1 ) select * from collections_and_disbursements where payment_due_date >= date_add('DAY', -90, current_date) --where payment_due_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp) --where disbursement_date >= date_add('DAY', -90, current_date) --where disbursement_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp) ``` ## Taxed invoices To find your taxed invoices, you can run a set of queries like the following example. The queries build on each other to create the **Taxation** report. You can use the example as shown, or customize it for your data and use cases. The comments in the queries explain what the queries do, and how to modify them. ``` -- Taxation report -- General note: When executing this query we are assuming that the data ingested in the database is using -- two time axes (the valid_from column and the update_date column). -- See documentation for more details: https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed.html#data-feed-details -- An account_id has several valid_from dates (each representing a separate revision of the data) -- but because of bi-temporality, an account_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) with accounts_with_uni_temporal_data as ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, from_iso8601_timestamp(valid_from) as valid_from, tax_registration_number from ( select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, delete_date, tax_registration_number, row_number() over (partition by account_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from accountfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), accounts_with_history as ( with accounts_with_history_with_extended_valid_from as ( select account_id, -- sometimes, this columns gets imported as a "bigint" and loses heading 0s -> casting to a char and re-adding heading 0s (if need be) substring('000000000000'||cast(aws_account_id as varchar),-12) as aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name tax_legal_name, -- The start time of account valid_from is extended to '1970-01-01 00:00:00', because: -- ... in tax report transformations, some tax line items with invoice_date cannot -- ... fall into the default valid time range of the associated account CASE WHEN LAG(valid_from) OVER (PARTITION BY account_id ORDER BY valid_from ASC) IS NULL THEN CAST('1970-01-01 00:00:00' as timestamp) ELSE valid_from END AS valid_from from (select * from accounts_with_uni_temporal_data ) as account ) select account_id, aws_account_id, encrypted_account_id, mailing_address_id, tax_address_id, tax_legal_name, valid_from, coalesce( lead(valid_from) over (partition by account_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to from accounts_with_history_with_extended_valid_from ), -- A product_id has several valid_from dates (each representing a product revision), -- but because of bi-temporality, each product_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) products_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(delete_date) as delete_date, product_id, manufacturer_account_id, product_code, title from ( select valid_from, update_date, delete_date, product_id, manufacturer_account_id, product_code, title, row_number() over (partition by product_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from productfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), products_with_history as ( select product_id, title, valid_from, case when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from < cast('2021-04-01' as timestamp) then date_add('Day', -3857, valid_from) -- 3827 is the longest delay between acceptance_date of an agreement and the product -- we are keeping 3857 as a consistency between the offers and products when lag(valid_from) over (partition by product_id order by valid_from asc) is null and valid_from >= cast('2021-04-01' as timestamp) then date_add('Day', -2190, valid_from) --after 2021 for the two offers we need to adjust for 2 more years else valid_from end as valid_from_adjusted, coalesce( lead(valid_from) over (partition by product_id order by valid_from asc), cast('2999-01-01 00:00:00' as timestamp) ) as valid_to, product_code, manufacturer_account_id from products_with_uni_temporal_data ), -- A tax_item_id has several valid_from dates (each representing a product revision), -- but because of bi-temporality, each tax_item_id + valid_from tuple can appear multiple times with a different update_date. -- We are only interested in the most recent tuple (ie, uni-temporal model) tax_items_with_uni_temporal_data as ( select from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, delete_date, cast(tax_item_id as varchar) as tax_item_id, cast(invoice_id as varchar) as invoice_id, cast(line_item_id as varchar) as line_item_id, cast(customer_bill_id as varchar) as customer_bill_id, tax_liable_party, transaction_type_code, product_id, product_tax_code, from_iso8601_timestamp(invoice_date) as invoice_date, taxed_customer_account_id, taxed_customer_country, taxed_customer_state_or_region, taxed_customer_city, taxed_customer_postal_code, tax_location_code_taxed_jurisdiction, tax_type_code, jurisdiction_level, taxed_jurisdiction, display_price_taxability_type, tax_jurisdiction_rate, tax_amount, tax_currency, tax_calculation_reason_code, date_used_for_tax_calculation, customer_exemption_certificate_id, customer_exemption_certificate_id_domain, customer_exemption_certificate_level, customer_exemption_code, customer_exemption_domain, transaction_reference_id from ( select valid_from, update_date, delete_date, tax_item_id, invoice_id, line_item_id, customer_bill_id, tax_liable_party, transaction_type_code, product_id, product_tax_code, invoice_date, taxed_customer_account_id, taxed_customer_country, taxed_customer_state_or_region, taxed_customer_city, taxed_customer_postal_code, tax_location_code_taxed_jurisdiction, tax_type_code, jurisdiction_level, taxed_jurisdiction, display_price_taxability_type, tax_jurisdiction_rate, tax_amount, tax_currency, tax_calculation_reason_code, date_used_for_tax_calculation, customer_exemption_certificate_id, customer_exemption_certificate_id_domain, customer_exemption_certificate_level, customer_exemption_code, customer_exemption_domain, transaction_reference_id, row_number() over (partition by tax_item_id, valid_from order by from_iso8601_timestamp(update_date) desc) as row_num from taxitemfeed_v1 ) where -- keep latest ... row_num = 1 -- ... and remove the soft-deleted one. and (delete_date is null or delete_date = '') ), taxation as ( select tax_items.invoice_id, tax_items.line_item_id, tax_items.customer_bill_id, tax_items.tax_liable_party, tax_items.transaction_type_code, tax_items.product_id, product_tax_item.title as product_title, tax_items.product_tax_code, tax_items.invoice_date, accounts_with_history.aws_account_id as taxed_customer_account_id, tax_items.taxed_customer_country, tax_items.taxed_customer_state_or_region, tax_items.taxed_customer_city, tax_items.taxed_customer_postal_code, tax_items.tax_type_code as tax_type, tax_items.jurisdiction_level, tax_items.taxed_jurisdiction, tax_items.display_price_taxability_type, tax_items.tax_jurisdiction_rate, tax_items.tax_amount, tax_items.tax_currency, tax_items.tax_calculation_reason_code, tax_items.date_used_for_tax_calculation, coalesce( --empty value in Athena shows as '', change all '' value to null case when tax_items.customer_exemption_certificate_id = '' then null else tax_items.customer_exemption_certificate_id end, 'Not exempt') customer_exemption_certificate_id, coalesce(--empty value in Athena shows as '', change all '' value to null case when tax_items.customer_exemption_certificate_id_domain = '' then null else tax_items.customer_exemption_certificate_id_domain end, 'Not exempt') customer_exemption_certificate_id_domain, coalesce(--empty value in Athena shows as '', change all '' value to null case when tax_items.customer_exemption_certificate_level = '' then null else tax_items.customer_exemption_certificate_level end, 'Not exempt') customer_exemption_certificate_level, coalesce(--empty value in Athena shows as '', change all '' value to null case when tax_items.customer_exemption_code = '' then null else tax_items.customer_exemption_code end, 'Not exempt') customer_exemption_code, tax_items.transaction_reference_id from tax_items_with_uni_temporal_data as tax_items left join products_with_history as product_tax_item on tax_items.product_id = product_tax_item.product_id and tax_items.invoice_date >= product_tax_item.valid_from_adjusted and tax_items.invoice_date < product_tax_item.valid_to left join accounts_with_history as accounts_with_history on tax_items.taxed_customer_account_id = accounts_with_history.account_id and tax_items.invoice_date >= accounts_with_history.valid_from and tax_items.invoice_date < accounts_with_history.valid_to ) select * from taxation where invoice_date >= date_add('DAY', -90, current_date) --where invoice_date between cast('2023-01-01' as timestamp) and cast('2024-12-31' as timestamp) ``` ## Disbursements by product To find the amounts disbursed by product, you can run a set of queries like the following. These example queries build upon each other to create the final list of product details with disbursements. It also shows how to get the product information at a specific point in time. You can use this sample as shown, or customize it for your data and use cases. Comments in the queries explain what the queries do, and how to modify them. **Note** When running this query, we assume that the data is ingested using two time axes, the `valid_from` and `update` columns. For more information about the axes, see [Storage and structure of AWS Marketplace data feeds](data-feed-details.md). ``` -- Get all the products and keep the latest product_id, valid_from tuple with products_with_uni_temporal_data as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY product_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from productfeed_v1 ) where -- A product_id can appear multiple times with the same -- valid_from date but with a different update_date column, -- making it effectively bi-temporal. By only taking the most -- recent tuple, we are converting to a uni-temporal model. row_num = 1 ), -- Gets the latest revision of a product -- A product can have multiple revisions where some of the -- columns, like the title, can change. -- For the purpose of the disbursement report, we want -- to get the latest revision of a product products_with_latest_version as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY from_iso8601_timestamp(valid_from) desc) as row_num_latest_version from products_with_uni_temporal_data ) where row_num_latest_version = 1 ), -- Get all the accounts and keep the latest account_id, valid_from tuple accounts_with_uni_temporal_data as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY account_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from accountfeed_v1 ) where -- An account_id can appear multiple times with the same -- valid_from date but with a different update_date column, -- making it effectively bi-temporal. By only taking the most -- recent tuple, we are converting to a uni-temporal model. row_num = 1 ), -- Gets the latest revision of an account -- An account can have multiple revisions where some of the -- columns, like the mailing_address_id, can change. -- For the purpose of the disbursement report, we want -- to get the latest revision of a product accounts_with_latest_version as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY from_iso8601_timestamp(valid_from) desc) as row_num_latest_version from accounts_with_uni_temporal_data ) where row_num_latest_version = 1 ), -- Get all the billing events and keep the -- latest billing_event_id, valid_from tuple: billing_events_with_uni_temporal_data as ( select * from ( select billing_event_id, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(invoice_date) as invoice_date, transaction_type, transaction_reference_id, product_id, disbursement_billing_event_id, action, from_account_id, to_account_id, end_user_account_id, CAST(amount as decimal(20, 10)) invoice_amount, bank_trace_id, ROW_NUMBER() OVER (PARTITION BY billing_event_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from billingeventfeed_v1 ) where row_num = 1 ), -- Get all the disbursements -- The billing events data is immutable. -- It is not required to use time windows based on the -- valid_from column to get the most recent billing event disbursement_events as ( select billing_events_raw.billing_event_id as disbursement_id, billing_events_raw.invoice_date as disbursement_date, billing_events_raw.bank_trace_id from billing_events_with_uni_temporal_data billing_events_raw where -- Only interested in disbursements, so filter out -- non-disbursements by selecting transaction type -- to be DISBURSEMENT: billing_events_raw.transaction_type = 'DISBURSEMENT' -- Select a time period, you can adjust the dates -- below if need be. For billing events use the -- invoice date as the point in time of the -- disbursement being initiated: and billing_events_raw.invoice_date >= from_iso8601_timestamp('2020-10-01T00:00:00Z') and billing_events_raw.invoice_date < from_iso8601_timestamp('2020-11-01T00:00:00Z') ), -- Get the invoices along with the line items that -- are part of the above filtered disbursements disbursed_line_items as ( select line_items.transaction_reference_id, line_items.product_id, line_items.transaction_type, (case -- Get the payer of the invoice from any -- transaction type that is not AWS and -- not BALANCE_ADJUSTMENT. -- For AWS and BALANCE_ADJUSTMENT, the billing -- event feed will show the "AWS Marketplace" -- account as the receiver of the funds and the -- seller as the payer. Filter those out. when line_items.transaction_type not like '%AWS%' and transaction_type not like 'BALANCE_ADJUSTMENT' then line_items.from_account_id end) as payer_account_id, line_items.end_user_account_id, invoice_amount, disbursements.disbursement_date, disbursements.disbursement_id, disbursements.bank_trace_id from billing_events_with_uni_temporal_data line_items -- Each disbursed line item is linked to the parent -- disbursement via the disbursement_billing_event_id join disbursement_events disbursements on disbursements.disbursement_id = line_items.disbursement_billing_event_id where -- we are interested only in the invoice line -- items that are DISBURSED line_items.action = 'DISBURSED' ), -- An invoice can contain multiple line items -- Create a pivot table to calculate the different -- amounts that are part of an invoice. -- The new row is aggregated at -- transaction_reference_id - end_user_account_id level invoice_amounts_aggregated as ( select transaction_reference_id, product_id, -- a given disbursement id should have the -- same disbursement_date max(disbursement_date) as disbursement_date, -- Build a pivot table in order to provide all the -- data related to a transaction in a single row. -- Note that the amounts are negated. This is because -- when an invoice is generated, we give you the -- positive amounts and the disbursement event -- negates the amounts sum(case when transaction_type = 'SELLER_REV_SHARE' then -invoice_amount else 0 end) as seller_rev_share, sum(case when transaction_type = 'AWS_REV_SHARE' then -invoice_amount else 0 end) as aws_rev_share, sum(case when transaction_type = 'SELLER_REV_SHARE_REFUND' then -invoice_amount else 0 end) as seller_rev_refund, sum(case when transaction_type = 'AWS_REV_SHARE_REFUND' then -invoice_amount else 0 end) as aws_rev_refund, sum(case when transaction_type = 'SELLER_REV_SHARE_CREDIT' then -invoice_amount else 0 end) as seller_rev_credit, sum(case when transaction_type = 'AWS_REV_SHARE_CREDIT' then -invoice_amount else 0 end) as aws_rev_credit, sum(case when transaction_type = 'SELLER_TAX_SHARE' then -invoice_amount else 0 end) as seller_tax_share, sum(case when transaction_type = 'SELLER_TAX_SHARE_REFUND' then -invoice_amount else 0 end) as seller_tax_refund, -- This is the account that pays the invoice: max(payer_account_id) as payer_account_id, -- This is the account that subscribed to the product: end_user_account_id as customer_account_id, bank_trace_id from disbursed_line_items group by transaction_reference_id, product_id, disbursement_id, -- There might be a different end-user for the same -- transaction reference id. Distributed licenses -- is an example end_user_account_id, bank_trace_id ), disbursed_amount_by_product as ( select products.title as ProductTitle, products.product_code as ProductCode, -- We are rounding the sums using 2 decimal precision -- Note that the rounding method might differ -- between SQL implementations. -- The disbursement seller report is using -- RoundingMode.HALF_UP. This might create -- discrepancies between this SQL output -- and the disbursement seller report round(invoice_amounts.seller_rev_share, 2) as SellerRev, round(invoice_amounts.aws_rev_share, 2) as AWSRefFee, round(invoice_amounts.seller_rev_refund, 2) as SellerRevRefund, round(invoice_amounts.aws_rev_refund, 2) as AWSRefFeeRefund, round(invoice_amounts.seller_rev_credit, 2) as SellerRevCredit, round(invoice_amounts.aws_rev_credit, 2) as AWSRefFeeCredit, ( round(invoice_amounts.seller_rev_share, 2) + round(invoice_amounts.aws_rev_share, 2) + round(invoice_amounts.seller_rev_refund, 2) + round(invoice_amounts.aws_rev_refund, 2) + round(invoice_amounts.seller_rev_credit, 2) + round(invoice_amounts.aws_rev_credit, 2) ) as NetAmount, invoice_amounts.transaction_reference_id as TransactionReferenceID, round(invoice_amounts.seller_tax_share, 2) as SellerSalesTax, round(invoice_amounts.seller_tax_refund, 2) as SellerSalesTaxRefund, payer_info.aws_account_id as PayerAwsAccountId, customer_info.aws_account_id as EndCustomerAwsAccountId, invoice_amounts.disbursement_date as DisbursementDate, invoice_amounts.bank_trace_id as BankTraceId from invoice_amounts_aggregated invoice_amounts join products_with_latest_version products on products.product_id = invoice_amounts.product_id left join accounts_with_latest_version payer_info on payer_info.account_id = invoice_amounts.payer_account_id left join accounts_with_latest_version customer_info on customer_info.account_id = invoice_amounts.customer_account_id ) select * from disbursed_amount_by_product; ``` ## Sales compensation report To find the billed revenue by customer, you can run a set of queries like the following. These example queries build on each other to create the final list of customer details with the total amount billed to each customer for usage of your software. You can use the queries as shown, or customize them for your data and use cases. Comments in the queries explain what the queries do, and how to modify them. **Note** When running this query, we are assuming that the data ingested is using two time axes (the `valid_from` and `update` columns). For more information, see [Storage and structure of AWS Marketplace data feeds](data-feed-details.md). ``` -- Gets all the products and keeps the latest product_id, -- valid_from tuple. with products_with_uni_temporal_data as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY product_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from productfeed_v1 ) where -- A product_id can appear multiple times with the same -- valid_from date but with a different update_date column, -- making it effectively bi-temporal. By only taking the most -- recent tuple, we are converting to a uni-temporal model. row_num = 1 ), -- Gets the latest revision of a product -- A product can have multiple revisions where some of the -- columns, like the title, can change. -- For the purpose of the sales compensation report, we want -- to get the latest revision of a product products_with_latest_revision as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY from_iso8601_timestamp(valid_from) desc) as row_num_latest_revision from products_with_uni_temporal_data ) where row_num_latest_revision = 1 ), -- Gets all the addresses and keeps the latest address_id, -- aws_account_id, and valid_from combination. -- We're transitioning from a bi-temporal data model to an -- uni-temporal data_model piifeed_with_uni_temporal_data as ( select * from ( select *, ROW_NUMBER() OVER ( PARTITION BY address_id, aws_account_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from piifeed ) where -- An address_id can appear multiple times with the same -- valid_from date but with a different update_date column. -- We are only interested in the most recent. row_num = 1 ), -- Gets the latest revision of an address. -- An address_id can have multiple revisions where some of -- the columns can change. -- For the purpose of the sales compensation report, we want to -- get the latest revision of an address + account_id pair. pii_with_latest_revision as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY address_id, aws_account_id ORDER BY from_iso8601_timestamp(valid_from) desc) as row_num_latest_revision from piifeed_with_uni_temporal_data ) where row_num_latest_revision = 1 ), -- Gets all the accounts and keeps the latest -- account_id, valid_from tuple. -- We're transitioning from a bi-temporal data -- model to an uni-temporal data_model. accounts_with_uni_temporal_data as ( select * from ( select *, ROW_NUMBER() OVER (PARTITION BY account_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from accountfeed_v1 ) where -- An account_id can appear multiple times with the same -- valid_from date but with a different update_date column. -- We are only interested in the most recent tuple. row_num = 1 ), -- Gets all the historical dates for an account -- An account can have multiple revisions where some of the -- columns like the mailing_address_id can change. accounts_with_history as ( select *, -- This interval's begin_date case when -- First record for a given account_id lag(valid_from, 1) over (partition by account_id order by from_iso8601_timestamp(valid_from) asc) is null then -- 'force' begin_date a bit earlier because of different -- data propagation times. We'll subtract one day as one -- hour is not sufficient from_iso8601_timestamp(valid_from) - INTERVAL '1' DAY else -- not the first line -> return the real date from_iso8601_timestamp(valid_from) end as begin_date, -- This interval's end date. COALESCE( LEAD(from_iso8601_timestamp(valid_from), 1) OVER (partition by account_id ORDER BY from_iso8601_timestamp(valid_from)), from_iso8601_timestamp('9999-01-01T00:00:00Z') ) as end_date from accounts_with_uni_temporal_data ), -- Gets all the billing events and keeps the latest -- billing_event_id, valid_from tuple. -- We're transitioning from a bi-temporal data -- model to an uni-temporal data_model. billing_events_with_uni_temporal_data as ( select * from ( select billing_event_id, from_iso8601_timestamp(valid_from) as valid_from, from_iso8601_timestamp(update_date) as update_date, from_iso8601_timestamp(invoice_date) as invoice_date, transaction_type, transaction_reference_id, product_id, disbursement_billing_event_id, action, currency, from_account_id, to_account_id, end_user_account_id, -- convert an empty billing address to null. This will -- later be used in a COALESCE call case when billing_address_id <> '' then billing_address_id else null end as billing_address_id, CAST(amount as decimal(20, 10)) invoice_amount, ROW_NUMBER() OVER (PARTITION BY billing_event_id, valid_from ORDER BY from_iso8601_timestamp(update_date) desc) as row_num from billingeventfeed_v1 where -- The Sales Compensation Report does not contain BALANCE -- ADJUSTMENTS, so we filter them out here transaction_type <> 'BALANCE_ADJUSTMENT' -- Keep only the transactions that will affect any -- future disbursed amounts. and balance_impacting = '1' ) where row_num = 1 ), -- Gets the billing address for all DISBURSED invoices. This -- will be the address of the payer when the invoice was paid. -- NOTE: For legal reasons, for CPPO transactions, the -- manufacturer will not see the payer's billing address id billing_addresses_for_disbursed_invoices as ( select billing_events_raw.transaction_reference_id, billing_events_raw.billing_address_id, billing_events_raw.from_account_id from billing_events_with_uni_temporal_data billing_events_raw where -- the disbursed items will contain the billing address id billing_events_raw.action = 'DISBURSED' -- we only want to get the billing address id for the -- transaction line items where the seller is the receiver -- of the amount and billing_events_raw.transaction_type like 'SELLER_%' group by billing_events_raw.transaction_reference_id, billing_events_raw.billing_address_id, billing_events_raw.from_account_id ), -- An invoice can contain multiple line items. -- We create a pivot table to calculate the different amounts -- that are part of an invoice. -- The new row is aggregated at -- transaction_reference_id - end_user_account_id level invoiced_and_forgiven_transactions as ( select transaction_reference_id, product_id, -- A transaction will have the same invoice date for all -- of its line items (transaction types) max(invoice_date) as invoice_date, -- A transaction will have the same billing_address_id -- for all of its line items. Remember that the billing event -- is uni temporal and we retrieved only the latest valid_from item max(billing_address_id) as billing_address_id, -- A transaction will have the same currency for all -- of its line items max(currency) as currency, -- We're building a pivot table in order to provide all the -- data related to a transaction in a single row sum(case when transaction_type = 'SELLER_REV_SHARE' then invoice_amount else 0 end) as seller_rev_share, sum(case when transaction_type = 'AWS_REV_SHARE' then invoice_amount else 0 end) as aws_rev_share, sum(case when transaction_type = 'SELLER_REV_SHARE_REFUND' then invoice_amount else 0 end) as seller_rev_refund, sum(case when transaction_type = 'AWS_REV_SHARE_REFUND' then invoice_amount else 0 end) as aws_rev_refund, sum(case when transaction_type = 'SELLER_REV_SHARE_CREDIT' then invoice_amount else 0 end) as seller_rev_credit, sum(case when transaction_type = 'AWS_REV_SHARE_CREDIT' then invoice_amount else 0 end) as aws_rev_credit, sum(case when transaction_type = 'SELLER_TAX_SHARE' then invoice_amount else 0 end) as seller_tax_share, sum(case when transaction_type = 'SELLER_TAX_SHARE_REFUND' then invoice_amount else 0 end) as seller_tax_refund, -- this is the account that pays the invoice. max(case -- Get the payer of the invoice from any transaction type -- that is not AWS and not BALANCE_ADJUSTMENT. -- For AWS and BALANCE_ADJUSTMENT, the billing event feed -- will show the "AWS Marketplace" account as the -- receiver of the funds and the seller as the payer. We -- are not interested in this information here. when transaction_type not like '%AWS%' and transaction_type not like 'BALANCE_ADJUSTMENT' then from_account_id end) as payer_account_id, -- this is the account that subscribed to your product end_user_account_id as customer_account_id from billing_events_with_uni_temporal_data where -- Get invoiced or forgiven items. Disbursements are -- not part of the sales compensation report action in ('INVOICED', 'FORGIVEN') group by transaction_reference_id, product_id, -- There might be a different end-user for the same -- transaction reference id. Distributed licenses -- is an example. end_user_account_id ), invoiced_items_with_product_and_billing_address as ( select invoice_amounts.*, products.product_code, products.title, payer_info.aws_account_id as payer_aws_account_id, payer_info.account_id as payer_reference_id, customer_info.aws_account_id as end_user_aws_account_id, ( invoice_amounts.seller_rev_share + invoice_amounts.aws_rev_share + invoice_amounts.seller_rev_refund + invoice_amounts.aws_rev_refund + invoice_amounts.seller_rev_credit + invoice_amounts.aws_rev_credit + invoice_amounts.seller_tax_share + invoice_amounts.seller_tax_refund ) as seller_net_revenue, -- Try to get the billing address from the DISBURSED event -- (if any). If there is no DISBURSEMENT, get the billing -- address from the INVOICED item. If still no billing address, -- then default to getting the mailing address of the payer. coalesce(billing_add.billing_address_id, invoice_amounts.billing_address_id, payer_info.mailing_address_id) as final_billing_address_id from invoiced_and_forgiven_transactions invoice_amounts join products_with_latest_revision products on products.product_id = invoice_amounts.product_id left join accounts_with_history payer_info on payer_info.account_id = invoice_amounts.payer_account_id -- Get the Payer Information at the time of invoice creation and payer_info.begin_date <= invoice_amounts.invoice_date and invoice_amounts.invoice_date < payer_info.end_date left join accounts_with_history customer_info on customer_info.account_id = invoice_amounts.customer_account_id -- Get the End User Information at the time of invoice creation and customer_info.begin_date <= invoice_amounts.invoice_date and invoice_amounts.invoice_date < customer_info.end_date left join billing_addresses_for_disbursed_invoices billing_add on billing_add.transaction_reference_id = invoice_amounts.transaction_reference_id and billing_add.from_account_id = invoice_amounts.payer_account_id ), invoices_with_full_address as ( select payer_aws_account_id as "Customer AWS Account Number", pii_data.country as "Country", pii_data.state_or_region as "State", pii_data.city as "City", pii_data.postal_code as "Zip Code", pii_data.email_domain as "Email Domain", product_code as "Product Code", title as "Product Title", seller_rev_share as "Gross Revenue", aws_rev_share as "AWS Revenue Share", seller_rev_refund as "Gross Refunds", aws_rev_refund as "AWS Refunds Share", seller_net_revenue as "Net Revenue", currency as "Currency", date_format(invoice_date, '%Y-%m')as "AR Period", transaction_reference_id as "Transaction Reference ID", payer_reference_id as "Payer Reference ID", end_user_aws_account_id as "End Customer AWS Account ID" from invoiced_items_with_product_and_billing_address invoice_amounts left join pii_with_latest_revision pii_data on pii_data.aws_account_id = invoice_amounts.payer_aws_account_id and pii_data.address_id = invoice_amounts.final_billing_address_id -- Filter out FORGIVEN and Field Demonstration Pricing transactions where seller_net_revenue <> 0 ) select * from invoices_with_full_address; ``` # Data feeds AWS Marketplace provides a number of data feeds to help sellers collect and analyze information about your product sales. Data feeds are available to all registered AWS Marketplace sellers. Since data feeds are generated within a day, they contain the most current data available. The following sections describe each data feed. **Topics** + [ # Account data feed ](data-feed-account.md) + [ # Address data feed ](data-feed-address.md) + [ # Agreements data feed ](data-feed-agreements.md) + [ # Agreement Charge data feed ](data-feed-agreement-charge.md) + [ # Agreement Term data feed ](data-feed-agreement-term.md) + [ # Billing event data feed ](data-feed-billing-event.md) + [ # Daily Usage data feed ](data-feed-daily-usage.md) + [ # Legacy mapping data feed ](data-feed-legacy-mapping.md) + [ # Offer data feed ](data-feed-offer.md) + [ # Offer product data feed ](data-feed-offer-product.md) + [ # Offer target data feed ](data-feed-offer-target.md) + [ # Product data feed ](data-feed-product.md) + [ # Tax item data feed ](data-feed-tax-item.md) # Account data feed This data feed provides information about all the accounts you interact with: your own, any channel partners you work with, buyers, payers, and all taxed accounts. Account data is immutable, and it is not associated with a version number. Changes to fields are appended, so this data feed may have several rows with the same `account_id` and different `valid_from` values. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). The account data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | account\$1id | The globally unique identifier (GUID) of the account. Can be used to join to fields in the `Product`, `Offer_Target`, `Billing_Event`, and `Tax_Item` data feeds. See those data feeds for information about the fields that can be used to join. | | aws\$1account\$1id | The AWS account number of the seller's AWS account, which is unique by AWS partition. | | encrypted\$1account\$1id | The unique, encrypted ID for an individual buyer of your application. The value for encrypted\$1account\$1id is used by the AWS Marketplace Metering Service, for example, as the value for CustomerIdentifier that is returned by the [https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html](https://docs.aws.amazon.com/marketplacemetering/latest/APIReference/API_ResolveCustomer.html) action. | | mailing\$1address\$1id | The mailing address reference for this account. | | tax\$1address\$1id | The tax address reference for this account. | | tax\$1registration\$1number | For non-US accounts, the tax registration number for this account. | | tax\$1legal\$1name | For non-US accounts, the legal company name. This is the name used on tax invoices. | ## Example of account data feed The following shows an example of the account data feed. For readability, the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | account\$1id | aws\$1account\$1id | encrypted\$1account\$1id | mailing\$1address\$1id | tax\$1address\$1id | tax\$1registration\$1number | tax\$1legal\$1name | | --- | --- | --- | --- | --- | --- | --- | | xk0CSmiAm6PQ4QqEog9iiaochIzuPlkMfba7a1oDlZ | 444456660000 | Zf7oMzheGWpH | 25o3k46eN6eViOfFiiqtxwX8e3kaOiPalUiofjyFa3 | | | | | 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | 555567679999 | 373vuQUqmQ8v | 5oJ6vTjSzMrrF2gvh2Vj9HfqiM800MuLEHmyFY5Lr42s8 | 5oJ6vTjSzMrrF2gvh2Vj9HfqiM800MuLEHmyFY5Lr42s8 | SE823935083345 | | | VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | 73739998888 | 8SPxAYmi8MwX | NLUc5UeiMlGFTrDWCoftDPhDUF1oaSd8xgl5QM8Db7 | V5NhBYBiYogwy0WMhndGU4AfMggmuoTC2j7Pm8ZKKNNyT | DE469558025 | | # Address data feed **Important** AWS Marketplace will discontinue the PIIFeed, which is delivered using the [seller delivery data feeds service](https://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-service.html), in December 2023. Use the AddressFeed\$1V1 data feed for your address data needs. If you have questions or require support, contact the [AWS Marketplace Seller Operations](https://aws.amazon.com/marketplace/management/contact-us/) team. This data feed provides contact information for all the accounts you interact with: your own, any channel partners you work with, buyers, payers, and all taxed accounts. Each time a new transaction occurs, the customer address for the transaction is scanned, and if it's not in your data feed, a new entry is added to your data feed file. Address data is immutable. The address data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | address\$1id | The unique key of the address. Can be used to join from the `Billing_Event` data feed on the `billing_address_id` field, or from the `Account` data feed on the `mailing_address_id` or `tax_address_id` fields. | | email\$1domain | The domain for the email address on file for this account. | | company\$1name | The company name on file for this account. | | country\$1code | The ISO 3166 alpha-2 country code on file for this address. | | state\$1or\$1region | The state or region on file for this address. | | city | The city on file for this address. | | postal\$1code | The postal code on file for this address. | | address\$1line\$11 | The first line of the address on file for this address. | | address\$1line\$12 | The second line of the address on file for this address. | | address\$1line\$13 | The third line of the address on file for this address. | ## Example of address data feed The following shows an example of the address data feed. In the data feed, this information is presented in a single table. For readability, the data is shown in two tables here, and the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | address\$1id | email\$1domain | company\$1name | country\$1code | state\$1or\$1region | city | postal\$1code | | --- | --- | --- | --- | --- | --- | --- | | V5NhBYBiYogwy0WMhndGU4AfMggmuoTC2j7Pm8ZKKNNyT | a.com | Mateo Jackson's Company | DE | | Hamburg | 67568 | | G68xdbkZQDVVHzfBGw6yf5yos0A6NiSVWHmH5ViLjf | b.com | Mary Major's Company | US | OH | Dayton | 57684 | | NLUc5UeiMlGFTrDWCoftDPhDUF1oaSd8xgl5QM8Db7 | c.com | Our Seller | US | NY | New York | 89475 | | address\$1line\$11 | address\$1line\$12 | address\$1line\$13 | | --- | --- | --- | | | | | | | | | | | 19th Floor | | # Agreements data feed This data feed provides information about agreements, which is a contract signed between a proposer (seller of record) and an acceptor (AWS Buyer) to start using a product. This data feed provides information about the agreements for the product offers you have created as the seller of record. The Agreement data feed is refreshed every 24 hours. The following table lists and describes the items in the data feed. | Column | Description | | --- | --- | | valid\$1from | The first date that the value for the primary key is valid for in relation to values for other fields. | | insert\$1date | The date a record was inserted into the data feed. | | update\$1date | The date the record was last updated. | | delete\$1date | This column is always blank. | | Status | The current status of the agreement. Supported statuses include: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-agreements.html) | | estimated\$1agreement\$1value | The total known amount the customer is likely to pay across the lifecycle of the agreement. | | currency\$1code | The currency of the total known amount the customer is likely to pay in across the lifecycle of the agreement. | | agreement\$1id | The unique identifier of the agreement. | | license\$1ids | The license identifiers associated with the agreement, represented as an array. | | proposer\$1account\$1id | The seller that proposed this PurchaseAgreement, represented by the globally unique identifier (GUID) of the seller's account. Can be used to join to the Account data feed. | | acceptor\$1account\$1id | The buyer that accepted this PurchaseAgreement, represented by the globally unique identifier (GUID) of the buyer's account. Can be used to join to the Account data feed. | | offer\$1revision\$1at\$1acceptance | The friendly ID of the offer that corresponds to this agreement. Can be used to join to the Offer and Offer target data feeds. | | offer\$1set\$1id | The identifier for the offer set associated with the offer. | | start\$1time | The date and time when the agreement starts. | | end\$1time | The date and time when the agreement ends. The field is null for pay-as-you-go agreements, which don’t have end dates. | | acceptance\$1time | The date and time the offer was accepted or the agreement was created. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-agreements.html) | | intent | The buyer's intent when the agreement was last modified. | | preceding\$1agreement\$1id | The agreement ID of the previous agreement. | | status\$1reason\$1code | The reason for the agreement status change. | | recipient\$1account\$1id | The account of the seller that is receiving the data in the feeds. Can be used to join to the `Account` data feed on the `account_id` field. | ## Agreements data feed example | valid\$1from | agreement\$1id | proposer\$1account\$1id | acceptor\$1account\$1id | offer\$1id | offer\$1revision\$1at\$1acceptance | offer\$1set\$1id | start\$1time | end\$1time | acceptancet\$1time | intent | preceding\$1agreement\$1id | status | status\$1reason\$1code | estimated\$1agreement\$1value | currency\$1code | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 2024-06-21 20:58:00.0 | agmt-34g544dfgsd5678adsrgwe5t | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 88a3xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | offer-krxxxxxxxxxxx | 1 | offerset-yricpu74oqox2 | 2024-06-21 20:58:00.0 | 2025-06-21 20:58:00.0 | 2024-06-21 20:58:00.0 | NEW | | ACTIVE | | 1,000 | USD | # Agreement Charge data feed This data feed provides an overview about the charge associated with an agreement created by you as the seller of record. This data feed currently provides only charge information for agreements associated with a Payment Schedule term. The Agreement Charge data feed is refreshed every 24 hours. The following table lists and describes the items in the data feed. | Column | Description | | --- | --- | | valid\$1from | The first date that the value for the primary key is valid for in relation to values for other fields. | | insert\$1date | The date a record was inserted into the data feed. | | update\$1date | The date the record was last updated. | | delete\$1date | This column is always blank. | | amount | The amount to be charged. | | currency\$1code | The pricing currency of the payment. | | time | The scheduled charge date for the planned installment. | | agreement\$1id | The unique identifier of the agreement. | | term\$1id | The unique identifier of the term. | ## Agreement Charge data feed example | valid\$1from | insert\$1date | update\$1date | delete\$1date | amount | currency\$1code | time | agreement\$1id | term\$1id | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 2024-12-24 11:31:47.0 | 2025-10-01 01:03:41.0 | 2025-10-01 01:03:41.0 | null | 28440.00 | USD | 2025-11-06 00:00:00.000000 | agmt-3kk39tbw3j6id2vakbp0XXXXX | term-3986e2c7f73768ed4ff7cd8a97b41ac0ae2aa02ada6b68deb9349c8604cXXXXX | # Agreement Term data feed This data feed provides an overview about the agreement term associated with an agreement created by you as the seller of record. The Agreement Term data feed is refreshed every 24 hours. The following table lists and describes the items in the data feed. | Column | Description | | --- | --- | | valid\$1from | The first date that the value for the primary key is valid for in relation to values for other fields. | | insert\$1date | The date a record was inserted into the data feed. | | update\$1date | The date the record was last updated. | | delete\$1date | The date the record was deleted. | | agreement\$1id | The unique identifier of the agreement. | | term\$1id | The unique identifier of the term. | | term\$1type | The type of term associated with the agreement. Refer to https://docs.aws.amazon.com/marketplace/latest/APIReference/API\$1Types\$1AWS\$1Marketplace\$1Agreement\$1Service.html for a complete list of available terms. | | term\$1configuration | The additional parameters specified by the acceptor while accepting the term. This is applicable for ConfigurableUpfrontPricingTerms and RenewalTerms. | ## Agreement Term data feed example | valid\$1from | insert\$1date | update\$1date | delete\$1date | agreement\$1id | term\$1id | term\$1type | term\$1configuration | | --- | --- | --- | --- | --- | --- | --- | --- | | 2024-12-24 11:31:47.0 | 2025-10-01 01:03:41.0 | 2025-10-01 01:03:41.0 | null | agmt-3kk39tbw3j6id2vakbp0XXXXX | term-3986e2c7f73768ed4ff7cd8a97b41ac0ae2aa02ada6b68deb9349c8604cXXXXX | ConfigurableUpfrontPricingTerm | \$1"selectorValue":"P36M","dimensions":[\$1"dimensionKey":"Applications","dimensionValue":1\$1]\$1 | # 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](#data-feeds-billing-event-query-examples) 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\$1event\$1id | An identifier for a billing event. This ID is unique in the seller's environment. | | from\$1account\$1id | 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-account.md) data feed. Can be used to join to the `Account` data feed on the `account_id` field. | | to\$1account\$1id | 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\$1user\$1account\$1id | 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\$1id | The identifier of the product. This is a foreign key to the [product](data-feed-product.md) 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: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) | | transaction\$1type | The type of transaction. For examples, see [Taxing scenarios](#data-feeds-billing-event-tax-examples). Possible values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) | | parent\$1billing\$1event\$1id | 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: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) 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: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) When the value of `broker_id` is `AWS_EUROPE`, this field is null for all remaining scenarios. | | disbursement\$1billing\$1event\$1id | The related disbursement when the value of `action` is `DISBURSED` and one of the following is true: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) In all other scenarios, this value is null. | | amount | The billing event amount. | | currency | The ISO 639 currency code. | | disbursement\$1amount | The disbursement amount. For dual invoicing scenarios, this field contains the disbursement amount. For unified invoicing scenarios, this field is null. | | disbursement\$1currency | The ISO 639 currency code for the disbursement amount. For dual invoicing scenarios, this field contains the disbursement currency code. For unified invoicing scenarios, this field is null. | | balance\$1impacting | 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\$1date | The date the invoice was created. | | payment\$1due\$1date | When the value of `action` is `INVOICED`, the due date for the invoice. | | usage\$1period\$1start\$1date | The start date for the period in the record. | | usage\$1period\$1end\$1date | The end date for the period in the record. | | invoice\$1id | The AWS invoice ID. | | billing\$1address\$1id | 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\$1reference\$1id | An identifier that allows you to cross-reference data from the following reports: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) | | bank\$1trace\$1id | 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\$1id | An identifier of the business entity which facilitated the transaction. Possible values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) | | buyer\$1transaction\$1reference\$1id | 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. | | action\$1date | The date when the transaction event occurred. The specific date depends on the action type: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-billing-event.html) | | recipient\$1account\$1id | The account of the seller that is receiving the data in the feeds. Can be used to join to the `Account` data feed on the `account_id` field. | | offer\$1id | The friendly ID of the offer that is associated with the buyer invoice line item.Can be used to join to the `Offer` data feed on the `offer_id` field. | | buyer\$1invoice\$1date | The invoice date of the associated buyer invoice line item. | | line\$1item\$1id | The invoice line item ID, at disbursement granularity. | | buyer\$1line\$1item\$1id | The buyer invoice line item ID at disbursement granularity. | | charge\$1variant | Indicates what is being bought or sold. | | charge\$1side | Indicates whether the seller is buying or selling the charge variant. | ## 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](https://aws.amazon.com/tax-help/). + He purchased `prod-o4grxfafcxxxx` and was invoiced \$1120.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\$1event\$1id | from\$1account\$1id | to\$1account\$1id | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | | --- | --- | --- | --- | --- | --- | --- | | I0 | 737399998888 | 777788889999 | 737399998888 | prod-o4grxfafcxxxx | INVOICED | SELLER\$1REV\$1SHARE | | I1 | 737399998888 | AWS | 737399998888 | prod-o4grxfafcxxxx | INVOICED | AWS\$1TAX\$1SHARE | | I2 | 777788889999 | 111122223333 | 737399998888 | prod-o4grxfafcxxxx | INVOICED | SELLER\$1REV\$1SHARE | | I3 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | INVOICED | AWS\$1REV\$1SHARE | | parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | | --- | --- | --- | --- | --- | --- | | | | 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\$1event\$1id | from\$1account\$1id | to\$1account\$1id | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | | --- | --- | --- | --- | --- | --- | --- | | I10 | 737399998888 | 777788889999 | 737399998888 | | DISBURSED | SELLER\$1REV\$1SHARE | | I12 | 777788889999 | 111122223333 | 737399998888 | | DISBURSED | SELLER\$1REV\$1SHARE | | I13 | 777788889999 | AWS | 737399998888 | prod-o4grxfafcxxxx | DISBURSED | AWS\$1REV\$1SHARE | | I14 | AWS | 777788889999 | | | DISBURSED | DISBURSEMENT | | parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | | --- | --- | --- | --- | --- | --- | | 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\$1event\$1id | from\$1account\$1id | to\$1account\$1id | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | | --- | --- | --- | --- | --- | --- | --- | | I5 | 777788889999 | 111122223333 | | prod-o4grxfafcxxxx | INVOICED | SELLER\$1REV\$1SHARE | | I6 | 777788889999 | 111122223333 | | prod-o4grxfafcxxxx | INVOICED | SELLER\$1TAX\$1SHARE | | I7 | 111122223333 | AWS | | prod-o4grxfafcxxxx | INVOICED | AWS\$1REV\$1SHARE | | parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | | --- | --- | --- | --- | --- | --- | | | | 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\$1event\$1id | from\$1account\$1id | to\$1account\$1id | end\$1user\$1account\$1id | product\$1id | action | transaction\$1type | | --- | --- | --- | --- | --- | --- | --- | | I30 | 777788889999 | 111122223333 | | prod-o4grxfafcxxxx | DISBURSED | SELLER\$1REV\$1SHARE | | I31 | 777788889999 | 111122223333 | | prod-o4grxfafcxxxx | DISBURSED | SELLER\$1TAX\$1SHARE | | I32 | 111122223333 | AWS | | prod-o4grxfafcxxxx | DISBURSED | AWS\$1REV\$1SHARE | | I33 | AWS | 111122223333 | | | DISBURSED | DISBURSEMENT | | parent\$1billing\$1event\$1id | disbursement\$1billing\$1event\$1id | amount | currency | invoice\$1date | invoice\$1id | | --- | --- | --- | --- | --- | --- | | I5 | I33 | -73.5 | USD | | | | I6 | I33 | -6.5 | USD | | | | I7 | I33 | 7.35 | USD | | | | | | 72.65 | USD | | | ## Example queries As described in [Collecting and analyzing data with data feeds](data-feed-using.md), you can use [ Athena](https://docs.aws.amazon.com/athena/latest/ug/what-is.html) 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; ``` # Daily Usage data feed This data feed provides detailed daily usage information for your products, including customer usage metrics, estimated revenue, and pricing details. The data helps you track how customers are using your products and calculate estimated revenue based on usage patterns. The Daily Usage data feed is refreshed every 24 hours. The following table lists and describes the items in the data feed. | Column | Description | | --- | --- | | valid\$1from | The first date that the value for the primary key is valid for in relation to values for other fields. | | insert\$1date | The date a record was inserted into the data feed. | | update\$1date | The date the record was last updated. | | delete\$1date | The date the record was soft deleted. | | usage\$1feed\$1id | The unique identifier of the usage record. This is a per-seller salted hash that ensures data privacy while maintaining record uniqueness. | | usage\$1date | The customer usage date, without the time component. The time is omitted because usage is aggregated for each day. | | product\$1id | The friendly ID of the product. Can be used to join to the `product_id` fields of the Account, Billing\$1Event, and Offer\$1Product data feeds. | | agreement\$1id | The unique identifier of the agreement. If present, this always starts with `agmnt-`. This field may occasionally be null or not match corresponding invoice line items. | | end\$1user\$1account\$1id | The account that actually used the product, represented by the globally unique identifier (GUID) of the end user's account. Can be used to join to the Account data feed. This is a per-seller salted hash, not the raw AWS account ID. | | payer\$1account\$1id | The account that is expected to pay for usage of the product, represented by the globally unique identifier (GUID) of the payer's account. Can be used to join to the Account data feed. This is a per-seller salted hash, not the raw AWS account ID. | | region | The AWS region where the buyer usage occurred. | | dimension\$1key | The dimension key configured by the seller when publishing the offer. | | usage\$1unit | A classification for the usage units, describing the type of usage measurement. | | usage\$1quantity | The usage value for the AWS service or usage type associated with the billing record. The value is provided with up to 2 decimal places. | | pricing\$1currency | The currency of estimated revenue. | | estimated\$1revenue\$1in\$1pricing\$1currency | The estimated revenue calculated using the `usage_rate_per_unit` and `usage_quantity`. The value is provided with up to 2 decimal places. For ISV views of usage from reseller offers, this needs to be multiplied by the revenue share percentage. | | recipient\$1account\$1id | The account of the seller that is receiving the data in the feeds. | | offer\$1id | The friendly ID of the offer. This will match the offer\$1id of the purchase agreement when available. Can be used to join to the Offer and Offer target data feeds. For consistency, this field is always non-null. | | usage\$1rate\$1per\$1unit\$1in\$1pricing\$1currency | The usage rate per unit that can be multiplied by the `usage_quantity` to verify the `estimated_revenue_in_pricing_currency`. The value is provided with up to 6 decimal places. | | charge\$1item\$1description | The complete charge item description, typically formatted as offer\$1term\$1description\$1region\$1dimension\$1description for usage-based charges. | ## Daily Usage data feed example The following shows an example of the Daily Usage data feed with key columns. For readability, some columns are not shown. | usage\$1date | product\$1id | agreement\$1id | region | dimension\$1key | usage\$1unit | usage\$1quantity | pricing\$1currency | estimated\$1revenue\$1in\$1pricing\$1currency | offer\$1id | usage\$1rate\$1per\$1unit\$1in\$1pricing\$1currency | charge\$1item\$1description | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 2025-01-15 | prod-abcd1234efgh5678 | agmnt-wxyz9876abcd5432 | us-east-1 | USE1\$1InputTokenCount | units | 24.00 | USD | 12.00 | offer-mnop5432qrst7890 | 0.500000 | AWS Marketplace software usage\$1us-east-1\$1Million Input Tokens | | 2025-01-15 | prod-ijkl9876mnop1234 | agmnt-stuv5432wxyz9876 | us-west-2 | USE1\$1InputTokenCount | units | 1000.00 | USD | 5.50 | offer-abcd9876efgh5432 | 0.005500 | AWS Marketplace software usage\$1us-west-2\$1API Calls | # Legacy mapping data feed This data feed lists how product IDs and offer IDs map to legacy globally unique identifiers (GUIDs). The legacy GUIDs were used in older reports, and the new IDs are used in data feeds and in AWS Marketplace APIs. This data feed provides information about all products you've created as the seller of record and all products you're authorized to resell. The legacy mapping data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | mapping\$1type | Whether this is a product ID or offer ID. | | legacy\$1id | The legacy ID for this product or offer. | | new\$1id | The friendly ID for this product or offer. This ID is used as the primary key and with all current API actions. | ## Example of legacy mapping data feed The following shows an example of the legacy mapping data feed. For readability, the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | mapping\$1type | legacy\$1id | new\$1id | | --- | --- | --- | | OFFER | 8a806c74-dbd6-403e-9362-bb08f417ff37 | offer-dacpxznflfwin | | PRODUCT | 1368541d-890b-4b6c-9bb9-4a55306ab642 | prod-o4grxfafcxxxy | | OFFER | 558d8382-6b3a-4c75-8345-a627b552f5f1 | offer-gszhmle5npzip | # Offer data feed The offer data feed provides information about all offers that you've created as the seller of record. If a single offer has multiple revisions, all revisions are included in the data feed. When you make an offer revision and the data in an exposed field changes, a new record is created in the data feed for the same primary key (`offer_id` plus `offer_revision`). However, the `valid_from` field has a different value. For more information about the data feed history columns, see [Historization of the data](data-feed-details.md#data-feed-historization). The offer data feed is refreshed every 24 hours, so new data is available daily. The following table provides the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | offer\$1id | The friendly identifier for the offer.Can be used to join to the `offer_id` field of the `Offer_Product` data feed. | | offer\$1revision | The offer revision. This field and the offer\$1id field combine to form the primary key.With `offer_id`, can be used to join to the `offer_id` and `offer_revision` fields of the `Target_Offer` data feed. | | name | The seller-defined name of the offer. | | expiration\$1date | The date and time that the offer expires. | | opportunity\$1name | Any opportunity data linked to this offer. If the offer is bound to an AWS opportunity, this field is populated. | | opportunity\$1description | Any descriptive information linked to this offer. If the offer is bound to an AWS opportunity, this field is populated. | | seller\$1account\$1id | The globally unique identifier (GUID) of the seller’s account. Can be used to join with the account\$1id field in the account data feed. | | opportunity\$1id | An identifier for the opportunity is only populated if a reseller is selling your product. All offers created by different channel partners (or sellers) have the same opportunity\$1id if the product is the same. | | recipient\$1account\$1id | The account of the seller that is receiving the data in the feeds. Can be used to join to the `Account` data feed on the `account_id` field. | ## Example of offer data feed The following shows an example of the offer data feed. For readability, the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | offer\$1id | offer\$1revision | name | expiration\$1date | opportunity\$1name | opportunity\$1description | seller\$1account\$1id | opportunity\$1id | | --- | --- | --- | --- | --- | --- | --- | --- | | offer-dacpxznflfwin | 1 | Enterprise Contract Program Offer | 9999-01-01T00:00:00Z | | | | | | offer-gszhmle5npzip | 1 | Private offer created by seller | 2020-10-31T00:00:00Z | | | | | | offer-hmzhyle8nphlp | 1 | Enterprise Contract Program Offer | 9999-01-01T00:00:00Z | | | | | # Offer product data feed One offer can have several products, and one product can be included in different offers. This data feed lists information about the relationships between offers and products. This data feed provides information about all product offers you've created as the seller of record. When you add or remove a product from an offer, you create an offer revision. The offer product data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. For information about the data feed history columns, see [Historization of the data](data-feed-details.md#data-feed-historization). | Column name | Description | | --- | --- | | offer\$1id | The friendly identifier of this offer.Can be used to join to the `offer_id` field of the `Offer` data feed. | | offer\$1revision | Combines with offer\$1id field to form the foreign key to the offer revision. | | product\$1id | The friendly identifier of the product, this is the foreign key to the product that this offer exposes. Can be used to join to the `product_id` field of the `Product` data feed. | ## Example of Offer product data feed The following shows an example of the Offer product data feed. | offer\$1id | offer\$1revision | product\$1id | | --- | --- | --- | | offer-dacpxznflfwin | 10 | prod-o4grxfafcxxxx | | offer-gszhmle5npzip | 24 | prod-o4grxfafcxxxy | # Offer target data feed This data feed lists targets of an offer's revision for all offers you've created as the seller of record. If a single offer has multiple revisions, all revisions are included in the data feed. When you make an offer revision and the data in an exposed field changes, a new record is created in the data feed for the same primary key (`offer_id` plus `offer_revision`), but with a different value for `valid_from` field. The offer target data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | offer\$1target\$1id | The primary key of the feed. | | offer\$1id\$1offer\$1revision | The identifier and revision of the offer. These two columns reference the offer that this target relates to.Can used to join to the `offer_id` and `offer_revision` fields of the `Target` data feed. | | target\$1type | Indicates whether the offer recipient is BuyerAccounts, which indicates a private offer, or ParticipatingPrograms. | | polarity | Indicates whether the offer is intended to be made to the `target_type`. Acceptable values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-offer-target.html) | | value | A string that represents the target: either an AWS account ID or a program that can be used with an offer. For example, [Standard Contract for AWS Marketplace (SCMP)](standardized-license-terms.md#standard-contracts), or [AWS Marketplace Field Demonstration Program (FDP)](field-demonstration-program.md). | ## Example of offer target data feed The following shows an example of the offer target data feed. For readability, the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | offer\$1target\$1id | offer\$1id | offer\$1revision | target\$1type | polarity | value | | --- | --- | --- | --- | --- | --- | | 925ddc73f6a373b7d5544ea3210610803b600 | offer-dacpxznflfwin | 1 | ParticipatingPrograms | PositiveTargeting | EnterpriseContract | | 471ff22ae3165278f1fb960d3e14517bcd601 | offer-gszhmle5npzip | 1 | ParticipatingPrograms | PositiveTargeting | FieldDemonstration | | 511ff22adfj65278f1fb960d3e14517bcd6e602 | offer-gszhmle5npzip | 1 | ParticipatingPrograms | PositiveTargeting | EnterpriseContract | # Product data feed This data feed provides information about all products you've created as the seller of record and all products you're authorized to resell. Product data is mutable. This means that when you change the value for one of the following fields, a new record is created in the data feed with a different value for `valid_from` field. For more information about the data feed history columns, see [Historization of the data](data-feed-details.md#data-feed-historization). The product data feed is refreshed every 24 hours, so new data is available daily. The following table explains the names and descriptions of the data feed's columns. | Column name | Description | | --- | --- | | product\$1id | The friendly identifier of the product.Can be used to join to the `product_id` fields of the `Account`, `Billing_Event`, and `Offer_Product` data feeds. | | manufacturer\$1account\$1id | The identifier of the product owner. This is a foreign key to the [Account](data-feed-account.md) data feed.Can used to join to the `account_id` field of the `Account` data feed. | | product\$1code | The existing entitlement product code used to meter the product. This value is also used to join data with a report, or to reference what is provided in AWS Marketplace Metering Service. | | title | The title of the product. | ## Example of product data feed The following shows an example of the offer target data feed. For readability, the data history columns aren't shown. For information about data history fields, see [Historization of the data](data-feed-details.md#data-feed-historization). | product\$1id | manufacturer\$1account\$1id | product\$1code | title | | --- | --- | --- | --- | | prod-o4grxfafcxxxx | 555568000000 | product\$1code\$11 | Product1 | | prod-t3grxfafcxxxy | 444457000000 | product\$1code\$12 | Product2 | | prod-x8faxxfafcxxy | 666678000000 | product\$1code\$13 | Product3 | # Tax item data feed This data feed provides information about tax calculations for a customer invoice. There can be multiple line items (`line_item_id`) for a given product (`product_id`) of a given customer invoice (`invoice_id`), one or more for each tax jurisdiction. This happens, for example, with usage-based bills for customers who are using different AWS Region rules by different AWS entities (say, the US and Ireland). To learn more about where AWS collects sales tax, VAT, or GST on your sales and remits such taxes to the local tax authorities, in the name of AWS, Inc., see [Amazon Web Service Tax Help](https://aws.amazon.com/tax-help/). The tax item data feed is refreshed every 24 hours, so new data is available daily. Tax item data is immutable. The following table explains the names and descriptions of the data feed's columns. For information about data history columns, see [Historization of the data](data-feed-details.md#data-feed-historization). | Column name | Description | | --- | --- | | tax\$1item\$1id | A unique identifier for a tax item record. | | invoice\$1id | The AWS invoice ID. You can use this value with the value of product\$1id to find related tax billing events. | | line\$1item\$1id | A unique identifier for a customer bill line item. Refund transactions have the same line item ID as their forward tax transactions. | | customer\$1bill\$1id | The unique identifier of the customer bill. Buyers can share this identifier with the seller to help identify and resolve tax calculation questions. | | tax\$1liable\$1party | Either `AWS` or `Seller`. If the seller is the tax liable party, taxes are collected. If AWS is the tax liable party, sales tax is collected and remitted by AWS. For more information, see [AWS Marketplace Sellers & Tax Collection](https://aws.amazon.com/tax-help/marketplace). If no taxes are collected, there is no value shown here. The seller needs to determine whether some taxes were collected for each invoice, as the seller is liable for tax collection. | | transaction\$1type\$1code | The type of transaction. Possible values are as follows: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-tax-item.html) Refund transactions share the line item ID with their original forward transactions. | | product\$1id | A foreign key to the product.Can be used to join to the `Product` data feed on the `product_id` field. | | product\$1tax\$1code | A standard code to identify the tax properties for a product. Sellers choose the properties when creating or modifying the product. | | invoice\$1date | The date the invoice was created. | | taxed\$1customer\$1account\$1id | A foreign key to the account entity who is taxed.Can be used to join to the `Account` data feed on the `account_id` field. | | taxed\$1customer\$1country | The ISO 3166 alpha 2 country code of the address used for tax calculations. | | taxed\$1customer\$1state\$1or\$1region | The state, region, or province used for tax calculations. | | taxed\$1customer\$1city | The city used for tax calculations. | | taxed\$1customer\$1postal\$1code | The postal code used for tax calculations. | | tax\$1location\$1code\$1taxed\$1jurisdiction | The vertex geocode that is associated with the taxed location. | | tax\$1type\$1code | The type of tax that is applied to the transaction. The possible values are None, Sales, and SellerUse. | | jurisdiction\$1level | The jurisdiction level of the address that is used for tax location. The possible values are State, County, City, and District. | | taxed\$1jurisdiction | The name of the tax jurisdiction. | | display\$1price\$1taxability\$1type | Whether the price that buyers see is inclusive or exclusive of taxes. All AWS Marketplace offerings are exclusive of taxes. | | taxable\$1amount | The amount of the transaction that is taxable, at this jurisdiction level. | | nontaxable\$1amount | The amount of the transaction that is nontaxable, at this jurisdiction level. | | tax\$1jurisdiction\$1rate | The tax rate that is applied, at this jurisdiction level. | | tax\$1amount | The amount of tax that is charged, at this jurisdiction level. | | tax\$1currency | The ISO 4217 alpha 3 currency code for above amounts. | | tax\$1calculation\$1reason\$1code | Whether the transaction is taxable, not taxable, exempt, or zero-rated, organized by the jurisdiction level. | | date\$1used\$1for\$1tax\$1calculation | The date that is used for calculating tax on the transaction. | | customer\$1exemption\$1certificate\$1id | The certificate ID of the exemption certificate. | | customer\$1exemption\$1certificate\$1id\$1domain | The location where the certificate is stored on Amazon systems. | | customer\$1exemption\$1certificate\$1level | The jurisdiction level that supplied the exemption. | | customer\$1exemption\$1code | The code that specifies the exemption; for example, RESALE. | | customer\$1exemption\$1domain | The Amazon system that is used to capture the customer exemption information, if available. | | transaction\$1reference\$1id | An identifier that allows you to cross-reference data from the following reports: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/marketplace/latest/userguide/data-feed-tax-item.html) | | legal\$1amount | The tax amount in the legal currency for the tax to be paid. | | legal\$1currency | The legal currency for the tax to be paid. | | tax\$1invoice\$1id | The tax invoice identifier for the tax line in cases of dual invoicing. This field will be null for unified invoicing scenarios. | | tax\$1invoice\$1date | The date of the tax invoice. This field will be null for unified invoicing scenarios. | **Note** Beginning August 5, 2021, international Marketplace Facilitator taxes for AWS Marketplace sales will have entries in the tax item data feed. This means that, beginning August 5, 2021, every AWS\$1TAX\$1SHARE and SELLER\$1TAX\$1SHARE record in the billing event data feed is expected to have a corresponding record in the tax item data feed. ## Example of tax item data feed The following shows an example of the tax item data feed. 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. | tax\$1item\$1id | invoice\$1id | line\$1item\$1id | customer\$1bill\$1id | | --- | --- | --- | --- | | 6p2ni6tu041xagvhbyanbgxl3xameha16txjoav\$10001 | 781216640 | 71000000000000000000 | 2210000000000000000 | | 6p2ni6tu041xagvhbyanbgxl3xameha16txjoav\$10002 | 781216640 | 53000000000000000000 | 2210000000000000000 | | flr4jobxjzww8czdsrq4noue2uxd56j39wxw0k7\$10001 | 250816266 | 76400000000000000000 | 5720000000000000000 | | gfkjjobxjzw56jgkrsrqgjtk52uxd56j39wgj567d\$10002 | 280336288 | 76400000000000000000 | 5724390000000000000 | | wwk1qpvb8ran3geiw8e3mp6dgs2qj7wpkuwhgk1\$10001 | 451431024 | 99300000000000000000 | 1230000000000000000 | | wwk1qpvb8ran3geiw8e3mp6dgs2qj7wpkuwhgk1\$10002 | 451431024 | 99300000000000000000 | 3120000000000000000 | | fnohdid8kwgqq9lvii2k30spn3ftgwihbe8h75x\$10001 | 229987654 | 92100000000000000000 | 6390000000000000000 | | tax\$1liable\$1party | transaction\$1type\$1code | product\$1id | product\$1tax\$1code | invoice\$1date | | --- | --- | --- | --- | --- | | Seller | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00:00:00Z | | Seller | AWS | prod-o4grxfafcxxxx | AWSMP\$1SOFTWARE\$1RA | 2018-12-31T00:00:00Z | | Seller | AWS | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00:00:00Z | | Seller | REFUND | prod-t3grxfafcxxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00:00:00Z | | Seller | AWS | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-08-31T00:00:00Z | | Seller | TAXONLYREFUND | prod-x8faxxfafcxxy | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2018-05-31T00:00:00Z | | AWS | AWS | prod-wghj8xfafrhgj | AWS\$1REMOTE\$1ACCESS\$1SOFTWARE | 2019-07-31T00:00:00Z | | taxed\$1customer\$1account\$1id | taxed\$1customer\$1country | taxed\$1customer\$1state\$1or\$1region | taxed\$1customer\$1city | taxed\$1customer\$1postal\$1code | | --- | --- | --- | --- | --- | | VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | US | GA | MILTON | 48573-4839 | | VIeGa2t9j3MuxioH9wc8lsndXXCgGCGUreeXriocM5 | US | GA | MILTON | 48573-4839 | | 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | US | NC | DURHAM | 27517-4834 | | 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | US | NC | DURHAM | 27517-4834 | | 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | US | TX | NOT APPLICABLE | 75844-1235 | | 7nyo5jwTRoPlyX81vx9ji04eEwTurO1Ff8biQi88W8 | US | TX | HOUSTON | 75844-1235 | | 192a0421313e41f069b52962ed7babf716291b688 | US | CT | NEW HAVEN | 06002-2948 | | tax\$1location\$1code\$1taxed\$1jurisdiction | tax\$1type\$1code | jurisdiction\$1level | taxed\$1jurisdiction | display\$1price\$1taxability\$1type | taxable\$1amount | nontaxable\$1amount | | --- | --- | --- | --- | --- | --- | --- | | 460473664 | Sales | State | GA | Exclusive | 100 | 0 | | 66301164 | Sales | County | FULTON | Exclusive | 0 | 100 | | 692938178 | SellerUse | State | NC | Exclusive | 58.1 | 523.8 | | 692938178 | SellerUse | State | NC | Exclusive | -58.1 | 523.8 | | 356794387 | Sales | State | TX | Exclusive | 1105.14 | 0 | | 528887443 | Sales | City | HOUSTON | Exclusive | -36 | 0 | | 171248162 | Sales | State | CT | Exclusive | 0 | 114.55 | | tax\$1jurisdication\$1rate | tax\$1amount | tax\$1currency | tax\$1calculation\$1reason\$1code | date\$1used\$1for\$1tax\$1calculation | | --- | --- | --- | --- | --- | | 0.206 | 20.6 | USD | Taxable | 2018-10-31T00:00:00Z | | 0 | 0 | USD | NonTaxable | 2018-10-31T00:00:00Z | | 0.1 | 5.8 | USD | Taxable | 2018-07-31T00:00:00Z | | 0.1 | -5.8 | USD | Taxable | 2018-07-31T00:00:00Z | | 0.06 | 66.3 | USD | Taxable | 2018-07-31T00:00:00Z | | 0.01 | -0.36 | USD | NonTaxable | 2018-07-31T00:00:00Z | | 0 | 0 | USD | Exempt | 2019-06-30T00:00:00Z |