We recently had an ecommerce site ask what trends we could find for customers who make multiple purchases. In order to provide some quick insights, we opted for using their data right within Excel. Right away you're probably wondering how you can do a fairly complex analysis like this without a fully-featured visualization platform. Of course you have to make some concessions, but it is definitely possible to gain insights that you can act on right away.

Data you will need:

• A customer ID such as email address
• An order ID to distinguish unique orders belonging to each customer
• An order timestamp (use this to sort your from oldest to newest)
• Time until the next order (This can be calculated in Excel by comparing order dates belonging to the same customer ID. Use vlookup and subtract the timestamps)
• Number of orders (This can be calculated in Excel by counting order IDs belonging to the same customer ID. Use countif)
• Products purchased - each row in your file should contain a separate product. This means that a single order with multiple products will appear across multiple rows

Now, make a few concessions:

• Remove any orders with more than one item. This analysis applies best to larger item purchases or sites with a limited number of products.
• Remove any orders that happened within the return policy time frame for the site. We discovered early that purchases of the same item within the return period are most likely returns/exchanges.

Now build a pivot table from this final data set. It should still be sorted order timestamp.

• Product in first order - this is just the column of products.
• Product in second order - this is a little more complicated. Do a vlookup for the customer ID in the data below this row, and grab the product from the next order.
• Days between orders

The pivot table will be setup as follows.

• Row labels
• Product in first order
• Product in second order
• Values
• Count of product in first order
• Count of product in second order
• Average number of days between orders

Now comes the easy part! Investigate the data. What is the most popular product ordered after various products? If the same product is being reordered, how long do customers wait to reorder on average?

And finally, the fun part! Act on the findings. Set up automated email campaigns based on the items purchased. Present customers with an offer if they have not reordered in the expected time frame. Go where the data takes you...