Fri 04 July 2014 | -- (permalink)

Last year I did a project with some other developers where we looked at some volunteer data from a Boston area charity. We had a record for every occurrence of a volunteer attending an event over several years.

Here is a plot with a sample of some of the volunteers and each of the events they attended.

One of the thing you can do with this data is to look for the natural rate at which a given person volunteers and find people who haven't been to an event at their regular interval. You could then flag those volunteers for some sort of outreach.

Here's the SQL I used to find volunteers that were "deactivated":

select
  contact_id,
  max(occurrence_end) as last,
  extract(days from max(occurrence_end) - min(occurrence_end))/count(1) as f,
  max(occurrence_end)::date +
  2*(extract(days from
            max(occurrence_end) - min(occurrence_end))
       /count(1))::int as end_date,
  count(1)
from
  volunteer
group by
  1,2,3
having
  count(1) > 5
  and max(occurrence_end)::date
         + 2*(extract(days from max(occurrence_end) - min(occurrence_end))
         /count(1))::int
     < '2013-01-01'::date
  and max(occurrence_end)::date > '2013-01-01'::date - 4*(extract(days
                                                from max(occurrence_end)
       - min(occurrence_end))
       / count(1))::int

If you are a non-profit that would like help implementing a system like this let me know, I would be interested in helping to set something up that you could act on.

The chart at the top is based on the scatter plot from wrobstory.