FGdb Reports

This is related to ticket number 12475.

Trend Reports

(These show trends over time)

Volunteer Related

• Numbers of volunteers volunteering over time.
  • filter on program (adoption, build, other)
  • filter on job (receiving, recycling, data entry)
• Same thing, but total of hours volunteered instead of volunteer count.
• Number of active volunteers over time (an active volunteer has logged >= 4 hrs in the last 90 days.) DONE
• hours by task category/program (does that exist yet?) OR distinct person count by task category/program over date range grouped by date
• - # new volunteers in build, adoption, other
• - # volunteers who logged hours in build, adoption, other
• - # hours logged in build, adoption,

Gizmo Related

• Numbers of (gizmo_types) received over time.
• Numbers of (gizmo_types) sold over time.
• Numbers of (gizmo_types) disbursed over time.
• Same as above but for (gizmo_categories) instead of (gizmo_types).
• Same as the above (gizmo_types) but be able to pick >1 type, so you can see (for example) the relation between laptops and systems received, sold, whatever.
• gizmos by type or category flow type (incoming or outgoing) over date range grouped by date, week, month, quarter, or year
• - # systems, monitors, printers granted
• - # systems, monitors, printers adopted
• - # systems, monitors, printers sold
• - # systems, monitors, printers recycled
• - % of systems, monitors, and printers granted, adopted, sold, and recycled
• * extra swanky: these numbers (or at least the non-recycle numbers) for laptops, macs

Income

• Average donations, fees, and total (i.e. front desk income) over time (DONE, pretty much).
• Average amount of sale over time. (Be able to select a (gizmo_type) or (gizmo_types) for comparison. OR Be able to look at all sales, regardless of (gizmo_type).)
• over date range grouped by day, week, month, or year donation income by fee and contribution sale income by discount type and totals
• Total sales ($) by gizmo_type (and gizmo_category) over a period of time. (This is a trends or "over time" type report.)
• Same as above, but limit to discount type -- that is I want to see trends on systems that were sold at a volunteer discount.

Atypical trend reports

These are implemented

Most trend reports show numbers on a daily, weekly, monthly, quarterly, or yearly basis (for a given period of time).

We will also want some reports that show day of the week or hour of the day (for a given period of time). For example "How many donations came in on all Sundays, Monday, Tuesdays, Wednesdays, Thursdays, Fridays, and Saturdays (in a given period of time).

This SQL produces one row for each day of the week:

Sales in dollars for 2008 (by Day of Week):

-- 2008-01-01 is a sample beginning date
-- 2008-12-31 is a sample end date

-- sales amount by category example:
SELECT
 EXTRACT( DOW FROM created_at ) AS dow,
  SUM( reported_amount_due_cents )/100.0 AS amount
  FROM sales
  WHERE sales.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  GROUP BY 1
  ORDER BY 1
  ;

Donation count for 2008 (by Day of Week):

-- 2008-01-01 is a sample beginning date
-- 2008-12-31 is a sample end date

-- donations count by DOW:
SELECT
  EXTRACT( DOW FROM created_at ) AS dow,
  COUNT(*) AS donation_count
  FROM donations
  WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  GROUP BY 1
  ORDER BY 1
  ;

Depending on the coding requirements, it might be better to loop through the numbers (0-6 == Sun-Sat) and get each DOW separately, for example (here 2 means Tuesday):

SELECT
  COUNT(*) AS donation_count
  FROM donations
  WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND EXTRACT( DOW FROM created_at ) = 2
  ;

Hours of the day work like this (12 will return anything that happened noon or after, but before 1 pm):

SELECT
  COUNT(*) AS donation_count
  FROM donations
  WHERE donations.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND EXTRACT( HOUR FROM created_at ) = 12
  ;

Or hour by hour sales amounts:

SELECT
  EXTRACT( HOUR FROM created_at ), SUM(reported_amount_due_cents) AS sales_amount
  FROM sales
  WHERE sales.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  GROUP BY 1
  ORDER BY 1
  ;

Other

• - # donations
  • - # unique donors
  • - avg. amount of money given per donation

• - # sales
  • - # unique store customers
  • - avg. amount spent per store transaction

Not trend reports

Some reports mentioned that are not trend report ("over time"):

• List all volunteers that have worked (more than | less than | between) a certain number of hours.
• List all organizations that have donated gizmos in a given date range that (are | are not) covered entities.

Questions PR wants answered (that could be reports)

Would need additional changes to database

• Recycling tonnage: How many tons of e-waste did we recycle last year?
• Collection events: "I helped organize 3 collection events (Hillsboro Library, Elementary School, and ECOHAUS) in 2008, how many others were there? For instance, I know that Hasson Realtors partnered with Free Geek on one."

Don't need additional changes

• How many folks volunteered at Free Geek in 2008?
• How many hours total did they collectively volunteer?

Not sure

• We donated approx. 1200 computers last year - how many were given to adoptees & how many to grantees?
• Do we keep track of numbers of equipment/gizmos processed for 2008: computers, printers, keyboards, etc.
• A list of organizations that received Free Geek computers in 2008.
• A list of all Free Geek cash donors and amounts for 2008-I've got the spreadsheet for the Give Guide donors. Can you combine the Causes and all others into one e-report? This would also include in kind donations from Companies/individuals. I would like to exclude the front desk donations unless they were notable amounts - $50+

Notes

One more report-request that I've heard of.

If the part of the gizmo report that lists disbursements could be a link to the actual disbursements showing the contacts who are getting disbursed to, that would be good and would save Dave a lot of time.

SQL samples for use in creating reports

Gizmo type sales and counts

(Note by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos sold report into a count of gizmos received report quite easily. Same for disbursements and recyclings, obviously.)

-- 6 is a sample gizmo_type (laptops)
-- 2009-04-01 is a sample beginning date
-- 2009-04-30 is a sample end date

-- sales amount by type example:
SELECT 
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  WHERE sale_id IS NOT NULL 
  AND gizmo_type_id = 6 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

-- sales count by type example:
SELECT 
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  WHERE sale_id IS NOT NULL 
  AND gizmo_type_id = 6 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

Gizmo category sales and counts

Similar to above, but based on broad category instead of specific gizmo type:

(Note again, by changing "WHERE sale_id IS NOT NULL" to "WHERE donation_id IS NOT NULL", we could turn the count of gizmos sold report into a count of gizmos received report quite easily. Same for disbursements and recyclings, obviously.)

-- 2 is a sample gizmo_category (monitors)
-- 2009-04-01 is a sample beginning date
-- 2009-04-30 is a sample end date

-- sales amount by category example:
SELECT 
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_types.gizmo_category_id = 2 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

-- sales count by category example:
SELECT 
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_types.gizmo_category_id = 2 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  ;

Since there are so few categories, we could show multiple lines on the same graph if we want. This might help do that:

-- sales amount by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( unit_price_cents )/100.0 AS due 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;

-- sales count by all categories example:
SELECT 
  gizmo_categories.name,
  SUM( gizmo_count ) AS count 
  FROM gizmo_events 
  LEFT JOIN gizmo_types ON gizmo_events.gizmo_type_id = gizmo_types.id
  LEFT JOIN gizmo_categories ON gizmo_types.gizmo_category_id = gizmo_categories.id
  WHERE sale_id IS NOT NULL 
  AND gizmo_events.created_at BETWEEN '2009-04-01' AND '2009-04-30'
  GROUP BY 1
  ;

Volunteer Related

Hours worked by volunteer task:

-- to get number of hours worked in volunteer_task_type = 28 (receiving) between Jan 1, 2008 and Dec 31, 2008:
SELECT SUM( duration ) 
  FROM volunteer_tasks 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND volunteer_tasks.volunteer_task_type_id = 28
  ;

To change this to a volunteer count instead of hours, change "SUM( duration )" to "COUNT(DISTINCT volunteer_tasks.contact_id)" like this:

-- to get number of volunteers that worked in volunteer_task_type = 28 (receiving) between Jan 1, 2008 and Dec 31, 2008:
SELECT COUNT(DISTINCT volunteer_tasks.contact_id) 
  FROM volunteer_tasks 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND volunteer_tasks.volunteer_task_type_id = 28
  ;

Sometimes we want to see adoption or build related tasks isolated out. This is by "program". Hours worked by program looks like this:

-- to get a list of programs do this:
SELECT 
  id, name 
  FROM volunteer_task_types 
  WHERE parent_id IN (SELECT id FROM volunteer_task_types WHERE name = 'program');

• Program 24 == adoption.
• Program 27 == build.
• Program 17 == all the other stuff

(Note there are some potential snags in the data since adoption and build also have a parent_id == 17. But this doesn't currently screw anything up.)

-- to get number of hours worked in program = 27 (build) between Jan 1, 2008 and Dec 31, 2008:
SELECT SUM( duration ) 
  FROM volunteer_tasks 
  LEFT JOIN volunteer_task_types ON volunteer_tasks.volunteer_task_type_id = volunteer_task_types.id 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  AND (volunteer_task_types.parent_id = 27 OR volunteer_task_types.id = 27)
  ;

This might help with a multi-line chart with one line for each program:

-- to get number of hours worked in all programs between Jan 1, 2008 and Dec 31, 2008, one row per program:
SELECT programs.description, SUM( duration ) 
  FROM volunteer_tasks 
  LEFT JOIN volunteer_task_types ON volunteer_tasks.volunteer_task_type_id = volunteer_task_types.id 
  LEFT JOIN volunteer_task_types AS programs ON volunteer_task_types.parent_id = programs.id 
  WHERE volunteer_tasks.created_at BETWEEN '2008-01-01' AND '2008-12-31'
  GROUP BY programs.id, programs.description
  ORDER BY programs.description;