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;
- 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.
- 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.
- 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.
- 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.
- 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