Track Alexa rank, Facebook Like, Twitter Follower, and FeedBurner Subscriber counts

Recently, I published a few posts on getting Facebook like count, Twitter followers count, FeedBurner Subscriber count, and Alexa Rank and Sites Linking in using simple PHP codes. Doing so has several advantages: replacing widgets (thereby improving page speeds) and displaying counts a simple formatted texts. It also provides the opportunity to combine subscriber / follower count to display a single count. I wanted to take this one step further and start recording these values in a MySQL database on a daily basis, thus building a history. There are a couple of major advantages (at least from my point of view) to doing this:

1. I check my Facebook like count, Twitter followers count, FeedBurner subscribers count, Alexa rank, and Alexa sites linking in at least 2-3 times a week. Recording these values in a database allows me to visit a single page to check all these values (as shown in the picture below).
2. One other major advantage is the ability to see how the visibility of your website is trending. For example, if you implement a giveaway for your readers if "Like" your Facebook fanpage, you can see what effect it had on your Facebook like count.

Requirements

• Basic PHP and MySQL knowledge would greatly help (but not necessary)
• Review: Get Facebook like count using PHP
• Review: Get Twitter followers count using PHP
• Review: Get FeedBurner subscribers count using PHP
• Review: PHP Code to Get Alexa Rank and Site Linking

Creating a MySQL Table

First, you will have to create a table called wp_social_stats in your MySQL database. This is easy if you already know a little bit of MySQL and you have access to your database through phpMyAdmin or by other means. Remember that wp_social_stats should be table within your main database. Structure of the table is shown in the picture below:

Alternatively, through phpMyAdmin you can go to SQL tab of your database and type in the following query to create the table in one go. Change ENTER_YOUR_DATABASE_NAME to the name of your database.

PHP Code to Record Facebook, Twitter, FeedBurner, and Alexa Information

After you have created the wp_social_stats table within your MySQL database, it is now time to get the PHP code ready to record the Facebook, Twitter, FeedBurner, and Alexa rank values. Create a file named socialstats_record.php in the root folder of your site and copy the following contents to the file:

Download the full code using the download button below:

The following values have to be edited:

//Database details
$db_host='ENTER_YOUR_DATABASE_HOST'; 
$db_database='ENTER_YOUR_DATABASE_NAME';
$db_username='ENTER_YOUR_DATABASE_USERNAME';
$db_password='ENTER_YOUR_DATABASE_PASSWORD';

//Get FeedBurner Subscriber Count
$fburl="https://feedburner.google.com/api/awareness/1.0/GetFeedData?uri=http://feeds.feedburner.com/ENTER_YOUR_FEEDBURNER_FEED_NAME";

//Get Twitter Followers Count
$twurl = "http://twitter.com/users/show/ENTER_YOUR_TWITTER_ID";

//Get Facebook Like Count
$fpageID = 'ENTER_YOUR_FACEBOOK_PAGE_ID';

//Get Alexa Rank and Sites Linking in
$source = file_get_contents('http://data.alexa.com/data?cli=10&dat=snbamz&url=ENTER_YOUR_DOMAIN_NAME');

If you have trouble editing the above code refer: here, here, here, and here.

To visit the page go to http://yourdomain.com/socialstats_record.php (if you saved the file in your site's root folder). From now on, every time the page is opened your social stats are recorded into the database.

Schedule the PHP Code to Execute Daily

Visiting the page manually everyday to record the Facebook like count, Twitter followers count, FeedBurner subscribers count, Alexa rank, and Alexa sites linking numbers can be tedious. There are two ways to open the page automatically everyday:

1. Scheduled Cron Job in Linux Server

If your site is self-hosted and running on a Linux server, you can setup a cron job. You will need to curl installed. Setup a cron job as shown in the picture below using Webmin (Webmin installation guide). socialstats_record.php file will open everyday at 12:00 pm and the numbers will be recorded in the MySQL database.

2. WP-Cron

If you do not run servers or do not have the ability to run scheduled command then you could use WordPress's WP-Cron to open the page everyday. Checkout plugins such as FFF-Cron-Manager or Run External Crons. Setting up is as easy as providing the link to socialstats_record.php and the run interval.

Display Facebook, Twitter, FeedBurner, and Alexa Information

Now to display the counts and rank from the MySQL database, use the following PHP code:

Edit the following lines:

//Database details - Edit this
$db_host='ENTER_YOUR_DATABASE_HOST'; 
$db_database='ENTER_YOUR_DATABASE_NAME';
$db_username='ENTER_YOUR_DATABASE_USERNAME';
$db_password='ENTER_YOUR_DATABASE_PASSWORD';

//Number of Days to show the results for - Edit this
$num_days = 30;

The output look very similar to the first image on this post. If you would like to display one or more of the current counts / ranks then take a look at this code. Put the following code block in your header.php (depending on where you add it, you will have ensure that the php tags are opened and closed properly):

< ?php
//Database details - Edit this
//Put this code block in header.php
//Begin header.php code block
$db_host='ENTER_YOUR_DATABASE_HOST'; 
$db_database='ENTER_YOUR_DATABASE_NAME';
$db_username='ENTER_YOUR_DATABASE_USERNAME';
$db_password='ENTER_YOUR_DATABASE_PASSWORD';

require_once('DB.php');
$statconnection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");

$statquery = "SELECT * FROM `$db_database`.`wp_social_stats` ORDER BY `FID` DESC LIMIT 1";
$statresult = $statconnection->query($statquery);
$lxtats = $statresult->fetchRow(DB_FETCHMODE_ASSOC))
//End header.php code block
?>

Use the code below in section of the page or widget (such as WP PHP Widget) where you would like to display the count / rank. Delete the ones that you do not want to display.

< ?php
//The codes below can be placed where you want to display the counts / rank
//Display Latest Alexa Rank
echo 'Alexa Rank: '.lxstats["AlexaRank"];

//Display Latest Alexa Sites Linking in
echo 'Alexa Sites Linking in: '.lxstats["AlexaLinks"];

//Display Latest Facebook Like Count
echo 'Facebook Likes: '.lxstats["FacebookLikes"];

//Display Latest Twitter Followers Count
echo 'Twitter Followers: '.lxstats["TwitterFollows"];

//Display Latest FeedBurner Subscribers Count
echo 'FeedBurner Subscriptions: '.lxstats["FeedburnerSubscriptions"];
?>

Facebook, Twitter, FeedBurner, and Alexa History

As described in the beginning of this post, one of the main advantages, in my opinion, is to be able to see your site's social stats history. You can export the MySQL data and construct a plot to see how the stats are trending:

It might require some groundwork. But once setup I think it is very useful information to have. It helps me monitor my blog's progress. I hope it comes in handy for you. Please do share your thoughts and ideas in the comments section.