Create a Twitter Analytics Dashboard with YQL and PHP

Written by Ethan Gardner Web Design

There are many reasons to monitor the performance of multiple Twitter accounts. If you maintain more than one site or blog, chances are you have multiple Twitter accounts that correspond to each contributing author, site, or product. Perhaps you want to keep tabs on how your account stacks up against your competitors. Having a birds’ eye view of how each account is performing can be a great time-saver, but unfortunately there is no easy way to keep track of how your accounts are engaging others.

Today, I’ll show you an easy way to create your own “dashboard” tool to get an at-a-glance view of multiple Twitter accounts using PHP and the Yahoo Query Language (YQL).

What is YQL?

YQL is a tool from Yahoo that can create an API from virtually anything on the web. The YQL Console allows a developer to retrieve data from Yahoo’s web services or any of the user-contributed “community tables” for a growing number of popular sites including Yelp, Facebook, and, in our case, Twitter. The best part of YQL is that the results of each query are returned in XML or JSON, so web developers can use their programming language of choice to develop applications.

There are a number of resources available for learning YQL. The best tutorial I have found on YQL in order to get you started is a live demo from Yahoo Developer Evangelist, Christian Heilmann.

Getting the Twitter Information

The data was generated by using a combination of the twitter.user.profile community table and query.multi to compile the results for the numerous Twitter accounts into one request. The URL of the JSON result was then reverse engineered, spliced, and then reassembled using PHP. Each request could have been hard coded instead of looping through an array, but ultimately adding the extra step to include the array makes the code more readable and maintainable. Here is the relevant code used to produce the sample.

 
<table id="social-media" class="tablesorter">
  <caption>Twitter Dashboard with YQL</caption>
  <thead>
    <tr>
      <th>Account Name</th>
      <th>Followers</th>
      <th>Following</th>
      <th>Last Tweet</th>
    </tr>
  </thead>
  <tbody>
  <?php
  // assemble the array of twitter accounts
  $accounts = array('ethangardner', 'yql', 'jquery', 'ydn', 'php_net');
  // sort accounts alphabetically 
  sort($accounts);
  // loop through the array of accounts and assemble the url for the YQL results
  $accountSize = sizeof($accounts);
  for($i=0; $i<$accountSize; $i++){
    $accounts[$i] = 'select%20*%20from%20twitter.user.profile%20where%20id=\'' . $accounts[$i] . '\'';
  }
  // prepare to assemble all urls in combination for the query.multi data in YQL  
  $allAccounts = implode(";", $accounts);
  $url = "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20query.multi%20where%20queries%3D%22" . $allAccounts . "%22&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";
  // call the url using curl
  $call = curl_init();
  curl_setopt($call, CURLOPT_URL, $url);
  curl_setopt($call, CURLOPT_RETURNTRANSFER, 1);
  $output = curl_exec($call);
  curl_close($call);
  // get the results of curl and assign it to a JSON object
  $data = json_decode($output);
  // loop through the results
  foreach($data->query->results->results as $profile){
    $item = $profile->item;
    $created = $item->meta[10]->content;
    $lasttweet = $item->item[1]->meta[1]->content;
    // Make any text in the most recent tweet beginning with 'http://' a clickable link
    $lasttweet = preg_replace("/(http:\/\/)?([a-zA-Z0-9\-.]+\.[a-zA-Z0-9\-]+([\/]([a-zA-Z0-9_\/\-.?&%=+])*)*)/", '<a href="http://$2">http://$2</a>', $lasttweet); 
    // create table rows for each of the results and assign data to its appropriate cell
    echo '<tr><td><a href="' . $item->resource . '">' . $item->meta[1]->content . '</td><td>' . $item->meta[5]->content . '</td><td>' . $item->meta[6]->content . '</td><td>' . $lasttweet . '</td></tr>';
    }
  ?>
  </tbody>
</table>
 

Demo

Where Do We Go From Here

A touch of jQuery was added using the fantastic tablesorter plugin to allow users to sort by number of followers/following. One suggestion would be to create a form to accept account names submitted by a user to allow for event more flexibility, but that is beyond the scope of this article. Other possibilities include connecting this information to a database so historical data with date ranges can be collected over time, adding an export utility to download a CSV file of the data, or using a chart API to provide more visual data.

Conclusion

The best tools solve problems and APIs are a great way to leverage data from other sources. Using YQL gives developers access to 800 data tables from some of the most popular and useful sites on the internet. YQL is effective because it allows developers to rapidly create solutions using their programming language of choice and has the potential to save a tremendous amount of time.