Project

General

Profile

Support #2568

Statistics Needed - Queens College Commons Usage

Added by scott voth almost 6 years ago. Updated about 3 years ago.

Status:
Resolved
Priority name:
Normal
Assignee:
Category name:
-
Target version:
Start date:
2013-05-14
Due date:
% Done:

0%

Estimated time:

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

#1 Updated by Boone Gorges almost 6 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?

#2 Updated by scott voth almost 6 years ago

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

#3 Updated by Rowena Li almost 6 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

#4 Updated by Boone Gorges almost 6 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.

#5 Updated by Rowena Li almost 6 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

#6 Updated by Boone Gorges almost 6 years ago

Sorry, Rowena, I guess I forgot to copy that number. It's 298.

#7 Updated by Rowena Li almost 6 years ago

Oh... thanks, Boone. You are the best.

#8 Updated by Boone Gorges about 3 years ago

  • Status changed from Reporter Feedback to Resolved

Also available in: Atom PDF