Repost: This was originally posted over at PowerPivotPro. I have since heard from numerous people that really enjoyed it… so I am re-posting here
By nature, I am a rather skeptical person. When I first hear an idea, I generally think it is probably wrong. This isn’t one of my finer traits and I am sure it drives Rob insane, since he has a rather impressive track record of being correct. Thankfully, this blog entry is not about one of the times I assumed Rob was wrong…
It is actually the complete opposite. It is one of those times when Rob told me something and it just instantly SUPER resonated with me and got stuck in me. I suspect it will do the same for most of you.
Nouns and Verbs
If you were fortunate enough to hear Rob speak recently at PASS Business Analytics Conference, you have already heard this idea.
It is super typical for us, as report authors, to generate The Report. The Noun. We hand it off with pride. And The Report has all the information somebody could possibly need to make a decision. All of it. Row after Row. Column after Column. Unfortunately, even with the fanciest conditional formatting in the world, it is not clear… how is somebody supposed to look at The Report and actually… do something?
Because, at the end of the day (and yes, with a handful of exceptions like keeping the SEC happy), it is our hope that somebody looks at our reports and uses it to make a decision. To do something. To Verb.
And that is the critical question you need to ask yourself as the report author. “How do I make this report actionable?”. Now, realize this is just an ideal. It is probably never perfect, or at least, certainly not for every report. But any step towards making it easier on the consumer of your data to do something, take make a decision, to take action… the more valuable the report.
Real Life Example
Over the last few months, I have been consulting for the fine folks of Black Label Strategies. They are in the middle of a project to provide business intelligence to one of the top companies in auto recycling.
At its core, the decisions that need to be made in this space come down to:
- What cars should I buy (from the “crashed car auction”) ?
- How long do I leave them on the lot before I have them crushed for scrap metal (thus, freeing up valuable space on the lot for cars with more valuable parts to sell) ?
We got to work generating The Report.
It had every car on the lot, how much the car cost, how much money we had already made off part sales, what percent of our profit goals have we met, how long the car has been on the lot, and generally cell after cell of information somebody might possibly need to make a decision about whether a car should be crushed for scrap or not. The Report. Delivered with pride in all its Noun glory.
How do we make this more actionable?
Well, the decision I am trying to make it “which cars should I crush for scrap?”. So all we really need to do is sort the list of cars. But… by what column? The decision to crush a car is made based on quite a few factors, so sorting by any of the existing columns isn’t likely to help us.
So, we make a new column, based on a new measure…
The Crush Factor
For the sake of our discussion let’s pretend we only care about 3 factors to make the choice:
- Days in stock
- % of projected profit
- # of parts still on the car
In reality there were quite a few more factors, and we did some work to provide visibility to parts that are more important/profitable (transmissions and engines are HOT!). But this should get the point across
How do we combine these factors to come up with one number that we can sort?
There are probably a bazillion ways with various trade-offs. If you are a mad man, you can invent your own magic formula, eg: DaysInStock^2 + %ProjectProfit/10 – PartsOnCar^3*2 We played with averaging the RANK() of each factor – which helps to factor out the actual values of these non-comparable metrics. However, both DENSE and SPARSE ranks had some side effects that we were not thrilled with (the # of distinct values being ranked ends up impacting your averages).
In the end, we came up with an fairly elegant and configurable solution that I’m sure we will re-use in the future.
Each factor gets both a “Weight” and a “Threshold”. The weight is just used to do a weighted average (so our 3 factors could be 25%, 25%, 50% instead of 1/3rd each). The threshold is used similar to if we were using “percent of max value” as our measure, but allows us to better control and allows us to “cap” the factors.
Maybe our max value is a total outlier, or maybe our business goal is to have a car in stock no more than 100 days. Whatever. We can set the Threshold for days in stock at 100. A car in stock for 50 days is at 50% threshold, and any car at 100 or more gets the full 100% of threshold.
We do need to be careful that we are consistent when setting up these formulas in terms of “higher” or “lower”. In our case, we chose “higher values are more likely to get crushed”, so while our [Days in Stock % Factor] is fine, we would need to do 1-[# Parts on Car % Factor] so that a car with no parts on it is MORE likely to get crushed, not less likely.
The overall Crush Factor would look something like:
[Days in Stock Weight] * [Days In Stock % of Threshold] +
[% Projected Profit Weight] * [% of Project Profit % of Threshold]
[Parts on Car Weight] * [Parts on Car % of Threshold]
Where the [Weight] measures are just reading weights out of a disconnected table that we use to easily configured the weight of each factor. The [% of threshold] measures look something like:
IF ([Total Days in Stock] > [Days in Stock Threshold],
DIVIDE([Total Days in Stock], [Days in Stock Threshold]))
[Total Days In Stock] is our actual measure of the total days in stock. [Days in Stock Threshold] is again just harvesting a value out of our configuration table. In the case we are above the configured threshold, we return 1 (eg: 100%), otherwise we return the percentage by dividing.
Obviously there is a bit of work involved here, but there are a lot of benefits:
1) the conversations around each of the factors are super valuable. “How much weight do we give this factor?” “Where do we draw the line for 100% threshold?” “This is already partially covered by this other factor, do we want to keep both?”
2) we have taken something that was (at best) vaguely sorted (likely by just one factor), and instead sorted it by how the business thinks it should be sorted.
3) It is easy to change thresholds/weights and not too hard to add new factors into our weighting. And 99% of the time… even if we are NOT sorting perfectly, a “pretty good” sort is vastly superior to (basically) no sort. And, if we find some cars aren’t sorting the way we want… we iteratively improve the formulas so that over time it becomes Really Darn Good.
4) once it is sorted, we immediately have something actionable. Something Verby. Something we can do any time we want. Need some more space on the lot? Bring up the report, and crush the top one. Than grab a latte
Depending on your individual needs, the steps you take to make your reports “more actionable” are likely to be a bit different than what we have here. Probably the best place to start is simply asking “What is somebody going to do with this report”. It may be there are a handful of things, so you need to create several different actionable reports. Remember, these are “ideals” and never perfect solutions. Maybe you start with the generic Noun version of The Report and… well, at least you feel a little bad 🙂 Next week, you can slip in juuust one actionable report. Then another.
But as a framework for thinking about your reports, I love this idea. I can’t even guess at the number of reports I have seen with over a thousand rows of data. Nobody wants to scroll through that. Nobody. Nobody is going to get value out of that. If I may… it’s a bit of an insult to the “intelligence” part of “business intelligence”
Just remember that your goal is almost never to generate “The Report”. Your goal is to deliver insights that help make decisions that drive action. If a report isn’t going to cause anybody to actually do something, it probably doesn’t have a ton of value. And as long as you want them to do something, take some time to think about how to reflect that action in your reports.