9 Replies Latest reply on Jan 8, 2016 8:50 AM by Caton Guilbault

    Need help with user activity segmentation by custom profile fields

    tmaurer

      We want to be able to report on specific user activities, and then segment that activity by specific criteria. These criteria happen to be custom profile fields. Here is the dilemma:

      1. We can use the CMR profile report to help with this since it includes all of the custom fields. EXCEPT that the CMR profile report doesn't contain any information for deactivated users. So once someone leaves the company, we can no longer match any of their data up with them and thus our reports will have holes.
      2. We can use the DES user reports, which contain ALL activities. EXCEPT that the user reports don't contain information from any custom fields.

       

      Has anyone found a way to work around this? I see the first limitation as especially difficult when trying to gather historic data.

        • Re: Need help with user activity segmentation by custom profile fields
          Caton Guilbault

          Hey Tracy,

           

          I'm assuming your on Cloud right? If you have the DB's this is possible but in cloud I think your currently SOL. Might be a good cross post to Ideas for Jive.

            • Re: Need help with user activity segmentation by custom profile fields

              Hey Tracy,

               

              While this information is not available in the CSV, you can use the API to get the custom fields downloaded. The information is captured, but its not part of the CSV download and you will have to pull it using the API.

                • Re: Need help with user activity segmentation by custom profile fields
                  tmaurer

                  I was hoping perhaps someone had examples of how they had accomplished something similar. At this point, I think we are going to try and use the DB. We've scheduled a weekly download of that, and will be creating reports from the data.

                    • Re: Need help with user activity segmentation by custom profile fields
                      Caton Guilbault

                      Hey Tracy,

                       

                      I did some work like this recently with Blog views and segmenting the intended audience by custom profile fields synced from Active Directory via SAML.

                       

                      Tables:

                      jivedw_userprofile - were the profile values are stored

                      jivedw_profilefield - were the values for each field are defined

                       

                      First something to keep an eye out for, If the values for the profile fields can change it might be hard to find out when they changed. In our implementation since a script was initiating the change that activity wasn't captured in the Analytics DB but the update value(s) were. This ended up creating multiple values for each users filed_id's in the jivedw_userprofile table. Since the jivedw_userprofile doesn't have a timestamp I couldn't find an easy way to identify which value was current. Hopefully you wont have that problem but if you do I have a band-aid of sorts.

                       

                      Due to the large number of users in my instance and subsequent huge volume of activity data they generate I split this process into two pieces to keep my server from melting into a puddle.

                       

                      1. Separate the wheat from the chaff - Isolate the user group I care about to limit the total activity rows eventually returned

                       

                          Query; Drops and recreated a table with all the user details I want to segment by with some filtering to isolate the users I want

                      drop table care_users;
                      CREATE TABLE care_users AS
                      SELECT
                          jivedw_user.user_id,
                          jivedw_user.username,
                          replace(jivedw_user.firstname,',','') as firstname,
                          replace(jivedw_user.lastname,',','') as lastname,
                          jivedw_user.creation_ts, 
                          jivedw_user.modification_ts, 
                          replace(userprof1.value,',','') as JobTitle,
                          replace(userprof2.value,',','') as Department,
                          replace(userprof3.value,',','') as Location,
                      
                          CASE (userenabled)
                              When 1 Then 'Y'
                              Else 'N'
                          End as Enabled
                      
                      FROM
                          jivedw_user
                          left Join jivedw_userprofile userprof1 on jivedw_userprofile.userid = jivedw_user.user_id
                          left join jivedw_userprofile userprof2 on userprof2.userid = jivedw_user.user_id
                          left join jivedw_userprofile userprof3 on userprof3.userid = jivedw_user.user_id
                      
                      WHERE 
                          jivedw_user.userenabled = 1 -- Constrains to enabled users
                      AND    jivedw_user.modification_ts >=  current_date - 90 -- Restricts to users that logged-in within last 90 days revolving
                      AND    userprof1.fieldid = 1 -- Constrains first profile value logic to Job Title field (1) 
                      AND    userprof1.value NOT iLIKE ALL(ARRAY['%Analyst%','%Quality%','%Assistant%',
                                                  '%Business%','%Campaign%','%Consultant%','%Owner%','%Coordinator%',
                                                  '%Designer%','%Developer%','%Director%','%Emerging%',
                                                  '%IT%','%Manager%','%Sales%','%Security%','%Statistician%','%Test%',
                                                  '%Trainer%','%VP%','%Content%']) -- List Job Titles we want excluded for Care 
                      AND    userprof2.fieldid = 2 -- Constrains second profile value logic to Department field (2)
                      AND    userprof2.value iLIKE ANY(ARRAY['%Customer Care%','%Customer Service%']) -- List of Departments we want included for Care 
                      AND    userprof3.fieldid = 5002 -- Constrains third profile value logic to Location 
                      
                      
                      ORDER BY user_id ASC
                      

                       

                       

                           2. Pluck the needles out of the haystack - Isolate the activities I want to report on for the users I care about

                       

                           Query: use CREATE instead of INSERT for the first creation and I recommend picking a hard cut off date for the first where then run this weekly to keep the new table updated

                      Insert Into care_blogviews 
                      
                      select 
                          jivedw_day.begin_ts, 
                          jivedw_activity_fact.activity_ts,
                          jivedw_object.object_id, 
                          jivedw_activity_fact.user_id, 
                          count(jivedw_activity_fact.user_id) as view_count
                          
                      from jivedw_activity_fact
                      
                      join jivedw_object on jivedw_object.dw_object_id = jivedw_activity_fact.direct_dw_object_id
                      left join jivedw_day on jivedw_day.day_id = jivedw_activity_fact.day_id
                      left join care_users on  care_users.user_id = jivedw_activity_fact.user_id
                      
                      where   jivedw_activity_fact.activity_ts > (select max(activity_ts) from care_blogviews)and 
                          jivedw_activity_fact.activity_type = 10 and 
                          jivedw_object.object_type = 38 and 
                          care_users.user_id is not null 
                          
                      group by 
                          jivedw_day.begin_ts, 
                          jivedw_activity_fact.activity_ts, 
                          jivedw_activity_fact.user_id, 
                          jivedw_object.object_id
                          
                      order by jivedw_activity_fact.activity_ts asc;
                      

                       

                      Once I have this care_blogviews table I combine it with other blog data to provide all the relevant details for each post including a constrained reach calculation for the intended audience of each blog.

                       

                      Hopefully this helps jump start your project. Let me know if you have any questions or want a peek at the final product.

                  • Re: Need help with user activity segmentation by custom profile fields
                    tmaurer

                    Not Cloud. Hosted. Our data is in the cloud, though.

                  • Re: Need help with user activity segmentation by custom profile fields
                    jkurutz

                    Hi Tracy,

                     

                    Have you implemented a solution for this need? We're interested in segmenting activity of customers vs. employees in our hybrid community. I'm considering adding an admin-controlled profile element to help make the distinction, but I don't really know what to do afterwards, especially with the out-of-the-box tools like the CMR. I've already got the employees placed in two permissions groups, and it would be nice to leverage that structure, thus avoiding the need to manually edit registrants' profiles.

                     

                    What do you think? What have you done so far, and do you think a similar approach could be used in our case?

                     

                    Thanks. - Josh