Tackling Order, Confirmation, Payment, and Production

Creating a bespoke solution from scratch.

Deep Dive on Module Specific Challenges

Introducing Google Order Form as well as sheets

With my very rudimentary skills as well as pressured to set up a working platform within a week, I chose to go with google form (for many reasons) compared to all other competitors.

I often get asked why I did not just port over to a website. Shopify, Wix, etc. These alternatives are great e-commerce platforms however, I took the time to set up the store on each platform to run a POC but all failed to meet all of my mother's ever-increasing list of needs and flexibility.

These sites are often too standardized in their operations (understandably so since they are a sass product) and as such, do not solve our need as a small business that makes small margins. This led me to build a bespoke solution that suited my mother's specific needs.

Consumer Needs

• Choose Date based on Availability

• Select Quantity of Product

• Understand ingredients that are used in each product

• Input special requests as well as customization

• Know where to go to request large bulk orders. [Groupbuys]

Seller Needs

• View all of these orders and track quantities as orders come in, cut off when it gets too overwhelming.

  • Not as simple as setting a quantity limit on products, as if product A is low today, it means it frees up capacity to produce more of product B. Quantities are dynamic and dependent on other orders.

• Revert back to users based on their special requests and customization

• Filter based on dates and view delivery areas

  • This is to eventually contact drivers and liaise with them areas they are assigned to for the day

• Based on each order, print out an order form that summarizes: Buer's details, Recipient's Details, Delivery address, Order items, etc. These details will be attached to the order so that the kitchen is able to manage and know where their products are going. This also helps drivers to organize and send orders to where they need to.

Backend Workflow

In order, to not give away any details about our buyer's personal information, everything I show below is an abstract of the real system we use.

A macro overview of how [with the new solutions] we are managing workflow

Types of "Views" Required

Page	Details
View Date Specific Orders	From the large database of order responses, each tab filters down to date specific orders. I can see for e.g. 21st January who ordered and on the same page, the product quantities for each day.
Order Form	A templated order page, where I can filter based on date as well as user, their specific order page.

View Date Specific Orders

First, define the database range and employ query function.

=QUERY(all,"SELECT * WHERE H = '19 January 2021'",1)

How I show the product quantities is a simple addition math game.

View Order Form

I utilized VLOOKUP alot here. Treating each chunk of information as a database and extracting information from a single unique ID given to each customer.

=VLOOKUP(E10,qty,MATCH(U11,qty_header,0))

Tip: Use MATCH function if you don't want to constantly change the column # when you manipulate your database. This function searches for similar strings rather than mindless takes via column #

Based on these 2 functions, I essentially created a system that could allow my mother to change the displayed quantities and cost, a unique message, remarks, order details just by selecting the dropdown in the pink box.

What I learned throughout this process

1. Designing in excel is hard

   1. If you were to see the order form above, it looks like chunk boxes meshed together and aren't visually appealing.

2. Important to preserve your database data else, if anything goes wrong, you will constantly be scouring through your version history to understand what went wrong

3. Protect your sheets

4. Show only what is necessary and automate everything from one central location. The more steps are involved in viewing order quantities as well as viewing the order form, the more room for error and bugs will surface.

   1. When bugs do surface, it is easier to debug and understand where along the chain, the logic has been disrupted.

5. Have visual anchors as well as instructions embedded within your excel on how to use this product. Just like code commenting, it is time-consuming for another software developer to read your code and understand how you have come to build this solution. Every output has a million ways of coding to get there.

6. The biggest lesson is that my client is a 40-year-old non-techie and hence, given the resources and hacky nature of this solution, it was even more important to make sure that this has a lot of visual guidance.

Backend 2.0 is coming up! I have re-designed this a total of 2 times. I think I am almost getting there!