What is the difference in kf_answer_stats and ans_stats tables for Knowledge Advanced?
Oracle Service Cloud Knowledge Advanced
I am building a custom report that includes statistics on the answers on my site. The kf_answer_stats and an_stats tables are both available, but it is unclear which table is the correct choice for my business needs.
The two tables aggregate data differently:
- The ans_stats table only has hourly data
- The kf_answer_stats table has windows of hourly, daily, monthly, and yearly data that is rolled up, i.e. aggregated over specific time periods.
The hourly and daily data is removed after 36 hours and KF_ACTIONABLE_DATA_PURGE_DAYS days respectively. The window values of 0, 1, 2, and 3 correspond to hourly, daily, monthly, and yearly rollups (note that hourly data is not reportable). Less granular rollup data includes the more granular data (for example, a yearly rollup value includes all of the monthly values).
Ultimately, the kf_answer_stats data is more useful, even though it eventually purges its daily counts, because of all of the extra data it contains. Keep in mind the kf_answer_stats window column is important when interpreting this data and has values of 1 - Daily, 2 - Monthly, and 3 - Yearly
- In the calculation of ans_stats.solved_* and kf_answer_stats.rated_*, the ans_stats also recognizes answer ratings (action = "AnswerFeedback")
- Note that the stat_date values for ans_stats are different than the kf_answer_stats values.
- ans_stats records the actual hour the rating took place: e.g. '2019-04-19 05:00:00'
- kf_answer_stats only shows the daily rollup time, which is the top of the day (adjusted for site time zone): e.g. '2019-04-19 00:00:00
- For every set of rows in the ans_stats table that share a month, there is a row in kf_answer_stats; however, the daily and yearly rollup values really don't have a corresponding piece of data in the ans_stats table.