December 10, 2019

How to know when to kill a project

Recently I found myself hands deep in a very promising project. There’s a business process involving the food ordering products I am currently managing, which requires us to manually create digital menus. 

Recently I found myself hands deep in a very promising project. There’s a business process involving the food ordering products I am currently managing, which requires us to manually create digital menus. 

The products are called Fave Table Ordering and Fave Takeaway. They’re pretty cool. Check them out!

As we’re adding more and more merchants every day, this process becomes a bottleneck and we wanted to use atomization to fast-track this. The process we had set-up for this was very simple:

Our merchants would provide us the images of their menu, we would run it through an AI-powered Object Recognition algorithm provided by our in-house Data Science team, which would generate a CSV with the list of items. However, a menu does not only consist of items, but there are prices, categories, preferences and add-ons and all of this would be in one list, meaning someone needs to annotate this. My responsibility was to build a Google Spreadsheet, which would allow our Operations team to easily and quickly annotate, building a menu in the structure that we require. 

Shouldn’t be too hard, just provide some drop-downs and the job should be done, right? That’s what I thought. So I went and got working and built a basic spreadsheet. 

Our ambition was to get a working prototype in place to validate if this automation can work and will actually make the process faster versus how we’re doing it right now, which is via a custom-built web-interface.

So once we had finished the Object Recognition prototype and a prototype of my Google Spreadsheet, we sat our menu creators in a room, briefly explained them the prototype and got them to work on it.

The result was devastating. On the first try it took 3 times as much as creating the menu in the old way and even after getting used to how the spreadsheet works, we were only able to get it down to still taking twice as much. Our ambition was to save time! Still, the feedback was very positive. The menu creators liked this way of creating menus and they had some feedback on how we might be able to make it faster - and this task was on me. At this point, I set a goal of how fast I want them to be able to create a menu.

Let’s assume building the Object Recognition prototype and the spreadsheet cost Fave a total of 7 man-hours, that’s 420 minutes and I expected to have to work another 3 hours on the spreadsheet, resulting in a total of 600 minutes. If our prototype manages to save the menu creators 1 minute for each menu, they have to create 600 menus until the project results in a net positive. Not good enough! So my ambition was to manage to save 5 minutes per menu for this project to be worthwhile.

I’m not gonna go through the whole process of what I did and what were the challenges, you can read this later. I will cut right to the chase…

Suspense…

...

...

We were not able to fasten the process to save 5 minutes per menu. We were, however, able to make creating a menu just as fast as the old way. However, this being a prototype, we did not even account for a lot of extra configurations you can do for a menu, so the actual time to finish up the menu would then probably be longer again. We were not confident we would be able to reduce the time even more and this is when we decided to kill it using a simple calculation of man-hours spent vs man-hours saved. It’s very easy to lose yourself in trying to automize things and in the end never end up saving time. 

Automation
From https://xkcd.com/1319/

Now you may ask, how the hell do you manage to cut the time from 2x - 3x down to just taking the same amount? Well, I can tell you it was not easy, but I learned a lot about spreadsheet formulas, and I want to share those learnings with you. I will go through the different challenges I had and how to solve them.



This is what it looks like

What you’re seeing here is that me filling fields like type or price will automatically detect below what to fill and will automatically show me with the color red what I still have to fill based on the type.

Let’s go one by one:

Automatically color-coding fields

Automatically color-coding fields

Let’s start with the easiest: Making sure that fields are dynamically colored to signify the user needs to input something.

For this, we can use Sheets function Conditional Formatting which allows you to "format cells if custom formula is".

Conditional formatting

In this case how I colored the columns description [E], category [F], preference_type_1 [G], preference_type_2 [H], preference_type_3 [I]. Custom formulas for Conditional Formatting work very similar to formulas in Spreadsheets in general. You have the same functions at your disposal and you basically need to create a formula that, if entered in a cell, would display TRUE.

In this case, the formula was =and($D2=“category”,E2="").


Let’s break it down: $D2 is type and E2 is description, the cell that we want to color red. So if D2 is category (which it is) and E2 is empty, which it is, the cell will be colored red. If E2 was not empty, it would be red, telling the user that they do not have to fill the field anymore.

Automatically pre-filling dropdowns

Automatically pre-filling dropdowns

Dropdowns such as in description, category or preference_type_1, etc… are actually pre-filled, allowing the user to simply type the first 1-2 letters and then select it pressing enter. Much faster than typing everything by hand! For this, we make use of the ability to use ranges in the feature Data validation:

You can see that in this case, you cannot simply put a formula that does everything for you in there. Rather, we are using a hidden helper-sheet where we have the formulas that create the list for us automatically that is then referenced from the Data validation feature. I specified the range auto_index!A2:A1000, so let’s see what we can find there.

In the screenshot, you can see the column A, which basically displays all the categories that the user had previously annotated in the sheet. I will explain to you how we pull this data not only from one sheet but from two sheets and organize neatly here. Why two sheets? In my spreadsheet, I am also giving the opportunity to add items in a different place that the Object Recognition prototype might not have caught.

This is the formula:

=unique(FILTER({
  QUERY(annotation!A2:D,"select B where D = 'category'");
  QUERY(final_categories!A:C,"select A")},
        LEN({
  QUERY(annotation!A2:D,"select B where D = 'category'");
  QUERY(final_categories!A:C,"select A”)})))

Pretty complex, so let’s break it down. At its heart is the QUERY function. We’re looking at the first one first: QUERY(annotation!A2:D,"select B where D = ‘category'"). It is receiving two parameters. The first parameter is the data source for where the function will get its information from. The second parameter is the actual query. If you’re familiar with SQL, you will feel at home here. Since in Parameter 1, we specified that we’re selecting data across column A to D, we can reference each of these columns and you can even specify the order in which you want them to be returned. In this example, we are selecting only the column. Additionally, we are filtering, as column B includes all items, not only categories. We do this by making use of column D in which the user has specified before whether an entry is a category.

Then you will see that next to the query function is another query function separated by a semicolon ; and overall it is  encompassed by curly brackets { }, resulting in {QUERY(annotation!A2:D,"select B where D = 'category'");QUERY(final_categories!A:C,"select A")}. This means that whatever is within the curly brackets, separated by ; will be returned in one table. This is basically what fuses together the categories from both sources into one list. Additionally, this function has another power-up. It actually allows combining multiple two-dimensional tables into one one-dimensional table. We’re not using this functionality, but I needed it for a previous use-case. Let me explain to you what it does. Imagine you have two tables like this and it would merge it into one:

2 2-dimensional tables become one 1-dimensional list

You might not think this is useful, but if you’re ever in the situation that you need this and you cannot find anywhere on the internet on how to do it, think back to me. You’re welcome.

So how does it work? This is where the FILTER function comes in combined with the LEN function. The structure looks like this:

FILTER({QUERY(…);QUERY(…)},LEN({QUERY(…);QUERY(…)}))

The FILTER function takes two parameters, first all your QUERY functions, which select the different tables that you want to combine and then exactly the same input encompassed by the LEN function, split by a comma. Let’s make it simpler:

FILTER( { A;B;C } , LEN( { A;B;C } ) ).

I’m not exactly too sure how it works, but I know it works. You’re basically telling the FILTER function that these are your tables and the length of each table and the FILTER function does all the rest to put it in one column. 

But we’re not done! We’re wrapping all of this with the UNIQUE function, which simply ensures that no value can be displayed twice, in case you have the same description twice on your menu.

Automatically pre-filling dropdowns

If you’ve checked the GIF above closely, you will see that based on what the user fills, the cells below automatically get pre-filled. For example,. if I specify that the cell in the column B with the value 5.8 is price, any cell with the value 5.8 will be specified as price. Similarly, if I specify that the cell in column B with the value small is preference, any cell with the value small will be specified as preference. This is extremely useful when filling menus, as a lot of these things are repetitive and it allows the user to focus on what actually requires their manual input.

So how do you do this? The most straight forward way is to simply display the same table in another place using the QUERY function and then use VLOOKUP to search in that table. Check this example:

Here columns I and J are simply populated using query:

=query(D6:E19,"SELECT *”)

The cells in column E have the formula =IFERROR(vlookup(D6,I$6:J$19,2,false),””), which basically checks for the value in column D in column I and selects the respective value in column J if found. So let’s put this into E6 and see what happens:

Oops

Hm. This is not what was expected. Sheets does not allow circular dependencies just like that. Circular dependency means that you are referencing a field that will only be calculated based on what is in the field that you are currently calculating 🤯. Sort of a chicken-or-egg problem. Well, we can fix that. Simply go into Spreadsheet Settings, then go to Calculation and to Iterative Calculation. I found a maximum number of calculations of 2 to work well, so you can just copy that.

And the result iiiiiiiisssssssss:

Almost there!. Don’t ask me why it displays 0, but it ends up displaying 0 everywhere, which is not what we wanted to achieve. It will just show 0 and the user will not gain a better overview. So let’s fix this with a simple adjustment: 

=IF(IFERROR(vlookup(D6,I$6:J$19,2,false),”")=0,"",IFERROR(vlookup(D6,I$6:J$19,2,false),""))

If our previous function IFERROR(vlookup(D6,I$6:J$19,2,false),””) equals 0, we will not display anything, otherwise, we execute our function again. 

That’s it for now! I hope you learned something just as I did and can use these examples to build your own spreadsheet automations. 

Pro tip: If I have had the time, I would have actually wanted to build this in Coda, as it gives the user much more the feeling of an App. If you don’t know Coda, check it here and how I use it to automatize my life here.

Love the solutions & automations I am building using simple tools? Contact me if you want me to create one for you!

other posts