A Problem With Trending Metrics vs Reality

[thetxlibra]

I have a somewhat complicated problem that I was hoping some of you may have some ideas for, both in approach and execution in Excel.

SITUATION: We have many accounts (70+) each of varying sizes and ticket volumes. Some accounts may have thousands of tickets per week, others may have 1 or less per week. All are judged by the same metric target: They must be trending downward or have a volume of zero for the most recent week.

REALITY: Let's leave aside the fact that the chances of the larger accounts' ticket volumes reaching zero are about the same as limit x -> 0, because I've given up on winning that argument. The smaller account managers find it unacceptable that they are flagged as failing a metric when they only have 0 or 1 ticket per week, and are trending at +0.1 tickets per week (which honestly could change within a few weeks regardless, because again, it's often 0). But the fact is, according to the metric target, if the current week's tickets are not 0, and they are trending upward in volume, they shall not pass!.

ATTEMPTS AT REMEDIATION: We have done the following to attempt to alleviate these kinds of issues:

• Calculate the FDIST via LINEST for both 8 and 16 weeks separately, determining a trend only if the FDIST is lower than 0.05 and if both are, we use the lowest one to determine slope via LINEST.
• We then check the slope against a "shallowness" threshold. If the slope is less than a 0.005 difference (half a percent), it is determined to be flat (because, seriously, a trend of 0.007 tickets per week is useless to us, no matter how low the FDIST is.
• We have even snipped out blank weeks and weeks with inapplicable data types (such as alpha characters). That's just best practices, but figured I'd go ahead and answer that question before it got asked.

PROBLEM: Despite these attempts, we still have several accounts getting ding'd each week for failing a metric when in reality, they should be quite happy about the numbers. The logical choice would be for the target to give more of a forgiveness threshold, such as "If it's under X tickets, don't call it a failure even if its trending upward." But we can't for a few reasons:

• The rule must be consistent and attainable by all accounts, and if we set the number at, say, 5 tickets, there's still no way that we can get the larger accounts down to that point.
• I don't have authorization to change the target anyway, and those that do, won't.
• Large accounts have a natural limit they are going to reach before they cannot reasonably be expected to get any better. For instance, an account with 8000-10000 tickets per week is unlikely to ever get below 2000 tickets per week, short of a holiday week where the entire company is shut down, and that's an outlier. So if that large account has a natural limit, it needs to be determined somehow with only 16 weeks worth of data at a time.

DESIRED SOLUTION: I am looking for a non-linear way to calculate a trend that will work for any size account, and determine its natural limit, and as it approaches that natural limit, beyond a certain threshold, determines the threshold to be flat even though a linear trend may determine it to be moving up or down. Sort of like a built-in law of diminishing returns or an inverse logarithmic function.

For those who haven't already moved on with TL;DR, I would greatly appreciate your help in this.

 

[RickXL]

Hi,

I am no expert but it sounds as if you want to be comparing shapes rather than absolute values.

Take a look at this to get some ideas: Normalization (statistics) - Wikipedia, the free encyclopedia

For instance, the "Standard Score" formula modifies all the sample values by subtracting the mean then dividing by the standard deviation. This will change all your accounts' figures to have a mean of zero and a standard deviation of one.

After that you could find slopes etc of the resulting samples and then compare like with like. Exactly which normalization formula you need will depend on your data.