1533326 - Generate sample of active bugzilla users to contact for community survey

Status: RESOLVED FIXED

(bugzilla.mozilla.org :: Administration, task, P1)

Description

[Hermina]

Hello,

We (Open Innovation) intend to survey again our Bugzilla community, with the same survey we sent them in 2017 (very slightly modified). For this we would need an updated list of bugzilla users (we still have the old list as well). As population, we are interested only in those people with at least 5 actions in Bugzilla. Please let me know if you could help with that.

Thanks a lot,
Hermina

Comment 1

[Hermina]

Hey Emma,

I cc'ed Kim and also Michael Feldman, to review from Legal team. Is there anything else I can do for now?

Thanks!

Comment 2

[Michael Feldman (use NI)]

Is there a way of contacting people in-product, or would it be an email?

Comment 3

[Hermina]

Hi Michael,

I don't know how we can contact them in Bugzilla in bulk - don't know if Bugzilla offers this functionality - in 2017, based on the rules we've set up (at least 5 contributions to Bugzilla) we surveyed approx 5000 people. We sent them an email, using Salesforce.

Hermina

Comment 4

[Emma Humphries ☕️🎸🧞‍♀️✨ (she/they) [:emceeaich] (Pacific Time) use needinfo]

Sending an email would probably be best. We have the ability to display a message to users, that that's to all users. We can talk about if we need the ability to control who sees what message like snippets.

Getting a list of users would be a data request we'd need reviewed.

What are the the types of actions in Bugzilla that would count towards the five actions?

Comment 5

[Hermina]

Well, I'd go about all people who have 5 or more actions on bugzilla, independent of what these actions were. Otherwise, it is hard to define the 'right' actions. For some projects filling a bug is the only action they need to do, for some others maybe I need to look at commits, pull requests, bug triaging etc. As I think every project has different business rules for defining “a real bugzilla contribution”, I would just go for any bugzilla action.
Does this sound good?

Comment 6

[Hermina]

Hi Michael,

Would this solution be OK with you - contacting people via email? We need your review and approval to move forward with this. Thanks so much.

Comment 7

[Hermina]

Hi Emma,

As I got confirmation from Michael a couple of days ago that this has been approved by Legal - bug 1535481, could you please let me know what's still needed to get access to the bugzilla users list? I am concerned with the timeline, we were hoping to get the emails out to users this week in order to have time to gather results, analyze them etc. Please let me know if I can help with anything to unblock this.

Thanks!
Hermina

Comment 8

[Emma Humphries ☕️🎸🧞‍♀️✨ (she/they) [:emceeaich] (Pacific Time) use needinfo]

Kim, my apologies, I was sitting on this and I think you're the right person to work with the BMO team about getting the OI team an extract. Please let me know what I can do to help with this.

Comment 9

[Dylan Hardison [:dylan] (he/him)]

Generating this data now. I'll share out of band.

Comment 10

[Hermina]

Hi Dylan,

I was wondering if you were able to look into the above?

Thanks,
Hermina

Comment 11

[Dylan Hardison [:dylan] (he/him)]

Ok, so here's the query I'm using (mostly for my future reference).
It includes the counts of comments, reported bugs, and needinfos.
In addition, it includes if the user is using secure mail, 2fa, and if the user has a password.
Passwordless users are ones that use github. Taken together these are good signals of involvement.
I'm also limiting the results to people seen in this year 2019.

It is very difficult to filter out employees -- I'm hoping this can be done as a second step with the data provided.

The data is in a google sheet I shared, which is accessible to any @mozilla that has the link.

SELECT 
    userid AS id,
    realname AS display_name,
    login_name AS email,
    IF(public_key != '', 'yes', 'no') AS use_securemail,
    IF(cryptpassword != ''
            AND cryptpassword != '*',
        'yes',
        'no') AS has_password,
    IF(mfa = '', 'no', 'yes') AS uses_2fa,
    (needinfo_request_count + comment_count + COUNT(bugs.bug_id)) AS total_activity,
    comment_count,
    needinfo_request_count,
    COUNT(bugs.bug_id) AS bug_count,
    p.creation_ts AS account_creation,
    last_seen_date,
    last_activity_ts AS last_activity
FROM
    profiles AS p
        JOIN
    bugs ON reporter = userid
WHERE
    last_seen_date IS NOT NULL
        AND YEAR(last_seen_date) = 2019
        AND disabledtext = ''
        AND login_name NOT LIKE '%.bugs'
        AND login_name NOT LIKE '%.tld'
GROUP BY p.userid
HAVING (needinfo_request_count + comment_count + bug_count) >= 5
ORDER BY total_activity DESC , last_seen_date ASC
LIMIT 100000