Data Explorer query help: metrics to understand unique individual participants


(Southpaw) #1

When I look at an individual topic, I can unfold a list of individuals who have posted on the topic, and the number of times they have done so:
image

Could someone please help me build a query in Data Explorer to determine:

  • Average number of unique individuals per topic (day/week/month/year)
  • Top-ten topics in the last day/week/month/year based on topics having the largest number of unique individual participants.

Thanks in advance.
Southpaw


(David Taylor) #2

I thought this would be really complicated, but turns out there’s a handy “participant_count” column in the database :slight_smile:

Top topics by number of unique participants over the last year

-- [params]
-- string :interval = 1 year

SELECT id as topic_id, participant_count  
FROM topics
WHERE bumped_at >= CURRENT_DATE - INTERVAL :interval
ORDER BY participant_count DESC

Average number of unique participants over the last year

-- [params]
-- string :interval = 1 year

SELECT avg(participant_count)
FROM topics
WHERE bumped_at >= CURRENT_DATE - INTERVAL :interval

(Southpaw) #3

Thank you so much @David_Taylor! That’s a tremendous help!

(And thank you @zogstrip for moving the thread to the right category. I’ll get my bearings eventually.)


(Southpaw) #4

Is there documentation anywhere of what the different variables, like participant_count actually represent?

The top topics query is returning numbers that don’t match the “Frequent posters” image in the thread. I’m wondering if it includes likes or some other activity.


(David Taylor) #5

Unfortunately the only documentation is the code itself. I’m pretty sure participant_count should be the number of unique posters…

Do you have an example public topic where it doesn’t match up?


(Southpaw) #7

Thanks for following up. I want to explore it further, and will post some examples if I can’t sort things out on my own.


(Southpaw) #8

Hi @David_Taylor,

Sorry for the very long delay. I do have some examples now. Sometimes the query is right, sometimes the count displayed in the thread is right. Sometimes they report the same number and are both right!

I noticed that when the counts differ, the threads have had posts split off to new threads.

Examples: Query results correct, thread count wrong

Kudo & Win shows 17 participants in the query results, but when I unfold the info in the thread, I see 21 “users”. Actual count: 17.


No connection to cellular network… has 26 participants in the query results. The thread shows 24. Actual count: 26.


Moto X4 Coming Soon to Republic Wireless! returns 21 participants in the query results, thread indicates 22, actual count: 21.


Examples: Thread count correct, query results wrong

Google Pixel2 and Pixel2 XL BYOP Support has 21 participants in the query results. The thread claims 19. Actual count: 19.


System Update for Moto X Pure (GSM Only) query results return 11 participants, thread count indicates 10. actual count: 10.


(Jeff Atwood) #10

I believe this is mostly expected. Important to note.


(Vinoth Kannan) #11

While calculating participant_count we are ignoring hidden post users.

In above example (both images are from same topic) the first image includes hidden post users since logged in as admin. So it have 21 Frequent Posters. In second image while looking at the topic as anonymous user it correctly shows 17 Frequent users by excluding hidden posts.

For staffs we will render hidden (deleted) posts. That’s why it included them too in “Frequent Posters” list. Should we hide them?


(Vinoth Kannan) #14

I tested this functionality. I am unable to reproduce it. As I described in previous post the counts are differ (for staffs) only if the topic had deleted posts.

Like it named as “Frequent Posters” currently we only displaying top 24 users who frequently posted. So if a topic had more than 24 users it will filter and display only top 24 participants.


(Sam Saffron) #15

A post was split to a new topic: 24 user max in topic statistics