Forum Discussion

tyw's avatar
tyw
Boss
4 years ago

Count # of members subscribed to a topic?

If my understanding is correct this call will let me see which members are subscribed to a discussion board.

E.g of 500 community members, 30 have subscribed to get a notification whenever activity occurs on this board. 

 

https://community.DOMAIN.com/restapi/vc/boards/id/BOARD-ID/subscribers/email/board/

 

Is there a way to do this for a specific topic however?  Identify who, and the total count of members subscribed to a topic?

  • You can supply the thread id when querying API v2's "subscriptions" collection:

    Count of subscribers: 
    SELECT count(*) FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'

    Subscribers: 
    SELECT subscriber FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'

    • tyw's avatar
      tyw
      Boss

      Thanks Claudius 

      I suspect user error on my part but this doesn't appear to be working for me. 

      If i subscribe using a test account the result doesn't change, I am show '1' as the result in count query, the details query just shows my details (the 1 account I assume)?

      I am changing the targetid of course so I'm not sure what I am doing wrong. 

      SELECT count(*) FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'

      • Shows the result of '1' despite subbing from other test accounts.

      SELECT subscriber FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'

      • Results in my details being shown only; I know another account is subscribed however. I am logged in as the Admin here. 
  • Hi all,

     

    Sorry to be late to this thread, as has already been said trying to get thread level subscriber counts is not very well supported at the moment. It should be better using the APIs provided by Aurora.

    For Board subscriber counts

    I have written an excel file which allows you to iterate through every board in a community and find out how many people are subscribed to that board.

    Instructions:

    Open this XLSM file: https://1drv.ms/x/s!AkiSjdO4yjrasPABOjsq12IkyikdVA?e=mYjICe

    You may need to enable content (depending on the security settings of your excel installation)

    Go to the settings tab and enter: 

    UserName: the user name of a user in your community who at least as read permissions to the API

    Password: The password for the above user (note: if your community uses SSO then you will need to create a non-sso user using the create user button in the Users Tab).
    Community URL: The base url for your community (i.e. https://community.khoros.com

    ClientID: Go to this page: OAuth 2.0 authorization grant flow (khoros.com) and follow the steps to register an App and you get this client id.

    Once done go back to the Report Data tab and click Get Subscription Data and it will pull every single category / board from your community and put the subscriptions to that board in this file.

    As noted previously this count DOES NOT include subscriptions to the actual topic just the board.

    If you get an error when you try to run the code you may need to add some references. To do this open the file, right click on the sheet name and click "View Code". When the VBA editor opens click Tools at the top and click References.

    This file requires:

    • Visual Basic for Applications
    • Microsoft Excel 16.0 Object Library (likely anything newer will also work)
    • OLE Automation
    • Microsoft Office 16.0 Object Library (likely anything newer will also work)
    • Microsoft ActiveX Data Objects 6.1 Library
    • Microsoft Forms 2.0 Object Library
    • Microsoft XML v6.0

     

    Disclaimer: this file is provided as is, it was clean of viruses and only does what I say it does at the time I uploaded it but you should treat any file you don't know with suspicion until your happy it works safely.

    This file REQUIRES Microsoft Excel Desktop and will not function in the Web browser.

    Feel free to reuse any of my VBA code, although would love to know if you use guys use it for anything else cool!

     

    • keithkelly's avatar
      keithkelly
      Leader

      Whoa!  This worked.  I checked the VBA for anything scummy and found nothing. 

      I did, however, make one change in Main_Prog to allow it to work for me:

      strLiql = "Select id,title from categories where ancestor_categories.id = 'communities' ORDER BY position DESC limit 1000"

      making it:

      strLiql = "Select id,title from categories ORDER BY position DESC limit 1000"

      Thank you for sharing this!  Currently I have a PowerQuery M script getting other sorts of data, and have been hoping to get some API tools a bit more integrated into Excel for stuff like this.  This file gives a sweet jump-start! 

      I will share my (re)usages!

      • allensmith81's avatar
        allensmith81
        Boss

        Good catch! I thought I had removed everything that was specific to our community.

        Also please you didn't find anything scummy 🙂

         

        Allen Smith

        Technical & Compliance Lead

        Microsoft Tech Community

  • Thanks this sheet I have put here is only a small subsample of functions in a big sheet I have that does everything from manage labels, extract blog authors, editors, publishers to find users by email address enmass (but still quicker than pulling out entire user database from admin).

     

    Infact here is what the full sheet can do, let me know if anyone needs functions for this:

     

    • Move Posts
    • Get a count of how many members have a role (part of another sequence of functions / subs)
    • Get a topic count (used repeatedly in another call to produce a list of topics for every board in the community)
    • Get MessageID from URL
    • Get repliy count for a given message id
    • Get a users rank
    • Delete messages
    • Get all the labels on a a given message id
    • Get all the tags on a given message id
    • Clean the date given by the API into an actual date for excel
    • Delete a Tag
    • Delete a Label
    • Create a Tag
    • Create a label
    • Find out the conversation style for a board id
    • Get all the roles a given user has
    • get the role id for a give blog role
    • Get the RSVPs for an event
    • get user id by email address
    • Get a unique list of labels for a given board (up to 1000)
    • List some of the meta data for a given collection of messages in a spreadsheet
    • Post a message from excel to the community
    • get all message ids for every topic in a board
    • Get the name of the board a message id is in
    • List all users who have not full completed registration
    • List all categories in a given category

    I also have a whole suite of tools that do stuff in Python as well.

    Happy to share, keep in mind I may not always be able to reply right away.

    • keithkelly's avatar
      keithkelly
      Leader

       

       

      I'm on a similar track to build out little helper utilities, but so far only using Python and PowerQuery / M.

       

      • allensmith81's avatar
        allensmith81
        Boss

        keithkelly Yeah PowerQuery could be really powerful, I wrote my VBA before PowerQuery was a thing.

        Of course I am kinda at the point now where I am clearing my decks ready to build utilities using the new API for Aurora.. that should be a game changer from what is documented.

        Can you imagine being able to label a post in real time as "hot" or "trending" because of realtime pageview counts....

        Community Mecca.

  • tyw 

    Unfortunately yes, this API is only designed to do stuff for the current users subscriptions (or another user if you pass subscriber.id='xx') and is unable to do a global count of subscribers to a node. 

     

  • Bparra, to locate the board ID, navigate to Community Structure, access the board, and select Manage. The URL should conclude with "forum-board:myforum". For filter creation, utilize your text editor to locate the necessary code phrases such as "board.id.title = Forum ID". Another option is to use "config.filter_report_board_id.title = Board ID for filter reports".

    Please refer to this post for complete details with screenshot. 

     
  • Is there a way to get board subscriptions by using the same format mentioned above? 

    "Count of subscribers: 
    SELECT count(*) FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'

    Subscribers: 
    SELECT subscriber FROM subscriptions WHERE target.type = 'thread' AND target.id = '2250'"

  • Hello Bparra 

    Could you clarify if you are interested in obtaining board subscriptions or subscriptions to individual messages/threads?

     
  • Hi MohammedSh 

    Both! However it keeps giving me my user id unique subscriptions and Im wanting global subscriptions to certain threads and boards.

    Another problem I am having is that our board ids have changed and Im not able to find the board id of when they were originally created. 

    Thanks! 
    Betty Jo