iTunes Data Grid Skin

Blue and Gray Skins

The Markup

    
    
The title goes here
    

        

            
            

                
First Name
                
Last Name
            
        
        

            
            

                
Matt
                
Berseth
            
            

                
Tony
                
Montana
            
        
    

The Images

Sorting

Generating the Markup

//  fix up the gridview so its header row is in a thead 
//  and the rows are in a tbody ...
$('#gridView .datatable').prepend(
    //  remove the header and wrap it in a THEAD
    $('').append($('#gridView .datatable TR.header').remove())
);

Updates to my Live Traffic Page

I made a few more modifications to the Live Traffic page I posted about previously. Here is what I changed ...

New IP to Location Database

• I replaced the WIPmainia database with the GeoLite City one that Richard Lawley recommended. For some IP's it can potentially provide location information down to Region/City/Postal Code level (and its still free). teebot raised a good question about the accuracy of these free IP to Location databases. The GeoLite web site claims it's database is over 99.3% accurate on a country level and 76% on a city level for the US. Not too bad ...

Using ASP.NET to Simulate a Windows Service

• Decoding an IP address to a location isn't an instantaneous operation. And I have a feeling as I build out the rest dimensions for my Visit/PageView cube it would be nice to run some of the data scrubbing processes some where other than my home PC (right now I have a scheduled task that runs a console app every 30 minutes or so) So I tried out Omar Al Zabir's technique Simulating a Windows Service using ASP.NET.

Here is how it works:

• As visitors navigate through mattberseth.com and mattberseth2.com the raw pageview data is persisted into a table in a database that DiscountASP is hosting for me.
• Within my livetraffic site (again, hosted out on DiscountASP's servers), I am using Omar's technique to wire a bit of code that needs to be run every minute or so. This bit of code fetches the next batch of un-decoded IP's from my pageview table, converts the IP address to an IP number, and bounces it against the IP to Location database.
• After the record is decoded I keep it in memory so its available for my web service that feeds my live traffic grid.

If this works well enough (and it seems to so far) I think might consider off-loading all of the cube related data scrubbing operations.

Now wouldn't that be a cool VS web site template. File -> Add -> New -> ASP.NET Service. Fill in the web.config with the schedule and a pointer to the function to call, maybe an admin page to monitor the services health ...

Fixed a Terribly Embarrassing Bug that was Causing Incorrect IP Address to IP Number Conversion

• This is pretty embarrassing, but when converting the IP address to an IP number I was doing integer arithmetic when I should have been using longs - causing the IP numbers to overflow. I didn't catch it because my home IP decoded correctly. Below is an example that shows what I was doing wrong ...

//  split the address into tokens
string[] ipTokens = "255.254.253.252".Split('.');
 
//  compute the ipNumber (WRONG!)
long ipNumber1 = (16777216 * int.Parse(ipTokens[0])) + (65536 * int.Parse(ipTokens[1])) + (256 * int.Parse(ipTokens[2])) + int.Parse(ipTokens[3]);
 
//  compute the ipNumber (RIGHT!)
long ipNumber2 = ((long)16777216 * int.Parse(ipTokens[0])) + (65536 * int.Parse(ipTokens[1])) + (256 * int.Parse(ipTokens[2])) + int.Parse(ipTokens[3]);
 
Console.WriteLine("ipNumber1: {0}", ipNumber1);
Console.WriteLine("ipNumber2: {0}", ipNumber2);

Real-time Popular Now Link Listing

• I am planning to do some minor tweaking to my blog over the next few weeks. One of the changes I am thinking about implementing is replacing my existing Popular widget (the links there are all static) with one that leverages my PageView database to display the links that really are the most popular (at least in the last 24 hours or so). So I added a new web method that retrieves the 20 most visited links from both mattberseth.com and mattberseth2.com.

Here are a couple of screen shots of what it looks like right now. I have to admit that I am getting tired of my blogs orange theme so I kicking around some other ideas here too.

Inline jTemplates

• Rick Strahl has a gem of a tip in his recent article about client side templates with jQuery. He noted that you can store the jTemplate inline with your page by placing it in a

  jTemplate Foreach Set Variables

  • And finally, in the previous version of my page I was running my data through the template, then going in after the fact and reapply a css class to the alternating rows ...

  var fetch = function(method, e){
      $.ajax({
          type: "POST",
          url: "Service.asmx/" + method, //  The path to my web method
          data: "{'topN':20}",  // grab the top 30 records
          contentType: "application/json; charset=utf-8",
          dataType: "json",
          success: function(result) {
              if(result.d && result.d.length > 0) {
                  //  let jTempalte do its stuff ...
                  e.processTemplate(result);
   
                  //  reapply the odd class so I get the subtle
                  //  light blue zebra striping
                  $('#visits TBODY TR:odd').addClass('odd');
              }
          }
      })
  };

  Well, I looked through jTemplate to see if there was any way I could include adding this class into the template. Well, it turns out you can. jTemplate has a handful of set variables that can help you with stuff like this. Here is the list of set variables ...

  $index - index of element in table
  $iteration - id of iteration (next number begin from 0)
  $first - is first iteration?
  $last - is last iteration?
  $total - total number of iterations
  $key - key in object (name of element) (0.6.0+)
  $typeof - type of element (0.6.0+)

  And here is how I am making use of the $iteration variable from within my template to add the odd css class ...

  That's it. Enjoy!

  no comments yet.

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

    

        

            
 
            
Location
            
Source
            
Page
            
When
        
    
    

        

            
Loading data ...
        
    

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.

{#foreach $T.d as visit}

        
    {$T.visit.CountryName}
    {#if $T.visit.Source == 'Direct'}Direct{#else}{$T.visit.Source}{#/if}
    {$T.visit.PageTitle}
    {$T.visit.When}

{#/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.

$(document).ready(function() {
 
    //  load up the template 
    $('#visits TBODY').setTemplateURL('_assets/templates/tbody.tpl');
    
    var fetch = function(){
        $.ajax({
            type: "POST",
            url: "Service.asmx/Fetch", //  The path to my web method
            data: "{'topN':30}",  // grab the top 30 records
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function(result) {
                //  let jTempalte do its stuff ...
                $('#visits TBODY').processTemplate(result);
                
                //  reapply the odd class so I get the subtle
                //  light blue zebra striping
                $('#visits TBODY TR:odd').addClass('odd');
            }
        })
    };
    
    //  queue the function so it runs every 30 seconds or so ...
    window.setInterval(fetch, 30000);
    
    //  and run it right away ...
    fetch();
});

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

• WorldIP - free geolocation database, service and tools
• hostip.info

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

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!

v0.2 of my Visit/PageView Cube - Creating a Hierarchy for the Source Dimension

So I am still playing around with building an Analysis Services cube from the pageview data I recently started collecting.  Over this past weekend I added a hierarchy to the Source dimension of my cube that gives me a bit more insight about how visitors find their way to my site.  If you read my last post, you saw that I could view my pageview data broken down by two pretty general traffic sources: direct traffic and referring links ...

Well, now with my new hierarchy, I can not only view hit counts by direct traffic, referring site, or search engine ...

... but I can also drill into each of these sources and view my pageviews at a more granular level.

I can do this because I have added a bit of preprocessing logic to my cube creation process that sends all referring urls through a very simple rule engine that assigns the referring url a category and subcategory.  There is some source code a little further below, but the rules look something like this ...

   1: -- Search Engine Rules

   2:  

   3: if ref_url's domain like google.com and ref_url querystring contains the q token then Category=SearchEngine, SubCategory=Google 

   4:  

   5: if ref_url's domain like yahoo.com and ref_url querystring contains the p token then Category=SearchEngine, SubCategory=Yahoo

   6:  

   7: -- Community Referrers

   8:  

   9: if ref_url's domain like dotnetkicks.com then Category=Referrer, SubCategory=Community

  10:  

  11: if ref_url's domain like digg.com then Category=Referrer, SubCategory=Community

  12:  

  13: -- Internal Referrers

  14:  

  15: if ref_url's domain like mattberseth.com then Category=Referrer, SubCategory=Internal

  16:  

  17: if ref_url's domain like mattberseth2.com then Category=Referrer, SubCategory=Internal

  18:  

  19: ...

So I took these rules (and a few others) and created a quick and dirty console app that rips through all of the my pageviews and assigns a category and subcategory based on these rules.  The console app is a total of 250 LOC, so I am not going to post all of the source, but this is the gist of it (I removed the SqlClient stuff where I do the getting and putting)

   1: class Program

   2: {

   3:     static void Main(string[] args)

   4:     {

   5:         List<TrafficSource> trafficSources = new List<TrafficSource>()

   6:         {

   7:             //  Search Engines

   8:             new SearchEngine(){ Name="google", SourceID=3, QueryToken="q", IsMatch = uri => uri.Host.IndexOf("google", StringComparison.OrdinalIgnoreCase) >= 0 && uri.LocalPath == "/search" },

   9:             new SearchEngine(){ Name="yahoo", SourceID=4, QueryToken="p", IsMatch = uri => uri.Host.IndexOf("search.yahoo", StringComparison.OrdinalIgnoreCase) >= 0 && uri.LocalPath == "/search" },

  10:  

  11:             //  Social

  12:             new TrafficSource(){ Name="dotnetkicks", SourceID=6, IsMatch = uri => uri.Host.IndexOf("dotnetkicks.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  13:             new TrafficSource(){ Name="digg", SourceID=6, IsMatch = uri => uri.Host.IndexOf("digg.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  14:             new TrafficSource(){ Name="reddit", SourceID=6, IsMatch = uri => uri.Host.IndexOf("reddit.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  15:             new TrafficSource(){ Name="stumbleupon", SourceID=6, IsMatch = uri => uri.Host.IndexOf("stumbleupon.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  16:  

  17:             //  Community

  18:             new TrafficSource(){ Name="weblogs.asp.net", SourceID=6, IsMatch = uri => uri.Host.IndexOf("weblogs.asp.net", StringComparison.OrdinalIgnoreCase) >= 0 },

  19:  

  20:             //  Forums

  21:             new TrafficSource(){ Name="forums.asp.net", SourceID=7, IsMatch = uri => uri.Host.IndexOf("forums.asp.net", StringComparison.OrdinalIgnoreCase) >= 0 },

  22:             new TrafficSource(){ Name="expertsexchange", SourceID=7, IsMatch = uri => uri.Host.IndexOf("experts-exchange.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  23:             new TrafficSource(){ Name="stackoverflow", SourceID=7, IsMatch = uri => uri.Host.IndexOf("stackoverflow.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  24:  

  25:             //  Internal

  26:             new TrafficSource(){ Name="mattberseth.com", SourceID=8, IsMatch = uri => uri.Host.IndexOf("mattberseth.com", StringComparison.OrdinalIgnoreCase) >= 0 },

  27:             new TrafficSource(){ Name="mattberseth2.com", SourceID=8, IsMatch = uri => uri.Host.IndexOf("mattberseth2.com", StringComparison.OrdinalIgnoreCase) >= 0 }

  28:         };

  29:  

  30:         //  default uncategorized traffic source

  31:         TrafficSource defaultTrafficSource = new TrafficSource() { Name = "Other", SourceID = 9, IsMatch = uri => true };

  32:         //  direct traffic

  33:         TrafficSource directTrafficSource = new TrafficSource() { Name = "Direct", SourceID = 0, IsMatch = uri => true };

  34:  

  35:         bool done = false;

  36:         while (!done)

  37:         {

  38:             //  fetch the next batch of page views

  39:             List<PageView> pageviews = PageView.Fetch();

  40:  

  41:             //  get all of the visits that contain referring urls

  42:             foreach (PageView pageview in pageviews)

  43:             {

  44:                 if (pageview.RefUri == null)

  45:                 {

  46:                     //  direct traffic, assign the direct traffic source

  47:                     pageview.Source = directTrafficSource;

  48:                 }

  49:                 else

  50:                 {

  51:                     foreach (TrafficSource source in trafficSources)

  52:                     {

  53:                         //  see if it matches any of the existing rules we have setup

  54:                         if (source.IsMatch(pageview.RefUri))

  55:                         {

  56:                             //  add it to the group

  57:                             pageview.Source = source;

  58:                             break;

  59:                         }

  60:                     }

  61:  

  62:                     if (pageview.Source == null)

  63:                     {

  64:                         //  else its just some random ref we don't care enough about

  65:                         //  to further categorize

  66:                         pageview.Source = defaultTrafficSource;

  67:                     }

  68:                 }

  69:  

  70:  

  71:                 //  update the record

  72:                 pageview.Update();

  73:             }

  74:  

  75:             done = pageviews.Count == 0;

  76:         }

  77:     }

  78: }

  79:  

  80: class TrafficSource

  81: {

  82:     public int SourceID { get; set; }

  83:     public string HostName { get; set; }

  84:     public string Name { get; set; }

  85:  

  86:     public Func<Uri, bool> IsMatch { get; set; }

  87: }

  88:  

  89: class SearchEngine : TrafficSource

  90: {

  91:     public string QueryToken { get; set; }

  92:  

  93:     public string ParseKeywords(Uri uri)

  94:     {

  95:         return HttpUtility.ParseQueryString(uri.Query)[this.QueryToken];

  96:     }

  97: }

  98:  

  99: class PageView

 100: {

 101:     public int ID { get; set; }

 102:     public Uri Uri { get; set; }

 103:     public Uri RefUri { get; set; }

 104:     public TrafficSource Source { get; set; }

 105:  

 106:     public void Update()

 107:     {

 108:         // update the pageview with the category/subcategory

 109:     }

 110:  

 111:     public static List<PageView> Fetch()

 112:     {

 113:         // get the next batch of pageviews that need to be categorized

 114:     }

 115: }

And the output of this preprocessing a table that contains the set of domains that link to my blog, as well as that referring domains category and subcategory.  I then take this table and use it as the data source for my Source dimension.

And after reprocessing the cube, you can write what ever custom MDX queries you want.  The one below calculates the what percent of the daily total the traffic category makes up.

   1: with

   2:     member [Measures].[Direct Traffic Hits] as ([Source].[Hierarchy].[Category Name].&[Direct], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'

   3:     member [Measures].[Search Engines Hits] as ([Source].[Hierarchy].[Category Name].&[Search Engine], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'    

   4:     member [Measures].[Community Hits] as ([Source].[Hierarchy].[Subcategory Name].&[Community], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'

   5:     member [Measures].[Forums Hits] as ([Source].[Hierarchy].[Subcategory Name].&[Forums], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'    

   6:     member [Measures].[Internal Hits] as ([Source].[Hierarchy].[Subcategory Name].&[Internal], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'    

   7:     member [Measures].[Unknown Hits] as ([Source].[Hierarchy].[Subcategory Name].&[Unknown], [Measures].[Hit]) / ([Measures].[Hit]), format_string = 'Percent'

   8:     member [Measures].[All Hits] as ([Measures].[Hit]), format_string = 'Standard'

   9: select

  10: non empty

  11: {

  12:     [Measures].[Direct Traffic Hits],

  13:     [Measures].[Search Engines Hits],    

  14:     [Measures].[Community Hits],

  15:     [Measures].[Forums Hits],

  16:     [Measures].[Internal Hits],

  17:     [Measures].[Unknown Hits],

  18:     [Measures].[All Hits]

  19: } on 0,

  20: non empty

  21: {

  22:     [Time].[Date].children

  23: } on 1

  24: from PageView

Executing the above MDX produces the following result set ...

And you can pipe this right into Excel to chart the results (thanks to ScottGu for the huge jump in referring traffic last Thursday) ...

Conclusion

I have got a ton of questions on how I am collecting my pageview data.  I don't have all of the kinks worked out yet, but I will probably write up a quick post next on how I am doing it.

That's it.  Enjoy!

v0.1 of my Visit/PageView Analysis Services Cube

So I created a cube using my the visit/pageview that I recently started collecting. The cube is VERY simple - only 4 dimensions {App, Page, Source, Time} and just a single measure - {Hit}. I created the cube using the 2005 versions of Visual Studio and Microsoft's Analysis Services. The IDE's wizards pretty much walk you through the process, which is great because creating an Analysis Services project from scratch is more than a little intimidating. Especially if your a web developer like me and you don't know a whole lot about querying, let alone designing a cube.

Anyway, like I said, my cube is very simple. The App dimension only contains 2 members: 'mattberseth' for this site, and 'mattberseth2' for my live demo site. The Page dimension contains all of the unique URLs for both sites as its members, the Source dimension is essentially a bit field for determining if the traffic was the result of a direct hit or from a referring site and finally the Time dimension represents the calendar and is used for counting hits by a time interval (i.e. Days, Weeks, Months, etc...).

In my simple cube, all 4 dimensions and the Hit measure are currently coming from a single table. I have simulated the standard star schema by generating the keys for the different dimensions using the following 4 SQL queries. So the first query in the SQL snippet populates my measure group and the other three are responsible for populating the App, Source and Page dimensions.

-- builds the fact_pageview measure group ***
select
    -- if the referring url is empty than we know the source comes from a direct hit
    (case when ref_url = '' then 1 else 0 end) as source_id,
    -- the app_id - either mattberseth or mattberseth2
    app_id,
    -- turn the page url to an int
    checksum(url) as page_id,
    -- extract the date portion of the datetime
    convert(datetime, floor(convert(float, date))) as date,
    -- each row is a sinlge hit
    1 as hit
from
    -- my visit table
    visit_load with(nolock)
 
-- builds the app dimension ***
select
    'mattberseth' as app_id,
    'My Blog' as app_name
union
select
    'mattberseth2',
    'My Live Demo Site'
    
 
-- builds the source dimension ***
select
    0 as source_id,
    'Referrer' as source_name
union
select
    1,
    'Direct'
 
 
-- builds the page dimension ***
select
    checksum(url) as page_id,
    url as url
from
    visit_load with(nolock)

Next, I created views for these 4 queries, let my Analysis Services project know about them and used them as the data source for my cube. Conceptually, this diagram shows how these 4 queries are related.

Browsing and Querying the Cube

And amazingly, only after a few minutes of nexting through wizards and drag and drop design work, I deployed and processed the cube to my local Analysis Server instance. And now I can start taking a look at the data.

Browsing the Cube

Once the cube is deployed and processed, you can start browsing it. Below are a couple of screen shots that show the structure of my cube on the left, and hit counts for my two sites (mattberseth.com and mattberseth2.com) segmented by the traffic source (either direct traffic or referring site). The screen shot below that shows these counts as a percentage of the grand total. Looks like direct traffic to my demo site only makes up 2% of my total traffic ;(

Querying the Cube

And if you can stomach writing a little MDX, you can write custom queries to extract even more useful information. Below is a sample MDX query and result set that shows the average traffic per day for both mattberseth.com and mattberseth2.com segmented by week. The numbers are a little deceiving because only Week 39 consists of a full 7 days, but I think you can get the picture.

with 
    -- define the Weekend and Weekday sets
    set [Weekday] as 
    {
        [Time].[Day Of Week].[Day 2], 
        [Time].[Day Of Week].[Day 3], 
        [Time].[Day Of Week].[Day 4], 
        [Time].[Day Of Week].[Day 5], 
        [Time].[Day Of Week].[Day 6]
    }
    set [Weekend] as 
    {
        [Time].[Day Of Week].[Day 1], 
        [Time].[Day Of Week].[Day 7]
    }
    -- create a few calculated meausres based that make use of these sets
    member [Measures].[Weekday Average] as avg([Weekday], [Measures].[Hit]), format_string = '#'
    member [Measures].[Weekend Average] as avg([Weekend], [Measures].[Hit]), format_string = '#'    
    member [Measures].[Weekly Average] as avg({[Weekday], [Weekend]}, [Measures].[Hit]), format_string = '#'    
select
{
    [Measures].[Weekday Average],
    [Measures].[Weekend Average],
    [Measures].[Weekly Average]
} on 0,
non empty
{
    [App].children * [Time].[Week Of Year].children
} on 1
from
    [PageView]

What's Next?

Well, I am pretty excited. I only have a handful of development hours invested in my visit cube (it honestly took longer to write this post than it did to create the cube) and I can already tell I have made the right decision by maintaining my own pageview/visit database. Of course there is still a lot to do ...

• My pageview JavaScript tracking code needs some work. I have been tweaking it over the past 2 weeks to play around with different techniques to keep my tracking request from getting cached. I have finally come within a few percent of what Google Analytics is reporting so I am happy. I think I will clean the handler up and write a quick post describing what I did.
• My Source dimension on has 2 members - Direct and Referrer. I would like to break down the Referrer further to include Search Engines, Community (DNK, Reddit, Digg, DZone, etc...), and forums (forums.asp.net, stackoverflow, etc...).
• I need to extract keywords from the Search Engine sources and get them into the cube
• Look up geography information based on IP
• IP + User Agent Sessionization. I would like to track time on site, navigation paths, etc...
• Incorporate additional dimensional data from my Moveable Type database

At some point I plan on sharing the solution: JavaScript tracking code, HttpHandler, OLTP and OLAP databases as well as the Analysis Services Project ...

That's it. Enjoy!