Calender Heatmap with Google Analytics Data

As a data analytics consulting firm, we think we are fortunate that we keep finding problems to find. Recently my teammate found a glaring problem of not having any connector for R with Google. With inspiration from Michael, and Ajay O, it soon becomes a worthwhile problem to solve.

With the RGoogleAnalytics package now, we have solved the problem of data extraction into R from Google Analytics a new breed of ideas started emerging primarily around visualization. I have been playing with GGplot2 has been a great package to convert data into visualization. Thanks, Dr. Hadley Wickham. Once you have followed this blog post, you are with the code there in a position to have the data required to get these calendar heatmaps done. Take up the below-given code and paste it into the R console and play around to see if you find it easy to work thru. If you have trouble, feel free to reach out to us.

Here is the code for extracting the Google Analytics data using the R-google Analytics package. Before running the following code, download the RGoogleAnalytics package and install it.

#Load RGoogleAnalytics library
library("RGoogleAnalytics")

# Create query builder object
query

For this example of a Calender heatmap, I am using data from an e-commerce store with data for more than 2 years in business. I will be plotting visits as well as transactions on the calendar so that I’d get perspective on how they interact viz-a-viz timeline.

Here is the code for plotting the heat map after you get data and have it stored in ‘data’. This frame is used to reference the source of data for the visualization below.

# Recommended R version - 2.15.1 or higher 
# install required  library by using the command install.packages(“libraryname”)
# For example install.packages(“ggplot2”)
# Required library
library(“quantmod”)
library(“ggplot2”)
library(“reshape2”)
library(“plyr”)
library(“scales”)

# Set extracted data  to this data frame
data

Once you run the code, you will be in a position to get the output like the below:

Now that we have a calendar heat map for visits, let me pull it off for transactions. In the above code for Google Analytics data extraction, you have to use transactions as well as visits as metrics. Since the data is already available in the ‘data’. we are ready by changing in code of visualization to choose the heat map for the transaction now.

It’s quite interesting now that you can make super nice inferences as I did below:

  • Tuesdays have high visits days but Wed has been the day when most transactions occur.
  • Visits increase towards the end of the year (shopping season) and then slow down towards the year’s start.

Visualization is an interactive process. Based on the feedback received from some of our readers, I tried plotting both KPIs on the same graph. With ggplot2, it was as simple as adding a line of code. Again some minor tweaks in the background colors and we are ready with another heat map.

ggplot(data, aes(monthweek, weekdayf, fill = visits)) +
 geom_tile(colour="white") +
 facet_grid(year~monthf) + 
 scale_fill_gradient(high="steelblue",low="white") +
 theme_bw() + 
 geom_point(aes(monthweek, weekdayf, size=transactions,alpha=transactions),color="firebrick") +
 theme(panel.grid.minor=element_blank(), panel.grid.major=element_blank()) +
 labs(title = "Time-Series Calendar Heatmap") +
 xlab("Week of Month") +
 ylab("")

 

Would you like to understand the value of predictive analysis when applied to web analytics data to help improve your understanding relationship between different variables? We think you may like to watch our Webinar – How to perform predictive analysis on your web analytics tool data. Watch the Replay now!

, , , , ,
Previous Post
Web Page Sequencing Analysis Using Google Analytics
Next Post
Measuring your Google Adwords campaign simplified

4 Comments. Leave new

  • Hey, pretty good example. It works fine although I’m using a different library…. I think you should change the “data2$weekdayf” definition due to day 0 is Sunday and no Monday…. I was checking a calendar and it is different right there. Your code should be:

    data2$weekdayf <- factor(data2$weekday,levels=rev(0:6), labels=rev(c("Sun", "Mon","Tue","Wed","Thu","Fri","Sat")), ordered=TRUE)

    Reply
  • Using lubridate simplified some of the date component handling but made it difficult to accurately display the Sun to Sat week display desired. So I created a R function based on http://blogs.technet.com/b/heyscriptingguy/archive/2005/05/05/how-can-i-determine-the-week-of-the-month-a-date-falls-in.aspx
    # Function WoM ————————-
    # Determines week of month for specified date
    # assumes day of week 1:7 for Sunday thru Saturday
    # First week of month, no restrictions; can be only 1 day
    # Year and month exist as columns in dataset
    #
    # INPUTS
    # (x1) day of the month rrtot$monthday
    # (y1) first of month as.Date(paste(rrtot$ryr,as.numeric(rrtot$rmo),”01″),”%Y%m%d”)
    #
    # PROCESS
    # Find Day of Week integer for the 1st of Month/Yr for date being evaluated
    # Create vars for end of each week in the month of interest (w1:w6)
    # determine which week of the month the date of interest belongs in
    #
    # OUTPUT
    # mw – monthweek as integer
    # ##
    WoM <- function(x1, y1) {
    m1 <- wday(y1) # day of week for first day of month from x1
    w1 <- 8 – m1 # establish last day of the week for current month
    w2 <- w1 + 7
    w3 <- w2 + 7
    w4 <- w3 + 7
    w5 <- w4 + 7
    w6 <- w5 + 7
    mw <- ifelse(x1 w5, 6, # compare day of month to end dates of each week
    ifelse(x1 w4, 5, # continue until week of month found
    ifelse(x1 w3, 4,
    ifelse(x1 w2, 3,
    ifelse(x1 w1, 2, 1)))))
    mw
    }

    # call function
    # rrtot$monthweek <- WoM(rrtot$monthday,as.Date(paste(rrtot$ryr,as.numeric(rrtot$rmo),"01"),"%Y%m%d"))

    Reply
    • Avatar
      Amar Gondaliya
      April 8, 2013 9:39 am

      Hi mielniczuk,
      Nice function created. You are right that it is difficult to display Sunday to Saturday week, week days have to be assigned appropriately. Created function is useful, if we want to display Sunday to Saturday week. In the current code(displayed in the post) week is set Monday to Sunday and it is working well.
      Regards,
      Amar

      Reply
  • Avatar
    Amar Gondaliya
    April 8, 2013 9:25 am

    Hi Anders,
    I think code has been changed now on the post. In the code, week day 0 is assigned to 7 and converted into factors Monday to Sunday as below.

    data$weekday[data$weekday==0] <- 7
    data$weekdayf <- factor(data$weekday,levels=rev(1:7),
    labels=rev(c("Mon","Tue","Wed","Thu","Fri","Sat","Sun")),
    ordered=TRUE)

    I have checked it and it is working well.

    Regards,
    Amar

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed

Menu