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

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

   

4. In the Chart Data tab, click on External Data Source > XML from File/URL.You 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.

   

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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.

Tagged as: google analytics, live charts, live data charting