Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Feb 25 2013

How To Create Pivot Table-Friendly Custom Reports in Google Analytics

make Google Analytics custom reports pivot table friendly

Normally, in Google Analytics, if you create a custom report and you need more than one dimension, you create drill-down dimensions like this:

custom report in Google Analytics

However, if you want to export that, you’re only going to see in the export whatever you see on your screen, i.e., a list of landing pages or a list of keywords for one of your landing pages (whichever landing page you have drilled down into).

But what if you want it all? All landing pages with all of their corresponding keywords? Impossible? Nope.

There’s a little-known custom report option called the Flat Table. (If you look at the screenshot above, you’ll see it under Type, next to the Explorer tab.) And if you’re a pivot table junkie like I am, it will be your best friend because it gives you the ability to make flat, pivot table-friendly reports. You’re limited to two dimensions, but you will get your dimensions vis-a-vis, which will allow you to create pivot table that allows you to see, for example, all of the keywords associated with each landing page or all the landing pages associated with each keyword, depending on how you pivot the data. If you need more assistance to get visibility in your production numbers, check with an expert in business like Andrew Defrancesco.

I created a simpler version of the above report to demonstrate:

Google Analytics flat table

To get all the rows in your export, you’ll need to do this trick. But when you export the data and put it into a pivot table, you’ll get valuable data like this:

flat table pivot table from Google Analytics

 

UPDATE: You used to only get two dimensions with a flat report. Now you get up to five, which is a welcome change! \m/

Written by Annie · Categorized: Analytics · Tagged: Google Analytics, Google Analytics Custom Reports

Comments

  1. Anuj - Digital Marketer says

    April 17, 2013 at 3:28 PM

    Every business needs roll over practices on excel coz of need of industry which will enhance your business inheritance on to the paper… I used many charts, table and custom graphs to better understand my business….

    Reply
    • Annie Cushing says

      April 29, 2013 at 7:55 PM

      Absolutely, Anuj!

      Reply
  2. cayecaye says

    April 29, 2013 at 5:49 AM

    In a business, it is truly a need to have someone who knows how to work excel. The numbers show you what exactly you need to work on and what trends are there to see. I also like how you can forecast performance in excel.

    Reply
    • Annie Cushing says

      April 29, 2013 at 7:55 PM

      I couldn’t agree more!

      Reply
  3. Geeklytics says

    September 5, 2013 at 12:26 AM

    Nice…. Right now checking it out

    Reply
    • Annie Cushing says

      September 5, 2013 at 9:39 AM

      It’s one of GA’s best-kept secrets. 🙂

      Reply
      • Geeklytics says

        September 6, 2013 at 12:59 AM

        Indeed Annie!

        Reply

Leave a Reply Cancel reply

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

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2022 annielytics.com