root/trunk/htdocs/stats.bml @ 919

(=PAGE
TITLE=>Statistics
BODY<=

(=H1 (=SITENAME=) Statistics H1=)
(=P
The following statistics may be interesting for some of you.  Note that for speed, most of this page is only updated every 24 hours.  However, certain parts are live.  Raw data can be picked up <A HREF="stats/">here</A>.
P=)
(=HR=)

(=_CODE

 my $ret = "";

 my $dbs = LJ::get_dbs();
 my $dbh = $dbs->{'dbh'};
 my $dbr = $dbs->{'reader'};

 $now = time();
 %stat = ();

 $sth = $dbr->prepare("SELECT statcat, statkey, statval FROM stats WHERE statcat IN ('userinfo', 'client', 'age', 'gender', 'account')");
 $sth->execute;
 while ($_ = $sth->fetchrow_hashref) {
     $stat{$_->{'statcat'}}->{$_->{'statkey'}} = $_->{'statval'};
 }

 unless (%stat) {
     return "(=H1 Sorry... H1=)(=P No statistics are available.  If you're the administrator for this site, run <b>ljmaint.pl genstats</b>, or ideally, put it in cron to run nightly. P=)";
 }

 $sth = $dbr->prepare("SELECT c.item, s.statval FROM stats s, codes c WHERE c.type='country' AND s.statcat='country' AND s.statkey=c.code ORDER BY s.statval DESC LIMIT 15");
 $sth->execute;
 while ($_ = $sth->fetchrow_hashref) {
     $stat{'country'}->{$_->{'item'}} = $_->{'statval'};
 }

 $sth = $dbr->prepare("SELECT c.item, s.statval FROM stats s, codes c WHERE c.type='state' AND s.statcat='stateus' AND s.statkey=c.code ORDER BY s.statval DESC LIMIT 15");
 $sth->execute;
 while ($_ = $sth->fetchrow_hashref) {
     $stat{'state'}->{$_->{'item'}} = $_->{'statval'};
 }

 $total = $stat{'userinfo'}->{'total'}+0;
 $usedever = $stat{'userinfo'}->{'updated'}+0;
 $used30 = $stat{'userinfo'}->{'updated_last30'}+0;
 $used7 = $stat{'userinfo'}->{'updated_last7'}+0;
 $usedlastday = $stat{'userinfo'}->{'updated_last1'}+0;
 $allow_getljnews = $stat{'userinfo'}->{'allow_getljnews'}+0;

 $ret .= "(=H1 Users H1=)\n";
 $ret .= "(=P How many users, and how many of those are active? <UL>";
 $ret .= "<LI><B>Total users: </B> $total\n";
 $ret .= "<LI><B>Users that have ever updated: </B> $usedever\n";
 $ret .= "<LI><B>Users updating in last 30 days: </B> $used30\n";
 $ret .= "<LI><B>Users updating in last 7 days: </B> $used7\n";
 $ret .= "<LI><B>Users updating in past 24 hours: </B> $usedlastday\n";
 $ret .= "</UL> P=)\n";

 $ret .= "(=H1 Gender H1=)\n";
 $ret .= "(=P Are males or females more likely to maintain journals? <UL>";
 {
     my $male = $stat{'gender'}->{'M'}+0;
     my $female = $stat{'gender'}->{'F'}+0;
     my $tot = $male+$female;
     $tot ||= 1;
     $ret .= "<LI><B>Male: </B> $male (" . sprintf("%0.1f", $male*100/($tot||1)) . "%)";
     $ret .= "<LI><B>Female: </B> $female (" . sprintf("%0.1f", $female*100/($tot||1)) . "%)";
 }
 $ret .= "<LI><B>Unspecified: </B> " . ($stat{'gender'}->{'U'}+0);
 $ret .= "</UL> P=)\n";

 $ret .= "(=H1 Account Types H1=)\n";
 $ret .= "(=P What type of <a href=\"/support/faqbrowse.bml?faqid=38\">account</a> do people have? <ul>";
 {
     # FIXME: ljcom specific, make into a hook.
     my %acct_name = ("paid" => "Paid Account",
                      "off" => "Free Account",
                      "early" => "Early Adopter",
                      "on" => "Permanent Account");
     foreach my $act (qw(off early paid on)) {
         my $num = $stat{'account'}->{$act}+0;
         $ret .= "<LI><B>$acct_name{$act}: </B> $num (" . sprintf("%0.1f", $num*100/($stat{'userinfo'}->{'total'}||1)) . "%)";   
     }
 }
 $ret .= "</ul> P=)\n";

 unless ($LJ::DISABLED{'stats-newsadv'}) {
     $ret .= "(=H1 Receiving News/Advertising H1=)\n";
     $ret .= "(=P This information says how many users of the ones with validated email addresses subscribe to the LiveJournal news that we send out. <ul>";
     $ret .= "<li><b>Users getting LiveJournal news: </b> $allow_getljnews</li>\n";
     $ret .= "</ul> P=)\n";
 }

 unless ($LJ::DISABLED{'stats-recentupdates'}) 
 { 
     $ret .= "(=H1 Recent Updates H1=)\n";
     $ret .= "(=P The following are the 10 most recently updated journals: <ul>";
     $sth = $dbr->prepare("SELECT u.user, u.name, uu.timeupdate FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate > DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY uu.timeupdate DESC LIMIT 10");
     $sth->execute;
     while (my ($iuser, $iname, $itime) = $sth->fetchrow_array) {
         $ret .= "<li><a href='/users/$iuser/'>(=_EH $iname _EH=)</a>, $itime</li>\n";
     }
     $ret .= "</ul> P=)\n";
 }

 unless ($LJ::DISABLED{'stats-newjournals'}) 
 {
     $ret .= "(=H1 New Journals H1=)\n";
     $ret .= "(=P The following are the 10 most recently created journals.  It's very likely these users haven't modified their journals much, and probably haven't wrote much in them yet either... <ul>";
     $sth = $dbr->prepare("SELECT u.user, u.name, uu.timeupdate FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate IS NOT NULL ORDER BY uu.timecreate DESC LIMIT 10");
     $sth->execute;
     while (my ($iuser, $iname, $itime) = $sth->fetchrow_array) {
         $ret .= "<li><a href='/users/$iuser/'>(=_EH $iname _EH=)</a>, $itime</li>\n";
     }
     $ret .= "</ul> P=)\n";
 }

 $ret .= "(=H1 Demographics H1=)\n";
 $ret .= "(=P The following are the 15 most popular countries LiveJournal is used in: <UL>";
 foreach my $key (sort { $stat{'country'}->{$b} <=> $stat{'country'}->{$a} } keys %{$stat{'country'}})
 {
     $ret .= "<LI><B>$key</B> - $stat{'country'}->{$key}\n";
 }
 $ret .= "</UL>\n";
 $ret .= "The following are the 15 most popular U.S. states LiveJournal is used in: <UL>";
 foreach my $key (sort { $stat{'state'}->{$b} <=> $stat{'state'}->{$a} } keys %{$stat{'state'}})
 {
     $ret .= "<LI><B>$key</B> - $stat{'state'}->{$key}\n";
 }
 $ret .= "</UL> P=)\n";

 # ages
 $ret .= "(=H1 Age Distribution H1=)(=P The following shows the age distribution of LiveJournal users: P=)\n";

 my %age = ();
 my $maxage = 1;
 foreach my $key (keys %{$stat{'age'}}) {
     $age{$key} = $stat{'age'}->{$key};
     if ($stat{'age'}->{$key} > $maxage) { $maxage = $stat{'age'}->{$key}; }
 }
 $ret .= "<P><TABLE>\n";
 my $lastage = 0;
 foreach my $age (grep { $_ >= 13  && $_ <= 55 } sort { $a <=> $b } sort keys %age)
 {
     $width = int(400 * $age{$age}/$maxage);
     $ret .= "<TR><TD ALIGN=RIGHT><B>$age</B></TD><TD>$age{$age}</TD><TD><IMG SRC=\"/img/bluedot.gif\" HEIGHT=10 WIDTH=$width></TD></TR>\n";
     $lastage = $_;
 }
 $ret .= "</TABLE>\n";


 # clients
 $ret .= "(=H1 Client Usage H1=)(=P How people update their journals (over the last 30 days): P=)\n";
 $ret .= "<P><TABLE CELLPADDING=3>\n";

 ### sum up clients over different versions
 foreach my $c (keys %{$stat{'client'}}) {
     next unless ($c =~ /^(.+?)\//);
     $stat{'clientname'}->{$1} += $stat{'client'}->{$c};
 }
 foreach my $cn (sort { $stat{'clientname'}->{$b} <=> $stat{'clientname'}->{$a} } keys %{$stat{'clientname'}})
 {
     $ret .= "<TR VALIGN=TOP><TD><FONT SIZE=+1><B>$stat{'clientname'}->{$cn}</B></FONT></TD><TD><B>$cn</B><BR>\n";
     $ret .= "<FONT SIZE=-1>\n";
     foreach my $c (sort grep { /^\Q$cn\E\// } keys %{$stat{'client'}}) {
         my $count = $stat{'client'}->{$c};
         $c =~ s/^\Q$cn\E\///;
         $ret .= "$c ($count), ";
     }
     chop $ret; chop $ret;  # remove trailing ", "
     $ret .= "</FONT>\n";
     $ret .= "</TD></TR>\n";
 }
 $ret .= "</TABLE>\n";

 ### graphs!

 $ret .= "(=H1 Pretty Graphs! H1=)(=P These are the most fun, aren't they? P=)";
 
 $ret .= "(=H2 Journal entries -- last 60 days H2=)(=P How often do people post over the last 60 days? P=)";
 $ret .= "<P><CENTER><IMG SRC=\"stats/postsbyday.png\" WIDTH=520 HEIGHT=350></CENTER>";

 $ret .= "(=H2 Journal entries -- by week, ever H2=)(=P What's the week-to-week trend? P=)";
 $ret .= "<P><CENTER><IMG SRC=\"stats/postsbyweek.png\" WIDTH=520 HEIGHT=350></CENTER>";

 $ret .= "(=H2 New accounts -- last 60 days H2=)(=P How fast are we growing? P=)";
 $ret .= "<P><CENTER><IMG SRC=\"stats/newbyday.png\" WIDTH=520 HEIGHT=350></CENTER>";

 return $ret;

_CODE=)

<=BODY
PAGE=)(=_C <LJDEP>
link: htdocs/stats/, htdocs/support/faqbrowse.bml
img: htdocs/img/bluedot.gif, htdocs/stats/postsbyday.png, htdocs/stats/postsbyweek.png, htdocs/stats/newbyday.png
</LJDEP> _C=)