Why: my client is currently using some statistics from the game into a bigger sheet of their own. They're using the daily email values and the Average duration from Data > Dashboard > Average duration. So each day they'll get the values from the email and then go to admin panel and hover on the new day to get the Average Duration value.
I am trying to recreate the Average duration with a Kusto query so it could be either automated or they could run it manually in the explorer and get the data more easily. So if there's an easier way to do it without a query (downloadable Average Duration) please tell me about it.
['events.all']
| where FullName_Name == "client_focus_change" and Timestamp > startofday(now(-10d)) // get all events of type focus change in the past 10 days
| extend Day = format_datetime(Timestamp, 'yyyy-MM-dd') // get the date from the event's timestamp to be used for grouping
| extend parsed = parse_json(tostring(EventData.PayloadJSON)) // get the event's data in useful format
| extend var2 = toreal(parsed.FocusStateDuration) // get the session duration in seconds
| where var2 > 0 // remove any 0 sessions
| summarize smrz = avg(var2) by Day // group the results by Day and fill the smrz field with the average value of this group's sessions lengths
| extend avgSessionDuration = format_datetime(todatetime(smrz * 1s), 'mm:ss') // format the result from number to time mm:ss
| project Day, avgSessionDuration, smrz // show only columns that are of interest
| order by Day desc // order by date descending
The query returns results that seem fine, but differ from the Admin Panel's data by a random percent in a +/- 10% range.
In this comment how-is-the-average-screen-time-calculated.html there's a hint PlayFab Admin Panel might be grouping within 10 minute ranges.
Looking for advice :)
