Today, we expanded our data display options to include raw tabular data. Simply choose “Table” from the list of charting options and you’ll be able to view the raw data behind any chart in tabular form right in your RJMetrics dashboard:

All existing charts can be viewed as tables of data

The tabular output provides an easy way to view the raw data powering your charts (and you can still always export to CSV or Excel).

View the raw data behind any chart right in your dashboard

Like all other charts, tabular data sets can be enlarged by clicking the “Enlarge” button beneath the chart. This will provide an expanded view, making large data set exploration easier than ever with RJMetrics.

[This article was also featured as a guest post on TechCrunch on October 5th, 2009.]

A few weeks ago, my former employer led a $100 million investment into Twitter and I must admit that I was quite jealous of my former colleagues.  Chances are they got the opportunity to do some very cool analytics on Twitter’s data.

Rather than wonder about what I missed, I decided to figure out what I could from the outside looking in.  Using some statistical trickery, the Twitter API, and my RJMetrics dashboard, I uncovered a ton of astonishing new information about Twitter.  Here are some highlights:

• Twitter’s user growth is no longer accelerating.  The rate of new user acquisition has plateaued at around 8 million per month.
• Over 14% of users don’t have a single follower, and over 75% of users have 10 or fewer followers.
• 38% of users have never sent a single tweet, and over 75% of users have sent fewer than 10 tweets.
• 1 in 4 registered users tweets in any given month.
• Once a user has tweeted once, there is a 65% chance that they will tweet again.  After that second tweet, however, the chance of a third tweet goes up to 81%.
• If someone is still tweeting in their second week as a user, it is extremely likely that they will remain on Twitter as a long-term user.
• Users who joined in more recent months are less likely to stop using the service and more likely to tweet more often than users from the past.

Read on for some detailed charts a deeper dive into the data.

Read More »

[Stay up to date with the latest content: follow us on Twitter @RJMetrics]

Last week, news sites across the web were flooded with stories about “lucky babies” who were born at 9:09AM on 9/9/09.  Even more amazing was this story, which told of a baby born at that very minute with a birthweight of 9 pounds, 9 ounces.

The story sounded familiar, so I poked around and found this story from last summer.  It tells of two babies, each born at 8:08AM on 8/8/08 and weighing 8 pounds, 8 ounces.

This kind of news story always makes me suspicious.  It seems like the chance of this happening would be astronomical, especially two years in a row.  Rather than ponder the odds,  I decided to calculate them.

The 9/9/09 Baby

The two independent characteristics of this phenomenon are its birthtime and its weight.  Let’s explore each individually.

Probability of Being Born at 9:09AM on 9/9/2009 

The easy (and highly flawed) way to do this would be to simply divide one by the number of minutes in a year to get a crude probability of being born on any given minute.  However, the individual components of a birth time (month, day, hour, minute) are not uniform.  Indeed, each is governed by its own probability distribution, which we explore below and use to calculate a more accurate probability.

Month: The CDC’s National Vital Statistics System contained this recent report, which breaks out US births by month of the year.  It shows the distribution below with 8.8% of births taking place in September:

Day: While the individual days of a month aren’t heavily studied, I did find an interesting probaiblity distribution by day of the week in the same report:

9/9/09 was a Wednesday, which typically contains 16.1% of the births in a given week.  September contains 30 days, or 4.3 weeks.  If we assume an equal probability that the baby will be born in any week of the month, the probability of a birth on a specific Wednesday of a 30-day month like September is 3.7%.

Time: Births are surprisingly unevenly distributed across the hours of the day, as shown in this report, which reveals the probability distribution below:

The probability of a birth during the 9AM hour is reflected here as 3.5%, and if we assume a uniform distribution across the minutes of an hour (i.e. it’s equally likely to be born at any minute in a given hour), this places the probability of being born at 9:09AM on any given day at 0.06%.

Conclusion: Based on these numbers, the probability of a baby being born on 9/9/09 at 9:09AM is 0.0002%.  (I should note that this assumes that each of these time attributes is independent of the others.)

If we estimate 4.3 million births this year (in line with recent years), this probability tells us that the expected number of babies born at 9:09 AM on 9/9/2009 is… 9.  OK, I’ll admit it: that’s a little creepy.

But, that number is before we consider that amazing birthweight of 9 pounds, 9 ounces. 

Probability of a 9 Pound, 9 Ounce Birthweight

Various sources, including this report from the International Journal of Epidemiology, confirm that birthweights conform to a Gaussian distribution, as shown below:

Distribution of Birth Weights

Another study provided the characteristics of this distribution: a mean birthweight of 3,369 grams and a standard deviation 567 of grams.  Among other things, this tells us that the 9 pound, 9 ounce (4,337 gram) baby was, well, a chubby one (to the tune of 1.7 standard deviations above the mean).

The metric weights that a doctor could convert to ounces to get the 9 pound, 9 ounce measurement were 4,323 to 4,349 grams, or 1.68 to 1.73 standard deviations from the mean. 

Using a standard z-table, we easily determined that the probability of a 9 pound, 9 ounce birthweight is quite low: 0.43%.

End Result: Probability of a 9/9/09 9:09 birth weighing 9 Pounds, 9 Ounces

It’s fair to assume that a baby’s birthweight is independent of its birthdate, so we can simply multiply the probabilities from the past two sections to determine the probability that any given 2009 baby was born on 9/9/09 at 9:09AM weighing 9 pounds, 9 ounces: 0.0000008%.

With our estimate of 4.3 million births in 2009, that means the expected number of births meeting those characteristics was 0.035, which implies that there was a 3.5% chance of this baby being born.  Another way of arriving at the same result is to calculate the probability that no babies with these birth statistics would be born and look at what’s left (100%-((100% – 0.0000008%)^4,300,000) = 3.5%).

Regardless of how you slice it, there was a 3.5% (or about 1 in 28) chance of a baby being born on 9/9/09 at 9:09AM weighing 9 pounds, 9 ounces. 

You don’t need a business intelligence dashboard to tell you that this was a long shot.  However, when we factor in the babies from last year, things start to get even more outlandish.

The 8/8/08 Babies

Using the same data and the steps explained above, we can easily identify the probability of any given baby born in 2008 having the characteristics below:

Probability of being born on 8/8/08 at 8:08AM: 0.0002%

Probability of being born at 8 pounds, 8 ounces: 10.6%

Overall probability: 0.00002%

Note that the overall probability here is more than twice as high as for the 9/9/09 baby, mainly because 8 pounds, 8 ounces is a significantly more common birthweight. 

Even still, however, with 4.3 million expected 2008 births, the chance of a baby being born in the US on 8/8/08 at 8:08AM weighing 8 pounds, 8 ounces was a mere 10%.  The chance of two such babies being born independently, therefore, was a only 1%.

Again, this is a very low probability but certainly not astronomical.  What starts to get outlandish is when you consider the probability that all three of these babies would be born.  As three independent events, the chance that these three births would take place as they did was approximately 0.04%, or about 1 in 2,500.

Conclusions and Complications

Given the large amount of press attention showered upon these babies and their doctors, there appears to exist a disturbing incentive system for the birthing of babies with numerologically noteworthy birth statistics.  (A quick Google News search turned up 290 stories containing the names of the 9/9/09 baby’s parents.)

Given the low likelihood of any such baby being born (and the extremely low likelihood of all three being born independently), two alarming scenarios seem possible (this is, of course, speculation on my part):

• the involved medical professionals tweaked numbers to yield more noteworthy statistics
• an unusually large number of deliveries were “scheduled” for these dates to increase the chances of birthing a child with these statistics

Further complicating my math is the popularity of scheduled Cesarian births which allow parents and doctors to choose a specific day and time to begin a delivery process (within a range of options that are equally safe for the mother and child, of course). 

While it may seem perverse, it’s entirely possible that such deliveries are being scheduled to increase the chances of outcomes like these.  This could heavily impact the probability calculations above and might provide an explanation for the otherwise unfathomable outcomes we’ve seen.

What really sealed the deal for me was this story, which talks about an 8/8/08 baby and casually mentions that he has a younger sibling who was born on 4/5/06.  What are the chances that any given set of parents has two children with these two birthdates?  I’ll tell you: it’s 0.0009%.  (Both were born by Cesarian.)

I’ll leave you to draw your own conclusions, but the next time I read a story about a baby with amazing birth statistics, I’ll chalk it up to more than just “good luck.”  Something tells me I’ll have plenty of chances to do so next year– on October 10th.

[Stay up to date with the latest content: follow us on Twitter @RJMetrics]

The Problem

If you have MySQL users who seem to spontaneously lose their “LOCK TABLES” privilege, you’ve come to the right place. I ran into this problem recently and, after way too much investigation, I tracked down the source of the problem. Read on to learn about the cause and a few easy solutions.

The Cause

If you’re having this problem, I’d bet that cPanel is installed on your system (it was the culprit in my case).  I’d also bet that, every day, a cron job runs a script called “cpup,” which is basically the cPanel updater script. It’s about a thousand lines long, but the line we care about (in my installation) is number 822:

cPScript::WHMAPI::status_cmd(‘/scripts/mysqlup’);

This line calls another script of similar length called mysqlup, which is basically the MySQL updater script. Check out my line 197:

system ‘/scripts/securemysql’, ‘-qFa’, ‘removelockntmp’;

Yep, we’re calling yet another script here. This one is called securemysql, and it’s meant to harden up the security of your MySQL installation. It can do things like removing anonymous users and making sure that root has a password.  Check out the code starting at my line 178 of that script:

if ( $actions{‘removelockntmp’} ) {
        print “Dropping global lock tables and create tmp tables permissions … ” if $verbose;
        print MYSQL qq{UPDATE mysql.user SET Lock_tables_priv=’N',Create_tmp_table_priv=’N’ WHERE User!=’root’;\n};
        print “Done\n” if $verbose;
}

Jackpot!  The securemysql script runs a query that removes the “LOCK TABLES” privilege from every user except root.  In the same swoop, it removes the ability to create temporary tables.  Now that we know where the problem is coming from, it’s easy to fix it.

Solutions

I’ll avoid an extended discussion of the security and usability implications of allowing MySQL users to lock tables or disabling scripts designed to enhance security.  However, please consider them when deciding on a course of action here.

Option 1: Change the parameters to the securemysql call

 You may have noticed above that upmysql calls securemysql with a few additional parameters (yours may vary):

system ‘/scripts/securemysql’, ‘-qFa’, ‘removelockntmp’;

Simply deleting the action “removelockntmp” from the code will cause the ifstatement shown in the code block in the previous section to evaluate false and prevent the query from running.  My only concern here is that changes made to mysqlup may not be retained if that file itself is ever overwritten by a re-installation or updating of cPanel. 

Option 2: Disable the entire securemysql call

Depending on your needs, the best move may be to prevent securemysql from executing.  You can do this without altering any code by simply executing the following at the command line:

touch /etc/securemysqldisable

This is because of the following line at the beginning of the securemysql script:

exit if ( -e ‘/etc/securemysqldisable’ || -e ‘/etc/mysqldisable’ );

By creating the securemysqldisable file, you will cause that ifstatement to evaluate true and the securemysql script will exit before executing the code that disables LOCK TABLES.

Option 3: Restore the privileges automatically after they are revoked

There is a script called /scripts/postupcp that gets called at the very end of the upcp script.  If you’re not comfortable altering the mysqlup code or disabling the entire securemysql call, you can add lines to postupcp that restore the “LOCK TABLES” privilege to your users immediately after they are taken away by upcp, negating its effect.  To do this, simply add a line like the one below to the file for each user whose rights you need to restore:

mysql -e “GRANT LOCK TABLES ON *.* to ‘user’@'localhost’ identified by ‘pass’; FLUSH PRIVILEGES;”

The main concern here is that the MySQL user’s password is stored in plaintext in this file.  However, the file postupcp is owned and only readable by root, so you can make a personal call about whether this makes you comfortable.  Also, since postupcp is run by root, you probably won’t need to specify a MySQL username or password in the command (we don’t above).   However, if you do, that adds to the sensitivity of information in the command.