Predict Off Shelf events before they happen

Clients often ask for this, some marketing materials claim to offer it, but can it be done?  It depends on what you want to predict.

Can you predict an individual OSA event:  the theft of a certain product at an exact time in a specfic store?  The loss of a shelf-tag, a store associate (or customer) blocking a replenishment spot, the misplacement and subsequent loss of product in the back room?

This is an emphatic, categorical "no, absolutely not, no way, never".   Think about it.  Do I really need to explain?

Can you predict when a store is going to run out of stock?

You bet!  We need a number of facts on inventory, orders and need to generate a store level forecast for demand by product, but this is (relatively) basic blocking and tackling work.  100% "yes" this can be done.  From an OSA standpoint though it's only responsible for about 25% of the overall problem and you can't do it very far into the future.

Can you predict which stores, products and categories are likely to have most problems with maintaining on-shelf availability?

90% "yes".  There is data to suggest that OSA is routinely different across categories implying there are structural reasons for this.  Similarly some stores routinely out-perform others.  It should be possible to build a predictive model using product, store and planogram attributes (drivers) to explain this variation in long term performance.  

It's not the prediction itself we are interested in, the model quantifies  the contribution of the various drivers of  OSA and that is what is valuable.  Once we know what drives OSA (and by how much) we can work to change those inputs we can control.  This is how you make a step-change in OSA performance.

So why haven't we done this already?  Ideally it should be run against OSA measures from physical audits which are not widely available,  and the ones I have seen are heavily biased in one form or another.  It could be run against off-shelf alerting system results which captures about 25% of the OSA problem but there are issues with both data volume and security to get a broad spectrum of products and stores into one analysis.  These can, I think, be overcome - watch this space.

Can you detect off-shelf events where there are persistent problems stocking the shelf, but before sales drop completely to zero?

Yes we could!  Analytically it’s not that much more difficult than checking for a gap against 0 sales.    (Although it's a little more challenging in terms of software implementation at scale.)

If we estimate that average sales over a period should have been 8.3 but actual sales were only 3 is that unusual?    A little stats can help:  if we assume a Poisson distribution for demand it turns out that the probability of selling 3 or less, given expected demand of 8.3 is 3.5%.  So, it’s unusual, but as this could happen by pure chance roughly 1 in 30 times, it’s probably not exceptional enough to call out an “off shelf” alert.  Actually, you won't call an alert at a 1% signifcance level until the expected demand exceeds 10.  Bear in mind that 10 units of sales per store is either a very fast-moving product or a very long evaluation period: the majority of grocery products sell less than 1 unit per store per week.   

This approach is really only relevant then for very fast-moving products.  You may also want to consider how you physically check for such an issue in store.  Chances are that the product is on the shelf much of the time.  How are you going to find the problem?

This post is the tenth in a series on On-Shelf Availability.  

Measuring Off Shelf Alerting Performance

My model is better than yours.  Or is it?  How do you really know?  When a company says that their tool is “more accurate” than others what do they mean?   If they can get 90% accuracy, is that good? 

There is no standard for how to measure the value of an off-shelf-alerting tool so it’s easy to be misled.  Furthermore, there is no single measure that will do everything you want it to.  We need at least two measures and I would suggest a few more.

Let’s start with “accuracy”:  the proportion of audited alerts that were found to be correct.

  • Remember that an off-shelf alerting tool cannot create an alert until a significant period of no sales has elapsed. It will never capture all or even most off-shelf events.  It should capture the more persistent (and costly) ones overlooked my routine store operations.  So, I’m not going to worry about the accuracy what is not flagged and assumed to be on shelf, these tools will not get that right.
  • Also, you should use the most recent alerts available when conducting an audit: if your field organization did not conduct the audit using the most recent alerts, you are not measuring the accuracy of the system but some combination of accuracy and data degradation.
  • Audits must have consistent rules as to what constitutes “on shelf and available for sale”.  To my mind it’s only on-shelf if:
    • The product is on the correct shelf
    • The product is visible (not hidden behind other products or at the back of high/low shelves)
    • The product is within reach of the average shopper.
    • The product is not damaged.

Accuracy is a useful metric, but it is not enough. 

Consider an OSA system set up so that it generate alerts for product-store combinations that normally sell 10 or more units a week but have sold nothing for at least a month.  Over that month "what you should have sold" is way beyond our basic rule of thumb threshold for an alert and such a system would generate very, very few alerts .  However, when it did fire an alert, you can be almost certain there was an issue.  Accuracy would probably be close to 100% but with almost no alerts we will capture very little value.  We need at least 1 balancing metric.

Ideally we want to know what proportion of the actual off-shelf positions we are capturing, but unless you have a very good OSA measurement scheme in place (more on that later) you just don’t have that information available.  I suggest a proxy:

Scope: the proportion of item-store combinations examined that yield an alert.

This is not quite as good but at least we have the data readily available.  If we test 10 million item-store combinations and generate 300,000 alerts, 3% scope.  Not bad.

(Scope is not sufficient by itself either of course, imagine a system that flags all item-store combinations as off-shelf every day .  Scope is now 100%, but with almost zero accuracy. )

The following chart shows performance of 3 OSA systems (X,Y and Z)

System Y is clearly the most accurate, 5 points better than the next best, system X.  However it also has the least scope, only 0.5% of all points of distribution (item-store combinations) get alerts.  In comparison, system Z generates 4 times as many alerts (and perhaps 4 times the value) with only 6 points loss in accuracy.

A great tool will let you trade-off scope for accuracy.  Reduce the confidence level used to generate alerts and accuracy will go down, but scope will increase.  Increase the confidence level, accuracy will increase but scope goes down.  You can flex this to gain trust in the system in initial rollout (thru higher accuracy) then reduce accuracy and increase scope to get more value once trust is established.

Off Shelf Alert Accuracy balanced by scope is a great start to look at performance, but we really want to look at value and ROI, more on this soon.

This post is the ninth in a series on On-Shelf Availability.  

Off Shelf Alerting and Sales Velocity

Let's talk about sales velocity.  Off shelf-detection algorithms are based on identifying a significant gap between what you expected to sell and what you actually sold (at a product-store level).  The basic system I described in "Off-Shelf Alerting tools are simple" will call out an alert when, for a period of time, you sold nothing, but expected to sell at least 5 units.  So how long should it take the average product at the average store to sell 5 units?  Rather longer than you might think.

The majority of supermarket grocery products sell less than 1 unit per store per week.  I have seen this borne out repeatedly in practice and it’s supported by other studies.  While I expect this feels wrong to you - you probably buy enough milk every week for this to seem wrong - it's real and has a huge impact on the value of off-shelf alerting, so please bear with me.

I can't share real data with you but I can generate something instructive from 2 key facts

Now, with a little math-magic I can figure out the average sales velocity for each 1% of the product range so that both these facts hold true.   The following results aren’t exact of course – some stores sell more than others, some have more products, some hold more closely to the Pareto assumption  but the results are representative and more importantly, instructive.

This is what Unit Velocity (unit sales per store per week) looks like.  There are a handful of very high velocity items (milk, some produce, fresh goods, soft-drinks, water, heavily-promoted items), but the vast majority clearly sell fewer, far fewer, than 10 a week. 

Let’s look at that long tail in a little more detail:  The bottom 90% of products sell less than 3.5 units per week and over 50% of products sell less than 1 unit per store per week.

 

What does this mean for off-shelf detection algorithms? 

Remember, the basic off-shelf decision rule is that we will call out an alert when you should have sold at least 5 units but actually sold none. With that in mind:

  • There are a few (very, very high-volume products) that could conceivably generate alerts on an hourly, intra-day basis.  My guess though is that if you really are out of milk, your customers may let you know faster than the algorithm can spit out a report.
  • Very few products have enough velocity even on a daily basis that 1 day of zero-sales is enough to flag an alert.
  • The “average product” will take weeks of zero sales before you can call an alert with any confidence.
  • Very low volume products may take so long it’s really not worth the effort in trying – thank goodness you haven’t lost too many sales waiting for that alert.

Now, this does not negate the value of off-shelf detection tools but it does help put things into context as to what they can (and can’t) do.

  • Being able to generate alerts slightly earlier in the day is of very little value to you.  Things just don’t change that fast.
  • Hourly data is of very little value other than for a handful of very high-velocity items (and I strongly suspect these will self-heal at store level before you can even plan any kind of external intervention). 
  • Daily data is useful for the top 40%-50% of products, for all others weekly data is fine.
  • It should come as no surprise that off-shelf detection algorithms generate far fewer alerts than you find when you do a physical audit: many of the off-shelf events get fixed by store operations before an off-shelf detection algorithm is capable of spotting them.

This post is the eighth in a series on On-Shelf Availability.  

Off-Shelf Alerting tools are simple

They monitor sales at the product-store level to spot when the difference between “what you did sell” and “what you should have sold” is unreasonably large. 

The most crude tools ask the user how many days of zero sales is a problem but these "guess the number" systems are practically unworkable.  Even a simplistic tool should do better than this.  

Without giving away any secret recipes here let’s go through a relatively easy approach based on real statistics that will work better than “guess the number” methods and start to give you a feel for what a real solution can do.

This is what we want to get to, a rule like.  “If you should have sold more than X (and you’ve sold nothing) call an off-shelf alert.”

Beware, there is a math beyond this point

It's not too bad though and I promise, no equations, no math notation. Stick with it.

First, let’s assume that retail sales follow a Poisson distribution.  A Poisson distribution is often used for modeling retail sales as it’s:

  • discrete (meaning it will only model unit sales of whole numbers);
  • can’t be less than 0 we are not implying negative sales
  • simple, because it needs only one parameter, average demand;
  • it’s often a reasonable approximation to reality.

Furthermore, let’s assume we have sold nothing now for product X at the target store for 3 days and that prior to this point our average sales over a 3 day period is 2.3 units.  This is what a Poisson distribution looks like for average demand of 2.3 units. 

It shows the probability of actually getting sales of 1,2,3, thru 10 units in the 3 day period.  As you can see there is a little over 25% chance of selling exactly 2 and about at 20% chance of selling 3.  It’s also possible (but very unlikely) that you could sell 8, 9, 10 or even more than 10 units.

Now, if this distribution really is a good representation of reality, how odd is it that we actually sold nothing at all in the most recent 3 day period?  This tells us that seeing no sales is going to happen about 10% of the time just based on random chance.   We probably don’t want to call out an off-shelf alert with such a high chance that nothing is wrong, so we wait…

When, a few days later, we reach the point at which you should have sold 4.6 units, and have still sold none, the probability of actually selling nothing through random chance is now just 1%. 

That’s the sort of risk you might be willing to take to call out that something seems to be wrong.  Ignoring errors in data, your estimation of average sales or your assumptions (perhaps it’s not a Poisson) you will be wrong about 1 in 100 times.

PoissonDist_mean_4_61.png

(FYI – you can model this easily in Excel using a POISSON.DIST function)

If you really want to be sure, wait a little longer.  At the point that you should have sold 6.91 units, there is only 0.1% chance that the zero sales you are seeing is due to random chance: far more likely in fact that there really is some issue inhibiting sales at the shelf.

Of course, had you called it correctly after lost sales of just 2.3 units you might have saved 4.6 units of incremental sales  (6.9 – 2.3 = 4.6).  Waiting helped you gain accuracy but it also cost you in lost sales.

Think of this probability of getting zero sales as a “sensitivity level”.  You can set it to whatever you feel most comfortable with (and find an associated average sales trigger point in Excel.)

Setting the right sensitivity-value then is a balancing act: 

  • choose a high sensitivity-value and you will
    • generate more alerts
    • catch problems earlier
    • but a higher proportion of your alerts will be wrong ;
  • choose a lower sensitivity-value and you will
    • generate fewer alerts
    • more of which will be right
    • but you will have lost more sales while waiting on the alert creation.

So, to get a decent balance between accuracy and number of alerts, your rule might look like “If you should have sold more than 5 units (and you’ve sold nothing) call an off-shelf alert.”  

This will give you dramatically better results that just “guessing the number” but it’s really only a starting point. 

The difference between a great OSA tool and a simpler one is the level of accuracy it brings to determining both “what you should have sold” and just how unusual “what you did sell” really is.  Better algorithms will yield both more alerts (capturing a larger proportion of the real problem) AND more accurate alerts (more of them are right).   

Here are some of the approaches a better tool might employ.

  • Using more appropriate distributions of sales. 
  • Getting more accurate estimates of "what you should have sold" by leveraging  similar stores, similar products and similar time periods
  • Using predictive models to account for day of week, day of month, seasonality and promotional activity.
  • Building teams of different models that collectively perform better than each individual member.
  • Reporting on sales lost while waiting for alerts and while waiting for intervention.
  • Estimating the lost sales that could be recovered by your immediate intervention (to prioritize your work).
  • Incorporating feed-back from field operations at to what was found at the store to further improve accuracy.

Question: How likely is it that a shopper encounters an off-shelf event?

Simple Answer: It’s almost guaranteed!

Good Answer:  It depends on the size of the shopping basket.  The more things a shopper buys, the more opportunities they have to find an off-shelf event.  If the shopper goes into store for a single item, and assuming the industry average on-shelf availability rate of 92%, then they have an 8% chance of finding an off-shelf. 

As the number of items on the shopping list increases, the likelihood of finding at least one off-shelf event increases rapidly (below).  

LikelihoodOfOffShelf.png

With a shopping list of just 20 items, there is already more than an 80% chance that at least one of those items is off-shelf.

So, how likely is it that a shopper encounters an off-shelf event?  It’s almost guaranteed.

This post is the sixth in a series on On-Shelf Availability.  Next week , we'll start to look at how off-shelf detection systems work by monitoring point of sale data.  Watch this space !

 

What happens when the product you want is not on shelf?

It’s something you encounter on most shopping trips with varying degrees of frustration.  For some products the shopper is happy to substitute (another flavor, another color, another brand, another size) and continue without, perhaps, noticing a problem.  For some products they are willing to delay purchase, get it on the next trip or go to another store.  In the most extreme case, their want can go unfulfilled or, recognizing that they must now shop elsewhere anyway, they may even abandon their shopping cart.

Clearly, different shopper choices impact the retailer and supplier differently but research suggests that both retailer and supplier stand to lose, on average, some 30%-40% of the lost sale in net revenue.

So, if you can increase on-shelf availability from 92% to 95%, both retailer and supplier should gain about 1% incremental revenue, net of changes in product or store substitution and purchase delays.

This post is the fifth in a series on On-Shelf Availability

On Shelf Availability in Pictures

I was at the store last week and took the opportunity to capture a few examples of the on-shelf availability problem.  This is the same store I blogged about previously, which had (as far as I could tell) perfect on-shelf availability the day it opened.  That was a few months ago now and while I consider this a well run store you really don't have to try too hard to find the holes on the shelf.  

 

 
20141204_121400.jpg

This was a quiet weekday, early morning but after overnight shelf-stocking should have been completed. 

I did not check the planogram for compliance, look for missing tags or deal with any slide and hide issues, this was a simple visual check of the categories I happened to be shopping in.   The real off-shelf position is undoubtedly worse.

 

 

 
20141204_123257.jpg

I did spot 2 special examples.   In the dog-food section there is a clear off-shelf b ut it also illustrates the difference between being physically and effectively on shelf.

With one product missing you can see that this is warehouse shelving, designed to hold a full pallet of product and it's over 40" deep.  It's well stocked right now with the Beneful product on the right but when that get's shopped down to the last 2-3 bags could you reach it?  I'm a tall guy but I can't reach and I'm not climbing into the shelving to get a bag of dogfood.  For me, it's effectively not on shelf at that point.

 

In the freezer section, the MorningStar Sausage Links are not off-shelf, in fact  there are a number of packs behind the damaged one at the front.

I just picked one of these for my basket and (like most shoppers) reached behind the damaged pack to get a "good" one.  

Once the good packs are gone, for many shoppers this is effectively off-shelf.

 

Grocery on-shelf availability averages about 92%.  It really does't have to be this bad: the shopping experience is compromised and both retailer and manufacturer are losing money.

This post is the fourth in a series on On-Shelf Availability

Searching for On Shelf Availability Solutions

Yesterday, I joined a class on On Shelf Availability run jointly by 8th and Walton, Crossmark, Rockfish and the University of Arkansas.  This was the pilot class and based on the feedback they solicited I'm sure it will be even better when they choose to run it again.

As a pilot, it was very well presented and covered a lot of ground.  I particularly liked some of the more introductory material:

  • Making it clear that an in-stock measure ("I believe I have product in the store") is not the same thing as on-shelf availability ("It's on the shelf where a customer can find it").  This is key because while we can easily get in-stock metrics they are in no way a substitute for on-shelf measurement.  On Shelf is much harder to measure well so most of us don't do it.  It can be done though and I'll post more on this later.
  • Highlighting the supply chain challenges of ensuring product is in the store at all
  • An excellent session on some of the issues with store operations and lack of process discipline that cause off-shelf problems

Frankly, it was a little lighter and less well organized on what to do about improving OSA, and that's a problem for most class attendees who, I think, wanted to come away with "here are the 3 things I need to do to fix OSA".

  1. We looked at SPARC (from Rockfish), the Walmart in-store mobile app that let's CPG reps (like Crossmark) query store data about specific products AND do something about the problems they find like requesting a shelf replenishment "pick" or printing replacement shelf tags.  
    This is a very useful app if you are an in-store rep and suspect an issue with a particular product, but not terribly useful if you are wondering which products or stores might have an issue.  (Bear in mind that many CPGs have millions of item-store combinations with Walmart and even relatively small suppliers can have tens of thousands - you can't check these one by one)
  2. We briefly reviewed an off-shelf detection solution from Atlas.  This tracks the daily point of sale data for all your products at all stores and spots periods of zero sales that are unusually long, indicating an off-shelf position.  This sounds simple enough but doing it accurately is an analytic challenge.  (More on this later).
    I know enough about this particular application to believe the math is sound and I'm hearing good things about it in the community.  (Full-discosure: I may well be working with them very soon).  
    We did not talk about how a good off-shelf detection system accurately generates alerts for just 1%-3% of all item-store combinations on any given day.  In contrast, physical audits generally find 8% are not on shelf.   Why the gap?  Well it takes some time for alert detection systems to spot an issue (very low-volume items take so long it's almost not worth checking them) and of course many off-shelf problems are short-lived and corrected by store operations long before it can be spotted by lack of sales.  The off-shelf detection system should be finding the medium to high velocity items that are not being routinely corrected by store operations, the ones that really matter to you, but it's not a complete solution.
  3. We had an introduction to Field Agent a fascinating application that crowd-sources the ability to check and record  store conditions.  You could use this to check whether your product is on -shelf: Field-Agent simply asks people in the right stores at the right time (via their mobile app) to check the shelf for your product and,if you wish, take a picture of it.
    This is an amazing idea for in-store data collection generally, but, for this application it only provides "eyes and ears" not a rep who can fix the problem.   Note also that  while Field-Agent could detect many more off-shelf conditions than a pos-based off-shelf detection system, it can only do it by checking every item in every store near continuously (which would be costly) and many of the additional problems found will be for lower volume products or temporary off-shelf conditions that are fixed by routine store operations.
  4. Finally, we looked at a tool from the University of Arkansas that (among other things) estimates the impact to on-shelf availability of changing case-pack quantity (the number of sellable units in each case).  I know this tool very well indeed having contributed heaviliy to it's development - right now, it's still under testing with a release planned in the early part of next year so the class did not get a very good look it.  That's a shame because it's the only tool in the set that attempts to associate On-Shelf Availability with a root-cause.  Not the only root-cause of course, just one we happen to know about at this point. 

Fixing OSA is a tough problem - perhaps that's why I'm so interested in it?  We don't have all the answers yet, but I do believe these are the right tools to be tacking the problem.  However, they need to work in tandem not in competition:

  • Off-Shelf detection systems, like the one from Atlas, scan the entirety of the system, all products in all stores every day to find the problems that are not being fixed by store operations and are worth fixing.
  • In-store reps (armed with SPARC for Walmart stores at least) help store associates fix these problems efficiently and provide feedback on what was wrong to further improve the Off-Shelf detection system.
  • Field Agent could be the basis of a superb OSA measurement system.  Measuring when shoppers are actually in store (evenings and weekends) rather than when reps are typically there (business hours) and using a well constructed sampling plan to avoid measuring everything.  Today, on-shelf availability is measured poorly if at all and that's going to make it very hard to improve.
  • Continuing research to identify additional root-causes of poor On-Shelf Availaibility so that we can work to reduce the size of the problem and fix fewer off-shelf events one by one.  What's preventing this now, I would argue, is the lack of a good measurement system.  Neither I (nor anyone else) can't build a predictive model on what drives good On-Shelf Availability with no reliable data for On-Shelf Availability :-)

This post is the third in a series on On-Shelf Availability.

 

 

On Shelf Availability is (almost) all about the last 100 yards

When CPGs look at their supply-chain they can take some comfort in relatively high fill-rates to their retail customers, often shipping 98%-99% of everything that has been ordered by a retailer DC.   Similarly, retailers’ DCs generally have very high service levels supplying store orders.  Indeed, often stores will report that they have enough inventory to meet short term demand on a daily basis for over 99% of their products.   

It all sounds like a well-oiled machine.  So why is it that the chances of finding the product you want on shelf are only about 92%?  It can’t be down to issues with product supply to the store.

This post is the second in a series on On-Shelf Availability

For sure, there are exceptional supply problems that are big enough to eliminate the inventory buffers at the DCs and stores leaving empty shelves but these are few and far between.  Most of the problem is with retail operations within the store and you won’t fix it by pushing your supplier service level from 99% to 99.2% or increasing your on-time arrival rates by a few points.

So what really causes off-shelf events?

Shelf-replenishment issues

Missing shelf tags.   The shelf tag is the small label attached to the shelf that tells you the price of a product.  It also defines where on the shelf that product should go and in some cases how many facings (spaces) on the shelf it should take up.  I’m sure there are other reasons that these tags go missing, but I can tell you from personal experience that curious young children pick them off the shelf for fun. (Sorry about that, they seem to have grown out of it now).  Regardless of the cause, once the shelf-tag is missing there is no longer a visual cue to the associate re-stocking the shelf that something should be there and when the product on shelf has been depleted, it could stay that way.

Slide and hide.  Gaps on the shelf look unsightly: they look as though you are not managing your shelf correctly.  If you really can’t find the product, what harm does it do to take a little of the product from the next facing and slide it across to hide the gap?  Looks better doesn’t it?  Perhaps it does but now when that product is found and you go to replenish the shelf there is no visual clue that it needs replenishing and no gap to put it in without additional work.

Over-replenishment.    An associate has 12 new cans of product to add to the shelf but the facing only has room for 8…what to do?  The additional 4 should be taken back into back-room storage and logged back into storage, but wouldn’t it just be easier to overflow into the next facing?  It’s slide and hide with a new

Hidden/unsaleable product.  There are many ways to make physical inventory unavailable to the customer.  Here are a few examples:

  • Baby food at the back of a 6’ high shelf is not available for sale to most of the shoppers.  Tinned cat-food at the back of warehouse style shelving, over 40” deep,  is visible but unavailable to anyone without a means to get it out.     (Both of these are real-life examples).
  • Product that is physically on the shelf, even in the right slot but with a layer of other product in front of it is, effectively, not there. 
  • Product physically on the shelf but not even close to where it’s supposed to be is unsaleable.  (You can probably thank a customer for moving it)
  • Product that is damaged or beyond it’s sell-by date is effectively unsaleable whether it’s visible and reachable or not.

Inventory issues

Phantom inventory.  Phantom Inventory is inventory that exists in the system but not in reality: essentially, inventory that has gone missing, which is inventory that cannot be re-used to replenish the shelf.  Furthermore, because it exists in the system, it prevents (or delays) ordering of new product, causing further shortages on shelf.  How did it go missing?

  • Perhaps you never received it in the first place.  Errors on receipt could mean your system thinks you have 2 cases of apple sauce, not the one you actually brought in at the dock.
  • Lost inventory.  With over 50,000 products in the average grocery store, and well over 100,000 in a super-center it’s perhaps not too surprising that they lose something every so often.  Keeping strict process discipline across a large and rapidly turning workforce must be hard enough, but as they also allow customers like you and me to come into the store and interact directly with the merchandise it’s a miracle more things do not get lost.
  • Shrink.   Some customers, sadly, interact with the merchandise in less honest ways than others. 
  • Scanning errors.  The classic example here is buying single-serve yogurt.  You have selected half a dozen flavors of yogurt, same brand, same size, and when you come to check out, the associate scans one, tells the system there are 6 total and bags them.  I still see this on a regular basis.

Planogram Issues

Peak vs average demand.  Planograms are typically designed around average demand, but some products (categories) even have more wild fluctuations from the average than others.  If the peaks are exceptionally high, regular demand can empty a shelf on peak days quickly enough that the shelf replenishment process cannot keep up.

Replenish to back-room.   Ideally, when new product arrives at the store, it is moved directly to the shelf.  This is cost-effective from a labor standpoint, but there is also some evidence to suggest that products which can be replenished directly to the shelf have better on-shelf availability.   Where the replenishment quantity is too big to fit in the shelf space available it must be stored in the backroom with extra labor, an increased chances of loss and shelf replenishment problems.

Predicting off-shelf before it happens

Having reviewed many of the causes of off-shelf events here let’s think about whether we can predict an off-shelf event before it happens.  Clients often ask for this and some marketing materials claim to offer it, but really?  How can you predict a specific theft, the loss of a shelf-tag, a store associate (or customer) blocking a replenishment spot, the misplacement and subsequent loss of product in the back room?

When you think of the causes of off-shelf events, in most cases, the event itself is not predictable.   (Except in the obvious situation that you are off-shelf because you have absolutely no inventory either in reality or due to an unresolved phantom inventory problem)

Predicting a propensity (likelihood) to being off-shelf is doable, and there coudl be real value in doing so. more on this later. Predicting the actual event, not so much.

The bottom line

If most of the problem here is in store operations, you may be thinking that there is nothing a CPG supplier can do to fix it..  I don't think that's the case and certainly it's not the message I am trying to get across.   My point is that you can't fix it by being ever better at upstream supply chain - you need to start thinking about how you can improve store execution.

OSA = On Shelf Availability

I recently had the experience of shopping in a grocery store where nothing, nothing at all, was missing from the shelf.   Industry studies generally report a 6%-10% chance of any particular item being off-shelf so for a store with roughly 50,000 products this was an astonishing achievement.   Sadly, this was probably the last and only day (the grand opening of the store) when this will be true and indeed returning there some weeks later, the usual rate of gaps on shelf was clearly evident.

Not having product on the shelf when a shopper wants to buy is the ultimate failure of the supplier-retailer supply-chain but few companies seem to recognize the issue or be actively working to resolve it.  The problem has been around at least since we started to measure it in the ‘60’s and seemingly there has been no progress in finding a solution.

On Shelf Availability is costing retailers and suppliers real money: Walmart recently estimated they lost $3 billion annually from not having product on the shelf. 

In this upcoming series of posts I’ll look at the myths and misconceptions around on-shelf availability, the underlying causes and solutions that provide real, incremental value.  I’ll deliver them in bite-sized chunks over the next few months so check back often.

Supply Chain Luck (good and bad)

roulette-wheel.jpg

It's 7:50 am and it's not going to be a good day. While reviewing your scorecard , you notice a big hit in a key service metric. Your boss has clearly noticed it too, a meeting request just hit your calendar for 08:05. Now, you did have some problems earlier in the year but they seemed to clear up and for the last few months you've been enjoying the rosy glow that comes from near perfect service levels on your products. This month, sales spiked, inventory crashed to zero and you started cutting orders. People are not happy. What changed? What went wrong?

It may be that you missed a predictable shift in demand ("Promo? What promo?") or perhaps you do not have safety-stock levels set correctly (your bad) or, and please consider this carefully,you might just have been unlucky.  Now, I'm not suggesting that every nasty surprise you encounter is bad luck ; neither is every bit of good news due to your utter mastery of your supply chain, but sometimes... stuff happens.

Call it what you will - luck, chance, risk, volatility, variability, uncertainty, randomness or noise - stuff happens. However good your sales forecasting may be, it will be wrong. Even with robust planning, you will, occasionally, have issues with supply, weather events, product quality, equipment failure. You can and should minimize it, mitigate it, expedite around it, yes, even measure it, but you cannot, ever, ever, eliminate it.

Bottom line - if, for any metric, you can't separate "unusual, but still within the expectations of the system" from "now that's really weird" you can spend a lot of time and money trying (and failing) to control it.

I came across a great example of this yesterday, looking at the results of a simulation study for a supply chain inventory/replenishment system. The chart below shows days 200 to 1000 of a simulation run.

The 4 component charts (top to bottom) show:

  • daily demand for a product with a reasonable level of variation. This is sampling from the same exact statistical distribution throughout time - those occasional spikes are purely random and within the bounds of "reasonable" for this distribution.
  • The results in inventory. The solid blue line shows on-hand inventory, the dashed line is on-hand and on-order together. You can see that as total inventory drops below the re-order point an order is triggered, on-order inventory shoots up and some days later it is transferred into on-hand inventory. You can also see that sometimes inventory drops below the safety stock line, sometimes it is above and occasionally inventory drops to 0 and orders are cut.
  • shorts are order-cuts due to insufficient inventory. Shorts can only happen at the end of a replenishment cycle, and in fact for most of this 800 day period there are no shorts at all. Some of them are fairly small (less than 5) others not so much.
  • fill-rate calculated on a moving 180 day window. E.g. the fill-rate metric shown on day 400 is based off performance for the prior 180 days (221 to 400). For this model, the re-order-point (ROP) was set to meet a 97.5% fill-rate (in the long-term) but in this chart, it's all over the place.

Now, you may think 180 days is long-term but with this system ordering approximately every 20 days it's only had about 9 chances to test that ROP level in any given 180 day window. That's clearly not enough to get a stable metric. Note that if you average the result across the whole 1000 days, roughly 50 order-cycles, you get 98.1%. That's still over 0.5% points off from what I intended and actually, luck is on our side, it could have been much further from the "truth".

When I repeat this simulation 100 times (with different random numbers), I get a wide variety of results for the 1000 day fill-rate.

It's centered around the right place, so the good news is that my math is working, but for any 1000 day run, random variation could lead me into thinking I have a problem when the underlying system is in control and no corrective action is needed. (Anyone want to explain an average 96% fill-rate for 3 years to the boss?)

Dealing with uncertainty is the realm of statistics and I know that many of you do not want to go there. I do understand your pain (even if I don't feel it so much): trust me that in writing this post my largest issue has been the amount of text I have written, re-written and deleted because it contains too many statistical references.

Let's keep it simple then: generally, larger samples give you a better/tighter estimate of a metric than do small ones. For example, your company-wide fill-rate metric for the month is probably quite stable/reliable. The further down you drill into metrics for specific products, locations or dates the smaller your sample and the less stable the metric becomes. Looking at an individual product for last week (or perhaps even the last 6 months) is not giving you reliable information as to whether your supply chain is under control so try not to treat every little bit of bad news like a fire drill.

Point of Sale Analytics - we already know the math !

For the last few months, my posts on Point of Sales Analytics have focused on the Next Generation of Demand Signal Repository.   New technologies have combined to allow for simpler, cheaper systems that can handle vastly more data effectively.

A key feature of the Next Gen DSR is to provide a fast, stable, productive environment for prototyping and release of analytic tools that drive real insights into the data.    The analytic solutions below are based on math that is already known and data that is readily available to blend into your DSR if it's not already there.   Our lack of progress in this area has little to do with the magic of math or modeling and much more to do with the inability of older platforms to bring data and analytic tools together effectively.

So what analytic applications could/should we build out ?

There is a lot of interest in so called "sales driver" models.  Understanding what drives your sales helps you understand the fluctuations in your past, better predict the future and target sales activities (pricing, promotions, regions) more effectively.  

Historically, we have seen most interest around pricing (price-elasticities and cross-price elasticities) and promotional evaluation, but there are many other possibilities for sales drivers.  What drives your sales?

  • On-shelf availability
  • Weather: rain-fall, temperatures, wind speed, hurricane warnings
  • Seasonal events,
  • Day of the week, Day of the month. 
  • Government assistance  programs like SNAP or WIC
  • Pay cycles
  • Placement in store, placement on shelf, product adjacency.
  • Store-area demographics

Use Cluster Analysis to find groups of similar stores or products.  For many actvities (assortment selection, price modeling) we want to operate at a low level gain more accuracy but cannot monitor or execute against thousands of unique plans so we try to a manageable number of "similar"  groups.   Cluster analysis does that for your very effectively.

Use market basket analytics to find products often bought together and products associated with specifc shopping "missions".

Automatically detect retailer price changes and pricing zones whie ignoring the noise in the signal driven by one-off price matching and discounting activities.

Find the Missing Stars in your assortment.  Find the geo/demographic characteristics that flag unusually high rates of sale for your product then make sure they are listed in all the stores that share these characteristics.

Build Optimal Assortments by understanding what sells well where , quantifying and accounting for product substitution (cannibalization) rates.

Optimize shelf space to generate incremental revenue, margin or velocity.

Design planograms to minimize store labor, increase on shelf-availability and drive sales.

Generate consistent rules-driven planograms from your assortment selection in less time than it hours rather than weeks.

Continuously monitor and validate retailer replenishment settings (safety stocks, re-order points, forecasts) to ensure your product flows to the shelf effectively.

Optimize order quanities to provide the best balance of labor, warehousing and inventory costs.

Build cost-effective, clean orders to support promotions, events or recovery activity.

Generate accurate allocations of promotional inventories across stores based on anticipated demand and current stocks.

Accuractely detect and address off-shelf events, characterized by an unusually long period of low (or no sales) , to ensure your product is available for sale when a shopper wants it.

Dramatically enhance the accuracy of short-term shipment forecasts using point of sale and inventory data.

Forecast the needs for customer shipments even before a customer has ordered to get dibs on freight capacity in a tight market.

I have no pretensions to this being a complete list: it doesn't address all the good questions that have already been asked, and I'm sure many of the best questions haven't been asked yet, but it's a fair start and all of these can be built using math that is known (if a little obscure) today.

What point of sale questions do you need answered?   What anaytic solutions would you like to see ?

Next Gen. Point of Sale Analytics

Over the last few months I've been exploring the features I want to see in a next generation platform for point of sale analytics: It's simpler, faster and cheaper, supports rapid blending of new data sources and is powered up with real analytic capability.

Looking back there are a lot of posts on this topic so here is a quick summary with links back to the detail. 

Note

  • I have no immediate plans to build such a system for sale but I do use systems with many of these features for ad-hoc analytics as they are flexible yet relatively easy to set up and tear-down without incurring substantial overheads. Consider this series more of a manifesto/buyers-guide.
  • I do see changes in the marketplace suggesting that a number of DSR vendors are at least considering a move in this direction. As to which one will get there first, I think it will be whoever feels least weighed down by their existing architecture.

Database technology has moved on dramatically over the last few years. For this scale of data, analytic solutions should be columnar, parallel and (possibly) in memory. This enables speed, scalability and a simple data structure that makes it easy to hook up whatever analytic or BI tools you wish.

If the only data you have in the system is pos sales for a single retailer, you can build a reporting system ("what sold well last week") but you will struggle to understand why  sales change. Bringing in other data sources: multi-retailer, demographics, weather information, promotional calendars, competitor activity, socio-economic trends, Google trends, social media, etc. allow for much more insighful analtyics. It's not easy to do this though, particularly if your source database is locked down so that it takes a software engineer to add tables

The term "Analytics" in general use covers a lot of activities most of which involve little more than reporting. In some instances you can slice and dice your way through a dataset to find insight, reporting is not without value but it's not analytics. Not even close.

Can you buy good analytics? Yes, but there are also a number of pseudo-analytic solutions in the market that have little to no analytic power - caveat emptor!

To get to real, deep insights you need real analytic tools. Depending on the taxonomy you are used to, we are talking about predictive and prescriptive analytics, machine learning,  statistics,  optimization or data science. Most of these tools are not new but they are not generally found in standard BI offerings and even when they are (e.g. reporting level R integration) you may struggle to apply the analytic tools at scale.

Finally, whether you build your own analytic tools or buy them in to run on your platform, clever math is not enough. If a user cannot comprehend the tool or it's suggestions due to poor user interface design and /or bad visualization choices it's worth precisely ... squat.

Analytics are for everyone !

Analytics are for everyone! Well, not building analytics, no. That needs a high level of expertise in statistics, machine-learning, optimization, programming, database skills, a healthy does of domain knowledge for the problem being addressed and a pretty wide masochistic streak too. Using analytics, now that is for everyone, or at least it should be. 

We all use analytics, and, I think, the best examples, we use without thinking about just how complex it is.

Is there anyone out there that hasn't used an electronic mapping service (GPS) for directions? Even ignoring the electronics, these are remarkable pieces of engineering! An extensive, detailed database of road systems and advanced routing analytics to help you find the best route from A to B without sending you backwards down one-way roads or across half-finished bridges.

Perhaps you're thinking it's not that hard? Could you build it? What if I got the data for you? No? But you can use it right? They are not perfect, mostly I think because of data cleanliness problems, but they are close enough that I don't travel far from home without one.

More examples. Anyone used a search engine? How about an on-line weather forecast? How about web-sites that predict house-values? Recommendation engines like those used by Amazon and Netflix? All heavy analytic cores wrapped in an easy to consume, highly usable front-end.

These are, I think, among the exceptions in analytic applications - good analytics AND good delivery.

I talked about pseudo-analytics in a recent post: shams with no basis in science wrapped in a User Interface with the hope that nobody asks too many questions about what's under the hood. This is not good analytics.

Unfortunately even good analytic tools get under-used if they have not been made accessible to the poor people that have to use them. Spreadsheet tools probably top the list for unusable analytic applications: unusable that is by anyone except the person that wrote them. Sadly though, I have seen many examples both in reporting and applications where so little effort was put in to User Experience that any good analytics is almost completely obscured.

Building new analytic capability is a highly skilled job. Delivering analytic results in an easy to consume format so that it gets used is also a highly skilled and, frankly, often forgotten step in the process. After all we do build analytic tools so that they get used. Don't we? Sometime I wonder.

Next Gen. DSRs - Bring the Analytics to the data

Under old world analytics, you move data from the DSR to your analytic server, build models, then write results (sometimes models too) back out for integration into the DSR.

Now, consider this:

  • DSR datasets are often enormous. (2 years of data for a DSR I worked with recently input to a model was approx. 270 GB)
  • Analytic tools are small. (The R base software, all 150 packages I have installed and the development environment is 625 MB)
  • Analytic models are tiny. (Expressing a 10 component regression model in SQL, just 288 bytes and most of that is down to variable names)

Let's try that visually.

The input data is huge, everything needed to run R (my analytics tool of choice) is barely a blip on the scale and the resulting model can't be seen on this scale at all. And today we move the DSR data to the analytic server to run the analytics.... anyone else having an issue with this ?

Where the data is small enough that we can pull what we need via query over an ODBC connection and hold it in memory to run the analytics, perhaps you can live with the network overhead.

Similarly, if the DSR and analytic servers are co-located with a big fat data pipe connecting them, it doesn't matter so much. It's not same machine I'm after necessarily, but same rack would be nice.

What happens though, when the data is too big and the connection too slow (think wide area network) to be feasible? Now we need to build database structures on the analytic server, load the data (taking a copy), and if we are to re-run the analytics routinely, keep it in sync with the source on an ongoing basis. This is a lot of (non-analytic) maintenance work before we can even get started on the analytics.

So why do we do this?

"The analytic server is a high power, high memory machine great for analytics!"

 That's true but chances are your database servers have the same thing.

There are also valid concerns around how an analytic tool connecting directly to a database may impact other users. I do have a little sympathy for this, certainly much more than I used to, but think on this: a DSR is not a mission critical system. The failure of a mission-critical systems stops your business. If the DSR stops (and the chances are very good that you will have no issue at all), your reports are a bit late. Relax !

I have a suspicion that some of this is related to licensing. If you pay a small fortune for your analytic tools and they are priced per server, per CPU or per core, I can see why you would not want to go installing that software everywhere you might want to use it. Cheaper perhaps to bring the data to the software. Working with free open-source tools, it's not been an issue for me to install co-located or even on the same machine as needed.

Recently a number of database and BI vendors have moved to integrate analytic tools (often R, sometimes SAS) into their offerings trying to deliver real in-database analytics. I do think this is a great direction to move in though I have some concerns about the level of integration currently available. see my post on 

Analytic Power !

for more details.

Even if you can't execute true in-database analytics (which should be a Next Generation feature) there are still things you should be able to do to

 bring the analytics to the data.

First let's make a distinction between model-building

 (the act of creating new models from data) and 

model-scoring 

(running existing models against new data to make new predictions). All predictive analytic models I can think of can have this same split. (Descriptive and Prescriptive analytics do not)

Model-building

 is an intensive task, this is where all the heavy lifting happens in analytic work so processing and memory needs can be substantial though this varies widely depending on the analytic method and to some extent the implementation. If you have installed analytic tools directly on your database servers this may be enough to cause something of a slow-down. OK - try to co-locate instead. If you absolutely must replicate data to an analytic server on the other side of the world and try to keep your data in sync, I pity you.

Model-scoring

 is fast. A model is just a set of simple calculations. Deciding exactly what simple calculations you needed was the job of model-building but now you have done that, scoring new data against that model is quick.

This is what the result of a simple regression model looks like (in SQL):

[Variable_1] *-49.8916 + [Variable_2] *-24.2773 + [Variable_3] *-48.1305 + [Variable_4] -253.7238 + [Variable_5] *-20.7173 + [Variable_6] *17.722 + [Variable_7] *12.9865 + [Variable_8] *-17.4036 + [Variable_9] *2.2738 + [Variable_10] *-7.9186 + 6.668 AS Prediction

If you think it looks complex, look again, it's just a set of input variables multiplied by specific weights (as found by model-building) and then added together. This is easy work for the database. More complicated models will have more complex expressions, you may see logs, exponents, trig., perhaps an if..then..else statement. Nothing the database will find difficult to execute if it's expressed in the right language.

Unless models change with every input of new data (and so need re-building) there is no excuse not to score the model directly against the data.

 How you execute the model scoring is a different question and you have some options:

  • you may load the model, new data and score directly in your analytic tools. This is using a sledgehammer to crack a nut, but it's easy to do if a little heavyweight/slow.
  • for simpler models converting the model into SQL is not that difficult (though you do need to know SQL pretty well and have permission to build it into the database as a view, stored procedure or user defined function. This is probably the most difficult but fastest to execute.
  • try converting the model to PMML (predictive model markup language) and use a server based tool designed to execute PMML against your database. (Many analytic tools have an option to export models as PMML.) A PMML enabled DSR would be a great enhancement for the Next Generation.

Bring your analytics to the data ,

 spend more time doing analytics and less data time wrangling.

Next Gen. DSRs - Analytic Freedom !

Current Demand Signal Repositories don't play well with others. Their data is locked away behind layers of security and you can only access it through the shackles of their chosen front-end for reporting. There is no good way to get that rich dataset into other tools: you have to copy it into a new database and new data structures. (In some cases you may have to do this twice, once to rearrange the data from the DSR into a format you can understand, then again to match the data structure needs of the downstream tool.)

For small-scale models (do we do those anymore?) that sip data from the original repository you can do this through the reporting engine and live with the pain, for large scale modeling it's really not an option.

I want freedom. Freedom to analyze with whatever tools I need

: The freedom to report in Business Objects, visualize in Tableau, analyze in R and run existing applications (order-forecasting, master-data-checking, clustering, assortment optimization, etc.) directly against this data. 

(I'm not endorsing any of these tools and you can replace the named software above with anything you deem relevant - that's kind of the point).

Much of this freedom comes from a simplified data model, enabled by new database technologies (massively parallel processing, scale-out, in-memory and columnar). See more details at 

data handling

.

It also needs a security model that is handled by the database NOT the reporting layer or as soon as you get to the underlying data you can see lot's of interesting things you shouldn't :-)

I suppose I could live with a little less freedom if a DSR offered all the tools I need but I don't think that's realistic. Not all DSR reporting layers are equal, data visualization is hit and miss, and as I posted in 

An Analytic name is not enough

 while there are some good DSR based analytic applications you will find many use pseudo-analytics and some have no analytic basis at all.

Do you think, perhaps, that the Next Generation DSR will provide the best reporting, visualization and analytic tools available? Sorry, I don't think so. DSRs cover a dizzying array of analytic need and developing robust, flexible analytic applications, even assuming easy access to the data, is an expensive proposition for any DSR vendor to do alone. I anticipate a few strong analytic "flag-ship" tools will emerge alongside more me-too/check-the-box applications packed with pseudo-analytics.

So, what can the Next Generation DSR do to help?

  • make it (much) easier to get at the data in large quantities,
  • make it (much) easier to bring analytics to bear on that data. (Perhaps with an integrated analytic toolset)
  • open up the system to whatever analytic tools work best for you
  • make it easy for other software vendors to provide add-in analytics on the DSR data/analytics platform.

Think about that last point for a moment, no DSR vendor is big enough to provide state of the art analytic applications in all areas, but make it easy enough to integrate with and it could enable specialist analytics vendors to offer their tools as add-ins to the platform. (This could be good news for the analytics vendor too, it removes the need for them to install and maintain their own DSR just to enable the analytics)

Let's look at an example.

Today if you want assortment-optimization capability, you can

  • wait for your DSR vendor to develop it and hope they use real analytics; or
  • search for another solution and work to interface the (very large) quantities of data you need between the applications;
  • write your own (always fun, but you had better know what you are doing) and you will still need to interface the data.
  • decide not to bother

All but the last one of these are slow - I'm guessing 12 months plus.

In the NextGen world, if you want to new analytic capability, you could still write your own, it's easy to hook up the analytic engine, or, just go to the DSR's analytic market-place and shop for it.

Next Gen DSRs - An Analytic name is not enough

You need not always build your analytic tools, sometimes you should buy in. If the chosen application does what you need that often makes good economic sense... as long as you know what you are buying.

Let's be clear, an Analytic name does NOT mean there are any real Analytics under the hood.

For many managers, Analytics is akin to magic. They do not know how an analytics application works in a meaningful way and have no real interest in knowing. At the same time, there is no business standard for what makes up "forecasting", "inventory optimization", "cluster analysis", "pricing analysis", "shopper analytics", "like products" or even (my favorite) "optimization".  

Don't buy a lemon!

In the worst examples, there is nothing under the hood at all.

 One promotion-analytic tool I came across recently proudly proclaimed that you (the user) could calculate the baseline and lift for each promotion however you saw fit and then just enter the result into their system. They presented this as a positive feature, but calculating a meaningful baseline and lift is the difficult part!!

I've seen similar approaches for:

  • off-shelf alerting tools that ask you how long of a period of zero sales is abnormal (so they can report exceptions)
  • supply chain systems that need you to enter safety-stocks or re-order-points (so they can figure out when to order).  
  • assortment optimization tools that want you to input product substitution rates.

Hmmm, is a car without an engine still a car?

Many applications use pseudo-analytics.

 After all, how hard can it be? "cluster analysis" , that's finding groups of things right? I reckon I can figure that out, no stats required. Yeah, right, of course you can... FYI - meaningful, useful clusters may be a little more difficult. It's not that cluster analysis is particularly hard, but neither is it something you can knock together without the right tools or any statistical understanding.

Sadly, I have seen real world examples of pseudo-analytics too in pricing analytics, off-shelf alerting, demographic analyses, inventory optimization and forecasting.

The right tool for the right job. 

There are many good analytic applications available, but you can still make it useless if it does not suit the task you have in mind. Using a time-bucket oriented optimization program to schedule production runs with sequencing comes to mind. OK, relatively few people are going to understand that one and it's not a DSR application, but it is real, the software vendor did not come out shouting that there would be a problem and 2 years down the line that project was abandoned.

Are DSRs worse than other applications?

I think this kind of feature-optimism, is a general issue in buying any analytic app but my perception is that it is a bigger problem in the DSR space.  Perhaps because the DSR is trying to offer so much analytic functionality to so many functional areas?  Is a DSR really going to handle forecasting, pricing-analytics, cluster-analysis, weather-sensitivity-modeling, promotional analytics, inventory optimization, assortment selection and demographic analysis (note - not a complete list), all as packaged software, for $50K a year?   Not unless they can scale that investment across a huge user-base.  Some will be good, others not so much - be warned.   

Spotting a lemon

An expert in the field (with analytic and domain knowledge) can spot a lemon from quite a distance. If you do not possess one you would be wise to invest in some consulting to bolster your purchasing team. For those applications that pass the sniff-test, the proof of any analytic system is in it's performance. 

Define rational performance criteria, test, validate, pilot and never, ever, ever rely on a software vendor ticking the box in your RFP.

Next Gen. DSRs - Analytic power !

To handle real Analytics (see my post Reporting is NOT Analytics) you need real Analytic power. BI tools are based on the language they use to interrogate the database (typically SQL) and with no library of analytic tools - it's not nearly enough.

We use SQL (Structured Query Language) to query relational databases like SQLServer, Oracle, MySQL and Access. SQL is a great tool for handling large quantities of data, joining tables, filtering results and aggregating data. However, SQL's math library is only sufficient for accounting (sum, product, division, count) and while I do know it can do a few more things, it's not enough to be useful for Analytics. Even getting it to calculate a simple correlation-coefficient is a big challenge. Want to build a simple regression model? That's just not going to happen in base SQL, we need something designed for the task.

R, SAS, SPSS, Statistica, and a good number of others, are the real deal and the difference between any of them and what you can do in SQL (or Excel) is vast! With these tools it's no longer a question of "can you build a regression model?" now it's "which particular flavor of regression do you need?". What! There's more than one? Oh yeah!

I'm not getting into which analytic tool is the best. I use R, and that's what I'll talk to, but I have good friends, analytic-powerhouses who insist on using SAS or SPSS. These tools have different strengths and weaknesses and within the analytic community a lot of time, blog posts and misinformation go into arguing the relative merits of one vs. another. My take is that for most business-analytic purposes any of them will get the job done. The one you choose should be driven most heavily by your ability to get the analytic tool working against your data.

The problem is that these analytic tools do not generally reside in the same space as your database or BI tool, so you spend a lot of time interfacing data between systems. It's slow, sometimes very slow, and requires replication in your resources.

In recent years many database and BI tools have started offering integration with statistical tools (Oracle, SAP Hana, Tableau, Spotfire, MicroStrategy). The ideal here is in-database analytics where we run the complex stats in-tandem, indeed in the same memory space as the database. That is very attractive but I would look very carefully at the depth of integration offered before getting too excited. In some cases, I think, vendors have done just enough to tick the box without making it truly useful. As examples:

  • One vendor limits the transfer of data between database and R to simple table structures. Now, imagine running a regression model. What goes into the regression is very likely a simple table - check! What comes out is anything but: it's a complex object combining multiple tables of different dimensionality and named values (like r-sq). We need this data to determine the validity of the model and make future predictions. Force me to return just one table structure and I must throw most of the information and capability away. Before anyone asks, no, this is not unique to regression models.
  • Another vendor has integrated R into the reporting layer. This is relatively functional as long as the data you want to work with can be generated in a report. If you need very large amounts of input data you may well exceed reporting limits. If you want to build a separate model for each product in your database, you may have to run the report separately for each one.
  • Standard R was not originally designed for parallel execution (though you can get around this with a little coding help). Current processors (CPUs) even on low-level laptops are multi-cored. Servers routinely run more cores per CPU, more CPUs per server and we want to scale-out across multiple servers. A BI offering that only offers single core R execution is wasting your resources and time.

Bottom line, to do real Analytics, you need real Analytic tools. But even the best tools must be able to get at the data to be useful. Choose carefully.

Next Gen. DSRs - Reporting is NOT analytics

problem-analysis-solution.jpg

I've written a number of posts now on the next generation of Demand Signal Repositories. DSRs are the specialized database and reporting tools primarily used by CPGs for retail Point of Sale data.

So far, I've looked at the challenges (and big opportunities) around handling the large quantities of data involved: better database technologies, scale-out platforms, true multi-retailer environments, effective data blending and dramatic simplification of data structures.

Taken as a whole this get's the necessary data into one place where it is relatively simple to overlay it with the BI or analytic tools of your choice and still get good performance. This is the starting point.

Now, we can get to the fun stuff, Analytics. Let's start by addressing a widespread misunderstanding.  Reporting is NOT analytics.  I've blogged on this before , actually one of my very first blog posts, but it bears repeating and extending from the original.  Reporting is about "what happened"; Analytics is concerned with "why?""what if?" and "what's best?".

You need reports.  Hopefully they are well constructed, with appropriate metrics, good visualization and exception highlighting. Perhaps they are also interactive so you can drill-down, pivot and filter. These are useful tools for exploratory "what happened" work, but, almost exclusively, reports leave it up to the reader to construct the "why".

Great reporting can pull together facts that you think are related for visual inspection (e.g. weekly temperature and ice-cream sales by region). Perhaps you can see a pattern, sort of, but reports will not quantify or test the validity of the pattern that's up to you, the reader, to guess at.

Even great reports can't help you much with more complex relationships. In reality, ice-cream sales are also dependent on rainfall, pricing, promotions, competitor activity etc. Who knew? Well we all did of course, but there is no reasonable way to visualize this in a standard report. Want to predict sales next week given weather, price and promo data for all products in all regions? Your going to need some good analytics.

You need Analytics too.  In some cases, basic, high-school, math is all you need. In most, it doesn't even get you close to the 80% solution beloved of business managers.   "Winging it" in Excel, Access, PowerPivot etc. can give you very bad answers that are seriously dangerous to your success and/or employment.  

Want to understand and predict the impact to sales of promotions, pricing or weather events? You need Analytics for that.

Wan't to know where you can safely reduce inventory in your supply chain while increasing service level? You need Analytics.

Wan't to alert when sales of your product are abnormally low? Analytics!

Want to know how rationalizing products across retailers would impact your supply chain? Yep, Analytics.

Want to know which shopper demographics are most predictive of sales velocity? I think you get it...

If your business question is something other than "what happened" you need Analytics.

Next Gen. DSRs - data blending (part 2)

My most recent post on Demand Signal Repositories bemoaned their general lack of ability to rapidly ingest new and interesting data sources  (e.g.: promotions, Twitter feeds, Sentiment analysis, Google trends, Shipment history, master data, geographic features, proximity to competitor stores, demographic profiles, economic time series, exchange rates etc.).

As a result, analysts spend far too much time collecting/copying data into ad-hoc data marts to enable useful modeling work. At the extreme, you can move Terabytes of low-level data from a perfectly good database into another one (probably on a lower powered machine) so as to manually merge it with a few hundred records of new data you need for analysis. This is slow (actually very slow), error prone and leaves very little time to do valued added work.

Based on questions from blog readers via email, I think that I failed to call out how big the gap is between where we are now and where we should be. Let me spell it out.  If I go to my (or your) IS department now and ask  "how long would it take to integrate XXX data into the DSR so it is loaded, cleaned, gap-filled, matched to appropriate dimensions and ready for some interesting analytic work."   I would expect to hear back  "between 6 and 12 months"  and that's assuming they have both some developer availability and the necessary access to add/modify data stuctures - some DSRs are locked down tight. If I went to the DSR vendor, it may be a little faster, depending on just how tightly the data structure is tied into their product release schedule. But here's the thing - I want to do this, myself, in real-time and certainly in less than a day.

Tools such as Alteryx  re designed to do data blending. Alteryx in particular, seems to do especially well handling geo/demographical data, some of which comes with it as standard. They also have a number of pre-defined macros to help you get at standard data source like Google Trends and Twitter. If I understand it correctly, it does this by loading all data sources into memory. Perhaps it constructs it's own data repository on the fly, but, certainly, it does not touch the source database's data structure at all.

This would work well for relatively small quantities of data. Let's say you are examining annual sales for a product group by store - you aggregate that to a few thousand records of POS data in the DSR, load it into Alteryx, geocode the locations, match up the geo/demographic data you are interested in and you are ready to run some math. I doubt that would take more than a couple of hours. There is also some analytic power in the platform and at least some level of R integration if you wish to extend it further. For ad-hoc small (sub 10 million record?) data analytics this looks really good.

What if you want to do your modeling at a much lower level of detail though? Do you have the capacity to match across billions of records outside the DSR? Perhaps, but it's going to cost you and why move it all into another database on another expensive server when you've already paid for those in your DSR? What if you to run analytics repeatedly, do you really want to do geocoding and ad-hoc matching every time you want to use census data in an analysis? Chances are the stores haven't moved :-) and the most frequently updated census data, I think, isn't updated any more often than annually.

Better to do it once, load it into new data structures in the DSR and enable it for ongoing reporting/analytics or... did you want to force even basic reporting through the data blending platform because that's the only place you can match up multiple data sources ? I didn't think so.

If would definitely look at something like Alteryx for ad-hoc work. If you can also use it to source, transform, handle dimensional matching, deal with missing data etc. and load the results back into your DSR (where you just defined new data structures to receive it), I think you might have something.