Amazon Selling Partner API

This kit utilizes the Selling Partner API (SP-API), a REST-based tool that enables Amazon sellers to access key data like orders, shipments, and payments. It boosts efficiency, reduces labor, and enhances customer responsiveness, fostering business growth.

Requirements:

• Python must be enabled

Variable configurations:

• {your_client_secret}
• {your_client_id}
• {your_refresh_token}
• {Marketplace_ids} – format: marketplaceid_1, marketplaceid_2
• Set "YOUR_DF" in Dataframe
• {Step_1a_start_date} – date to start pulling historic data from
• {aws_seller_database} – your Database in Snowflake
• {aws_seller_schema} – your Schema in Snowflake
• {aws_seller_raw_table} – your Temp Table in Snowflake
• {GET_SALES_AND_TRAFFIC_REPORT} – your final Table name in Snowflake

Kit logic overview:

Step 1.a: CREATE DATE VARIABLE – PULL HISTORIC

This step allows you to pull historical data by setting the desired date inside the {Step_1a_start_date} placeholder variable.

If you do not need this step, you can disable the toggle and set up just Step 1.b: CREATE DATE VARIABLE – DYNAMIC DATES below.

Step 1.b: CREATE DATE VARIABLE – DYNAMIC DATES

This step allows you to set data to -X days back for every run and loop over these days for each run. Example below queries for 1 day back each run:

WITH RECURSIVE date_sequence AS ( SELECT CAST(CURRENT_DATE AS VARCHAR) AS date_value UNION ALL SELECT CAST(DATEADD(day, -1, CAST(date_value AS DATE)) AS VARCHAR) FROM date_sequence WHERE CAST(date_value AS DATE) > DATEADD(day, -1, CURRENT_DATE))SELECT date_valueFROM date_sequenceORDER BY CAST(date_value AS DATE;    

Note: It’s recommended to run this with 1 or 2 days back and schedule the river to run on a daily basis.

Step 3: Loop Sequence

The loop will iterate over the full API to Database End Table sequence by 1 day (using the {date_var} from Step 1.a or 1.b).

Step 3.a: GET AMAZON SELLER END_URL

This step executes a sequence of API calls to fetch the Amazon Seller End_URL via a Python script. The {end_url} downloads the data, which is a Gziped file that is unzipped and loaded into the Dataframe {YOUR_DF}.

Step 3.b: WRITE FLATTENED DATA INTO TEMP TABLE

The data in the Dataframe is written into the interim table {aws_seller_raw_table}.

Recommendation: Drop the temp table after the river run to avoid duplicates on the next run.

Step 4: UNNEST DATA INTO FINAL TABLE

The data in the {aws_seller_raw_table} is unnested and restructured into the final table {GET_SALES_AND_TRAFFIC_REPORT}. Ensure that the SQL query references the correct table.

Output Overview

Connections

If there is already a Connection for Snowflake, import this by following the Kit wizard after clicking ‘Use Kit’. Otherwise, Kits are imported with ‘blank’ connections. In this case, in order to use the kit, you have two options:

• Swap out blank connections with existing connections

• Add credentials to the blank connections that come with the imported Kit