The challenge

Did you know that the first ever IoT device was a vending machine? They are still one of the most iconic IoT use-cases, and although many still aren’t connected, with more than 6m machines in the USA alone there’s plenty of potential.

As usual with IoT, here its purpose is to enable the vendor to deliver a good service, efficiently. We want to:

• Keep the machines well-stocked
• Understand what stock is needed to refill each machine (so we can do that efficiently)
• Gather stats overall on what goods are selling, where
• Collect information on faults

The data

Each time a product is vended the machine sends a message to the Cloud which looks something like this:

{
    $id : 1234,
    $ts: 2019-10-08T14:23:00,
    “vend_event_product” : “Hershey’s Milk Bar 4oz”
}

Each time the machine is replenished (refilled-up with goods), it sends a message something like this:

{
    $id: 1234,
    $ts: 2019-10-09T09:12:44
    “event_replenish” : true              // (the value doesn’t matter, it’s just an event)
}

The solution

Naively we might imagine that every time a machine is replenished, the driver fills it completely full again. But that would mean that:

• Slow-moving stock languishes in the machine for a long time, expiring and tying-up cash
• The driver has to carry an enormous amount of stock to ensure that they can replenish machines in any state

A cunning insight is that - on average - the amount of each product needed to replenish a machine is simply the amount of that product that has been sold since the machine was last replenished, so let’s measure that.

Our solution will rely on two aspects of DevicePilot:

• Every message is timestamped, therefore so are all the properties that change in that message, and we can ask DevicePilot when each property was last changed, which is known as its timestamp.
• For properties that are only sent occasionally (i.e. not in every message), such as with our occasional replenish events, the value of that property (and the timestamp when it was last changed) gets “dragged” through to all later messages. Therefore all product vend events after the last replenish will have an event_replenish time of that last replenish.

So firstly, for each machine, we ask:

last_replenish_time = LAST(TIME(event_replenish))

to get the time that the machine was last replenished.

Then we go through all the vend_event_product events, summing how many of each product type have been sold. The trick is to then JOIN BY last_replenish_time so that we only count the products where this matches, i.e. the products that have been sold since the last replenish of that machine.

We can then GROUP BY e.g. county or route to give each delivery driver a list of what they need to put into their truck.

Conclusion

Here we've seen how by combining streaming and relational analysis we can deliver the information needed to run a business efficiently. See more ways to get business value from combined analysis.