297 lines
12 KiB
Plaintext
Executable File
297 lines
12 KiB
Plaintext
Executable File
<?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/stats.txt">here</a>.
|
|
p?>
|
|
<?hr?>
|
|
|
|
<?_code
|
|
|
|
use strict;
|
|
|
|
LJ::set_active_crumb('stats');
|
|
|
|
my $dbr = LJ::get_db_reader();
|
|
my $sth;
|
|
|
|
my $now = time();
|
|
my %stat = ();
|
|
|
|
$sth = $dbr->prepare("SELECT statcat, statkey, statval FROM stats WHERE statcat IN ('userinfo', 'client', 'age', 'gender', 'account', 'size')");
|
|
$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'};
|
|
}
|
|
|
|
my $total = $stat{'userinfo'}->{'total'}+0;
|
|
my $usedever = $stat{'userinfo'}->{'updated'}+0;
|
|
my $used30 = $stat{'userinfo'}->{'updated_last30'}+0;
|
|
my $used7 = $stat{'userinfo'}->{'updated_last7'}+0;
|
|
my $usedlastday = $stat{'userinfo'}->{'updated_last1'}+0;
|
|
my $allow_getljnews = $stat{'userinfo'}->{'allow_getljnews'}+0;
|
|
my $active = $stat{'size'}->{'accounts_active'}+0;
|
|
|
|
my $ret = "";
|
|
$ret .= "<?h1 Users h1?>\n";
|
|
$ret .= "<?p How many users, and how many of those are active? p?> <ul>";
|
|
$ret .= "<li><b>Total accounts: </b> $total</li>\n";
|
|
$ret .= "<li><b>... active in some way: </b> $active</li>\n" if $active;
|
|
$ret .= "<li><b>... that have ever updated: </b> $usedever</li>\n";
|
|
$ret .= "<li><b>... updating in last 30 days: </b> $used30</li>\n";
|
|
$ret .= "<li><b>... updating in last 7 days: </b> $used7</li>\n";
|
|
$ret .= "<li><b>... updating in past 24 hours: </b> $usedlastday</li>\n";
|
|
$ret .= "</ul> \n";
|
|
|
|
$ret .= "<?h1 Gender h1?>\n";
|
|
$ret .= "<?p Are males or females more likely to maintain journals? p?><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)) . "%)</li>";
|
|
$ret .= "<li><b>Female: </b> $female (" . sprintf("%0.1f", $female*100/($tot||1)) . "%)</li>";
|
|
}
|
|
$ret .= "<li><b>Unspecified: </b> " . ($stat{'gender'}->{'U'}+0) . "</li>";
|
|
$ret .= "</ul>\n";
|
|
|
|
LJ::run_hook('statspage', {
|
|
stat => \%stat,
|
|
ret => \$ret,
|
|
});
|
|
|
|
unless ($LJ::DISABLED{'stats-recentupdates'})
|
|
{
|
|
$ret .= "<?h1 Recent Updates h1?>\n";
|
|
$ret .= "<?p The following are the 50 most recently updated journals: p?>\n<ul>\n";
|
|
$sth = $dbr->prepare("SELECT u.user, u.name, uu.timeupdate, u.statusvis FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate > DATE_SUB(NOW(), INTERVAL 2 DAY) AND uu.timecreate < DATE_SUB(NOW(), INTERVAL 5 DAY) ORDER BY uu.timeupdate DESC LIMIT 50");
|
|
$sth->execute;
|
|
my $ct;
|
|
while (my ($iuser, $iname, $itime, $status) = $sth->fetchrow_array) {
|
|
my $tmp = "<a href='/users/$iuser/'><?_eh $iname _eh?></a>, $itime";
|
|
$tmp = "<strike>$tmp</strike>" if $status =~ /[DXS]/;
|
|
$tmp = "<li>$tmp</li>\n";
|
|
$ret .= $tmp;
|
|
$ct++;
|
|
}
|
|
$ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
$ret .= "</ul>\n";
|
|
}
|
|
|
|
unless ($LJ::DISABLED{'stats-newjournals'})
|
|
{
|
|
$ret .= "<?h1 New Journals h1?>\n";
|
|
$ret .= "<?p The following are the non-empty journals, created for last 5 days. p?>\n<ul>\n";
|
|
$ret .= "<noindex>\n"; # L.P.
|
|
$sth = $dbr->prepare("SELECT u.userid, u.user, u.name, uu.timecreate, u.statusvis FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate > DATE_SUB(NOW(), INTERVAL 5 DAY) AND uu.timecreate > DATE_SUB(NOW(), INTERVAL 5 DAY) ORDER BY uu.timecreate DESC");
|
|
$sth->execute;
|
|
my $ct;
|
|
while (my ($iuserid, $iuser, $iname, $itime, $status) = $sth->fetchrow_array) {
|
|
my $tmp = "<a href='/users/$iuser/' rel='nofollow'><?_eh $iname _eh?></a>, $itime";
|
|
$tmp = "<strike>$tmp</strike>" if $status =~ /[DXS]/;
|
|
# determine the number of posted comments
|
|
my $com_post;
|
|
my $mysth = $dbr->prepare("SELECT COUNT(*) FROM talkleft " .
|
|
"WHERE userid=?");
|
|
$mysth->execute($iuserid);
|
|
($com_post)=$mysth->fetchrow_array;
|
|
# number of posted comments is found
|
|
if ($com_post >0) { $tmp= "$tmp; <b>$com_post</b> comments posted.";}
|
|
$tmp = "<li>$tmp</li>\n";
|
|
$ret .= $tmp;
|
|
$ct++;
|
|
}
|
|
$ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
$ret .= "</noindex>\n</ul>\n";
|
|
}
|
|
|
|
unless ($LJ::DISABLED{'stats-newjournals'})
|
|
{
|
|
$ret .= "<?h1 New Empty Journals h1?>\n";
|
|
$ret .= "<?p The following are the most recently created empty journals for last 20 days: p?>\n<ul>\n";
|
|
$ret .= "<noindex>\n"; # L.P.
|
|
$sth = $dbr->prepare("SELECT u.userid, u.user, u.name, uu.timecreate, u.statusvis FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate IS NULL AND uu.timecreate > DATE_SUB(NOW(), INTERVAL 20 DAY) ORDER BY uu.timecreate DESC");
|
|
$sth->execute;
|
|
my $ct;
|
|
while (my ($iuserid, $iuser, $iname, $itime, $status) = $sth->fetchrow_array) {
|
|
if ($iuser !~ /^(imp|ext)_.*/) {
|
|
# determine the number of posted comments
|
|
my $com_post;
|
|
my $mysth = $dbr->prepare("SELECT COUNT(*) FROM talkleft " .
|
|
"WHERE userid=?");
|
|
$mysth->execute($iuserid);
|
|
($com_post)=$mysth->fetchrow_array;
|
|
# number of posted comments is found
|
|
my $tmp = "<a href='/userinfo.bml?user=$iuser&mode=full'><?_eh $iname _eh?></a>, $itime";
|
|
$tmp = "<strike>$tmp</strike>" if $status =~ /[DXS]/;
|
|
if ($com_post >0) { $tmp= "$tmp; <b>$com_post</b> comments posted.";}
|
|
$tmp = "<li>$tmp</li>\n";
|
|
$ret .= $tmp;
|
|
$ct++;
|
|
}
|
|
}
|
|
$ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
$ret .= "</noindex>\n</ul>\n";
|
|
}
|
|
|
|
unless ($LJ::DISABLED{'stats-newjournals'})
|
|
{
|
|
$ret .= "<?h1 New OpenID users h1?>\n";
|
|
$ret .= "<?p The following are the most recently created OpenID users for last 20 days: p?>\n<ul>\n";
|
|
$ret .= "<noindex>\n"; # L.P.
|
|
$sth = $dbr->prepare("SELECT u.userid, u.user, u.name, uu.timecreate, u.statusvis FROM user u, userusage uu WHERE u.userid=uu.userid AND uu.timeupdate IS NULL AND uu.timecreate > DATE_SUB(NOW(), INTERVAL 20 DAY) ORDER BY uu.timecreate DESC");
|
|
$sth->execute;
|
|
my $ct;
|
|
while (my ($iuserid, $iuser, $iname, $itime, $status) = $sth->fetchrow_array) {
|
|
if ($iuser =~ /^(ext)_.*/) {
|
|
## determine the number of posted comments
|
|
my $com_post;
|
|
my $mysth = $dbr->prepare("SELECT COUNT(*) FROM talkleft " .
|
|
"WHERE userid=?");
|
|
$mysth->execute($iuserid);
|
|
($com_post)=$mysth->fetchrow_array;
|
|
## number of posted comments is found
|
|
my $tmp = "<a href='/userinfo.bml?user=$iuser&mode=full'><?_eh $iname _eh?></a>, $itime";
|
|
$tmp = "<strike>$tmp</strike>" if $status =~ /[DXS]/;
|
|
if ($com_post >0) { $tmp= "$tmp; <b>$com_post</b> comments posted.";}
|
|
$tmp = "<li>$tmp</li>\n";
|
|
$ret .= $tmp;
|
|
$ct++;
|
|
}
|
|
}
|
|
$ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
$ret .= "</noindex>\n</ul>\n";
|
|
}
|
|
|
|
$ret .= "<?h1 Demographics h1?>\n";
|
|
{
|
|
$ret .= "<?p The following are the 15 most popular countries $LJ::SITENAMESHORT is used in: p?><ul>\n";
|
|
my $ct;
|
|
foreach my $key (sort { $stat{'country'}->{$b} <=> $stat{'country'}->{$a} }
|
|
keys %{$stat{'country'}})
|
|
{
|
|
$ret .= "<li><b>$key</b> - $stat{'country'}->{$key}</li>\n";
|
|
$ct++;
|
|
}
|
|
$ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
$ret .= "</ul>\n";
|
|
}
|
|
|
|
# Removed by popular request - M.V.
|
|
# {
|
|
# $ret .= "The following are the 15 most popular U.S. states $LJ::SITENAMESHORT is used in: <ul>";
|
|
# my $ct;
|
|
# foreach my $key (sort { $stat{'state'}->{$b} <=> $stat{'state'}->{$a} } keys %{$stat{'state'}})
|
|
# {
|
|
# $ret .= "<li><b>$key</b> - $stat{'state'}->{$key}</li>\n";
|
|
# $ct++;
|
|
# }
|
|
# $ret .= "<li><i>stat not available</i></li>" unless $ct;
|
|
# $ret .= "</ul> \n";
|
|
# }
|
|
|
|
# ages
|
|
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}; }
|
|
}
|
|
my @ages = grep { $_ >= 13 && $_ <= 55 } sort { $a <=> $b } sort keys %age;
|
|
if (@ages) {
|
|
$ret .= "<?h1 Age Distribution h1?><?p The following shows the age distribution " .
|
|
"of $LJ::SITENAMESHORT users: p?>\n";
|
|
$ret .= "<table>\n";
|
|
my $lastage = 0;
|
|
foreach my $age (@ages) {
|
|
my $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 listing is broken - sorry (MV, 07, 09, 2005)
|
|
# clients (if enabled)
|
|
# unless ($LJ::DISABLED{'clientversionlog'}) {
|
|
### sum up clients over different versions
|
|
# foreach my $c (keys %{$stat{'client'}}) {
|
|
# next unless ($c =~ /^(.+?)\//);
|
|
# $stat{'clientname'}->{$1} += $stat{'client'}->{$c};
|
|
# }
|
|
#
|
|
# my $out;
|
|
# foreach my $cn (sort { $stat{'clientname'}->{$b} <=> $stat{'clientname'}->{$a} }
|
|
# keys %{$stat{'clientname'}})
|
|
# {
|
|
# last unless $stat{'clientname'}->{$cn} >= 50;
|
|
# $out .= "<tr valign='top'><td><font size='+1'><b>$stat{'clientname'}->{$cn}</b></font></td>".
|
|
# "<td><b>" . LJ::ehtml($cn) . "</b><br />\n";
|
|
# $out .= "<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\///;
|
|
# $out .= LJ::ehtml($c) . " ($count), ";
|
|
# }
|
|
# chop $ret; chop $ret; # remove trailing ", "
|
|
# $out .= "</font>\n";
|
|
# $out .= "</td></tr>\n";
|
|
# }
|
|
#
|
|
# if ($out) {
|
|
# $ret .= "<?h1 Client Usage h1?><?p How people update their journals (over the last 30 days): p?>\n";
|
|
# $ret .= "<table cellpadding='3'>\n";
|
|
# $ret .= $out;
|
|
# $ret .= "</table>\n";
|
|
# }
|
|
# }
|
|
|
|
|
|
# return early, since the graphs below are pretty much broken now
|
|
# with index changes. FIXME: make alternate means to generate stats
|
|
|
|
$ret .= "<?h2 New accounts -- last 60 days h2?><?p How fast are we growing? p?>";
|
|
$ret .= "<p align='center'><img src=\"/stats/newbyday.png\" width='520' height='350' /></p>";
|
|
|
|
return $ret;
|
|
|
|
### 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 align='center'><img src=\"stats/postsbyday.png\" width='520' height='350' /></p>";
|
|
|
|
$ret .= "<?h2 Journal entries -- by week, ever h2?><?p What's the week-to-week trend? p?>";
|
|
$ret .= "<p align='center'><img src=\"stats/postsbyweek.png\" width='520' height='350' /></p>";
|
|
|
|
|
|
|
|
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?>
|