Saturday, June 04, 2011

Retrieving the most popular pages using Google Analytics API

For a long time I’ve shown the most popular pages on the home page of my website. I did this by logging every page that was viewed to the MySql database on the back end. This kind of worked but had a few problems. First, it wasn’t very clever since it couldn’t tell the difference between a real visitor and a search engine bot. Second, since I’ve started to get quite a few visitors (no, really), it was writing a large amount of data to the database.

So I thought there must be a better solution. Figuring that all the information I needed was already being collected by Google Analytics, I thought I could grab this data and dump it into a much smaller with just the page URL and the number of visits (rather than adding a row for every visit). So I coded up a solution using the .NET wrapper around the Google Analytics API. And this is what it looks like (with the database access code removed for clarity). You’ll need to provide your own email address, password and Google Analytics account table ID to get this to work, for obvious reasons.

using System;
using Google.GData.Analytics;

namespace GoogleAnalytics
  class Program
    static void Main(string[] args)
      AccountQuery feedQuery = new AccountQuery();
      AnalyticsService service = new AnalyticsService("DoogalAnalytics");
      service.setUserCredentials("email address", "password");

      DataQuery pageViewQuery = new DataQuery("");
      pageViewQuery.Ids = "Google Analytics account table ID";
      pageViewQuery.Metrics = "ga:visits";
      pageViewQuery.Dimensions = "ga:pagePath";
      pageViewQuery.Sort = "-ga:visits";
      pageViewQuery.GAStartDate = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd");
      pageViewQuery.GAEndDate = DateTime.Now.ToString("yyyy-MM-dd");

      DataFeed feed = service.Query(pageViewQuery);
      for (int i = 0; i < 20; i++)
        DataEntry pvEntry = (DataEntry)feed.Entries[i];
        string page = pvEntry.Dimensions[0].Value.Substring(1);
        string visits = pvEntry.Metrics[0].Value;

        Console.WriteLine(page + ": " + visits);