Create live charts in PowerPoint with Twitter Analytics data

by Hrishikesh on August 18, 2011 · 3 comments

in Tutorials

You have been creating charts in PowerPoint for a long time. You create a presentation, insert a chart, write or copy-paste the data into spreadsheet, customize some settings and you are done. And, it has worked perfectly for you till now. Hasn’t it?

But what if the data changes every day or week, or heck every minute? Right now, just before you deliver the presentation, you update the data by copying it from your source (like your sales data from SalesForce or other CRM if you are a sales guy, analytics data from Twitter or Google Analytics if you are a marketing guy), edit the chart and paste the new data again? This is repetitive, boring and takes up innumerable hours of your life. We say, read this blog post and learn how oomfo can automate this entire process for you. You’ll be able to build the charts once, connect it to your live data and never ever have to update it again.

Sounds hard to believe, but let us show you how.

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 which would talk to the data source, retrieve data, and present 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, but while you are enjoying the 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 mention that we intend to provide ready-made connectors for many popular systems like Twitter, SalesForce, Google Analytics. However, if you have a proprietary ERP or CRM system or one hidden behind your firewall, you might to get your IT team involved.

The most compelling benefit of having such connectors is that you need to create both the connector and chart only once(we’ll show you how), and the latest data will automatically be shown on your slides every time you run the slideshow. Now you don’t need to copy-paste your data in PowerPoint whenever it changes.

In this blog post, we will build an example that marketing guys would love to use – building live Twitter analytics charts within their presentations that are always updated. We will show you how to pull vital data from Twitter and other sources related to Twitter accounts. This connector is written in PHP, and would be accessing the Twitter API. We have also provided the connector as a download, so that you can start using it right away.

How to get started

To begin with, you would need to have:

  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.
  3. Download the Twitter connector.
  4. You will also require an active internet connection to query the API.
  5. Request your IT department to host the connector on a Linux server. More info here.
  6. Request your IT department to enable cURL on the Linux server. More info here.

Charts provided by the Twitter Connector

Using the Twitter connector provided, you can create 5 different kinds of charts:

  • Followers and Following: This call gives you the followers and following count of one or more Twitter handles you that ask for. It allows you to create a single-series chart if only 1 handle is given, or a multi-series chart if more than 1 handles are given.
  • Count: This call gives you the number of times a term (or more than one term) has been tweeted in a particular time period. It returns data for a single-series chart.
  • Historical data: This call is quite useful. It gives you the number of times a term has been tweeted over a period of time. You can get the data split into time intervals also. For example, you could check for occurrences of newyork in the last 30 days, with individual counts on each of the last 30 days. It returns data for a multi-series chart.
  • Followers’ history: This call gives you the count of followers for one of more Twitter handles for the last 14 days. It returns data for a multi-series chart.
  • Rank: This call calculates the relative rank of one or more Twitter handles among all Twitter users, taking into consideration the tweets, retweets, following count and a lot more parameters.

These calls provide most of the Twitter analytics web marketers need.

Let’s see the charts in action

We will be trying out the Followers and Following call. You would not be required to share your Twitter login credentials to use this connector.

Start PowerPoint, click on the Insert tab, and on the right hand side of the ribbon you would see the cluster of oomfo buttons.

Click on the Insert Chart button. In the oomfo window, select Single Series from the drop down, and select 3D column chart.

In the Chart Data tab, click on External Data Source > XML from File/URL.

You would be writing the connector’s URL along with the parameters in the textbox to the right. The general structure of the URL would be URL path + call type + parameters and values required for each call. We will now see how to construct this properly.

Paste the connector’s URL in the textbox. It should be http://yoursite.com/path_to_file/Twitter.php. Here yoursite.com/path_to_file is just a placeholder and should be replaced by the URL of your server, to which your IT team has copied the connector file. Make sure you get the correct URL. This URL now needs parameters according to which it will query and return the data.

For the Followers and Following call, we require the following parameters:

PARAMETER VALUE
call ff
followers yes (optional)
following yes (optional)
twitter_handle a valid Twitter handle

At least one of followers or following is required.

So, let’s say we want to get the followers and following count of the Twitter handle barackobama in our chart. Here is how you would configure the URL:

http://yoursite.com/path_to_file/Twitter.php?call=ff&followers=yes&following=yes&twitter_handle=barackobama

Please notice the ? and the &; they are required exactly in the way shown. Now click on the Load button, and you would get the generated XML data in the text area below. Click on OK > Finish.

There you have it! A live count of the followers and following of a Twitter handle! These values would keep updating each time you start your presentation, till infinity and beyond!

Compare multiple Twitter handles

To make things more interesting, try adding more Twitter handles. Just separate them with a comma. First of all, try changing the chart from Single Series to Multi Series.

Then modify the above URL to twitter_handle=barackobama,SenJohnMcCain,algore. Click on Load and go back to your slide to see a multi-series chart.

That’s it!

So that was about one of the five calls. There are still 4 more for you to play around with, each with more interesting data than the other.

Details of each call

Followers and Following

PARAMETER VALUE
call ff
followers yes (optional)
following Yes (optional)
twitter_handle a valid Twitter handle

At least one of followers or following is required

Sample query:

http://yoursite.com/path_to_file/Twitter.php?call=ff&followers=yes&following=yes&twitter_handle=barackobama,SenJohnMcCain,algore

Count of a term over a period of time (single-series)

PARAMETER VALUE
call count
term comma separated – barackobama, algore(more than 1 term is required)
period a (all time) / m (last 30 days) / w (last 7 days) / d (today) / h (current hour) (required)

Sample query:

http://yoursite.com/path_to_file/Twitter.php?call=count&term=apple,google&period=m

Historical data (multi-series)

 

PARAMETER VALUE
call history
term any string (or comma separated – barackobama, butt, ohm) (required)
slice slice in seconds (defaults to 86400 seconds, which equals 1 day)
period number of days (defaults to 30)

Sample query:

http://yoursite.com/path_to_file/Twitter.php?call=history&term=newyork,mumbai,bangalore&slice=86400&period=7

Get followers count of a user over the last 14 days (multi-series)

 

PARAMETER VALUE
call followershistory
twitter_handle barackobama, algore, charliesheen (comma separated values)

Sample query:

http://yoursite.com/path_to_file/Twitter.php?call=followershistory&twitter_handle=barackobama,algore

 

Get the relative rank of a Twitter user, with respect to amount of retweets and followings (single series)

 

PARAMETER VALUE
call rank
twitter_handle barackobama,algore,charliesheen (comma separated values)

Sample query:

http://yoursite.com/path_to_file/Twitter.php?call=rank&twitter_handle=barackobama,algore,charliesheen

Fun!

In this blog post we just explained how to use the provided Twitter connector. If you understand PHP, you can open the code and see how we connected to the Twitter 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.

{ 3 comments… read them below or add one }

mukesh sharma August 27, 2011 at 8:02 pm

Great post! It would be great if you can publish a guide on how to connect with Google Analytics.

Reply

Rishi August 29, 2011 at 10:20 am

Hi Mukesh, we’re coming out with a post within a couple of days that will use a Google Analytics connector. If you like this post then please share it with your network :)

Reply

Chad Musgrove (Easy Analytics Reports) September 13, 2011 at 2:40 am

Thank you very much for this.  I, like Mukesh Sharma, am very interested in the upcoming post describing how to use Google Analytics API export as a connector.  

Love what I see so far.

Reply

Leave a Comment

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

Previous Post

Next Post