Querying and Exporting RG information

Options
Hello All, I'm a fairly new RE user and am trying to pull some accurate data for an upgrade campaign that we will be starting soon. I was pointed to this great forum and am hoping you may have some insight. I need to find the following: All donors who give via recurring gift WHO: - Have given a minimum of one year AND - Have not missed more than 1 donation I will need to export: - Their contact details - Their first recurring gift details (date/amt/appeal) - Their next transaction due date - Their recurring gift CC/bank details - Their last one-off gift details I'm not sure whether I should be using a Constituent query or a Gift query/export. Also, since I will be importing back in a lot of changed information, do I need to be exporting import IDs at this stage? Thanks heaps!
Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    Lauren Smith:
    Hello All, I'm a fairly new RE user and am trying to pull some accurate data for an upgrade campaign that we will be starting soon. I was pointed to this great forum and am hoping you may have some insight. I need to find the following: All donors who give via recurring gift WHO: - Have given a minimum of one year AND - Have not missed more than 1 donation I will need to export: - Their contact details - Their first recurring gift details (date/amt/appeal) - Their next transaction due date - Their recurring gift CC/bank details - Their last one-off gift details I'm not sure whether I should be using a Constituent query or a Gift query/export. Also, since I will be importing back in a lot of changed information, do I need to be exporting import IDs at this stage? Thanks heaps!

    Lauren,

    Still thinking about best way to get all the info you want in the easiest manner.  But yes, if you are importing anytime with the chance that you may want to import info back into RE you want to export IDs for all fields that apply (example: constituent, gift, contact etc.) or may be imported back in. 

    Looks like the fields you're looking for are available in a gift export.  I just looked there as it seems like the primary thing you're interested in.  But do your donors have more than one recurring gift? Are all the gifts to one fund? 

    Just looking for what you'll want for query criteria.  You'll need to create a query of the records/gifts to take to Export.  Think you should get desired results with gift type, gift date, and number of consecutive payments missed fields.

    Can I ask what fields you will be changing to import back in?  Remember you will lose your original info if you change fund.  Down the road, an auditor may question how a donor made a recurring gift in 2009 to a campaign that began in 2013. 

    Also, if you're looking at next transaction due date - sometimes you will get misinformation on this.  I have had 2 cases open with support this year about records showing up that shouldn't and records not showing up that should in just a simple query recurring gifts with output of next transaction date.  Just a caution to look this over carefully. 

    Hope this provides a little help at least.  Welcome to the forums. 

  • JoAnn Strommen:

    Lauren,

    Still thinking about best way to get all the info you want in the easiest manner.  But yes, if you are importing anytime with the chance that you may want to import info back into RE you want to export IDs for all fields that apply (example: constituent, gift, contact etc.) or may be imported back in. 

    Looks like the fields you're looking for are available in a gift export.  I just looked there as it seems like the primary thing you're interested in.  But do your donors have more than one recurring gift? Are all the gifts to one fund? 

    Just looking for what you'll want for query criteria.  You'll need to create a query of the records/gifts to take to Export.  Think you should get desired results with gift type, gift date, and number of consecutive payments missed fields.

    Can I ask what fields you will be changing to import back in?  Remember you will lose your original info if you change fund.  Down the road, an auditor may question how a donor made a recurring gift in 2009 to a campaign that began in 2013. 

    Also, if you're looking at next transaction due date - sometimes you will get misinformation on this.  I have had 2 cases open with support this year about records showing up that shouldn't and records not showing up that should in just a simple query recurring gifts with output of next transaction date.  Just a caution to look this over carefully. 

    Hope this provides a little help at least.  Welcome to the forums. 

    JoAnn, Thank you for your quick response. To answer your questions... 1. Some of our donors do have more than one recurring gift, although right now they only have one ACTIVE recurring gift. 2. For this upgrade campaign, the recurring gifts currently go into one of two different funds. These funds would not be changed on the import back in. 3. The information that could be imported back in would include: Bio info, RG CC information, RG ammendment (to amount AND to split the appeal moving forward to track the upgrade success), Spouse Relationships, Address (including phone types), Actions, Appeal (to tab) I'm not sure of the 'number of consecutive payments missed' functionality. Right now we do record $0 gifts (where their CC failed, etc.) So would that get counted? I'm a bit scared at hearing of the issue with 'next transaction due date'. Were the errors there because your CRITERIA was 'next transaction due date' or because it was just in the OUTPUT? What RE version are you on? Cheers, Lauren
  • Lauren Smith:
    JoAnn, Thank you for your quick response. To answer your questions... 1. Some of our donors do have more than one recurring gift, although right now they only have one ACTIVE recurring gift. 2. For this upgrade campaign, the recurring gifts currently go into one of two different funds. These funds would not be changed on the import back in. 3. The information that could be imported back in would include: Bio info, RG CC information, RG ammendment (to amount AND to split the appeal moving forward to track the upgrade success), Spouse Relationships, Address (including phone types), Actions, Appeal (to tab) I'm not sure of the 'number of consecutive payments missed' functionality. Right now we do record $0 gifts (where their CC failed, etc.) So would that get counted? I'm a bit scared at hearing of the issue with 'next transaction due date'. Were the errors there because your CRITERIA was 'next transaction due date' or because it was just in the OUTPUT? What RE version are you on? Cheers, Lauren
    For some reason, even though I am writing with paragraph spaces, the forum is cramming my posts into one wall of text. Does the forum not work with Google Chrome?
  • JoAnn Strommen
    JoAnn Strommen Community All-Star
    Ancient Membership 2,500 Likes 2500 Comments Photogenic
    Lauren Smith:
    JoAnn, Thank you for your quick response. To answer your questions... 1. Some of our donors do have more than one recurring gift, although right now they only have one ACTIVE recurring gift. 2. For this upgrade campaign, the recurring gifts currently go into one of two different funds. These funds would not be changed on the import back in. 3. The information that could be imported back in would include: Bio info, RG CC information, RG ammendment (to amount AND to split the appeal moving forward to track the upgrade success), Spouse Relationships, Address (including phone types), Actions, Appeal (to tab) I'm not sure of the 'number of consecutive payments missed' functionality. Right now we do record $0 gifts (where their CC failed, etc.) So would that get counted? I'm a bit scared at hearing of the issue with 'next transaction due date'. Were the errors there because your CRITERIA was 'next transaction due date' or because it was just in the OUTPUT? What RE version are you on? Cheers, Lauren

    (About the lack of paragraphs - I don't know.  Happens to others at times too.  Don't know why.)

    I'm not sure how $0 gifts would affect number of payments missed.  Might cause it not to work.  You'd just have to try it in a query and see what happens I guess.

    We're using 7.92 with all but very latest patch for mobile use. 

    On the problems with next transaction due - I was checking data for records with a next transaction due just to make sure all those with pledges were getting reminders. (We don't bill recurring gifts.)  Anyway, my only criteria was "Next transaction date is not blank".  I got results for recurring gifts made a number of years ago and terminated/completed a long time ago.  I was told that the status of terminated/completed does not affect/is not linked to payment schedule.  The only way to not have a next transaction due date was to go in each record and use creative dating to put in an end date prior to next transactin.  As they were ongoing recurring gifts, we did not have end dates entered on the gift records. 

    So when I was talking to support I asked why it wasn't pulling any recent recurring gifts that were terminated/completed that did not have end dates either.  It should have pulled them all.  It didn't pull anything recent.  They could not give me any reason.  We did screen shares looking at older gifts and recent ones.  Spent way too much time on it.  Was quite frustrating.  Just do a simple query and see what you get for next transaction dates and/or number of transactions missed.

    You've got an awful lot of data types to attempt in one import.  Pretty ambitious.  Not even sure if you could do it in one.  Am guessing you'd be creating new and updating current.  If you're updating addresses you may lose your current data. 

    Our org would not do CC information in excel or an outside program due to PCI security issues with the CC #s.  And we would probably enter new RG on records as needed vs. an import.

    I just pulled out my RE import guide to check a couple things.  If your spouses have their own RE records you can not update their info by relationship import.  Guide also says that importing recurring gifts can only do individuals, even though you're importing RG, if you choose Import Recurring Gifts, you are limited in fields available.  For more detailed info, use Import, but guide says several import files may be required.  Here's link to the Import Guide: https://www.blackbaud.com/files/support/guides/re7/import.pdf

    Best of luck.

  • Hello!

    I appreciate the ideas in this forum, and have a related question: Is there an easy way to sychonrize the Scheduled Date with the Gift Date in RG's? Somehow our Scheduled Dates are out of synch, sometimes by years. Appreciate your thoughts. Cheers, Jane

Categories