FGdb Reports

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

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
  ;

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
  ;