How Reports in the Analytics Dashboard are Calculated

    This document captures the SQL behind the components of the admin dashbaord

    Cumulative Membership Trend & Cumulative Content Trend

    For cumulative membership we run this query:

    SELECT

        day.begin_ts,

        count(user_id)

    FROM

        jivedw_day day

    INNER JOIN

        jivedw_user u

        ON

        u.initlogin_ts BETWEEN day.begin_ts

       AND day.end_ts

    WHERE

        u.initlogin_ts IS NOT NULL

    GROUP BY

        day.begin_ts

    ORDER BY

        day.begin_ts DESC

     

    Each record in the result indicates the day and a count of the users who had logged in for the first time on that day.  In order to present a graph showing running total (day to date), the running totals are calculated in the application server by java code. However, there are techniques to do this on the database side.

     

    Initlogin is calculated in a prior step (but only once). It is calculated by looking in the activity fact table for the earliest timestamp for a LOGIN activity event.  It is performed in the UserInitLoginETL java class using this query:

     

    SELECT

        min(activity_ts),

        f.user_id

    FROM

        jivedw_activity_fact f

    INNER JOIN

        jivedw_user u

        ON

        u.user_id = f.user_id " + "

    WHERE

        u.initlogin_ts IS NULL

        AND activity_type = " + AnalyticsEventCode.LOG_IN.getId() + "

    GROUP BY

        f.user_id

     

    For customers using an SSO provider this LOGIN event will not be captured. We could therefore modify our this process to expand the search to include any type of view activity.

     

    For cumulative content we run this query:

     

    SELECT

        begin_ts,

        SUM(activity_count) as actsum

    FROM

        jivedw_activity_agg_day a

    INNER JOIN

        jivedw_day day

        ON

        a.day_id = day.day_id

    WHERE

        activity_type          = :activityType

        AND direct_object_type = :objectType

        AND day.begin_ts BETWEEN :beginTs

        AND :endTs

    GROUP BY

        day.begin_ts

     

    Each record in the result indicates the day and a count of all the activity for a particular activity code. In order to present a graph showing running totals (day to date), the running totals are calculated in the application server by java code. However, there are techniques to do this on the database side.

     

    Active Users and Contributors

     

    Active Users are determined by looking for any activity by a user. The count works this way:

     

    SELECT

        begin_ts,

        COUNT(DISTINCT user_id)

    FROM

        jivedw_activity_agg_day a

    INNER JOIN

        jivedw_dau day

        ON

        a.day_id = day.day_id

    WHERE

        day.begin_ts BETWEEN :beginTs

        AND :endTs

    GROUP BY

        day.begin_ts

     

    Contributors works the same way but only write activity is included.

     

    Top Container and Content Queries

     

    The calculation of top containers and top content is a two step process. The first step executes a query to aggregate the counts of activity for each container/content – the results of these queries are entered into a temporary table. The second step queries from this table using a different aggregate grouping, e.g. summing the top container by user or by activity.

     

    Here are the two queries that populate the temporary table:

     

    Top Container:

     

    INSERT

    INTO

        jivetmp_obj_cnt

        (

        SELECT

            id,

            cnt

        FROM

            (

            SELECT

                a.dw_container_id   as id,

                SUM(activity_count) as cnt

            FROM

                jivedw_activity_agg_day a

            INNER JOIN

                jivedw_day day

                ON

                day.day_id = a.day_id

            INNER JOIN

                jivedw_container c

                ON

                a.dw_container_id = c.dw_container_id

            WHERE

                day.begin_ts BETWEEN :beginTs

                AND :endTs " + "

                AND activity_type  = :activityType

                AND container_type = :containerType

            GROUP BY

                a.dw_container_id

            ORDER BY

                cnt DESC

            )

            sub)

     

    Top Content:

     

    INSERT

    INTO

        jivetmp_obj_cnt

        (

        SELECT

            id,

            cnt

        FROM

            (

            SELECT

                a.direct_dw_object_id as id,

                SUM(activity_count)   as cnt

            FROM

                jivedw_activity_agg_day a

            INNER JOIN

                jivedw_day day

                ON

                day.day_id = a.day_id

            INNER JOIN

                jivedw_object o

                ON

                a.direct_dw_object_id = o.dw_object_id

            WHERE

                day.begin_ts BETWEEN :beginTs

                AND :endTs " + "

                AND activity_type      = :activityType

                AND direct_object_type = :objectType

            GROUP BY

                a.direct_dw_object_id

            ORDER BY

                cnt DESC

            )

            sub )

     

    This temporary table can be queried to get different types of aggregate activity:

     

    E.g.  For top container by activity:

     

     

    SELECT

        id,

        SUM(activity_count)

    FROM

        jivedw_activity_agg_day a

    INNER JOIN

        jivetmp_obj_cnt tmp

        ON

        tmp.id = a.dw_container_id

    INNER JOIN

        jivedw_day day

        ON

        day.day_id = a.day_id

    WHERE

        day.begin_ts BETWEEN :beginTs

        AND :endTs

        AND activity_type = :activityType

    GROUP BY

        id

     

    For top content by activity:

     

    SELECT

        id,

        SUM(activity_count)

    FROM

        jivedw_activity_agg_day a

    INNER JOIN

        jivetmp_obj_cnt tmp

        ON

        tmp.id = a.direct_dw_object_id

    INNER JOIN

        jivedw_day day

        ON

        day.day_id = a.day_id

    WHERE

        day.begin_ts BETWEEN :beginTs

        AND :endTs

        AND activity_type = :activityType

    GROUP BY

        id

     

    For top content by user participation:

     

    SELECT

        id,

        COUNT(DISTINCT user_id)

    FROM

        jivedw_activity_agg_day a

    INNER JOIN

        jivetmp_obj_cnt tmp

        ON

        tmp.id = a.direct_dw_object_id

    INNER JOIN

        jivedw_day day

        ON

        day.day_id = a.day_id

    WHERE

        day.begin_ts BETWEEN :beginTs

        AND :endTs

        AND activity_type = :activityType

    GROUP BY

        id

     

    Rolling up activity to parent communities is not currently possible because we do not have the community hierarchy available.