How can I get an average of the latest AQI scores using Performance Analytics?

[Lisa Baremore]

Hello, 

Looking for some advice on how to create an automated indicator that shows trending on the average of the "Latest AQI" scores.

In the kb_knowledge table there is a field called "Latest AQI". Great field, however it's a reference field so I can't do math on it. In order to populate this, there must be a business rule that looks at the Article Checklist Summary [kb_article_checklist_summary] table > aqi_score field, looks at all the records for a particular knowledge article, and finds the most recent on to populate this "Latest AQI" field in the kb_knowledge table.

Of course, when I create an automated indicator based on this field, it doesn't work because "Latest AQI" is a reference field. In the automated indicator source tab, I am not able to dot walk to the Article Checklist Summary [kb_article_checklist_summary] > api_score field - which would be a great solution if I could.

What do you recommend for how I can achieve this?

[Josh Cooper · Accepted solution]

I wish I had better data to show you, but at least I have some good news for you.  The AQI indicator that you're looking for is already part of the Knowledge Management content pack that's available from London forward:

It may be easiest to install the content pack for Knowledge.  It's free as long as you have Performance Analytics, and it looks like you do.  Once you've got the content pack, even if you don't want the whole dashboard, you're able to reuse all of the widgets, indicators, breakdowns and sources that come with it.

If you don't want or need the whole package, the way they're doing it is this:

Formula Indicator - Article Quality Index
Formula = [[Sum of AQI Scores - Monthly]] / [[Number of AQI Records - Monthly]]

Automated Indicator - Sum of AQI Scores - Monthly
Sum of 'AQI Score' field on KBArticleChecklist.Monthly Indicator Source
Add'l Conditions:  State is Submitted

Automated Indicator - Number of AQI Records - Monthly
Count on KBArticleChecklist.Monthly Indicator Source
Add'l Conditions:  State is Submitted

Indicator Source - KBArticleChecklist.Monthly
Facts Table is Article Checklist Summary (kb_article_checklist_summary)
Conditions:  Created on This month

Hopefully this helps!

[Lisa Baremore]

Hi Josh

That is fantastic information and I greatly appreciate the detail! My problem is that in the kb_article_checklist_summary table, there is more than one record per article where the state is submitted so I will get inflated numbers. Could that be an issue with our implementation of AQI? Should there only be one record per article where the state is submitted? If I could solve for that, I could create these exactly as you described.

Oh and we are still on Kingston. Upgrading to London is coming soon, about 3 months from now. So I am looking for an immediate solution.

Thank you!!!

[Josh Cooper]

No problem!  

You're going to make me 'talk out of school' because I'm an analytics guy and not so familiar with Knowledge Management, but it just so happened I had to work with someone on Knowledge content a few months back, and I had to ask for the explanation of AQI.

I BELIEVE, and don't hold me to this, but I believe I was told that the idea of AQI is that it's a sort of a survey type thing built around knowledge articles, the way the CSAT scores are done for Incidents.

So with that in mind, I can see how it could go either way... I'm not 100% sure if the table is intended to hold essentially each survey (like if you take the survey and I take the survey for the same article), or if it's intended to hold just the final score for each article.

It seems like you could make a case for it either way, but I'm not totally sure which way it was intended to be as delivered!

[Lisa Baremore]

Hi Josh, I just spoke with my Knowledge Management product owner and he explained that in the future there shouldn't be multiple AQI records in the 'submitted' state so I am going to go with your solution. 

Thank you so much for the advice!