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.

image

 

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 ...

   1: <table id="visits" class="datatable" cellpadding="0" cellspacing="0">
   2:     <thead>
   3:         <tr>
   4:             <th class="first">&nbsp;</th>
   5:             <th>Location</th>
   6:             <th>Source</th>
   7:             <th>Page</th>
   8:             <th>When</th>
   9:         </tr>
  10:     </thead>
  11:     <tbody>
  12:         <tr>
  13:             <td colspan="5">Loading data ...</td>
  14:         </tr>
  15:     </tbody>
  16: </table>

 

And when this first renders (while the data is being fetched from the web service) it looks like this.

image

 

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. 

image 

 

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.

   1: {#foreach $T.d as visit}
   2: <tr>
   3:     <td><img src="_assets/img/flags/{$T.visit.CountryCode}.png" alt="{$T.visit.CountryName}" /></td>    
   4:     <td>{$T.visit.CountryName}</td>
   5:     <td>{#if $T.visit.Source == 'Direct'}Direct{#else}<a href="http://{$T.visit.Source}">{$T.visit.Source}</a>{#/if}</td>
   6:     <td><a href="{$T.visit.PageUrl}">{$T.visit.PageTitle}</a></td>
   7:     <td>{$T.visit.When}</td>
   8: </tr>
   9: {#/for}

 

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.

   1: $(document).ready(function() {
   2:  
   3:     //  load up the template 
   4:     $('#visits TBODY').setTemplateURL('_assets/templates/tbody.tpl');
   5:     
   6:     var fetch = function(){
   7:         $.ajax({
   8:             type: "POST",
   9:             url: "Service.asmx/Fetch", //  The path to my web method
  10:             data: "{'topN':30}",  // grab the top 30 records
  11:             contentType: "application/json; charset=utf-8",
  12:             dataType: "json",
  13:             success: function(result) {
  14:                 //  let jTempalte do its stuff ...
  15:                 $('#visits TBODY').processTemplate(result);
  16:                 
  17:                 //  reapply the odd class so I get the subtle
  18:                 //  light blue zebra striping
  19:                 $('#visits TBODY TR:odd').addClass('odd');
  20:             }
  21:         })
  22:     };
  23:     
  24:     //  queue the function so it runs every 30 seconds or so ...
  25:     window.setInterval(fetch, 30000);
  26:     
  27:     //  and run it right away ...
  28:     fetch();
  29: });

 

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: 82.55.96.129 you get back the following ...

image

 

Try it out for yourself: http://api.hostip.info/get_html.php?ip=82.55.96.129&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!


TrackBack

TrackBack URL for this entry:
http://mattberseth.com/blog-mt/mt-tb.fcgi/156

Comments


My licensing fee for that technique is $1 per item displayed. My legal team will be in touch shortly!

That's a great implementation of the templating.

It would be cool to see this improved to render and insert only the delta between the last update and current. That would be pretty slick (and not very difficult, with jQuery's manipulation tools). Maybe fade or slide in the delta items.

(On a side note, I think I like your comment form better than mine.)

Posted by: Richard Lawley on October 13, 2008 03:26 AM

Nice demo!

I recently implemented some IP-to-country stuff on our web app for identifying where customers were from, and for fraud purposes. I tried hostip.info, but found that it didn't know where I was (this was my first test!), and I didn't like the idea of relying on an external site to do my lookups (we use them in real time for fraud prevention purposes).

I went with MaxMind's GeoLite/GeoIP product (GeoLite is free, GeoIP is a little more accurate apparently). This is provided as either a web service or a binary database with sample code to read it, which is what I went for in the end - very fast and not reliant on someone else's servers.

Hey Dave -
crap - I guess I better start a tab or something ...

@Richard -
Thanks Richard - I am going to add geolite to this list.

I would rather store the database locally as well. My ultimate goal is to add the location info to my cube and it would be way easier (and faster to process the cube) if I had everything locally.

Hallo Matt,
I am a developer of WIPmania. I just want to say that in contrast to other databases, our data are from core and backbone routers, so we provide real geographic location of the network and not its owner, (this main feature is frequently mentioned on the website, but often will be missed by users). Here is why these data are being shown: Why WorldIP data rather than whois data. Examples.

Precise information about cities possible to get(I can presume) by buying($$$) it from all regional providers worldwide(!!!)(but I cannot even presume, what is can cost :), or by getting it from the routers (nobody didn't do it until now). There are a couple of cities databases. But none of them shows correctly, where I'm from, according to my IP address 89.59.93.103 I'm living everywhere but only not in Karlsruhe :)

Posted by: teebot on October 15, 2008 05:01 PM

Just to let you know that I'm in Canada not in Norway :)
Those geo ip database are rarely reliable

@teebot -
I guess it depends on how you define reliable. What if its correct at the country level 90% of the time? Is that reliable? Would you consider the information valuable? Correct at the Country/Region level 80% of the time? Is that reliable? Is it valuable?

I would think your reliable/valuable threshold depends on how you plan on using the information (I come from a marketing background so naturally I find it rather interesting).

Thanks for the comment.


@teebot
I can presume you are using Opera browser.
Your IP is really in Norway for outside networks, because your Opera uses proxy.
You can see "Opera-proxy" near the country on main page in the section "Your IP" of wipmania website.

The geographical info is cool, but a little flawed. Says I'm in Burlington Vermont, when I'm really in Burlington Massachusetts. Very close though.

Any recommended reading for creating Analysis Cubes? I've been reading alot but I still can't see a good starting place for development, seems like a big cloud of knowledge. Anyhow cool stuff

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Consulting Services

Yep - I also offer consulting services. And heck, I'll do just about anything. If you enjoy my blog just drop me an email describing the work you need done.

Recent Comments

  • Evan wrote: The geographical info is cool, but a little flawed. Says I'm in Burlington Vermont, when I'm really ...
  • Alrond wrote: @teebot I can presume you are using Opera browser. Your IP is really in Norway for outside networks,...
  • Matt Berseth wrote: @teebot - I guess it depends on how you define reliable. What if its correct at the country...
  • teebot wrote: Just to let you know that I'm in Canada not in Norway :) Those geo ip database are rarely reliabl...
  • Alrond wrote: Hallo Matt, I am a developer of WIPmania. I just want to say that in contrast to other databases, o...
  • Matt Berseth wrote: Hey Dave - crap - I guess I better start a tab or something ... @Richard - Thanks Ric...
  • Richard Lawley wrote: Nice demo! I recently implemented some IP-to-country stuff on our web app for identifying where cus...
  • Dave Ward wrote: My licensing fee for that technique is $1 per item displayed. My legal team will be in touch shortl...