NPR

Marketing Forms: Filtering Pledge Totals to Only See New Pledges

If you use Authorize.net or Sage Authorize & Capture processing, the schedule for future monthly payments is maintained within Marketing Forms.  This means that payments on sustaining pledges set up in the past are sent to your gateway automatically each month, and you see the details of the payments received within your pledge reports.

For stations in this situation, the Pledge Revenue Summary report will show the totals for new pledges received AND payments on pledges set up in the past if the processing occurred during the time period selected.

Currently there is not a way to filter payments on past pledges from the Pledge Revenue Summary report. The best way to get this total quickly is to do some filtering in Excel via the Donation Export report.

1. Use the Donation Export (Memsys) report to generate a CSV export, and open the report in Excel.

In the "H" column, you’ll see there is a field labeled “Gift Sequence”.  If the number in this field ends in 000 then it is a one-time gift.  If the number ends in 001, it is the first payment on a new sustaining pledge.  If you see any other number here, then it is a payment on a monthly gift that was set up in the past.

Gift Sequence Field
It is possible to use a little Excel wizardry to filter out any pledges that do not have a gift sequence ending in 000 or 001.  Here’s how:
 
  1. Right-click on the very top of Column A and select, “Insert” to insert a new column at the very beginning of the spreadsheet.

    Add New Column
     
  2. Click at the top of the your new first column to highlight the entire column.  In the formula bar at the top, insert this formula:  =RIGHT(I1,3)
     
  3. After you’ve pasted the formula at the top, hit CONTROL + ENTER at the same time. This will apply the formula to every field in that column.  It may take a few seconds for the system to catch up, but you should see numbers appear all the way down the first row.

    Paste Formula
     
  4. Spot check – the numbers in the first column should now match the last 3 digits on the numbers in the Gift Sequence field on that row.

    Excel spot check
Next you’ll want to set up filters so you can filter your Excel sheet to only see the pledges with the numbers 000 and 001:
 
  1. Highlight the top header row.
     
  2. In the “Data” tab at the top of the page, click on the Filter button

    Excel - Turn On Filters
     
  3. All the field headers should now be drop-down menus
     
  4. Click on the drop down for the first column – Uncheck the “select all” box, then check the boxes next to 000, 001 and blanks (the blanks option will be at the bottom of the list - Bill Me Later payments will have a blank Gift Sequence field)

    Filtering Column Data
     
  5. Click OK.  Excel will then hide all the rows except for those that represent new pledges
 
Now you can use Excel to run a quick Sum and see the total dollars received for new pledges:
 
  1. Find the donation column, and highlight all the cells that contain a donation amount.
  2. Go to the Formulas menu and click the AutoSum button.


     
  3. The total for all the highlighted pledges will appear in the first available empty cell underneath the cells you highlighted.
  4. Alternatively, you can do an auto-sum on the Year-End field.  This will give you a projected revenue total for 12 months out.
Questions? Having Trouble? Contact Station Relations 

 

Did you find this article helpful?