Aller directement à la fin des métadonnées
Aller au début des métadonnées

Project to Add calculated fields to CiviCRM, extra reports and survey functionality

Report installation - update

Several of the reports in this project are now available - you need to install both https://github.com/eileenmcnaughton/nz.co.fuzion.reportbase and https://github.com/eileenmcnaughton/net.ourpowerbase.reports.advancedfundraising (by downloading or git cloning them to your extensions directory & then installing from the manage extensions page)

 

These are improvements PTP has identified as being particularly useful to fund-raising organisations - including 'roll-up' fields, extra reports and the ability to receipt contributions from the phone banking field

 

 EstimateRequirementAssignedComments
 1

10 hours

 

Create new Custom  Data - figure out install mechanism (see below for field list)


I'm assuming we will only install and not uninstall these fields.

Jamie -

Note from ptpmark on March 14 2013: this is completed and is in beta form as an extension.


  • From Lobo: preference for field data stored in xml or JSON as non devs can edit
  • Jamie pointed out that "I was assigned a bug against civicrm_engage from someone complaining that they enabled civicrm_engage, it created a bunch of fields, and then they un-installed it and the custom fields remained.

     

    I fixed the bug by not creating the custom fields on install. Instead, you have to go the configuration screen and click a button to install them (which says this step is not-reversible)."

 

     
 2? TBC?Create new Contribution Status – PromisedON HOLD

Should be similar issues around install / uninstall as above. Dave has questioned this as it seems similar to the existing 'Pending Pay Later' Can we just rename this type?

from PTP-Mark:  Pending Pay Later is okay if its not already used for something else. We may also want to simply use the Pledge system. I'm double checking that option.

From Owen : We use pending pay later for invoicing and event registration.

     
 340-60 hours

 Establish mechanism to update custom fields based on triggers or nightly script.

Contribution 'simple'

  • Lifetime Contributions
  • Total This Year (by this person)
  • Total Last Year
  • Date Last Contribution 
  • Date First Contribution
  • Largest Contribution
  • Total # of Contributions

    Contribution -requires 2 queries / subquery
  • Amount of Last Contribution

Jamie

Note from ptpmark on March 14 2013: this is completed and is in beta form as an extension.

Preference is currently for Triggers. This would mostly be around updating custom fields for the given contact when a given contribution is updated. But there is also an event custom field

I assume mysql has a reasonably efficient 'this year mechanism'

 

Generally I have found getting data about the latest contribution or similar somewhat complex as a MAX('receive_date') only gives you the date - you then need to use this date to get the id & details

PTP - Mark: we've created the sql for updating these fields in a cron job, so I think we've got a good portion of it.

From Owen : Would be good to have "Total contribution" with both inc and ex soft credits.

  
  • Event - requires 2 query / subquery
  • Last Event Attended - show Name + Date

Jamie -

Note from ptpmark on March 14 2013: this is completed and is in beta form as an extension.

 
 4 Reports - To be shown on the Dashboard:  

 

4.1

 

20 hours but should check how far existing Lybunt report goes towards this

1. Renewal Chart: 


Accept these parameters:

  • Group(s). Default to none selected, which means include all.
  • Contribution/Financial types selection set . Default to none selected, which means include all.
  • Cutoff date for current reporting period/
  • Time period of contributions to include in each column. Default to 12 months.
  • Stacked Bar Column intervals.  Default to 12 months.

 

Read the contribution table for completed contributions from a user-specified group of contacts. If no group is specified, default to all contacts.  The contributions included should default to be of all contribution/financial types, but allow the user to update which contribution types to be used in this run.

The cutoff date will determine the last date of financial transactions to include.

The time period of contributions to include should default to 12 months.  Allow the user to change the number of months.

 The column intervals will default to 12 months apart from the cutoff.  This interval determines the last date of financial transactions to include for the comparison bars for the three prior periods.

Save the entries selected as the default for this user.  Alternatively, save it as report criteria.

Show the count of contacts that gave in the prior time period and gave in the most recent time period (Renewals), and those who gave in the prior time period but did not give in the most recent period (Lapsed). Show the results for the current period and the prior three periods.  Make available as a stacked bar chart with raw numbers and renewal rate shown as a percentage.

The renewal rate is calculated by dividing the number of contacts who donated in the current period by the total number of contacts who donated in the prior period.  In the chart below, the renewal rate is shown as a line chart, but it could be shown in another way, depending on the requirements of the software.

Permit clicking through the separate components of the bar chart to retrieve the supporting contribution details and display as a contribution search result.

 

 Use Cases:

A group with a well-organized fund raising program does multiple phone banks throughout the year.   One measure of its success is how well it renews existing individual donors.  Its fundraising advisor recommends that they consider a donor as renewed if they donate again sometime during the 18 months after their original donation, so it will select an 18 month Time Period of Calculation.  For this group, a renewal is only a contribution that is of the “membership” type.  They check their results every six months, so the column interval will be six months.

Another group has a fiscal year ending on June 30. It has a goal of 60% renewal rate.  Since it doesn’t charge for events, it has no event fees and so all of the contributions will be included. It’s new to fundraising efficiently, so its advisor suggests that they treat multiple donations over a two year period as a renewal.

A third group has a fundraising program that is targeted at its member organizations. The organizations make contributions as well as sponsor tables at the annual gala dinner.  The report is done annually covering the calendar year. Only organization contact types are included and the tax deductible contributions and event sponsorship contribution types are included.

These items below are sample selections for illustration purposes.

Contact Groups (created beforehand)

  1. Include all the individual donors who’ve given more than a certain amount to see the renewal rate of major donors.
  2. Include members only
  3. Include individuals with specific issue interest
  4. Include all unions and churches organization types
  5. Include all foundations
  6. Include all contributions, dropping out event fees and merchandise sales.
  7. Include only campaign contributions and dropping out merchandise fees and tax deductible contributions.
  8. Include only tax deductible contributions
    1. 12 months
    2. 18 months
    3. Every six months
    4. Every 12 months

Contribution Type Sets

  1. Include all contributions, dropping out event fees and merchandise sales.
  2. Include only campaign contributions and dropping out merchandise fees and tax deductible contributions.
  3. Include only tax deductible contributions
    1. 12 months
    2. 18 months
    3. Every six months
    4. Every 12 months

Time period of contributions to include in each column.

  1. 12 months
  2. 18 months

Column Interval

  1. Every six months
  2. Every 12 months
Eileen

 

Create Temp table for people who gave last year (previous year for other versions) and join against civicm_contribution table for this year/ later year. Conversion rate doesn't seem like a problem.

How far does the existin LYBUNT report go towards this?

I'm not 100% how a pie chart would show this. I guess it shows 2 colours to represent the % that renewed

PTP Mark:This spec is complete.

4.210 hours but should check how far existing Sybunt report goes towards this

2.     Lapsed and Recovered Donors Chart

Show two pie charts of lapsed and recovered donors.  A lapsed donor is someone who gave money in the past, but has not given money in the period in question.  The pie charts should cover two successive periods

Accept these parameters:

  • Group(s). Default to none selected.
  • Contribution/Financial types selection set. Default to none selected.
  • Cutoff date for current reporting period
  • Time period of contributions to include in each pie. Default to 12 months.
  • Time interval between the pie charts’ cutoff dates. Default to 12 months.
  • Maximum number of months to include in the universe of contributions. Default to 60.

Read the contribution table for completed contributions from a user-specified group of contacts. If no group is specified, default to all contacts.  The contributions included should default to be of all contribution/financial types, but allow the user to update which contribution types to be used in this run.

The Cutoff date will determine the last date of financial transactions to include in the first pie chart.

The Time period of contributions to include should default to 12 months.  Allow the user to change the number of months.

The Time interval between the pie charts’ cutoff dates will default to 12 months apart from the cutoff.  This interval determines the last date of financial transactions to include for the pie chart of the prior period.

Save the entries selected as the default for this user.  Alternatively, save it as report criteria.

The existing SYBUNT report tackles this and the comparison logic will work. There are two issues with the SYBUNT report’s application to this chart.  First, SYBUNT does not include the recovered donors.  Secondly, there is a bug in the SYBUNT report in which, when searching for results from an earlier year, it includes donations from donors who made donations for the first time AFTER the reporting year in question. These people should not be included.

When the user clicks on a pie chart, show the list of lapsed individuals as a result set similar to what one finds when doing an Advanced Search.  Allow the actions that one typically finds for an Advanced Search result set. If this can be saved as a smart group without a great deal of coding, it would be desirable to allow creation of a smart group at this stage. 

Alternatively, show a list of individuals in the manner that the LYBUNT report does and give an option to add them to a group as the LYBUNT report does.

 

Similar to above  - how does this compare to existing Sybunt report?

PTP-Mark: This spec is currently being reviewed to see if it really shows the info in the best way. The problem is that the number of "people who had previously given" will continue to increase as the years go on. It may not be relevant to count the recovered donors against a list of donors that is 10 years old, for example. 

 

PTP Mark -Update March 14 2013 - this spec is completed.

     
4.310 hours

3.     New Contributor Counts

 Total number of people who contributed for the first time, by quarter, for the current year. Show total number per quarter. Total number for the current year also needs to be shown. Make available as a bar chart. When you click on the bar chart, open a report that shows you the current bar chart and bar charts for the previous three years.

Sample Charts:

Eileen 
4.420-30 hours

4. Box of key numbers:

  1. Total number of DONORS in current year
  2. Total amount raised in current year YTD and % growth over previous year YTD
  3. Total amount raised from Individuals YTD and % growth over previous year. YTD
  4. Average Individual gift this year and previous year.
  5. % of Individual DONORS who increased their total this year (does this means the person was a donor both years?)
  6. Total number of sustainers (people who give monthly)
  7. Number of new contacts added to the database this year.
  8. % growth of database. YEAR over YEAR

 

 

 

This Year

Percentage Change

Prior Year

Total Number of Donors

1765

 

Amount Raised

 $1,037,448

23%

    $841,096

Amount of Individual Donations

               275,487

-4%

    285,547

Average Donation

                 156.08

 +8.5%

      145.24

Individual Donors who Increased their donation

372

21%

527

Number of sustainers

212

 10%

181

New Contacts in the database

1697

5.0%

 

Eileen

Seems like a lot bundled up in this one - would we just rely on the dashboard to cache the data - or would we cache in the cache table (probably better). When would we update?

PTP-Mark - For number 2 and 3 to be useful as comparisons to the previous year, they probably need to be Year To Date comparisons

5 

Reports:

  
5.110 hours but see comments

Solicitor Report: Grouped by Solicitor, show Total Gifts and Total Amount. Then list Contributors with Amount, Date, Phone and Email.

Date filter needed. Don't know what “door opener” means.

 Soft Credit Enhancements Dave G is recommending we review using soft credits to record this data.
     
5.215 hours

How many people made a gift: Filter by Date on Ask Date field to be able to then calculate 1) how many people asked, 2) how many people gave after being asked, 3) how many were phonebanked (how many have a phonebank activity with the same date as the date asked

  
5.315 hoursWhat was the range of gifts: Filter by Date and return Highest Contribution, Lowest Contribution, Avg Contribution, Median Contribution, Total number of Contributions.  
5.415 hours

Who are your regular givers: Return a list of people who have contributed in consecutive years. Show number of years and sort from high to low.

  
     
6 Extra Survey Functionality  
 

20-32 hours? assuming the Xav-type button works in conjunction with the free-html

Otherwise will need to come up with a new approach

A “Record Donation” button in each row within a survey that pops up a window that let's you fill in a Contribution for the person in that row (as seen here: http://forum.civicrm.org/index.php/topic,26498.0). This feature needs to show up only for PhoneBanking. Additionally, it would be great if this could be a pop up window.

Also need "Record Membership" button in some cases.

Jamie

Note by PTP Mark on March 14 2013: There is a beta version of this for 4.2 survey that allows either a link to membership renewal or contribution tabs. It currently applies system wide.

This would need configuration on the Survey page as to whether the button would be enabled or not. Outside this spec but discussed was the option of more than one possible button (e.g create new participant, create new pledge).

 

Another option discussed is using the custom field type 'free-html'. However, the problem here is it would need a contact id passed into it. It may be possible to use a jquery approach using classes - like the edit in place, which leverages the entity id already stored in a class.

Custom Fields

Create new Custom Data Sets - Fundraising Info

Create Custom View Only fields for each donor contact

(Determination of which contribution  types to include in totals should be driven from a table of included contribution types)

  • Lifetime Contributions
  • Total This Year (by this person)
  • Total Last Year
  • Total Year before last (this gets around issues of lack of data at the first of the year)
  • Amount of Last Contribution
  • Date Last Contribution 
  • Date First Contribution
  • Largest Contribution
  • Total # of Contributions
  • Last Event Attended - show Name + Date

Create Solicitor custom field - contact reference

Create new Custom Data Set - Fundraising

Create custom fields:

* Ask Amount

* Ask Date

Étiquette
  • Aucun
  1. Jan 18, 2013

    Ask Amount and Ask Date are 'recurring' and might best be attached to the Survey (and Canvass and Phonebank) activity types. This would allow those fields to just be included 'at will' in a survey profile.

    1. Jan 18, 2013

      Mark Sherman dit :

      Dave et al, this is one Ask Amount and Solicitor use case:

      Each year the organization reviews its major donors and assigns a solicitor to them.  There will be an Ask Amount assigned as well as a category for the donor - Maintain, Increase, New are some possible categories The donor will be contacted possibly multiple times during the course of the year.  The contacts are not about donating, but are designed to communicate about the work of the organization.  The solicitor is responsible for making an ask as appropriate.  This process is managed by someone who is reviewing the progress throughout the year.  The process manager wants to know if the solicitors are contacting the donors, and more importantly, wants to know if the money is coming as planned.  Since donors can give in a variety of ways (monthly, once a year) and the donation cycle is heavily loaded towards the end of the calendar year in the U.S. anyway, it's difficult to know whether an organization is on track or not.  In this use case, the group tracks results quarterly against its targets. 

  2. Jan 18, 2013

    Solicitor is pretty similar to existing Soft Credit concept. In discussions about enhancements to soft credits - Eliet and Kellie were promoting adding a 'Soft Credit Type' property (and Solicitor would be one of the types).

  3. Apr 14, 2013

    I have some questions/concerns about the general approach of adding custom fields to the database for each calculated field.  ( such as most recent contribution).  It seems like adding a custom field and a trigger/nightly job is a very heavy weight approach to having a calculated field in CiviCRM. ( Plus it creates its own risks, such as an end-user deleting the fields by mistake) 

    Since there is already a hook in CiviCRM for creating calculated fields ( ie the custom tokens hook) seems like it would be a cleaner approach to enhance/extend the areas of CiviCRM where custom tokens are available to the user. For example, it would be great if custom tokens were available as exportable fields to a CSV, were available in the Advanced Search filters, and available for report criteria.  This approach would also be a clean model for other developers to create other kinds of custom calculated fields that can be shared via extensions.

  4. Apr 16, 2013

    obowden dit :

    I've added some comments above. 

    We assign all contributions with campaigns so it would be good to have all reports filterable by campaign.

  5. Apr 16, 2013

    Mark Sherman dit :

    Sarah - I shared your trepidation about mysql triggers, but we're not finding them to be a drag on the databases in our environments. I'm afraid it's not realistic to try to jam more logic into the already complex advanced search environment.  While end users can delete the summary fields that we created, they can just as easily re-enable them.

    As for being able to export the values to a csv table, my general stance is to try to find where our users are using csv files to get work done and replace that process with something that runs inside CiviCRM.  We're probably never going to achieve it, but I think a robust system is one that is able to execute all use cases without resorting to moving data to another system -  with the exception of financial accounting, which I think should remain outside of CiviCRM.

    obowden - good point about filtering by campaigns.

  6. Apr 17, 2013

    obowden dit :

    We're probably edge case, but custom fields would work better for us as we use 3rd party reporting tools and dashboards that pull straight from the database.

    One avenue to explore once these are in place is RFM segmentation.for mailings etc..

    http://en.wikipedia.org/wiki/RFM  

    http://cmason.myweb.uga.edu/Course_Roadmap/_RFM_Analysis/Note_-_RFM_Analysis.pdf


Creative Commons License
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.