eCommerce Stories | Automating Order Placement & Data Entry

This is the first in a series of posts that will describe software I have built for my full time employer. In addition to a technical overview of the software, these posts give me an opportunity to reflect on how my work impacts the day to day operations at work.

Summary

This article describes why and how I created a JavaScript application that automates a large portion of order placement for my employer. I believe that my app saved my employer from having to hire at least 1 full time data entry level employee at a salary I would estimate at $30 - $35K per year. In reality, the automation also freed up existing employees to pursue other tasks that benefit the company. We are a small eCommerce website in the process of leveling up.

I am proud of the application, for its simplicity and effectiveness in solving a problem, and of course it makes me look valuable in the eyes of my employer. Despite all these facts, I can't help but calculate my financial gain versus that of my employer and be a little frustrated. I suppose that is a common grievance in just about every line of work.

The Problem

The essential problem is dealing with growth. Systems that work one day, utterly fail as they are forced to scale up.

Our eCommerce business sells high-end fabrics and wallpaper, from multiple suppliers, that were traditionally only available to interior designers. A typical purchase can range from a few hundred to thousands of dollars. Since these purchases involve a substantial financial commitment, we offer samples for $5 a piece. These enable customers to experience the material first hand, and allow them to make a more informed choice. This is an industry standard. It is a valuable service for the customer and also provides a revenue stream for the business.

Once we received a sample order through our website, a salesperson would then order the sample from the supplier. Suppliers either have an online interface to enter customer and product information or require an email with all these details (small companies without website forms).

This system was fine with low volume, but did not scale well. As sales grew, our main sales staff was too busy selling regular orders so we hired a full time person specifically to process sample orders. As orders continued to grow, I was occasionally asked to help out for a couple hours a day at first, and eventually devote entire works days to ordering samples. I quickly realized these online interfaces were designed for individual interior designers, not for bulk purchasers. They were slow and inefficient. On top of that, the work was boring - basic copy and paste data entry.

Eventually, we attempted to hire a second samples processer. Two separate people quit after two days, presumably once learning what the job really entailed.

At our lowest point we were 10 days behind in placing these sample orders. At a certain point, blaming everything on the pandemic wasn't cutting it. We started to get phone calls from customers who wanted to know why they hadn't received their order. This served to slow us down even more, with our samples person spending more time on the phone than ordering samples.

Potential Best Solutions

The person who signs the paychecks at my company - the Boss - came to be and asked what we could do about this.

Unfortunately, only one of our suppliers had an EDI system set up. This is the ideal solution, and amounts to batch ordering. In this scenario we would be responsible for uploading a document in some previously agreed to format that contained all the orders for a given day.

Another solution is to outsource. As a programmer and problem solver, I am always against this option - at least initially. If someone else has a solution that involves some sort of code, I know that I can also come up with something. There is also added cost and a loss of control over the process. On the other hand, it could be a great solution in some cases. I don't really know first hand.

Since EDI was basically off the table, we had to come up with something else.

The Solution We Used

A few weeks after I was sucked into this problem, the solution fell into my lap.

We were experiencing issues with the order interface on a popular manufacturer's website. Basically, the there were critical errors that made it impossible for us to order samples. When we reached out to the company in question they decided we should just email our orders and that they were giving us a dedicated person on their end to make sure our email orders were placed. This service was granted based on our high volume.

I thought, what if everyone did that for us?

Quickly and persuasively our management reached out and got all of our suppliers to agree to this email model. Now I had the conditions present to create a sample order application.

The Development Process

In order to simplify and speed up the development process I decided to lean on my preferred language of JavaScript and designed a Node based command line application. This application would take user input in the form of an order number and some option flags, then consume our REST API to gather order details, format a text email, and then use the SendGrid API to send out multiple emails to our suppliers in rapid fire succession. It is not uncommon for a single order to require 5 or more separate emails since we sell product from so many suppliers.

Alternatively, I could have designed custom PHP code, or what is commonly called a plugin, to work with our WooCommerce (WordPress) based website directly. Perhaps, this will be the eventual, long term solution, but I decided against this initially, since it would take longer to develop. On the other hand, if its not broke don't fix it.

With my Node CLI approach, I was able to write the script to solve all our problems in less than half a day.

How It Works

I already had a command line application for work related tasks so integrating this order placement functionality into this preexisting framework made sense. For as basic introduction into creating command line applications with Node, check out my article How To Build a Command Line Application With Node.

The CLI is built with a great framework called Commander that allows me to create a main command with multiple subcommands. Each subcommand is responsible for a different task and can accept a number of inputs in the form of flags. Flags can pass Strings, Numbers or Booleans to the underlying business logic.

Command Structure

For this application to work it requires a number of inputs. First and foremost is the order number from our WooCommerce system. In addition, the application also has a test mode, that prints out all the emails before sending them. This is a great feature to make sure that all order items are being processed and that everything is formatted correctly and being sent to the proper destination. There are some other options, but in the interest of time I will omit those from this discussion.

The final command that is entered into the terminal might look something like this.

A generic version

main-command subcommand --flag1 input1 --flag2 input2

A real world example. Note that flags have a long and short form, differentiated by a single dash and single letter -f or double dash and word --flag.

ladc samples -o 1023356 -t

The above example would trigger a test for order number 1023356.

The code is fairly simple using Commander. The framework allows you to define your flags, which also appear when the help command is triggered, and also parses them to be used as variables in your business logic.

#!/usr/bin/env node

const program = require('commander')

program
  .option('-o, --order <number>', 'Order number to process')
  .option('-t, --test', 'Enable test mode', false)
  .parse(process.argv)

async function main() {
  const {
    order,
    test
  } = program

// use variables in logic...
}

main()

Dependencies

Besides Node itself and the previously mentioned Commander CLI framework my application requires three libraries - the WooCommerce REST API, SendGrid Mail API and the Klaviyo API. We already had a paid SendGrid account for some transitional emails, but even their free tier allows a generous number of emails per day.

Fetching The Order Data

The first step in the business logic is to use the WooCommerce API to lookup with data associated with order number supplied to the command line. This data includes all the relevant customer information - name and shipping address, as well as the line items the application will loop through looking for, and grouping samples by manufacturer.

const WooCommerceRestApi = require('@woocommerce/woocommerce-rest-api').default

const WooCommerce = new WooCommerceRestApi({
  url: 'http://example.com',
  consumerKey: process.env.WOOCOMMERCE_KEY,
  consumerSecret: process.env.WOOCOMMERCE_SECRET,
  version: 'wc/v3',
})

async function main() {
   const {
    order,
    test
  } = program

   try {
      const res = await WooCommerce.get(`orders/${order}`)
      const { first_name, last_name, address_1, address_2, city, state, postcode } = res.data.shipping
      const items = res.data.line_items

      for (let item of items) {
         // 1. check if item is a sample
         // 2. if it is a sample, group by manufacturer
      }
   } catch(error) {
      // log it or do something else...
  }
}

main()

Example WooCommerce Order JSON Object

Straight from the documentation.

{
  "id": 727,
  "parent_id": 0,
  "number": "727",
  "order_key": "wc_order_58d2d042d1d",
  "created_via": "rest-api",
  "version": "3.0.0",
  "status": "processing",
  "currency": "USD",
  "date_created": "2017-03-22T16:28:02",
  "date_created_gmt": "2017-03-22T19:28:02",
  "date_modified": "2017-03-22T16:28:08",
  "date_modified_gmt": "2017-03-22T19:28:08",
  "discount_total": "0.00",
  "discount_tax": "0.00",
  "shipping_total": "10.00",
  "shipping_tax": "0.00",
  "cart_tax": "1.35",
  "total": "29.35",
  "total_tax": "1.35",
  "prices_include_tax": false,
  "customer_id": 0,
  "customer_ip_address": "",
  "customer_user_agent": "",
  "customer_note": "",
  "billing": {
    "first_name": "John",
    "last_name": "Doe",
    "company": "",
    "address_1": "969 Market",
    "address_2": "",
    "city": "San Francisco",
    "state": "CA",
    "postcode": "94103",
    "country": "US",
    "email": "john.doe@example.com",
    "phone": "(555) 555-5555"
  },
  "shipping": {
    "first_name": "John",
    "last_name": "Doe",
    "company": "",
    "address_1": "969 Market",
    "address_2": "",
    "city": "San Francisco",
    "state": "CA",
    "postcode": "94103",
    "country": "US"
  },
  "payment_method": "bacs",
  "payment_method_title": "Direct Bank Transfer",
  "transaction_id": "",
  "date_paid": "2017-03-22T16:28:08",
  "date_paid_gmt": "2017-03-22T19:28:08",
  "date_completed": null,
  "date_completed_gmt": null,
  "cart_hash": "",
  "meta_data": [
    {
      "id": 13106,
      "key": "_download_permissions_granted",
      "value": "yes"
    }
  ],
  "line_items": [
    {
      "id": 315,
      "name": "Woo Single #1",
      "product_id": 93,
      "variation_id": 0,
      "quantity": 2,
      "tax_class": "",
      "subtotal": "6.00",
      "subtotal_tax": "0.45",
      "total": "6.00",
      "total_tax": "0.45",
      "taxes": [
        {
          "id": 75,
          "total": "0.45",
          "subtotal": "0.45"
        }
      ],
      "meta_data": [],
      "sku": "",
      "price": 3
    },
    {
      "id": 316,
      "name": "Ship Your Idea – Color: Black, Size: M Test",
      "product_id": 22,
      "variation_id": 23,
      "quantity": 1,
      "tax_class": "",
      "subtotal": "12.00",
      "subtotal_tax": "0.90",
      "total": "12.00",
      "total_tax": "0.90",
      "taxes": [
        {
          "id": 75,
          "total": "0.9",
          "subtotal": "0.9"
        }
      ],
      "meta_data": [
        {
          "id": 2095,
          "key": "pa_color",
          "value": "black"
        },
        {
          "id": 2096,
          "key": "size",
          "value": "M Test"
        }
      ],
      "sku": "Bar3",
      "price": 12
    }
  ],
  "tax_lines": [
    {
      "id": 318,
      "rate_code": "US-CA-STATE TAX",
      "rate_id": 75,
      "label": "State Tax",
      "compound": false,
      "tax_total": "1.35",
      "shipping_tax_total": "0.00",
      "meta_data": []
    }
  ],
  "shipping_lines": [
    {
      "id": 317,
      "method_title": "Flat Rate",
      "method_id": "flat_rate",
      "total": "10.00",
      "total_tax": "0.00",
      "taxes": [],
      "meta_data": []
    }
  ],
  "fee_lines": [],
  "coupon_lines": [],
  "refunds": [],
  "_links": {
    "self": [
      {
        "href": "https://example.com/wp-json/wc/v3/orders/727"
      }
    ],
    "collection": [
      {
        "href": "https://example.com/wp-json/wc/v3/orders"
      }
    ]
  }
}

Looping Over Line Items

With the raw order data information now read into the application's memory, the next task is to loop through the line items and perform what amounts to filtering and reducing (or grouping).

The filtering is simple enough. In our system a sample is considered a singular product. In fact, the details - such as the name and SKU of the sample are determined by meta data.

The biggest challenge turns out to be sifting through the data structures. In the end the Array.find method comes in handy to find the Name and SKU. With this Name value it is now possible to map the current sample to what we already know about brands, their manufacturers and the email of the person we need to send the order to. All sample names follow the same pattern - BRAND PATTERN_NAME COLOR CATEGORY.

As I created this business logic I also created my own data structure containing all of the brands. It ended up being a big array - we offer circa 80 brands.

const BRANDS = [
   {
      name: 'NAME OF BRAND',
      to: { name: 'NAME OF CONTACT', email: 'EMAIL OF CONTACT' },
      samples: []
   },
]

Now I can get the name of the sample and the loop over the BRANDS to store like with like. The only complicating factor is that multiple brands can come from the same manufacturer or distributor, and must be grouped even further at a later stage.

for (let item of items) {
  if (item.name === 'Sample') {
    let name = item.meta_data.find((el) => el.key === 'Name')['value']
    let sku = item.meta_data.find((el) => el.key === 'SKU')['value']

    for (let brand of BRANDS) {
      if (name.startsWith(brand.name)) {
        brand.samples.push(`${name} ${sku.slice(sku.indexOf('-') + 1)}`)
      }
    }
  }
}

Grouping Samples By Manufacturer

The line item loop effectively group samples by brand. Each relevant brand will have an array of string that describe each sample we intend to order. The next step is to consolidate the brand arrays to manufacturer arrays. I often used the first brand, because in our industry the manufacturer typically has a brand with the same name, in addition to many other brands with other names. In can get kind of confusing.

    var arr1 = BRANDS.find((el) => el.name === 'Brand A')['samples']
    var arr2 = BRANDS.find((el) => el.name === 'Brand B')['samples']
    var arr3 = BRANDS.find((el) => el.name === 'Brand C')['samples']

    BRANDS.find((el) => el.name === 'Brand A')['samples'] = [...arr1, ...arr2, ...arr3]
    BRANDS.find((el) => el.name === 'Brand B')['samples'] = []
    BRANDS.find((el) => el.name === 'Brand C')['samples'] = []

Formatting The Email

With the samples grouped by manufacturer I can now loop over BRANDS to format the emails that will be sent to each suppliers samples contact. As a sidenote, this whole episode gave us insight into our ability to set terms with our suppliers. Since we provide a lot of business, these companies are willing to provide resources to us to facilitate our operation. As I mentioned earlier, the vast majority of our suppliers are set up to sell to individual designers, not a service like ours that sells direct to consumer with a high level of customer service.

Our purposes require a simple email. Using the name and email that are predefined, all that is left to do is some formatting and looping over the samples. Below is a simplified version of what we use, there are specific changes per manufacturer but those aren't important here. When the -t or --test flag is present the email content is logged to the console, otherwise the email is sent using SendGrid. The cool part is that our contacts did not realize we had switched to an automated system. One day, one of our suppliers asked us how we sent so many orders so fast.

const sgMail = require('@sendgrid/mail')
sgMail.setApiKey(process.env.SENDGRID_KEY)

// ... more code here

for (let brand of BRANDS) {
  if (brand.samples.length) {
    var text = `Hello${brand.to.name},\n\nCan we please have the following samples sent to the address below?\n\n`

    for (let sample of brand.samples) {
       text += `${sample}\n\n`
    }

    text += `${first_name} ${last_name}\n${address_1} ${address_2}\n${city}, ${state} ${postcode}\n\n`

    text += `Please include sidemark: ${order} on paperwork with samples.\n\n`

    text += `Thanks,\n\n${from.nickname}\n\n${from.name}\nBusiness Name Here\n${from.email}\n`

    let subject = `Sample Request - ${order} Client - ${last_name}`

    let message = {
      to: brand.to.email,
      cc: from.email,
      from: from.email,
      subject,
      text,
    }

    if (test) {
      console.log(`=======================TEST MODE=======================`)
      console.log(`To: ${brand.to.email}`)
      console.log(`From: ${from.email}`)
      console.log(`Subject: ${subject}\n`)
      console.log(text)
    } else {
      await sgMail.send(message)
      console.log(`Email sent! To: ${brand.to.email} Brand: ${console_brand}`)
    }
  }
}

Final Thoughts

This article was more of a personal record of the work I have done than it was a tutorial. That being said, it seems that this content could inspire a similar application for any number of purposes. What made this application interesting to me was how it solved a significant problem for my employer - it saved tens of thousands of dollars and hours of repetitive work. There is an interesting voice in my head connecting this relatively small example to the overall trend in society - that is technology replacing human employment, but in this case everyone disliked this task and multiple people quit after a couple days doing it. I am also left with a deeper understanding of how powerful code can be - one day we had an ad posted and as soon as I deployed this app it was down for good. This is a satisfying feeling.