Khoros Atlas Logo

Occasional Advisor
Occasional Advisor

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

Jump to solution

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

Tags (1)
0 Kudos
6 Replies 6
Honored Contributor
Honored Contributor

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

Jump to solution

@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
Tags (1)
0 Kudos
Reply
Loading...
Occasional Advisor
Occasional Advisor

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

Jump to solution

Thanks VikasB.

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

Thanks

0 Kudos
Reply
Loading...
Honored Contributor
Honored Contributor

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

Jump to solution

@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
Reply
Loading...
Occasional Advisor
Occasional Advisor

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

Jump to solution

Thank you!! So helpful.

0 Kudos
Reply
Loading...
Advisor
Advisor

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

Jump to solution

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 Certified Community Manager/ModeratorIf my answer helps, please accept it as the solution or give it the thumbs up!
0 Kudos
Reply
Loading...
Honored Contributor
Honored Contributor

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

Jump to solution

@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
Reply
Loading...