Join Corey Koberg for an in-depth discussion in this video Extracting Google Analytics data to spreadsheets the easy way, part of Google Analytics Tips.
- One of the most common requests I hear is, "How can I pull the data out of Google Analytics, "and into my other systems?" Now, in the Essentials course we looked at the built-in ways such as exporting a CSV file. In this series we've looked at how to use the API, and even some R scripts. But the CSV download requires having access to the UI, and the API and R script options are beyond most people's ability to develop code. If you don't have those coding skills, you're in a hurry, or if you simply appreciate the elegance of a simple solution, I'll show you how you can extract Google Analytics data into a spreadsheet simply and quickly, and even build a really lightweight dashboard.
First, let's just start here in a Google Docs spreadsheet. Let's come here and create a blank document. What we want to do, is we want to come up here to the "Add-ons", and go to "Get add-ons." We'll do a quick search for "Analytics," and select the "Google Analytics" add-on." It's going to ask for permission. We accept that permission. And now I've given this Google Doc the ability to go in and access my Google Analytics.
Okay, to start, let's come up here to the "Add-ons," "Google Analytics." We're going to "Create a New Report." And it's going to give us this interactive dialog here, we can go ahead and name our report. Okay, select your Account, your Property, and the View or Profile that you want to use here. And we can select some initial "Metrics and Dimensions" here as well here. So in this case, let's just take something like "Pageviews" and "Sessions." As well as the "Dimension." In our case, let's look at "Date" from this first one.
Okay, go ahead and click "Create Report." And really what that dialog box got us to, is this configuration page, which is where we're going to do most of our work, but initially this is done. In fact right now this would work, we can go ahead and just select "Google Analytics," "Run Reports." And it's going to go to Google Analytics. It's going to pull back this data, and it's going to pull it back into our spreadsheet here now. So here we go, we have the time that it was last run, we know the view that it pulled from, the number of results that it found, whether or not the data that it pulled was sampled. It's going to tally all these up for us in totals, and we also get the individual dates broken down by their sessions and their pageviews.
So there it is, in about a minute or less, we were able to go and pull data back into our spreadsheet, that's up to date, and refreshed from Google Analytics. Let's see if we can dive in a bit deeper. Can we manage this report Configuration page and see there's a lot of options here that we didn't touch. Now, remember how we had that scenario last time, where we created a specific segment that was our business-to-business watch list of those academic institutions. We had that segment, we saved that in Google Analytics. Let's say we want to specifically target those metrics to put together this kind of mini dashboard. Let's say the first one I want to do here, we are going to call this "Watchlist" that we created and we're going to do this by date, as we've got it here.
And we want just to able to understand how this is coming back. So this is the first one that we created. What we need to do here, is we need to tell it that we very specifically only want to get the target of the "Watchlist." So that's a specific segment that we've got in Google Analytics. We can put this segment in here, but we're not sure what to put in here. We're going to go and, as we often do, we are going to use the Query Explorer tool. Just type in "Query Explorer." Come here. When we come down here to "segment," we can see the names that we've given our segments.
In our case we called that one Academia. And then, what it's going to do is give us the idea of the segment Google is going to use in the back end. We want to copy that over. And that's what we're going to paste here as our segment. So now, when I run this report it's going to pull back that data, but just for that segment. Let's go ahead and give it out a try. "Run Reports." In this case I've created a new tab, and that's important because every time you have a new name it's going to create a new sheet. So I'm going to go ahead and look at the one that we had where we did not have a segment. And then we compare that to our "Watchlist." Which of course it's much smaller.
It's a sub-segment of that overall. I'm going to go ahead and delete this first task, and get that kind of out of our way. And here we got our "Watchlist" by date. We can do some things here just to take advantage even more of the features that are built-in. For example, let's say I want to do a very quick chart on this. And I want to get this one by date. That's going to show how this is kind of trending. There we have it. We got our last few days here of how this specific segment is trending both in terms of pageviews and of sessions.
What I like even better about this is that you can even publish this now. If I can here and click "Publish chart," I can select this and let's say I put this simply as one that I want to embed as an image. Click "Publish," it'll ask me if I'm sure I want to publish. It gives me a link here, in a way of an image or interactive chart that I can do in any web browsers. Come up here. And I'll simply have that as an image here for anyone can reach. So I can do anything I want with this image. I can email it to someone. I can have it and it will always have the data that's in the spreadsheet up-to-date in this image.
Okay, coming back to our example. Let's kind of take this a bit further. Coming back to our report configuration. What if I want to do multiple things? Next thing I might want to do is take a look at the specific schools that are part of that trend. Again, I'm going to name this up here. And that's what's going to kick off another tab. Maybe I'll call this "Schools." And I'm going to copy this data over. And we're just going to modify it a bit. I still want to grab the last seven days. I still want to get "Sessions" and "Pageviews." But I don't want to get it by date anymore. In this case I want to get it by network location.
This is the name we get for those providers. This is going to tell us which are the actual schools that are coming by. And in this case we are going to sort by pageviews. But I want to put a "-" in front here. Remember that's how we tell it we want to sort in descending order. And in this case I only want to grab the top 10 results. Let's put that in there. Okay, let's go ahead and run this and see what happens. In this one I've got two reports. It's going to keep the one that I had before, the Watchlist by Date, and that's going to be simply updated.
But now I've got a new one here. And this is the watchlist Schools. In here, you can see I've got these breakdown by the top schools. It's going to be sorted by pageviews, in descending order, of the top schools that are visiting my site. So again, if I want to create this, I can take this, and I can build a quick chart out of that. And here we have it, sessions and pageviews for the top universities visiting on the last seven days. Okay, let's try one more. Let's say here that I want to also capture the top pages that are being visited by that particular segment.
We'll call this one, Watchlist Pages. Again I'm going to copy over exactly what I had before, and just make a few changes here. Again, we're not looking for sessions and pageviews here. Here, I just want pageviews. Instead of the network location coming back, I want the actual pages that they've visited, so we'll call this "pagepath". And again we're going to sort in descending order by pageviews. I'm going to maintain that same segment, and I'm going to pull back the first 10 results. Run my report. In this case I should get another tab up here, I'm going to update those other two.
We do have three reports completed successfully. And again we have pages here which is going to give me the top pages sorted by pageviews for that particular segment over the last seven days. Our charting and our table weren't quite as interesting, but we still have that ability to publish those charts Come down here, select "More," go down to the bottom, and we can select a chart here that is going to give us that interactive chart, just like that. There you have it. We've extracted data out of Google Analytics. We've put it into three different tables here. We can do lots of stuff from here.
One of the things I'd like to do, is show how easy this is to publish. For example, we've shown how we can come and publish these charts. We can either put them as Links or Embedded. We can make it Interactive or as an Image. And we grab that data out of there, we grab that link when we publish these. I like to sometimes just take these and put them in a quick page. For example, pull these into here. Put each of these as iframes. We can upload that to a webpage here, and this is simply going to pull those three charts down, one after the other.
And probably the quickest dashboard you could possibly put together that's going to display these data. And again, this is going to be updated as those spreadsheets are updated. So this would be as up to date as a spreadsheet is. So inside of a few minutes we showed you how you can query Google Analytics to pull data in your spreadsheet in a variety of different ways. Including data that is specifically segmented, using all of the power of the Google Analytics segmentation tools. And we even published out a quick and dirty dashboard visible to anyone that has internet access.