Adobe Analytics Requirements and SDR in Workspace – Part 3
Over the past two weeks, I have been posting about how to view your business requirements and solution design in Analysis Workspace. First, I showed how this would look in Workspace and then I explained how I created it. In this post, I am going to share how you can extend this concept to calculate the completion percentage of business requirements directly within Analysis Workspace. Completion percentage is important because Adobe Analytics implementations are never truly done. Most organizations are continuously doing development work and/or adding new business requirements. Therefore, one internal KPI that you may want to monitor and share is the completion percentage of all business requirements.
Calculating Requirement Percentage Complete
As shown in the previous posts, you use Data Sources to upload a list of business requirements and each business requirement has one or more Adobe Analytics variables associated to it:
When this is complete, you can see a report like this:
Unfortunately, this report is really showing you how many total variables are being used, not the number of distinct business requirements (Note: You could divide the “1” in event30 by the number of variables, but that can get confusing!). This can be seen by doing a breakdown by the Variable eVar:
Since your task is to see how many business requirements are complete, you can upload a status for each business requirement via a SAINT file like this:
This allows you to create a new calculated metric that counts how many business requirements have a status of complete (based upon the SAINT Classification attribute) like this:
However, this is tricky, because the SAINT Classification that is applied to the Business Requirement metric doesn’t sum the number of completed business requirements, but rather the number of variables associated with completed requirements. This can be seen here:
What is shown here is that there are five total variables associated with completed business requirements out of twenty-five total variables associated with all business requirements. You could divide these two to show that your implementation is 20% complete (5/25), but that is not really accurate. The reality is that two out of nine business requirements are complete, so your actual completion percentage is 22.22 % (2/9).
So how do you solve this? Luckily, there are some amazing functions included in Adobe Analytics that can be used to do advanced calculations. In this case, what you want to do is count how many business requirements are complete, not how many variables are complete. To do this, you can use an IF function with a GREATER THAN function to set each row equal to either “1” or “0” based upon its completion status using this formula:
This produces the numbers shown in the highlighted column here:
Next, you want to divide the number of rows that have a value of “1” by the total number of rows (which represents the number of requirements). To do this, you simply divide the preceding metric by the ROW COUNT function, which will produce the numbers shown in the highlighted column here:
Unfortunately, this doesn’t help that much, because what you really want is the sum of the rows (22.22%) versus seeing the percentages in each row. However, you can wrap the previous formula in a COLUMN SUM function to sum all of the individual rows. Here is what the final formula would look like:
This would then produce a table like this:
Now you have the correct requirement percentage completion rate. The last step is to create a new summary number visualization using the column heading in the Requirement Completion % column as shown highlighted here:
To be safe, you should use the “lock” feature to make sure that this summary number will always be tied to the top cell in the column like this:
Before finishing, there are a few clean-up items left to do. You can remove any extraneous columns in the preceding table (which I added just to explain the formula) to speed up the overall project so the final table looks like this:
You can also hide the table completely by unchecking the “Show Data Source” box, which will avoid confusing your users) :
Lastly, you can move the completion percentage summary number to the top of the project where it is easily visible to all:
So now you have an easy way to see the overall business requirement completion % right in your Analysis Workspace SDR project!
[Note: The only downside of this overall approach is that the completion status is flagged by a SAINT Classification, which, by definition, is retroactive. This means that the Analysis Workspace project will always show the current completion percentage and will not record the history. If that is important to you, you’d have to import two success events for each business requirement via Data Sources. One for requirements and another for completed requirements and use formulas similar to the ones described above.]
Click here to see Part 4 for even more cool things related to this concept!