Recently, I needed to be able to get a list of popular pages for a site that I manage and display that information. I figured, rather than building a database and code hit tracking and page views from scratch, I would just tap into my Google Analytics data.
Requirements:
- Jacob Reimers Google Analytics Reader for .net
- ASP.net LINQ
- Database for caching results
All of the articles have the path format “/articles/[ category ]/[ article-title ]/” and all articles have unique titles. First stop, create the article class:
class ArticleInfo
{
public string title { get; set; }
public string path { get; set; }
public string hits { get; set; }
}
This is what I will use to sort by title after sorting by hits. Granted, this could probably be optimized a bit more but I’m really tired…
Basically, this creates a class of titles, paths, and hits which I will be pulling from later to create the HTML.
Here is the rest of the code-behind for the user control which will display the data. I will go through this piece by piece
protected void Page_Load(object sender, EventArgs e)
{
try
{
clsDBGet db = new clsDBGet();
DataTable dt = db.GetDTfromProc("[STORED PROCEDURE - GET]", null);//Stored procedure to pull from the DB
if (dt.Rows.Count > 0)
ga.Text = dt.Rows[0]["DATA"].ToString();
else
{
try
{
ReportRequestor rr = new ReportRequestor("[gmail account email]", "[password]");
AnalyticsAccountInfo ainfo = new AnalyticsAccountInfo();
IEnumerable(AnalyticsAccountInfo) accounts = rr.GetAccounts();
string analyticsTitle = "V2 Live";//Title of Profile where I want to grab the data
AnalyticsAccountInfo account = accounts.First(a => a.Title == analyticsTitle);
DateTime from = DateTime.Now.AddDays(-2);//last 24 hours (data is always a day behind)
DateTime to = DateTime.Now;
IEnumerable(GenericEntry) report = rr.RequestReport(account, new Dimension[] { Dimension.pagePath, Dimension.pageTitle }, new Metric[] { Metric.pageviews }, from, to, 1000);
string myString = string.Empty;
int x = 0;
string[] articleTitles = new string[22]; //To only grab unique titles
ArticleInfo[] articleData = new ArticleInfo[22];
bool unique = true;
report = report.OrderByDescending(myReport => Convert.ToInt32(myReport.Metrics.First().Value));
foreach (GenericEntry myReport in report)
{
Regex isArticle = new Regex("/articles/.+/.+/");
string path = myReport.Dimensions.First().Value;
string title = myReport.Dimensions.Last().Value;
string hits = myReport.Metrics.First().Value;
if (x < 22 && isArticle.IsMatch(path))
{
if (!path.Contains("/page_") && !path.Contains("/search/") && title.Trim() != "(not set)")
{
//Add to array of article titles then check to see if it exists in the array
for (int y = 0; y < articleTitles.Length; y++)
{
if (articleTitles[y] != null && title.Trim().ToLower() == articleTitles[y].ToLower())
{
unique = false;
break;
}
else
unique = true;
}
if (unique)
{
articleData[x] = new ArticleInfo { path = Server.UrlDecode(path).Trim(), title = title.Trim(), hits = hits };
x++;
}
}
}
}
IEnumerable(ArticleInfo) articles = articleData.OrderBy(article => article.title);
foreach (ArticleInfo article in articles)
{
myString += "<li><a class=\"gaHits\" href=\"" + article.path + "\" title=\"" + article.title + "\" data=\"" + article.hits + "\"></a ></li>";
}
if (x < 10)
{
panelGA.Visible = false;
}
else
{
clsDBPost dbp = new clsDBPost();
string proc = "[STORED PROCEDURE - INSERT]";
Hashtable ht = new Hashtable();
ht.Add("data", myString);
bool inserted = dbp.ExecuteProcedure(proc, ht);
}
ga.Text = myString;
}
catch (Exception EX)
{
//Response.Write(EX.ToString());
panelGA.Visible = false;
}
}
}
catch (Exception ex2)
{
//Response.Write(ex2.ToString());
panelGA.Visible = false;
}
}
Let's break this up into parts.
-
The first thing I want to do is query my database to see if I already have the latest Google Analytics data for today. If it's there, render the data outright. This allows me to only query Google Analytics once per day, instead of on every page load. This is important because Google limits the amount of requests you can make per day and because we are requesting data for the previous day, there's no need to pull up to the minute results.
clsDBGet db = new clsDBGet(); DataTable dt = db.GetDTfromProc("[STORED PROCEDURE - GET]", null); if (dt.Rows.Count > 0) ga.Text = dt.Rows[0]["DATA"].ToString(); -
This sis where we tap into Reimer's reader. Further explanation can be found in the link at the top of this article but for now let's focus on some key points. I'm pulling data from Google from 2 days ago, through today. I'm also pulling the pagePath, pageTitle and using the metric pageViews. This will translate into the URL of the post, the title of the post and the hits. The last thing to note is that I'm pulling the top 1000 results because not every page on the site is an article and I want to make sure I get at least 10 articles in the data I'm pulling back. This number is arbitrary but I think it defaults at 1000 anyway.
ReportRequestor rr = new ReportRequestor("[gmail account email]", "[password]"); AnalyticsAccountInfo ainfo = new AnalyticsAccountInfo(); IEnumerable(AnalyticsAccountInfo) accounts = rr.GetAccounts(); string analyticsTitle = "V2 Live";//Title of Profile where I want to grab the data AnalyticsAccountInfo account = accounts.First(a => a.Title == analyticsTitle); DateTime from = DateTime.Now.AddDays(-2);//last 24 hours (data is always a day behind) DateTime to = DateTime.Now; IEnumerable(GenericEntry) report = rr.RequestReport(account, new Dimension[] { Dimension.pagePath, Dimension.pageTitle }, new Metric[] { Metric.pageviews }, from, to, 1000); -
In this next part I'm setting my variables that I will be using later in the loops to filter the rows.
string myString = string.Empty; int x = 0; string[] articleTitles = new string[22]; //To only grab unique titles ArticleInfo[] articleData = new ArticleInfo[22]; bool unique = true; report = report.OrderByDescending(myReport => Convert.ToInt32(myReport.Metrics.First().Value));
I'm also creating an array of ArticleInfo's called articleData which will hold 22 elements as I only want to display the top 22 articles. Lastly, I'm ordering the report data by hits (pageViews) because the report data comes from Google sorted by URL path. This is bad because I want to get the top articles, not a list ordered alphabetically by URL. Without this sort, I would be getting articles in a certain category only (because the URL lists the category before the title) and it would not be a true representation of site activity.
-
Here come the loops:
foreach (GenericEntry myReport in report) { Regex isArticle = new Regex("/articles/.+/.+/"); string path = myReport.Dimensions.First().Value; string title = myReport.Dimensions.Last().Value; string hits = myReport.Metrics.First().Value; if (x < 22 && isArticle.IsMatch(path)) { if (!path.Contains("/page_") && !path.Contains("/search/") && title.Trim() != "(not set)") { //Add to array of article titles then check to see if it exists in the array for (int y = 0; y < articleTitles.Length; y++) { if (articleTitles[y] != null && title.Trim().ToLower() == articleTitles[y].ToLower()) { unique = false; break; } else unique = true; } if (unique) { articleTitles[x] = title.Trim(); articleData[x] = new ArticleInfo { path = Server.UrlDecode(path).Trim(), title = title.Trim(), hits = hits }; x++; } } } }Lots of stuff is going on here. The first thing I'm doing is creating a filter to filter through the results and only pull back pages that are articles by doing "isArticle.IsMatch(path)". I also don't want to do this loop more than I need to, hence the "x < 22" part. The next "IF" statement is arbitrary and required for the site.
The next FOR LOOP is to make sure I'm only grabbing unique articles. This is important because sometimes users include capital letters in the URL and this will make Google Analytics display multiple rows for the same page and split up the numbers. There's a way to counter this in GA but for today's purposes, I'm assuming that your results might have the same page split across different URLS. I take the title of the article and check to see if it's in the articleTitles array. If it isn't already in there, I add it to the array and set the "unique" flag to true and move on. If it is, I skip that row altogether by setting the "unique" flag to false. (This method will probably produce some skewed results if you are splitting page views across URLs).
-
In this last part, I'm creating another enumerable list of articleInfo's and sorting it by title. This way the bar graph that I display won't just look like a linearly decreasing graph. I also create the HTML list item and append it to the full list of items in myString. This is what will be rendered out on the page and also what will be placed in the database using the stored procedure, "V2_INSERT_GOOGLE_DATA".
If there are less than 10 results, or if for some reason I cannot communicate with either Google or the database, I hide the panel altogether.IEnumerable(ArticleInfo) articles = articleData.OrderBy(article => article.title); foreach (ArticleInfo article in articles) { myString += "<li><a class=\"gaHits\" href=\"" + article.path + "\" title=\"" + article.title + "\" data=\"" + article.hits + "\"></a ></li>"; } if (x < 10) { panelGA.Visible = false; } else { clsDBPost dbp = new clsDBPost(); string proc = "[STORED PROCEDURE - INSERT]"; Hashtable ht = new Hashtable(); ht.Add("data", myString); bool inserted = dbp.ExecuteProcedure(proc, ht); } ga.Text = myString;
I hope this helps some of you who are trying to implement this method of pulling data from Google. I used jQuery to have the list animate as a bar graph with the article titles appearing on hover. This is the final result:
[...] This post was mentioned on Twitter by Gustavo Tandeciarz. Gustavo Tandeciarz said: How to pull data from Google Analytics using ASP.net and then sort that data first by hits then by title http://bit.ly/dhhZs4 #selfserving [...]
Buy:Prozac.Buspar.Lipitor.Lipothin.Amoxicillin.Nymphomax.SleepWell.Ventolin.Aricept.Female Cialis.Advair.Seroquel.Cozaar.Lasix.Wellbutrin SR.Zetia.Zocor.Acomplia.Female Pink Viagra.Benicar….