Presenting Real-time Google Analytics Data in PowerPoint: Live Charts, Lesser Heartburn, Greater Accuracy

by Hrishikesh on October 2, 2011 · 8 comments

in Tutorials

What does an SEO agency gearing up for a client review, an advertiser trying to showcase media to potential buyers, a business head trying to prove his worth to an investor, and an  in-house analyst trying to streamline ecommerce operations have in common? The heartburn of having to put together a meaningful story from the exhaustive data in their Google Analytics account. First, pulling out the right information and tying it together in a neat presentation for the report is at least a half day’s work (on a good day). And since website information changes rapidly, the report could get obsolete before it even gets presented. And finally, the routine of pulling out fresh data, exporting it to Excel to run some numbers and putting that back into PowerPoint is just downright boring.

But what if you did not have to do any of this? If you could just create your presentation once and have it updated in real-time? Sounds unbelievable, right? In this post we’ll show you how oomfo can ensure that your analytics presentations are always up-to-date, every time!

oomfo makes sure that your presentation has the latest information every time you run a slide show, through a connector which logs in to Google Analytics for you and retrieves all the relevant data. You wouldn’t ordinarily have to worry too much about the connectors after the initial setup, unless you have some legacy data sources that need customization and inputs from your IT team. Anyway, we have a detailed explanation of what connectors are and how they do what they do towards the end of this post.

By following the simple steps in this post, you will be able to create a scalable PowerPoint presentation that presents live Google Analytics data every time you run it.

Getting Started

To begin with, you will need:

  1. PowerPoint 2003 / XP / 2007 / 2010. (Only 32-bit version for PowerPoint 2010).
  2. The latest oomfo add-in installed. Get the latest version of oomfo here.
  3. The oomfo connector for Google Analytics. Download the Google Analytics connector here.
  4. Your Google Analytics Profile ID. You can get your Profile ID here AFTER logging into your Google Analytics account.
  5. An active internet connection when viewing the presentation, to get the latest data.
  6. A linux server to host the connector. Your IT department can help you with this, or you can find more information here.
  7. You will also need to enable cURL on the Linux server. You can find more information on this here.

Which charts does the Google Analytics connector provide out of the box?

Using the Google Analytics connector provided, you can create 8 different kinds of charts. To make them easier to refer and use, we’ve named each one with an alias:

  • visitorsbycount: Number of visitors, sorted by their visit count.
  • visitorsbydate: Total number of visitors for every day of the period you specify.
  • pageviewsbydate: Total number of pageviews for every day of the period you specify.
  • visitorsbysource: Number of visitors, grouped by the source from which they come from.
  • visitorsbycountry: Number of visitors, grouped by their country.
  • visitortype: Number of visitors, categorized by New Visitors and Returning Visitors.
  • ismobile: Number of visitors who come from a mobile device versus from a desktop/laptop.
  • exitpagepath: The number of visitors who leave your site, grouped by the exit page (last page they were on before leaving).

These calls provide the metrics that SEO specialists require most often. Now that we got that covered, let’s jump right in to making a slide deck with real-time Google Analytics.

Creating the charts in your presentation

Let us start off with the visitorsbycount call. You will be required to use your Google Analytics login credentials to use this connector. We’ll show you how and where to enter them.

  1. Open PowerPoint, and click on the Insert tab. On the right hand side of the ribbon should see the cluster of oomfo buttons.
    Cluster of oomfo buttons
  2. Click on the Insert Chart button. An oomfo window should open.
  3. Select Single Series from the drop down, and select 3D column chart.

    Select the 3D Column chart from the Single Series menu

  4. In the Chart Data tab, click on External Data Source > XML from File/URL.External Data Source in oomfo from the Chart Data tabYou will need to enter the connector’s URL along with some parameters in the top textbox. Now let us construct this URL.
  5. The connector’s URL should read http://yoursite.com/path_to_file/GoogleAnalytics.php. Replace yoursite.com with the URL of the server where the connector is hosted. Make sure you get the correct URL. This URL now needs parameters according to which it will query and provide the right data for your presentation.
  6. For the visitorsbycount call, you will need to provide the following parameters:
    PARAMETER VALUE
    email Google Analytics email ID
    password Google Analytics email password
    profile_id Google Analytics Profile ID (sample — ga:123456)
    type visitorsbycount
    start-date YYYY-MM-DD
    end-date YYYY-MM-DD

    Here is how you can configure the parameters and the URL:

    http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=yourpassword&profile_id=ga:yourprofileid&type=visitorsbycount&start-date=yyyy-mm-dd&end-date=yyyy-mm-dd

    Please notice the ? and the &; it is required exactly in the way shown. Replace youremail@abc.com, yourpassword and yourprofileid with the email address and password you use to access your Google Analytics, and your profile ID respectively.

  7. Now click on Load, and you would get the data in the text area below. Click on OK > Finish.

    Visitors by Count

There you have it! A live count of your visitors sorted by the number of visits in just seven steps!

But wait- you have 7 calls more to play around with. For each call, you would only have to modify the parameters in the final URL.

Details of each call

visitorsbycount (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type visitorsbycount
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=visitorsbycount&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by Count

visitorsbydate (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type visitorsbydate
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=visitorsbydate&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by date

pageviewsbydate (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type pageviewsbydate
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=pageviewsbydate&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Pageviews by date

visitorsbysource (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type visitorsbysource
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=pageviewsbydate&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by source

visitorsbycountry (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type visitorsbycountry
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=visitorsbycountry&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by Country

visitortype (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type visitortype
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=visitortype&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

ismobile (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type ismobile
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=ismobile&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Percentage of users from mobile devices

exitpagepath (single-series)

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type exitpagepath
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=exitpagepath&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by Exit-Page-Path

custom (single-series)

We wanted to provide you an option to create a chart using your own dimensions and metrics. The Google Analytics Query Explorer provides you an amazing way to try out newer combinations. To create such a custom call:

PARAMETER VALUE
email Google Analytics email ID
password Google Analytics email password
profile_id Google Analytics Profile ID (sample — ga:123456)
type custom
dimensions A valid dimension (eg. ga:continent)
metrics A valid metric (eg. ga:visitors)
sort A valid sorting attribute (eg. -ga:visitors)
start-date YYYY-MM-DD
end-date YYYY-MM-DD

Sample query:

http://yoursite.com/path_to_file/GoogleAnalytics.php?email=youremail@abc.com&password=xxxxxx&profile_id=ga:xxxxxx&type=custom&dimensions=ga:continent&metrics=ga:visitors&sort=-ga:visitors&start-date=YYYY-MM-DD&end-date=YYYY-MM-DD

Visitors by Continent

Quick tip:

If you’d rather not enter your email, password and profile id every time, you can enter these in the GoogleAnalytics.php file that you’ve downloaded. You can open the file in any plain text editor, and add the email address on line 10, the password on line 11 and the profile id on line 12. These have to be written within the quotes provided. And then un-comment the 3 lines by deleting the 2 slashes at the beginning of each line.

Save your credentials in the connector's file

After doing this, you would not have to write these in your query string everytime. However, be aware that your credentials are stored in plain-text for anyone to see.

The Magic Connectors

oomfo can automatically pull data from any data source that can be accessed from your browser, be it SalesForce, Google Docs, Google Analytics, Twitter, SharePoint, your CRM or ERP system, or even local files. All one needs to have, is a connector. A connector is something that talks to the data source, retrieves data, and presents it in a structure that oomfo understands.

Now for the difficult part. The connector is a web page (with programming instructions) that can talk to various other systems on web or your enterprise network like Twitter, SalesForce, Google Analytics, Google Docs, your ERP or CRM. If login credentials are required for any of these services, then these will have to be provided to the connectors. It’s like the connector is logging instead of you, getting the latest data, converting it into XML format and updating it in PowerPoint, while you are enjoying your siesta or the game. This will happen automatically every time the presentation is viewed as a slideshow.

Now before you start sending emails to your IT team, let us quickly tell you that we provide the connector for Google Analytics, SalesForce, and have done it for Twitter earlier. However, if you have a proprietary ERP or CRM system or one hidden behind your firewall, you might have to get your IT team involved.

Summing it up

In this blog post we saw how to create live charts in PowerPoint using your Google Analytics data. If you understand PHP, you can open the connector provided and see how we connected to the Google Analytics API, retrieved data and built the XML. The XML is based on the FusionCharts format. In the coming posts, we will talk more about how the connector was built and the programming side of things.

If you want more oomfo goodness, you can read more about the connector for Twitter and the connector for SalesForce.

{ 8 comments… read them below or add one }

Chad Musgrove (Easy Analytics Reports) October 4, 2011 at 9:59 pm

Thank you very much for posting this.  I have been playing with the slides.  I have been able to get the first slide to pull the data for visitors by count – but the data is presented in a weird way.  When I take the sum of the total number it returns – it is correct, but it is laid out in a strange manner.  

For example – there are 13 data labels on the x-axis.  Why is this?  The first slot on the x-axis returns a count of 100 – then the remaining 50 are split up (randomly?) in the remaining 12 slots on the x-axis.  I hope my description is making sense.  

Please advise.  As this does not make any sense for a number such as “visitor by count”.  

Thanks,
Chad

Reply

Chad Musgrove (Easy Analytics Reports) October 4, 2011 at 11:14 pm

I have figured it out – It was the sort.  To resolve the above issue, I added a dimension to the search in the GoogleAnalytics.api – then sorted by that dimension.  

The work you have done is wonderful – and helpful to an intermediate coder like myself.  :)  

Thank you so much for the great work.  

Regards,
Chad

Reply

Chad Musgrove October 5, 2011 at 12:09 am

Sorry to flood your comments with only mine (even though I appear to be the first to try this.) – but I can’t seem to get any type other than the visitors by count to work.  All other types return an error stating can not retrieve data from the URL.  

Any ideas?

Reply

Chad Musgrove (Easy Analytics Reports) October 5, 2011 at 12:26 am

it appears that the only 2 that are unable to retrieve data are “visitorsbydate” and “pageviewsbydate”

// Please delete these last 2 comments…I have put them there as feedback for you.  

Thanks,
Chad

Reply

Hrishikesh Choudhari October 7, 2011 at 11:33 am

Hi Chad,

We’re extremely glad that oomfo has got one more fan!

With regards to the “Unable to retrieve data from the specified URL” error, we tried duplicating the error on our side. The only way we got the error is when the querystring is malformed. Either a simple spelling mistake or a misplaced character could be the reason behind this.

We request you to please cross-check the sample query provided from ‘visitorsbydate’ and ‘pageviewsbydate’. If it still doesn’t work out, then we would like to have a look at the querystring that you’re using.

Hope this helps.

Reply

Leon Lindsey November 10, 2011 at 11:15 pm

I seem to get an error message when trying to download.  Has there been any other issues with trying to download the flash player

Reply

Hrishikesh Choudhari November 11, 2011 at 11:28 am

Hi Leon,

Downloading the connector doesn’t seem to be an issue. I have checked it twice.

Are you having a problem while downloading oomfo?

The Flash player will be automatically upgraded when installing oomfo.

Reply

Raj November 14, 2012 at 11:38 am

Sounds interesting.
I am a bit overawed by the Linux server requirement. Is there a way to make it easier for a non-techie?
thanks,
Raj

Reply

Leave a Comment

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Previous Post