15 Replies Latest reply on Oct 22, 2013 3:37 AM by dishantgniit

    Metrics for Usage of Community

    dishantgniit

      Hello,

       

      We need to know who are the top users of our community. We need be able to examine this over different time periods so we can see who is increasing usage, and who is decreasing.

      I have looked at CMR(Community Manager Report) and Jive SBS Reporting tool but didn't find good option.

      Is there any way in which I can get the details of all users in Spreadsheet file how many times all users login in one month, week etc?


      Ryan Rutan Can you please give me an idea on this?


      Thanks In Advance


      Best Regards

      Dishant

        • Re: Metrics for Usage of Community
          dishantgniit

          Ryan Rutan: Can you please reply on this?

           

          Best Regards

          Dishant

            • Re: Metrics for Usage of Community
              DustinSmth

              Hi Dishant,

               

              Do you have access to the Jive Analytics database for your Jive Instance?  If yes, you can get this answer from Activity Fact table.

               

              Reference documentation for what I'm talking about: How to import the analytics database into your local Postgres install

               

              -Dustin

                • Re: Metrics for Usage of Community
                  Ryan Rutan

                  Dishant Mehta - I would look at Dustin's post above.  This is what I would recommend in general, not knowing the details.

                  1 person found this helpful
                  • Re: Metrics for Usage of Community
                    dishantgniit

                    Hello Dustin Smith,

                     

                    Thanks you much very much for the reply!

                    I don't have access on Jive Analytics database. I am working with Jive Support on this.

                    Can you please let me know which SQL query I need to run in Activity Fact table for the results?

                     

                    Ryan Rutan: Thanks for your reply.

                     

                    Best Regards

                    Dishant

                    • Re: Metrics for Usage of Community
                      dishantgniit

                      Hello Dustin Smith,

                       

                      Thanks you much very much for the reply!

                      I don't have access on Jive Analytics database. I am working with Jive Support on this.

                      Can you please let me know which SQL query I need to run in Activity Fact table for the results?


                      Best Regards

                      Dishant

                        • Re: Metrics for Usage of Community
                          Caton Guilbault

                          Hi Dishant,

                           

                          This query should get you started once you have access to the Analytics database. Let me know if you have any questions. I also recommend joining this group Jive Analytics you will find some nifty queries other folks have posted as well.

                           

                          SELECT

                            jivedw_user.user_id,

                            jivedw_activity_meta.activity_name as "Activity",

                            jivedw_day.begin_ts,

                            sum(jivedw_activity_agg_day.activity_count)

                          FROM

                            jivedw_day INNER JOIN jivedw_activity_agg_day ON jivedw_day.day_id=jivedw_activity_agg_day.day_id

                            LEFT JOIN jivedw_activity_meta ON jivedw_activity_meta.activity_type=jivedw_activity_agg_day.activity_type

                            LEFT JOIN jivedw_user ON jivedw_user.user_id=jivedw_activity_agg_day.user_id

                          WHERE

                            (

                             jivedw_day.begin_ts

                            >=  '9/21/2013 00:0:0'

                             AND

                             jivedw_day.end_ts

                            <  '10/8/2013 00:0:0')

                            AND jivedw_activity_agg_day.activity_type in ('10','160')

                           

                          GROUP BY

                            jivedw_user.user_id,

                            jivedw_activity_meta.activity_name,

                            jivedw_day.begin_ts

                            • Re: Metrics for Usage of Community
                              dishantgniit

                              Hello Caton,

                               

                              I have looked/downloaded Analytics PostgreSQL Backup and System PostrgreSQL Backup.

                              I noticed that both databases provide same table data information.

                              What is the difference between both databases tables like:

                               

                              jivecommunity Vs jivedw_community

                              jivesgroup Vs jivedw_sgroup

                              jivedocument Vsjivedw_document

                               

                              Which database I should follow for Analytics purpose?

                               

                              I want to get below report:

                              All the Places with no activity for more than 3 months.

                              Content that has not been viewed/accessed for over 1 year

                               

                              Can you please suggest me on this?

                               

                              Best Regards

                              Dishant

                                • Re: Metrics for Usage of Community
                                  Caton Guilbault

                                  Hey Dishart,

                                   

                                  The database you will use depends on what you are looking for. The Analytics database basically records what happened in the past while the system database stores what powers the UI. That was a very simplistic definition for brevity sake.

                                   

                                  I recommend reading the following to help get up to speed on where to look for what:

                                   

                                  Now on your specific report requests:

                                  1. All the Places with no activity for more than 3 months. - I would use the CMR places report for this as its easier than writing queries and the CSV export pulls in all the basic info you will need with a little extra for good measure.
                                  2. Content that has not been viewed/accessed for over 1 year - Take a look at this doc posted by Matt Nevill content viewed in the last month by logged in users you could use the data provided to compile the report your looking for.
                                    • Re: Metrics for Usage of Community
                                      dishantgniit

                                      Hello Caton,

                                       

                                      Thanks you very much for the reply!

                                       

                                      All the Places with no activity for more than 3 months : I don't think CMR is able to provide the report of my this requirement because its only provides the Report that shows the number of views, rates, votes, responses, and revisions between date range.

                                       

                                      Content that has not been viewed/accessed for over 1 year: As you suggested, I am trying this.

                                       

                                      I think Analytics DB is the good option for my this requirement right?

                                       

                                      Kindly let me know the query for my 1st and 2nd requirement, if you know.

                                       

                                      May-Many Thanks in Advance!

                                       

                                      Best Regards

                                      Dishant

                                      • Re: Metrics for Usage of Community
                                        dishantgniit

                                        Hello Caton Guilbault

                                         

                                        Thanks you very much for your help!

                                        I have prepared one query for my 2nd question:

                                         

                                        SELECT

                                        jivedw_activity_fact.activity_ts, jivedw_activity_fact.user_id, jivedw_activity_fact.activity_type, jivedw_object.dw_object_id, jivedw_object.object_type, jivedw_object.object_id, jivedw_object.name, jivedw_container.dw_container_id, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name, jivedw_user.username, jivedw_user.email, jivedw_user.firstname, jivedw_user.lastname

                                        FROM

                                        jivedw_activity_fact, jivedw_user, jivedw_object, jivedw_container

                                        WHERE jivedw_activity_fact.activity_type = 10 AND jivedw_activity_fact.user_id != -1 AND jivedw_activity_fact.user_id = jivedw_user.user_id AND jivedw_activity_fact.direct_dw_object_id = jivedw_object.dw_object_id  AND jivedw_activity_fact.dw_container_id= jivedw_container.dw_container_id

                                        AND jivedw_activity_fact.activity_ts  < current_date - interval '1 year'

                                         

                                        Kindly review my query and help me to correct it.

                                         

                                         

                                        Thanks in Advance!

                                         

                                        Best Regards

                                        Dishant

                                          • Re: Metrics for Usage of Community
                                            Caton Guilbault

                                            Did the query return what you expected? It looks like you might have to specify how these tables are joined.

                                              • Re: Metrics for Usage of Community
                                                dishantgniit

                                                Hello Caton Guilbault,

                                                 

                                                Thanks for the reply!

                                                I am getting the content that viewed from 2011 to 2012 but its not correct.

                                                I need below two reports:

                                                 

                                                1. Content that has not been viewed/accessed for over 1 year

                                                2. Collaboration:

                                                - Most active users using our community for collaboration in groups, spaces, projects

                                                - Users trending upwards - users who are starting to contribute and participate more than before

                                                - Users trending downwards - users who were formerly key contributors and participants that are reducing their activity


                                                Social Networking/Communication/Content management

                                                - Most active contributors using our community (blogs, documents, videos and other types of content)

                                                - Most active participants using our community (comments, discussions)

                                                - Users trending upwards - users who are starting to contribute and participate more than before

                                                - Users trending downwards - users who were formerly key contributors and participants that are reducing their activity

                                                 

                                                 

                                                Can you please help me to provide these quries?

                                                 

                                                Thanks in Advance

                                                Best Regards

                                                Dishant

                                • Re: Metrics for Usage of Community
                                  bchamberlain

                                  HI Dishant -

                                   

                                  I don't know if you are looking for an add-on module or not, but the analytics framework Centric Consulting built (based on QlikView) easily accommodates what you describe. (as well as a lot more) If you are interested in chatting, juet let me know.