Forum Discussion

Akenefick's avatar
Akenefick
Genius
4 years ago

API for board information per time period

We are trying to pull some data about specific boards through the API, and we also want to be able to specify a time period. Is there a way to do the following?

  • Total Posts by Board per defined time period
  • Total Members accessing the Board per defined time period
  • Top Solution Authors by Board per defined time period

I did find info here Get message counts (khoros.com) that works to get total threads for a board, but I can't figure out how to put time constraints on that.

I also found info here Get top-kudoed authors (khoros.com) but I want top solution authors instead of kudoed. Is there a way to do that?

Thanks

API
  • SuzieH's avatar
    SuzieH
    4 years ago

    Hi Akenefick 

    You might have better luck if you use the Community API v2 /search endpoint with a LiQL query that includes a date range in the WHERE clause. I'm not an engineer, but I was able to get what I think are correct responses on my QA site. 

    For Total Posts by Board per defined time period, I tried the following query to the messages collection. Note that I included "(depth=0)" in the WHERE clause. That filters results to topics only. Remove it if you want both topics and replies in the response. Also, be sure to look at the note about using date ranges in LiQL queries when using the post_time field as a constraint. 

     

    select count(*) from messages where board.id = 'suzieForum1' AND depth=0 AND post_time > 2018-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:04:30-08:00

     

     

    For Top Solution Authors by Board per defined time period, I tried the following. I took "Top" to be 'most kudoed' so that is how I ordered the results. Also, I only returned messages that had 1 or more kudos with "kudos.sum(weight) > 0".  I think you'd want to do something like that so that your response is lighter?

     

    select author, subject, view_href, kudos.sum(weight) from messages where board.id = 'suzieForum1' AND kudos.sum(weight) > 0 AND is_solution = true AND post_time > 2013-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:18:30-08:00 order by kudos.sum(weight) DESC LIMIT 5

     

     

    I'm not sure how to get Total Members accessing the Board per defined time period. The boards collection has a views field, but I don't think that's unique views, and I don't know of a way to return whether views are by actual community members vs anonymous users. Perhaps someone in Services or Support has done something like this ChadB AdamN TysonN MattV ? Or maybe some of our other Titans Claudius jeffshurtliff allensmith81 ? Hopefully one of these folks can also verify whether my example queries are performant or not 😀.

    Cheers!

     

  • allensmith81's avatar
    allensmith81
    Boss
    4 years ago

    Akenefickwhat SuzieH @has given you above is about the limit of what you can get in terms of timeboxed data.

    the views count can not be limited by a timeboxed period so you either get total views over all time or no views. 

    also the views count returned in the message collection is total pageviews (human, bot, crawler etc) where as the views you get in community analytics is total human pageviews so the numbers never add up and can cause confusion for stakeholders.

    there is no way to get total unique pageviews in a timeboxed period via khoros APIs.

    you could possibly do something using third party metrics such as Adobe analytics but we haven’t tried that.

    also in some of the roadmap calls we have seen some promising changes to analytics which may make some of this possible.

     

    Allen

  • SuzieH's avatar
    SuzieH
    Khoros Alumni (Retired)
    4 years ago

    Hi Akenefick 

    You might have better luck if you use the Community API v2 /search endpoint with a LiQL query that includes a date range in the WHERE clause. I'm not an engineer, but I was able to get what I think are correct responses on my QA site. 

    For Total Posts by Board per defined time period, I tried the following query to the messages collection. Note that I included "(depth=0)" in the WHERE clause. That filters results to topics only. Remove it if you want both topics and replies in the response. Also, be sure to look at the note about using date ranges in LiQL queries when using the post_time field as a constraint. 

     

    select count(*) from messages where board.id = 'suzieForum1' AND depth=0 AND post_time > 2018-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:04:30-08:00

     

     

    For Top Solution Authors by Board per defined time period, I tried the following. I took "Top" to be 'most kudoed' so that is how I ordered the results. Also, I only returned messages that had 1 or more kudos with "kudos.sum(weight) > 0".  I think you'd want to do something like that so that your response is lighter?

     

    select author, subject, view_href, kudos.sum(weight) from messages where board.id = 'suzieForum1' AND kudos.sum(weight) > 0 AND is_solution = true AND post_time > 2013-10-07T10:04:30-08:00 AND post_time < 2021-07-19T10:18:30-08:00 order by kudos.sum(weight) DESC LIMIT 5

     

     

    I'm not sure how to get Total Members accessing the Board per defined time period. The boards collection has a views field, but I don't think that's unique views, and I don't know of a way to return whether views are by actual community members vs anonymous users. Perhaps someone in Services or Support has done something like this ChadB AdamN TysonN MattV ? Or maybe some of our other Titans Claudius jeffshurtliff allensmith81 ? Hopefully one of these folks can also verify whether my example queries are performant or not 😀.

    Cheers!