Build your own Google Analytics Dashboard in Excel for free!

UPDATE (November 26, 2015): This blog post has been updated to xlwings v0.5.0

This post is about building a Google Analytics Dashboard in Excel (the place where the data is ending up sooner or later anyway). To do this, we’ll be using Python with the Pandas and xlwings packages.

Google Analytics is great and free, but sometimes you wish it had a bit more power. However, the price tag of $150,000/year for the premium version isn’t exactly what you would call “pocket change”, especially if you are a smaller company.

For example, in the online version of Google Analytics, I miss the behavior chart that can be found in the iPhone version: A simple heatmap that shows activity per hour and weekday. That’s exactly the thing we’re going to build, only better: We’re going to split the data in half-hour intervals, taking advantage of the fact that the API delivers data on minute level. Here is a screenshot of what we’re going to build (click to enlarge):

image-1

The advantages of building our own dashboard with Python are obvious:

  • Save time: Combine all your favorite statistics into a single dashboard. Exactly the way you want them.
  • Keep your data safe: Combine Google Analytics data with your company internal data without the need to upload sensititve data to Google.
  • More details: The API offers data on minute level, not just hour level as the online version does.
  • Better accuracy: Get around the problem of sampling (Google reports are sampling results as soon as a certain amount of data is involved).
  • One stop shop: Python can do it all - you don’t need another add-in for database access, reporting etc.

If this sounds all good to you but you would like to hire us to help, please contact us for more info.

Step 1: Install Python

The fastest way to get off the ground is by installing the Anaconda distribution. Since we make use of Excel, our tool will only run on Mac and Windows, so get one of these two versions. Unfortunately, Google currently offers their API package only for Python 2 (shame on you, Google!), so it is recommended to work with Python 2.7 (although you might want to try your luck with an unofficial version that supports Python 3). Once Anaconda is installed, there’s only a few packages that need to be installed additionally:

$ pip install xlwings==0.5.0
$ pip install google-api-python-client==1.3.1
$ pip install python-gflags==2.0
$ pip install oauth2client==1.4.12

If you don’t go for Anaconda, you additionally need to install numpy, pandas and if you are on Windows also pywin32.

Step 2: Google Developers Console

Now head over to Google Developers Console and login with the account you use for Google Analytics.

A) CREATE PROJECT

After logging in, click on the blue button Create Project and type in a project name, something like “Google Analytics Dashboard for Excel”. The default for Project ID is just fine. Then click on Create.

B) API

Under APIs & auth > APIs, enable Analytics API by clicking on the OFF button. You may want to disable the other APIs that are already enabled:

image-1

Click on APIs & auth > Consent screen and select as a minimum an Email Address and type in a Product Name, again, something like “Google Analytics Dashboard for Excel” will do. Then click Save.

D) CREDENTIALS

As last step, go to APIs & auth > Credentials and click Create new Client ID under “OAuth”. Then, select Installed application as “Application Type” and Other as “Installed Application Type” and hit Create Client ID. Once done, hit the Download JSON button, and save the downloaded file under the name client_secrets.json in a new directory for the project, i.e. ga_dashboard. It is important that this file is only downloaded after the API and Consent Screen have been set.

Note: Keep your client_secrets.json file in a safe place and don’t share it with anybody, as they could otherwise use your quota on Google Analytics.

Step 3: Build the Excel Tool

Now we can turn to Excel and start building our GUI (graphical user interface).

If you want it the easy way, simply download a ready-to-use zip-file here. Then unzip it (Mac: double-click, Windows: right-click and select “Extract all…”) and place the json file from before in that same unzipped folder.

Your folder should now look like this:

image-1

Fill in the Account, Property and View fields in the Excel sheet. You’ll find the according names when you log into Google Analytics, under the Admin menu. You may also want to adjust the From and To fields.

Ready to go? Hit the Refresh button! Note that you might have to change the settings according to the xlwings docs, if you haven’t installed Anaconda with the default settings.

The very first time, a browser window will pop-up, where you’ll have to login with your Google Account and allow access to the project you created under Step 2:

Once you have granted access, the browser will give you a confirmation (“The authentication flow has completed”) and Excel will give you a deprecation warning (should be addressed in a future release of Pandas) along with an “Authentication Successful” message:

image-1

That’s it! The next time you hit refresh, you don’t need to re-authenticate (the token analytics.dat will be stored in the pandas/io directory). If Effective = Max Rows, you should increase Max Rows to get all data for the indicated time period.

If you want to build the Excel tool on your own, follow these steps:

  • Create a new Workbook called GA_Dashboard.xlsm - make sure to save it as Excel Macro-Enabled Workbook in the ga_dashboard folder along with the json file you downloaded under Step 2.
  • Press Alt-F11 to open the VBA Editor. There, go to File > Import File... and select the xlwings VBA module. See the xlwings docs for details.
  • Then, still in the VBA editor, create a new VBA module: Insert > Module and paste the following code:

    Sub Refresh()
        RunPython ("import ga_dashboard;ga_dashboard.refresh()")
    End Sub
    
  • Save the following code in a file called ga_dashboard.py in the ga_dashboard folder along with the json and xlsm files.

    import os
    import numpy as np
    import pandas as pd
    import pandas.io.ga as ga
    from datetime import datetime
    from xlwings import Workbook, Range
    
    # Worksheets
    sheet_dashboard = 'Sheet1'
    
    # Client Secrets file: same dir as this file
    client_secrets = os.path.abspath(os.path.join(os.path.dirname(__file__),
                                                  'client_secrets.json'))
    
    
    def behavior(start_date, end_date, account_name, property_name, profile_name, max_results):
      """
      Writes a DataFrame with the number of pageviews per half-hours x weekdays
      to the Range "behavior"
      """
      # Let pandas fetch the data from Google Analytics, returns a generator object
      df_chunks = ga.read_ga(secrets=client_secrets,
                             account_name=account_name,
                             property_name=property_name,
                             profile_name=profile_name,
                             dimensions=['date', 'hour', 'minute'],
                             metrics=['pageviews'],
                             start_date=start_date,
                             end_date=end_date,
                             index_col=0,
                             parse_dates={'datetime': ['date', 'hour', 'minute']},
                             date_parser=lambda x: datetime.strptime(x, '%Y%m%d %H %M'),
                             max_results=max_results,
                             chunksize=10000)
    
      # Concatenate the chunks into a DataFrame and get number of rows
      df = pd.concat(df_chunks)
      num_rows = df.shape[0]
    
      # Resample into half-hour buckets
      df = df.resample('30Min', how='sum')
    
      # Create the behavior table (half-hour x weekday)
      grouped = df.groupby([df.index.time, df.index.weekday])
      behavior = grouped['pageviews'].aggregate(np.sum).unstack()
    
      # Make sure the table covers all hours and weekdays
      behavior = behavior.reindex(index=pd.date_range("00:00", "23:30", freq="30min").time,
                                  columns=range(7))
      behavior.columns = ['MO', 'TU', 'WE', 'TH', 'FR', 'SA', 'SU']
    
      # Write to Excel.
      # Time-only values are currently a bit of a pain on Windows, so we set index=False.
      Range(sheet_dashboard, 'behavior', index=False).value = behavior
      Range(sheet_dashboard, 'effective').value = num_rows
    
    
    def refresh():
      """
      Refreshes the tables in Excel given the input parameters.
      """
      # Connect to the Workbook
      wb = Workbook.caller()
    
      # Read input
      start_date = Range(sheet_dashboard, 'start_date').value
      end_date = Range(sheet_dashboard, 'end_date').value
      account_name = Range(sheet_dashboard, 'account').value
      property_name = Range(sheet_dashboard, 'property').value
      profile_name = Range(sheet_dashboard, 'view').value
      max_results = Range(sheet_dashboard, 'max_rows').value
    
      # Clear Content
      Range(sheet_dashboard, 'behavior').clear_contents()
      Range(sheet_dashboard, 'effective').clear_contents()
    
      # Behavior table
      behavior(start_date, end_date, account_name, property_name, profile_name, max_results)
    
    
    if __name__ == '__main__':
      # To run from Python. Not needed when called from Excel.
      path = os.path.abspath(os.path.join(os.path.dirname(__file__), 'GA_Dashboard.xlsm'))
      Workbook.set_mock_caller(path)
      refresh()
    
  • Insert a button (you may have to enable the Developer tab on the ribbon first) and connect it to the Refresh macro.
  • Create the following named ranges: B5: “account”, B6: “property”, B7: “view”, B9: “from”, B10: “to”, B12: “max_rows”,B13: “effective”, D4:J52: “behavior”
  • Label the fields accordingly: A5: “ACCOUNT”, A6: “PROPERTY”, A7: “VIEW”, A9: “FROM”, A10: “TO”, A12: “MAX ROWS”, A13: “EFFECTIVE”
  • Label C5:C52 with the hour from 0.00 to 23.30 in half hour steps.
  • Apply a conditional formatting of your liking to D5:J52.

That’s it! Enjoy!