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.
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.
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.
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.
- 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.
Not Cloud. Hosted. Our data is in the cloud, though.
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
If you already have distinct security groups it's relatively easy to segment your data by that criteria. The membership is stored in the jivegroupuser table here is a quick query to find all the users of a security group:
select juser.userid, juser.username, jgroup.name from jivegroupuser guser left join jivegroup jgroup on jgroup.groupid = guser.groupid left join jiveuser juser on juser.userid = guser.userid where jgroup.name like '%ESP%
I haven't found a related Analytics table with the same info so i just DBlink and copy the table over when I need it.
At this stage, it's best to admit my ignorance. I have no idea how to get to the tables you're talking about. I know people access the data in different ways, such as the REST API, but I've never sat down with someone who's shown me what they are or how to access them.
BTW, our instance is Jive-X Cloud.
Could you please provide some guidance for how to gain such access? Even if we decide to not go that route, it will be useful to determine the complexity of the solution.
Thanks! - Josh
1 person found this helpful
Luckily Jive has gotten a lot better about documenting this stuff in recent history.
Here is a short list of 'light' reading to get you started:
- Jive Analytics and Reporting Solutions
- How To: Working With Database Dumps
- Analytics DB Data Model
- Measurement Resources
- Advanced Customer Measurement
Also feel free to PM me if you have database or query questions.