Sorting out Foundation/Company vs. Individual Giving

Options
I am sure there are discussions about this because we can't be the only organization with this issue, but none of the searches I thought of yielded the results I was looking for, so I'll just go ahead and reinvent the wheel.


Many of our top donors actually do much of their giving through multiple different entities, all of which we have records for in RE:--sometimes multiple foundations and a company or two. I am looking for a way to easily sort this kind of thing out for when we pull mailing lists or giving reports for internal use--I don't want to send a mailing to all of the entities through which they donate, and I need to make sure giving levels are accurately counted and reported on without dupes or overlapping.


I could probably handle this by including Soft Credits and crediting them to both the Donor and Recipient, and include fields to show this information, but I know there are some cases where soft-credits aren't done, and if I am under the gun, I need something quicker.


My initial thought is to use Attributes to assign a code to each person and their various entities, and using that to match things up once the list is pulled. I'm sure there are smarter solutions, though. What do others do?

Comments

  • I have, essentially, a Solicit Code for these folks.  Then, I can check to be sure the Individual is on the list already, and not add the Organization.  Unless there's another contact at the Organization that should get the mailing.  This is partly why I use Attributes for Solicit Codes, because I can add a Date and a Comment.


    My system still requires a little bit of manual work, but the added bonus of making me very familiar with the names involved, which means it goes faster each time.  To speed a manual review, you can start the Comment Field text with the Const ID of the Individual and the Const ID of the Organization (i.e. Robert is the Individual, c456789, and ABC Foundation is the Organization, c123456, so the Attribute (Solicit Code) Comment on both records would start with "c456789+c123456".  Then you can sort by that field and it will put them together in the list.
  • Daniel Noga:

    I am sure there are discussions about this because we can't be the only organization with this issue, but none of the searches I thought of yielded the results I was looking for, so I'll just go ahead and reinvent the wheel.


    Many of our top donors actually do much of their giving through multiple different entities, all of which we have records for in RE:--sometimes multiple foundations and a company or two. I am looking for a way to easily sort this kind of thing out for when we pull mailing lists or giving reports for internal use--I don't want to send a mailing to all of the entities through which they donate, and I need to make sure giving levels are accurately counted and reported on without dupes or overlapping.


    I could probably handle this by including Soft Credits and crediting them to both the Donor and Recipient, and include fields to show this information, but I know there are some cases where soft-credits aren't done, and if I am under the gun, I need something quicker.


    My initial thought is to use Attributes to assign a code to each person and their various entities, and using that to match things up once the list is pulled. I'm sure there are smarter solutions, though. What do others do?

    Also, if you pull your mailing lists out of mail, you do have the option to send only one piece of mail to the constituent (rather than one to their home, one to their foundation and another to their business).  I can't check which tab that is right now because I'm waiting on a mail process to run at the moment, but I believe it is on the Organizational Address tab.  However, this will address all constituents on the mailing list the same way, so it may or may not work for your org.

  • Jen Claudy:

    I have, essentially, a Solicit Code for these folks.  Then, I can check to be sure the Individual is on the list already, and not add the Organization.  Unless there's another contact at the Organization that should get the mailing.  This is partly why I use Attributes for Solicit Codes, because I can add a Date and a Comment.


    My system still requires a little bit of manual work, but the added bonus of making me very familiar with the names involved, which means it goes faster each time.  To speed a manual review, you can start the Comment Field text with the Const ID of the Individual and the Const ID of the Organization (i.e. Robert is the Individual, c456789, and ABC Foundation is the Organization, c123456, so the Attribute (Solicit Code) Comment on both records would start with "c456789+c123456".  Then you can sort by that field and it will put them together in the list.

    I like this solution. Manual work I don't mind so much, as long as it's clear-cut, straightforward and something I could theoretically put into a written procedure that someone new could follow with reliable results if I ever get hit by a bus. The thing that really makes me nervous is missing someone accidentally, and this procedure safeguards against that (I know a lot of this info offhand, but in no way do I have this memorized for every case in RE:) 


    Question: We have one or two donors who give through more than one entity, so if we did that, we'd have to use multiple Constituent Codes. Have you come across that as well? Would you just use a three-code sequence instead of two-code?

  • If you have one Individual giving thru multiple Organizations, I would just use the same format on the Org records and either a string of IDs on the Individual or just leave it with only the Ind Record's ID, as that's what the sort happens on.  Theoretically, all Records are linked by Relationships, so you could simply use the ID from the Individual record and that's it.
  • Jen Claudy:

    If you have one Individual giving thru multiple Organizations, I would just use the same format on the Org records and either a string of IDs on the Individual or just leave it with only the Ind Record's ID, as that's what the sort happens on.  Theoretically, all Records are linked by Relationships, so you could simply use the ID from the Individual record and that's it.

    I'm a bit confused now, or just misunderstanding--what's the difference between how I apply this on the Org record(s) vs. the Ind. record? I'm also not sure how Solicit Codes come into play or how the leap is made from there to Attributes (though I assumed you meant to write Attribute since you say you are using Attributes in the same way that others use Solicit Codes) After reading your first response, the picture I had in mind was:

     
    • I add an Attribute called [whatever], and in the Comments field, I basically include all the Constituent Codes of whatever records I need to bundle together as one entry, and I repeat this process on all records concerned.
    • Then when I pull lists, I sort by this column to group all associated records together, decide which one I want to keep, and delete the rest (or consolidate the giving into one line in the case of internal giving reports)
    Am I missing something?


    I am hesitant at this point to rely on Relationship records, as that's probably the part of the RE: system I've inherited that has been most inconsistently-managed in the past. This solution would be a stop-gap measure until I can get those cleaned up, and it also looks like it will serve the dual function of serving as a good blueprint for said cleanup.
  • Find a group of records, including, for this example, Robert Hernandez and ABC Foundation.  I would use Import for this.  Export your data either via Query, Export, or Import's Create an Import feature.  In Excel, add a column for Attribute Category (either a new Attribute or an existing one), a column for Attribute Description (i.e. "Individual" or "Organization"), a Date if desired, and then a column for Attribute Comment.  That's the one that will take a bit to set up.  In my example, the Import File would look something like:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456


    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones


    Once back in your database, you can include this Attribute, and then Sort by the Comment then by Description, and get this:


    Robert Hernandez   c456789+c123456   Individual

    ABC Foundation   c456789+c123456   Organization


    Now you can easily tell that Robert should be on your list but ABC Foundation should not.


    With Robert sometimes giving via ABC Foundation and sometimes via XYZ Fund, it would like like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456+c987654

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789+c987654


    OR like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789


    Christine is correct, Mail will do a decent job of this for you.  But I have other issues with Mail and tend not to use it...so this is my workaround.  =)  Does this make any more sense?
  • Jen Claudy:

    Find a group of records, including, for this example, Robert Hernandez and ABC Foundation.  I would use Import for this.  Export your data either via Query, Export, or Import's Create an Import feature.  In Excel, add a column for Attribute Category (either a new Attribute or an existing one), a column for Attribute Description (i.e. "Individual" or "Organization"), a Date if desired, and then a column for Attribute Comment.  That's the one that will take a bit to set up.  In my example, the Import File would look something like:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456


    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones


    Once back in your database, you can include this Attribute, and then Sort by the Comment then by Description, and get this:


    Robert Hernandez   c456789+c123456   Individual

    ABC Foundation   c456789+c123456   Organization


    Now you can easily tell that Robert should be on your list but ABC Foundation should not.


    With Robert sometimes giving via ABC Foundation and sometimes via XYZ Fund, it would like like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456+c987654

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789+c987654


    OR like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789


    Christine is correct, Mail will do a decent job of this for you.  But I have other issues with Mail and tend not to use it...so this is my workaround.  =)  Does this make any more sense?

    Crystal clear!


    I'm with you on Mail--I don't use it. I'm rarely asked for "Just a mailing list" anyway--I almost always need to include some ancillary data for the Fundraising folks to use in further refining whatever I pull for them.

  • We use Campaign and Appeal for this, as the type of "Giving Vehicle" is often closely tied to the gift's purpose and how we received it.


    For example, "Campaign ID = Donor-Directed; Appeal ID = EMP" would likely be a Matching Gift Company's contribution (Benevity, TRUIST, etc). These gifts are soft-credited to the original donor/employee so we can Query later: "How much $ in EMP did we receive?  How many participating corp's?  How many indv. gifts?"


    "Campaign ID= Donor-Directed; Appeal ID = GRANT" would likely be a Community Foundation that issues grants and introduces donors to our org. but on an anonymous level. They tax-receipt and forward our correspondence but the family fund's direct contact is usually never given to us.


    So if Dr. Robert Hernandez gave $50 personally, then his Matching Gift Company matched the $50, and then his Family Foundation disbursed $15,000 annually to us, we would just need to set up  a Query to narrow down Dr. Hernandez' giving, then MAKE SURE one includes the Soft-Credit fields so you can differentiate between the three gift types.  


    Hope that gives you another way to look at it!
  • Daniel Noga:

    I am sure there are discussions about this because we can't be the only organization with this issue, but none of the searches I thought of yielded the results I was looking for, so I'll just go ahead and reinvent the wheel.


    Many of our top donors actually do much of their giving through multiple different entities, all of which we have records for in RE:--sometimes multiple foundations and a company or two. I am looking for a way to easily sort this kind of thing out for when we pull mailing lists or giving reports for internal use--I don't want to send a mailing to all of the entities through which they donate, and I need to make sure giving levels are accurately counted and reported on without dupes or overlapping.


    I could probably handle this by including Soft Credits and crediting them to both the Donor and Recipient, and include fields to show this information, but I know there are some cases where soft-credits aren't done, and if I am under the gun, I need something quicker.


    My initial thought is to use Attributes to assign a code to each person and their various entities, and using that to match things up once the list is pulled. I'm sure there are smarter solutions, though. What do others do?

    About how many donors do you think fall into this category?  I think that how you develop your system should really be based on how large this issue actually is and how much time you'll spend manipulating data outside of RE.  I've been thinking about the different RE setups I've used as a DBA and as a consultant and for us, using SC in reports and exporting mailings via Mail (to use the suppression there - on the Org tab, open the contact info box), but we've never had more than 20-30, so I have been able to use those tools and spot check them afterwards.  But, if you have a lot of them or if you have really specific requirements (such as some of them want the mail to come to their homes while others want it to their foundations, etc.), then I think you'll need to create a system similar to Jen's and manipulate the data accordingly. 

  • Jen Claudy:

    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones

    Christine McMinn:

    I can't check which tab that is right now because I'm waiting on a mail process to run at the moment, but I believe it is on the Organizational Address tab.

    Slightly off topic, but as it's come up several times on this particular thread: did you know that you can open up a second copy of Raiser's Edge and continue working normally in the new window while your import/export/global change continues processing? I found that out recently at a Blackbaud user group and it's been very useful!


     

  • Christine McMinn:

    About how many donors do you think fall into this category?  I think that how you develop your system should really be based on how large this issue actually is and how much time you'll spend manipulating data outside of RE.  I've been thinking about the different RE setups I've used as a DBA and as a consultant and for us, using SC in reports and exporting mailings via Mail (to use the suppression there - on the Org tab, open the contact info box), but we've never had more than 20-30, so I have been able to use those tools and spot check them afterwards.  But, if you have a lot of them or if you have really specific requirements (such as some of them want the mail to come to their homes while others want it to their foundations, etc.), then I think you'll need to create a system similar to Jen's and manipulate the data accordingly. 

     

    There are a few issues I need to take into consideration as I formulate policies and procedures. I don't think we have much more than 20 or 30 instances of this, either.

    One issue I have to contend with is that there appears to have been no special rhyme or reason to how/why a lot of things have been done in the system I inherited. It changed hands several times over the years with no written P&P to keep things in line, and each of the previous admins apparently started doing things in whatever their style dictated, without bothering to bring existing records into conformity with that or to record why they did things the way they did. If there was any sort of verbal consensus forged with other staff members at the time, nobody currently on staff remembers it happening.


    Unfortunately, one of those arbitrary areas is the application of Soft-Credits. Aside from a couple of circumstances, we can't see a pattern as to when and why they were used, and there are many cases in which they could be useful in addressing the issues I brought up in this thread, but the Soft-Credits simply never happened. As far as we can tell, they were basically only done in cases where a pledge was recorded on one record, but the payment came from the person's company instead of the individual, etc. Not reliable enough for me.


    There is a lot I am having to scrap and totally rebuild, including all of our Campaign/Fund/Appeal coding. It's a nightmare, but what a notch in my belt it will be when it's all fixed! Learning experience FTW!


    Because so much is in flux and so much will have to be torn down and rebuilt, I think Jen's approach will be very helpful. The inconsistency of our data is another reason I currently avoid using Mail. It's a very helpful module when a system is in good working order, but...Garbage In, Garbage Out.


    Forgive my overblown ranting, but seriously!!!

  • Alan French:

    Jen Claudy:

    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones

    Christine McMinn:

    I can't check which tab that is right now because I'm waiting on a mail process to run at the moment, but I believe it is on the Organizational Address tab.

    Slightly off topic, but as it's come up several times on this particular thread: did you know that you can open up a second copy of Raiser's Edge and continue working normally in the new window while your import/export/global change continues processing? I found that out recently at a Blackbaud user group and it's been very useful!


     

     

    How would that work with a limited number of users?

  • Christine McMinn:

    Alan French:

    Jen Claudy:

    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones

    Christine McMinn:

    I can't check which tab that is right now because I'm waiting on a mail process to run at the moment, but I believe it is on the Organizational Address tab.

    Slightly off topic, but as it's come up several times on this particular thread: did you know that you can open up a second copy of Raiser's Edge and continue working normally in the new window while your import/export/global change continues processing? I found that out recently at a Blackbaud user group and it's been very useful!


     

     

    How would that work with a limited number of users?

     

    I don't think it counts against the user limit. It just opens an additional instance of the program that, I believe, uses the same user slot. We usually operate at our user limit and I've opened extra windows accidentally many times.

  • Daniel Noga:

    I am sure there are discussions about this because we can't be the only organization with this issue, but none of the searches I thought of yielded the results I was looking for, so I'll just go ahead and reinvent the wheel.


    Many of our top donors actually do much of their giving through multiple different entities, all of which we have records for in RE:--sometimes multiple foundations and a company or two. I am looking for a way to easily sort this kind of thing out for when we pull mailing lists or giving reports for internal use--I don't want to send a mailing to all of the entities through which they donate, and I need to make sure giving levels are accurately counted and reported on without dupes or overlapping.


    I could probably handle this by including Soft Credits and crediting them to both the Donor and Recipient, and include fields to show this information, but I know there are some cases where soft-credits aren't done, and if I am under the gun, I need something quicker.


    My initial thought is to use Attributes to assign a code to each person and their various entities, and using that to match things up once the list is pulled. I'm sure there are smarter solutions, though. What do others do?

    I would make a relationship between each entity and the individual - so you know why they are there (part of the trail).  If you are putting the gift on the entity, then you soft credit the individual and their spouse. 

    In order not to mail to all of these entities I would flag a Do No Mail and/or Do No Solcit code on those records and uncheck the Send Mail so they do not show up on mailing lists.


    If all of the $$$ are supposed to show up for the individuals, then when you pull the reports you should be asking for just the soft credit recipents for soft credits and not the donor


    my two cents
  • What we've done in the past is for these specific gifts where a constituent is giving through an organization., we assign a specific gift attribute. This serves to 'mark' that particular gift so it can be treated separately.


    In your gift query, you can choose to exclude gifts with this attribute an create a mailing list for these -- and then you can create another query of just these gifts and treat them separately (where you can decide if you're going to mail/not mail to all constituents associated with the gift.


    This way it is gift-specific - vs. creating a relationship between a constituent and the organization -- that may not apply to each gift given by the constituent.  So somehow indicating on the gift seems like a better solution.

  • Does anyone know if you can be in two different hosted accounts at
    the same time? I’ve had times when I’m in one client’s hosted
    account and need to open other client’s hosted account. Out of
    habit, I’ve made a practice of closing the first account before
    opening the second. Has anyone tried to open two hosted accounts at
    the same time?


     


    Best,


     


    Lauren


     





  • Gina Gerhard:

    What we've done in the past is for these specific gifts where a constituent is giving through an organization., we assign a specific gift attribute. This serves to 'mark' that particular gift so it can be treated separately.


    In your gift query, you can choose to exclude gifts with this attribute an create a mailing list for these -- and then you can create another query of just these gifts and treat them separately (where you can decide if you're going to mail/not mail to all constituents associated with the gift.


    This way it is gift-specific - vs. creating a relationship between a constituent and the organization -- that may not apply to each gift given by the constituent.  So somehow indicating on the gift seems like a better solution.

    Gina, that actually sounds like a really great idea to also include a Gift Attribute for each individual gift that comes through in this way!  I can see circumstances where you aren't able to access SC information, but you CAN pull Attributes nearly all of the time.  I think I may sit down and brainstorm a few Gift Attributes that would help keep our Giving Histories a little cleaner.  :)  Even if we primarily use Campaign/Appeal to designate these gifts, adding an Attribute won't hurt anything!  :)

  • Lauren Schler, CFRE, bCRE-Pro:

    Does anyone know if you can be in two different hosted accounts at the same time? I’ve had times when I’m in one client’s hosted account and need to open other client’s hosted account. Out of habit, I’ve made a practice of closing the first account before opening the second. Has anyone tried to open two hosted accounts at the same time?

    Best,

    Lauren


    I just tested and successfully opened two instances of the same Hosted RE:7 at one time.  Looked at the Database Connection Viewer and it just lists me once.  As far as opening two different databases simultaneously, I think if you can manage to keep them straight, it wouldn't be a problem, as you'd be logged in via two different Citrix receivers, and I do that every time I access our Finance software.


    There is also this handy little button (pictured below) that allows you to toggle back and forth between different areas without losing your place.  Click this, and a separate (in this example, Records) window opens and you can move to, say, Config and maintain access to Records.  Doesn't help when running an Import, but it's saved me time and clicks quite often.

    155a24ff4de1e70b532f30203ea4b32d-huge-ca

     

  • Hi,


    Yes you can have two different hosted accounts.


    Thank you.

    Mridula

    Lauren Schler, CFRE, bCRE-Pro
    :

    Does anyone know if you can be in two different hosted accounts at the same time? I’ve had times when I’m in one client’s hosted account and need to open other client’s hosted account. Out of habit, I’ve made a practice of closing the first account before opening the second. Has anyone tried to open two hosted accounts at the same time?

     

    Best,

     

    Lauren

     


     


     

     


  • Thanks so much Jen. That’s a great tip!


     





  • Jen Claudy:

    Find a group of records, including, for this example, Robert Hernandez and ABC Foundation.  I would use Import for this.  Export your data either via Query, Export, or Import's Create an Import feature.  In Excel, add a column for Attribute Category (either a new Attribute or an existing one), a column for Attribute Description (i.e. "Individual" or "Organization"), a Date if desired, and then a column for Attribute Comment.  That's the one that will take a bit to set up.  In my example, the Import File would look something like:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456


    * please note, these are incorrect Field Names, I'm in the midst of a Global Change and can't go look up the correct ones


    Once back in your database, you can include this Attribute, and then Sort by the Comment then by Description, and get this:


    Robert Hernandez   c456789+c123456   Individual

    ABC Foundation   c456789+c123456   Organization


    Now you can easily tell that Robert should be on your list but ABC Foundation should not.


    With Robert sometimes giving via ABC Foundation and sometimes via XYZ Fund, it would like like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789+c123456+c987654

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789+c123456

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789+c987654


    OR like this:


    ConstName   ConstID   AttrCat   AttrDesc   AttrDate   AttrComment   *

    Robert Hernandez   456789   Giving Vehicle   Individual   8/2/2016   c456789

    ABC Foundation   123456   Giving Vehicle   Organization   8/2/2016   c456789

    XYZ Fund   987654   Giving Vehicle   Organization   8/2/2016   c456789


    Christine is correct, Mail will do a decent job of this for you.  But I have other issues with Mail and tend not to use it...so this is my workaround.  =)  Does this make any more sense?

    I just wanted to check back in here and add that this coding scheme contributed by Jen is serving another useful purpose for us: Easily putting together consolidated giving histories. For strictly accounting purposes, it's important to keep these separate records in RE: to make it clear which specific entities our gifts are coming from, but Development takes a different view of things. If Jane Doe is a CEO and gives individually, through a Foundation and through her company, that's basically all the same donor in our eyes. When I create reports of, say, top donors for a given FY, our people don't want to have to pick out the different giving entities to find out how much giving Jane Doe is ultimately responsible for. This set of Attributes makes it very easy to merge all of her line items into one figure. Thus I find that even if we eventually get our system clean enough to start using Mail more reliably, I will keep this in place for that purpose.


    Thanks again, Jen!

Categories