Data Analysis and Visualization

Build KPIs not One Time Reports

Written by Alon Brody|January 19, 2016

Businesses need to deliver insights fast if not immediately. If this were not true, we would not be writing this post, you wouldn’t be reading it and over all, big data would be less of a diamond in the rough. That said, fast is a relative term and one that is not necessarily equivalent to efficiency.

Often, when an analyst is asked a question the first instinct is to rush to find the answer. When asked how many users opened your application in the past month, the answer is quite easy, all you need to do is count the unique users that opened the app in the past month. Three minutes tops and you have your answer. So far so good, but what will happen if the question is much more difficult to answer? The time that you will need to invest in it will definitely be more than a few minutes. Often, you will save your script and if you’re lucky, next time you’re asked the same or similar question you will only need to change a few parameters in order to answer the question. However (come on, you knew that was coming), if these types of questions are asked often it turns into a tedious task and the reality is that the questions will never be exactly the same so more and more often changing the parameters simply won’t cut it.

Data Warehouse too complex to manage? Learn how to leverage AWS

This methodology, or to be more exact lack of methodology, will result in you building many one-time reports where each script answers a specific question or a set of questions. To break this spiral and empower data consumers to ask and retrieve data on demand you need to think ahead and create relevant KPIs in advance. Why create a report/script that shows the MAU (monthly active users) of Nov. 15′ when you can create an aggregated table (as granularly as possible) that will answer this question and many more. Just to set a few things straight here’s an example:

Q: MAU Nov. 15’?

Month MAU
Nov. 15’ 15,289

Q: MAU Oct. 15’?

Month MAU
Oct. 15’ 14,347
Nov. 15’ 15,289

Two scripts written so far. Only now you are asked the same questions per platform:

Q: MAU Oct – Nov 15’ by platform?

Month Platform MAU
Oct. 15’ iOS 5,732
Oct. 15’ Android 10,223
Nov. 15’ iOS 5,173
Nov. 15’ Android 11,136

Now we know MAU per platform. Did you notice that the sum of both platforms by month is higher than the total MAU we’ve received in the previous table; Apparently some of your users are both iOS and Android users. This issue will reoccur the granular the data becomes; the deeper down we will dig the more often the cases where users can be in two different rows at the same time. We can solve this by saving several different levels of aggregation (in the same table/query or a few). The result table should look like this:

Month Platform MAU
Oct.15’ iOS 5,732
Oct. 15’ Android 10,223
Oct. 15’ Total 14,347
Nov. 15’ iOS 5,173
Nov. 15’ Android 11,136
Nov. 15’ Total 15,289

This way all the user needs to do is ask what was the MAU with the relevant parameters he needs. As long as we keep the KPI table granular your users will be able to get answers for any question they have so long as sufficient data is available. Obviously you can add to the KPI table more dimensions like: country, gender, city, etc.:

Month Platform Country City Gender MAU Installs Revenue

All the data consumer needs to do now is query this KPI table and ask for the relevant combinations only. Note, that now your data consumer (CMO/ CEO/ product manager and so on) has the answers for the “simple” questions at the edge of their fingertips. This will free up a huge amount of time allowing you to focus on deep analysis, data mining and so forth.

So to conclude, with a single query we can now answer many questions and not only one. The MAU KPI created enables us to ask as many varied questions regarding this set of KPI (MAU of Nov. 15’, Average MAU on Android, male MAU in Oct. 15’ and many more). All we need to do is query this KPI table/ view and we will have all or most of the answers we are looking for. Obviously, this KPI table/ query/ view will require maintenance if a new dimension or metric is added to the data but on a day to day basis there is little to no work needed on this query. The end users will be able to use this query/ table/ view in any data visualization tools the company has.

Data Warehouse too complex to manage? Learn how to leverage AWS

 

From raw data to analysis in under 10 minutes.

Sign up now for a demo or a free trail of the Panoply.io platform.

Learn more about platform features