Using Koha's Patron Emailer

Koha's patron emailer functionality allows us to send custom notices to patrons whatever patron, item, or other data we wish to pull from the database. The cronjob allows recurring scheduled messages, while the plugin is great for one-offs. The two interfaces have broadly the same functionality, with a few small differences that I'll cover below.

When the patron emailer is (and isn't) the answer

While the patron emailer is a great feature with lots of applications, it does bear two big caveats right up front. First, it does not support HTML formatting of these emails. The messages will always be fairly plain and text-based. Second, using the patron emailer to regularly email all of your patrons is likely to get your Koha server on a spam list, blocking all your Koha emails from getting to your patrons.

This is all to say, the patron emailer isn't a good replacement for something like MailChimp or Constant Contact. If you're interested in pulling email addresses out of Koha for use with such an email service, we're happy to help.

A report and a notice

Fundamentally, the patron emailer combines the data from a report with the content of a notice. Basically, it's like we're doing a mail merge in Office. Our report is like the spreadsheet in Excel and our notice is like the form letter in Word. This structure is pretty different from other Koha notices, and leads to three big rules to keep in mind:

  • One email per row: Koha will send one email per row in your report results. Make sure your report includes only the patrons you want to email and groups them appropriately if you want to send just one email per patron.
  • All data must be in your report: Notices sent via the patron emailer can only pull data from your report. In most Koha notices, we can use double angle brackets (<< >>) or Template Toolkit tags ([% %]) to pull in whatever database fields we want, but for these notices we need can only look at what's in the report.
  • Your report must contain cardnumber and email: This is really an extension of the previous rule, but bears a specific callout. Because Koha isn't referring over to the database as a whole for our notice, we need to hand it cardnumbers and emails in our report.

How about an example?

For a simple example, let's look at how to use the patron emailer to send something to every patron who owes the library money. We know the first thing we need is a report. We can start with this one from the Reports Library.

SELECT 
 (SELECT CONCAT('<a href=\"/cgi-bin/koha/members/boraccount.pl?borrowernumber=',b.borrowernumber,'\">', b.surname,', ', b.firstname,'</a>') 
 FROM borrowers b WHERE b.borrowernumber = a.borrowernumber) AS Patron, 
 format(sum(amountoutstanding),2) AS 'Outstanding',
 (SELECT count(i.itemnumber) FROM issues i WHERE b.borrowernumber = i.borrowernumber) AS 'Checkouts'
FROM 
 accountlines a, borrowers b
WHERE 
 (SELECT sum(amountoutstanding) FROM accountlines a2 WHERE a2.borrowernumber = a.borrowernumber) > '0.00'
 AND a.borrowernumber = b.borrowernumber
GROUP BY 
 a.borrowernumber ORDER BY b.surname, b.firstname, Outstanding ASC

That's got the basic information we need and meets our one-line-per-email requirement, but we have some things we need to clean up. First off, our report has helpfully inserted some HTML to make the name into a link and we already know the patron emailer cannot process HTML, so we want to simplify that. Also, we know we need to add in cardnumber and email and we don't care about that count of checkouts. Also also, because I can never leave well enough alone, I'm going to drop that subquery in favor of a HAVING value. This is all the sort of fiddling I might not worry about if the report were our end goal, but we need to be fairly picky here.

SELECT firstname,
	surname,
 cardnumber,
 email,
 format(sum(amountoutstanding),2) as owed
FROM accountlines
	LEFT JOIN borrowers USING (borrowernumber)
WHERE email is not null and email not like ''
GROUP BY borrowernumber
HAVING owed > 0

Having gotten our report in order, we now need a notice. If you go to Tools and then Notices & Slips, you'll see a button near the top of the page that you've likely not had any reason to use before: New Notice.

The first thing Koha wants to know about your new notice is which module it belongs to. For our purposes, this choice doesn't matter but you may as well pick whatever makes sense to you. I went with Circulation. My notice also needs a code and a description. This format should be familiar from item types, patron categories, and authorized values. You'll want to avoid spaces or special characters in your code -- I usually stick to alphanumerics and underscores.

Finally, we need the actual notice content. Don't bother defining anything other than Email, as they won't be used. The "HTML message" checkbox is irrelevant for the patron emailer. "Message subject" is what the patron will see as the subject on the email they receive.

If you've spent time with other Koha notices, this might look a bit odd. In an overdue notice, if we wanted the patron's first name we'd put "<<borrowers.firstname>>" in the notice to tell Koha to go check the firstname field in the borrowers table of the database. Here, I've said "[% firstname %]" instead. The "[% %]" are Template Toolkit tags and serve the same purpose as "<< >>." The "firstname" is actually referring to the column header in our report, rather than the database field. You'll notice that my notice includes "[% owed %]," which is the name I gave to my column of amounts owed.

I didn't include the usual block of the library's name and contact information as we often see in a notice. If we wanted that, we could either type it directly into the notice or update the report to find all those values from the branches table and then put those report values in the notice.

As with any other notice, anything outside of special tags is just text. Put in whatever you like and it will be passed into your notice verbatim. Remember you cannot format it with HTML.

To send with the cronjob

If you want your new notice to send automatically on a schedule, then it needs to be scheduled on your server, which will mean opening up a ticket. As such, there's not a lot for me to show you here. Tell us which report and notice you're using and how often you want it to send!

To send with the plugin

If you want to send your notice using the plugin, we actually have a whole other blog post about that already, but I want to revisit it a little as the plugin has had several updates since then. If you've never used plugins before, we may need to enable them for you or you may just want a little introduction to them to get started. Again, please feel free to open a ticket.

Once the plugin is installed, you'll want to go to the plugins page, click Actions for the Patron Emailer plugin, and select Run Tool. In the patron emailer blog post I linked above, Kelly and Jessie use an older version of the plugin that requires one to first configure the plugin in order to define one's notice content. These days, the plugin also knows how to use a notice that's saved in Koha. Since we've already created a notice, I'll use that.

Older versions of the plugin also required one to download and then re-upload one's report results. We still could upload a CSV file of report results (or even a CSV we made manually), but we don't have to.

You can find the Report ID of the report you wrote either in the Saved Reports list or in the URL of your report results page.

For the notice, pick the library and module you selected earlier. In the "Choose a notice" dropdown, look for the description you entered. If you have defined a notice in the plugin configuration, you'll see an option to "Use notice configured in plugin" rather than using a notice saved in Koha proper. That's a nice option if you don't expect to ever send this email again.

Once you've picked your report and notice and clicked Upload, you'll be taken to a page that previews the notices you're about to generate. If they look good, go ahead and click Send Emails. When you do so, the notices will be generated and go into the message queue like most other emails Koha sends. You'll see them in the Notices tab of each patron record. They'll show Pending until the next time the process message queue cronjob runs (generally every 15 minutes).

And that's it!

All our patrons who owe us money (and have an email address) have been emailed a reminder. If we set this as a cronjob, it could go out monthly or annually. Once the cron is set, if we wanted to tinker with the report or the notice we'd see those changes reflected on the next run, with no need to change the cron setup. Altogether, it's not a terribly complicated process, but there are some sticking points. I encourage you to think about what uses you might have for this and let us know how we can help!

Read more by Andrew Fuerste-Henry

Tags