Administrative Reporting

[JasonTheAdams]

Overview

SDRT does quite a bit of reporting on volunteers for the purposes of making data-driven decisions, writing grants, and managing volunteers. Presently, reporting is done manually by exporting data, creating spreadsheets, or simply doing math by hand. These methods of reporting are prone to human error and, at the very least, take redundant time.

The goal of this initiative is to discover the most critical metrics and KPI's that SDRT relies on in order to operate. The metrics will first be identified, and then sorted by importance. Once a list is compiled and ordered, development will be done to build exportable reports within the SDRT website's admin, tailored to specifically provide the desired data.

Glossary

Event Any event of any kind which volunteers can RSVP to.

Session Either an Elementary or Middle & High School tutoring event.

Non-Tutoring Event An event which is not a session and (implicitly) is also not an orientation.

Metrics

"Volunteer Categories" refers to the Elementary School and Middle & High School categories for the tutoring events. Volunteer-based metrics will include a total, as well as subtotals for the categories.

Sessions

The following metrics are on the top-most level, spanning across all sessions. All metrics have a total as well as Volunteer Category subtotals. Dates can be applied which narrow or expand the range of the metrics.

	Combined	Elementary School	Middle & High School
Total Sessions	10	4	6
Total Hours	500	200	300
Repeating Volunteers (Total)	32	16	18
Repeating Volunteers (%)	45%	37%	44%
Double-Time Tutors (Total)	8	6	2
Double-Time Tutors (%)	10%	7%	3%

Total Hours The total number of hours volunteers have provided, only for volunteers confirmed to have been there.

Repeating Volunteers Number (or %) of volunteers that are active within the current period when compared to the previous period — if the period is 30 days, then its the volunteers which were active during the past 30 days compared to the 30 days before that.

Double-Time Tutors Number (or %) of volunteers that attended 75% of the sessions within the given period.

Tutoring Sessions

The following metrics are on a per tutoring session basis. Each

Event Name	Event Date	Category	Total Attendees	Attended
Event A	08-13-2022	Elementary	26	19
Event B	08-17-2022	Elementary	26	19
Event C	08-21-2022	Middle & High	26	19

Event Volunteers

Tutor Name	RSVP	Attended
Jason Adams	Yes	Yes

Volunteer

Name	Email	Join Date	First Session Date	First Non-Tutoring Event Date	Elementary Sessions Attended	Middle & High Sessions Attended	Year Active
Jason Adams	jason@example.com	07-30-2018	02-14-2020	05-05-2021	30	10	3

Active Years The number of years the volunteer has been active. This only includes years in which the volunteer attended at least one event.

[dreazee]

Notes from Meeting with Melissa--

Attendance Rate not needed

There would need to be 2 different Event Categories:

1. Sessions a. In WP, Event Category = Tutoring Dates.
2. Non-Tutoring Events b. In WP, Event Category = Non-Tutoring Event only (not Orientation Dates)

Sessions would then be broken down into 2 Volunteer Categories =

1. Elementary
2. Middle & High

Multi-Day Volunteers = qualifies as 75% of Sessions (only, not Non-Tutoring Events) during a given time period

Event Volunteers not needed

Volunteer Table: Name, Email, Registration Date, 1st Session Attended, Total Sessions Attended, 1st Non-Tutoring Event Attended, Non-Tutoring Events Attended, Years Active

[dreazee]

@JasonTheAdams After letting our meeting sink in, I have notes regarding the Volunteer Table--

To confirm for Volunteer Table: Name, Email, Registration Date, 1st Session Attended, Total Sessions Attended, 1st Non-Tutoring Event Attended, Non-Tutoring Events Attended, Years Active -- Could we also have optional columns where we could add multiple columns of RSVP data by specific date ranges? So if the Volunteer data shows that a User is active for 4 years but registered 5 years ago, I can see a breakdown/full history of their full SDRT event activity by inputting different date ranges. Or maybe better to add those in there as fixed columns by default - with date ranges coinciding with our tutoring year (ex: Sept 2021 to Aug 2022) instead of a calendar year.

[JasonTheAdams]

@dreazee I've made the changes we've discussed on our call. For the Event Metric and Events reports, I included Non-Tutoring Events as a way of exporting similar data and not having yet another type of export. For values that don't apply the values are simply empty.

As far as being able to get a list of events that a specific Volunteer attended, that's a good idea, but it wouldn't fit into the Volunteers report. That report is intended to have each row represent a single Volunteer. To generate a report of a single volunteer's events, that would be a report by itself so the whole report represents a single volunteer. Does that sound good and make sense?

[dreazee]

@JasonTheAdams Fantastic, Jason. As per the 2nd item, I realized I did NOT explain my desire correctly. WOW. Sorry about that! I did not mean a FULL history; I meant a snapshot history of their year-by-year tutoring involvement by gross # of sessions. (Ex: Sept 21-Aug 22 = 42; Sept 2021-Aug 2022 = 20) Indeed still with the idea that there is one row per volunteer. Does that make more sense now?

[dreazee]

@JasonTheAdams In addition to the below, spoke with Melissa about the term "Repeating Volunteers" (now labeled above as MVP Volunteers) and about how we needed something catchier... She came up with "Double-Time Tutors". What do you think?

@JasonTheAdams Fantastic, Jason. As per the 2nd item, I realized I did NOT explain my desire correctly. WOW. Sorry about that! I did not mean a FULL history; I meant a snapshot history of their year-by-year tutoring involvement by gross # of sessions. (Ex: Sept 21-Aug 22 = 42; Sept 2021-Aug 2022 = 20) Indeed still with the idea that there is one row per volunteer. Does that make more sense now?

[JasonTheAdams]

Hi @dreazee!

Regarding "Double-Time Tutors" — sounds great! Updated!

Thanks for explaining the year-by-year tutoring total for the Volunteers report. I think I'm leaning towards the "fixed" option you mentioned, where we have a column that corresponds with each school year.

[JasonTheAdams]

Just an update, here. I'm currently putting together some designs for this. I think to start I'm going to keep this simple and make it possible to download a CSV version of the data. It will have some filters to limit the data, but we'll do thinks like graphs and visualizations later. The simple reason being that would increase development time when the same visualizations could pretty easily be made in Excel or Google Sheets.

[JasonTheAdams]

I'm going to start putting the queries here for the reporting as I put them together, then I'll assemble them in the application.

Tutoring Sessions

SELECT
    events.ID,
    events.post_title AS name,
    tm.name AS category,
    rsvpAttendedCount.totalAttend AS totalAttending,
    rsvpAttendedCount.total AS totalAttended,
    startDateMeta.meta_value AS startDate,
    endDateMeta.meta_value AS endDate
FROM
    wp_posts events
    LEFT JOIN wp_postmeta startDateMeta ON events.ID = startDateMeta.post_id
        AND startDateMeta.meta_key = '_EventStartDate'
    LEFT JOIN wp_postmeta endDateMeta ON events.ID = endDateMeta.post_id
        AND endDateMeta.meta_key = '_EventEndDate'
    LEFT JOIN (
        SELECT
            events.ID AS eventId,
            rsvpCounts.attendedRsvps AS total,
            rsvpAttendCounts.attendRsvps AS totalAttend
        FROM
            wp_posts events
            LEFT JOIN (
                SELECT
                    eventMeta.meta_value AS eventId,
                    COUNT(rsvps.ID) AS attendedRsvps
                FROM
                    wp_posts AS rsvps
                    INNER JOIN wp_postmeta AS rsvpMeta ON rsvps.ID = rsvpMeta.post_id
                    LEFT JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
                        AND eventMeta.meta_key = 'event_id'
                WHERE
                    rsvps.post_type = 'rsvp'
                    AND rsvpMeta.meta_key = 'attended'
                    AND rsvpMeta.meta_value = 'Yes'
                GROUP BY
                    eventMeta.meta_value) AS rsvpCounts ON events.ID = rsvpCounts.eventId
            LEFT JOIN (
                SELECT
                    eventMeta.meta_value AS eventId,
                    COUNT(rsvps.ID) AS attendRsvps
                FROM
                    wp_posts AS rsvps
                    INNER JOIN wp_postmeta AS rsvpMeta ON rsvps.ID = rsvpMeta.post_id
                    LEFT JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
                        AND eventMeta.meta_key = 'event_id'
                WHERE
                    rsvps.post_type = 'rsvp'
                    AND rsvpMeta.meta_key = 'attending'
                    AND rsvpMeta.meta_value = 'Yes'
                GROUP BY
                    eventMeta.meta_value) AS rsvpAttendCounts ON events.ID = rsvpAttendCounts.eventId
            WHERE
                events.post_type = 'tribe_events') AS rsvpAttendedCount ON events.ID = rsvpAttendedCount.eventId
    INNER JOIN wp_term_relationships tr ON events.ID = tr.object_id
    INNER JOIN wp_term_taxonomy tx ON tr.term_taxonomy_id = tx.term_taxonomy_id
    INNER JOIN wp_terms tm ON tx.term_id = tm.term_id
WHERE
    events.post_status = 'publish'
    AND events.post_type = 'tribe_events'
    AND tx.taxonomy = 'tribe_events_cat'
    AND tm.term_id IN(17, 18)
HAVING
    startDate >= '2022-01-01 00:00:00'
    AND endDate <= '2022-12-30 23:59:59';

Note:

• Term ids are currently hard-coded and should be dynamic

[JasonTheAdams]

Event Volunteers

SELECT
    rsvps.ID,
    rsvpNameMeta.meta_value AS attendeeName,
    rsvpAttendMeta.meta_value AS rsvp,
    rsvpAttendedMeta.meta_value AS attended
FROM
    wp_posts AS rsvps
    INNER JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
        AND eventMeta.meta_key = 'event_id'
    LEFT JOIN wp_postmeta AS rsvpNameMeta ON rsvps.ID = rsvpNameMeta.post_id
        AND rsvpNameMeta.meta_key = 'volunteer_name'
    LEFT JOIN wp_postmeta AS rsvpAttendMeta ON rsvps.ID = rsvpAttendMeta.post_id
        AND rsvpAttendMeta.meta_key = 'attending'
    LEFT JOIN wp_postmeta AS rsvpAttendedMeta ON rsvps.ID = rsvpAttendedMeta.post_id
        AND rsvpAttendedMeta.meta_key = 'attended'
WHERE
    rsvps.post_type = 'rsvp'
    AND eventMeta.meta_value = 29945;

Notes:

• The event id is hard-coded and will be dynamic

[JasonTheAdams]

I was able to simplify the following:

Tutoring Events

SELECT
    events.ID,
    events.post_title AS name,
    tm.name AS category,
    rsvpAttendCounts.attendRsvps AS totalAttending,
    rsvpCounts.attendedRsvps AS totalAttended,
    startDateMeta.meta_value AS startDate,
    endDateMeta.meta_value AS endDate
FROM
    wp_posts events
    LEFT JOIN wp_postmeta startDateMeta ON events.ID = startDateMeta.post_id
        AND startDateMeta.meta_key = '_EventStartDate'
    LEFT JOIN wp_postmeta endDateMeta ON events.ID = endDateMeta.post_id
        AND endDateMeta.meta_key = '_EventEndDate'
    LEFT JOIN (
        SELECT
            eventMeta.meta_value AS eventId,
            COUNT(rsvps.ID) AS attendedRsvps
        FROM
            wp_posts AS rsvps
            INNER JOIN wp_postmeta AS rsvpMeta ON rsvps.ID = rsvpMeta.post_id
                AND rsvpMeta.meta_key = 'attended' AND rsvpMeta.meta_value = 'Yes'
            LEFT JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
                AND eventMeta.meta_key = 'event_id'
        WHERE
            rsvps.post_type = 'rsvp'
        GROUP BY
            eventMeta.meta_value) AS rsvpCounts ON events.ID = rsvpCounts.eventId
    LEFT JOIN (
        SELECT
            eventMeta.meta_value AS eventId,
            COUNT(rsvps.ID) AS attendRsvps
        FROM
            wp_posts AS rsvps
            INNER JOIN wp_postmeta AS rsvpMeta ON rsvps.ID = rsvpMeta.post_id
                AND rsvpMeta.meta_key = 'attending' AND rsvpMeta.meta_value = 'Yes'
            LEFT JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
                AND eventMeta.meta_key = 'event_id'
        WHERE
            rsvps.post_type = 'rsvp'
        GROUP BY
            eventMeta.meta_value) AS rsvpAttendCounts ON events.ID = rsvpAttendCounts.eventId
    INNER JOIN wp_term_relationships tr ON events.ID = tr.object_id
    INNER JOIN wp_term_taxonomy tx ON tr.term_taxonomy_id = tx.term_taxonomy_id
    INNER JOIN wp_terms tm ON tx.term_id = tm.term_id
WHERE
    events.post_status = 'publish'
    AND events.post_type = 'tribe_events'
    AND tx.taxonomy = 'tribe_events_cat'
    AND tm.term_id IN(17, 18)
HAVING
    startDate >= '2022-01-01 00:00:00'
    AND endDate <= '2022-12-30 23:59:59';

[JasonTheAdams]

Volunteers

Wooo! I got it!

SELECT
    volunteers.ID AS id,
    volunteers.user_email,
    volunteers.user_registered AS joinDate,
    sessions.totalSessions,
    sessions.totalK5,
    sessions.totalMiddleHigh,
    sessions.firstSessionDate,
    sessions.latestSessionDate,
    TIMESTAMPDIFF(YEAR, sessions.firstSessionDate, sessions.latestSessionDate) AS yearsActive
FROM
    wp_users AS volunteers
    INNER JOIN (
        SELECT
            rsvpUserIdMeta.meta_value AS userId
        FROM
            wp_posts AS rsvps
            LEFT JOIN wp_postmeta AS rsvpUserIdMeta ON rsvps.ID = rsvpUserIdMeta.post_id
                AND rsvpUserIdMeta.meta_key = 'volunteer_user_id'
        WHERE
            rsvps.post_type = 'rsvp'
        GROUP BY
            userId) AS rsvps ON volunteers.ID = rsvps.userId
    LEFT JOIN (
        SELECT
            MIN(startDateMeta.meta_value) AS firstSessionDate,
            MAX(startDateMeta.meta_value) AS latestSessionDate,
            COUNT(events.ID) AS TotalSessions,
            SUM(IF(tm.term_id = 17, 1, 0)) AS totalK5,
            SUM(IF(tm.term_id = 18, 1, 0)) AS totalMiddleHigh,
            rsvps.userId
        FROM
            wp_posts events
            LEFT JOIN wp_postmeta startDateMeta ON events.ID = startDateMeta.post_id
                AND startDateMeta.meta_key = '_EventStartDate'
            LEFT JOIN (
                SELECT
                    eventMeta.meta_value AS eventId,
                    rsvpUserMeta.meta_value AS userId
                FROM
                    wp_posts AS rsvps
                    INNER JOIN wp_postmeta AS rsvpMeta ON rsvps.ID = rsvpMeta.post_id
                        AND rsvpMeta.meta_key = 'attended'
                        AND rsvpMeta.meta_value = 'Yes'
                LEFT JOIN wp_postmeta AS eventMeta ON rsvps.ID = eventMeta.post_id
                    AND eventMeta.meta_key = 'event_id'
                LEFT JOIN wp_postmeta AS rsvpUserMeta ON rsvps.ID = rsvpUserMeta.post_id
                    AND rsvpUserMeta.meta_key = 'volunteer_user_id'
                WHERE
                    rsvps.post_type = 'rsvp'
                GROUP BY
                    eventMeta.meta_value,
                    rsvpUserMeta.meta_value) AS rsvps ON events.ID = rsvps.eventId
            INNER JOIN wp_term_relationships tr ON events.ID = tr.object_id
            INNER JOIN wp_term_taxonomy tx ON tr.term_taxonomy_id = tx.term_taxonomy_id
            INNER JOIN wp_terms tm ON tx.term_id = tm.term_id
        WHERE
            events.post_status = 'publish'
            AND events.post_type = 'tribe_events'
            AND tx.taxonomy = 'tribe_events_cat'
            AND tm.term_id IN(3, 17, 18)
        GROUP BY rsvps.userId
    ) AS sessions ON volunteers.ID = sessions.userId
ORDER BY volunteers.ID

[JasonTheAdams]

@dreazee I noticed something while working on the Volunteer query. It looks like we only (somewhat) recently started discerning between K-5 and Middle/High in event categories:

Notice that the total events tied to "Tutoring Dates" is 631, while the other two are only 34. That means that the majority of sessions were tagged with "Tutoring Dates", but not with the K-5 or Middle/High categories.

So someone would need to go through all the old events and apply the right category in order for the "Elementary Sessions Attended" and "Middle & High Sessions Attended" columns to be accurate.

[dreazee]

Oof @Jason Adams ... So here's the thing, until Covid/2020 there WASN'T a differentiation between elementary & ms/hs. It's something we started doing for zoom that we're now carrying over to in-person starting Jan 2023l. But before that we had ONE event for each day and then the tutors would either go to the elementary room or the ms/hs room to be paired with a student. So "TUTORING DATES" was both elementary & ms/hs at the same time. So I guess we would need to make a 3rd reporting column like "pre-2020 Tutoring Sessions Attended" or something like that... ?

[JasonTheAdams]

Hi @dreazee!

Ah, hahah! Well that's a fun twist. Fortunately, I really don't think it's too big of a deal. The Tutoring Sessions and Event Volunteer reports are unaffected by this, the Volunteers report includes all sessions, so we have a comparison, and the Sessions Metrics report will be date based — and I doubt much reporting will be done thats pre-2020 which affects this.

If you think it would help, I can also add a note on the reporting page to help make folks aware of this fact as to keep it in mind when viewing the report data.

[dreazee]

@JasonTheAdams ha! Lots of fun twists here at SDRT :) Couldn't hurt to add a little note so anyone viewing will know