Paid File – Direct Debits

Published: 5th February 2018

This guide assumes a basic/intermediate knowledge of the gift tab on Raiser’s Edge, Excel and IOM (ImportOmatic).

Step 1

On the 9th and 22nd of each month you will get an email that says ShareFile Activity Notification – these are ‘paid’ files for the 1st and 15th of the month Direct Debits (lag in time to allow for failed DDs to be tried again or bank holiday dates etc.) – download these items.

Step 2

It’s a list of all the payments we receive on that date (the lump sum can be seen in the bank account on the 1st/15th) + ‘unpaids’ + a summary of the money that’s come in.

Unpaids
  • These may have already been dealt with in a previous cancelled file – it just depends on the timing of the donor’s cancellation, for others it may be the first time you know they’ve cancelled.
  • Some are a skipped transaction (ie no money in the bank at that time, but not cancelled their direct debit Rapidata usually try 2 collections then auto-cancel?) – which can be reflected in RE
  • See attached RDS Cancellation codes pdf which relates to column E payment_cancellation_code in the ‘unpaid’ file (same codes used in the cancellation file) – for more info/explanation of these, ask bmay@rapidata.co.uk
  • For cancellations made around the time of a payment, it is worth checking the paid file to see if this person paid and then cancelled or cancelled before their payment was taken.

 

Step 3

Rapidata vs. RE expecting vs. Bank Account

  • After the unpaids are done, in theory, what RE is expecting should now match with the paid file list that you downloaded from sharefile/Rapidata. (Sometimes it doesn’t – but don’t worry! Some people may have reactivated and you won’t know until the paid file or an upgrade wasn’t applied to the donor’s recurring gift, some people cancel and they don’t turn up in the cancelled file).
  • Check bank account.
  • Go to RE > Query > Finance Processes > OMATIC DD PAID FILE. Change date to the date these DDs were paid (either 1st or 15th of that month). Export query and save somewhere you can easily find – this is what RE is expecting us to have paid based on the Recurring Gift on the donor’s record.

Step 4

Matching the data before import

You now have 2 spreadsheets: RE expecting and the paid file downloaded from Rapidata.

Using columns URN, Surname, and DD_Amount from the paid file, compare the following:

  • Number of transactions
  • Total £ value

Then, sort by URN and Gift Reference (separately).

Go to conditional formatting – check duplicate Gift Ref/URNs – it’s easy to see ones that don’t match.

Match on amount and gift reference/URN for each person using IF statement       =IF(AND(E2=K2, F2=I2), “Full Match”, “?”)

If you see a row with a ‘?’, it may mean that the amount doesn’t match – make sure to check RE before continuing. Just be aware of the Gift Reference being ‘stored as text’ or ‘stored as number’ as this will affect your formula!

Step 5

If all the rows match, delete the information from the Rapidata file and import your RE expecting file straight into RE using the Direct Debit Giving payments profile in IOM.