Public
Advisor

How can I track the number of unanswered posts over time?

I would like to compare how many topics went unanswered in July 2018 versus how many went unanswered in July 2019.

6 Replies 6
Honored Contributor

@NicolaC 
The below queries can help you to get the topics count which were posted in a specific month but not resolved yet. 
For July 2019

Show more
select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00

For July 2018

Show more
select * from messages where conversation.solved=false and depth=0 and post_time > 2018-07-01T00:00:00.000-00:00 and post_time < 2018-07-31T23:59:59.999-00:00

You can run it you have studio/admin access. 
Studio > API Browser 
Here is the screenshot for your reference https://prnt.sc/px8v6p

Give kudos if you find my posts helpful or mark solution if it answers your query

Thanks VikasB.

We only have Studio on staging. Is there any way to do this on the live site?

Thanks

Honored Contributor

@NicolaC 
You can use the below URL to run it for production. You need to replace the base URL with your community base URL

Show more
community.YourCommunity.com/api/2.0/search?q=select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00

 

 

Give kudos if you find my posts helpful or mark solution if it answers your query

Thank you!! So helpful.

Hi @VikasB 

I have tried this and it works a treat - thanks for the tip!  When I "select * from..." I get a size of 25 but when I changed the query to "select count(*) from..." I get a count of 969.  Do you know why this is? 

 

Regards, Paul Khoros CertificationsIf my answer helps, please accept it as the solution or give it the thumbs up!
Honored Contributor

@PAULEM 
By default, API returns 25 results. You can pass a specific limit up to 1000 to get more results. 

Show more
community.YourCommunity.com/api/2.0/search?q=select * from messages where conversation.solved=false and depth=0 and post_time > 2019-07-01T00:00:00.000-00:00 and post_time < 2019-07-31T23:59:59.999-00:00 limit 1000
Give kudos if you find my posts helpful or mark solution if it answers your query

Welcome to the Technology board!

Curious about our platform? Looking to connect on social technology? You've come to the right place!

Are you a Khoros customer? For direct assistance from our Support team, please visit the Support Forum.