Custom Reporting using Google Analytics and Google Docs – The Ultimate Analytics Mashup

Posted by Jamie Realtime Google Analytics data inside a Google Doc—a panacea! Don’t believe me? Check out that screenshot below. In this blog post I’ll show how you can do this yourself, and I’ve created an easy template to help get you started. Google Analytics is my favorite analytics product. And it’s only been getting better with the new interface , flow visualization , and multi-channel funnels . Google Analytics is still best game in town for the price (it’s free)! But, despite all the flexibility that Google Analytics offers, sometimes you want to access data in a spreadsheet and create a truly custom report. That’s where the Google Analytics Data Feed API comes in. This blog post is going to show you how to create a custom report by connecting a Google Spreadsheet directly with your data from Google Analytics. When data is available directly in a spreadsheet you’re able to make interesting comparisons, create the dashboard of your dreams, or chart data however you’d like. And the only requirement is that you have Analytics setup for your website. I’ve created a simple Google Spreadsheet template that makes the whole thing easy. Analytics geeks: hold onto your seats! It all started with the Data Feed Query Explorer (Those who want to start accessing data in Google Docs should jump right to the next section.) Before we dive in, a little background. A few weeks ago I was looking for a solution to directly access Google Analytics data in Microsoft Excel or Google Docs using the Google Analytics API. I first discovered Google’s excellent Data Feed Query Explorer .  The explorer lets you connect to your Analytics account and pull custom data until your heart’s content. This tool is not only an efficient way to figure out what’s available via the API, but it’s also great for pulling custom data. Want to see which organic keywords drove conversions on your site? Enter the details as below, after authenticating and adding your appropriate profile ID: The Data Feed Query Explorer is a great way to explore the Google Analytics API, and to understand what data is available. If you’re interested in understanding the API, experiment with the tool but also check out the the API documentation . While this tool is helpful, it didn’t meet my goal of accessing this data within a live spreadsheet such as Google Spreadsheets. Enter Mikael Thuneberg . Mikeal wrote an excellent set of scripts  that pulls data from the Google Analytics API, and allows you to access that data within a Google Spreadsheet. Nice work, Mikeal. He provides this code free of charge (and it’s included in my template below), but feel free to reach out to him if you’re interested in paying an expert for your custom reporting needs. I used Mikeal’s scripts to create a template that accesses Google Analytics data and allows you to customize it in almost any way. Let’s get started! Connecting Google Analytics to Google Docs I’ve created a brief screencast to walk you through connecting your Google Analytics account to the template I’ve created , but the instructions are also written out below the video. (A small disclaimer: this spreadsheet is provided without warranty or support, so please use at your own risk!) 1) Make sure you have a Google Analytics account with data. Duh. Make sure you’re logged into Google Analytics on the computer you’ll be using with my spreadsheet template. 2) Open the spreadsheet template and save a copy. Open this Google Spreadsheet template , and save a copy to your own Google Account (as you cannot edit this public version). Once the spreadsheet is open, choose “File”… “Make a copy”. Get the Google Spreadsheet template here! (open this and save a copy to your own Google account) 3) Enter your Google Analytics username. Give the browser a few moments to make the duplicate copy. Once the copy is created, enter your Google Analytics username (usually an email address). 4) Enter your Google Analytics password. Enter your Google Analytics password. Once entered, you may hide that row to obfuscate your password. If the cell below the Profile ID shows an Auth Token (a very long alphanumeric string) you have successfully authenticated. If you have an issue, ensure you are logged into the same Google Account for which you are trying to access. If you still have any issues, such as a CAPCHA warning, wait 30 minutes and try again. 5) Enter your Google Analytics Profile ID. You’ll need to determine the Google Analytics Profile ID of the site you’d like to create a custom report for, and enter it into the Google Spreadsheet. Log into GA (in a seperate browser window) and open the profile for which you’d like to access data. Getting the profile ID isn’t easy, and it differs based on which version of GA you use. Once you’re logged into Google Analytics, grab the profile ID from the browser address bar. Here’s where you can find it depending which interface of Google Analytics you’re using. Finding your Profile ID in the Old Google Analytics Interface: If you’re using the old Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreadsheet as characters only. Finding your Profile ID in the New Google Analytics Interface: If you’re using the new Google Analytics interface, your profile is highlighted below in yellow. In the example below it is 2917495 and should be entered into the spreedsheet as characters only. Once you have the profile ID, add it to the appropriate field in the spreadsheet template. If everything worked, the cell below the Profile ID should display an Auth Token (a very long alphanumeric string). If you have any issues, ensure you are logged into the same Google Account for which you are trying to access. If you still have issues, such as a CAPCHA warning, wait 30 minutes and try again. 6) Click the “Custom Report” tab to start accessing your data! Now you’re all set! Click on the “Custom Report” tab at the bottom of the Google Spreadsheet to start interacting with your data. Edit the cells in yellow to change what data is pulled, and for what data ranges. Read on to learn more about choosing which metrics to pull, and how to filter the data. Customizing the data When you jump into the “Custom Report” tab of the spreadsheet you’ll notice several of the cells are yellow. You can update these cells to change what data is pulled from Google Analytics. For a full walkthrough of the spreadsheet template, be sure to watch the screencast earlier in this blog post. There are four ways you can change the information that’s pulled from Google Analytics into the spreedsheet. Metric: Change which metric is pulled in that column of the spreadsheet—for example: visits, pageviews or bounces. Change this value and the cells below will update to pull that data. Check out Google’s Dimensions & Metrics Reference for details on what data you can access. Filter: Change how the data below is filtered, i.e. what data is included. Here you can specify a filter that will show only metrics for which the filter is true. For example, setting ‘ga:medium==organic’ in the filter cell will only show data where the traffic medium is organic search. The filter section is where you have a lot of power—you can even use regular expressions to do advanced filtering. To learn more about setting the filter cell, read Google’s Data Feed documentation . Start Date : Enter a date in the MM/DD/YYYY format to select the start date for cells in that particular row. End Date: Enter a date in the MM/DD/YYYY format to select the end date for cells in that particular row. How to make this actionable So you’ve connected your Google Analytics account to a Google Spreadsheet. Now what? There’s a lot you can do when you access your analytics in this format; I’ve included a few ideas below: Put interesting metrics next to one another. Have you ever wanted to see your total visits next to your organic search visits and goals completions? By choosing the metrics that get displayed in each column you can compare metrics however you like. Compare a variety of date ranges easily. Want to compare several days, weeks or months? Change the start and end dates and you can compare multiple periods. Create advanced filters. Get creative with your filters. Try creating a filter for organic search traffic (ga:mediun==organic), or for a set of keywords using regular expressions. There are unlimited ways you can slice and dice your data! Create calcuated cells.  Add a column to the spreadsheet and cacluate your conversion rate by dividing your goal completions by your visits.  Create your ultimate dashboard. Probably the most useful way to use this report is to create a dashboard of your favorite key performance indicators. This spreadsheet can automate your weekly or monthly reporting by pulling all of the relevant metrics in one swoop! These are just a few of the many ways you can use Google Analytics data within a spreadsheet. I’d love to hear your ideas for how to make this actionable—please let me know in the comments. A few technical notes The Google Analytics API is rate limited, so you may occassionally receive errors because your spreadsheet has made too many API calls at once. Unfortunatly, there’s no easy way around this expect to reduce the number of rows or columns of data you’re pulling. Please let me know in the comments if you’ve found a good workaround for this. Your password is in plaintext in the Setting tab of the spreadsheet. Be sure you don’t share this Google Doc unless you want someone to have access to your Google Analytics password. Be a data ninja! I hope this template is useful and that you’re now able to do all sorts of fancy things with your web analytics data. Please let me know how it works in the comments! Do you like this post? Yes No

Taken from:
Custom Reporting using Google Analytics and Google Docs – The Ultimate Analytics Mashup

How Big is Your Long Tail? – Whiteboard Friday

Posted by Aaron Wheeler  Choosing keywords to optimize for is a tricky business, made all the more tricky as keyphrases grow longer than a couple of words. As Google has said , up to 20% of search queries in any given day are completely unique. Should you try to optimize your tauntaun sleeping bags product page for “tauntaun sleeping bag,” for “childrens’ tauntaun sleeping bag,” or for “childrens’ star wars tauntaun sleeping bag from hoth”? How can you research whether or not to optimize for such a long tail query? In this week’s Whiteboard Friday, Rand is back to explain just how long of a tail you should be optimizing for. Have any suggestions on how you do this research? Give us your thoughts in the comments!   Video Transcription Howdy, SEOmoz fans. Welcome to another edition of Whiteboard Friday. This week we’re asking the question: How big is your long tail? No innuendo intended. This is a totally serious question for the search world, wink wink, nod nod, say no more. Many of you are familiar with the fact that the world of search is really dominated by this concept of the long tail. Google talks about this incredible metric that 20% of any search that’s performed every day is completely unique. Google has never seen that search before performed on their engine at all. No one in history has ever made that search. That happens on one out of every five queries every single day. We are amazingly unique creatures, especially when we get in front of a search box. That’s a great thing, but of course it means that doing keyword research can be tremendously tough. There are a lot of folks who ask the question: “I’ve heard of this tail concept, but I only do keyword targeting and keyword research really on the fat head, maybe the chunky middle.” I’ll talk about those in a sec. “I don’t even know how to do keyword research on the long tail. I don’t know how much of an opportunity it is.” This Whiteboard Friday is here to answer that last question: How big of an opportunity it is. Can we measure it? Can we look at the size? Can we understand? Because some industries are going to be very narrowly focused on a few head terms. That’s what people search for. Those are the money terms. That’s where people convert, that’s where the value comes from. In other industries, the long tail is a huge, huge win, and you need to be able to understand that in order to do the right kinds of keyword targeting. So let’s begin. This is our classic long tail graph. We’ve got the quantity of visits that any particular keyword sends you on this axis, and then down here on this axis, the keywords themselves. This keyword sent a ton of visits. This keyword sends a ton of visits. This keyword sends a bunch of visits. Then there’s this huge tail that comprises usually 70% of all of the quantity. If we were to take this area under the graph, do some calculus, figure out how big the whole opportunity is, oftentimes the tail is 60%, 70% of the full opportunity. It’s because it extends for miles and miles and miles in that direction. We kind of classify these into three chunks. So we have our fat head, our chunky middle, and our long tail. The fat head, in my view, tends to refer to the things that are very popular in your niche. I say in your niche, because depending on your niche, these may be very different in terms of quantity. I’ve given a rough estimate for SEOmoz. Usually the categories we like to bring them into are something that sends more than 100 visitors each month. If there’s a keyword that’s sending us more than 100 visits a month, we put that in the fat head. That’s sort of a big term for us. If there’s something sending between 10 and 99 visits a month, that’s our chunky middle. If it’s sending fewer than 10, it’s our long tail. Some SEOs like to have very, very different orders of magnitude on these. Some people might say, “This is only things that send over 1000. This is stuff between 20 and 500. This is stuff that’s only less than 5.” Whatever you want to do is fine. You can classify your traffic that way. That’s a good way to go. You should just be aware that this classification system exists. I think this is a very healthy way to be able to look at things. You tend to look at the chunky middle and the fat head and say, “I am going to manage these.” Whatever I’m using, if I’m using the SEOmoz Pro Suite, I’m going to manage these in my rankings. If I’m using Raven or Authority Labs or any of these other services, these are the keywords that I want to care about tracking their rankings, tracking their visits, keeping good tabs on how they’re doing. It’s harder in the long tail. I might have a subset of these that I’m monitoring as well just to get a sample, but I’m generally not paying attention one-on-one to them. The problem can be when as SEOs we naturally, since we’re paying attention to these keywords we manage and rank track, we get obsessed with them. We stop thinking and worrying about the long tail and the opportunity we’re missing here. Meanwhile, one of our competitors is going, “Yeah, yeah, yeah. Go ahead and let him win the number two, number one rankings for those keywords. I’m winning over here where there’s no competition, and where there’s generally higher conversions, and where there’s tons more volume.” We’re kicking ourselves when we find that out. Instead of losing out, let’s figure out how much opportunity exists there. Before we answer that, we should know: Well, how much am I currently capturing of these? This is pretty easy. What you can do is you can create advanced segments inside Google Analytics, or you can create segmentation inside Google Analytics for each of these buckets. You decide how big these buckets are. You can say, “I only want keywords that sent me fewer than 10 visits.” That works great. Those segments can then be classified and you can say, “All right. This sent me 13,510 visits last month. That is up from the month before, so I’m sort of doing better in my long tail.” Or long tail demand’s getting better, whichever. From that, measure the quantity of keywords and visits in each bucket. You can also measure the quality. Measure quality with one of two things. If you have goals set up, hopefully you have goals and conversion rates set up in your Analytics, that’s a great way to look. The other way, if you don’t, if you’re just trying to say, “How much is this traffic worth to me from a branding perspective, from a usefulness perspective, from a reaching new audience perspective?” The metric I really like for that is browse rate. The reason I like it so much is because browse rate says on average how many pages did a visitor visit in a single session when they came via this keyword. Browse rate is great way to say usually, when you have a higher browse rate, that means more engagement. It means someone’s surfing around your site more. They’re spending more time on the site. They are more likely to convert or come back and convert. Browse rate is a good sort of substitute metric. It’s not great, not perfect, not nearly as good as goals and conversions, but if you don’t have that, browse rate is a great way to judge qualitatively: How’s this traffic performing? You take that and you sort of go, “All right. This is how well I’m doing. This is my trend over time. Am I improving? Am I not improving?” For some people they want to know, “Yeah, but what’s the opportunity? Am I really missing out here, or am I doing a good job?” Just measuring your own progress won’t tell you that. You need broader industry statistics. There’s a number of ways to do that. The most obvious one, of course, is to go to Google AdWords and try and figure out what the fat head and chunky middle distribution looks like. But because there’s no real keyword research available for the long tail, Google through their AdWords tool or their AdWords API, or Bing through AdCenter, are not generally showing you keywords that send fewer than 10 searches, that have a very small search quantity, but there are tons of those keywords. That’s a really challenging thing to search. Of course, no search engine’s going to be able to tell you what those 20% of queries that they’ve never seen before every day are. So that’s frustrating too. Solution to this . . . it’s not that ingenious, you probably know how to do it, you can probably guess, but let me walk you through it anyway. I think it’s super exciting. This is asking how much opportunity do you have. Oftentimes it’s a lot. One of the best ways to figure this out – this won’t answer it perfectly, but it does a nice decent job – is to say, “I’m going to go to AdWords or AdCenter and I’m going to create a paid search campaign.” This is one of the times when paid search and organic search overlap very, very well. It’s just because the research is so handy. For my major fat head terms, I’m going to create campaigns around those and target groups in both the exact and broad matches for those keywords: Exact match, of course, the example would be like “chess tournaments.” “Chess tournaments” is an exact match. I only want that precise phrase. Google do not show my ad and don’t tell me about that. I want to know only the clicks that I get for “chess tournaments” exactly. Then I’m going to create another group that contains phrase based matching. Show me phrases that contain “chess tournaments” but not this exact phrase. Meaning things like, if somebody searched for “playing chess in a tournament in Miami.” That would show up in . . . well, yeah, it contains the phrase, but it’s not that exact phrase. Or I’m sorry, “Miami chess tournaments” would be in there. “Minneapolis chess tournaments” would be in there. “Pro chess tournaments” would be in there. It’s not the exact phrase “chess tournaments”, but it contains that phrase. Then you have that final bucket of contains the words but is not matching a high-volume phrase and does not necessarily need to be the exact phrase. This could be “tournament style chess games” or “tournament video game chess”. All this type of stuff can add up to a bunch, and what will happen when you buy these keywords in AdWords is that they will show you something called impression count. The impression count can actually be drilled into and you can see all of the terms that send those. From that impression count, you can then take them and segment them into these buckets. You can say, “Oh, okay. We had 500 keywords that had 10 or fewer impressions, so we’re going to put those 500 keywords in here. Then we had another 110 keywords that fit into our chunky middle stats. We had another 42 that fit into the head.” This kind of distribution is incredibly valuable because it gives you a sense for a phrase or a bunch of phrases, if you’re doing this work consistently, how big is the opportunity in the tail? It really does vary. Some things are hyper-geographic, so geographic modifiers get in there. Some things are very tuned to customization and specialization and weird sorts of searches. This happens a lot in the programming world. You’ll see “mySQL calls” have a bunch of volume and then you might say “PHP mySQL calls”, and then there’s a ton of long tail weird stuff. Being able to see that versus something that’s much more narrow is really, really handy and where the volume is constrained or confined to the fat head, chunky middle. Now that you’ve got this process, if you’ve got some budget for AdWords, you can start testing, grouping these things into buckets. You can measure your buckets over time and how you’re performing and you can see: Am I capturing the long tail? Or am I losing out on an opportunity to capture the long tail and maybe I need to be spending a little less time and attention with the fat head and chunky middle? All right gang, thank you very much for joining me. I hope we’ll see you again next week for another edition of Whiteboard Friday. Take care. Video transcription by Speechpad.com Do you like this post? Yes No

Taken from:
How Big is Your Long Tail? – Whiteboard Friday

Jerry Clark, Rhino Marketer, on the MLM Success Triangle

And Comment Below! Jerry Clark, Club Rhino Marketing for the MLM Success Triangle Last night I did a team webinar with my mentor and coach, Jerry Clark . It was freaking awesome. Jerry has been in this game for a long time and he really did a great job and I happened to record it, check out the video and prepare for Jerry Clark to get you re-energized about your network marketing business! Who is Jerry Clark and Club Rhino? Jerry Clark became a self-made millionaire while still in his 20’s. Today, through his company Club Rhino , Inc., he conducts personal and professional development seminars around the World. The topics covered range from peak performance training, effective communication strategies, and how to increase your productivity and profitability in your Direct Sales or Direct Marketing Business. Jerry is the producer of over 100 of the most empowering audio programs available in the Direct Sales / Direct Marketing industry. Jerry has shared the stage with industry leaders such as Tony Robbins, Jim Rohn, Brian Tracy, Denis Waitley, Charlie “Tremendous” Jones, Les Brown, Robert Kiyosoki, Robert Allen, Florence Littauer, Mark Victor Hansen, and many others. His articles has appeared in many publications such as Success Magazine, Money Maker’s Monthly, Wealth Building Magazine, Working at Home Magazine, Networking Times, and many others. Jerry is the CEO & President of Club Rhino, Inc., and the founder of AMG Business Group. Why I like Jerry Clark and his message Before the age of facebook, ppc, seo, article spinning, etc, Jerry built a network marketing income to over $600,000…in a single month! This guy knows what it takes to build a very serious business and he also knows the pitfalls and obstacles most marketers face. In this video which is over an hour of pure content, he gives you the lowdown that so many people really need to hear. If you are serious about building your home business, watch the video with Jerry Clark and see how he did it and how you can do it by getting into rhythm and getting re-energized! To find out more about Jerry, visit his site here If you enjoyed this post please comment and share if you want more content like this Ray Higdon Skype: ray.higdon Email: RayHigdon@RayHigdon.com Image: renjith krishnan / FreeDigitalPhotos.net PS: If Your Upline Does Not Have a Step-By-Step Blueprint For Success, Check This Out (Unless You Already Have Too Many Leads) – Click Here For Instant Access If you enjoyed this MLM Training audio, retweet and comment please Free Video: 10 Ways to Get 50 Leads Per Day ( With Little to No Budget ) I struggled with Marketing until I paid a lot of money to learn these secrets. The Only Reason You Shouldn’t Enter Your info is if You Already Have too Many Leads. Note that your Your email will be used to send you the promised information and daily alerts about new blog entries. You’ll be able to unsubscribe any time. We may also give you a call to offer you additional services and assistance in your marketing setup. Name:  Email:    Powered by Subscribers Magnet

Read More:
Jerry Clark, Rhino Marketer, on the MLM Success Triangle