1 person found this helpful
I am not sure if you are hosted by Jive or hosting internally but some of the questions you are asking can be assembled using a bit of elbow grease and the transactional/application database. Currently I live in a bit of a hybrid world where I am using the BusinessObjects BI OnDemand solution for some data (user/community activity) but then working directly with the database for other areas (document/discussion/question reporting).
My origins for reporting came from an environment where our previous solution vendor didn't provide any sort of reporting/analytics module so I had to build my own from the ground up using only the database.
The following recommendations assume you define active as "posting/voting of, or on, threads/documents/polls/videos". It isn't an elegant solution but if you only had the transactional/application database and something like MS Access/Excel to do some data massaging afterwords it will get the job done.
The first phase is to get a table with all the necessary reporting elements on it. To do that:
- Extract the data w/ timestamps from the various content type tables (document, thread, question, vote/rating, comments, etc) keeping a format that offers you the necessary datapoints like objectID, userID, container and so on.
- Mash all the data together in to one table/sheet and then use some built-in formulas to calculate week of year and month (offshoots of the DATE function).
Here is how the reports would work then:
- number of active users per week/month - Pivot table with count of userIDs based on the weekofyear column you calculated above. Plus: You can calculate the same information by container as well.
- number of creators per week/month - Looking at documents and the root message in a thread, video creator, etc you can calculate against your week/month time interval.
- most active discussions - Depends on how you define active again, but you could use the above dataset to calculate most replied to threads during the time interval.
- most active spaces - Basically just swap out the data from the discussion column and replace with the column that identifies the container name (constrain to only container type 14 which is for Spaces).
- most active groups - Same as most active spaces, just change the constraint to 700 for Groups.
- number of new or updated documents - Using only the data from the documents, count how many documents had a modify date for the week/month or whatever the time interval.
As you can see, it does take a bit of work to make happen. The analytics module does address these areas fairly well and eliminates the need to get too deep in the weeds. Personally speaking, having access to both sources extends my capabilities to provide meaningful analysis to my business partners.
Hope this helps.
yes it does help and I appreciate the detailed response.
If your community is externally facing. (Meaning your not an internal community manager). Google Analytics is free and very robust.
we have an internal community that is hosted on premise so Google Analytics is unfortunately not an option.
but thanks any way.
Another option is cheap but painful and that's to download the entire '.dmp' database, crunch that with PostgreSQL tool (it needs an installer for the installer as I found out after a few goes) and then import the converted data into xls. We borrowed a report from another Jive user near to us that reported out all the key info for us. Big drawback is the size of the .dmp files which are gigabytes.
PostgreSQL (just some of the learning pain )
Thank you Russell.
Looking at the referenced document I see that you are talking about jivedw_xxxx tables. I don't see those in our installation. Will they become only active when you purchase the Analytics module?
You can do the same process but with the application/transactional database. The info I provided above is based on the PostgreSQL dmp files for the application database as Russell describes.
The analytics database just makes it a lot easier to pull the data since the data relationships are already pre-built vs. crunching them yourself.