In this blogpost, I want to dive deeper into the explanation of the relationship between Frequency and Recency of Visits with the Conversion Rate and Average Order Value. I have used the RGA package for data extraction and Dr. Hadley Wickham’s ggplot2 package to achieve the visualizations.

Here’s the data aggregation script :

#transactions dataframe contains the input data extracted via RgoogleAnalytics
head(transactions, n = 3)
#  visitsToTransaction daysToTransaction transactions transactionRevenue
#1                   1                 0         1639           11505429
#2                  10                 1            1               3700
#3                  10                10            1               6050
transactions$visitsToTransaction <- as.numeric(transactions$visitsToTransaction)    #Convert metrics from chr to numeric
transactions$daysToTransaction <- as.numeric(transactions$daysToTransaction)
#trans_data is a placeholder data frame
str(transactions)
str <- c("1-3","4-6","7-9")                   #Create three labels
trans_data <- data.frame(Visits_To_Transaction=c(1:9),
                  Days_To_Transaction=c(1:9),
                  Transactions = c(1:9),
                  Revenue = c(1:9),
                  Average_Order_Value = c(1:9),
                  stringsAsFactors=F)         #Placeholder to hold aggregated data frame
index<-1                  #Initialize index
for (i in 1:3)
{
  for(j in 1:3)
  {
    trans_data$Visits_To_Transactions[index] <- str[i]
    trans_data$Days_To_Transactions[index] <- str[j]
    subset <- transactions[(transactions$visitsToTransaction>=(3*i-2)&transactions$visitsToTransaction<=3*i)&(transactions$daysToTransaction>=(3*j-2)&transactions$daysToTransaction<=3*j),]
    trans_data$Transactions[index] <- sum(subset$transactions)
    trans_data$Revenue[index] <- sum(subset$transactionRevenue)
    trans_data$Average_Order_Value[index] <- trans_data$Revenue[index]/trans_data$Transactions[index]
    index <- index + 1
  }
}
head(trans_data,n=3)
#   Visits_To_Transaction  Days_To_Transaction Transactions   Revenue Average_Order_Value
#1                    1-3                  1-3          167 1085055.0            6497.335
#2                    1-3                  4-6           42  420142.2           10003.385
#3                    1-3                  7-9           20  135783.2            6789.163

We now convert our data into a visualization using the ggplot2 package. Here's the command:

require(ggplot2)
aov <- ggplot(trans_data,aes(Visits_To_Transactions,Days_To_Transactions)) +
  geom_tile(aes(fill=Average_Order_Value),colour="black") +
  theme_bw() +
  geom_text(aes(label=round(trans_data$Average_Order_Value),
                size=trans_data$Average_Order_Value),color="white") +
  scale_size(range=c(3,12)) +
  ggtitle("Average Order Value (in Rs.)") +
  xlab("Visits to Transaction") +
  ylab("Days to Transaction") +
  theme(legend.position="none",
        axis.text.x= element_text(size=16),
        axis.text.y=element_text(size=16),
        axis.title.x=element_text(face="bold"),
        axis.title.y=element_text(face="bold"),
        plot.title = element_text(face="bold",size=30))
aov

Let us make some quick inferences:

• When the consumers visit 1-3 times across a period of 4-6 days they tend to buy the most expensive products
• When they visit the site 7-9 times across a very small period of 1-3 days, these might be the consumers who visit repeatedly to keep a tab on offers and prices of a product of their choice they too have a higher Average Order value
• Spontaneous buying decisions are made when the Visits and Days to Transaction are in the 1-3 categories. As expected, the average order value for these transactions is on the lower end.

Let us do a similar exercise for the Conversion Rate. We extract data corresponding to the dimensions: Visit Count, Days since Last Visit and Metrics: Visits. Repeat the same steps as before. We already had the Transactions binned and categorized earlier. We now divide the total number of Transactions across each category to the Total Visits in order to get the Conversion Rate and plot it.

Both the plots stacked up together help us understand the relationship between the Average Order Value and the Conversion Rate which in this case seems to be an inverse relationship i.e. AOV tends to be higher when Conversion Rate is the lowest. Now, this correlation may not imply an underlying causation therefore we need to drill down further to verify our hypothesis.

Consumers do tend to visit the website multiple times before making a purchase. Some might buy right away, but most of them will research a bit and come back. With this realization, we could focus on giving them more information to help with their research and getting them to convert at their own pace. On the other hand, if the time period is short (for e.g. 1-3 Visits in 1-3 Days) and the purchase is more spontaneous we have some room for improvement here (Average Order Value: 6497, Conversion Rate 1.37 %). We could play with pricing strategy and thereby to increase the Average Order Value or provide a referral discount and get more consumers to convert. Of course, this has to be done keeping the site’s business objective in mind.

Being a web analyst is very challenging at times, isn’t it? I believe that most of you have already heard that doing data analysis is like finding needle in a haystack. And, therefore, considerable amount of time has to be devoted to the whole web analytics cycle.

It’s also known that the majority of web analysts spend most of their time scraping a mountainous pile of raw data and extracting them from Google Analytics/Adwords accounts to a BI tool, in order to prepare business reports/dashboards. This way, they barely get enough time to spend on data analysis itself.

If you find yourself in this situation, cool down! I will show you a couple of ways to optimize your time expenditure on data extraction process and you will realize that there is no reason to freak out!

Extracting Data from Multiple Profiles

Usually, whenever you need to prepare reports for multiple accounts/profiles using identical metrics and dimensions, you manually extract data from their Google Analytics/Adwords profiles. This process involves the extraction of multiple reports from GA UI and subsequently consolidating them into a single workbook. This may even require some data cleaning for efficient processing. As you can realize, this is a very cumbersome process. Tatvic Excel Plugin is a tool which facilitates you to extract data from multiple accounts/profiles at one go.

Single Click Refresh

What about when you need to update many of these dashboards/reports on a weekly, fortnightly or monthly basis? Then, you often have to go to each and every client’s profile to extract their data and update your reports/dashboards. This is a headache for analysts, isn’t it?

What if I tell you that with this plugin you can update all your existing reports/dashboards by “SINGLE CLICK on Refresh” functionality? That’s right! With only a single click, all your reports become updated in a few seconds and you are done!

See? You definitely have a way out!

Now that you are aware of these tips, you can finally find yourself in your dream scenario: extracting your data effortlessly, automating your dashboards and spending the majority of your working hours on data analysis.

Do you aim to know how you can save even more time during your cumbersome data extraction process?

So, you should definitely watch our webinar: How to simplify and automate data extraction process using Tatvic Excel Plugin. Watch the Replay Now!

As some of you guys may know, our GTM Webinar “Learn how to make Google Tag Manager implementation easier” took place last Wednesday (April 10th).
We really hope that after attending this webinar you are now able to have a much easier life, when talking about GTM implementation.
In case you were unable to attend the live event, you may want to have a look at our Replay.

Now, as we have promised, we would like to share with all of you the answers for the queries that we have received during the webinar.

If you any further question regarding Google Tag Manager, we would love to help you out.

Q: Does the Tag type you select from the drop down menu matter?
A: Yes, definitely. If you want to implement Google tags you can use the GTM inbuilt tags such as Google Analytics, Adwords Conversion, Adwords Remarketing, etc. For non-Google tags you can use custom HTML tag of GTM. The tag should be selected according to the requirement.

Q: Why have you used custom HTML tag rather than the Google Analytics tag?
A: In the example, we tried to change the clumsy URL to turn it into a friendly name. In general, any change that you want to do to standardize GA code will be better executed using custom HTML tag instead of standard GA tag.

Q: What is the best practice for implementing two UA Codes on the same page? Custom HTML tag or two Google Analytics tags?
A: Ideally, you would want to have two different custom HTML tags for two different UA Codes for the same page. However, there will be just one GTM container code on that page, which will contain two different custom HTML tags. These two custom HTML tags will contain & fire two different UA codes based on rules.

Q: In GTM how do you link the new container/tag/rules/macros made with the Google Analytics UA code?
A: The default functionality of GTM has 4 different elements: container, tags, rules & macros. All of them have many to many relationships. GTM by default saves the combination of the 4 of them for each version that you create and, thereby, they are linked with one another.

Q: So is there a section under GTM that lets me input the corresponding GA UA code?
A: Yes. If you create new tag, you can use Google Analytics tag as type then insert GA UA code.

Q: To implement GTM, do i need to add GTM container code in all pages, like we add GA code in all pages?
A: Yes, you have to add it on all your pages.

Q: Which tool have you showed that lists all the request ID numbers, subject, status, priority, etc?
A: We used Trac, which is an open source management tool that we have customized for our purpose.

Q: I’d like my company to become GTMCP. What do I need to do to obtain this certificate?
A: Google Analytics certified partners can become Google Tag Manager certified partners. If your company is Google Analytics certified partner you should contact with the account manager to understand what all is required to be a Tag Manager certified partner.

Q: I could not find any GTMCP in my country. Can you help?
A: If your country doesn’t have a GTMCP company, we can help you locate the nearest one or we can help you from our office locations as well.

Q: Does GTM have any limits regarding the number of tags, rules, etc?
A: GTM Tags has the limit of 15,360 characters. Other than this, GTM has no other limits as of now. There is no hard limit in GTM regarding the number of tags, but it is recommended to keep the number of tags as lean as possible.

Q: Why do I need to create a version? What is its role?
A: A version is essentially a snapshot of the container. You can save the current state of container anytime and you can go to the previous version whenever you need to.

Q: Why should I give argument to track page view function? Just to see a friendly URL?
A: Yes, that is correct. There are times when business analysts are better off with better URL naming convention than with clumsy URLs. But that’s not always the case.

Q: As an agency, can I use GTM for my clients? How?
A: Yes, GTM can be used by agencies. We understand that many agencies face challenges such as managing multiple clients and monitoring tracking progress of their measurement plan from single location and sharing the progress by way of granting different user levels of permission to clients. Infact, GTM has multi-account feature that allows you to create different accounts for different clients. Thus, you can determine appropriate workflow and user level permissions based on clients to manage their tags efficiently.
We recommend that end clients should create their own accounts and then grant access to their agencies using their permissions feature.

Q: When I allow marketers to take control of analytics tags, how do I ensure that it won’t break my site?
A: GTM has a tag preview mode with automatic error-checking that prevents publication if tags are not properly deployed on website.
There’s also a feature called version history, which makes sure that no faulty codes are placed on website.

Q: Can I include non-Google tags as well?
A: Yes. You can add custom code for any JavaScript tag you like, but it’s extremely important to check out Google Tag Manager’s Use Policy before using.

Q: Does GTM collect any data? If yes, what does Google do with this information?
A: Actually, GTM is a solution for marketers to manage website tags, from one interface. The tag manager tool itself (which deploys the tags) is a cookie-less domain and doesn’t collect any PII (personally identifiable information). It only instructs other tags to fire and these other tags may collect data, that is not accessed by GTM.

Q: Does it work on mobile and other platforms?
A: Yes, Google Tag Manager can also be used on mobile sites. You can manage your mobile tags and your standard website from the same GTM account.

Tons of material is available on internet about what Google Tag Manager is? Below mentioned image describes what Google Tag Manager is ‘NOT’.


You may also want to have a look at the explanation that has been given during our webinar:

Here is the simplest way to understand elements of Google Tag Manager. The below mentioned image shows that how you can correlate elements of GTM with real life object for better understanding.

You may also want to have a look at the explanation that has been given during our webinar:

Here is the example of how Google Tag Manager differs from traditional way of implementing java script codes. You can see in the below mentioned image how an implementation specialists have to insert java script codes on pages which need to be tracked. This can be simplified by GTM using containers and can be controlled according to requirements.


You may also want to have a look at the explanation that has been given during our webinar:

Whenever we want to research for information or services on the internet, we open a search engine page, like Google, and we type the words that refer to what we are looking for.

Most of the times when results are listed, we notice that the very first ones are “sponsored links”. These links are advertisements, which are identified through Google Adwords, an advertisement service provided by Google in order to help you market your products and services on its search engine and affiliate websites.

But how does it work and how much does it cost?

Once you create an ad, you need to identify which are the words that you want to be related to it. Then, every time visitors search for phrases or keywords that you have linked to your offer, your ad will be shown to them at the top of the page, as a “sponsored link”. As the payment system is called “Pay per click”, you will only pay for that advertisement if visitors click on the same. This way, you can bid for series of phrases or keywords, but you will pay only for the successful ones.

As a marketer, you need to identify whether visitors come to your website just to collect information (“browsing traffic”) or if they come looking for specific products and services. Google Adwords helps you identify and differentiate these segments of visitor, impacting directly on your website’s revenue.

Moreover, researches indicate that 87% of people who are researching through search engines do not scan past their first page. Google Adwords allows you to decide in which page of search you want your ad to be shown. You can even choose in which order you want your ad to appear, to increase its effectiveness, once visitors are more likely to click on ads that are placed on the top of search page.

Analyzing data from Google Adwords

Internet is the best medium to measure advertisement effectiveness. Marketers need to analyze data of every single online advertisement and for that they need to extract the data from their Google Adwords account to a MS Excel spreadsheet. Tatvic’s Excel Plugin for Google Adwords simplifies this step of data extraction from Google Adwords, saving tons of time for marketers/analysts. It can really make maketers’ lives easier by:

• Extracting data from multiple accounts simultaneously. There is no need to extract data separately from different accounts.
• Updating data with changed parameters by “Single click on refresh”.
• Sharing saved query with your team members, so that they won’t need to spend extra time to generate the same queries again.
• Being compatible with MAC and DFP.

Tutorial video of Excel Plugin for Google Adwords

Through this video, we explain how to use Tatvic’s Excel Plugin for Google Adwords. Once you have the plugin installed in your computer, this “how to” video will teach you its very first steps, like opening MS Excel and logging in, and its features.

If you need any further information related to this tool, please feel free to contact me!

Would you like to reduce the amount of time you usually spend on data extraction from your Google Analytics / Google Adwords account? We think you may like to watch our Webinar – How to simplify and automate data extraction process using Tatvic Excel Plugin. Watch the Replay Now!

We are very glad to be the only Indian company that holds a Google Tag Manager (GTM) Certified Partner certificate. GTM is a Free Tag Management system from Google which allows marketers to create and/or update their website tags within a few minutes.

Here, throughout this infographic, we try to help newbies to understand more about it.
We hope you find it helpful! Enjoy!

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

With 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 great package to convert data into visualization. Thanks Dr. Hadley Wickham. Once you have Following this blogpost, you are with the code there in position to have data required to get these calendar heat map done. Take up below given code and paste into R console and play around to see if you find it easy working thru. If you have trouble, feel free to reach out to us.

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

#Load RGoogleAnalytics library
library("RGoogleAnalytics")
 
# Create query builder object
query <- QueryBuilder()
 
# Authorize your account and paste the accesstoken
access_token <- query$authorize()
 
# Create a new Google Analytics API object
ga <- RGoogleAnalytics()
ga.profiles <- ga$GetProfileData(access_token)
 
# List the GA profiles
ga.profiles  # select index corresponds to your profile and set it to query string
 
# For example if index is 7 of your GA profile then set ga.profiles$id[7] in
# query$Init() method given below
# Build the query string
query$Init(start.date = "2010-01-01", # Set start date
           end.date = "2012-12-31", # Set end date
           dimensions = "ga:date",
           metrics = "ga:visits,ga:transactions",
           max.results = 10000,
           table.id = paste("ga:",ga.profiles$id[6],sep="",collapse=","),
           access_token=access_token)
 
# Make a request to get the data from the API
ga.data <- ga$GetReportData(query)  # data will be stored in this data frame
 
# Set date in format YYYY-MM-DD (to use into heatmap calender)
ga.data$date <- as.Date(as.character(ga.data$date),format="%Y%m%d")

For this example of Calender heatmap, I am using data of an e-commerce store with having data for more than 2 years in business. I will be plotting visits as well as transactions on 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 store 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 <-  ga.data
 
# Run commands listed below
data$year <- as.numeric(as.POSIXlt(data$date)$year+1900)
data$month <- as.numeric(as.POSIXlt(data$date)$mon+1)
data$monthf <- factor(data$month,levels=as.character(1:12),
                      labels=c("Jan","Feb","Mar","Apr","May","Jun",
                               "Jul","Aug","Sep","Oct","Nov","Dec"),
                      ordered=TRUE)
data$weekday <- as.POSIXlt(data$date)$wday
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)
data$yearmonth <- as.yearmon(data$date)
data$yearmonthf <- factor(data$yearmonth)
data$week <- as.numeric(format(as.Date(data$date),"%W"))
data <- ddply(data,.(yearmonthf),transform,monthweek=1+week-min(week))
 
# Plot for visits
P_visits <- ggplot(data, aes(monthweek, weekdayf, fill = visits)) +
  geom_tile(colour = "white") +
  facet_grid(year~monthf) +
  scale_fill_gradient(high="#D61818",low="#B5E384") +
  labs(title = "Time-Series Calendar Heatmap") +
  xlab("Week of Month") +
  ylab("")
 
# View plot
P_visits
 
#Plot for transactions
P_transactions <- ggplot(data, aes(monthweek, weekdayf, fill = transactions)) +
  geom_tile(colour = "white") +
  facet_grid(year~monthf) +
  scale_fill_gradient(high="#D61818",low="#B5E384") +
  labs(title = "Time-Series Calendar Heatmap") +
  xlab("Week of Month") +
  ylab("")
 
# View plot
P_transactions

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

Now that we have a calendar heat map for visits, let me pull it off for transaction. In the above code for Google Analytics data extraction you have use transaction 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 transaction now.

Its quite interesting now that you can make super nice inferences like I did below:

• Tuesdays have high visits days but wed has been the day when most transactions occurs
• Visits increases towards the end of year (shopping season) and then slows down towards year start

Visualization is an interactive process. Based on the feedback received from some of our readers, I tried plotting both the KPIs on the same graph. With ggplot2, it was as simple as adding a line of code. Again some minor tweaks in the background colours 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("")

Recently on a project, we were trying to implement the network path diagram and carry out an analysis with website’s page sequence data to know if there exists a specific traversing pattern among visitors.

While there are lot of visual representation available (such as Paditrack) of sequence of pages visitor follows the real beauty is an having such sequence as row data to have them analyzed.

For example if you know a specific visitor pattern is always observed before visitor initiates the sale, it would be great to know that. IF you know a specific content consumption results into visitors return on the website next day, it would be awesome for you.

By having data for sequences in raw format, you are aided with a tool that you can collect those data and eventually carry out such beautiful analysis.

We are using anonymous visitor id for each visitor in one custom variable on visitor level and set another custom variable on page level to have the incremented counter value which is stored and accessed from the cookies.

Implementation:

1. Download the script “Page Counter Script” from JavaScript Resource section.
2. Unzip the content and include the script on all the pages of your website.
3. We are calling another __utm.gif request using trackEvent (with Non Interaction event flag); you may also use trackPageView to pass the custom variable’s value to GA. (While firing with trackEvent call data may not appear in GA interface for page level custom variables. It’s available through API)
4. When you are done with the implementation on all pages of your website check with the implementation using “Omnibug” in Firefox or using Developer tool in Google chrome.
5. Once you see the gif requests are made to GA server and data would show under custom variables.

Note: We have used 2 custom variables as following:

How does it work?

1. We initialize a visitor id in one custom variable which sets to visitor level upon first visit and set a page counter cookie value to 1 and assign it to another custom variable on page level. In our case “vid” is customVar1 and “PgC” is customVar4.
2. Once visitor browse through other pages in session; page counter value is incremented by one and value is submitted to GA using trackpageview or trackEvent call.
3. On a next visit or if the visit count of GA is changed then page counter cookie value resets to 1 and begins the new counter for the new session
4. All the data can be extracted using GA API as mentioned below. Which can be used to represent visual flow from a page to another page

Google Analytics would show reports as following:

Visitor Id Data:

Page Counter Data:

Note: it would show above data only when used trackpageview. You can use event data for the calls made through trackevents.

Data extraction of the data collected:

Once the tracking is implemented on all pages and data is being collected on the GA interface. You can use data query feed explorer from Google Analytics using GA API v3 or use our excel plugin (http://www.tatvic.com/google-analytics-excel-pricing/). Data should be similar to image below.

Reference:

1. Visitor Id: Unique visitor id.
2. Page Sequence Counter: Sequence in which page is visited.
3. Visit Count: Visit count of a visitor.
4. Page Path: URL of the page

How it is different form Page-Depth
Page Depth gives you count of pages viewed by visitors in a session. Where as Page Sequence gives you which pages a visitor visited in a session along with it’s sequence.

Benefits:

• You can know how user follows through pages and can know navigation trail easily.
• You can find what are the pages which are important which makes users to interact with your website
• Helps you identifying pages from where user leaves your website if conversions are not happening. So that you can implement strategies to win your customers.

Want to implement this solution on your website and do the data analysis? Contact us