Sometimes, a store may struggle to monitor all its products at once. If this is the case for your company, it likely uses some categorization method based on family, size, color, or volume. Tracking sales by category is a good business practice, allowing you to make informed decisions from a broader perspective. For example, you might determine that a particular category requires promotions or is no longer worth selling. This article will analyze product category performance using OpenAI’s Large Language Model (LLM).

Artificial Intelligence (AI) can revolutionize Amazon sellers’ decision-making by simplifying sales trend analysis, forecasting demand, and streamlining operations. But AI models are only as good as the data they are fed.

Defog is a powerful tool that facilitates data analysis for Amazon sellers by seamlessly integrating with Amazon Seller Central and Amazon Advertising. It automatically retrieves and organizes sales data into Google Sheets, enabling sellers to uncover actionable insights quickly. One crucial analysis sellers must conduct is tracking category-level performance over time to identify shifts in demand and seasonal trends. By leveraging Defog and AI-driven prompts, sellers can quickly analyze monthly sales patterns, optimize inventory planning, and align their strategies with customer preferences.

This is what the results of this article will look like:

One good thing about doing this in Defog’s spreadsheet is that as soon as Defog updates the data if you repeat the prompt, you will have the latest information about your customer behavior as the answer. 

Create product categories

Defog and the AI model must know your products’ categories to create a category analysis. Let’s start by creating a table to add each product’s category.

Step 1: On your desktop browser, open your Defog spreadsheet. To create a new worksheet for the categories, click on the plus + symbol at the bottom left of the spreadsheet and change the new sheet name to Categories. The Categories sheet will have a table where you can add your products and their respective categories.     

Create the Categories table.

Step 2: In the Category sheet, use the UNIQUE formula to extract distinct values from the SKU column in the OrdersTable. This will ensure that the Category table gets automatically updated with new SKU entries as they are added to the OrdersTable. Copy and paste the formula below to the A1 cell on the new Categories sheet.

=UNIQUE(OrdersTable!I:I)

Step 3: Next, you can manually enter categories for each SKU in column B in the Categories sheet. We usually paint cells with a blue background to remind us that someone should type in the values. The SKUs in column A will be automatically populated by the UNIQUE formula in Step 2 above, and column B will remain editable for you to assign your company’s categories, as in the example shown in the table below.

Add each SKU’s category. 

Step 4: Go to the OrdersTable and select the column AH. Click on the mouse button and click on + Insert 1 column right. Edit the title in the new column (AI) as Category. 

This column will map the SKUs from the OrdersTable to their corresponding categories from the new Categories table.

Each row in the OrdersTable has an SKU. We will use a formula to look up the SKU’s category and add it to the new AI column. Copy and paste the formula below into cell AI2.

=ARRAYFORMULA(IF(I2:I<>"", VLOOKUP(I2:I, Categories!A:B, 2, FALSE), ""))

Insert the category lookup formula.

Congrats, you did it! You now have a new column with each order’s category.

ChatGPT – Setting Up Google Sheets Integration

Step 5: To facilitate the data upload from Defog to ChatGPT, we suggest you connect your ChatGPT with Google Drive. Here is how to set up this integration. Start by clicking on the paper clip icon and then click on Connect to Google Drive (see images below):

Follow the login and authorization process presented by Google Drive. Then, you can upload data from Google Drive to ChatGPT.

ChatGPT – Upload Your Defog from Google Drive

Step 6: Now that you have connected your Google Drive with ChatGPT, click on the paper clip icon again and then click Add from Google Drive.

Choose your Defog on Google Drive. In this example, we will use a Defog named “Joe’s Coffee – Defog.”

Using ChatGPT to generate a table with the number of units purchased by category per month

Step 3: Copy and paste the prompt below into your ChatGPT and press send.

I need help analyzing the OrdersTable worksheet. The goal is to generate a table like this: each row is dedicated to one product Category, and the columns should have units purchased in each Category by the orders' month/year.  Please create the table as described from June 2024 up to August 2024. Sort the final table columns by the month/year in ascending order. Show the number of units ordered per month in the last row (total row) and the number of units ordered per category in the last column (total column).

After ChatGPT analyzes the data, you will get a result like the one below.

Visit this article if you want to create a dashboard in Google Sheets to track monthly sales trends and analyze category performance without relying on ChatGPT.

If you want to learn what a particular column stands for on Defog’s tables, please visit our glossary.

Thank you for reading this post. If you still haven’t used Defog, you can do so for free here.

If you need any help, we are here for you.

Disclaimer: Defog is not responsible for any decisions made by the reader of this post regarding the data, formulas, and visuals provided.