Hi,
I'm doing my first solicitation mailing since using Tessitura. I'm good with overall list, but really want to include the donor's last gift amount in my letter and response form, so therefore want it showing, I assume, in the output set (or extraction manager?)
This seems a standard type of requirement for a solicitation mailing...but I can't see in the list of options in the contributions heading of the output set any option for last gift. Love to hear from anyone out there who has done this for pointers in the right direction!
Cheers
Steff
Good morning Stephanie,
We have this as an Output Set Element, as well as a few other riffs/customizations on it.
To get the most basic last gift amount information, you'll want to enter the following in TR_QUERY_ELEMENT:
That will get you just the amount of whatever the last contribution amount was for a donor.
We also have additional values for the Campaign, Date, and Fund of the last gift. In those cases, you can use the same Category and Data From values, and just replace the Data Select with:
Hope this helps!
Thank you,
Brian
Thank you Brian! My eyes are going a little cross-eyed but I will have a go in the next few days and let you know how I go!
Ok, I was excited so I have given it a go! I added the four new output set elements, then created an output set including them. It came up with a note saying that more than expected would come up, and then an error message. Do I need to do something different other than Create a new Output set, or within it?
Sorry, I mean Execute an Output Set...
Try checking the "Single Row" checkbox in TR_QUERY_ELEMENT; I glossed over that in my response earlier. That should limit to just a single value per customer ID.
Aha, will try that, thanks!
Hi Brian...tried this straight away, had trouble and only now back looking at it. when I run this I get an error message attached, basically saying that lvs_giftstats is an invalid object name...any thoughts??
Well darn! I almost wonder if lvs_giftstats is a view that needs to be installed in your database then. I did not do the initial configuration, I just benefitted from it being available to use.
Hi there .. lvs_ object means it's a local view and not standard views. Brian, if you have access to the database via SSMS you could script out a create to file of the view to pass along for Stephanie to run. Stephanie - you'll just need to be sure to add permissions on the view to impusers in order to use it.
I would love to have this as well...Brian Parker (Past Member)did you have any luck pulling the file of the view?
Hey Stephanie Kemp (Past Member), ;
Sorry for being way behind on this! We've been in some beta testing lately and this fell off my desk completely.
USE [impresario] GO /****** Object: View [dbo].[LVS_GIFTSTATS] Script Date: 9/11/2018 9:32:26 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO /* 6/22/2015 by Matthew Echert View to compile the following stats for export for iWave batch screening: -largest gift amount -largest gift date -last gift amount -last gift date -first gift amount -first gift date Output set elements already exist for these two: -Total Gift Count (Lifetime # of Gifts) -Total Gift Amount (Lifetime Total Giving) 8/6/15 changed to use VS_CONTRIBUTION instead of LVS_CREDIT_CONT -ME */ CREATE VIEW [dbo].[LVS_GIFTSTATS] AS WITH lgift_amt AS ( SELECT customer_no , MAX(cont_amt) AS amt --largest gift amount per cust FROM VS_CONTRIBUTION WHERE customer_no <> 0 GROUP BY customer_no ) , lgift_dt AS ( SELECT cc.customer_no , MAX(cont_dt) dt --date of most recent gift of largest amt FROM VS_CONTRIBUTION cc JOIN lgift_amt la ON cc.customer_no = la.customer_no AND la.amt = cc.cont_amt GROUP BY cc.customer_no ) , first_gift AS ( SELECT customer_no , MIN(ref_no) AS ref_no FROM VS_CONTRIBUTION WHERE cont_amt > 0 GROUP BY customer_no ) , last_gift AS ( SELECT customer_no , MAX(ref_no) AS ref_no FROM VS_CONTRIBUTION WHERE cont_amt > 0 GROUP BY customer_no ) SELECT la.customer_no , la.amt AS largest_gift_amt , ld.dt AS largest_gift_dt , lg_cont.cont_amt AS last_gift_amt , lg_cont.cont_dt AS last_gift_dt , cn.description AS last_campaign , fu.description AS last_fund , fg_cont.cont_amt AS first_gift_amt , fg_cont.cont_dt AS first_gift_dt , ca.description AS first_campaign , fd.description AS first_fund FROM lgift_amt la JOIN lgift_dt ld ON la.customer_no = ld.customer_no JOIN first_gift fg ON la.customer_no = fg.customer_no JOIN last_gift lg ON la.customer_no = lg.customer_no JOIN VS_CONTRIBUTION fg_cont ON fg.ref_no = fg_cont.ref_no JOIN VS_CONTRIBUTION lg_cont ON lg.ref_no = lg_cont.ref_no JOIN VS_CAMPAIGN ca ON ca.campaign_no = fg_cont.campaign_no JOIN VS_CAMPAIGN cn ON cn.campaign_no = lg_cont.campaign_no JOIN VS_FUND fd ON fd.fund_no = fg_cont.fund_no JOIN VS_FUND fu ON fu.fund_no = lg_cont.fund_no GO
Attached please find the SQL file for LVS_GIFTSTATS that I generated thanks to Rebecca Harriman's helpful direction.
Thank you, and apologies again!
Oh my word, thank you Rebecca and Brian, very much! I will have a go...likely not for a couple weeks but I really appreciate it! I am not a coder nor do we have a coder, but I will see what i can do...might need to check in on your meaning Rebecca re permissions, when I get to it :-)
This is fantastic! Ran the script and the outputs are working like a charm! Thanks so much, Brian Parker (Past Member)!!
Ok....WHERE do you run the script (I am not a coder...can you tell?)
Hey Stephanie,
Sorry for the delayed response here! I've been hosting my parents in town for the last week, so I've been away from the forums.
If you have access to the SSMS application when you log in to RAMP, you'll be running the script there for your database; you could just open that SQL file I shared in this thread and execute it, or copy-paste the code into the window and execute.