Examining all orders to find popular product combinations can guide retailers in offering bundles that may increase sales and customer loyalty. 

This article will answer the question: Get me the repeat customers. Which product combinations do they buy more frequently?

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 determine how customers purchase their products to search for repetitive combinations.

The most significant benefit of analyzing SKU combinations is that it provide valuable insights into customer purchasing behavior. This allows sellers to understand which products are frequently bought by the same customer and offer bundles to increase sales and loyalty.

We will create a Pivot Table like the one below.

One good thing about doing this in Defog’s spreadsheet is that as soon as Defog updates the data, the Table we will create below will be automatically updated. 

To create the Table, we will use two columns from OrdersTable: Column I (SKU) and Column AA (customer ID).

Listing the SKUs Ordered per Customer

Step 1: Create a new sheet by clicking the + icon and renaming it to Customer Profile: SKUs Combinations.

Step 2: Use the formula below to extract distinct customer IDs from the OrdersTable in this new sheet.

Copy and paste the formula on E1:

=UNIQUE(FILTER(OrdersTable!AA:AA,OrdersTable!AA:AA<>""))

Step 3:  Write the title SKUs ordered on F2 and use the formula below to calculate the SKUs ordered for each unique customer ID from the OrdersTable. Copy and paste the formula on F2:

=BYROW(E2:E,LAMBDA(x, if(x<>"",(TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(OrdersTable!I:I, (OrdersTable!AA:AA = x) * (OrdersTable!I:I <> "")))),"")))))

​​

Listing the SKUs Combinations and Their Frequency

Step 4: We first need all unique combinations to analyze the total number of customers who bought each SKU combination.

In Column H, add the title Unique combinations of SKUs and copy and paste the formula below on H2:

=FILTER(UNIQUE(F2:F),FIND(",",UNIQUE(F2:F))>0)

Step 5: In Column I add the title Total count. We then calculate the frequency of each unique SKU combination by counting the times each combination appears across all customers’ orders.

Copy and paste the formula below on I2:

=ARRAYFORMULA(IF(H2:H<>"",COUNTIF(F:F, H2:H),""))

Creating the final visualization with SKU combinations

Step 6: Select columns H and I, then click Insert on the top Google Sheets menu and then click Pivot table. 

Step 7: After clicking Pivot table, choose Existing sheet and copy and paste ‘Customer Profile: SKUs Combinations’!A1 to the line below Existing sheet as shown in the image below. After that, click Create.

Step 8: On the Pivot table editor (the sidebar on the right of the sheet), select Unique combinations SKUs and drag it under Rows. 

Step 9: Use the drag-and-drop method to set up the Pivot Table values. Drag the Total count data column into the Values section.

Step 10: On the box under Rows, on the pull-down menu Order (left), select Descending, and on the pull-down Sort By (right), choose SUM of Total count.

After some cosmetic formatting, your sheet should look similar to the one below:

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 regarding the data, formulas, and visuals provided.