SQL for Marketing Analysis – All Google Analytics Analysts Should Know

Marketers and Marketing Analysts generally depend on the tools or IT department to help them pull the data for marketing purposes. There comes a time when they can’t just wait around for IT to help them data pulls and manipulations.  They have to know how to do it on their own. This course is for those marketers who would like to know how to use SQL to conduct their marketing analysis.

The course uses MYSQL to show how SQL works but all the leanings and syntax are applicable to other databases as well.  Sign up for SQL for Marketers and Marketing Analysts

SQLForMarketersCoverImage

5 Online Google Analytics Courses You Might Like

Few Online Courses to Learn Google Analytics

I came across few online courses on Google Analytics that might help you as you are learning or improving your Google Analytics skills.  I have not personally not gone trhough these courses so can’t vouch for how good they are but I have used the reviews of others, who have taken these courses, to rank them.

If you have an online course that you teach or love, then send me the link to include it in this list.

    1. Google Analytics for Beginners – Learn to use Google Analytics for uncovering actionable data and growing your business online.
    2. The Complete Google Analytics Course For Beginners - Learn Google analytics and its strategies to increase the traffic and sales of your business
    3. Google Analytics Mastery – Sky rocket marketing results through the power of data analysis and Google Analytics!
    4. Google Analytics 2015: Turn Data Into Strategic Decisions – Google Analytics: Grow your business by setting goals, tracking marketing analytics & performing business analysis
    5. Google Analytics Fundamentals – Learn the fundamentals of Google Analytics including core concepts, the interface, using reports and customization.687474703a2f2f7777772e6461696d746f2e636f6d2f77702d636f6e74656e742f75706c6f6164732f323031332f31312f676f6f676c652d616e616c79746963732e706e67

 

6 Reasons Why Your Google Analytics Reports Might Be Wrong

tumblr_mxa45rwot71ro4c22o1_500

  1. Missing Tags – This is the most common error of wrong data.  This generally happens when new pages are added or the exiting pages are redesigned/recoded and the developer forgets to include the tags.  Make sure all of your pages are tagged with Google Analytics code.  You can use a tool like GAChecker, to verify if the Google Analytics tags are missing on any pages of your site.
  2. Mistagged Pages – Incorrect implementation or double tagging leads to wrong data in Google Analytics.  Double tagging results in increased page views and a low bounce rate. If you bounce rate is lower than 20% then that’s the first thing you should check.
  3. Location of GA Tags – Placing the tag towards the bottom of the page could result in no data particularly for the users with slow connections or pages that are slow to load.  This happens when a user tries to loads a page and clicks on another link before the first page is loaded. Since the Google Analytics tag is towards the bottom of the page, it might not get a chance to execute.  To avoid this issue, put your Google Analytics JavaScript in the <head> section of the page.
  4. Incorrect Filters – Wrong Filters can mess up the data and distort the view.  Always create an unfiltered view so that you have correct data to fall back on.
  5. Tags Not Firing Properly – This can happen when your page(s) have JavaScript errors.  A JavaScript error on any part of the page can result in an error in Google Analytics code. Verify the JavaScripts on your site to make sure there are no errors.
  6. Sampling – Sampling happens on highly trafficked site. Sampling in Google Analytics is the practice of selecting a subset of data from your traffic and reporting on the trends available in that sample set.  For most purposes, this might not be a non-issue however it can be of concern in eCommerce sites where sampling can (will) result in wrong sales figures.   You can get more information about GA sampling on “How Sampling Works“.

Tracking Campaigns in both Google Analytics and Omniture Site Catalyst

If you are using both Google Analytics and Omniture Site Catalyst then you know that both these tools require different campaign variables to track campaigns. So what are the ways you can satisfy the requirements of both the tools so that you can see campaign performance in both of them.

Option 1 (Best Option): Use multiple query parameters

For GA you should use utm_campaign, utm_source, utm_medium

For Omniture add another parameter say ocid

Example: http://gapremium.com?ocid=123&utm_campaign=spring&utm_source=msn&utm_medium=banner

Option 2: Use one set of variables for one tools and convert them for other tool

For example: If you have GA enabled campaigns with only utm_campaign, utm_source and utm_medium already running then you can campture google campaign variables in javascript code , concatenate them to create a single value and then set that value in Omniture s.campaign variable.

Example:

http://gapremium.com?ocid=123&utm_campaign=spring&utm_source=msn&utm_medium=banner

Capture all the Google Analytics campaign variables in a JavaScript code as

campaignvalue = ‘spring : msn : banner’

The set this as the value for s.campaign value in Omniture

s.campaign = campaignvalue;

Get Google Analytics Help When You Need It

GAPremium is a Google Analytics expert consulting company.  We know Google Analytics in and out and that’s what we focus on.  If you need help with Google Analytics then we have the resources for you.

How is GAPremium Different from other Analytics Consulting Companies?

  1. We only focus on Google Analytics and related technologies only.
  2. We offer support for Google Analytics when you need and never lock you in for a long term contract
  3. Even if you need someone for few hours then we have the resources for you.
  4. We support your business 7 days a week

For Companies

  1. Get help when you need and as much you need. Minimum 1 hour commitment
  2. Get you internal technical teams trained in Google Analytics
  3. Get your business users trained in using Google Analytics to make data driven decisions

For Agencies

  1. White label our services and solutions
  2. Build you internal competency and internal team with help of our experts
  3. Tap into our resources as needed

Contact us today at support@gapremium.com for a no obligation initial consultation.

Google Analytics Event Tracking Builder Sheet V1

Event Tracking gets compiles when you have to track multiple events on a site. Not only you have to know the right syntax but also you have to keep track of all the values you are using for Category, Actions and Labels. To make your life easy we are releasing the V1 of Event Tracking Builder Sheet. This sheet will help you in getting organized and also provide you a code that you can copy and paste on the actions that you want to track as events.

 

Download Your Event Tracking Builder Sheet

 

Also, see Tracking Non Google Analytics Campaign Parameters in Google Analytics

Other Downloads:
Download your copy of the Google Analytics Campaign Tracking Sheet

Google Analytics Campaign Tracking Made Easy

We have just released V1 of Google Analytics Campaign Tracking Sheet. This sheet makes it easy for you to keep track of the campaign your are running and also allows you to easily build a tracking URL that you can copy and paste.

Please send us your feedback so that we can improve this sheet. Feel free to tweet about this and share the love.

V2 is already in works and will be released shortly.

Download your copy of the Google Analytics Campaign Tracking Sheet

Also see How to Track Campaigns in Google Analytics

Tracking Non Google Analytics Campaign Parameters in Google Analytics

Google Analytics can track up to 5 campaign variable of which 3 are required.  Generally, the campaign variables are passed via URL of the campaign landing page.   For example, if you  want to send users to http://www.GAPremium.com via a campaign then you might use something like

http://www.gapremium.com? utm_campaign=Feb2012bannercampaign&utm_medium=bannerad&utm_source = yahoo&utm_content = 300X250&utm_term=training

In the above URL, all the query parameters are the campaign parameter that Google Analytics needs to track and attribute the click to the campaign. (See How to Track Campaigns in Google Analytics for more details)

In some case you might not have the campaign parameters as formatted above, maybe because you were using some other web analytics tool that required different parameters or somebody made a mistake and the parameters are messed up. Whatever the reason ; you just don’t have the right parameters.

Let’s assume that instead of the URL above, your URL looks like following

http://www.gapremium.com? campaign=Feb2012bannercampaign& medium=bannerad& source = yahoo& content = 300X250&term=training

(Note that none of the parameters contain utm_, e.g. instead of “utm_campaign”, the URL only has “campaign”)

So Google Analytics won’t recognize these parameters as valid campaign parameters and will ignore them.  However you can fix the problem by forcing Google Analytics to recognize these variable as valid campaign variable by using few lines of codes. The way you fix them is by calling a function that sets the new campaign parameter

_gaq.push(['_setCampNameKey', 'campaign']); 

The above code tells Google Analytics to use ‘campaign’ as the CampaignName parameter instead of the ‘utm_campaign’ parameter

Similarly set other parameters as follows

_gaq.push(['_setCampMediumKey', 'medium']);

_gaq.push(['_setCampSourceKey', 'source']);

_gaq.push(['_setCampContentKey', 'content']);

_gaq.push(['_setCampTermKey', 'term']);

Using Google Analytics to Detremine Who Visited the Site

Customer and visitors alike visit your site but your Google Analytics reports show you aggregate numbers to show how many visits came, from what sources, how many of the viewed a certain page etc.   But these reports don’t tell who visited your site, weather that is an existing customer or someone who has not purchased anything yet etc.

However, there is a way in Google Analytics that allows you to know “Who” visited the site, what sources they used, what pages they saw etc.

Setting Customer ID in Google Analytics

If you store your customer records in a database or CRM solution then you assign each customer and ID that identifies each individual customer.  Assuming you this ID for individual available either in a cookie or in the web session when that individual comes to your site.  You can extract that ID from the cookie or session and pass it to Google Analytics using visitor level custom variable as shown below to get individual level reporting.

_gaq.push(['_setCustomVar',index, name, value, opt_scope]);

(Note: You can get more information about Custom Variable at http://code.google.com/apis/analytics/docs/tracking/gaTrackingCustomVariables.html)

Example

Let’s assume that we have that we have a customer “John Doe” identified by customer id 21590 in the database. Let’s further assume that this value is available to us in the visitors online session.

Let’s use the name “UserID” for this custom variable. The slot we will use to store this variable is 3 (You can use whatever slot is available but I decided to store it in slot number 3). The opt_scope is 1 for a Visitor Level custom variable

So the values that we need to set are

index = 1

name = UserID

value = 21590

opt_scope = 1

The way you will set is

_gaq.push(['_setCustomVar',1, "UserID", "21590", 1]);

Once Google Analytics collects the data you can start to see this data in your Google Analytics reports.

 

Note: Google Analytics does not allow you to pass in PII (Personally Identifiable Information) data but an id, which on its own does not tell you anything about the person is not PII in my opinion. Google Analytic does not clarify if you can use it or not so use it at your own discretion or consult Google Analytics, if they respond.

 

 

How to Find the Keywords marked as “(not provided)”

A few months ago, Google, the search engine, started encrypting searches for user who are logged into their Google account while conducting the search. As a result of this encryption, the keyword that the visitors search to arrive to your site is not passed in the referring URL. Web Analytics tools rely on the keywords passed in the referring URL to build the search engine traffic report and in the absence of the keywords there is nothing to report, though they still see that the visits came from Google search. So Google Analytics now marks those visits that do not have a keyword but come from Google with “(not provided)” keyword instead of the actual keyword.

Finding those keywords

Google still tracks all the keywords search by logged in users but just does not pass it in the referrer to the site that the user clicks through to. These keywords are available in the Google Webmaster Tools.  To see the report you will have to register your sites in Google Webmaster Tool. Google Webmaster tools will allows you to see all the keywords that were searched, the number of clicks your site got, the average position of your site for those keywords and the landing pages.

 

(All images provided by GAPremium.com – Google Analytics Tips and Tricks)

If you are using Google Analytics then you can connect Google Analytics reports and Google Webmaster tools to get Webmaster reporting within the Google Analytics interface.

However there are three issues with this report when used with Google Analytics (or any another web analytics tool)

  1. You don’t get other metric (e.g. goal conversion) about the visits that arrived from the keywords.
  2. This list of keywords includes not only the keywords marked with “(not provided)” but also the other keywords that you see in Organic traffic report. So you will have to do extra analysis to see which keywords are hidden under “(not provided)”.
  3. If you look at Google Webmaster tool report then you will notice that there are a lot more impressions and clicks than those displayed in the Webmaster report and the Google Analytics report (see below). I was not able to find a reason why Google is only displaying the partial number of keywords, if you know the reason then please let me know.