Tracking Customer Usage Levels – Excel and Outlook

Background

Skillname
55%
Skillname
55%

A large firm needed to find an easy way to monitor their storage tanks in remote locations, used by both clients and internally.

The tanks were fitted with a transmitter that allowed the company to request information on demand (via email) regarding the current capacity of the tanks. If the tank usage was above 85%, a new tank would need to be shipped out to that location before it was finished.

However, the company had over 500 tanks that needed monitoring on a daily basis, and they had been using Microsoft Word’s mail merge feature to send emails to the tank transmitters – but, crucially, were manually processing the email response from the tanks. This meant copying information from 500 emails, on an daily basis, into spreadsheets.

The process currently took over 3 hours per day; they could then send the list down to the distribution team who had no more than 3 hours to get shipments prepared and ready to sent out to customers that day. This often led to shipment deadlines being missed if a large number of tanks needed to be replaced in one day – as 3 hours would sometimes not be enough to get everything ready. Forecasting would only prepare them up to a certain point i.e. every Monday usage is always higher, but some weeks multiple days are busier than Mondays.

It also left a large room for error as manually entering information extracted from emails that if a information was missed or put in the wrong place, an order would not be created for a custom in need of another tank as data would be inaccurate e.g. if 9% was entered instead of 90% – a shipment would not go out for this client.

 

Solution

Microsoft Excel is able to integrate into other applications – such as Microsoft Outlook. This means Excel can send emails and read received emails.

[responsive]014_Img001[/responsive]

By deriving a table with tank information and email addresses, Excel is able to automatically send out emails to every tank, requesting information.

014_Img004

After the final email (of 500) has been sent out, Excel starts to monitor the Outlook inbox for new messages that were received, after the first one was sent out (by recording the time the first email was sent out).

[responsive]014_Img002[/responsive]

If a new message is received, it checks the sender’s email address. This will match up to a unique record on the list of tanks (if it’s from someone else, it is ignored). Once a match is found, it checks the body of the email for a specific phrase: “Usage”. It will take the next few characters after this phrase until it returns a usage amount e.g. “65%”. It will then place this in the cell next to the matched email address in the spreadsheet. It will then file the email away in a predesignated folder, and continue polling the email inbox until this process is completed.

[responsive]014_Img003[/responsive]

In total, this takes on average 9 minutes to complete (subject to Outlook server not slowing down) – a time saving of 95%. Three months after implementation of this process, the company has reported an increase in customer satisfaction from 79 to 96% due to on-time deliveries.