🐶Return to Good Dog People

Problem

After the postal code segmentation issue was solved, there were two more time-pressing activities.

1. Notifying customers that they should expect their orders at the doorstep on that particular day. This was done using a bulk messaging tool. However, the numbers were not registered in the Shopify database consistently. So the user had to take all the relevant numbers and format them to “6512345678”.
2. A lot of time was spent on customer orders for the day. Most of the time was used to figure out what products had to be ordered from the supplier on that day. The company has a same-day delivery option and frozen orders, so those orders had to be prioritised. However, there was always some form of human error. The user may have missed an order or double-ordered. This was something to be looked at.

The Solution

First Problem

Having practiced with Excel VBA. A simple macro was created to automate the cleaning process. User would just have to paste all the numbers (formatted correctly or not) and press the button for that particular macro. It would then clean up all the numbers if it was required.

SMSAutomation.pdf

AddingButtonExcel.pdf

Second Problem

To tackle this problem, we used a combination of Shopify tags and Excel macros. Using the tags, we could highlight the orders that were of priority. After that, using the SKU code format “XXXX-Product Code” (XXXX - specifying which supplier the product is owned by) we could categorise all the orders into the individual suppliers that had them. The user would just have to check in-stock items and update the list. Then the user would send the updated list to each individual supplier, cutting down on all possible human errors and speeding up the process.

SKU Automation (Mac).pdf

AddingButtonExcel.pdf

Summary

Review & Limitations

The solution for the first problem was a simple one. However, this repeated process felt redundant. The data should just be cleaned/updated on Shopify instead. This macro should have been used to update the database rather than formatting the customer numbers daily.

The solution for the second problem was a quick resolution to the existing solutions. However, there were some limitations. If the company had a new supplier that they were working with, the excel macro had to be updated. Then the excel with all the macros had to be updated on GitHub. So that all the machines using the macros were up to date. The more the company grew with partners/suppliers, the more this step had to be repeated.