Google Analytics Dashboard in Excel

In this tutorial I will teach you how to build your own Google analytics dashboard using excel.

  • Exporting the data from Google Analytics using custom reports
  • Date formats, and modifying to best suit our purpose
  • Pivot tables and using them to control the graphs
  • Inserting slicers to help dashboard interactivity
  • Using two date selector to find 'previous' vs 'current' numbers

The analysis you can do with this style dashboard will help improve the insights you can pull out of this data. Even better than that, you will learn how to provide more value to your clients with an easy to update dashboard. This is definitely not rocket science!

Please provide feedback below, as I would love to hear your thoughts. Any additional insight you can provide is definitely useful.

 

Download the Google Analytics Dashboard
  You will receive a download link via email. We hate spam and never share your details.

14 Comments

  1. Hello Sam,

    Highly appreciate your tutorial and Excel you sent.

    Just one thing. I am trying to use the excel as a template for other clients, so just update the data often, but the problem is, the dashboard sheet, when i Refresh all data it says ”cant open pivottable source file”

    I also tried using your excel and putting my data and says the same, so how do i get the pivottables (dashboard sheet) to update to the data i put in RAW sheet.

    Please advice on how i can sync the data and the dashboard.

    Thank You,
    M.

    Reply
    • Hi,

      Glad you like the template! I think I covered this in the video, but you need to disconnect the slicers from the pivot tables. You then need to change the data source for each pivot table (in the ‘data’ or ‘pivot tables’ tab), to just be called “RAW”, as this will reference the actual data. If that doesn’t make sense, just let me know and I will create a separate video just for this.

      Unfortunately this is how Excel treats pivot tables when the files are shared, and there seems to be no way around it.

      Thanks,
      Sam.

      Reply
  2. Excellent piece of work. I really loves the way you explains.
    Thank you for this great stuff.

    Reply
  3. Hey Sam,

    I’m having the same problems M was having, Could you send me a video on what your talking about, please?

    I want to continuously update my data so that my clients will at a moments notice have the most up to date data.

    Thank you
    Richard

    Reply
  4. Really great work, Thanks for sharing to us!!!

    Again Thanks,

    Alberto

    Reply
  5. Great stuff. Is there a simpler tutorial for Pageviews? We’d like to only compare visits (per month) to specific sites within our web pages.

    Reply
    • Hi Peter,

      Instead of using traffic source, you could replace that in the data with ‘Site’. Or you could add a new column and filter the data by site, allowing you to analyse multiple sites / projects at once.

      You could also use pageviews instead of conversions, which would then allow you to have visits and pageviews on the same dashboard.

      I will look at creating a simpler video, for just visit across different sites / site sections, but it might be a little bit away.

      Thanks,
      Sam.

      Reply
  6. Hi Sam,

    Great tutorials very helpful, can you please share the excel, it would be really helpful.

    Thanks,
    Jyoti

    Reply
    • Hey Jyoti,

      Thanks for the feedback. I have just updated the content with the download option so that you can download the analytics dashboard excel file I created 🙂

      Thanks,
      Sam.

      Reply
  7. Hi Sam,

    Thanks for your reply, much appreciated.
    I still can’t see the link or the download option on this page.

    Regards,
    Jyoti

    Reply
    • Hi Mate,

      It’s the big colourful box 🙂 If you’re using an ad blocker, or similar, just disable it so you can see how to download.

      Thanks,
      Sam.

      Reply
  8. Thanks Sam.

    Reply
  9. Awesome report.

    Reply

Submit a Comment

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

Download the Keyword Categorisation Template!
  You will receive a download link via email. We hate spam and never share your details.
Download the Google Analytics Dashboard
  You will receive a download link via email. We hate spam and never share your details.