Businesses benefit from knowing who their customers are and planning based on that information. One cool thing to learn about customers is how they fall into different spending ranges. This can help businesses better determine what products to offer and how to price them.
Uncover Key Insights into Your Customer’s Behavior
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 customer spending changes over time or every time they change pricing and product offerings.
This article will guide you through creating an interactive chart that calculates the percentage of customers across different spending ranges. You’ll be able to customize the ranges you want to visualize. With some formula editing, you may use the same chart created here to profile the customers using different metrics like the number of units bought.
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 L (item price) and Column AA (customer ID).
Calculating Total Spending per Customer
Step 1: Create a new sheet by clicking the + icon and renaming it to Customer Profile: Spending.
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: Write the title Total spending on B1 and use the formula below to calculate the total spending 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!L:L),"")
Defining Spending Ranges
Step 4: To analyze the percentage of customers per spending range, we must first 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 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 Spending Chart
Step 5: Write the title Number of Customers in F1. This column will calculate the number of customers who have spent within each specified 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 spending 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. Copy and paste the formula in G2, then drag it down to G10 to apply it across all spending ranges. Click the % symbol on Google Sheets’ ribbon to show the decimal numbers as percentages.
=F2/sum(F:F)
Step 7: Write the title Spending Ranges in H1. In Cell H2, enter the formula below to define the spending ranges that will serve as labels for our chart. Copy and paste the formula on F2 and then drag it down to apply it across all spending ranges.
=if(D2=0,"< $"&E2,if(E2>10000,">= $"&D2,">= $"&D2&" < $"&E2))
Visualizing Percentage of Customers per Spending Range With a Bar Chart
Step 8: To create a chart based on spending ranges and their frequencies, select the table we made from D1 to H10. 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 Spending Ranges column to display each spending 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 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. Analyzing customer spending by country or currency is crucial, as you cannot combine spending amounts in different currencies.
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.
