2 Replies Latest reply on Jan 14, 2011 5:12 AM by thadams

    Report on user profile updates since Jan 1 (for example)

    thadams

      We would like to create a report on which users have updated their profile in the last week, month, day, etc.

      Does anyone have a query against the analytics DB to give us this information?

       

      Thanks

        • Re: Report on user profile updates since Jan 1 (for example)

          Hi Tracy,

           

          We don't really track individual profile field updates in Analytics, only updates to the user object, so it isn't possible to differentiate between profile updates and other types of updates to the user object. However, you can get this information from the application database using the lastprofileupdate column in jiveuser. A good example of this (Postgres specific):

           

          select count(*) from jiveuser where to_timestamp(lastprofileupdate/1000) > (current_date - 7);

           

          Hope that helps!

          1 person found this helpful
            • Re: Report on user profile updates since Jan 1 (for example)
              thadams

              Thanks Karl,  the problem with this is that any change to the user object causes the date change.  So, even automated changes via the sync change the date.   We want to give our users a "reward" if they manually updated their profile fields in January.

              For anyone that's wondering - here is the mysql version more or less:

              SELECT j.username, from_unixtime(j.lastProfileUpdate/1000) FROM clearspace.jiveUser j order by j.lastProfileUpdate desc;