2 Replies Latest reply on Jun 6, 2014 7:54 AM by aliceyang

    Exporting users based on profile content

    aliceyang

      Hi,

       

      We're new to Jive and we're getting into the metrics using the databases. We've customized our profiles pretty extensively, and want to be able to export and use this information. I can find all the profile boxes under jivedw_profilefield, but am unsure what other tables there are that I can join this with in order make this more useful. Does anyone have any experience with this or tips? Thank you

        • Re: Exporting users based on profile content

          The user profile table has entries for each user, field combo and can be reported on by the column: value. Here is an example query that uses this and the activity_agg_day table to pull views by department (a profile field) for the month of May - hope it helps.

           

          SELECT up.value, sum(activity_count)

            FROM jivedw_day d

             INNER JOIN jivedw_activity_agg_day aad ON (d.day_id = aad.day_id)

             INNER JOIN jivedw_userprofile up ON (aad.user_id=up.user_id)

             INNER JOIN jivedw_profilefield pf ON (up.field_id=pf.field_id)

             INNER JOIN jivedw_activity_meta am ON (am.activity_type = aad.activity_type)

          WHERE 1=1

            AND d.year = 2014

            AND d.month_of_year = 5

            AND pf.name = 'Department'

            AND am.activity_name = 'View'

          GROUP BY up.value