Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

How to get average of latest attribute values

mobile
nrql
insights

#1

Hello all!

We are trying to record the average user preferences in the mobile app.
Is there a way to use average/count/percentage … with only the latest event sent by a user?

Problem is that the record event function records events per session, so if a user is using the app 5 times a day, his preferences are being counted 5 times more.

Example:
user1 -> notificationMinutes = 10
user2 -> notificationMinutes = 4

user1 logs in 5 times, user2 only 1 time -> average I get is 9 => ( 5*10 + 4 ) / 6 = 9 and it should have been 7 => (10+4) / 2 = 7

Queries we have tried
This one brings the latest values, but I don’t manage to put them together because of the FACET uuid and it’s not useful to see the value user per user
SELECT latest(notificationMinutes) FROM Mobile, CallInEvent FACET uuid SINCE '2017-12-05' WHERE appName='Prep-iOS' and name='userPreferences'

This one brings the average, but it’s a wrong one, taking into account all the sessions recorded (more than one time per user) and it’s not useful to see data that is not accurate
SELECT average(notificationMinutes) FROM CallInEvent SINCE this quarter WHERE appName='Prep-iOS' and name = 'userPreferences'

Insights dashboard:
https://insights.newrelic.com/accounts/1335477/dashboards/693309

Discussion/Advise
If this is not possible, how would you approach this problem then? How do you record static things in the app that don’t change per session, but just change from time to time?

Thanks a lot for your help :slight_smile:


#2

Hey Patricia!

Unfortunately, getting this value from a single NRQL query would involve multiple subqueries, which isn’t a supported feature in NRQL.

Fortunately, it may be possible to calculate and store the average of the latest notificationMinutes values using the Insights Query API. By breaking the process of calculating this value into a few queries, and processing the results locally, you could potentially create a custom Insights event to keep track of averageNotificationMinutes. As an example, the process to calculate the average value of all user’s reported notificationMinutes in the last quarter might look like:

  • Call the Insights Query API to request the latest reported notificationMinutes value for all users using the query: SELECT latest(notificationMinutes) FROM CallInEvent FACET uuid SINCE last quarter (this will return a JSON object, containing an array of all faceted values)

  • Calculate the sum of all notificationMinutes values reported by the above query. I will refer to this total sumNotificationMinutes.

  • Call the Insights Query API to request a count of all users who have reported notificationMinutes values. Example query: SELECT uniqueCount(uuid) FROM CallInEvent WHERE notificationMinutes IS NOT NULL SINCE last quarter. I will refer to this total as userCount.

  • Divide sumNotificationMinutes by userCount and save the resulting value. This value could be sent to New Relic using the Insights Insert API or as a custom event from the Mobile agent.

I hope that helps! Let me know if you’d like to go into more detail on any part of this process, I’d be happy to help.


#3

Unfortunately, NRQL is pretty limited when it comes to any kind of subquery or manipulating data after you’ve retrieved it. You can sometimes “jiggle the handle” a bit to get what you want… you might try the below and then verify that it gives you the data that you need but it will only be in raw JSON:

SELECT uniques(uuid),average(notificationMinutes) FROM Mobile, CallInEvent SINCE '2017-12-05' WHERE appName='Prep-iOS' and name='userPreferences'

I tested this with some of my own metrics and it did give me accurate data.


#4

Thanks for adding in your thoughts @Michael.Sleep, validating the data through the raw JSON isn’t the nicest solution but it certainly can help.