Automating Google Analytics Reports

If you followed my advice in my last post, you now have your web site traffic segmented perfectly in Google Analytics using custom segments.  While this gives you tremendous insight into where your customers are coming from, it can take some work to spit out meaningful reports each week.  While Google does have built-in reporting tools, you can only look at a few segments at once and frankly, the output of the reports can be a bit limited.

My preferred approach for analyzing Google Analytics data is to use the tool that so many office workers rely on every day - Excel.  Excellent Analytics offers an excellent tool for pulling Google Analytics data into Excel, and last time I checked, it was free.  What I will show you how to do is to use Excellent Analytics to build a spreadsheet that automatically pulls your weekly Google Analytics customer traffic data by segment into Excel.

The first step is to install Excellent Analytics.  I won't cover that here as the software provides instructions for that - it is a plug-in for Excel so it is pretty straightforward. Once you get it installed, you should see an extra tab in Excel like this.  Note the extra tab called "Excellent Analytics" on the right.

Next you will click on New Query and you will need to sign in with your Google Analytics account information.  You now can pick the metric you want to report on.  Let's pick "visits"...

Now pick the segment you want to report on.  Use the custom segments that we set up last week.  You will have one query for each segment...

Finally pick the start and end dates. When you are ready, hit the execute button.  This will bring the data back into Excel.  You basically will repeat this process for each segment you want to track.  You can summarize the data in a table or in a chart.  Each time you need to refresh the data, just hit "update query" for each segment, change the start and end dates if you like, and hit execute.  You will almost instantly have a powerful chart showing where your traffic is coming from that you can show to your management.

If You Are Not Using Custom Segments , You Are Not Really Using Google Analytics

So you use Google Analytics but you don't use custom segments.  Guess what, you aren't really using Google Analytics, at least not with any level of proficiency.  Let's explore why custom segments in Google Analytics are so critical.

Let's take two statements that I often hear from web site owners that best demonstrate the value of custom segments.  The first comment is, "My traffic is down (up), but I don't really know why."  If the person has some knowledge of Google Analytics, they know to click on the Traffic Sources menu option and drill down on each of the main sources that Google defines: Direct, Referrals, and Search.  Hopefully they also know how to set the date range so they can compare the traffic year over year.  However, the problem is that those three giant buckets don't really break the traffic into granular enough buckets to understand what is happening.  I like to look at traffic from these buckets:

• Direct - new visitor
• Direct - returning visitor
• Referral - other domains owned by my company
• Referral - other sites
• Email
• Paid search - branded
• Paid search - non branded
• Organic search - branded
• Organic search - non branded

While this list may need a little adjustment for a given business, it usually is pretty close.  As you probably noticed, there are a couple things I have done in this list. First, I broke the direct traffic into new visitors and return visitors.  Offline marketing campaigns (e.g., direct mail) will generally show up in the direct new visitors bucket.  Second, I have broken out traffic from any other sites that my company owns.  Often a company might have multiple domains that are not all under one Google Analytics account.  For example, they might have a marketing site, an external blog, and a e-commerce site.  If you are looking at traffic on the e-commerce site, you want to know how much is coming from the marketing site and the blog.  The third thing I have done is break out email as a traffic source.  Email is such a critical communication tool that it is key to have it stand on its own.  Lastly, I have broken out branded search under both paid and organic search.  If you don't know if people are finding you for your brand or for generic search terms, it is almost impossible to understand how search is doing.

Let's get these buckets set up in Google Analytics.  If you want, you can access much of this data from the standard traffic sources that Google provides if you know how to do it.  For example, to measure direct traffic - new visitors, you can go to Traffic Sources -> Direct and then click on Secondary Dimension and add Visitor Type and then click on the Advanced filter feature to filter Visitor Type = New Visitors.  However, there are quite a few steps here, and it is easy to mess something up if you are doing this all the time.

The better option is to use custom segments!  Click on the Advanced Segments button toward the upper left corner of the screen and then +New Custom Segment.  You now can define the criteria for this segment so you can use it over and over.  For this example we set Visitor Type = Returning Visitor and Source = (direct).  The sky is the limit for how you can you segment your customers with this functionality - you can create a canned segment for almost anything you can dream up.

Most of the segments are fairly straightforward to set up but there are a few things to note.  When you are setting up the non branded search segments, you will want to make sure you don't include your brand and any common mis-spellings of your brand name.  You will want to look at the keywords that people are using to find you (Traffic Sources -> Search - > Keyword).  The one bucket that requires a little work to set up is Email.  If you have been tagging all your outbound emails with Google tracking code and setting Medium = Email, you are in great shape because you can just set up the segment to filter on Medium = Email. If you don't know what I am talking about, take a look at this article on email tracking.  However, if you are like most people, you probably forgot to tag a few emails.  Ok, maybe you missed a lot of emails. The best thing to do at this point is to pick up people who are reading email via a web mail client.  To do that, I add an "or" clause to the segment and tell it to include visitors where Source includes "mail."

Now that you have set up custom segments, let me return to the second business question that segments help you answer.  Web site owners will often say, "My conversion is up (down)!."  That statement by itself is basically meaningless.  Conversion rates by segment vary tremendously.  Customers who are coming in on organic branded search have a very different conversion rate than folks who are coming in on non branded paid search terms.  As such, blending everything together is really meaningless because conversion might be up because you got a lot more traffic from organic branded terms and far less from non branded paid search.  When you are looking at conversion rates, you need to always drill down and look at it on a segment by segment basis.  That is where custom segments are key.

Go set up those custom segments today so you can really understand what is happening with your business!  Next week I will show you a great way to automate the analysis of segment data.

SEO - Get Started with 5 Quick Tips that Can Have a Big Impact

A couple times a week I get requests from people asking for help with SEO, search engine optimization. These are mostly small businesses who don't have the resources to hire a SEO consultant but know they need to take search seriously. They have good reason to think search is important.  I have seen companies get the equivalent of millions of dollars of free advertising because they were highly optimized for search.  Here are a couple quick things that someone can do to make an impact on their SEO rankings:

• Fix mistakes on your website. I like to start by fixing any mistakes on the website that could be dragging down the search results.  You would be amazed how many sites have links that go nowhere.  Another frequent crime is moving pages around on your web site without adding the necessary code to point people to where the pages now live.  I have seen sites that had tens of thousands of external sites pointing to pages that no longer exist.  Not only do these changes kill your search rankings, but they also frustrate customers and make you look bad. This is why I like to start here.  Ask your webmaster to set up Google's free and very powerful Webmaster Tools.  This is about a 5 minute exercise.  Once that is done, go into Webmaster Tools and look for the menu option for Crawl Errors.  That will tell you what pages are missing and who is linking to them.  You then can work with your webmaster to resolve the issues.
• Check the title.  When Google looks at your site to determine what each page is about, arguably the most important thing is the title tag.  One easy way to see the title tag for each page is to look at the tab names in your browser.  Most browsers will use the title tag as the name for that tab.  See screenshot below.  The key is for each page to have a good description of what the page is about.  You would be amazed how many companies just have their company name as the title tag on each page - you are basically telling Google nothing meaningful about that page.  The descriptions should be short (just a couple words) and should be the types of things your customers would be searching on.  Again, your webmaster can help you change these.

Title Tag Displayed in Tab Window

• Check your H1's.  One of the other most critical things Google looks for on your pages is your H1 tag and to a lesser extent your H2's and H3's.  I like to think of the H1, H2, and H3 tags as the web's version of an outline.  These blocks of text show up on the page and describe how the page is going to flow.  Ideally the H1 describes the main point of the page, the H2 describes the main sub-sections of the page, and so on.  I would focus on your H1's as those are most import.  Go to a given page and right click on your browser and say View Page Source.  Now use the Find feature to look for "h1."  You will see something like this <h1>Some Text Here</h1>.  You will want to go back to the actual page and see where this text shows up.  Make sure the H1 is a good description of the page and again ties to what customers might be searching for.

• Competitive research.  Is there a company in your industry who you think does search well?  If so, you can quickly leverage the work they have done.  I really like to use a tool called SEMRush.  They allow you to punch in a site and see 1) the value of the free traffic they are getting (they call it SE traffic price) and 2) what keywords were most valuable.  (SEMRush gives you a little information for free and a whole lot more if you pay for their service.)  If you see some keywords that are driving a lot of people to this other company in your industry, consider using the keywords in your title tags and H1's.
• Get others to link to you. One of the key things Google looks at when it ranks your site is how many other sites link to you.  Google's original design was based on the belief that if a lot of sites link to you, you must be an "expert" in this particular subject so they should rank you high in the search results.  Think about any partners, blogs, customers, news sites, etc who should be linking to you.  Make sure all your press releases have links back to you.  The best links from external sites link to pages other than your home page and use descriptive language in the link.  For example, if you make surfboards and you have someone reviewing a specific surfboard, the link should look more like this "great XYZ surfboards" not "click here" and it should go to your XYZ surfboards page, not your homepage.

While these tips just scratch the surface when it comes to SEO, you might be surprised how much you can get done in a short period of time.  I have seen companies invest just 5 to 10 hours in the tips above and then seen their search results increase by 2, 3 or even 10x.  It is a great place to start and for many companies, the key to SEO is just getting started.

How to Really Track Lifetime Customer Value

Tracking lifetime customer value and measuring the ROI of each of your marketing vehicles is the holy grail of online marketing.  Unfortunately many online marketers don’t know how to actually execute on this vision.  These marketers understand the goal of measuring lifetime customer value, but they take so many shortcuts that their conclusions are suspect.  

Tracking lifetime customer value is more complex than it seems because marketers rely on two disparate systems for customer tracking, and those systems generally don’t talk to each other.  The first tracking system is a web analytics package, Google Analytics being the most common.    The other tracking system is the transactional system (e.g., ecommerce database) that tracks customers and orders.  While the web analytics package holds information about where customers came from, lifetime value of a customer is generally in the transactional system, thus creating the challenge.

Because marketers don’t understand how to make their analytics package interface to their transactional system, they start taking shortcuts.  The most common shortcut is to pull an average lifetime customer value from the transactional system and assume that value holds across all customer segments.  This is a big assumption that often doesn’t hold up when you have access to true lifetime value by customer segment.  The fact of the matter is that some segments spend far more than other segments do so you need to dig deeper.

Another approach marketers sometimes take is to estimate customer value based on the data in Ad Words or Google Analytics (when the e-commerce capabilities are turned on.)  The problem with this approach is that Ad Words uses a 30 day cookie so you only capture customer spend for the first 30 days after they click on an ad.  That is far too little time to get a feel for lifetime value.

There are two basic approaches for accurately tracking lifetime customer value: pass customer source information to your transactional system or extract enough information from your analytics package that you can match it up with your transactional system.  In the first scenario you tag each paid ad campaign with extra data that specifies where a customer came from.  For example, let’s say we are running ads for my blog at http://kennethpmcdonald.blogspot.com/.  When we set up the ads, instead of entering http://kennethpmcdonald.blogspot.com/ for our landing page, we enter http://kennethpmcdonald.blogspot.com/?source=123 where 123 stands for the ad campaign.  We then need the transactional system to capture "?source=123" and associate this information with the appropriate customer.  In other words, when a customer clicks through on that ad, you store “123” in a field in your database for that customer. If you wrote your own transactional system, this generally isn’t a hard change on most systems.  If you are running a packaged transactional/ecommerce system, this approach may or may not work depending on how flexible your system is.

Aside from the integration issues, there are some other pros and cons of this approach.  On the positive side, once you get this system up and running, it is fairly straightforward to run reports that tell you the total amount of revenue by campaign including what customers purchased and when.  The reason for this is that all the segmentation and revenue data is one place – your transactional systems.  However, you don’t have the cost of your campaigns in the transactional systems so you will still need to match that up, but that is generally simple enough that you can do that by hand if you don’t have a ton of campaigns.  

One major issue with this approach is that it works for paid ad campaigns and other approaches where you can control the URL (in order to append "?source=123" information).  However, there are cases like free search where you cannot control the URL.  As such, you cannot measure ROI for all sources with this approach.  While paid ad campaigns are the primary place where we want to know the ROI, it is always nice to know the ROI for SEO efforts and other marketing projects too.

The second approach for tracking lifetime customer value is to extract enough information out of the web analytics system to know which customers came from where.  If you are using Google Analytics, you need to implement the e-commerce capabilities in Google Analytics.  Once this is done, you will be able to run reports in Google Analytics that show transaction IDs by customer source.  For example, can click on the E-Commerce section in Google Analytics and then click on the Transactions report.  From there you can select a segment or use the secondary dimension feature to filter the results.  Viola, you now have a list of transactions by source.  You can export this data from Google and import it into a reporting database for your transactional system where you can see what follow-on purchases were made by the customers in each source.  In other words, Google Analytics tells you that a customer came from a given campaign and placed order 1001.  You now can look in your transactional system and find out that this customer later placed orders 1010 and 1011. 

To export data from Google Analytics it is best to use an automated tool of some sort.  Excellent Analytics is an Excel plugged-in that grabs data from Google Analytics using the Google Analytics API.  It takes a little work to get set up, but it is extremely helpful if you go for this approach.

One of the great things about this second approach is that it works for virtually any customer source.  Want to know how much customers who came from organic search spent?  No problem with this approach.  As a matter of fact, you can make the data as granular as you want.  For example, you can look at users who came in on a specific keyword phrase via organic search and figure out their lifetime value.  Basically the sky is the limit on how much you can slice and dice the customer value data.

There is one major issue to be aware of with this approach  – Google Analytics’ terms of service have the following section:

You will not (and will not allow any third party to) use the Service to track or collect personally identifiable information of Internet users, nor will You (or will You allow any third party to) associate any data gathered from Your website(s) (or such third parties' website(s)) with any personally identifying information from any source as part of Your use (or such third parties' use) of the Service.

I won’t try to be a lawyer, but you could definitely read these terms to say this approach violates the Google terms of service.  On the other hand, one could argue that Google is prominently listing the Transaction ID in their interface and that is a personally identifiable piece of information so aren’t they going against their own terms of service?  In addition, if you are aggregating the data by customer segment instead of looking at it by specific customer, you probably are not violating the spirit of this section.  You will have to make the call on it.  You could always use a different web analytics package if you are concerned about Google’s terms.

Regardless of what approach you take, there is one issue that you will need to think through – customers don’t follow a linear path from one source through your web site.  Customers often times will pass through multiple sources before completing a purchase.  They might click on several paid campaigns, an email campaign, and follow an organic link before making a purchase.  Which source takes credit for the customer?  You will have to figure out what rules apply.  Many of the companies I have worked with generally consider the first source to be the one that “owns” the customer but they will reassign the customer to another source if the customer goes dormant long enough (e.g., no purchases for 6+ months). 

If you follow these approaches to track lifetime customer value, you will find that your decision making is greatly improved.  You now will be able to measure the impact of virtually all your marketing activities in great detail.

There are Jobs in Big Data

Today the New York Times had an article that followed up on my last two posts about the importance of analytics.  OK, the New York Times probably didn't read my last two posts, but let's just say that great minds think alike.

The NYT article today was called, "The Age of Big Data."  The basic thesis was that there is a real need for people with top notch analytical skills.  They cite a McKinsey study that said that the US will need another 140k to 190K workers with deep analytical expertise and 1.5 million more data-literate managers.

The article did focus more on unstructured data (think Twitter instead of an official company database), but the point remains that businesses are becoming more data driven.  Employees with analytical skills will do well in this environment.  My favorite example of the growing importance of data was that Google searches are becoming a more accurate predictor of housing sales than forecasts by real-estate economists!

Given the growing importance of analytics, I do worry a lot about the state of our education system, particularly around math and science.  I recently was looking to cross-train employees on a specific function that required a fair bit of math, geometry to be specific.  I was amazed at how many employees said they didn't want to learn this new skill because they were concerned about the math involved.  We need more passion for math and that needs to start in elementary and middle schools!  In full disclosure, I always loved math and made it all the way through the math program at Dartmouth so I have a side of me that thinks everyone should love math too.

The full New York Times article can be found at http://www.nytimes.com/2012/02/12/sunday-review/big-datas-impact-in-the-world.html?_r=1&ref=technology

Why Every Internet Marketing Professional Needs to Know SQL

My first job after college was with Oracle back in the late 1980’s.  At the time Oracle made all employees go through an incredibly intensive 3 week training course on SQL.  It didn’t matter if you were a developer, a marketing professional, or a receptionist, you were sent to a training class at a hotel for 3 weeks to learn SQL 20 hours a day.  Yes, it was 20 hours a day – Oracle took things seriously.  At the time I remember thinking, “Why in the world do I need to know SQL when I am on the marketing side?”  

Boy was I wrong!  More than 20 years later, I have written thousands and thousands of queries and use SQL in my job almost every day.  I have come to the conclusion that every online marketing professional needs to know SQL.  As many online marketing professionals like to say, “To be in online marketing, you have to be a data geek.”  If you are a data geek, you need to be able to able to collect and analyze data on your own.  Let’s face it, the explosion of the Internet means that the amount of data being captured in SQL databases these days is jaw dropping.  I have seen far too many Internet professionals who don’t know SQL who are literally hostage to IT, waiting for IT to write another query for them.  You won’t be a world-class Internet professional if you have to wait for others for a key part of your job.  

For years I have started my work day almost the same way every day.  I get a variety of reports via email that talk about the health of the business.  They essentially give me a high level picture of the customer funnel.  How many customers came to the site?  How many registered?  How many ordered?  Some of these come from Google Analytics and others come from the transactional systems.  More often than not, the reports highlight issues that require further investigation.  If the data is in Google Analytics, I drill down in Analytics.  If the data came from the transactional systems, I fire up SQL.  Canned reports just don’t do it – I need to be able to explore the data in whatever direction it takes me to answer the questions that have arisen from the previous day’s data.  I often have the answers to my questions by 9am.  If I was waiting on IT or a marketing analyst, it would be hours at best and possibly weeks before I got an answer.  I need to be self-sufficient.

Learning SQL can be intimidating for a marketing person.  A lot of SQL books that you find on developers’ desks are the size of a dictionary and look pretty daunting.  However, the reality is that SQL has become a lot easier for a variety of reasons.  There are a wealth of books that cover SQL for business folks.  Furthermore, the tools have advanced a lot.  If you are using a visual query tool, it really makes building queries much easier for someone just getting started. If someone on the IT side has been nice enough to name the tables and columns clearly that is a big help. Even better, if IT has defined the primary and foreign keys, most query tools will automatically join the tables together for you. 

Here are my tips for a marketing person who wants to learn SQL:

• Buy a good SQL basics book, but make sure you read a little and then try it out with a hands-on session.  Read a little more, try it out.  You get the idea.

•  Microsoft Access is a great tool for getting started.  Access does an incredible job of importing data from Excel which makes it easy to pull in data and get started.  It also is super easy to export data from Access to Excel so you can show others the results of your work. (I still use Access almost daily because of the Excel integration.)  Access is also great because if you do something horribly wrong, the worst thing you can do is cause the CPU on your local machine to peg at 100%.  Finally, Access allows you to build queries visually and see the SQL behind those queries, something that is key for SQL novices. My only caution with Access is that there are some small SQL differences between Access and the database systems that most companies use like Oracle, MySQL, SQL Server, etc.
• When you are ready for a real world project, ask your IT department to set you up with read only access on a reporting server.  You want an environment where you cannot delete any important data and where if you issue a CPU-intensive query, it won’t block customers from using your site.  Before you approach IT, I strongly encourage you to know some of the things that you can do incorrectly in SQL that can suck every ounce of CPU from your server.  Do you know what the following things are: a Cartesian product, doing a full table scan vs. hitting an index, and locking a table?  If you don’t know what those things are, you aren’t ready yet to write SQL against a real world system – keep reading your SQL books.

Knowing SQL has a lot of additional benefits beyond just analytics. For example, I have regularly used it to provide exports to key customers or partners.  I use it to set up complex, tailored email marketing programs.  SQL is everywhere these days!

So while SQL can seem a little intimidating at first, it is a tool that every Internet marketing professional needs to have in their tool-belt if they are to be successful.

Online Marketing Starts and Ends with Analytics

Marketing professionals regularly ask me how they can improve their online marketing skills, and my response is always, "Online marketing starts and ends with analytics."  The whole premise of online marketing is that you can measure the ROI on everything.  No more hand-waiving to your CFO when s/he asks what the return was on a specific campaign - with online marketing you can say EXACTLY what the return was for the spend.

The problem is that I find a lot of online marketing professionals are particularly weak in analytics.  They know a little about Google Analytics or another tool, but they don't really dig into the numbers.  Well, if you are going to tell your CFO what the return was on your campaigns, you (or someone on your team) needs to be a rocket scientist when it comes to analytics.  Everyone wants to jump into all the sexy functional skills like social marketing or email marketing, but if you cannot measure your results, you are missing the point.

I also see a lot of marketing professionals cast about looking for new analytical tools.  However, when it comes down it to, most of them only use 1% of the capability of Google Analytics, and Google Analytics is free!  There is no need to look for something better if you are not really using the tool.  Google Analytics is really an amazing tool when you dig into all the advanced goodies like custom variables, event tracking, advanced segments, profiles, custom reports, the API, etc.

Learning Google Analytics is mostly a mental challenge.  You can set the tool up on any web site in a few minutes so the learning opportunity is right in front of you.  However, really squeezing a lot out of the tool takes some tenacity - the product is deep and wide and takes time to learn.  I recently have been touting a book that I think does a superb job of teaching online marketing professionals what they need to know about Google Analytics; the book is "Advanced Web Metrics with Google Analytics" by Brian Clifton.  The book walks through how to use all the advanced capabilities of Google Analytics in the real world - how you apply the techniques to SEO, paid search, video marketing, etc.  I love that the book is so focused on real world situations and not academic theories.  I am such a fan of the book that I already have pre-ordered the updated edition that comes out in March of this year.  The book is not for beginners, it covers advanced techniques for people who are serious about their analytical skills.

So back to the task of becoming a better online marketing professional.  Analytics is so important, and there is so much to it, that you really have to work analytics first and last.  Start by learning a ton about Google Analytics by reading a book like Brian Clifton's and applying it in the real world.  Then go build up your functional customer acquisition and customer engagement skills (paid search, affiliate marketing, SEO, social marketing, email marketing, etc).  Then when you have built up your customer acquisition / engagement skills,come back to Google Analytics.  Analytics are so important that you need to be reviewing them regularly.  Hey online marketing professionals, if you are not a Google Analytics guru, you are not an online marketing professional!