Hi,
With this request url I can get the total unique user count:
I used this page to get that: https://dev.applicationinsights.io/apiexplorer/metrics?metricId=users%2Fcount×pan=P7D&aggregati...
But now I want to get the Returning users count, so I want to know how many of the unique users are returning users. How do I query that?
I have been looking through the azure application insights blades and metrics to get that, the only place I could find it was under Usage => More => New, Returning and Churrned Users. It seems to be querying what I want:
So I want to get this query for myself so I can get the Returning users count on a daily bases, via the api.
When clicking on Edit at the top of the page, I can edit each individual item on the page, so I can now also edit the graph. When I do that, I can see the query, which is as follows:
let timeRange = {TimeRange};
let monthDefinition = {Metric};
let hlls = union customEvents, pageViews
| where timestamp >= startofmonth(now() - timeRange - 2 * monthDefinition)
| where name in ({Activities}) or '*' in ({Activities})
{OtherFilters}
| summarize Hlls = hll(user_Id) by bin(timestamp, 1d)
| project DaysToMerge = timestamp, Hlls;
let churnSeriesWithHllsToInclude = materialize(range d from 0d to timeRange step 1d
| extend Day = startofday(now() - d)
| extend R = range(0d, monthDefinition - 1d, 1d)
| mvexpand R
| extend ThisMonth = Day - totimespan(R)
| extend LastMonth = Day - monthDefinition - totimespan(R)
| project Day, ThisMonth, LastMonth);
churnSeriesWithHllsToInclude
| extend DaysToMerge = ThisMonth
| join kind= inner (hlls) on DaysToMerge
| project Day, ThisMonthHlls = Hlls
| union (
churnSeriesWithHllsToInclude
| extend DaysToMerge = LastMonth
| join kind= inner (hlls) on DaysToMerge
| project Day, LastMonthHlls = Hlls)
| summarize ThisMonth = hll_merge(ThisMonthHlls), LastMonth = hll_merge(LastMonthHlls) by Day
| evaluate dcount_intersect(ThisMonth, LastMonth)
| extend NewUsers = s0 - s1
| extend ChurnedUsers = -1 * (dcount_hll(LastMonth) - s1) // Last Months Users - Returning Users
| project Day, ["Active Users"] = s1 + NewUsers, ["Returning Users"] = s1, ["Lost Users"] = ChurnedUsers, ["New Users"] = NewUsers
Awesome, I thought, but unfortunately it does not work correctly, there is even a button to open the query in the Query Logs View.
But I don't really get how the query is build to get Returning Users. According to the query this should be defiend by "s1" which I cannot see to be defined anywhere. Also, I am a noob to this query language, so I would highly appreciate if someone could tell me how to adjust this query so that it only provides the Returning users count using a start and end date.