How to Find The Most Popular Categories on eBay


Easy Auction Management

Free Membership

* Receive software news
* Get exclusive discounts
* Post in the forum

Already a member? Login

RainWorx Online
Auction Software
RainWorx Auction Software
Create your own Online Auction Website!
Plus Reverse, RFQ, Classifieds, Trades, & MORE

Home » Articles » Statistics You Won't Find on eBay » How to Find The Most Popular Categories

Instructions
ASP Code
HTML Code
SQL Code

Instructions

Create an ASP page, inserting the ASP code below at the very start of the page. You will need to create a database table to hold the results (see below) and enter a valid connection string in the variable indicated.

Insert the HTML code below between the <body> tags of your ASP page. It simply outputs the data extracted to the page so you can check it is working as it should.

Use the SQL code to create queries in your database which aggregate the data you have extracted.

Please note the following:

No warranties or guarantees are offered for this code. It has been tested and used by us, but your system settings, eBay changes, and other factors may stop it working as it should.
You use this code at your own risk. We are not responsible for any damage or complaint arising from its use.
You may freely use and adapt this code for any purpose, as you see fit. Please credit this site if you do so.

ASP Code


  <%
  
  'First you will need a database table with the structure below, 
  'or equivalent
  '***********************************************************
  'CREATE TABLE [tblEbayCategories] (
  '[CatID] [int] IDENTITY (1, 1) NOT NULL ,
  '[CatNumber] [int] NOT NULL ,
  '[TopLevelName] [nvarchar] (50) NOT NULL ,
  '[CatName] [nvarchar] (50) NOT NULL ,
  '[ItemCount] [int] NOT NULL )
  
  Dim strDBConnection, objXMLHttp, strURL, strResponse, reAllCats
  Dim reLevel1Cats, reLevel2Cats, Match, Matches, strOutPut
  Dim strTopLevelCat, strSQL, con, strCatName

  'Set the variable strDBConnection to connect
  'to your database
  '***********************************************************
  strDBConnection = "ENTER YOUR CONNECTION STRING HERE"

  'Get page of first-level and second level 
  'category data from eBay
  '***********************************************************
  Set objXMLHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.4.0")
  strURL = "http://pages.ebay.com/buy/index.html?ssPageName=HAC01"
  objXMLHttp.Open "GET", strURL, False
  objXMLHttp.Send
  strResponse = objXMLHttp.ResponseText
  Set objXMLHttp = Nothing

  '***********************************************************
  'Set up regular expression objects
  '***********************************************************
  
  'The reLevel1Cats object matches top-level categories
  'The "$1" placeholder matches the Category Name specifically
  '***********************************************************  
  Set reLevel1Cats = New RegExp
  reLevel1Cats.Global = True
  reLevel1Cats.IgnoreCase = True
  reLevel1Cats.Pattern = "<a href=""http:\/\/page" & _
    "s\.ebay\.com\/\w{1,20}\-index\.html\?from=R12""" & _
	"><b>([^<]*)<\/a>"
  
  'The reLevel2Cats object matches 2nd-level categories
  'The following placeholders are used:
  '$1 = Category Number
  '$2 = Category Name
  '$3 = Item Count
  '**********************************************************
  Set reLevel2Cats = New RegExp
  reLevel2Cats.Global = True
  reLevel2Cats.IgnoreCase = True
  reLevel2Cats.Pattern = "<a href=""http:\/\/listings\.ebay\." & _
    "com\/aw\/plistings\/list\/category(\d{2,5})\/index\.html" & _
    "\?from=R12"">([^<]*)<\/a><\/FONT><font size = ""1""> \((" & _
    "\d{1,10})\)" & _
  
  'The reAllCats object adds the first two regular expressions
  'with an OR operator - it matches both first-level and second-level
  'categories
  '**********************************************************
  Set reAllCats = New RegExp
  reAllCats.Global = True
  reAllCats.IgnoreCase = True
  reAllCats.Pattern = reLevel1Cats.Pattern & "|" & reLevel2Cats.Pattern
  
  'Initialise the database connection
  '**********************************************************
  Set con = Server.CreateObject("ADODB.Connection")
  con.Open strDBConnection
  
  'Uncomment the next two lines below to clear the 
  'database table before adding data
  '**********************************************************
  'strSQL = "TRUNCATE TABLE tblEbayCategories; "
  'con.Execute strSQL
  
  '**********************************************************
  'Execute the regular expression which matches both first-level 
  'and second-level categories then iterate through the resulting
  'collection of matches, checking whether each one is a 
  'first-level or second-level category
  '
  'If it is a first-level category, store the name in the 
  'strTopLevelCat variable and build an HTML table including the
  'category name as a header
  '
  'If it is a second-level category, add the data in the 
  'placeholders to the HTML table and execute a SQL query to add
  'it to the database
  '**********************************************************
  Set Matches = reAllCats.Execute(strResponse) 
    For Each Match in Matches
	
      If reLevel2Cats.Test(Match.Value) = True Then '2nd-level category
        strOutput = strOutPut & reLevel2Cats.Replace(Match.Value, & _
          "<tr> <td>$1</td> <td><img src='images/spacer.gif' " & _
          "height='1' width='10'></td> <td>$2</td> <td>$3</td" & _
          "> <td>items</td> </tr>" & vbNewLine)
        'Lines below escape apostrophes for SQL query
        strCatName = reLevel2Cats.Replace(Match.Value, "$2")
        strCatName = Replace(strCatName, "'", "''")
        strSQL = reLevel2Cats.Replace(Match.Value, "INSERT INTO " & _
          "tblEbayCategories (CatNumber, TopLevelName, CatName, " & _
          "ItemCount) VALUES ($1,'" & _
          Replace(strTopLevelCat, "'", "''") & "','" & _ 
          strCatName &"',$3);") & vbNewLine
        con.Execute strSQL
        
      Else: 'First-level category
	  
        strTopLevelCat = reLevel1Cats.Replace(Match.Value, "$1")
        strOutput = strOutput & "<tr><td colspan='5'>&nbsp;" & _
          "</td></tr><tr><td colspan='3'><h2>" & strTopLevelCat & _
          "</h2></td></tr>" & vbNewLine
      
      End If
	  
    Next
    
  con.Close
  Set con = Nothing
  
  %>

HTML Code

  <%Response.Write("<table>" & strOutPut & "</table>")%>

SQL Code

This counts the number of items in each first-level category:

	SELECT TopLevelName, SUM(ItemCount) AS TotalItems
	FROM  tblEbayCategories
	GROUP BY TopLevelName
	ORDER BY SUM(ItemCount) DESC

This returns the top 20 most popular second-level categories:

	SELECT TOP 20 TopLevelName, CatName, SUM(ItemCount) AS TotalItems
	FROM  tblEbayCategories
	GROUP BY CatName, TopLevelName
	ORDER BY SUM(ItemCount) DESC

Contents

Instructions

ASP Code

HTML Code

SQL Code