Social distancing for retailers by using SMS – A case study

Introduction

In this case study we are going to enable a small shop to implement a social distancing solution for retailers using SMS messages. This case study contains everything you need to set this up and test it yourself in just a couple of minutes.

The Covid-19, or Corona crisis requires people to keep at least 1.5 meters distance from each other. We call this ‘Social Distancing’ and it is proving quite a challenge for small retailers.

A lot of businesses, bars or retailers don’t have a lot of space available and as such they can only receive a limited number of people at the same time.

Use case

This use case concerns a fictional business called: ‘Harrie’s Hardware‘. This is a small hardware shop that can reopen their business starting at 6/1/2020 provided that their customers always stay 1.5 meters apart from each other.

For Harrie’s Hardware this means that they cannot receive more than 8 persons at the same time. They estimate that every person stays for about 15 minutes on average.

To make sure that there are no lines outside of their building they want to use the following system:

A customer sends an SMS message announcing when they plan to shop
Harrie’s Hardware will automatically reply with the closest time available for this person.

The customer needs to initiate the message with the keyword SHOP so our system will know what the message is about. An example exchange should look like this:

Social Distancing Example SMS Dialogue

In the example there was no room at 1:30 PM so the application decided put the customer at 1:45 PM. This use case will automatically schedule a time up to 1 hour in the future. If there is no room within an hour of the suggested time the application will ask the customer to make a different suggestion. Of course the script will also keep the scheduled times within the open times of the shop.

Prerequisites

The Auron SMS Server
A SQL Server (express) installation or the built-in LocalDB database
A GSM modem, SMPP subscription or the built-in demonstration gateway.

The Gemalto EHS6T is an excellent GSM modem for this application. An SMPP provider like Clickatell would work great for higher messaging volumes.

Installation

The use case starts with the installation of the SMS Server. Have a look in the knowledge base to find out the system requirements and the installation instructions.

You can use the built-in LocalDB database to get started right-away. But it makes sense to install to a SQL Server Express database before going live. In any case. Make sure to install the free SQL Server management studio for easy access to your database.

Setup database

The Social Distancing SMS application will only use a single table to keep its administration. The table contains the following fields:

Field Name	Field Type	Description
ID	Integer	Auto number field
Plan	DateTime2	The plan or schedule date for a person
PhoneNr	NVARCHAR(20)	The phone number of the person that is going to be there

We’ll call this table ‘Population’ and to keep it apart from the SMS Server database we’ll create a new database for it called ‘AuIntegration’. This can be done by running the following SQL Script in the SQL Server manager:

CREATE DATABASE AuIntegration
GO

USE AuIntegration
GO

CREATE TABLE [Population]
(
		[ID]		INT IDENTITY(1,1)
	,	[Plan]		DATETIME2
	,	[PhoneNr]	NVARCHAR(20)
)

That’s all we’ll need. Everything else happens in the SMS Server.

Configure the SMS Server

The SMS Server configuration contains two important parts:

Setting up the communication channel
Setting up the SMS triggers

The communication channel determines how the SMS is sent. For this example we’ll use the Auron test and demonstration gateway. The test and demonstration gateway allows trial users to send a number of free SMS messages. This way it’s possible to test a project without immediately having to commit to an SMS provider.

The triggers are small scripts that automatically handle incoming messages. We will use triggers to actually implement this use case.

Setting up the communication channel

The first thing to setup is the SMS Channel. This will make it a lot easier to test our application later on.

We’re using the built-in test and demonstration gateway so the setup is easy.

First open the SMS Server Manager application and click on ‘Channels’ in the tree view on the left. Next click on ‘New Channel’ in the view on the right and select ‘SMPP Client’.

Create SMPP Channel

Next leave the channel settings as they are by default. You can just click on Next and finally on Finished and the channel will be created and ready for use.

Default settings for the Auron test and demonstration gateway

Setting up the triggers

Now that the SMPP channel is setup you can start adding the necessary triggers. For this project we need 2 triggers.

The social distancing SMS application trigger for SMS messages that start with ‘SHOP’
A fall through trigger for SMS messages that don’t start with ‘SHOP’

Social Distancing Trigger Configuration

As you can see from the screenshot both triggers use the VBScript language. They only trigger when receiving an SMS message.

The trigger condition further species when to run a trigger. In the case of the ‘SOCIALDISTANCE’ trigger it runs when the message is not a delivery report and the body text starts with the text ‘SHOP’. The fall through trigger runs when a message is not a delivery report and is not caught by the previous ‘SOCIALDISTANCE’ Trigger.

Setting up the fall through trigger

This one is easy to setup as it’s a simple ‘auto reply’ trigger that can be entirely generated from a script template.

Auto Reply Trigger Template

Just click on ‘Triggers’ in the tree view on the left. Next click on ‘New Trigger’ in the view on the right and scroll down to select the ‘Reply Trigger Template’ option.

Social Distance Auto Reply

Click through the wizard and make sure the trigger and the corresponding script file receive meaningful names. Finally, specify the message that a customer should receive when this trigger is activated. You can use parts of the original message here but in this case we don’t need that.

Setting up the social distancing trigger

This is completely custom. So we’ll start out with an empty VBScript trigger and implement this one afterwards.

Create an Empty VBScript Trigger

Create this trigger with a meaningful trigger name and script name.

Social Distance Empty Trigger

After creating the trigger edit the trigger from the triggers view to fine tune the trigger condition and start editing the trigger script.

Edit the Social Distance Trigger

While editing the condition you can use the ‘Insert Fields‘ and ‘Insert Constants‘ buttons to find which fields and constants are available for use in the trigger condition. Use the ‘Test Syntax‘ button whenever you are unsure if a condition is correctly formatted. The condition should always be a valid SQL expression.

Start editing the actual script file by clicking on the ‘Script’ tab at the top of the same ‘Edit Trigger’ dialog.

Edit Trigger Script

Click on the pencil icon to open the script editor and start working on the script.

Tip: The SMS Server will automatically reload a script once changes are detected. So it’s very convenient to keep the trigger log open. This way you can find out about any syntax errors without needing to run a test message.

Automatic Reload of Trigger Scripts

Tip 2: It’s easy to create an incoming message. This will run your trigger scripts as well. So that’s a great way to simulate an end-to-end test without spending SMS credits.

Create Incoming Test Message

Social Distancing Script

Find the full script at the bottom of this page.
The first part of the scripts defines a couple of parameters, or constant values, that affect the behavior of the script. These need to be changed according to your situation.

CONST STR_DEBUGFILE     = "C:\ProgramData\Auron\SMS Server\Log\SOCIALDISTANCE.txt"
CONST B_ENABLE_DEBUG    = True

CONST MAX_POPULATION    = 8   ' Maximum numbers of customers in the shop
CONST AVG_SHOPTIME_MIN  = 15  ' A customer will, on average, shop for 15 minutes
CONST NR_SLOTSTOTEST    = 4   ' Test a maximum of 4 slots ahead of requested time
CONST TABLE_POPULATION  = "[AuIntegration].[dbo].[Population]"
CONST SHOP_NAME         = "Harrie's Hardware"

These constants have the following function:

Constant Name	Description
STR_DEBUGFILE	Full path of the debug log file for this script
B_ENABLE_DEBUG	Enable debug logging by setting this value to true
MAX_POPULATION	The maximum number of people that are allowed to be in the shop at one time
AVG_SHOPTIME_MIN	The average time a person is estimated to be in the shop. This also determines the size of time slots that are allocated
NR_SLOTSTOTEST	The maximum number of time slots that this script will look ahead in order to schedule a customer. If the average shop time is set to 15 minutes and this number is set to 4 then the script may schedule a visit at most 1 hour after the requested time.
TABLE_POPULATION	The full name of the table that contains our population data
SHOP_NAME	The full name of your shop

Next is the main script function: ‘ProcessMessageEx‘. This function is automatically called by the SMS Server for every message that matches the trigger condition. In this case it’s called automatically for every incoming message that starts with the keyword: ‘SHOP’.

' // ========================================================================
' // Function: ProcessMessageEx
' // ------------------------------------------------------------------------
' // ProcessMessageEx trigger function to process incoming messages
' // ========================================================================
Function ProcessMessageEx(objMessageIn, objMessageDB, dctContext)

  Log ">> ProcessMessageEx"
  
  objMessageIn.TriggerStatusID = g_objConstants.MESSAGETRGSTATUS_PROCESSED
  
  Dim dtDateTime
  bSuccess = FindDateTime(objMessageIn.Body, dtDateTime)
  
  ' Test if we could find a date time; reply if we did not understand the message
  If Not bSuccess Then
    SendSms "We're sorry but unfortunately no date/time was found in your message. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'. Please try again." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function
  End If
  
  ' Test if the time was in the future. Send a friendly reply if not in the future
  If dtDateTime < Now() Then
    SendSms "Please specify a date/time that is in the future. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function 
  End If

  ' Test if our shop is open at that time. Send a friendly reply if we're not open
  If Not IsShopOpenAt(dtDateTime) Then 
    SendSms "We're sorry but our shop is closed at that time. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'. Please try again" _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function
  End If
  
  ' Snap the requested time to the first slot boundary on or after the requested time
  dtDateTime = SnapToSlot(dtDateTime)

  ' Find the first available slot that is within a maximum distance of the requested time
  Dim dtFirstSlot
  bSuccess = FindFirstAvailableSlot(dtDateTime, objMessageDB, dtFirstSlot)
  If Not bSuccess Then
    nMinutes = AVG_SHOPTIME_MIN * NR_SLOTSTOTEST
    SendSms "We're sorry we could not find a slot for you within " & _
      nMinutes & "minutes of your requested time. Please try again with a different time." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function    
  End If  

  ' Automatically scheduled the customer for this time
  RegisterOnSlot dtFirstSlot, objMessageIn, objMessageDB
  SendSMS "Thank you for choosing " & SHOP_NAME & ". We're looking forward to receiving you at: " & _
    FormatDateTime(dtFirstSlot), objMessageIn.FromAddress, objMessageDB
    
  Log "<< ProcessMessageEx"

End Function

This provides a nice high-level view of what happens. The following steps take place:

Parse the message and find the requested date/time
Make sure the requested date/time is in the future
Make sure that the shop is opened at the requested time
Align the requested time according to the closed time slot
Find the first available time slot within an hour of the requested time

If all of this went well: Plan the visit and reply with the planned time
In any other case: Send an SMS message to explain what went wrong and what we expect our customer to do next.

Finally we also need to know at what times Harrie’s is open for business:

Function IsShopOpenAt(dtDateTime)
  
  IsShopOpenAt = False
    
  nHour = Hour(dtDateTime)
  Select Case WeekDay(dtDateTime)
    Case 1     ' Sunday - closed
    Case 2     ' Monday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 3     ' Tuesday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 4     ' Wednesday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 5     ' Thursday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 6     ' Friday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 7     ' Saturday
      IsShopOpenAt = nHour >= 9 And nHour < 12
  End Select

End Function

This function lays out the business hours per weekday. Update this according to your situation.

In Conclusion

This script is a great way to get started using SMS as a way to enforce social distancing inside a bar or retail shop.

However, depending on your situation this may not be the final answer. You may want to extend the script to do more things like:

Make the customer specify their business in order to more accurately predict the time they will stay
Make sure a person does not make multiple future appointments
Verify that a person is OK with the suggested time if it’s different from the requested time.
Etc..

If you are at least somewhat technically inclined and comfortable with scripting languages then you will be able to make any changes yourself.

If you need help with adapting this use case to your specific situation please let us know. We’re glad to help you get started.

Appendix I – Full ‘socialdistancing.vbs’ script

To use this script:

Copy the script into notepad and save it to your SMS Server trigger directory. (By default: C:\ProgramData\Auron\SMS Server\Triggers)
Create a new Empty VBScript trigger as discussed in this article
Edit the newly created trigger and go to the ‘Script’ tab.
Browse and select the script file to connect it to this trigger.

CONST STR_DEBUGFILE     = "C:\ProgramData\Auron\SMS Server\Log\SOCIALDISTANCE.txt"
CONST B_ENABLE_DEBUG    = True

CONST MAX_POPULATION    = 8   ' Maximum numbers of customers in the shop
CONST AVG_SHOPTIME_MIN  = 15  ' A customer will, on average, shop for 15 minutes
CONST NR_SLOTSTOTEST    = 4   ' Test a maximum of 4 slots ahead of requested time
CONST TABLE_POPULATION  = "[AuIntegration].[dbo].[Population]"
CONST SHOP_NAME         = "Harrie's Hardware"

' Creation of global objects
Set g_objConstants      = CreateObject("AxMmServer.Constants")
Set g_objFso            = CreateObject("Scripting.FileSystemObject")

' // ========================================================================
' // Function: ProcessMessageEx
' // ------------------------------------------------------------------------
' // ProcessMessageEx trigger function to process incoming messages
' // ========================================================================
Function ProcessMessageEx(objMessageIn, objMessageDB, dctContext)

  Log ">> ProcessMessageEx"
  
  objMessageIn.TriggerStatusID = g_objConstants.MESSAGETRGSTATUS_PROCESSED
  
  Dim dtDateTime
  bSuccess = FindDateTime(objMessageIn.Body, dtDateTime)
  
  ' Test if we could find a date time; reply if we did not understand the message
  If Not bSuccess Then
    SendSms "We're sorry but unfortunately no date/time was found in your message. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'. Please try again." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function
  End If
  
  ' Test if the time was in the future. Send a friendly reply if not in the future
  If dtDateTime < Now() Then
    SendSms "Please specify a date/time that is in the future. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function 
  End If

  ' Test if our shop is open at that time. Send a friendly reply if we're not open
  If Not IsShopOpenAt(dtDateTime) Then 
    SendSms "We're sorry but our shop is closed at that time. " & _
      "Format your SMS like this: 'SHOP 10:30AM 6/24'. Please try again" _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function
  End If
  
  ' Snap the requested time to the first slot boundary on or after the requested time
  dtDateTime = SnapToSlot(dtDateTime)

  ' Find the first available slot that is within a maximum distance of the requested time
  Dim dtFirstSlot
  bSuccess = FindFirstAvailableSlot(dtDateTime, objMessageDB, dtFirstSlot)
  If Not bSuccess Then
    nMinutes = AVG_SHOPTIME_MIN * NR_SLOTSTOTEST
    SendSms "We're sorry we could not find a slot for you within " & _
      nMinutes & "minutes of your requested time. Please try again with a different time." _
      , objMessageIn.FromAddress, objMessageDB
    Exit Function    
  End If  

  ' Automatically scheduled the customer for this time
  RegisterOnSlot dtFirstSlot, objMessageIn, objMessageDB
  SendSMS "Thank you for choosing " & SHOP_NAME & ". We're looking forward to receiving you at: " & _
    FormatDateTime(dtFirstSlot), objMessageIn.FromAddress, objMessageDB
    
  Log "<< ProcessMessageEx"

End Function

Function RegisterOnSlot(dtFirstSlot, objMessageIn, objMessageDB)

  sQ = "INSERT INTO " & TABLE_POPULATION & " ([Plan], [PhoneNr]) VALUES (" & _
    "'" & FormatDateTime(dtFirstSlot) & "', '" & objMessageIn.FromAddress & "')"

  objMessageDB.AdoConnection.Execute(sQ)

End Function

Function FindFirstAvailableSlot(dtDateTime, objMessageDB, ByRef dtFirstSlot)

  FindFirstAvailableSlot = False
  dtFirstSlot = dtDateTime
  
  For n = 1 To NR_SLOTSTOTEST
    If IsShopOpenAt(dtFirstSlot) Then 
      bAvailable = IsSlotIsAvailable(dtFirstSlot, objMessageDB)
      If bAvailable Then
        FindFirstAvailableSlot = True
        Exit Function
      End If      
    End If 
    
    dtFirstSlot = DateAdd("n", AVG_SHOPTIME_MIN, dtFirstSlot)
  Next
  
End Function

Function IsSlotIsAvailable(dtDateTime, objMessageDB)
  
  IsSlotIsAvailable = False
  
  ' formulate an SQL query that will test how many customers are already planned for this slot
  dtSlotEnd = DateAdd("n", AVG_SHOPTIME_MIN, dtDateTime)  
  
  sStart = FormatDateTime(dtDateTime, 0)
  sEnd = FormatDateTime(dtSlotEnd, 0)  
  
  sQ = "SELECT COUNT(PhoneNr) AS cnt FROM " & TABLE_POPULATION & _
    " WHERE [Plan] >= '" & sStart & "' AND [Plan] < '" & sEnd & "'" 

  ' run the query  
  Set objRs = objMessageDB.AdoConnection.Execute(sQ)
  If Not objRs.EOF Then
    objRs.MoveFirst
    nCount = objRs("cnt").Value

    If nCount < MAX_POPULATION Then 
      IsSlotIsAvailable = True
    End If    
  End If
 
End Function

Function SnapToSlot(dtDateTime)
  ' get the number of minutes after midnight
  nMinutes = Hour(dtDateTime) * 60
  nMinutes = nMinutes + Minute(dtDateTime)
  
  ' devide through number of slots and round up
  dMinutes = nMinutes / AVG_SHOPTIME_MIN  
  nMinutes = Int(dMinutes)
  If nMinutes <> dMinutes Then
    nMinutes = nMinutes + 1
  End If
  
  ' convert back to minutes
  nMinutes = nMinutes * AVG_SHOPTIME_MIN
  
  ' convert back to date
  dtNew = CDate(FormatDateTime(dtDateTime, 2)) ' Use short date format to get just the date
  dtNew = DateAdd("n", nMinutes, dtNew)
  
  SnapToSlot = dtNew
End Function

Function IsShopOpenAt(dtDateTime)
  
  IsShopOpenAt = False
    
  nHour = Hour(dtDateTime)
  Select Case WeekDay(dtDateTime)
    Case 1     ' Sunday - closed
    Case 2     ' Monday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 3     ' Tuesday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 4     ' Wednesday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 5     ' Thursday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 6     ' Friday
      IsShopOpenAt = nHour >= 9 And nHour < 17
    Case 7     ' Saturday
      IsShopOpenAt = nHour >= 9 And nHour < 12
  End Select

End Function

Function FindDateTime(sText, ByRef dtDateTime)

  FindDateTime = False

  ' "10:30AM 05/24" or "9:30AM 5/24" or "2:00PM"
  Set oRe = New RegExp
  oRe.Pattern = "\d{1,2}:\d{1,2}[AP]M(?: \d{1,2}/\d{1,2})?"
  
  Set oMatches = oRe.Execute(sText)
  If oMatches.Count > 0 Then
    FindDateTime = True
    dtDateTime = CDate(oMatches(0))
    
    ' test if a date was specified; if not, make it today
    If Year(dtDateTime) < 1900 Then 
      nMinutes = Hour(dtDateTime) * 60 + Minute(dtDateTime)
      dtDateTime = DateAdd("n", nMinutes, Date())
    End If
    
  End If
  
End Function

Function SendSms(sText, sTo, objMessageDB)

  SendSms = False
    
  Set objMessageOut = objMessageDb.Create("SMS")
  objMessageOut.ToAddress = sTo
  objMessageOut.Body = sText
  objMessageDb.Save objMessageOut
  If objMessageDb.LastError = 0 Then
    SendSms = True
  End If

End Function

' // ========================================================================
' // Log
' // ------------------------------------------------------------------------
' // Write text to the log file. 
' // Use this function to debug your trigger script
' // ========================================================================
Function Log(sText)
    If B_ENABLE_DEBUG Then
        Set fOut = g_objFso.OpenTextFile(STR_DEBUGFILE, 8, True)
        fOut.WriteLine sText
        fOut.Close
    End If
End Function

How can we help?