How to detect a % difference in how often an event happened in x period vs the previous period?

[nicholmikey]

Hi, 

I'm trying to figure out how to detect if one of our ecommerce integrations has an error and the transactions drop relative to their normal rate. 

For example if we see 200 purchases a week and suddenly it goes down to 10. Each purchase has category and storefront ID fields in the events, so I would want to automatically adapt to new storefronts and categories that appear in the events. 

The output I would hope to get is an alert when a combo of storefront x with category y has dropped to 70% or less of its normal event rate. Say in a period of a day or a week. 

How can you have a search work in that way, where it compares its results in this period to the previous period? 

Thanks

[ITWhisperer · Accepted solution]

Firstly, I assume "normal" would be the median average purchases for that category for that store.

Secondly, I assume you only want to pick up when the weeks purchases are lower than the previous week (for example, if the first week is lower than the median, this would not be of concern).

Try something like this:

| bin _time span=7d 
| stats sum(purchases) as purchases by _time storefront category
| eventstats median(purchases) as median_purchases by storefront category
| streamstats last(purchases) as previous global=f window=1 current=f by storefront category
| eval drop=if(purchases<previous AND purchases/median_purchases < 0.7,1,null())

[nicholmikey]

This is really handy thank you!

I modified it to count by storefront ID and category as I am looking for the count of purchases not the dollar value of the purchase, and that seems to work. The problem is it does not seem to work if one day the purchases drop to 0. If there is at least 1 it shows up in the data and I can see how it compares to previous time periods, but is there a way to force a 0 if the event did not appear one day but did appear previous days? 

[nicholmikey]

I was able to figure this out by finding another trick on the forums. 

What I did was combine my storefront id and category to one field orderEvent, then I used this to show a 0 if no order was placed in that time period. 

| timechart count by orderEvent
| untable _time, orderEvent, count

untable seems like a handy command!