ℹ️ The Auron SMS Server is now Auron Omni. Learn more here.

How can we help?

Social distancing for retailers by using SMS – A case study

Introduction

In this case study we 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.

Covid 19, also known as the Corona crisis, requires people to keep at least 1.5 meters distance from each other. We call this social distancing and it proves to be quite a challenge for small retailers.

Many businesses, bars, and retailers do not have a lot of space available and can therefore 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 reopens starting 6/1/2020, provided that customers always stay 1.5 meters apart from each other.

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

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

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

The customer must start the message with the keyword SHOP so the system knows what the message is about. An example exchange looks like this:

Social Distancing Example SMS Dialogue

Social Distancing Example SMS Dialogue

In the example there is no room at 1:30 PM so the application schedules the customer at 1:45 PM. This use case automatically schedules a time up to 1 hour in the future. If there is no room within 1 hour of the requested time the application asks the customer to make a different suggestion. The script also keeps scheduled times within the opening hours of the shop.

Prerequisites

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

Installation

The use case starts with installation of Auron Omni. Review the knowledge base for system requirements and installation instructions.

You can use the built in LocalDB database to get started right away. It is recommended to install SQL Server Express before going live. In all cases, make sure to install the free SQL Server Management Studio for easy database access.

Setup database

The Social Distancing SMS application uses a single table for administration. The table contains the following fields:

Field Name Field Type Description
ID Integer Auto number field
Plan DateTime2 The scheduled date and time for a person
PhoneNr NVARCHAR(20) The phone number of the person attending

We call this table Population and we create a new database for it called AuIntegration. Run the following SQL script in SQL Server Management Studio:

CREATE DATABASE AuIntegration
GO

USE AuIntegration
GO

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

That is all that is required. Everything else runs inside Auron Omni.

Configure Auron Omni

Auron Omni configuration contains two important parts:

The communication channel defines how SMS messages are sent. For this example we use the Auron test and demonstration gateway. This allows trial users to send a limited number of free SMS messages so the solution can be tested without an SMS provider.

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

Setting up the communication channel

The first step is to set up the SMS channel. This makes testing easier later on.

We use the built in test and demonstration gateway so setup is simple.

Open the Auron Omni Manager application and click Channels in the tree view on the left. Click New Channel and select SMPP Client.

Create SMPP Channel

Create SMPP Channel

Keep the default settings and click Next, then Finish. The channel is now ready for use.

Default settings for the Auron test and demonstration gateway

Default settings for the Auron test and demonstration gateway

Setting up the triggers

Now that the SMPP channel is set up you can add the required triggers. This project uses 2 triggers:

  • The social distancing SMS trigger for messages that start with SHOP
  • A fallback trigger for messages that do not start with SHOP
Social Distancing Trigger Configuration

Social Distancing Trigger Configuration

Both triggers use VBScript and only run for incoming SMS messages.

The condition defines when a trigger runs. The SOCIALDISTANCE trigger runs when the message is not a delivery report and the body starts with SHOP. The fallback trigger runs when a message is not a delivery report and is not handled by the SOCIALDISTANCE trigger.

Setting up the fallback trigger

This trigger is a simple auto reply trigger created from a script template.

Auto Reply Trigger Template

Auto Reply Trigger Template

Click Triggers in the tree view and then New Trigger. Select Reply Trigger Template.

Social Distance Auto Reply

Social Distance Auto Reply

Complete the wizard and use clear names for the trigger and script. Define the reply message that customers receive when the trigger runs.

Setting up the social distancing trigger

This trigger is fully custom, so we start with an empty VBScript trigger and implement it manually.

Create an Empty VBScript Trigger

Create an Empty VBScript Trigger

Create the trigger with a meaningful name and script name.

Social Distance Empty Trigger

Social Distance Empty Trigger

After creating the trigger, edit it to refine the condition and script.

Edit the Social Distance Trigger

Edit the Social Distance Trigger

Use Insert Fields and Insert Constants to build the condition. Use Test Syntax to validate it. The condition must always be a valid SQL expression.

Edit the script by opening the Script tab and clicking the pencil icon.

Edit Trigger Script

Edit Trigger Script

Auron Omni automatically reloads scripts when changes are detected, which makes testing easier.

Automatic Reload of Trigger Scripts

Automatic Reload of Trigger Scripts

You can also create incoming test messages to simulate real traffic without sending SMS messages.

Create Incoming Test Message

Create Incoming Test Message

Social Distancing Script

Find the full script at the bottom of this page.

The first part of the script defines configuration parameters that control behavior. These values must be adjusted for your situation.

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

CONST MAX_POPULATION    = 8   ' Maximum number 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 Auron Omni for every message that matches the trigger condition. In this case it is 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 or 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 or 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 are 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 are 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:

  1. Parse the message and find the requested date/time
  2. Make sure the requested date/time is in the future
  3. Make sure that the shop is opened at the requested time
  4. Align the requested time according to the closed time slot
  5. 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:

  1. Copy the script into notepad and save it to your Auron Omni trigger directory. (By default: C:\ProgramData\Auron\Auron Omni\Triggers)
  2. Create a new Empty VBScript trigger as discussed in this article
  3. Edit the newly created trigger and go to the ‘Script’ tab.
  4. Browse and select the script file to connect it to this trigger.
CONST STR_DEBUGFILE     = "C:\ProgramData\Auron\Auron Omni\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