Gift Aid - seeking new declarations in bulk

Hi Chris

We have done a few Gift Aid drives and found them very worthwhile but we don't currently have Gift Aid declarations online or any way for customers to amend their Gift Aid status on the website so it was all postal but paid for itself through the increased amount we could claim.

Karen

Hi Chris

Our Development department have done this using Wordfly emails, as I have amended our LP_UPDATE_EMAIL_PROMOTION to look for click throughs on two urls (a yes and no) to quickly add a gift aid declaration to their accounts. As Wordfly passes in customer number etc mean customer doesn't need to login, the pages are static html as there is no need for code on the page and so Wordfly and the SP do all the work. Also add a check in to see if a customer was changign their declaration status and send an email to the dev team to investigate.

If @response_code = 4

Begin  

   If @url in ( 'http://www.nationaltheatre.org.uk/yestogiftaid','http://www.nationaltheatre.org.uk/notogiftaid')

   Begin

      if @url='http://www.nationaltheatre.org.uk/yestogiftaid'

      begin

         select @declare='Y'

      end

      else

      begin

         select @declare='N'

      end

      if not exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no)

      begin

         Execute @ga_decl_no = AP_Get_NextID_function 'GD'    

         insert into T_GIFTAID_DECLARATION(ga_declaration_no, customer_no,giftaid_type,eligible_ind,contact_method,document_status,recd_dt,start_dt, ineligible_reason)

         select @ga_decl_no,@customer_no,1,@declare,3,6,getdate(),dateadd(year,-4,GETDATE()), case when @declare='N' then 5 else 0 end

      end
     else

      begin

         if exists (select 1 from T_GIFTAID_DECLARATION (NOLOCK) where customer_no=@customer_no and eligible_ind<>@declare)

         begin

            select @subject='Email Gift Aid Declaration Eligible (' + @declare +') for customer '   + convert(varchar(10), @customer_no),

            @body = 'Customer '   + convert(varchar(10), @customer_no) + ' has submitted a second declaration but this time set Eligible to ' + @declare +'.

Please check this account.

Thank you'

            exec msdb.dbo.sp_send_dbmail 

            @recipients='devteam',

            @subject = @subject,

            @body = @body,

            @body_format = 'TEXT'

         end

      end

   End

End

Hi Mark,

I just found this answer and wow! Exactly what I was looking for – thank you!

Your help now would be amazing if you have a few minutes…

Basically, we are looking to do a very similar drive to secure retrospective Gift Aid declarations in bulk, by contacting all our UK customer who have made an online donation in the past 4 years but who for whatever reason, left the Gift Aid box unticked.

My questions are:

- We also have Wordfly so I imagine we could do something similar to what you did with the links to two URLs (Y/N)?

- Does that then update their Tessitura accounts or does it need to be done manually? I’m not sure what SP is but I’ll check that we have that also.

- In an ideal world, we’d love to be able to do this fully-automated, although if we could update in bulk by collecting declarations besides Tessitura numbers, that wouldn’t be too bad.

- GDPR: Would such a request by email be allowed in the post-GDPR era or would we need explicit consent to contact previous donors?

We really hope it’s possible as I think it would be really worthwhile. On the initial pilot alone there’d be about 15,000 customers and it would give us an easy template to follow in future on a more regular basis.

Thank you so much in advance for any advice.

Nick

1. If you use wordfly then Yes, you can do this

2.This automatically updates their record. My code assumes that there is no declaration at all on their record so you may need to adjust the code if you are also trying to renew expired declarations.
Essentially when you promote and track source number using Wordfly they update Tessitura and as part of this update the LP_UPDATE_EMAIL_PROMOTION stored procedure is called (standard wordfly local procedure so should be in your DB).
They pass in source number, customer number, url, response_code , response date time and email address to the procedure.
In this case we need response code 4 URL Clicked, the URL and the customer number.
The code checks to see if they already have a Declaration on their record, if they don't it adds one with their response.
if they do and their response is now different then it emails someone in the dev team - did this just incase people clicked both links for whatever reason - as anyone emailed didn't have a declaration on the record to begin with.

3. It should be possible to fully automate this.
If you can setup a list of everyone who has donated (during a set time) and doesn't have a gift aid declaration you can then use that list to trigger the email from wordfly

One other way of doing this would be to set an output set element of Has GA Declaration and then email all donors thanking them for their donation and then use the flag to only show a section of the email asking if they are eligible for Gift Aid when Has GA Declaration is N.

4.For GDPR would assume you could use legitimate interest, as it is a followup on the donation for completing legal/official documentation.

Mark