Creating a Live Traffic Page from my PageView/Visit Database
I put a screen on top of the pageview data that I recently started collecting. Thought I would pass along some of the interesting stuff I encountered while building it ...
- I used the jQuery jTemplate plug-in Dave Ward blogged about to build the rows for the grid. The data is fetched from a webservice and then sent through the jTemplate templating engine to build the markup for the rows
- I looked into finding a free IP to Location database that I could bounce incoming IPs against to get some high level geographic information about my visitors (I need something like this anyway for my PageView/Visit cube)
Below is what the end product looks like (you can check out the live version here). And below the screen shot is some additional information regarding the two points above.
Using jTemplate and a Webservice to Populate the Grid
I used jQuery's jTemplate plugin to build the markup for the TR elements in my grid. To do this, I first created the skeleton markup for my TABLE. My skeleton is pretty simple, just defines the column headers and has a single cell in the TBODY for letting this user know the data is on the way. Here is what the markup for the skeleton looks like ...
And when this first renders (while the data is being fetched from the web service) it looks like this.
Next, I created a simple webservice that returns a few key attributes from my most recent N visit's. Stuff like the page that was visited (PageUrl), where the traffic originated from (Source) and how long ago the page was requested (When). Below is a screen shot of a sample the JSON object graph that is returned from the webservice.
Then, with these attributes above in mind, I created the jTemplate file that I used to create the TR elements for my grid. Below is what my template file looks like - basically, I just loop over the visit objects my webservice returns and use these attributes (CountryCode, CountryName, Source, etc ...) to create the cell content. I use the CountryCode property to build the IMG's scr link so the correct image is displayed. If the Source property is 'Direct', I just show that bit of text, otherwise I create a hyperlink to the url.
Finally, I wired up a bit of code that calls my webmethod every 30 seconds or so and pushes the resulting data through the template.
IP to Location Database
To get the geographic information from the visitors IP address I looked into finding a good, free, IP to Location database. I spent about a half hour googling and found these two to be the top contenders ...
For this page, I chose the WorldIP database. I downloaded the database as well as the flag images. I imported the IP info into my local SQL Server (~50K rows) and use this to get the country for the requesting IP address. It seems to work OK, but the geographic information doesn't get anymore granular than Country. I think for my cube I will probably checkout hostip.info because you can drill down to the City as well as Lat/Long. For example, if you request the geo information for IP: 18.104.22.168 you get back the following ...
Try it out for yourself: http://api.hostip.info/get_html.php?ip=22.214.171.124&position=true
If anyone has any other pointers to good, free IP to Location databases, please leave a comment or send me an email.
That's it. Enjoy!