Understanding customer ordering behavior, like the number of units purchased, helps businesses tailor their product availability and pricing. By identifying key quantity ranges, companies can better align their offerings with customer demand and improve inventory planning.
The most significant benefit of Defog is that it connects to Amazon Seller Central, automatically retrieving valuable data to Google Sheets. Sellers may then use the data to monitor how many units their customers buy over time.
This article will guide you through creating an interactive chart that calculates the percentage of customers who buy various quantities of units from your store. You’ll also be able to customize the ranges of units you want to visualize. With a few formula tweaks, the same chart can be adapted to profile the customers using other metrics, such as customer spending.
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 J (Quantity Ordered) and Column AA (Customer ID).
Calculating Total Quantity Purchased per Customer
Step 1: Create a new sheet by clicking the + icon and renaming it to Customer Profile: Units Ordered.
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 and press Enter. The formula will auto-fill the column.
=UNIQUE(filter(OrdersTable!AA:AA,OrdersTable!AA:AA<>""))
Step 3: Write the title Total Units Ordered on B1 and use the formula below to calculate the total Units Ordered for each unique customer ID from the OrdersTable. Copy and paste the formula on B2:
=ARRAYFORMULA(IF(A2:A<>"",SUMIF(OrdersTable!AA:AA,A2:A,OrdersTable!J:J),""))
Defining The Units Ordered Ranges
Step 4: To analyze the percentage of customers per range of units ordered, we should define the ranges. To do that, we will create two columns, one with the minimum value and one with the maximum value in each range. We will paint the cells’ backgrounds in light blue to indicate that those cells could be changed to make other analyses.
In Column D, write the title Min; in Column E, write the title Max. Paint the cells’ background, and write the number as indicated below.
Creating Columns for the Chart
Step 5: Write the title Number of Customers in F1. This column will calculate the number of customers who have ordered a specified number of units within each range set in Columns D and E.
Copy and paste the formula below on F2 and then drag it down to F10 to apply it across the Quantity ranges.
=countifs(B:B,">"&D2,B:B,"<="&E2)
Step 6: Write the title Percentage of Total in G1. In Cell G2, enter the formula below to calculate the percentage of customers from the total number of unique customers. Drag the formula in G2 down to G10 to apply it across all Quantity ranges. Click the % symbol on Google Sheets’ ribbon to show the decimal numbers as percentages.
=F2/sum(F:F)
Step 7: Write the title Units Ordered Ranges in H1. In Cell H2, enter the formula below to define the Quantity ranges that will serve as labels for our chart. Drag the formula on F2 down F10 to apply it across all ranges.
=if(D2=0,"= "&E2,if(E2>10000,"> "&D2,"> "&D2&" <= "&E2))
Visualizing the Percentage of Customers per Ranges of Units Ordered Using a Bar Chart
Step 8: To create a chart based on the ranges of ordered units and their frequencies, select the table we made from F1 to H7. Then, click Insert > Chart, creating a chart in your sheet. 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 9: Go to the Setup tab and set the X-axis to the Units Ordered Ranges column to display each Unit range along the horizontal axis. For the Y-axis, select only the Number of Customers 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 Number of Customers and then click Add labels. Choose Percentage of Total as the label. Your chart setup will look like the one below.
After some cosmetic formatting, your sheet should look similar to the one below:
If you sell in multiple countries within the same Amazon region (such as North America, Europe, or the Far East), Defog will consolidate data from all marketplaces into a single OrdersTable. If you want to analyze customer behavior by country, you must filter the customers accordingly in column A.
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.
