Manage FBA Inventory Report: A Comprehensive Overview

Manage FBA Inventory Report

Key components of the FBA Inventory Report to optimize performance and growth.

Efficient inventory management is the backbone of successful e-commerce operations, and Amazon provides sellers with valuable tools to achieve this, including the Manage FBA Inventory report.

This report offers essential information about your listings, product conditions, quantities, and dispositions, making it an indispensable resource for day-to-day inventory management tasks. In this comprehensive guide, we will explore the Manage FBA Inventory report in depth, including field definitions, report scheduling, and how to harness its power for effective inventory control.

Understanding the Manage FBA Inventory Report

The Manage FBA Inventory report provides sellers with vital data on their products within Amazon’s Fulfillment by Amazon (FBA) network. It covers listing details, product conditions, quantities, and more. It’s important to note that archived listings are not included in this report.

Why the Manage FBA Inventory Report Matters

This report is instrumental for several reasons:

1. Inventory Oversight: It offers detailed insights into your inventory, helping you monitor and manage it effectively.
2. Product Information: You can access crucial information such as SKUs, ASINs, product names, and conditions.
3. Pricing Insights: Your current selling prices are available, allowing you to make informed pricing decisions.
4. Fulfillment Methods: It indicates whether a product is listed for fulfillment by you or by Amazon (Fulfillment by Amazon — FBA).

Field Definitions in the Report

To fully grasp the Manage FBA Inventory report, let’s explore its key field definitions:

| Field Name                 | Description                                                      | Example          |
|----------------------------|------------------------------------------------------------------|------------------|
| sku                        | Unique product identifier (letters or numbers) assigned by you.   | AB-8675309       |
| fnsku                      | Unique identifier assigned by Amazon for FBA products.           | X00000E5TX       |
| asin                       | Amazon's unique identifier for items.                            | B003ZYF3LO       |
| product-name               | The title of your product.                                        | Toysmith Nonstick Bakeware Set |
| condition                  | The condition of your product (e.g., New or Used - Good).        | New              |
| your-price                 | Your current selling price.                                       | 10.25            |
| mfn-listing-exists         | "Yes" indicates if the product is listed for fulfillment by you. | Yes              |
| mfn-fulfillable-quantity   | Quantity in your fulfillment network ready for picking and shipping. | 10           |
| afn-listing-exists         | "Yes" indicates if the item is listed for Fulfillment by Amazon (FBA). | Yes        |
| afn-warehouse-quantity     | Number of units for an SKU in fulfillment centers (processed).   | 35               |
| afn-fulfillable-quantity   | Number of units for an SKU in fulfillment centers (pickable).    | 20               |
| afn-unsellable-quantity     | Number of units for an SKU in fulfillment centers (unsellable).  | 5                |
| afn-reserved-quantity      | Number of units for an SKU in fulfillment centers (reserved).    | 10               |
| afn-total-quantity         | Total units for an SKU in a shipment or fulfillment centers.    | 55               |
| per-unit-volume            | Volume in cubic feet of one unit (to the nearest 1/100 of an inch). | 0.23          |
| afn-inbound-working-quantity | Units in a shipment to fulfillment centers that you've notified Amazon about. | 10   |
| afn-inbound-shipped-quantity | Units in a shipment to fulfillment centers with tracking numbers provided. | 5 |
| afn-inbound-receiving-quantity | Units in a shipment not yet received at a fulfillment center, part of a shipment with some units already received. | 5 |
| afn-researching-quantity    | Units being researched at Amazon's fulfillment centers.          | 1                |
| afn-future-supply-buyable   | Units available for customers while inventory is on its way through In-Stock Head Start. | 10 |
| afn-reserved-future-supply | Units purchased by customers while inventory is on its way through In-Stock Head Start. | 5 |

Report Scheduling for Effective Management

To optimize your inventory management, you can schedule the generation of the Manage FBA Inventory report. Here’s how to do it:

Creating a Scheduled Report

• On the Manage FBA Inventory report page, navigate to the “Schedule” tab.
• From the drop-down menus, select the desired report frequency (e.g., daily, weekly), a future start date, and the preferred report format.
• Click “Create scheduled report.”

Your schedule will be listed in the “My schedule” section of the page. Note that you can only create one schedule per report.

Depending on your chosen frequency, the report will be generated around midnight in your store’s time zone. Each scheduled report will be stored in the “Scheduled reports” section, with a maximum limit of 30 reports, listed in chronological order.

If you require an immediate report, you can download it directly from the “Download” tab. Please note that manually requested reports won’t be listed in the “Scheduled reports” section.

Go Faster With Selling Partner API Manage FBA Inventory Automation

The Amazon Selling Partner API (SP-API) allows for direct, automated access to Manage FBA Inventory data, making it easier for businesses to integrate this data into their systems.

Openbridge allows Amazon sellers to streamline the data management process by automating Manage FBA Inventory report integrations and storing data in a unified data warehouse. This service saves sellers time manually downloading reports and reduces errors in merging and tracking downloaded reports.

Automation eliminates manual download wrangling, ensuring up-to-date and accurate data straight from the AmazonFBA Inventory API. With data stored in a unified data warehouse like Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena, you can combine different Amazon reports is easier to gain a more holistic view of the business.

This fast and unified data access enables teams to utilize their preferred analytical tools, such as Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight, for various purposes, including machine learning, business intelligence, data modeling, and online analytical processing.

Maximizing the Manage FBA Inventory Report’s Potential

To make the most of this report, consider these best practices:

1. Regular Monitoring: Set up scheduled reports and routinely review them to keep a close eye on your inventory.
2. Pricing Strategy: Use pricing insights from the report to adjust your selling prices competitively.
3. Inventory Allocation: Leverage data on fulfillable and reserved quantities to allocate inventory effectively.
4. In-Stock Head Start: Make use of data on future supply buyable and reserved future supply to manage inventory availability.

The Manage FBA Inventory report is a vital tool for Amazon sellers, offering detailed insights into their inventory, product conditions, and more. By understanding its field definitions, scheduling reports, and implementing best practices, sellers can maintain control over their inventory and optimize their e-commerce operations.

Get Started Automating Manage FBA Inventory Data for — For Free.

Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s Manage FBA Inventory data. Openbridge integration is a code-free, fully-automated API integration. By providing Sellers with access to high-velocity data, they can accelerate the speed at which the team can gain valuable insights that deliver data-driven growth and profit.

Sign up for a 30-day free trial of our Amazon API code-free automation.

Frequently Asked Questions (FAQs)

How often should I schedule Manage FBA Inventory reports?

The scheduling frequency should align with your inventory management needs. Consider factors like product turnover and sales velocity when choosing a frequency.

Can I use the Manage FBA Inventory report for pricing decisions?

-Yes, you can access pricing information in the report to make informed pricing decisions and stay competitive.

How can I best utilize the data on future supply buyable and reserved future supply?

-This data can help you manage inventory availability and plan for future sales. Adjust your listings and promotions accordingly.

What actions should I take if I notice discrepancies in the report data?

Investigate discrepancies promptly, reconcile your data, and take necessary actions to ensure accurate inventory management.

Can I access historical Manage FBA Inventory reports?

Amazon typically retains historical reports for a limited time. Consider downloading and archiving reports for reference if needed in the future.

---

Manage FBA Inventory Report: A Comprehensive Overview was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/OYCUtbs
via IFTTT

Amazon All Orders Report: A Comprehensive Guide

Amazon All Orders Reports

Introduction to the Amazon Seller Central All Orders Report

Amazon has long been at the forefront of e-commerce innovation, providing sellers with tools to enhance their sales strategy. Among the most valuable of these tools is the Amazon All Orders Report data. In this guide, we’ll delve into its intricacies, offering insights and practical applications for both novices and seasoned sellers.

Overview and Purpose

The Amazon All Orders Report is a crucial tool for sellers, offering a wealth of information related to their sales activities. This report combines data on order status, sales channels, and item details, ensuring sellers have a holistic understanding of their business operations.

What can you do with this data?

1. Growth Pattern Analysis:
   Study the report to identify consistent growth patterns. Are there specific times of the year where certain products outperform others? By understanding these patterns, you can adjust marketing efforts and promotions to capitalize on predictable sales surges.
2. Inventory Optimization:
   Analyze the report to understand which products are selling fast and which ones are lagging. Based on these insights, adjust your inventory levels. Holding the right stock ensures you can meet demand during high sales periods, while also reducing costs associated with overstocking slow-moving items.
3. Leverage Advanced Analytics:
   Integrate the All Orders Report with advanced analytical tools to derive deeper insights. Visual representations like trend lines, bar graphs, and pie charts can highlight sales channels’ performance, product preferences, and even regional sales trends. This can guide targeted marketing and sales strategies.
4. Data Ownership and Backups:
   Regularly export and back up your All Orders Report data. Owning your data ensures you can access historical information, even beyond Amazon’s retention period. This long-term data is invaluable for year-over-year growth comparisons and long-term strategic planning.
5. Engage in Predictive Analysis:
   Use the report’s data in conjunction with forecasting tools to predict future sales and inventory needs. By understanding what’s likely to come, you can pre-emptively adjust inventory levels, plan promotions, or even explore potential new product additions.

Accessing the Report: Manual or Automated

Two primary methods for accessing this report; Automated or manual downloads. Downloading occurs in your account interface. Automation taps into the Amazon Selling Partner API (Amazon SP-API) to automate report processing and storage in a cloud warehouse or data lake.

Manual Access via Seller Central:

1. Login: First, sellers need to log into their Amazon Seller Central account.
2. Navigate to Reports: Usually found in the main navigation bar, this section contains various sales, inventory, and performance reports.
3. Locate the ‘All Orders Report’: Within the Reports section, sellers can find and select the specific “All Orders Report”.
4. Select Date Range: Amazon allows sellers to pull reports based on specific date ranges. Choose the desired range.
5. Download: Once the report has been generated, there will typically be an option to download it. The report is often available in different formats like .csv or .txt.
6. Import to BI Tool: If you’re using tools like Power BI, Tableau, or Looker, you can then manually upload this downloaded file to visualize and analyze the data.

Note: Manual downloads are more time-consuming and may not be ideal for frequent and up-to-date data analytics.

Go Faster With Selling Partner API Report Automation

The Amazon Selling Partner API (SP-API) allows for direct, automated access to Amazon seller data, making it easier for businesses to integrate this data into their systems.

Openbridge allows Amazon sellers to streamline the data management process by automating All Orders API integrations and storing data in a unified data warehouse. This service saves sellers time manually downloading reports and reduces errors in merging and tracking downloaded reports.

Automation eliminates manual download wrangling, ensuring up-to-date and accurate data straight from Amazon APIs. With data stored in a unified data warehouse like Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena, you can combine different Amazon reports is easier to gain a more holistic view of the business.

This fast and unified data access enabled teams to utilize their preferred analytical tools, such as Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight, for various purposes, including machine learning, business intelligence, data modeling, and online analytical processing.

Types Of Reports

The report’s data can be tailored based on two primary criteria:

• By order date: This captures the data of orders placed within a designated timeframe.
• By last update: This includes data on orders that have undergone recent changes, such as updates or edits, within a specified period.

Limitations: What’s Not Offered

Before diving into the data, it’s worth noting that an online view of the All Orders Report isn’t available. This can be a drawback for those used to real-time analytics but ensures data privacy and accuracy.

Deciphering Field Definitions

The table provides a detailed set of field definitions for Amazon’s “All Orders” data.

• Identification Fields: This includes unique order IDs from both Amazon and the seller, signifying each transaction.
• Date Fields: There are fields indicating when the order was placed and its most recent update.
• Status and Channels: Information regarding the current status of orders, fulfillment methods, and channels through which orders were placed is provided.
• Product and Pricing: Product-related fields capture the name, unique identifiers (SKU and ASIN), and pricing details. This includes the base price, tax, shipping costs, promotions, and any associated gift wrapping charges.
• Shipping Details: Information on both the default and actual shipping addresses are provided in a detailed manner, capturing name, address lines, city, state, country, and postal codes.
• Taxation and Licensing: There’s data on the tax collection model and the Import One Stop Shop (IOSS) number which is crucial for sellers shipping to the EU.
• Promotions: The table also offers fields defining promotional discounts on items and shipping.
• Licensing: Some fields point towards professional licenses, suggesting that certain items might require buyer credentials (like medical equipment).

This table is designed to capture a comprehensive view of any order placed on Amazon, from basic transactional details to shipping and promotions, ensuring sellers have a complete picture of their sales.

| Field Name                            | Description                                                           | Example                                      |
|---------------------------------------|-----------------------------------------------------------------------|----------------------------------------------|
| amazon-order-id                       | Amazon's unique, displayable identifier for an order                   | 058-3718414-0463502 or S01-3718414-0463502   |
| merchant order-id                     | A unique identifier optionally supplied for the order by the seller   | my-order                                    |
| purchase-date                         | The date the order was placed                                         | 2003-07-14T18:53:56+ 00 :00                  |
| last-updated-date                     | The date of the most recent order update                              | 2003-07-14T18:53:56+ 00 :00                  |
| order-status                          | Current status of the customer order                                  | Complete                                    |
| fulfillment-channel                   | Indicates how the order was fulfilled, via Amazon (AFN) or Merchant (MFN) | AFN or MFN                                |
| sales-channel                         | Channel through which the order was purchased                         | Amazon.com                                  |
| order-channel                         | The sub channel through which a sale was made for CBA/WBA orders      | Phone                                       |
| ship-service-level                    | An enumerated value that determines the type of fulfillment service that the buyer expects the seller to use | Standard                                  |
| product-name                          | The short title for the product                                       | Chocolate Truffles                          |
| sku                                   | A seller-defined unique identifier for a product                      | HarryPotter                                 |
| asin                                  | Amazon inventory ID                                                   | B000WON1Z0                                  |
| item-status                           | Current status of this item within the order                          | Shipped                                     |
| quantity                              | The number of this item that were purchased                           | 1                                           |
| currency                              | The currency used for the purchase                                    | USD                                         |
| item-price                            | The amount the buyer paid for the item                                | 15                                          |
| item-tax                              | The amount the buyer paid for item tax                                | 0                                           |
| shipping-price                        | The amount the buyer paid for shipping                                | 4.99                                        |
| shipping-tax                          | The amount the buyer paid for shipping tax                            | 0                                           |
| gift-wrap-price                       | The amount the buyer paid for gift wrap                               | 4.99                                        |
| gift-wrap-tax                         | The amount the buyer paid for gift wrap tax                           | 0                                           |
| item-promotion-discount               | Total of all promotion discounts applied to the order item            | -6.5                                        |
| ship-promotion-discount               | Promotion discount applied to shipping                                | -4.25                                       |
| ship-city                             | The city of a standard address                                        | Los Angeles                                 |
| ship-state                            | The state or region of a standard address                             | California                                  |
| ship-postal-code                      | The postal (zip) code of a standard address                           | 90039                                       |
| ship-country                          | ISO 3166 standard two-letter country code                              | US                                          |
| promotion-ids                         | List of all item promotions applied to this order item                | -                                           |
| Licensee name                         | Name of the individual on the professional license                     | Jane Doe                                    |
| License number                        | Professional license number provided by the customer                  | 12345                                       |
| License state                         | State where professional license number is valid                      | California                                  |
| License expiration date               | Professional license expiration date provided by the customer         | 2017-08-20                                  |
| default-ship-from-address-name        | The warehouse or business name used in the Seller Central account shipping settings or template | John Doe                                  |
| default-ship-from-address field-1     | First line of "Default Ship From" address                             | 4270 Cedar Ave                              |
| default-ship-from-address-field-2     | Second line of "Default Ship From" address                            | Optional                                    |
| default-ship-from-address-field-3     | Third line of "Default Ship From" address                             | Optional                                    |
| default-ship-from-address-city        | City of "Default Ship From" address                                   | SUMNER PARK                                 |
| default-ship-from-address-state       | State of "Default Ship From" address                                  | FL                                          |
| default-ship-from-address-country     | Country of "Default Ship From" address                                | US                                          |
| default-ship-from-address-postal-code | ZIP code or postal code of "Default Ship From" address                | 32091                                       |
| actual-ship-from-address-name         | The warehouse or business name that is chosen while confirming the order | John Doe                                  |
| actual-ship-from-address-1            | First line of "Actual Ship From" address                              | 4270 Cedar Ave                              |
| actual-ship-from-address-field-2      | Second line of "Actual Ship From" address                             | Optional                                    |
| actual-ship-from-address-field-3      | Third line of "Actual Ship From" address                              | Optional                                    |
| actual-ship-from-address-city         | City of "Actual Ship From" address                                    | SUMNER PARK                                 |
| actual-ship-from-address-state        | State of "Actual Ship From" address                                   | FL                                          |
| actual-ship-from-address-country      | Country of "Actual Ship From" address                                 | US                                          |
| actual-ship-from-address-postal-code  | ZIP code or postal code of "Actual Ship From" address                 | 32091                                       |
| tax-collection-model                  | Tax Collection Model or Tax Collection Responsible Party              | MarketplaceFacilitator                      |
| IOSS Number                           | Import One Stop Shop (IOSS) number for the store                      | IMXXXYYYYYYZ                                |

Critical Fields Explained

The report boasts a comprehensive set of fields, each offering a unique piece of the puzzle. Fields like the amazon-order-id offer a unique identifier for an order, whereas the product-name provides insights into the specific products that are selling, for instance, “Chocolate Truffles”.

Practical Examples

For a clearer understanding, consider a seller aiming to determine the popularity of “Chocolate Truffles.” By filtering data using the product-name field, they can quickly ascertain the product’s sales trajectory.

FBA vs. Seller-Fulfilled Orders

Not all Amazon orders are made equal. The platform differentiates between orders fulfilled directly by Amazon (FBA) and those managed by the sellers themselves. This report captures data from both these streams, allowing sellers to track and measure demand variations and performance across both channels.

Utilizing the Report

With fields like order-status and item-status, sellers can effectively monitor and manage order progress, ensuring timely deliveries and satisfied customers. For businesses operating on multiple channels, understanding where orders are coming from is essential. The fulfillment-channel and sales-channel fields provide this clarity, helping businesses refine their multi-channel strategies.

Here are a collection SQL queries based on the provided field definitions for the All Orders data:

Find total sales per product:

SELECT product-name, SUM(item-price) as total_sales
FROM orders
GROUP BY product-name
ORDER BY total_sales DESC;

Determine the total number of orders shipped via each fulfillment channel:

SELECT fulfillment-channel, COUNT(amazon-order-id) as total_orders
FROM orders
GROUP BY fulfillment-channel;

Identify the top 10 selling products based on quantity:

SELECT product-name, SUM(quantity) as total_sold
FROM orders
GROUP BY product-name
ORDER BY total_sold DESC
LIMIT 10;

Calculate the average item price per sales channel:

SELECT sales-channel, AVG(item-price) as average_price
FROM orders
GROUP BY sales-channel;

Find orders that have not been shipped (assuming statuses other than ‘Shipped’ imply not shipped):**

SELECT amazon-order-id, order-status
FROM orders
WHERE item-status <> ‘Shipped’;

Determine total shipping cost per city:

SELECT ship-city, SUM(shipping-price) as total_shipping_cost
FROM orders
GROUP BY ship-city
ORDER BY total_shipping_cost DESC;

Find the top 5 states based on total sales

SELECT ship-state, SUM(item-price) as total_sales
FROM orders
GROUP BY ship-state
ORDER BY total_sales DESC
LIMIT 5;

These queries can provide valuable insights into sales performance, product popularity, shipping costs, and more. They’re a great starting point for sellers looking to optimize their operations based on historical data.

What about Daily queries?

Daily time series queries are particularly valuable for identifying short-term trends or patterns. Here are some daily time series SQL queries based on the “All Orders” data:

Total Sales per Day:

SELECT
 DATE(purchase-date) as sales_date,
 SUM(item-price) as total_sales
FROM orders
GROUP BY sales_date
ORDER BY sales_date;

Daily Quantity Sold of a Specific Product (e.g., “Chocolate Truffles”):

SELECT
 DATE(purchase-date) as sales_date,
 SUM(quantity) as total_quantity_sold
FROM orders
WHERE product-name = ‘Chocolate Truffles’
GROUP BY sales_date
ORDER BY sales_date;

Average Daily Shipping Costs:

SELECT
 DATE(purchase-date) as sales_date,
 AVG(shipping-price) as average_shipping_cost
FROM orders
GROUP BY sales_date
ORDER BY sales_date;

Number of Orders per Day by Fulfillment Channel:

SELECT
 DATE(purchase-date) as sales_date,
 fulfillment-channel,
 COUNT(amazon-order-id) as total_orders
FROM orders
GROUP BY sales_date, fulfillment-channel
ORDER BY sales_date, fulfillment-channel;

Total Daily Sales for Each Sales Channel:

SELECT
 DATE(purchase-date) as sales_date,
 sales-channel,
 SUM(item-price) as total_sales
FROM orders
GROUP BY sales_date, sales-channel
ORDER BY sales_date, sales-channel;

Daily Tax Collection from Item Tax and Shipping Tax:

SELECT
 DATE(purchase-date) as sales_date,
 SUM(item-tax) + SUM(shipping-tax) as total_tax_collected
FROM orders
GROUP BY sales_date
ORDER BY sales_date;

By analyzing the results of these daily time series queries, sellers can identify day-to-day fluctuations, determine the cause of any spikes or drops in sales or other metrics, and strategize to optimize operations and marketing activities.

Ensuring Customer Privacy

Customer trust is paramount. That’s why the All Orders Report deliberately excludes any customer-specific details, ensuring privacy and compliance with data protection regulations.

Additional Resources

Dive Deeper into Amazon’s Reporting Tools

The All Orders Report is just the tip of the iceberg. Sellers eager to delve deeper can explore a plethora of tools and resources on Amazon Seller Central, amplifying their e-commerce success.

• Understanding Amazon Order and Sales Reports | Openbridge Help Center
• Amazon Sales Connector | Openbridge Help Center
• Amazon Sales and Traffic Business Report | Openbridge Help Center

Get Started Automating Amazon All Orders Data for — For Free.

Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s All Orders data. Openbridge integration is a code-free, fully-automated API integration. By providing Sellers with access to high-velocity data, they can accelerate the speed at which the team can gain valuable insights that deliver data-driven growth and profit.

Sign up for a 30-day free trial of our Amazon API code-free automation.

Frequently Asked Questions (FAQs)

What is the primary purpose of the All Orders Report?

It offers sellers a detailed insight into their sales activities, encompassing both FBA and self-fulfilled orders.

Is there an online view available for this report?

No, currently Amazon does not offer an online view for the All Orders Report.

Does the report contain any customer-specific information?

To ensure utmost data privacy, the report does not include any customer-identifying information.

How can the report aid in inventory management?

By analyzing sales trends and order statuses, sellers can optimize their inventory, reducing stockouts and overstock scenarios.

Are there any future enhancements expected for this report?

Given Amazon’s commitment to seller success, we can expect regular updates and enhancements to the report in the future.

---

Amazon All Orders Report: A Comprehensive Guide was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/hvrPt5M
via IFTTT

Discover Airbridge: Configuration-Driven Airbyte Cloud Data Integration Pipelines

Quickly run configuration driven Airbyte connectors via Airbridge, an open source, Docker based service.

Earlier this year the Meltano community released the tap-airbyte-wrapper. This is what open source is all about! The Meltano Airbyte wrapper takes advantage of Docker, translating output from a source into a Singer-compatible format. This is a perfect fit for someone invested in the Meltano ecosystem.

While we were inspired by the effort, there was one hitch for us. We wanted something that works for data-curious spelunkers, data engineers, and developers so they can explore configuration-driven data pipelines without relying on Meltano.

Keeping things lean and simple, with a focus on the true open source aspects of Airbyte, was important to us. As a result, we built Airbridge as a pure Python+Docker integration tool that orchestrates data ingestion pipelines from Airbyte data sources like Stripe, Facebook, or Google to Airbyte data destination connectors like a S3 data lake, Redshift, Snowflake, or BigQuery.

Project Framework Approach

While Airbridge shares a common core (Python, Docker…) with Meltano, unlike the Meltano wrapper, Airbridge does not require using the Meltano SDK. Also, unlike Meltano tranforming Airbyte output to the Singer spec, Airbridge retains the raw data source output in an unmodified state. Nothing against Singer formatted outputs, we just wanted to keep the Airbyte output in raw form.

The following are a collection of the guiding principles we set forth at the start of the work effort;

🐳 Use Docker-Driven Workflows: Airbridge must be Docker-centric methodology that capitalizes on ready-to-go source and destination Docker images accessible via Airbyte Docker Hub. This facilitates a streamlined setup procedure, directing focus where needed most: crafting seamless data flows.

🐍 Leverage Python: Anchor the work in the well-traveled and standard-based Python framework. Airbridge offers a clean, swift, and modular data flow, fostering easy setup and modification.

🔗 Seamless Airbyte Sources and Destinations Integration: Airbridge must orchestrate the essential resources to seamlessly sync source connectors to data destinations.

🔄 Automated State Management for Hassle-Free Operations: Provide the ability for automated state tracking for each run. Use simple yet potent solution to ensure uniformity and dependability in tasks, curbing errors, and conserving resource time.

🔓 Open-Source and Community-Driven: Airbridge must be released under the MIT license.

📦 No Bloat, Just Efficiency: No unnecessary wrappers; Airbridge should concentrate on delivering a lean, efficient tool that caters to the core needs of developers, fostering seamless data integration pipelines.

Getting Started: Where can I deploy Airbridge?

Deploy it in the cloud, locally, or somewhere in between. Setting up Airbridge in your cloud computing environment — be it AWS, Azure, or Google Cloud, is a breeze.

Before you begin, ensure that you have the following prerequisites installed on your system:

1. Python: Verify or install it using the command: python --version
2. Git: Verify or install it using the command: git --version
3. Docker: Verify or install it using the command: docker --version

If any of the above software is not installed, please install them before proceeding.

Step 1: Create a local project directory and navigate into it:

mkdir airbridge_project && cd airbridge_project 

Step 2: Clone the project from Github and navigate into the project directory:

git clone https://github.com/openbridge/airbridge.git && cd airbridge

Step 3: Set up a virtual environment (optional but recommended):

python -m venv venv
source venv/bin/activate # On Unix or MacOS
# .\venv\Scripts\activate # On Windows

Step 4: Install the project dependencies. There is a pyproject.toml file, so you can use poetry to manage the dependencies. First, install poetry using the following command:

pip install poetry

Now, install the project dependencies using poetry:

poetry install

Step 5: Start the Docker daemon if it is not already running. You can do this via the Docker desktop application or by using the following command, depending on your OS:

systemctl start docker # You might need sudo privileges

Step 6: Once you have your Airbyte source and destination configs ready, you can run Airbridge. Here is an example command using Stripe as a source and Amazon S3 as a destination:

poetry run main -i airbyte/source-stripe -w airbyte/destination-s3 -s /airbridge/env/stripe-source-config.json -d /airbridge/env/s3-destination-config.json -c /airbridge/env/stripe-catalog.json -o /airbridge/tmp/mydataoutput/

Explanation of the command parameters:

• -i: Input source (Stripe in this case)
• -w: Destination (Amazon S3 in this case)
• -s: Path to the source configuration file
• -d: Path to the destination configuration file
• -c: Path to the catalog configuration file
• -o: Output directory

Comprehensive Airbyte install guides are at your disposal, guiding you to get your Airbyte connector and data destinations up and running. Visit the project here:

GitHub - openbridge/airbridge

License

Airbridge is released under the MIT license. Access the code and kickstart your efforts.

Community

For enthusiasts eager to delve deeper, the Airbridge and Airbyte GitHub spaces are where developers can discover a treasure trove of resources, contribute to the projects, or build knowledge from the community. It’s a platform where your expertise, novice or veteran, finds appreciation and acknowledgment.

As with most efforts like these, we cordially invite you to partake in a collaborative expedition as the effort moves forward. Community perspectives, feedback, and involvement are crucial in sculpting a tool that aligns with an open source projects aspirations.

Frequently Asked Questions (FAQs)

What does Airbyte do?

Airbyte is a data integration platform that facilitates the extraction, transformation, and loading (ETL) of data from various sources to destinations of your choice. It helps automate the data pipeline creation process, allowing businesses and developers to focus on deriving insights from the data rather than grappling with data sync challenges.

How does Airbyte work?

Airbyte operates through a series of connectors that enable pipelines between different data sources and destinations. These connectors are pre-configured to work with specific platforms, simplifying the data extraction and loading process. Airbyte also offers a graphical user interface (UI) where users can configure and manage their data pipelines easily. Under the hood, it utilizes Docker containers to manage and isolate different data connectors, ensuring a robust and scalable data solution.

What is Airbyte used for?

Airbyte is primarily used for data integration and ETL processes. It allows organizations and developers to create data pipelines easily, facilitating data movement from various sources, like databases, APIs, and flat files, to different destinations, including data warehouses, data lakes, or other business intelligence tools. This helps consolidate data in a centralized location, making it easier to analyze and derive valuable business insights.

Is Airbyte open-source?

Yes, parts of Airbyte are open-source. Some features of Airbyte operate under the MIT license, allowing users to use, modify, and distribute the software freely. Airbridge focuses on those parts of Airbyte that are open source under the MIT license. Open source fosters a community where developers can contribute to the project, enhancing its functionality and robustness. Airbridge source code is publicly available on GitHub, encouraging transparency and continuous improvement.

Airbridge will not support any parts of Airbyte that fall under, as the Open Source Initiative says, a “fauxpen” source license.

Does it require the Airbyte UI?

Using the Airbyte UI is not needed. The Airbyte UI offers a user-friendly environment where you can configure data connectors, set up data pipelines, and monitor the status of your data integrations. However, Airbridge allows configuring and managing data pipelines without using the UI for users comfortable with command-line operations.

What is the Airbyte Pricing for running open source?

Airbyte Cloud costs $2.50 per credit to run. If you replicate 100M rows of data per month, Airbyte will cost over $14,000 for the year. However, if you run Airbyte in a self-managed context, your costs are infrastructure and management time. You just need to make sure you are compliant with Airbyte licenses.

---

Discover Airbridge: Configuration-Driven Airbyte Cloud Data Integration Pipelines was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/kPW5n6u
via IFTTT

Introduction to Amazon return report data analysis

Amazon FBA returns data analysis examples

You can perform various types of analysis based on Amazon returns report data. We detail various examples and opportunities for performing data analysis of returns reports.

For our test dataset, our sample data set is relatively small, with just a few days of return data.

• The dataset contains 46,586 entries.
• There are 13 columns in the dataset: ‘return-date’, ‘order-id’, ‘sku’, ‘asin’, ‘fnsku’, ‘product-name’, ‘quantity’, ‘fulfillment-center-id’, ‘detailed-disposition’, ‘reason’, ‘status’, ‘license-plate-number’, and ‘customer-comments’.
• Most columns are of object (string) type, except ‘quantity’ which is an integer.
• The ‘customer-comments’ column has some missing values (NaN), with 13,013 non-null entries out of 46,586 total entries.
• All returned items have a quantity of 1, as indicated by the minimum, maximum, and average values of the ‘quantity’ column.
• The ‘sku’, ‘asin’, ‘fnsku’, and ‘product-name’ columns have several unique entries, indicating a wide variety of products in the dataset.
• The ‘return-date’ column has 28 unique entries, suggesting that the dataset spans a period of 28 days.
• The ‘fulfillment-center-id’ column has 170 unique entries, indicating that the dataset includes returns processed by 170 fulfillment centers.
• The ‘detailed-disposition’ column, which describes the condition of the returned items, has 5 unique entries.
• The ‘reason’ column, which contains the reason for return as indicated by the customer, has 26 unique entries.
• The ‘status’ column, which describes the status of the customer return, has 2 unique entries.
• The ‘license-plate-number’ column, which uniquely identifies specific items, has 46,538 unique entries, almost one for each entry in the dataset.

Below are some example SQL queries that could be used to explore the dataset.

Count of returned products by reason:

This can help you understand the primary reasons customers are returning products.

• SELECT reason, COUNT(*) AS count FROM Returns GROUP BY reason ORDER BY count DESC;

Count of returned products by disposition (condition):

This can help you understand the condition of products when they are returned.

• SELECT detailed_disposition AS disposition, COUNT(*) AS count FROM Returns GROUP BY detailed_disposition ORDER BY count DESC;

The total quantity of each product returned:

This can help you understand which products are returned the most frequently.

• SELECT product_name, SUM(quantity) AS total_quantity FROM Returns GROUP BY product_name ORDER BY total_quantity DESC;

Number of returns processed by each fulfillment center:

This can help you understand if there are issues with specific fulfillment centers.

• SELECT fulfillment_center_id AS fc, COUNT(*) AS count FROM Returns GROUP BY fulfillment_center_id ORDER BY count DESC;

Percentage of returned items that are sellable:

This can help you understand the financial impact of returns.

• SELECT (COUNT(*) FILTER (WHERE detailed_disposition = 'SELLABLE') * 100.0 / COUNT(*)) AS sellable_percentage FROM Returns;

Average number of returned items per order:

This can help you understand if there are orders with multiple returned items.

• SELECT AVG(quantity) AS average_returns FROM Returns;

Visualization Examples

Let’s start with the first one: Count returned products by reason. We’ll compute the counts and create a bar plot for visualization.

The bar plot above shows the count of returned products grouped by the reason for return. “APPAREL_STYLE” is the most common reason for returns, followed by “APPAREL_TOO_LARGE.”

Next, let’s examine the count of returned products by their disposition (condition)

The bar plot above shows the count of returned products by their condition (disposition). Most returned items appear in the “SELLABLE” condition, followed by “CUSTOMER_DAMAGED.”

Next, calculate the number of returns processed by each fulfillment center. Due to the potentially large number of unique centers, we will visualize the top 10 centers with the highest return counts.

The bar plot above shows the number of returns processed by each fulfillment center, focusing on the top 10 centers with the highest return counts.

Here is a word cloud based on customer comments;

Additional Analysis Opportunities

Here are additional examples of how you might visualize the data;

1. Count of returned products by reason: A bar chart would work well here, with the reasons on the x-axis and the counts on the y-axis.
2. Count of returned products by disposition (condition): Similarly, a bar chart would work well for this data.
3. The total quantity of each product returned: If there are many different products, a horizontal bar chart or a pie chart is more effective in visualizing the products with the highest return quantities.
4. Number of returns processed by each fulfillment center: A bar chart or pie chart could be used to visualize this data.
5. Percentage of returned items that are sellable: This could be visualized using a pie chart showing the proportion of sellable vs. unsellable returns.
6. Average number of returned items per order: A histogram could be useful here to see the distribution of the number of returned items per order.

Seamless, Code-free Rapid Retail API Automation Unleashed

Openbridge provides automated data feeds, without the need for code, to quickly deliver data directly to Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena.

This fast and unified data access enabled teams to utilize their preferred analytical tools, such as Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight, for various purposes, including machine learning, business intelligence, data modeling, and online analytical processing.

Get Started Automating Amazon Returns Data for — For Free.

Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s Return data. Openbridge integration is a code-free, fully-automated API integration. By providing Sellers with access to high-velocity data, they can accelerate the speed at which the team can gain valuable insights that deliver data-driven growth and profit.

Sign up for a 30-day free trial of our Amazon API code-free automation.

---

Introduction to Amazon return report data analysis was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/9Cejbqo
via IFTTT

Amazon Rapid Retail Analytics: New API for Real-time Metrics

Unleash the power of your data with Amazon Rapid Retail Analytics for real-time insights for Amazon Vendor Central and Seller Central.

Amazon Vendor Central and Seller Central have a new real-time API, Rapid Retail Analytics. Openbridge has been partnering with Amazon on a closed beta of the new API, and we are excited to announce it has finally been released.

Amazon’s Retail Rapid Analytics API for Vendors and Sellers offers insight into key performance benchmarks, including hourly sales, traffic, and inventory data. At the moment, Sellers only have access to Sales data. Vendors can access Sales, Traffic, and Inventory, a game changer.

Here are the three core datasets in this release;

📊 Sales

• Ordered Units: It represents the number of units Amazon customers order for a specific ASIN within a given time frame. This number can be negative if there are more cancellations than orders.
• Ordered Revenue: The total revenue generated from the aggregated ordered product sales for the specified ASIN within a given time frame. Adjustments are made to account for cancellations.

📦 Inventory:

• Highly Available Inventory Units: This metric represents the number of units available for sale on the Amazon website across different regions and apps. It is based on the units in Amazon fulfillment centers that are in a sellable condition, excluding units in customer shopping carts or allocated for other orders.

🚀 Traffic:

• Glance Views: It indicates the number of times customers have viewed the product detail page for a specific ASIN. The views are counted when a particular merchant is the featured offer for the product. For vendors, this metric is applicable when retail is the featured offer. Glance views are reported based on the Amazon Retail Analytics manufacturing view.

Examples of Rapid Retail Analytics API Data

Here are some examples of the data included in the hourly data feeds:

• Sales: Hourly sales data would show two units of ASIN “B123456789” ordered between 7:00 pm and 8:00 pm UTC on February 20th, 2023, which generated revenue of 37.98.
• Traffic: Hourly traffic data would show ASIN “B123456789” received 5,000 “glance views” between 7:00 and 8:00 pm UTC on February 20th, 2023.
• Inventory: Hourly inventory data would show that ASIN “B123456789” had 9,000 units highly available between 7:00 pm and 8:00 pm UTC on February 20th, 2023.

The “highly available” metric refers to the number of units available for sale on Amazon with the fastest shipping speed, typically Prime, based on units in a sellable condition in Amazon’s fulfillment centers. This number considers units unavailable for sale, such as those in customer shopping carts or reserved for other orders. The quantity shown reflects this metric when a customer sees a product available to buy directly from Amazon. It’s important to note that this metric differs from “Sellable Units,” which doesn’t factor in known demand when determining inventory counts.

Real-world Use Cases For Retail API Data

We have included a collection of thought-starter SQL queries for the Retail API data. These queries aim to provide a starting point for understanding how the data can be used to drive insights that inform efficient business growth.

Unleashing Business Insights With Amazon Rapid Retail Analytics

Say Goodbye to Manual Processes with Amazon’s Retail Rapid Analytics API

Traditionally, Vendors have had to rely on manual Amazon Vendor Central Retail Analytics report downloads or legacy EDI services to access their sales data from retailers. These methods were often time-consuming and prone to errors, as they required manual generation and downloading of reports. This manual process resulted in delayed insights and an inability to make timely business decisions.

Amazon’s Retail Rapid Analytics API is a significant shift for Vendors, providing new levels of speed and automation previously unavailable. By leveraging high-velocity data, Vendors can gain deeper insights into their business performance, make more informed decisions, and drive growth and profitability.

RocketBike Soars with Real-Time Rapid Retail Analytics Insights

RocketBike, one of the early beta testers, was thrilled to compare the new API against the traditional data access methods. They noted that Vendor real-time data provides unprecedented levels of automation and accelerates the velocity of understanding vendor business performance.

As Michael Swenson, the CMO at Rocketbike, puts it;

“We use Vendor real-time data to drive growth and profitability for our clients by gaining insights into sales, traffic, and inventory faster than ever before.”

Fast Access to ASIN Level Sales, Inventory, and Traffic Data

There are three new data sets available to Vendors:

• Real-time Sales: This metric supports the Ordered Units and Ordered Revenue, giving you access to sales data in real time for the US, CA, MX, BR, UK, DE, FR, IT, ES, NL, PL, SE, BE, EG, TR, SA, AE, IN, SG, AU, JP.
• Real-time Inventory: This metric supports the Quantites and Sku, providing inventory data in real time for the US, CA, MX, BR, UK, DE, FR, IT, ES, NL, PL, SE, BE, EG, TR, SA, AE, IN, SG, AU, JP.
• Real-time Traffic: This metric supports the Views, Buy Box Percentage, and Sessions, allowing you to track your traffic in real time for the US, CA, MX, BR, UK, DE, FR, IT, ES, NL, PL, SE, BE, EG, TR, SA, AE, IN, SG, AU, JP.

Check out our Amazon Rapid Retail Analytics API documentation for a deep dive.

Seamless, Code-free Rapid Retail API Automation Unleashed

Openbridge collaborated with Amazon in a closed beta to provide automated data feeds, without the need for code, to quickly deliver Vendor data directly to Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena.

This fast and unified data access enabled teams to utilize their preferred analytical tools, such as Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight, for various purposes, including machine learning, business intelligence, data modeling, and online analytical processing.

Get Started Automating Amazon Rapid Retail Analytics API Data for Amazon Vendor Central — For Free.

Ditch the messy, manual report downloads for code-free automation access to the new Amazon’s Retail Rapid Analytics. Openbridge integration is a code-free, fully-automated API integration. By providing Vendors with access to high-velocity data, they can accelerate the speed at which the team can gain valuable insights that deliver data-driven growth and profit.

Sign up for a 30-day free trial of our Amazon Rapid Retail Analytics API code-free automation.

References

• The power of hourly insights: Introducing rapid retail analytics
• Amazon Rapid Retail Analytics Connector | Openbridge Help Center
• Report Type Values

---

Amazon Rapid Retail Analytics: New API for Real-time Metrics was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/5gJ2NmR
via IFTTT

Unleashing Business Insights With Amazon Rapid Retail Analytics

Rapid Retail Analytics SQL Queries

Real-world SQL queries to jumpstart your retail data analysis efforts

In the ever-evolving landscape of e-commerce, harnessing the power of data is crucial for businesses striving to drive efficient growth. As an Amazon Vendor, or Seller, you can access new near real-time Amazon Rapid Retail data feeds that unlock actionable insights and enable informed data-driven decision-making.

What Can I Do With Amazon Rapid Retail Analytics Data?

Rapid Retail Analytics provides a view of ASIN-level sales, traffic, and inventory performance on the Amazon platform. This is the first time Amazon has offered this data type to Vendors, enabling new paths for data-driven decisions that improve performance and stay ahead of the competition.

Here are five areas of opportunity this new retail data will unlock. There are more areas of opportunity, but these are the obvious and immediate areas;

1. Identifying Top Selling Products: One of the fundamental aspects of driving growth is knowing which products perform exceptionally well. We can use SQL queries to uncover the top-selling ASINs (Amazon Standard Identification Number) based on sales volume and revenue. This information allows businesses to allocate resources effectively, prioritize marketing efforts, and identify potential areas for expansion.
2. Analyzing Sales Trends Over Time: Tracking sales performance over time is crucial for identifying patterns, seasonality, and overall growth trajectory. By employing SQL queries, we can generate insightful visualizations that illustrate the daily sales trends, empowering businesses to make data-driven decisions regarding inventory management, marketing campaigns, and resource allocation.
3. Monitoring Inventory Levels: Maintaining optimal inventory levels is essential for meeting customer demand and maximizing sales potential. SQL queries can monitor inventory levels in real-time, ensuring products are readily available while minimizing the risk of stockouts. By leveraging this data, businesses can strike the right balance between inventory holding costs and customer satisfaction.
4. Assessing Traffic and Customer Engagement: Understanding customer engagement and traffic patterns is vital to efficient business growth. By analyzing the provided traffic data, SQL queries can unveil which ASINs receive the highest number of glance views. With this information, businesses can fine-tune marketing strategies, improve product visibility, and enhance customer engagement, ultimately driving sales and expanding their customer base.
5. Combining Data Sources for Comprehensive Insights: By combining sales, inventory, and traffic data through SQL queries, businesses can gain a holistic understanding of their product performance. These integrated insights provide a complete picture of sales velocity, inventory turnover, and the impact of customer engagement on overall business growth. With this knowledge, businesses can optimize operations, identify areas for improvement, and capitalize on growth opportunities.

Whether you are a data scientist, analyst, or business owner, understanding how to utilize this data effectively can unlock opportunities for efficient business growth. We will explore how to query this data by leveraging new near real-time sales, inventory, and traffic information.

For a deeper dive into this data, see our doc Amazon Retail Analytics API.

Jump below for examples of how to query this data.

Getting Started Answering Key Performance Questions

We have included a collection of thought-starter SQL queries. These queries aim to provide a starting point for understanding how the data can be used to drive insights that inform efficient business growth.

Remember to customize the queries based on your specific business goals, timeframes, and any additional dimensions or filters you may require. Lastly, they may require slight modifications for your SQL environment.

Analyzing Sales Data:

Retrieve the total ordered units and revenue for each ASIN:

SELECT asin, SUM(ordered_units) AS total_units, SUM(ordered_revenue) AS total_revenue FROM sp_vendor_rt_sales GROUP BY asin;

Calculate the daily sales summary:

SELECT ob_date, SUM(ordered_units) AS total_units, SUM(ordered_revenue) AS total_revenue FROM sp_vendor_rt_sales GROUP BY ob_date;

Identify the top-selling ASINs based on units or revenue:

SELECT asin, SUM(ordered_units) AS total_units FROM sp_vendor_rt_sales GROUP BY asin ORDER BY total_units DESC LIMIT 10;

Analyzing Inventory Data

Calculate a daily inventory summary:

SELECT ob_date, SUM(highly_available_inventory) AS total_inventory FROM sp_vendor_rt_inventory GROUP BY ob_date;

Identify ASINs with low inventory levels:

SELECT asin, SUM(highly_available_inventory) AS total_inventory FROM sp_vendor_rt_inventory GROUP BY asin HAVING total_inventory < desired_threshold;

Analyzing Traffic Data

Retrieve the total glance views for each ASIN:

SELECT asin, SUM(glance_views) AS total_glance_views FROM sp_vendor_rt_traffic GROUP BY asin;

Retrieve the total glance views, by the hour, for each ASIN, over the past 30-days:

SELECT asin, DATE_TRUNC('hour', event_time) AS hour, SUM(glance_views) AS total_glance_views
FROM sp_vendor_rt_traffic
WHERE event_time >= NOW() - INTERVAL '30 days'
GROUP BY asin, hour;

Calculate the daily traffic summary:

SELECT ob_date, SUM(glance_views) AS total_glance_views FROM sp_vendor_rt_traffic GROUP BY ob_date;

Identify ASINs with high traffic

SELECT asin, SUM(glance_views) AS total_glance_views FROM sp_vendor_rt_traffic GROUP BY asin HAVING total_glance_views > desired_threshold;

Combining Data Sources:

Join the sales, inventory, and traffic data for a comprehensive analysis:

SELECT s.asin, s.total_units, i.total_inventory, t.total_glance_views FROM (SELECT asin, SUM(ordered_units) AS total_units FROM sp_vendor_rt_sales GROUP BY asin) s JOIN (SELECT asin, SUM(highly_available_inventory) AS total_inventory FROM sp_vendor_rt_inventory GROUP BY asin) i ON s.asin = i.asin JOIN (SELECT asin, SUM(glance_views) AS total_glance_views FROM sp_vendor_rt_traffic GROUP BY asin) t ON s.asin = t.asin;

Tableau, Power BI, Looker

You can use Rapid Retail Analytics data in tools like Tableau, Looker, Power BI, or Amazon QuickSight.

Below is a collection of views that can be created as overlays on the data. Views can be a very convenient way to encapsulate logic and make that available to any user on your team.

To create a set of views that can be used in tools like Tableau, Power BI, or Looker for time series performance reports based on the provided data feeds, you can define the following views:

Sales Performance View

This view provides aggregated sales performance metrics for each day.

CREATE VIEW vw_sales_performance AS
SELECT ob_date AS date,
       SUM(ordered_units) AS total_units,
       SUM(ordered_revenue) AS total_revenue
FROM sp_vendor_rt_sales
GROUP BY ob_date;

Inventory Performance View

This view provides aggregated inventory metrics for each day.

CREATE VIEW vw_inventory_performance AS
SELECT ob_date AS date,
       SUM(highly_available_inventory) AS total_inventory
FROM sp_vendor_rt_inventory
GROUP BY ob_date;

Traffic Performance View

This view provides aggregated traffic metrics for each day.

CREATE VIEW vw_traffic_performance AS
SELECT ob_date AS date,
       SUM(glance_views) AS total_glance_views
FROM sp_vendor_rt_traffic
GROUP BY ob_date;

Combined Performance View

This view combines sales, inventory, and traffic metrics for each day.

CREATE VIEW vw_combined_performance AS
SELECT s.ob_date AS date,
       s.ordered_units AS total_units,
       s.ordered_revenue AS total_revenue,
       i.highly_available_inventory AS total_inventory,
       t.glance_views AS total_glance_views
FROM sp_vendor_rt_sales s
JOIN sp_vendor_rt_inventory i ON s.asin = i.asin AND s.ob_date = i.ob_date
JOIN sp_vendor_rt_traffic t ON s.asin = t.asin AND s.ob_date = t.ob_date;

Exploring Relationships Between Sales, Traffic, and Inventory

The types of analysis you can perform with the data can get very sophisticated. Below are a collection of exploratory and conceptual areas of analysis.

Identify the correlation between daily sales revenue and glance views for a specific ASIN.

SELECT s.ob_date, s.ordered_revenue, t.glance_views FROM sp_vendor_rt_sales s JOIN sp_vendor_rt_traffic t ON s.asin = t.asin AND s.ob_date = t.ob_date WHERE s.asin = 'desired_asin' ORDER BY s.ob_date;

Determine the top-selling ASINs based on the correlation between ordered and highly available inventory units.

SELECT s.asin, SUM(s.ordered_units) AS total_units, SUM(i.highly_available_inventory) AS total_inventory FROM sp_vendor_rt_sales s JOIN sp_vendor_rt_inventory i ON s.asin = i.asin GROUP BY s.asin ORDER BY total_units DESC;

Calculate the average revenue per ordered unit for each ASIN monthly.

SELECT DATEPART(MONTH, s.start_time) AS month, s.asin, SUM(s.ordered_revenue) / SUM(s.ordered_units) AS avg_revenue_per_unit FROM sp_vendor_rt_sales s GROUP BY DATEPART(MONTH, s.start_time), s.asin;

Find the ASINs with the highest revenue growth rate between two consecutive months.

WITH monthly_revenue AS ( SELECT asin, DATEPART(MONTH, start_time) AS month, SUM(ordered_revenue) AS revenue FROM sp_vendor_rt_sales GROUP BY asin, DATEPART(MONTH, start_time) ) SELECT current.asin, (current.revenue - previous.revenue) / previous.revenue AS growth_rate FROM monthly_revenue current JOIN monthly_revenue previous ON current.asin = previous.asin AND current.month = previous.month + 1 ORDER BY growth_rate DESC;

Identify the day of the week with the highest average revenue for a specific ASIN.

SELECT DATEPART(WEEKDAY, s.start_time) AS weekday, AVG(s.ordered_revenue) AS average_revenue 
FROM sp_vendor_rt_sales s WHERE s.asin = ‘desired_asin’ 
GROUP BY DATEPART(WEEKDAY, s.start_time) 
ORDER BY average_revenue DESC;

These strategies leverage multiple tables to analyze and derive insights from the data. Remember to replace desired_asin with the specific ASIN you want to analyze.

Find the ASINs with the highest revenue growth rate between two consecutive days:

WITH daily_revenue AS (
SELECT asin, ob_date, SUM(ordered_revenue) AS revenue
FROM sp_vendor_rt_sales
GROUP BY asin, ob_date
)
SELECT current.asin, (current.revenue - previous.revenue) / previous.revenue AS growth_rate
FROM daily_revenue current
JOIN daily_revenue previous ON current.asin = previous.asin AND current.ob_date = DATEADD(DAY, 1, previous.ob_date)
ORDER BY growth_rate DESC;

Identify changes in the “highly available” metric that impact sales negatively. The SQL query calculates the negative impact on sales when the “highly available” metric decreases:

SELECT
  i.start_time,
  i.asin,
  i.highly_available_inventory - lag(i.highly_available_inventory) OVER (PARTITION BY i.asin ORDER BY i.start_time) AS inventory_change,
  s.ordered_units - lag(s.ordered_units) OVER (PARTITION BY s.asin ORDER BY s.start_time) AS sales_change
FROM
  sp_vendor_rt_inventory i
JOIN
  sp_vendor_rt_sales s ON i.asin = s.asin AND i.start_time = s.start_time
WHERE
  inventory_change < 0 AND sales_change < 0;

Openbridge Report Automation

Openbridge Rapid Retail automation is a code-free, fully-automated integration to the Rapid Retail API. Teams ensure they get up-to-date and accurate data from official, certified Amazon APIs. With data stored in a unified data warehouse like Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena, you can combine different Amazon datasets to gain a more holistic view of the business.

Your team can then leverage best-in-class analytics and business tools like Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight.

Get Started Automating Rapid Retail Analytics — For Free

Whether it’s identifying top-selling products, analyzing sales trends, monitoring inventory levels, assessing customer engagement, or combining multiple data sources, the power lies in understanding, interpreting, and acting upon the insights derived from the data.

Embrace the data, harness its potential, and embark on your journey to be a more data-driven organization.

Sign up for a 30-day free trial of our Amazon Rapid Retail Report automation.

---

Unleashing Business Insights With Amazon Rapid Retail Analytics was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/LorKqYM
via IFTTT

Amazon Brand Metrics: Introducing New-To-Brand Analytics

Amazon Advertising Brand Metrics

Amazon Advertising now provides accessible new-to-brand metrics for Sponsored Brands and Sponsored Display campaigns, allowing for the measurement and optimization of ongoing campaigns. These metrics aim to facilitate the development of marketing strategies that can effectively increase customer acquisition and foster brand loyalty.

What is “New To Band”?

An order is considered new-to-brand if the customer has not purchased from that particular brand during that timeframe.

So how are these metrics calculated? Amazon will model the customer’s purchase history for the past 12 months to determine whether an order is new to a brand. The list below details available new-to-brand metrics for sponsored ads campaigns and keywords.

1. New-to-brand orders: The count of initial orders for products within the brand during a 1-year observation period.
2. % of orders new-to-brand: The percentage of total orders that are new-to-brand orders. This metric is calculated by dividing the number of new-to-brand orders by the total number of orders and multiplying by 100.
3. New-to-brand sales: The total sales (in local currency) generated by new-to-brand orders.
4. % of sales new-to-brand: The percentage of sales (in local currency) attributed to new-to-brand orders. This metric is calculated by dividing the total sales of new-to-brand orders by the total sales and multiplying by 100.
5. New-to-brand units: The number of units purchased through new-to-brand orders.
6. % of units new-to-brand: The percentage of total units acquired through new-to-brand orders. This metric is calculated by dividing the number of new-to-brand units by the total number of units and multiplying by 100.
7. New-to-brand order rate (for Sponsored Brands only): The ratio of new-to-brand orders to the number of clicks. This metric is calculated by dividing the number of new-to-brand orders by the number of clicks and multiplying by 100.

How Can New To Brand Metrics Be Utilized?

Leveraging new-to-brand metrics can optimize Sponsored Brands and Sponsored Display campaigns, facilitating new customer acquisition and fostering long-term brand relationships.

Below are some recommendations for optimizing customer acquisition by identifying or creating Sponsored Brands or Sponsored Display campaigns with a specific focus on this goal:

1. After the campaign has gathered a minimum of 14 days’ worth of data, closely analyze the campaign’s new-to-brand keyword metrics. Filter keywords based on acceptable ROAS (Return on Advertising Spend) or ACOS (Advertising Cost of Sales) values.
2. From the resulting set of keywords, identify those with the highest number of new-to-brand orders. These keywords represent potential targets for driving new-to-brand orders.
3. Evaluate the metrics related to new-to-brand units and sales, as they may reveal keywords that generate new-to-brand orders with higher price points and basket sizes.
4. Continuously monitor the campaign’s new-to-brand performance over time using the performance dashboard, making necessary adjustments as required.

These metrics can also apply to broader, strategic efforts to refine positioning, competitive pressures, and product;

1. Identifying Untapped Market Segments: New-to-brand metrics can provide valuable insights into customer behavior and preferences. Businesses can uncover untapped market segments by analyzing the characteristics and demographics of customers who make new-to-brand purchases. This information can guide marketing and advertising strategies to target these specific segments, tailoring messages and campaigns to attract new customers more likely to engage with the brand.
2. Assessing Competitive Positioning: New-to-brand metrics can also offer insights into a company’s competitive positioning. By comparing the percentage of new-to-brand orders and sales against competitors in the same industry, businesses can gauge their market penetration and identify areas for improvement. If the percentage of new-to-brand orders is lower than competitors, it may indicate a need to enhance brand awareness, expand marketing efforts, or improve product differentiation to attract new customers.
3. Influencing Product Development: New-to-brand metrics can inform product development decisions by highlighting customer preferences and purchase patterns. By analyzing the new-to-brand units and sales metrics for different products, businesses can identify which products are more successful in attracting new customers. This information can guide product expansion or innovation strategies, focusing on developing new offerings that align with customer preferences and have a higher potential for attracting new customers to the brand.

Where Can These Metrics Be Found?

New-to-brand data has been available for Sponsored Brands campaigns since November 1, 2018. However, if a start date before November 1, 2018, is chosen, the new-to-brand metrics will be calculated based on November 1, 2018, as the starting point.

As for Sponsored Display, new-to-brand metrics have been available for Sponsored Display since May 1, 2021. These metrics are available for seller and vendor product targeting campaigns and seller and vendor audience targeting campaigns.

Amazon Brand Metrics Report Data Automation

Take control of your Amazon business with Openbridge, the solution that automates Amazon Brand Metrics API integrations and centralizes data storage in a unified data warehouse. Say goodbye to manual report downloads and errors in merging and tracking reports.

By leveraging automation, Openbridge eliminates the need for cumbersome manual downloads, ensuring you have up-to-date and accurate data directly from Amazon APIs. With data securely stored in a unified data warehouse such as Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena, combining various Amazon reports becomes a breeze, allowing you to gain a comprehensive and holistic view of your business.

Experience the benefits of an automated and streamlined process that grants you access to all your crucial data in one trusted and private location. Empower your team to utilize best-in-class analytics and business tools like Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight.

Take action now and optimize your Amazon Seller or Vendor operations with Openbridge. Simplify your data management and unlock the full potential of your business insights.

Sign up for a 30-day free trial of our Amazon Advertising Brand Metrics Report automation.

---

Amazon Brand Metrics: Introducing New-To-Brand Analytics was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/rDnYAyC
via IFTTT

Brand Analytics Amazon: Insights To Optimize Sales Performance

Brand Metrics provides a new measurement solution that quantifies opportunities for your brand at each stage of the customer journey in Amazon's store. Brands can understand the value of shopping engagements that impact sales in a journey.

Brand Metrics helps you:

• Understand your brand performance. Brand metrics measure the number of actual on-Amazon shopper engagements.
• Measure the impact of your upper and mid-funnel tactics and see how they contribute to shoppers moving through the purchase journey.
• Evaluate engagement metrics to understand the value of your branded intent and how brand purchasers generate additional sales in the 12 months following purchase.
• Monitor your performance relative to your category and peers at each stage of the purchase journey and over time.
• Optimize your marketing and advertising efforts in Amazon's stores to engage more shoppers and build your brand.

The metrics offer critical insights for brands to understand the value of shopping engagements during the Amazon purchase process. The metrics allow you to measure historical sales following a consideration event or purchase.

What Brand Metrics Are Available?

Brand Metrics offer awareness and consideration indicators that compare performance against peers. Amazon also quantifies the number of customers in your awareness and consideration marketing funnel.

The metrics include all shopping engagements with your brand on Amazon, not just ad-attributed engagements. The following is a collection of metrics Amazon makes available:

• engagedShopperRateLowerBound — Lower bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchase from in the selected time frame divided by the total unique shoppers with 1+ detail page views in the selected category
• engagedShopperRateUpperBound — Upper bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchase from in the selected time frame divided by the total unique shoppers with 1+ detail page views in the selected category
• engagedShopperRateCategoryMedianLowerBound — Lower bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchased from in the selected time frame divided by the total unique shoppers with 1+ detail page view in the selected category for the median peers
• engagedShopperRateCategoryMedianUpperBound — Upper bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchase from in the selected time frame divided by the total unique shoppers with 1+ detail page view in the selected category for the median peers
• engagedShopperRateCategoryTopPerformersLowerBound — Lower bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchase from in the selected time frame by the total unique shoppers with 1+ detail page view in the selected category for the top 95th-99th percent of peers
• engagedShopperRateCategoryTopPerformersUpperBound — Upper bound on the percentage of unique shoppers in a category that your brand has driven an engagement with or purchase from in the selected time frame divided by the total unique shoppers with 1+ detail page view in the selected category for the top 95th-99th percent of peers
• customerConversionRate — percentage of shoppers moving from “considering” [Brand Name] in the [categoryNodePath] to “purchased” in the [lookBackPeriod]
• customerConversionRateCategoryMedian — the percentage of shoppers moving from "considering" the peer median in the [categoryNodePath] to "purchased" in the [lookBackPeriod]
• customerConversionRateCategoryTopPerformers — the percentage of shoppers moving from "considering" the average of the top 95th-99th percent of peers in the [categoryNodePath] to "purchased" in the l[lookBackPeriod]
• newToBrandCustomerRate — share of customers that had not purchased [Brand Name] products in the last 12 months but did so in the [lookBackPeriod]
• newToBrandCustomerRateCategoryMedian — share of customers that had not purchased peer median products in the last 12 months but did so in the [lookBackPeriod]
• newToBrandCustomerRateCategoryTopPerformers — share of customers that had not purchased an average of the top 95th-99th percent of peers' products in the last 12 months but did so in the [lookBackPeriod]
• brandedSearchesOnly — shoppers that searched for [Brand Name] but did not further engage or purchase from [Brand Name] and [categoryNodePath] in the [lookBackPeriod]
• brandedSearchesCategoryMedian — shoppers that searched for the peer median but did not further engage or purchase from peer median and [categoryNodePath] in the [lookBackPeriod]
• brandedSearchesCategoryTopPerformers — shoppers that searched for the avg. of the top 95th — 99th percent of peers and [categoryNodePath] but did not further engage or purchase from the Top Performers in the [lookBackPeriod]
• viewedDetailPageOnly — shoppers that viewed detail page only of [Brand Name] in [categoryNodePath] in [lookBackPeriod]
• viewedDetailPageCategoryMedian — shoppers that viewed the detail page only of peer median in [categoryNodePath] in [lookBackPeriod]
• viewedDetailPageCategoryTopPerformers — shoppers that viewed the detail page only of avg. of the top 95th — 99th percent of peers in [categoryNodePath] in [lookBackPeriod]
• viewedDetailPageOnlyReturnOnEngagement — The average number of sales per customer in this state 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population moved over the last year for your brand in the selected category.
• viewedDetailPageROECategoryMedian — The average sales of each customer in this state for the peer median 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population moved over the last year for your brand in the selected category.
• viewedDetailPageROECategoryTopPerformers — The average sales of each customer in this state for the 95–99 percent peers 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population moved over the last year for your brand in the selected category.
• brandedSearchesAndDetailPageViews — shoppers that searched brand and viewed the detail page of [Brand Name] in [categoryNodePath] in [lookBackPeriod]
• brandedSearchesAndDetailPageViewsCategoryMedian — shoppers that searched brand and viewed the detail page of peer median in [categoryNodePath] in [lookBackPeriod]
• brandedSearchesAndDetailPageViewsCategoryTopPerformers — shoppers that searched brand and viewed the detail page of avg. of the top 95th — 99th percent of peers in [categoryNodePath] in [lookBackPeriod]
• brandedSearchesAndDetailPageViewsReturnOnEngagement — The average sales of each customer in this state 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• brandedSearchesAndDetailPageViewsROECategoryMedian — The average sales of each customer in this state for the peer median 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• brandedSearchesAndDetailPageViewsROECategoryTopPerformers — The average amount of sales each customer in this state for the 95–99 percent peers 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• addToCarts — shoppers that added [Brand Name] to the cart in [categoryNodePath] in [lookBackPeriod]
• addToCartsCategoryMedian — shoppers that added the peer median to the cart in [categoryNodePath] in [lookBackPeriod]
• addToCartsCategoryTopPerformers — shoppers that added the avg. of the top 95th — 99th percent of peers to the cart in [categoryNodePath] in [lookBackPeriod]
• addToCartsReturnOnEngagement — The average sales of each customer in this state 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• addToCartsROECategoryMedian — The average sales per customer in this state for the peer median 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• addToCartsROECategoryTopPerformers — The average sales of each customer in this state for the 95–99 percent peers 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population moved over the last year for your brand in the selected category.
• brandCustomers — 0%-90% of sales contributing shoppers for [Brand Name] who purchased in [categoryNodePath] in [lookBackPeriod]
• brandCustomersCategoryMedian — 0%-90% of sales contributors for the peer median who purchased in [categoryNodePath] in [lookBackPeriod]
• brandCustomersCategoryTopPerformers — 0%-90% of sales contributors for avg. of the top 95th — 99th percent of peers who purchased in [categoryNodePath] in [lookBackPeriod]
• brandCustomersReturnOnEngagement — The average sales of each customer in this state 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• brandCustomersROECategoryMedian — The average sales per customer in this state for the peer median 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• brandCustomersROECategoryTopPerformers — The average sales of each customer in this state for the 95–99 percent peers 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• high-value customers — top 10% of sales contributing shoppers for
• highValueCustomersCategoryMedian — top 10% of sales contributing shoppers for peer median in [categoryNodePath] in [lookBackPeriod]
• highValueCustomersTopPerformers — top 10% of sales contributing shoppers for the top 95th — 99th percent of peers in [categoryNodePath] in [lookBackPeriod]
• highValueCustomersReturnOnEngagement — The average sales of each customer in this state 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• highValueCustomersROECategoryMedian — The average sales per customer in this state for the peer median 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• highValueCustomersCategoryTopPerformers — The average sales of each customer in this state for the 95–99 percent peers 12 months ago drove over one year. Calculate by taking the total original population 12 months ago by the total sales this population drove over the last year for your brand in the selected category.
• awarenessIndex — Awareness Index measures actual on-Amazon audience signals reflecting awareness for your brand in the short and long term across the entire on-Amazon audience. The percentile lets you know how your brand's awareness activity compares to your peers.
• considerationIndex — Consideration Index measures actual on-Amazon audience signals reflecting consideration for your brand in the short and long term across the entire audience. The percentile lets you know how your brand's consideration activity compares to your peers.
• salesIndex — Sales Index represents actual on-Amazon sales in the short term and long term across the whole on-Amazon audience. The percentile lets you know how your sales compare to your peers.

Who Is Eligible For Amazon Brand Metrics?

Brand Metrics is available in beta to professional sellers enrolled in Amazon Brand Registry and vendors selling Amazon products in the following markets: United States, Canada, United Kingdom, Germany, France, Italy, Spain, and Japan. Please note that Brands with less than 20 orders a month will not have access to Brand Metrics.

Amazon Brand Analytics Report Data Automation

Openbridge allows Amazon sellers to streamline the data management process by automating Amazon Brand Analytics API integrations and storing data in a unified data warehouse. This service saves sellers time manually downloading reports and reduces errors in merging and tracking downloaded reports.

Automation eliminates manual download wrangling, ensuring up-to-date and accurate data straight from Amazon APIs. With data stored in a unified data warehouse like Amazon Redshift, Amazon Redshift Spectrum, Google BigQuery, Snowflake, Azure Data Lake, and Amazon Athena, you can combine different Amazon reports is easier to gain a more holistic view of the business.

The automated, streamlined process enables sellers to access all their important data in one private, trusted destination. Your team can then leverage best-in-class analytics and business tools like Google Data Studio, Tableau, Microsoft Power BI, Looker, or Amazon Quicksight.

Sign up for a 30-day free trial of our Brand Analytics Amazon Insights Report automation.

---

Brand Analytics Amazon: Insights To Optimize Sales Performance was originally published in Openbridge on Medium, where people are continuing the conversation by highlighting and responding to this story.

from Openbridge - Medium https://ift.tt/BCTUuN8
via IFTTT