What % of invitation list editors joined the event (event registration only)?

Is this an accurate measure for effectiveness since not everyone on the list may be invited? But it may be difficult to measure the percentage of invited editors who register since we are not tracking what editors out of all the editors on the list were sent invitations. Perhaps we can track if an editor in the invitation list received a talk page message from the organizer (but i guess it wouldn't be enough)
@ifried @Iflorez

@gonyeahialam We marked this under "Tool usage" rather than "Effectiveness" because we do not think it is an accurate measure of effectiveness. Note that we do not have a category for "Effectiveness" in the measurement plan right now. We can probably only begin measuring effectiveness when we know who the organizer invited -- and this will only come when we implement support for messaging, which is a post-MVP feature. In the meantime, I don't think it would be worth the effort to try to analyze talk page messages, especially since many organizers may choose to invite editors via wikimail.

So, with that being said, do you think this metric is useful for helping measure tool usage with the data we will have available for the MVP, or no?

@cmelo Hello! We discussed in a team meeting that we should already be able to track tool usage data when the MVP is released, but I wanted to confirm in this ticket. Thanks in advance!

In T365292#9918970, @ifried wrote:

@cmelo Hello! We discussed in a team meeting that we should already be able to track tool usage data when the MVP is released, but I wanted to confirm in this ticket. Thanks in advance!

Hi @ifried, yes it is.
Also, to make it easy, I have created SQL queries to get these reports, it may be useful for @Iflorez to build the reports:

• 1. How many Invitation Lists are generated per month? (and break down by wiki)

SELECT 
    DATE_FORMAT(STR_TO_DATE(ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') AS month,
    ceil_wiki,
    COUNT(*) AS invitation_lists_count
FROM 
    ce_invitation_lists
GROUP BY 
    month, ceil_wiki
ORDER BY 
    month, ceil_wiki;

• 2. How many organizers have generated more than one Invitation List? (Calculate in months 1-3 only as a gut check)

SELECT 
    ceil_user_id,
    COUNT(*) AS lists_count
FROM 
    ce_invitation_lists
WHERE 
    DATE_FORMAT(STR_TO_DATE(ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') BETWEEN '2024-01' and '2024-12'
GROUP BY 
    ceil_user_id
HAVING 
    lists_count > 1;

• 2.1 Count how many ce_invitation_lists have created more than one list by ce_user_id per wiki (ceil_wiki)

SELECT 
    ceil_user_id,
    ceil_wiki,
    COUNT(*) AS lists_count
FROM 
    ce_invitation_lists
WHERE 
    DATE_FORMAT(STR_TO_DATE(ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') BETWEEN '2024-01' and '2024-12'
GROUP BY 
    ceil_user_id, ceil_wiki
HAVING 
    lists_count > 1;

• 3. What % of invitation list editors joined the event (event registration only)?
  • Note, this may not be 100% real because we don't know if the user joins the event because was invited or not
  • I mean a user may have joined the event without been invited, this query represents users that register for an event and are also in an invitation list linked to an event ID

SELECT 
    COUNT(DISTINCT ceilu.ceilu_user_id) AS total_invitation_list_editors,
    COUNT(DISTINCT ceilu.ceilu_user_id) / COUNT(DISTINCT p.cep_user_id) * 100 AS percentage_joined
FROM 
    ce_invitation_list_users ceilu
JOIN 
    ce_invitation_lists ceil ON ceilu.ceilu_ceil_id = ceil.ceil_id
JOIN 
    ce_participants p ON p.cep_user_id = ceilu.ceilu_user_id AND p.cep_event_id = ceil.ceil_event_id
WHERE 
    DATE_FORMAT(STR_TO_DATE(ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') BETWEEN '2024-01' and '2024-12';

• 4. Top 50 users that show up in event lists per wiki

SELECT 
    ceilu.ceilu_user_id,
    ceil.ceil_wiki,
    COUNT(*) AS event_list_count
FROM 
    ce_invitation_list_users ceilu
JOIN 
    ce_invitation_lists ceil ON ceilu.ceilu_ceil_id = ceil.ceil_id
WHERE   
    DATE_FORMAT(STR_TO_DATE(ceil.ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') BETWEEN '2024-01' AND '2024-12'
GROUP BY 
    ceilu.ceilu_user_id, ceil.ceil_wiki
ORDER BY 
    event_list_count DESC
LIMIT 50;

• 5. How many users in an invitation list (invitation_list_users) register for the event on (ce_participants) linking cep_event_id with ceil_event_id

SELECT 
    ceilu.ceilu_user_id,
    COUNT(*) AS registration_count
FROM 
    ce_invitation_list_users ceilu
JOIN 
    ce_invitation_lists ceil ON ceilu.ceilu_ceil_id = ceil.ceil_id
JOIN 
    ce_participants p ON p.cep_user_id = ceilu.ceilu_user_id AND p.cep_event_id = ceil.ceil_event_id
WHERE 
    DATE_FORMAT(STR_TO_DATE(ceil_created_at, '%Y%m%d%H%i%s'), '%Y-%m') BETWEEN '2024-01' and '2024-12'
GROUP BY 
    ceilu.ceilu_user_id;

Hello, @Sadads! I have discussed the 'other' request on the top 50 contributors who appear in invitation lists with @Iflorez. Here are the follow-up questions:

1. Global or per wiki: If we start with enabling Invitation Lists on Igbo and Swahili Wikipedia, would it be useful to have the top 50 invited contributors per wiki, or would we want it to be global (i.e., both wikis) since perhaps the 50 number would be quite high to look into for smaller wikis, such as Igbo? What do you think? Are you interested in a smaller list?
2. Top 50 vs 10 or 20: We wanted to check if 50 is truly the number you find most useful, or would a smaller number, such as top 10 or 20, provide what you need? Irene said that top 10 tells you the peak and gives you the sense of the difference of the peak and those who follow. Meanwhile, 50 is more exhaustive, but it will demand more from those who are reviewing (and may give you similar info).

Update: the notebook and related code for reporting is now drafted. I will touch base with Ilana during the first week of August. If the feature is live, I will proceed with testing and then pulling data.

Update: The feature is live, but we do not know if there is any usage yet.

@Udehb-WMF: Do you know if anyone is using Invitation Lists on Igbo or Swahili Wikipedia yet?

Update: the notebook and related code for reporting is now drafted.

@Iflorez: Is the notebook (without data outputs) in a repo somewhere? Can you please upload it if you have not yet.

test_query = '''
SELECT * 
FROM ce_invitation_lists 
'''

test_results = mariadb.run(test_query, 'wikishared')
test_results

I see six uses of the tool, all on igwiki in September. Five have "test1" in the ceil_name field. One is called "List2"

Queries available on GitLab

Minimum usage needed to make this an actionable task:
If we are seeing ~10 invites created per month and see at least 30 invites created in total, the analysis requested in this ticket can be processed, providing useful data for the team that can be used in planning and reporting. Those could be a reasonable starting point for the team.

Thanks for this update, @mpopov & @Iflorez. Do you think it is possible to also track the number of organizers who use Invitation Lists per month? I ask because organizers can generate multiple invitation lists, so if we see growth/decrease in usage, that information could be complemented by the # of organizers to see if fluctuations are perhaps due to the usage of a few users or if they do indicate a larger trend related to an increase/decrease in organizer adoption overall.

In September there were two users. Could that be added to the list when the ticket is reopened?

Yup, I can ask later, @Iflorez. But will this ticket be re-opened? I am not clear on the process. It seems like this ticket is being declined.

@ifried update: I'm adding number of organizers who use Invitation Lists per month to the metrics output.
thank you

Fantastic; thank you so much, @Iflorez!