Stef Monson

This Data Studio report was created as a tool for the Field Organizers in Florida to keep track of their Active Volunteers. All of the information in this report; including names, emails, and phone numbers; has been obsured with randomly-generated data, and all data structure identifiers in the query below have been repaced with the word "schema" in order to protect campaign data.

Language: SQL (Redshift/PostgreSQL)
Development Tools: Civis Platform, Google Sheets, and Google Data Studio

Code Sample

/**
 * This Postgres query creates a view containing all current, previous, and almost active volunteers.
 *
 *@author Stefani Monson
 */

create view schema.active_vols as
select ta.pod
     , ta.region
     , ta.turf_code
     , ta.organizer_name
     , wx.report_week
     , wx.four_week_start
     , to_char(wx.four_week_start,'MM/DD/YY') 
         || ' - ' 
         || to_char(getdate()::date -1, 'MM/DD/YY') active_range
     , wx.week_start
     , wx.week_end
     , es.myc_van_id
     , p.first_name
     , p.last_name
     , to_char(p.primary_myc_phone,'FM9(999) 999-9999') primary_myc_phone
     , p.primary_myc_email
     , min(fs.event_date) as next_event_date
     , max(es.event_date) as last_event_date
     , count(es.event_shift_id) as num_shifts
     , case 
         when num_shifts >= 2 
           then 1 
         else 0 
       end as active_flag
     , case 
         when num_shifts = 1
           then 1
         else 0
       end as almost_flag
     , case 
         when active_flag = 1 
           and count(case 
                       when es.event_date between four_week_start + 7 
                         and least(wx.week_end, getdate()::date-1) 
                           then es.event_shift_id
                       else null 
                     end) < 2 
           then 1 
         else 0 
       end as drop_flag
       , coalesce(lag(active_flag) over (partition by es.myc_van_id order by wx.week_start asc),0) as previously_active
       , case 
           when count(fs.event_shift_id) > 0 
             then 1 
           else 0
         end as future_shift_scheduled

from schema.turf_assignments ta
cross join (select report_week
           , week_start
           , week_end
           , four_week_start 
      from schema.week_xref 
      where report_date <= getdate()::date
      group by 1,2,3,4
  ) wx

left join (
  select fo_name
       , myc_van_id
       , event_date
       , event_shift_id
  
  from schema.event_shifts
  where current_shift_status_name = 'Completed'
  and event_type in ('Canvass', 'Phone Bank', 'Text Bank', 'Voter Registration')
) es  
  on es.event_date between four_week_start and least(week_end,getdate()::date - 1)
  and es.fo_name = turf_code
  
join schema.person p
  on p.myc_van_id = es.myc_van_id
  
  left join schema.event_shifts fs
    on fs.myc_van_id = es.myc_van_id
   and fs.event_date between getdate()::date and wx.week_end
   and fs.current_shift_status_name ~* 'Conf|Sched'
  
  group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14
  order by turf_code, week_start, active_flag desc, drop_flag desc
  with no schema binding;

Stef Monson ☰

Active Volunteer Directory

Code Sample