Community Metrics in OpenACS

What can we provide now?

* WIKI

new pages this week
average: new pages per month
page edits this week
average: page edits per month
average: editors per page

* FORUMS

new threads this week
posts this week
average: new threads per month
average: posts per month
average: forum notification subscriptions per month
ratio: posts per thread (monthly?)

example queries:

hub=# select to_char(r.publish_date, 'YYYY-MM') as month, count(*) from cr_items i join cr_revisions r on (i.item_id = r.item_id) where parent_id = '2239905' group by month order by month desc;
  month  | count
---------+-------
 2009-10 |   123
 2009-09 |   394
 2009-08 |   410
 2009-07 |   671
 2009-06 |   158
 2009-05 |     4
 2009-03 |     8
 2009-01 |     4
 2008-12 |    56
(9 rows)

hub=# select to_char(o.creation_date, 'YYYY-MM') as month, count(*) from cr_items i join acs_objects o on (i.item_id = o.object_id) where parent_id = '2239905' group by month order by month desc;
  month  | count
---------+-------
 2009-10 |     2
 2009-09 |    31
 2009-08 |    38
 2009-07 |    71
 2009-06 |    39
 2009-05 |     2
 2009-03 |     4
 2009-01 |     1
 2008-12 |    12
(9 rows)

hub=# select max(count) from (select count(distinct creation_user) as count from cr_items i join cr_revisions r on (i.item_id = r.item_id) join acs_object\
s o on (o.context_id = r.item_id) where parent_id = '2239905' group by i.item_id) a;
 max
-----
   7
(1 row)

hub=# select avg(count) from (select count(distinct creation_user) as count from cr_items i join cr_revisions r on (i.item_id = r.item_id) join acs_object\
s o on (o.context_id = r.item_id) where parent_id = '2239905' group by i.item_id) a;
        avg
--------------------
 1.4900000000000000
(1 row)

hub=# select count(distinct creation_user), i.item_id from cr_items i join cr_revisions r on (i.item_id = r.item_id) join acs_objects o on (o.context_id =\
 r.item_id) where parent_id = '2239905' group by i.item_id;
 count | item_id
-------+---------
     2 | 2239907
     1 | 2240099
     1 | 2240101
     2 | 2240276
     1 | 2240751
     1 | 2240763
     1 | 2240769
     7 | 2240772
     1 | 2248445
     1 | 2249069
     1 | 2249119
     1 | 2249149
...
     2 | 2714595
     1 | 2716420
     1 | 2721530
     1 | 2722191
     2 | 2724344
     1 | 2731842
     1 | 2752093
(200 rows)

What could we provide in the future?

average: # new/departed group members per day/month/year
average: visit length of group members
2 day answer rate for new threads
average: uploads/downloads per month
average: first answer time for threads
ratio: page view per post (lurk)
etc.