3 Replies Latest reply on Dec 20, 2013 3:41 PM by spagnj

    Capturing User info from Content Views

    kusuma

      I'd like to know if there is a way to find out about the users viewing the Content. Currently, I only have a table jiveviewcount which gives the count of views for a thread but no user info.

        • Re: Capturing User info from Content Views

          The analytics Db and new analytics service (for Fall Cloud and Jive 7) both capture this information. In fact in Fall Cloud and Jive 7 it is exposed in the UI as Impact Metrics.

           

          But to get it from an analytics standpoint in version 6 and earlier, you would look at the jivedw_activity_fact table - particularly at the VIEW events (event code 10 - more info here: Analytics DB Data Model) or at one of the _agg_ tables that roll up activity by day, week, or month...

          1 person found this helpful
          • Re: Capturing User info from Content Views
            spagnj

            Kusuma,

             

            If you have the system database and analytics database .dmp files (you will need both) loaded into a PostgreSQL database... (to load them into a PostgreSQL database follow these instructions: How to import the analytics database into your local Postgres install )

             

            This SQL statement will provide you what you're looking for...

             

            SELECT DISTINCT

            daa.activity_ts

            ,o.creation_ts

            ,o.object_id

            ,o.object_type

            ,CASE o.object_type

            WHEN 1 THEN 'Thread'

                    WHEN 18 THEN 'Poll'

                    WHEN 3 THEN 'User'

                    WHEN 13 THEN 'Attachment'

                    WHEN 14 THEN 'Community Space'

                    WHEN 20 THEN 'Private Message'

                    WHEN 38 THEN 'BlogPost'

                    WHEN 102 THEN 'Document'

                    WHEN 1100 THEN 'Video'

                    WHEN 96891546 THEN 'Event'

                    WHEN 1464927464 THEN 'Wall Entry'

            WHEN 3227383 THEN 'Idea'

                    ELSE 'Other'

            END AS object_description

            ,o.name as object_name

            ,dc.name as community_name

            ,dc.container_type

            ,dc.container_id

            ,daa.user_id

            ,CASE WHEN ju.name IS NOT NULL THEN ju.name

            WHEN ju.name IS NULL THEN (ju.firstname||' '||ju.lastname)

            ELSE ju.email END AS user_disp_name

            ,ju.username

            ,ju.email

            FROM jivedw_activity_fact daa

            LEFT JOIN jivedw_object as o on o.dw_object_id = daa.direct_dw_object_id

            LEFT JOIN jivedw_container as dc on daa.dw_container_id = dc.dw_container_id

            LEFT JOIN jiveuser as ju on ju.userid = daa.user_id

            WHERE 1=1

            AND daa.activity_type IN (10)

            AND (daa.activity_ts BETWEEN date_trunc('hour', timestamp '2013-01-01 00:00:00') and date_trunc('hour', timestamp '2014-01-01 00:00:00'))