Pygsheets cafe reservations

Contents

1. Overview
   • Before the spreadsheets
   • Goals
   • Result
2. Spreadsheets
   • Data validation
   • Conditional formatting
   • Automated creation
3. Data analysis
4. Further automation
5. Credits

Overview

Before the spreadsheets

Fig 1. Administrator's notebook

All the table reservations were written down arbitrarely in a notebook.

Goals

• Validate each reservation data;
• Speed up the free tables lookup;
• Eliminate collisions and errors;
• Enable the analysis of collected data.

Result

> Link to the demo Spreadsheet

Fig 2. Reservations Google Spreadsheet

Spreadsheets

Data validation

Consider the example:

Fig 3. Татьяна has booked the table 102 for 2 hours

Each reservation is headed by a GuestData cell. This cell must comply with the following format:

{name} {persons} [чел|ч|Ч] {phone} {optional_comment}

In terms of a regular expression that would be:

(.{2,}\s(\d+\sчел|\d+\s[чЧ]|\d+[чЧ])\s(\d{9}|\d{2}\s\d{7}))

The remaining reservation cells may contain only 1 character - x or o. X's are used to mark the duration: 1 cell = 30 min interval. And when a guest arrives, the topmost x is replaced with an o for the purpose of visibility - see Conditional formatting.

Reservation cells regex:

[xoXOхоХО]{1}$

The whole Google Sheet data validation formula is constructed as follows:

this_cell = 'INDIRECT(ADDRESS(ROW(), COLUMN()))'
cell_above = 'INDIRECT(ADDRESS(ROW()-1, COLUMN()))'
guest_data = '(.{2,}\s(\d+\sчел|\d+\s[чЧ]|\d+[чЧ])\s(\d{9}|\d{2}\s\d{7}))'
duration_cell = '[xoXOхоХО]{1}$'

formula = f'=OR('\
            f'REGEXMATCH({this_cell}, "{guest_data}"), '\
            f'AND('\
                f'REGEXMATCH({cell_above}, "{duration_cell}|{guest_data}"), '\
                f'REGEXMATCH({this_cell}, "{duration_cell}")))'

Conditional formatting

There are 3 formatting rules, ordered by their priopity descending:

• If a cell contains symbol "o", the cell above it is filled with blue - it helps the administrator to quickly distinguish between the guests who have or haven't arrived;

• If a GuestData cell contains comments ("\d{7,}\s.{2,}") it is filed with yellow;

• All non-empty cells are filled with green color.

Automated creation

Spreadsheets for a whole year can be easily created in the spreadsheet.ipynb notebook:

for m in range(1, 13):
    Spreadsheet(2022, m).create()

Data analysis

The analysis.ipynb notebook contains RawData and ProcessedData classes which help to retrieve the data from sheets and convert it to the following DataFrame:

Fig 4. Sample from the DataFrame (phone numbers are altered)

This allows us to easily conduct studies and gain insights. For example, the vast majority of all reservations are made by women:

Fig 5. Gender distribution

Studying the variance of the mean number of reservations across the week helped to optimise staff's shifts:

Fig 6. Reservations by times of day

In october 2021 café's management decided to make it more accessable to visitors "from the street" by limiting the amount of reservations by 10 per day:

Fig 7. Reservations per month

This measure has affected the table popularity, increasing the variance in shares of total reservations among all tables:

Fig 8. Share of all reservations by table

The most desirable tables are always booked first, and since the daily limit was set their reservations commonly exhaust the quota.

Further automation

Now that the reservations are digitized, the whole booking proccess may be delegated to a telegram chatbot. Its major goals would be:

• Free up the administrator's time (≈ 2 hours every day). Although there are only 10 reservations available for each date, the number of incoming calls is significantly higher. Also, when a guest is being late the administrator usually calls him to confirm that the reservation is still on.
• Allow for more reservations while increasing the walk-in availability. Obviously, the current daily quota is heuristic and can be replaced with a more advanced algorithm which would operate on a more precise time scale (hours instead of days), offer the alternative times to the guests, evenly distribute the bookings between the tables and between the halls and concider the average loads.
• Establish a free, feature-rich and voluntary communication channel with guests, asking them to sign in for the cafe's newsletter.

Credits

Used libraries:

• nithinmurali pygsheets
• cphyc matplotlib-label-lines
• lead-ratings gender-guesser
• barseghyanartur transliterate
• numpy, matplotlib, pandas - awesome as usual :)