Support #2568: Statistics Needed - Queens College Commons Usage - CUNY Academic Commons - CUNY Graduate Center - Project Tracking System

Custom queries

. 2.3.6 open
2.4.0 open
2.4.0 open (by category)
2.4.0 open (by tracker)
High Priority Issues by User
Inbox (no milestone)
My Assigned Issues (by milestone)
My Watched Issues (by milestone)
Not Tracked - grouped by assignee
Not tracked (milestone-independent)

Actions

Copy link

Support #2568

closed

Statistics Needed - Queens College Commons Usage

Added by scott voth almost 11 years ago. Updated about 8 years ago.

Status:

Resolved

Priority name:

Normal

Assignee:

Boone Gorges

Category name:

Target version:

Not tracked

Start date:

2013-05-14

Due date:

% Done:

Estimated time:

Deployment actions:

Description

Rowena Li is working for Center for Teaching and Learning at QC and we would like to collect some data about Queens College and the Commons. She needs to know:

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.

History
Notes
Property changes

Actions

Copy link

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?

Actions

Copy link

Updated by scott voth almost 11 years ago

I've asked Rowena to join the conversation here and clarify her needs. Thanks Boone!

Actions

Copy link

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

Actions

Copy link

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.

Actions

Copy link

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

Actions

Copy link