Export criteria for lapsed donors

Karina A

I'm to pull a lapsed donor list with last gift date of 2014.  When exporting the data I'm using Last gift with a criteria of gifts dated between 01/01/2014 and 31/12/2014.  When I pull the data, individuals are being pulled that have given a gift in 2015 and 2016.  Any ideas on what I'm missing in the criteria when trying to export?

Thanks!

Kristine Marvin

Karina A:

I'm to pull a lapsed donor list with last gift date of 2014.  When exporting the data I'm using Last gift with a criteria of gifts dated between 01/01/2014 and 31/12/2014.  When I pull the data, individuals are being pulled that have given a gift in 2015 and 2016.  Any ideas on what I'm missing in the criteria when trying to export?

Thanks!

You can add a crierita that says "and" gift date "not between" 2015 adn 2016. 

John Heizer

Karina A:

I'm to pull a lapsed donor list with last gift date of 2014.  When exporting the data I'm using Last gift with a criteria of gifts dated between 01/01/2014 and 31/12/2014.  When I pull the data, individuals are being pulled that have given a gift in 2015 and 2016.  Any ideas on what I'm missing in the criteria when trying to export?

Thanks!

What's your source Query for the Export?  Export will provide results for all Constituents in the source Query regardless of whether they match criteria in other parts of the Export (such as Last Gift).  The Last Gift criteria in the Export output will result in only Gifts that fall in that criteria, but you'll still get a record for that Constituent because they exist in the source Query.


Another thing to note; "Last Gift" behaves differently between Constituent Query and Constituent Export.  In Query it will only look at the most recent Gift and see if that Gift matches the criteria given for Last Gift.  In a Constituent Export it will pull the most recent Gift that matches the criteria given for Last Gift.

JoAnn Strommen

Wow, John. That small difference could really mess with data!  Thanks for sharing that.


Now, my question is how to remember that for the rare occasion when we look at last gift data. Am guessing it will be looked at much more for us in the future.

Karina A

John Heizer:

 

Karina A:

I'm to pull a lapsed donor list with last gift date of 2014.  When exporting the data I'm using Last gift with a criteria of gifts dated between 01/01/2014 and 31/12/2014.  When I pull the data, individuals are being pulled that have given a gift in 2015 and 2016.  Any ideas on what I'm missing in the criteria when trying to export?

Thanks!

What's your source Query for the Export?  Export will provide results for all Constituents in the source Query regardless of whether they match criteria in other parts of the Export (such as Last Gift).  The Last Gift criteria in the Export output will result in only Gifts that fall in that criteria, but you'll still get a record for that Constituent because they exist in the source Query.


Another thing to note; "Last Gift" behaves differently between Constituent Query and Constituent Export.  In Query it will only look at the most recent Gift and see if that Gift matches the criteria given for Last Gift.  In a Constituent Export it will pull the most recent Gift that matches the criteria given for Last Gift.

 

My soure query was anyone who has given gifts over $20.  I didn't put a date on those gifts as I thought I could specify in the export criteria?  So my question is... I just want to see those who haven't given a gift since 2014.  Should I still be using Last gift date between Jan 1, 2014 and December 31, 2014 or should I be giving it different criteria?

JoAnn Strommen

To me, if you "want to see those who haven't given a gift since 2014," your last gift criteria should be less than 1/1/2015 or equal or less than 12/31/14.  By using a date range you won't pull the person who made their last gift in 2013 or earlier etc.  

Melissa S Graves

Whenever I am doing a lapsed donor query I use summary information.


Summary total = 0 (filtered on dates between which you want these donors to have no giving) AND

Summary total >=.01 (filtered on dates between which you expect these donors to have given)


You can add other filters in each one if you want to limit their giving to annual fund, certain gift types such as just cash, etc.

Alan French

JoAnn Strommen:

To me, if you "want to see those who haven't given a gift since 2014," your last gift criteria should be less than 1/1/2015 or equal or less than 12/31/14.  By using a date range you won't pull the person who made their last gift in 2013 or earlier etc.  

Be aware that if you do this in the query, you will lose constituents who have any gift record after this date, including unfulfilled pledges or recurring gifts. If you're looking for lapsed donors, I'm guessing you'd want to retain some of those people in your results.

Melissa S Graves

Melissa Graves:

Whenever I am doing a lapsed donor query I use summary information.


Summary total = 0 (filtered on dates between which you want these donors to have no giving) AND

Summary total >=.01 (filtered on dates between which you expect these donors to have given)


You can add other filters in each one if you want to limit their giving to annual fund, certain gift types such as just cash, etc.

LMAO - I guess when you misspell "doing" in the community it can some out to be a "banned" word and it came out "d***". Bahhhhh

John Heizer

Melissa Graves:

 

Melissa Graves:

Whenever I am doing a lapsed donor query I use summary information.


Summary total = 0 (filtered on dates between which you want these donors to have no giving) AND

Summary total >=.01 (filtered on dates between which you expect these donors to have given)


You can add other filters in each one if you want to limit their giving to annual fund, certain gift types such as just cash, etc.

LMAO - I guess when you misspell "doing" in the community it can some out to be a "banned" word and it came out "d***". Bahhhhh

 

Thanks for the laugh!!!

John Heizer

Karina A:

 

John Heizer:

 

Karina A:

I'm to pull a lapsed donor list with last gift date of 2014.  When exporting the data I'm using Last gift with a criteria of gifts dated between 01/01/2014 and 31/12/2014.  When I pull the data, individuals are being pulled that have given a gift in 2015 and 2016.  Any ideas on what I'm missing in the criteria when trying to export?

Thanks!

What's your source Query for the Export?  Export will provide results for all Constituents in the source Query regardless of whether they match criteria in other parts of the Export (such as Last Gift).  The Last Gift criteria in the Export output will result in only Gifts that fall in that criteria, but you'll still get a record for that Constituent because they exist in the source Query.


Another thing to note; "Last Gift" behaves differently between Constituent Query and Constituent Export.  In Query it will only look at the most recent Gift and see if that Gift matches the criteria given for Last Gift.  In a Constituent Export it will pull the most recent Gift that matches the criteria given for Last Gift.

 

My soure query was anyone who has given gifts over $20.  I didn't put a date on those gifts as I thought I could specify in the export criteria?  So my question is... I just want to see those who haven't given a gift since 2014.  Should I still be using Last gift date between Jan 1, 2014 and December 31, 2014 or should I be giving it different criteria?

 

With that source Query you'll get a record output for every Constituent who has given gifts over $20, but the Last Gift field may be blank if they don't have Gifts within the date range specified in the Last Gift criteria.  Criterial added at the Export level will only filter data specific to the field being filtered, it won't add or subtract any "base" reccords (Constituents for a Constituent Export, Gifts for a Gift Export, etc.).


Melissa's method using summary information in the Query is the most reliable way to go.  Using summary information lets you add reliable Gift filters in a Constituent Query and avoids the problems with Last Gift in Query looking at only the most recent gift to see if it matches criteria.

John Heizer

JoAnn Strommen:

Wow, John. That small difference could really mess with data!  Thanks for sharing that.


Now, my question is how to remember that for the rare occasion when we look at last gift data. Am guessing it will be looked at much more for us in the future.

Yeah, it's really easy to find yourself with mis-matched results between your Query and Export output.  I try to avoid ever using First Gift or Last Gift in Query and, as Melissa suggested, use summary information instead.