How do I track repeat buyers and popular items?

Question: How do I track repeat buyers and popular items?

Identifying repeat buyers and popular items are two of a seller's most important tasks. Unfortunately, eBay doesn't provide an "easy and obvious" way for sellers to find either of these quantities in a basic seller account. With a little ingenuity and a spreadsheet program like Microsoft Excel, you can use the data that eBay provides through either File Exchange or PayPal's history download to mine these kinds of data quickly from and for your own records.

Answer: Begin by downloading your transaction history. While the technique described below can work in theory with either type of Excel file, it's the PayPal Excel file that will be described in these examples, so follow the steps described here to download and open a PayPal history file in Microsoft Excel.

Once you have your history file open, use the steps below to find your most loyal buyers, most popular items, or adapt these techniques to perform similar counts of all the other kinds of data in your history.

Identify Repeat Buyers (Excel 2003)

To create a sorted list of your repeat buyers in Excel 2003 while looking at an open PayPal history spreadsheet in Excel, follow these steps:

1. Click on Data -> PivotTable and PivotChart Report.
   
   
2. In the wizard popup, click Next to proceed with default settings.
   
   
3. When asked for a range, click Next again to use default settings.
   
   
4. When asked where to put the report, click Finish to use default settings. A new worksheet containing several boxes will open.
   
   
5. On the new worksheet, find Buyer ID in the box labeled PivotTable Field List.
   
   
6. Click and drag Buyer ID into the box labeled Drop Row Fields Here.
   
   
7. Click and drag Buyer ID again, this time into the box labeled Drop Data Items Here.
   
   
8. Hit Ctrl-A, then Ctrl-C, then Ctrl-N. A new, blank worksheet will open.
   
   
9. Click on Edit -> Paste Special, chose Values in the popup that appears, then click OK.
   
   
10. Hit Ctrl-A, then click Data -> Sort. In the popup that appears, select Column B and Descending in the Sort by area and click OK.

Identify Repeat Buyers (Excel 2007)

To create a sorted list of your repeat buyers in Excel 2007 while looking at an open PayPal history spreadsheet in Excel, follow these steps:

1. Click on Insert -> PivotTable.
   
   
2. In the wizard popup, click OK to proceed with default settings. A new worksheet containing several panels will open.
   
   
3. On the new worksheet, find Buyer ID in the panel labeled PivotTable Field List.
   
   
4. Click and drag Buyer ID into the box labeled Row Labels.
   
   
5. Click and drag Buyer ID again, this time into the box labeled Values.
   
   
6. Hit Ctrl-A, then Ctrl-C, then Ctrl-N. A new, blank worksheet will open.
   
   
7. Click on Home -> Paste -> Paste Special, chose Values in the popup that appears, then click OK.
   
   
8. Hit Ctrl-A, then click Data -> Sort. In the popup that appears, select Count of... in the Sort by column and Largest to Smallest in the Order column, then click OK.

What You've Done and How to Do It for Other Types of Sales Data

Whichever set of steps you followed above, what you've just created is a list of all the buyers in your history, sorted in order from most frequent to least frequent, with a count of the number of purchases they've made.

As luck would have it, it's easy to do the same thing for other kinds of data, like your most popular sale items, or most common buyers' email addresses. For example:

• To get the same kind of list but with buyers' email addresses instead of eBay IDs, repeat the instructions above but use "From Email Address" in steps 5-7 (Excel 2003) or 3-5 (Excel 2007) instead of the "Buyer ID."
  
  
• To get the same kind of list but with your most popular item titles, repeat the instructions above but use "Item Title" in steps 5-7 (Excel 2003) or 3-5 (Excel 2007) instead of "Buyer ID."
  
  
• Repeat these steps using any of the fields in the "PivotTable Field List" in steps 5-7 (Excel 2003) or 3-5 (Excel 2007) to generate a similar list for each kind of data in your PayPal history download.

Alternatives to the Excel Method

If using Excel and sorting and maintaining your own data files isn't quite your style, similar functionality can be had, albeit at a price and often in ways better suited to larger sellers, by connecting your business either to an independent auction manager or to eBay's own Selling Manager and one of the number of selling research apps.