Amazon sellers benefit from knowing how customers behave and plan based on that information. One cool thing to learn is the percentage of customers who buy only once, twice, or some other number of times. This can help retailers precisely calculate how much it costs to make a sale and how much, on average, a customer spends in the store.

This article will answer the question: How many customers buy this many times at my store?

Uncover Key Insights Into Your Customer’s Behavior

The primary advantage of Defog is its connection to Amazon Seller Central, which allows for the automatic retrieval of valuable data into Google Sheets. Sellers may then use the data to understand and monitor customer behavior.

This article will guide you through creating an interactive chart that calculates the percentage of customers by the number of times they bought in the store. You can customize the number of orders you want to visualize. With some formula editing, you may use the same chart created here to profile the customers using metrics like the number of units bought or total spend.

We will create a chart like the one below:

One advantage of doing this in Defog’s spreadsheet is that the chart will be automatically updated as soon as Defog updates the data.

To create the chart, we will use two columns from OrdersTable: Column D (Amazon Order ID) and Column AA (Customer ID).

Calculating Total Orders per Customer

Step 1: Create a new sheet by clicking the + icon and renaming it to Customer Profile: No of Orders.

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 Total Orders on B1 and use the formula below to calculate the total orders for each unique customer ID from the OrdersTable. Copy and paste the formula on B2:

=COUNTA(UNIQUE(FILTER(OrdersTable!D:D, OrdersTable!AA:AA = A2)))

Defining Orders Ranges

Step 4: To analyze the percentage of customers per number of orders 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 can 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 Ordering Chart

Step 5: Write the title Number of Customers in F1. This column will calculate the number of customers with orders within each specified range set in Columns D and E.

Copy and paste the formula below on F2, then drag it down to F7 to apply it across the Ordering 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 G7 to apply it across all Ordering ranges. Click the % symbol on Google Sheets’ ribbon to show the decimal numbers as percentages.

=F2/sum(F:F)

Step 7: Write the title No of Orders Range in H1. In Cell H2, enter the formula below to define the 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 Ordering Range With a Bar Chart

Step 8: To create a chart based on the number of order ranges 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 No of Orders Range column to display each 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.

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; we suggest you do this analysis by country as market particularities may result in differences in customer behavior.

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.

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.