Do an analysis of volunteer applications in limbo for active and inactive clubs

[conoramurphy-zz]

Business Goal We are suspicious that a high number of volunteers are applying to join a club but never being accepted. This analysis task is to see how much of this affects us.

Analysis Tasks

• [x] See how many volunteers have 1 or more pending application
• [x] See top countries
• [x] See if it's grouped by clubs (are there clubs with lots of pendings?)
• [x] See if it happens for clubs with and without events
• [x] Break down the above by active clubs and inactive clubs
• [x] Anything else interesting?

[Wardormeur]

• numbers of dojos with requests where the user is not part of the Dojo : 610

  SELECT count(*), verified_at, deleted, stage, alpha2, COALESCE(event_nb, 0) as event_nb, MIN(timestamp), MAX(timestamp) FROM (
      SELECT dojoId, event_nb, ud.id, d.verified_at, d.deleted, d.stage, d.alpha2, timestamp FROM (
              SELECT unnest(join_requests)->>'dojoId' as dojoId, unnest(join_requests)->>'timestamp' as timestamp, id FROM sys_user u) as q 
      LEFT JOIN cd_usersdojos ud ON q.id = ud.user_id AND ud.dojo_id = q.dojoId 
      JOIN cd_dojos d ON q.dojoId = d.id 
      LEFT JOIN ( SELECT dojo_id, count(*) as event_nb FROM cd_events GROUP BY dojo_id) events ON events.dojo_id = d.id       
      WHERE ud.id IS NULL) as gq 
  GROUP BY dojoId, verified_at, deleted, stage, alpha2, event_nb;

Members

SELECT MIN(timestamp), MAX(timestamp), count(*) FROM (
        SELECT q.id as user_id, timestamp FROM (
                SELECT unnest(join_requests)->>'dojoId' as dojoId, unnest(join_requests)->>'timestamp' as timestamp, id FROM sys_user u) as q
        LEFT JOIN cd_usersdojos ud ON q.id = ud.user_id AND ud.dojo_id = q.dojoId
        WHERE ud.id IS NULL 
) as sq 
GROUP BY user_id;

See document for anonymised data https://docs.google.com/spreadsheets/d/1BLe5RqEXCtsgfPMBDqkdQrEVQPYAzvrE2QQLHsHA0_w/edit#gid=0

[Wardormeur]

Report: https://datastudio.google.com/open/1dUMpxkhLBLd3qpXmm7PpifAJYH0mO9__

More than 600 dojos with unsolved invitations for a total of 1400 unanswered requests 62% are for dojos in UK/GB/USA, and they represent ~33% of all Dojos. Inactive Dojos counts for 1/3 of all Dojos. Members generally do 1 requests (66%). Dojos with events receives slightly more requests than those without.