Querying and conditionalizing based on consecutive years giving?

Greg Sims

With donor retention and loyalty increasingly important, I would love to create an email campaign that recognized and thanked our donors for their years of consecutive giving. I'm envisioning an anniversary-like message with balloons that says "Happy 6 Year Anniversary!" -- with the consecutive years value pulled in dynamically.


Is there any way to use S-tags or E-tags or some other tag to have Luminate calculate # of consecutive years giving for a given constituent and insert into a message? We use Raiser's Edge and have the RELO plugin. The only solution I can come up with to accomplish this is very labor intensive -- creating multiple RE queries to find donors who've given for 1,2,3,4,etc. years, mapping each query to an offline group in LO, then using an S45 tag to create conditional content based on group membership. Our org has been around for 37 years. Creating 37 queries, groups, S-tag parameters is not realistic.


Any ideas on how to get at consecutive years giving? 

Brian Mucha

Here's one bad idea.


If you can already get this out of RE, you could periodically pull a report and import the number of years using a custom cons import, and store that value in a custom field on the cons record.


It's manual, and not always current. So, yeah.


 

Brian Mucha

Here's more bad idea. Assume we're going with using a custom field and an import.


Rather than 37 queries in RE, you can just pull a transaction report and use Excel or Access.


I'm sure there's a ton of ways to do this in Excel. You're gonna need some RAM though! I think maybe there's something in =FREQUENCY, but I'm not sure about that one.


I'd try to get to a sorted spreadsheet with CONS_ID and TRANS-YEAR. Easy to do with excel: =YEAR(TRANSACTION_DATE)

A        B     C
ConsId   Year  Count

123456   2010  

123456   2011  
123456   2011  
123456   2011  

123456   2012  

323457   2010  

323457   2014  
323457   2015  
323457   2015  

323457   2016  


Then get the unique rows. (Maybe you can get to here right out of RE?)

A        B     C
ConsId   Year  Count

123456   2010  

123456   2011  

123456   2012  

323457   2010  

323457   2014  

323457   2015  

323457   2016  


Then for Col C use a formula, starting at the second row (C3) in the data. (Hard code the first 0 in the counter column, cause we add 1 to it on the next row) Then fill the formula down the whole column.

=IF(AND(A3=A4, B4=B3-1), C2+1, 0)


EDIT: Rather then compare with the next row, I should have compared with the previous.

=IF(AND(A3=A2, B3=B2+1), C2+1, 0)


If ConsID3 = ConsId2 and Year3 = Year2 + 1 then add 1 to the previous row's counter value, otherwise reset to 0

A        B     C
ConsId   Year  Count

123456   2010  0

123456   2011  1  <--formula here and fill down

123456   2012  2

323457   2010  0

323457   2014  0

323457   2015  1

323457   2016  2


So now you have a spreadsheet with cons Ids and a counter of sequencial years.


Now just filter on col B = 2016 and export to CSV.


Or dump the whole thing in a pivot table and get the max value for column C. That's the longest number of years they ever gave in a row.



There's probably better ways in Excel, and I didn't test this of course. Too much data would probably mean Access instead. That is probably the real correct tool, but I haven't fooled in that in years.



EDIT: Another trick to make this more managable. When getting the initial data, you might be able to filter on the constituent's LAST_TRANSACTON_DATE. Presumably you'll only care about those that gave in the last year, otherwise they've already broken their run.

Greg Sims

Thanks for putting so much thought into this problem, Brian. I'll play around with your suggested solutions and see if I can make this doable. Am also going to add this as an Idea to vote on.