Support #2568
closedStatistics Needed - Queens College Commons Usage
0%
Description
- How many people from QC are users of Commons and consist of what percent compared to all the users?
- How many people from QC own blogs on the Commons and at what percentile compared to all the blogs on Commons?
- How many groups are from QC and at what percentile compared to all the groups on Commons?
Can someone run a query on the database to get this info? Thanks.
Updated by Boone Gorges almost 11 years ago
- Status changed from New to Reporter Feedback
- Assignee set to Boone Gorges
- Target version set to Not tracked
I can gather some info, Scott. The first two items shouldn't be a problem.
How many groups are from QC and at what percentile compared to all the groups on Commons?
This is trickier. We don't have any structured data that says whether a group is "from QC". I can determine whether a group contains users that are from QC, or whether a group was created by a QC user, or whether it contains the words "Queens College" in the title, etc. Could you check with Rowena to get a sense of exactly what she's hoping to learn from this number?
Updated by scott voth almost 11 years ago
I've asked Rowena to join the conversation here and clarify her needs. Thanks Boone!
Updated by Rowena Li almost 11 years ago
Thanks. Boone and Scott.
I think I would say to get data as long as it satisfies either of these:
- a group created by a QC user or
- the group's organizer is a QC member or
- it contains the words "Queens College: in the title
And also the percentile of the whole. Thanks.
Rowena
Updated by Boone Gorges almost 11 years ago
Okey dokey. I'm going to record my sql queries here for future reference. You can ignore them if you wish.
How many people from QC are users of Commons and consist of what percent compared to all the users?
Total active users on the Commons: 4073 (select count(user_id) from wp_usermeta where meta_key = 'last_activity';)
Total active users who list Queens College as an affiliated school: (select count(user_id) from wp_bp_xprofile_data where field_id = 2 and value like '%Queens College%';)
How many people from QC own blogs on the Commons and at what percentile compared to all the blogs on Commons?
Total active blogs on the Commons that are associated with users: 1299 (select count(1) from (select distinct blog_id from wp_bp_user_blogs) temp;)
Total active blogs on the Commons that have users from QC as an administrator: 99 (select distinct user_id from wp_usermeta where meta_key like '%_capabilities' and user_id in (select user_id from wp_bp_xprofile_data where field_id = 2 and value like '%Queens College%') and meta_value like '%administrator%';)
How many groups are from QC and at what percentile compared to all the groups on Commons?
Total groups on the Commons: 622 (select count(*) from wp_bp_groups;)
Total groups on the Commons that either: (a) have a creator who is from QC, (b) have "Queens College" in the group name, or (c) have "QC" in the group name: 36 (select count(*) from wp_bp_groups where ( creator_id in (select user_id from wp_bp_xprofile_data where field_id = 2 and value like '%Queens College%') OR name like '%Queens College%' OR name like '%QC%' );)
Let me know if that's helpful, Rowena.
Updated by Rowena Li almost 11 years ago
Thanks, Boone. But no users listed QC as an affiliated school for the first query? That seems strange, doesn't it? Could you please check again? Thanks a lot!
So the data read as (see if I am reading it right):
1. 0 out of 4073
2. 99 out of 1299
3. 36 out of 622
Updated by Boone Gorges almost 11 years ago
Sorry, Rowena, I guess I forgot to copy that number. It's 298.
Updated by Boone Gorges about 8 years ago
- Status changed from Reporter Feedback to Resolved