Jive Analytics

4 Posts authored by: roginsb
roginsb

Report on user login frequency

Posted by roginsb Apr 27, 2012

In order to get users more engaged and actively logging into the Jive platform on a regular basis we are thinking of kicking off a "perfect attendance" competition where internal users with perfect attendance over a specified date range are rewarded. The query below is used to generate the report example below which allows us to see who has attendance (# of logins) above 90% of the working days (weekends excluded). In addition for all the users we can see what number/proportion of users who logged in 0-20% of the time 20-40% of the time 40-60% of the time etc... Users with > 100% logged in over weekends, that's dedication!

 

loginactivity.PNG

 

SELECT
    (u.firstname || ' ' || u.lastname) AS name,
    COUNT(d.begin_ts)                  AS LoginCount,
    (
        SELECT
            COUNT( *)
        FROM
            (
                SELECT
                    ROWNUM RNUM
                FROM
                    ALL_OBJECTS
                WHERE
                    ROWNUM <= TO_DATE(:endDate) - TO_DATE(:startDate)+1
            )
        WHERE
            TO_CHAR(TO_DATE(:startDate) + RNUM - 1, 'DY') NOT IN('SAT', 'SUN')
    )                                          AS DayCount
FROM
    JIVEANALYTICSUSER.JIVEDW_activity_agg_day a
INNER JOIN JIVEANALYTICSUSER.JIVEDW_DAY d
ON
    d.day_id = a.day_id
INNER JOIN JIVEANALYTICSUSER.JIVEDW_USER u
ON
    u.user_id = a.user_id
WHERE
    d.BEGIN_TS BETWEEN :startDate AND :endDate
AND TO_CHAR(begin_ts,'DY') NOT IN ('SAT','SUN')
AND a.activity_type IN(160)
GROUP BY
    u.firstname,
    u.lastname
ORDER BY
    LoginCount DESC

The query below is used to understand user activity levels based on how active users are in regards to their level of activity above or below the average amount of activity for all users. The output in report form looks something like the example below. This information can be used to gauge not only the number of active users over time but the proportions of users by their activity tier. Additionally, with date parameters you can modify the date range to see how these proportions change over time. Additionally, the user names and email are loaded so that users that fall in each tier can be contacted (ie. requests for feedback on how to make the platform more engaging, interesting, useful etc... for users who have very low levels of activity)

UserTiers.PNG

 

 

 

 

 

 

 

 

 

 

 

WITH
    ACTIVITY AS
    (
        SELECT
            day.begin_ts,
            COUNT(DISTINCT u.user_id) AS total_users,
            SUM(activity_count)       AS total_activity
        FROM
            JIVEANALYTICSUSER.JIVEDW_ACTIVITY_AGG_DAY a
        INNER JOIN JIVEANALYTICSUSER.JIVEDW_USER u
        ON
            a.user_id = u.user_id
        INNER JOIN JIVEANALYTICSUSER.JIVEDW_USERPROFILE up
        ON
            u.user_id = up.user_id
        INNER JOIN JIVEANALYTICSUSER.JIVEDW_DAY DAY
        ON
            a.day_id = day.day_id
        WHERE
            u.userenabled = 1
        AND day.begin_ts BETWEEN :startDate AND :endDate
        /*Exclude Weekends*/
        AND TO_CHAR(day.begin_ts,'d') NOT IN(1,7)
        GROUP BY
            day.begin_ts
        ORDER BY
            day.begin_ts DESC
    )
    ,
    AVGACTIVITY AS
    (
        SELECT
            AVG(total_users)    AS avg_users,
            AVG(total_activity) AS avg_activity
        FROM
            ACTIVITY
    )
/*SELECT avg_users, avg_activity FROM AVGACTIVITY*/
SELECT
    day.begin_ts,
    u.user_id,
    u.firstname,
    u.lastname,
    u.email,
    SUM(a.activity_count) AS total_activity,
    CASE
        WHEN Sum(a.activity_count) <= ((
            (
                SELECT
                    avg_activity
                FROM
                    AVGACTIVITY
            )
            /
            (
                SELECT
                    avg_users
                FROM
                    AVGACTIVITY
            )
            )*1)
        THEN '1-Light'
        WHEN Sum(a.activity_count) <= ((
            (
                SELECT
                    avg_activity
                FROM
                    AVGACTIVITY
            )
            /
            (
                SELECT
                    avg_users
                FROM
                    AVGACTIVITY
            )
            )*1.5)
        THEN '2-Moderate'
        WHEN Sum(a.activity_count) <= ((
            (
                SELECT
                    avg_activity
                FROM
                    AVGACTIVITY
            )
            /
            (
                SELECT
                    avg_users
                FROM
                    AVGACTIVITY
            )
            )*2)
        THEN '3-Heavy'
        ELSE '4-Super'
    END AS user_level
FROM
    JIVEANALYTICSUSER.JIVEDW_ACTIVITY_AGG_DAY a
INNER JOIN JIVEANALYTICSUSER.JIVEDW_USER u
ON
    a.user_id = u.user_id
INNER JOIN JIVEANALYTICSUSER.JIVEDW_DAY DAY
ON
    a.day_id = day.day_id
WHERE
    u.userenabled = 1
AND day.begin_ts BETWEEN :startDate AND :endDate
/*Exclude Weekends*/
AND TO_CHAR(day.begin_ts,'d') NOT IN(1,7)
GROUP BY
    u.firstname,
    u.lastname,
    u.email,
    u.user_id,
    day.begin_ts
ORDER BY
    total_activity DESC







 

The following query is used to calculate the scorecard average number of users per day and the average activity per user per day as seen in the example below. In addition the trend of activity for the average user activity level over time can be reported on.

useractivitytrend.PNG

 

SELECT
    day.begin_ts,
    COUNT(DISTINCT u.user_id) AS total_users,
    SUM(activity_count)       AS total_activity
FROM
    JIVEANALYTICSUSER.JIVEDW_ACTIVITY_AGG_DAY a
INNER JOIN JIVEANALYTICSUSER.JIVEDW_USER u
ON
    a.user_id = u.user_id
INNER JOIN JIVEANALYTICSUSER.JIVEDW_USERPROFILE up
ON
    u.user_id = up.user_id
INNER JOIN JIVEANALYTICSUSER.JIVEDW_DAY DAY
ON
    a.day_id = day.day_id
WHERE
    u.userenabled = 1
AND day.begin_ts BETWEEN :startDate AND :endDate
AND TO_CHAR(day.begin_ts,'d') NOT IN(1,7)
GROUP BY
    day.begin_ts
ORDER BY
    day.begin_ts DESC




roginsb

Query most 'liked' content

Posted by roginsb Apr 27, 2012

Here's a useful query with object type lookup and content title for the the most liked content. Liked content types include Threads, Questions, Documents, Blogposts, Bookmarks (internal and external)

SELECT acc.object_id,
       om.object_name AS object_type,
       o.name,
       SUM(score) AS total_likes
FROM JIVEANALYTICSUSER.JIVEDW_ACCLAIM acc
INNER JOIN JIVEANALYTICSUSER.JIVEDW_OBJECT o ON o.object_id = acc.object_id AND o.object_type=acc.object_type
INNER JOIN JIVEANALYTICSUSER.JIVEDW_OBJECT_META om ON o.object_type = om.object_type
WHERE acc.acclaim_type = 'like'
AND o.object_type IN(1,27,102,38,800,801)
GROUP BY acc.object_id, om.object_name, o.name
ORDER BY  total_likes DESC
roginsb

Query Search Terms Used

Posted by roginsb Apr 27, 2012

Below is an Oracle solution for querying the analytics database and returning a list of search terms used over a user specified time interval along with the counts. To achieve this the distinct terms are first put into a temp table using the with clause. Next each unique term used is listed along with an aggregated count of its use which is achieved by an inner join to the search terms table. The three activity types that are filtered in the where clause are representative of spotlight search, content search and user search. Enjoy.

 

WITH
    SEARCH_TERMS AS
    (
        SELECT DISTINCT
            metadata AS term
        FROM
            jivedw_activity_fact
        WHERE
            activity_type IN(170,180,190)
        AND metadata IS NOT NULL
        AND to_date(TO_CHAR(activity_ts, 'YYYY-MM-DD'), 'YYYY-MM-DD') BETWEEN :startDate AND :endDate
    )
SELECT
    st.term,
    COUNT(metadata) AS total
FROM
    jivedw_activity_fact f
INNER JOIN SEARCH_TERMS st
ON
    st.term = f.metadata
GROUP BY
    st.term
ORDER BY
    total DESC


Filter Blog

By date:
By tag: