Understanding which SKUs attract the most new customers can be precious for Amazon sellers. Analyzing customer purchases and identifying which SKUs bring in the most customers helps Amazon sellers decide how to advertise, promote, and set product prices to boost sales and better meet new customer needs.
Uncover Key Insights Into Your Product
Defog’s primary benefit is its connection to Amazon Seller Central, automatically bringing sales data into Google Sheets. This allows sellers to quickly track which products attract the most new customers.
This article will guide you through creating a bar chart that calculates the percentage of new customers purchasing each of the top 10 first SKUs bought. With simple formula adjustments, this chart can help you profile customer behavior across various metrics related to customers and their purchases.
We will create a chart like the one below.
One good thing about doing this in Defog’s spreadsheet is that as soon as Defog updates the data, the chart we will create below will be automatically updated.
To create the chart, we will use two columns from OrdersTable: Column I (SKU) and Column AA (customer ID).
Calculating the First SKU Bought by a New Customer
Step 1: Create a new sheet by clicking the + icon and renaming it to First SKU bought.
Step 2: Use the formula below to extract distinct customer IDs from the OrdersTable in this new sheet.
Copy and paste the formula on A1.
=UNIQUE(filter(OrdersTable!AA:AA,OrdersTable!AA:AA<>""))
Step 3: Use the formula below to create the list of SKUs bought per unique customer ID from the OrdersTable. Copy and paste it into B1.
={"SKUs bought";BYROW(A2:A,LAMBDA(x, if(x<>"",TEXTJOIN(", ", TRUE, UNIQUE(FILTER(OrdersTable!I:I, (OrdersTable!AA:AA = x) * (OrdersTable!I:I <> "")),"")))))}
Step 4: Use the formula below to retrieve the first SKU bought from the list created in Step 3. Copy and paste it into cell C1.
={"First SKU Bought";arrayformula(if(B2:B<>"",index(split(B2:B,", "),0,1),""))}
Calculating the Percentage of New Customers by First Purchased SKU
Step 5: Use the formula below to extract distinct SKUs from the OrdersTable in the existing sheet
Please copy and paste it into E1.
=UNIQUE(OrdersTable!C:C)
Step 6: Write the title Customers First Bought SKU in F1. This column will count the number of customers who have bought the SKU.
Copy and paste it into cell F2.
=ARRAYFORMULA(IF(E2:E="","",COUNTIF(C2:C,E2:E)))Step 7: Write the title Percentage of total in G1. Use the formula below to calculate the percentage of customers who first bought each SKU.
Please copy and paste it into G2.
=ARRAYFORMULA(IF(E2:E="","",F2:F/SUM(F:F)))
Creating Top 10 Products bought
Step 8: Write Serial No. in H1. To analyze the Top 10 products, enter the numbers 1 to 10 in H2 under Serial No.
Step 9: Write the title Top 10 SKU in Column I, Top 10 SKU Counts in Column J, and Top 10 percentage in Column K. Use the formulas below to calculate the Top 10 SKU, Counts, and Total Percentage of SKUs bought.
Please copy and paste it into I2.
=FILTER(E2:G,G2:G=LARGE(G2:G,H2))
The rest of the columns will automatically fill in; the result should look like the image below.
Visualizing the Percentage of Customers by First Product Purchased Using a Bar Chart (Histogram)
Step 10: To create a chart based on the top 10 SKUs bought first by new customers, select the table we made from I to H. Then, click Insert > Chart. Go to the Chart Editor sidebar that appears on the right. Under the Setup tab, select Chart Type and choose Column Chart from the dropdown menu.
Step 11: Go to the Setup tab and set the X-axis to the Top 10 SKU column to display the products along the horizontal axis. For the Y-axis, select only the Top 10 percentage column. Remove the other columns from the chart by clicking the three-dot menu and then clicking Remove.
Step 10: In the same Chart Editor sidebar, click the three-dot menu next to the Top 10 percentage column and then click Add labels. Choose the Top 10 percentage column as the label. Your chart setup will look like the one below.
Your chart should look similar to the one below:
Suppose you sell in multiple countries within the same Amazon region (such as North America, Europe, or the Far East). In that case, Defog will consolidate data from all marketplaces into a single OrdersTable. Analyzing customer behavior on the first product bought per country may be better, as there may be differences in customer behavior and product offerings between countries.
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 or for the consequences of using the data, formulas, and charts provided.
