Start learning with our library of video tutorials taught by experts. Get started

Google Analytics Tips
Illustration by Richard Downs

Google Analytics Tips

with Corey Koberg

Video: Extracting Google Analytics data to spreadsheets the easy way

- 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.
Expand all | Collapse all
  1. 8m 48s
    1. Extracting Google Analytics data to spreadsheets the easy way NEW
      8m 48s
  2. 54s
    1. Welcome to the series
      54s
  3. 17m 8s
    1. How Google Analytics accounts, properties, and views are structured
      6m 36s
    2. User management: 2013 update
      6m 19s
    3. Best practices for user management
      4m 13s
  4. 20m 49s
    1. Installing Google Analytics on WordPress
      8m 28s
    2. Google Analytics debugging and troubleshooting: The basics
      6m 10s
    3. Google Analytics debugging and troubleshooting: With a tag management system (TMS)
      6m 11s
  5. 23m 58s
    1. Demographics
      6m 45s
    2. Interest categories
      5m 26s
    3. Enabling Audience reports and understanding where the data comes from
      11m 47s
  6. 26m 37s
    1. Intro and built-in segments
      14m 57s
    2. Custom segments via the Segment Builder
      11m 40s
  7. 12m 25s
    1. User Segments: A huge improvement
      6m 23s
    2. Advanced Topic: Sequenced Segments
      6m 2s
  8. 27m 8s
    1. Cohort analysis now built into Google Analytics
      8m 22s
    2. Permanent filters and removing internal traffic
      8m 19s
    3. Filters everyone should use to get clean data
      10m 27s
  9. 13m 25s
    1. Using an advanced filter to get accurate information on subdomains
      7m 20s
    2. When to use a segment vs. a filter
      6m 5s
  10. 18m 0s
    1. Finding my most valuable customers
      9m 54s
    2. Making Adwords work smarter and more profitably
      8m 6s
  11. 15m 20s
    1. What is Google’s Universal Analytics?
      7m 7s
    2. Upgrading from Classic to Universal Analytics
      8m 13s
  12. 7m 11s
    1. Test driving the API in 5 minutes
      7m 11s
  13. 14m 16s
    1. Migrating Custom Variables to Custom Dimensions
      14m 16s
  14. 4m 5s
    1. Creating a Custom Report in 30 seconds
      4m 5s
  15. 5m 41s
    1. Five Time zone tips to save your data
      5m 41s
  16. 9m 56s
    1. Importing data with Custom Data Import
      9m 56s
  17. 6m 29s
    1. Query string parameters
      6m 29s
  18. 17m 17s
    1. YouTube videos
      9m 3s
    2. Local visitor time zones
      8m 14s
  19. 7m 18s
    1. R Programming and Google Analytics
      7m 18s
  20. 24m 39s
    1. Part one: Why nearly everyone gets segmentation wrong
      10m 59s
    2. Part two: Why nearly everyone gets segmentation wrong
      8m 17s
    3. Part three: Why nearly everyone gets segmentation wrong
      5m 23s
  21. 4m 40s
    1. Creating an industry vertical
      4m 40s

Watch this entire course now —plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
Google Analytics Tips
4h 46m Intermediate Dec 04, 2013 Updated Oct 22, 2014

Viewers: in countries Watching now:

Get a new Google Analytics tip every other week from online marketing expert Corey Koberg. Most users unlock just a fraction of the power that Google Analytics offers, so in this course Corey exposes tips and tricks to unlock insights into one of the most sophisticated tools in the marketer or site owner's arsenal. He offers peeks into the latest power features, advice for deeply mining your digital data, and actions you can take to optimize your site for both traffic and conversions. Corey answers common questions about online marketing and web analytics, including installation, tag management, reporting, custom variables/dimensions, attribution modeling, segmentation, multichannel funnels, data accuracy, visualizations, Universal Analytics, and more. What's more, Corey welcomes your questions and will shape future videos based on member requests, so send them to us at feedback@lynda.com.

Subjects:
Marketing Analytics
Software:
Google Analytics
Author:
Corey Koberg

Extracting Google Analytics data to spreadsheets the easy way

- 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.

Find answers to the most frequently asked questions about Google Analytics Tips .


Expand all | Collapse all
please wait ...
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Google Analytics Tips.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.