1067503 - Check how many active contributors have legacy only vouches

Status: RESOLVED FIXED

(Community Building :: Systems and Data, task)

Description

[Pierros Papadeas [:pierros]]

In two weeks, mozillians.org team will be unvouching almost 3000 mozillians.org profiles having legacy only (and not new) vouches.

We need to cross-check the emails that will be unvouched with emails we consider currently active according to Baloo data. (counting towards active counts)

In mozillians.org the matching emails can be obtained (and will be edited to remove legacy vouch) using the following query:

Selecting the vouched=true from profile table we need to exclude the IDs that have not empty descriptions in user_vouch table.

Expected delivery of this bug would be the count of emails that fit both criteria above.

Thanks!

Comment 1

[Sheeri Cabral [:sheeri]]

The overlap is 842 emails. 

mysql> select distinct email from auth_user inner join profile on (auth_user.id=profile.user_id) inner join users_vouch on (vouchee_id=user_id) where description='';


mysql> select distinct contributor_key from contributor_active WHERE c_date='2014-09-22';
+---------------------------------+
| count(distinct contributor_key) |
+---------------------------------+
|                            6651 |
+---------------------------------+
1 row in set (0.02 sec)

Comment 2

[Sheeri Cabral [:sheeri]]

Do you want a list of the e-mails?

Comment 3

[William Reynolds [:williamr]]

(In reply to Sheeri Cabral [:sheeri] from comment #1)
> The overlap is 842 emails. 

We currently have 2645 profiles that only have a legacy vouch. Based on Sheeri's query, 32% of those people are active contributors but do not have a new-style vouch with a description yet. We should work on getting those people a new vouch.

Yes, it would be helpful to get a list of the emails. I can check if those people are clustered around certain functional areas or geographies, and we can reach out to groups to get these people vouched.

Off-topic idea for how to get these people vouched. All vouched Mozillians to view a listing of all users who only have a legacy vouch, similar to how we have a search filter to include non-vouched users: https://mozillians.org/search/?q=&limit=&include_non_vouched=on

Thanks!

Comment 4

[Pierros Papadeas [:pierros]]

@Sheeri, please send the list. Thanks!
@William, lets discuss this today to decide on best course of action.

Comment 5

[Sheeri Cabral [:sheeri]]

Sent the list to pierros.

Comment 6

[Pierros Papadeas [:pierros]]

@Tasos and @Nemo, can you check the query please? The list that Sheeri supplied to me seem to include vouched people (with the new system) (including ourselves here :) ) There is obviously something wrong.

Comment 7

[Sheeri Cabral [:sheeri]]

Pierros - you asked me to find where vouched=true with no description, so everyone on the list is vouched.

Comment 8

[John Giannelos [:nemo-yiannis]]

Hey :sheeri!

In comment #0 what :pierros is asking is the following:

* Select all the vouched mozillians (vouched=True).
* From the previous selection exclude those users that have at least one vouch with non-empty description.

From what I understand the query in comment #1 does the following:

* Selects all the users that have at least one vouch with empty description.

This selection also includes users that might also have vouches with non-empty descriptions.

Please correct me if i am wrong regarding your query and let me know if you need any extra info about our DB schema.

Comment 9

[Sheeri Cabral [:sheeri]]

Ah! I didn't realize that users had multiple vouches. We need to change query 1 in comment 1.

Comment 10

[Sheeri Cabral [:sheeri]]

New query #1:
select distinct email 
from auth_user inner join profile on (auth_user.id=profile.user_id) 
inner join users_vouch on (vouchee_id=user_id) where email not in
   (select distinct email 
    from auth_user inner join profile on (auth_user.id=profile.user_id) 
    inner join users_vouch on (vouchee_id=user_id) 
    where description!='');

new overlap is 343 emails.

Sent attachment to pierros and williamr with the e-mails.

Comment 11

[Sheeri Cabral [:sheeri]]

Assuming resolved....if this is not accurate, please re-open.