Annual campaign database lists

beth cupp

I am preparing my first annual campaign appeal using RE. I like to have multiple databases such as the ones below, while also showing their total giving amount (sometimes i have issues with the soft-credits for the spouses/for soft-credits for payroll deductions, etc.) for each year

1. All donors who have given to the annual campaign in the last 3 years, regardless of amount given
2. All major donors (defined as amount they give), which is taken out of list 1
3. People who have given to a campaign other than the annual campaign
4. Lapsed donors who gave in the past, but not the last three years
5. People who have never given to my organization, but were added to the list after a certain date (we have a lot of people who are on our list from years ago when we acquired other org lists and i don't want to bother snail mailing to them, but if they're new to my database and haven't given, i do want to solicit them)

Does anyone have any recommendations on how you set up queries for lists such as these (or if you have any other recommendations/feedback on how you do it at your org)? I know this is a huge, loaded question, but any advice is appreciated. Thanks! Beth

Jen Claudy

If you are asking how to go about compiling one big mailing list with these specific parameters, this is what I do:



I set up an Appeal record with a Package for each of these segments.  The Packages are listed in a heirarchical order and prefixed with a number to keep them sorted properly and for easier data entry on the backend (when the gifts start flooding in!).  I then set up a Constituent Attribute for this Appeal, which will later be removed from the database.  On the Attribute, be sure to check the "Allow only 1 per record" box.



Next is tagging constituent records.  I start with Package 1, write a query to catch those records and Globally Add the Attribute (not the Appeal) to those records, putting something in the Description, Date, and Comment fields (because something, even "." makes Global Changes much easier if needed).  Move on to Package 2, 3, etc.  Using Attribute means that when you Globally Add for the 2nd segment of records, anyone who already has the Attribute will be an exception.  Appeals will let you add multiple, and that creates problems or means that you have to add criteria to your Query, and that can get confusing.



I try to keep my Queries simple, even if it means doing multiple Global Adds for the same segment.  I.e. if all Board members and all Employees are supposed to be in Package 2, if they aren't already on the list in Package 1, then I query for Board members and Global Add, then change and query for Employees and Global Add again.  Cuts down on potential errors where someone is included or excluded from the list and should have been the opposite.



You can also use Global Change to change, add, or delete an Attribute.  And if you have to, you can delete it all and start over.  Attributes are easier than Appeals here, too, because you can't Globally Change a Package, or add one after the Appeal tag is on the record.



Once the whole list is tagged with Attributes, then you start culling the list.  I use MS Access, because I do this a lot and have built a system that works for me and automates a lot of the steps I take.  But you can do it with Global Change within RE as well.  Don't delete the Attribute, just change the Description from whatever Package was initially assigned to "X" or something to indicate this record should be excluded.



Again, run a series of Queries and Global Changes, and I usually add a Comment of why the record is being removed from the list.  Look for solicit codes, your soft-credit issues, HOH/Spouse (what I call Secondary Records) instances, etc.  (You can have Mail remove those secondary records, but as you'll see, I have a reason for not doing this.)



Once you are at a point where your list is as clean as you can get it, export it to Excel and share it with your fundraisers/solicitors or anyone who might need to review it.  Include a second list of those you've already pulled (and include the reason/comment as an FYI) so if someone questions why a particular person isn't on the list, that person will (hopefully!) be on that second list.



Edit the list per feedback from those reviewing it, and then export the clean list and run your mail merge.  After the mailing is out the door, you can use either Global Add or Import to convert those Attributes to Appeal tags and then delete the Attributes from your database.  I like to put the Appeal tag on all of the records, even those that were pulled (including those secondary records), because later on, if someone asks why a particular person didn't receive the mailing, you can look it up and have an answer, because the Appeal is on their record with the reason why.  I use a Package of ID "DNM" and Description "Do Not Mail" and leave the number solicited as 0 or null.



Hope this helps...and is along the lines of what you are asking!

Joshua Bekerman bCLO bCRE

Before I jump into this one it would be good to know how you will be using the information.  Are you looking for mailing lists as Jennifer mentioned, or perhaps just a report to keep an eye on the different segments, or ...?



Thanks.

 

JoAnn Strommen

Ditto, Josh's question. Knowing how you plan to use info would be helpful.   Explaining how to do each query can take time.  Do you have some experience doing queries in RE?  While I'm sure the process Jennifer describes works for what her org needs, it sounds much more involved that what we would do or what we would need.  That's why knowing what your end goal is can guide responses.







 

Christine Cooke 2

Agree with Josh and Joann, we need to know end goal/use.  There are a few different ways to tackle this, but it depends on what you are doing with the info and what info needs to come out of RE. 

beth cupp

Christine Cooke:

Agree with Josh and Joann, we need to know end goal/use.  There are a few different ways to tackle this, but it depends on what you are doing with the info and what info needs to come out of RE. 

Thank you all for your responses, and you are absolutely correct! I think I need to spend a little more time with my data before configuring a good question and it's good to know you are all out there when I'm ready. Thanks!