Monday, July 24, 2023

Sending Bulk Overdue Notifications from Koha via WhatsApp

In today's fast-paced world, libraries are continuously seeking innovative ways to enhance their services and keep their users engaged. Real-time updates and notifications play a vital role in ensuring library users stay informed about events, due dates, and other important updates. In this blog post, we will explore a creative solution for small libraries to leverage WhatsApp as a means of sending bulk overdue notifications to their patrons using the Koha Integrated Library System.

Step 1:Setting up WhatsApp Business for the Library: 
To streamline communication and improve user engagement, the library can obtain a new mobile number dedicated solely to library services. Consequently, the librarian can set up WhatsApp Business on his phone and can also link it to other library staff members (currently supporting up to 4 devices simultaneously). This approach can help to facilitate swift responses to user queries and ensure efficient user support without affecting personal WhatsApp.
Step 2: Modifying Member Entry Form

To begin with, the first step is to modify the label "phone" to "WhatsApp Number" in the member entry form in Koha. By doing this, libraries can collect and store WhatsApp Numbers for their patrons effectively.

Go to Home > Administration > System preferences > IntranetUserJS, paste the provided code, and save the changes.

///// hide Alternate address /////

document.addEventListener("DOMContentLoaded", function() {
        // Find the fieldsets with the classes "rows" and "memberentry_address"
        const addressFieldset = document.querySelector("#memberentry_address");
        const altAddressFieldset = document.querySelector("#memberentry_altaddress");

        // Hide the fieldsets by setting their display property to "none"
        if (addressFieldset) {
   = "none";
        if (altAddressFieldset) {
   = "none";

///// Change Primary phone label on member details page /////

if ( $('#pat_moremember').length ) {
    $('#patron-phone .label').text('WhatsApp Number:');
/* change sort1 label on member entry form page */
if ( $('#pat_memberentrygen').length ) {
    $('label[for="phone"]').text('WhatsApp Number:');

By making this simple modification, libraries can now collect and store patrons' WhatsApp numbers, enabling better communication with them.

Step 3: Install WhatsApp Sender Free Bulk Messaging Chrome Extension

As a cost-effective approach, the next step involves using the "WhatsApp Sender Free Bulk Messaging" Chrome extension. This extension enables the library staff to send messages to multiple patrons simultaneously, ensuring they receive timely notifications about overdue items and other important information.

Download the Extension from the Chrome Web Store 

Step 4: Creating Customized SQL Reports from Koha

In order to generate the necessary data for overdue notifications, library staff can create customized SQL reports from Koha. Two specific reports are discussed here: one for the overdue list and another for books due tomorrow. These reports provide valuable information such as the WhatsApp numbers of patrons, card numbers, surnames, due dates, and book details.

Overdue List Report

The following SQL query generates a report for items that are overdue by more than 30 days:

SELECT AS 'WhatsApp Number',
    DATE_FORMAT(issues.date_due, '%d/%m/%Y') AS date_due,
    (TO_DAYS(curdate()) - TO_DAYS(issues.date_due)) AS 'days_overdue',
    GROUP_CONCAT(CONCAT_WS(' - ', items.barcode, items.itype, biblio.title, SEPARATOR ' | ') AS combined_data
LEFT JOIN issues ON (borrowers.borrowernumber = issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber = items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
    (TO_DAYS(curdate()) - TO_DAYS(issues.date_due)) > 30
    AND items.homebranch = <<Branch|branches>>
    borrowers.cardnumber ASC, issues.date_due ASC


Books Due Tomorrow Report

This SQL query generates a report for items due to be returned tomorrow:

SELECT AS `WhatsApp Number`,
       DATE_FORMAT(DATE(co.date_due), '%d/%m/%Y') AS duedate,
       GROUP_CONCAT(CONCAT_WS(' - ', i.barcode, i.itype, b.title, SEPARATOR ' | ') AS combined_data
FROM borrowers p
LEFT JOIN issues co ON (co.borrowernumber = p.borrowernumber)
LEFT JOIN items i ON (co.itemnumber = i.itemnumber)
LEFT JOIN biblio b ON (b.biblionumber = i.biblionumber)
WHERE DATE(co.date_due) = DATE_ADD(curdate(), INTERVAL 1 DAY)
      AND i.homebranch = <<Branch|branches>>
GROUP BY p.cardnumber, p.surname, p.branchcode, p.sort1, DATE(co.date_due)
ORDER BY p.surname ASC


Step 5: Sending Messages

At the end of each day, the circulation desk staff can run the appropriate report from Koha and download it. With the WhatsApp Sender Chrome extension, they can attach the downloaded report file (.csv), select a preset message template, and click on the send button. This automated process ensures that each user receives a personalized notification with details of their overdue items or upcoming due dates.

Message Text Template

The following is a sample message template to be added in the WhatsApp Sender Chrome extension:


Dear *{{surname}},

Your library book(s) is/are overdue. Please return them by tomorrow to avoid late fees.

Card Number: {{cardnumber}}
Library: {{branchcode}}
Date Due: {{duedate}}
Book Details: {{combined_data}}

For inquiries, contact our library staff.

Thank you,

-- East Campus Learning Resource center

Advantages of the WhatsApp Notification System:

Implementing this WhatsApp-based notification system offers several advantages:

  • Real-Time Updates: Patrons receive timely notifications directly on their WhatsApp, ensuring they stay informed about library events, overdue items, and important updates with attachments.
  • No Number Saving Required: Unlike broadcast/group messages, patrons do not need to save the library's WhatsApp number, simplifying the process and ensuring notifications reach everyone.
  • Cost-Effective: As a small library, investing in costly APIs for WhatsApp integration might not be feasible. This approach provides an affordable alternative to reach patrons effectively.

Embracing modern technologies like WhatsApp and integrating them creatively into library services can significantly improve user engagement and satisfaction. By following the step-by-step guide outlined above, small libraries can streamline their overdue notification process and provide a more convenient experience for their patrons. This innovative solution showcases the adaptability and resourcefulness of librarians in today's digital age. So, why wait? Get ready to revolutionize your library's communication with WhatsApp bulk messaging and keep your users connected in real-time!

Disclaimer: Please ensure that you comply with all applicable privacy laws and regulations while using WhatsApp for communication with library patrons.

No comments:

Post a Comment