Sellers can significantly benefit from understanding their customers’ profiles and behaviors. By gathering information about them and how they shop, such as which products were ordered, the number of orders and total spent, and refunds, sellers can create ways to improve their offerings and increase sales.
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 their customer behavior and profile.
This article will guide you through creating a detailed customer profile table that consolidates customer information based on store purchases.
We will create a table like the one below with all your store’s customers sorted by the number of orders:
One advantage of doing this in Defog’s spreadsheet is that the table will be automatically updated as soon as Defog updates the data.
We will use a few columns from OrdersTable and FinancialTable to create this table.
Adding the Customer’s Location
Step 1: Create a new sheet by clicking the + icon and renaming it to Customer: Profile.
Step 2: Use the formula below to extract distinct customer IDs from the OrdersTable in this new sheet.
In your new sheet, copy and paste the formula on N1:
=UNIQUE(FILTER(OrdersTable!AA:AA, OrdersTable!AA:AA <> ""))Step 3: Add the formula below to retrieve the customers’ country from the OrdersTable for each unique Customer ID.
Copy and paste the formula into cell O1:
= {"Country";BYROW(N2:N, LAMBDA(row, IF(row<>"", INDEX(OrdersTable!X:X, MATCH(row, OrdersTable!AA:AA, 0)), "")))}Step 4: Add the formula below to retrieve the customers’ state from the OrdersTable for each unique Customer ID.
Copy and paste the formula into cell P1:
={"State";BYROW(N2:N, LAMBDA(row, IF(row<>"", INDEX(OrdersTable!V:V, MATCH(row, OrdersTable!AA:AA, 0)), "")))}Step 5: Add the formula below to retrieve the customers’ city from the OrdersTable for each unique Customer ID.
Copy and paste the formula into cell Q1:
={"City";BYROW(N2:N, LAMBDA(row, IF(row<>"", INDEX(OrdersTable!U:U, MATCH(row, OrdersTable!AA:AA, 0)), "")))}Adding Customer Purchase Metrics
Step 6: Add the formula below to calculate the total orders for each unique Customer ID from the OrdersTable.
Copy and paste the formula on R1:
={"Number of Orders";BYROW(N2:N, LAMBDA(row, if (row<>"", COUNTA(UNIQUE(FILTER(OrdersTable!D:D, OrdersTable!AA:AA = row))),"")))}Step 7: Add the formula below to calculate the units ordered for each unique Customer ID from the OrdersTable.
Copy and paste the formula on S1:
={"Total Units Ordered"; ARRAYFORMULA(IF(N2:N<>"",SUMIF(OrdersTable!AA:AA,N2:N,OrdersTable!J:J),""))}Step 8: Add the formula below to calculate the total spending for each unique Customer ID from the OrdersTable.
Copy and paste the formula on T1:
={ "Total Spent"; ARRAYFORMULA(IF(N2:N<>"",SUMIF(OrdersTable!AA:AA,N2:N,OrdersTable!L:L),""))}Calculating the Number of Refunds per Customer
Calculate the number of refunded orders by creating another table that consolidates all refunded orders from the FinancialTable with the respective customer IDs from the OrdersTable. To make this table, we will use columns I (Category) and C (Amazon Order ID) from the FinancialTable, as well as columns D (Amazon Order ID) and AA (Customer ID) from the OrdersTable.
Step 9: Add the formula below to Z2 to get all the order IDs from refunded orders:
=unique(FILTER(FinancialTable!C:C, FinancialTable!I:I="Refunds", FinancialTable!C:C<>""))Step 10: Add the formula below to AA2 to find the customer ID for each refunded order:
=iferror(BYROW(M2:M,LAMBDA(orderId, if(orderID<>"", VLOOKUP(orderId, OrdersTable!D$2:$AA, 24, FALSE),""))))In the end, and after some cosmetic editing, you will have a table like the one below:
Step 11: Copy and paste the formula below to U1. This formula will add all refunded orders per customer ID.
={ "Number of refunded orders";byrow(N2:N,lambda(r, if(r<>"",countif(AA:AA,r),"")))}List Products Ordered per Customer
Step 12: Add the formula below to calculate the SKU list bought for each unique Customer ID from the OrdersTable.
Copy and paste the formula on V1:
={"SKU bought";BYROW(N2:N,LAMBDA(x, if(x<>"",TEXTJOIN(", ", TRUE, UNIQUE(FILTER(OrdersTable!I:I, (OrdersTable!AA:AA = x) * (OrdersTable!I:I <> "")),"")))))} Step 13: The formula below calculates the First SKU bought by the customer so you can see which product first attracted the customer to your brand or store.
Copy and paste the formula on W1:
={"First SKU Bought";arrayformula(if(V2:V<>"",index(split(V2:V,", "),0,1),""))}Setting Up the Customer’s Last Purchase Date
Step 13: Add the formula below to calculate the latest order date for each unique customer ID from the OrdersTable.
Copy and paste the formula on X1:
={"Last Purchase Date";BYROW(N2:N, LAMBDA(row, IF(row<>"", query({OrdersTable!AA2:AA,OrdersTable!AB2:AB},"select max(Col2) where Col1 = '"&row&"' label max(Col2) '' "),"")))}After the steps above and some cosmetic editing, you will have a table like the one below:
Please note that we want to sort the table by the number of orders. To do just that, follow the next steps.
Step 14: We’ll create a new table with all the data from the main table we created above, but remove all formulas and then sort the data.
Copy and paste the formula below into cell A1.
=query(N2:X,"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 Order By Col5 DESC label Col1 'Customer ID', Col2 'Country', Col3 'State', Col4 'City', Col5 'Number of Orders', Col6 'Total Units Ordered', Col7 'Total Spent', Col8 'Number of Refunded Orders', Col9 'SKUs Bought', Col10 '1st SKUs Bought', Col11 'Last Purchase Date'")Once you copy and paste the formula, the table will automatically generate already sorted by number of orders, resembling the image below. You can then apply some custom formatting to enhance its appearance.
Please note that if you sell in more than one country within the same Amazon region (such as North America, Europe, or the Far East), Defog will download data from all countries within that region. You may then look at the Country column to determine which currency the Total Spent amount is in.
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.
