This guide covers Amazon Unified, Multi-Marketplace Seller Accounts. If you have a multi-market Seller account, Amazon may supply data in unexpected and inconsistent ways for each market. For example, when requesting data for the US marketplace, Amazon may return data for CA. As a result, the data may be loaded for each marketplace, creating duplicate entries. For multi-market unified sellers, the outputs from Amazon can be confusing.
Settlement Reports are a prime use case of this behavior. For example, you have US settlement reports with ID 12345 and 54321. Rather than return the report once for the US ID 12345 and 54321, Amazon will return the US report ID for all four unified NA markets (US, CA, MX, and BR). This means that when a request is made to Amazon for the CA settlement report, it will return the US one. A request for MX will also return the US one.
Ideally, Amazon would reply with NO_DATA for a market without a report or merely provide a unified Settlement report by region. However, that is not the current state of their system.
As a result, you need to adjust how you query the data being mindful of this behavior.
How To Perform De-Duplication
The ob_transaction_id is a unique key per record so that you can use this key to only return one row for a given marketplace_name. In our Settlement Report example, you can get a unique record, per settlement report ID, by using the ob_transaction_id. In this case, you want Amazon.com as the marketplace, given it reflects the US settlement activity.
select sum(cast(amount as decimal)) as total, marketplace_name, settlement_id from `mydata.sp_settlements_master` where ob_transaction_id in (select max(ob_transaction_id) as ob_transaction_id from `mydata.sp_settlements_master` where settlement_id in (12345,54321) and order_id is not null group by order_id, ob_index, ob_modified_date) group by marketplace_name, settlement_id;
This query type can be translated into a view, meaning you only need to define the logic necessary once. Tools like Tableau, Power BI, Data Studio, or anything else can easily connect to the view without fuss.
Here is an example guide for creating views in Google BigQuery for reference. The process is similar for other destinations like Redshift, Snowflake, Athena, and others.