Fundraising/Internal Endpoints/End of year emails

From Wikitech

We send a letter for recurring donations once a year rather than as each donation is received.

The code sends annual summary letters under 2 scenarios

  1. to bulk receipt recurring donations once a year (by scheduled job)
  2. sending from the UI for a single donor.

The code to do this is currently in civicrm_extension wmf-thankyou.The main test cover for this code is in Civi\Api4\EOYEmailTest.

Sending to an individual contact

This UI feature is accessible from the actions menu (bottom right) from the contact summary screen. The email will be sent regardless of whether the contact has recurring contributions. The UI will show a preview of the email to be sent - but only if there are donations in the previous year (since it is rendering using php on load rather than ajax)

There are 2 apiv4 actions that can be used in this context EOYEmail.send and EOYEmail.render.. Render is useful if you only want to preview the mail, but not actually action it. The drush commands to call these api look like:

drush @wmff cvapi EOYEmail.Send version=4 year=2021 contactID=11863262

drush @wmff cvapi EOYEmail.Render version=4 year=2021 contactID=11863262

Bulk sending

Once a year we send an end of year email to all contacts who

  • have made one or more completed donations associated with a recurring contribution between 10am GMT on the first day the year in question and 10 AM GMT on the first day of the following year. We use this time since it should catch all donations made in the US before midnight on the 31st.
  • are not deleted
  • have an email

We do not consider the following in our calculation currently (possibly by design, possibly by accident) -contact is_deceased, contact do_not_email, email.on_hold, any opt in or out settings. In this scenario we only send to donors with donations attached to a recurring contribution - but the summary includes any other donations they have made in the year (it might be confusing to them if some were missing)

Note that we only send one email per unique email - but we do link all contacts with that email to the activitiy record (meaing the total of activity contact records will exceed the number of activities in the end)

When we bulk send we run both the MakeJob and the Send jobs.

MakeJob

Make job should be run just once and takes 3-4 minutes. It populates a `wmf_eoy_receipt_donor` with a list of email addresses to receive an end of year summary, the relevant year, and the status of their summary. Running it multiple times will not add new rows unless a new email address meets the criteria. In 2021 it was necessary to turn off other scheduled jobs to run this - however, the queries were a lot more time consuming so that might not be needed in 2022


The drush command looks like:

drush @wmff cvapi EOYEmail.MakeJob version=4 year=2021`

Send

This should run regularly until completed. It runs at a rate of around 400 per minute

  • drush @wmff cvapi EOYEmail.Send version=4 limit=1000 year=2021`

We normally set up 2 jobs for this with different SMTP servers to spread the load. They are scheduled to alternate with the batch size set to finish before the other starts. If they did overlap the fetch mechanism is per row (updating each row as sent) so they might double up on some rows if they tried to grab them at exactly the same time but would not each grab the same 1000 rows to process & do so regardless of the other job's processing.

Scheduled jobs

We use 2 scheduled jobs for the Send action

'''eoy_receipt_send'''

This sends half the emails through frmx1001

'''eoy_receipt_send_two'''

This sends the other half of the emails through frmx2001

Pre-coffee guide to running the jobs

- Load the CiviCRM form to monitor recent eoy emails https://civicrm.wikimedia.org/civicrm/eoy (staging url)

- Turn off all the jobs and notify any civi users that the UI will be slow while the calculate job runs

- Run eoy_receipt_calculate. This only needs to be run once then everything in in the table for the email sends.

- Turn the jobs back on

- Test sending a receipt with slow start

- Turn the main sending jobs on

Tracking what has been sent

In addition to the server level mail logs each email is recorded in an activity against the affected contacts. These can be viewed in this form https://civicrm.wikimedia.org/civicrm/eoy (it shows recent sends, not all sends)

To see a breakdown of sent, queued and failed while the process is running hop on to frdev1001 and run the following query against the CiviCRM database: 

SELECT year, status, COUNT(*) FROM wmf_eoy_receipt_donor WHERE year = (YEAR(NOW()) - 1) GROUP BY year, status;

to watch the job send, can go to frdev1001 and run the following script

watch -n 60 mysql civicrm -e "'select count(*), status from wmf_eoy_receipt_donor where year=(YEAR(NOW()) - 1) group by status'"

Finishing up

Once no more emails are going out it is time to comment out the schedule. Note however there will be a small portion of failed lines in the donor table - these can be viewed using 

select * FROM wmf_eoy_receipt_donor WHERE status = 'failed' and year = (YEAR(NOW()) -1);

Sleuthing in the log_civicrm_email table will likely find the contact they belonged to and reveal that they were edited. Re-running MakeJob will likely insert rows for their new emails - in which case they will go out with the send job

Duplicate handling

The selection of who to send to is based on the relevant primary email address. The email to that person will include all contributions made by a non-deleted contact with that email as their primary address. The details of the contact with the most recent donation will be prioritised.

Messages and tokens

The emails sent out are parsed through the Smarty templating system. They include smarty code and 2 types on tokens

  1. CiviCRM native tokens. These do not have a $ sign and look like {contact.display_name}. A list is available in the tokens widget in the UI. When these tokens are used in smarty logic they must have quotes around them as the tokens are evaluated before smarty runs so {if {contact.first_name}} would be reduced to {if } before smarty sees it whereas {if '{contact.first_name}'}will be the more-parseable {if ''}.
  2. WMF Smarty variables. These are deliberately defined by us within the class CRM_Contribute_WorkflowMessage_EOYThankYou. They have $ signs before them. As of writing we expose
    • {$year} - this comes through from the year passed to the api (or assumed if relying on the default of last year)
    • {$active_recurring} - does the contact have an active recurring contribution - used to determine whether to include the cancel link.
    • {$hasEndowment} - does the contact have any endowment contributions in the given year. (Currently the US text version (only) lists endownment contributions.)
    • {$hasAnnualFund} - does the contact have any non-endowment contributions in the given year.
    • {$totals} - this is an array of totals contributions by currency (parse with smarty foreach syntax). The keys in the array are:
      • amount - formatted by locale
      • currency
    • {$contributions} - this is an array of contributions (parse with smarty foreach syntax). The keys in the array are:
      • currency
      • total_amount (raw, unformatted)
      • financial_type (e.g 'Endowment')
      • amount (formatted amount)
      • date (receive_date in Hawaii timezone to ensure all US donations fit in the 'right' year)
      • receive_date - same as date but deprecated as the hope is to free this up again for the raw date in future.

Outstanding issues

  • In order to cover both Norwegian variants Donor Relations added a second translation - this will be need to be updated in tandem, unless we figure out language fallback for it
Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=Fundraising/Internal_Endpoints/End_of_year_emails&oldid=2136833"