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.

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

 

If two dimensions aren’t enough for you, you’ll need to use the Google Analytics API, but you should be using it anyway! I show in that Search Engine Land post just how easy it is to pull GA API data straight into Excel using a free plugin.

  • Anuj – Digital Marketer

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

    • http://www.annielytics.com/ Annie Cushing

      Absolutely, Anuj!

  • http://twitter.com/iamcayester cayecaye

    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.

    • http://www.annielytics.com/ Annie Cushing

      I couldn’t agree more!

  • Geeklytics

    Nice…. Right now checking it out

    • http://www.annielytics.com/ Annie Cushing

      It’s one of GA’s best-kept secrets. :)

      • Geeklytics

        Indeed Annie!

  • Pingback: VLOOKUP Tips And Tricks For Marketers - Annielytics

Powered by WishList Member - Membership Software