Getting More Rows from Google Analytics

Facebooktwitterlinkedinmail

This is the first installment in a two-part series about setting up spreadsheets that automatically pull in Google Analytics data. In this post I talk about Query Explorer 2, an awesome tool for pulling more rows from Google Analytics.

While using Google Analytics, I’ve found that there are often valuable insights that are tucked away in, or even impossible to access with, the interface that Google provides. If you’re looking for the top 20 pages visited after your landing page, for example, then too bad. Google gives you no more than 10.

With third party services or a bit of coding knowledge it’s possible to circumvent this issue, but the simplest solution I’ve found is Query Explorer 2. This tool has been really useful for me as I dive into analytics, allowing me to quickly shine light on the problems, trends, and insights that help focus our team’s creativity on the areas where it’s needed the most.

So here’s how it works: Query Explorer 2 uses Google’s Core Reporting API to ask Google for the information you want to see. It then displays that information in a table that you can share or export to a spreadsheet. We’ll use these queries in the second part of this series to pull Google Analytics data into a Google Spreadsheet automatically.

But first, I’ll walk through one simple query and one complex query using Query Explorer 2. If you have any questions I’ll be checking the comments often.


Example Query #1: Mobile vs. Non-mobile Visits

Let’s say you’re thinking of doing a responsive redesign and you want to see how many visits to your site were on mobile devices in January. Here’s how can ask Google for that data:

Step 1: Authorize access to your Google Analytics account

So first, go here: Query Explorer 2. Then to start using this tool, click the “Authorize Access” button and log in to your Google Analytics account. (Don’t worry, this tool was created by the Google Analytics dev team; you won’t get hacked!)

Then select the Web Property and Profile that you’re interested in observing.

Step 2: Define what data you want to see

Now it’s just a matter of picking what data you want to see. You’ll want to set up your form like this (you can leave the other fields blank):

  • dimensions= ga:isMobile
    • Note: Dimensions are the row labels in your Google Analytics report tables. For example, if you’re looking at the Browser dimension, you’ll see each Browser as a row label.Right now you’re asking Google whether your site’s visits were on mobile devices or not, so you’ll see this represented with “yes” or “no” in your report’s row labels.
  • metrics= ga:visits
    • Note: Metrics are the numbers that populate each row.In this case, you’ll have a number of visits for mobile visitors (“yes” in the isMobile dimension) and a number of visits for non-mobile visitors. These numbers are metrics.
  • start-date= 2013-01-01
  • end-date= 2013-01-31

query1

 

Click “Get Data” and you’ll see a report that compares mobile to non-mobile visit numbers (ga:isMobile = “No” and ga:isMobile = “Yes”)

That was simple enough, right? You picked the data you wanted to see with dropdowns, then you set a date range and asked Google for the data.

If you aren’t sure, you can hover over each metric and dimension in the dropdowns to see what they mean.

hoverinfo

Now let’s try a more complex query.


Example Query #2: Top 20 pages visited after the landing page

So now let’s find the top 20 pages users visited after your primary landing page:

Step 1: Authorize access to your Google Analytics account

Make sure you’re logged in and that you have a profile selected (step 1 above).

Step 2: Define what data you want to see

Then set up your data request:

  • dimensions= ga:secondPagePath
  • metrics= ga:visits
  • filters: ga:landingPagePath==www.yourLandingPage.com.
    • Note: Filters limit your results to a subset of your data.You want to see the second page path only for your main landing page. Replace www.yourLandingPage.com with your own landing page URL.
  • sort= -ga:visits
    • Note: The sort field lets you define the order of your data in the table. You can sort by multiple dimensions and metrics.Make sure you include the – before ga:visits. What you’re doing here is asking for the rows to be sorted by visits in descending order. If you leave out the -, you’ll sort visits by ascending order (starting from 1), which isn’t as useful!
  • max-results= 20

query2

What Google gives you is the top 20 pages visited after your primary landing page. Setting this up took (1) a tiny bit of research into what variables we wanted; (2) a few keystrokes. This was much easier than clicking and scrolling through Google’s interface, and we got more results this way, too.


I’ve found this tool to be very useful for exploring the data I’m interested in, but without seeing how the numbers change over time, the tables it gives back are pretty meaningless. We need multiple snapshots in order to get a sense of the bigger picture.

So stay tuned for part two, where I’ll share an easy way to pull the power of Query Explorer 2 into a meaningful Google Spreadsheet.

One thought on “Getting More Rows from Google Analytics

Leave a Reply

Your email address will not be published. Required fields are marked *