Analytics Query Total Page #

[CommunityUser]

Hello,

I have been testing out our analytics feature and am having a problem. I’m trying to fetch a list of the top 20 EUM applications based on the total # of page requests outside of the U.S. for a duration of one year. I have figured out how to narrow the search to only include geocountries but the numbers seemed incorrect. So, I simply tried to query one application and try to identify the total # of page requests for all locations to see if it matched the Page Requests box listed in the AppDy UI. The numbers did not match, as the UI reports 0.91m and the query only counts 4731. I checked my data metrics retention and it is set to 365 days. Does anyone have any advice on why the numbers would be different?

The logic behind the query was that I know every time someone hits a website; the entry should be logged under “browser_records”. And every time they would click on a different page of that site, a new entry should be created. So, I figured I could just sum up all of the entries where the appkey was the same, by using their modified groupby where it automatically buckets values to obtain the total page request #. It seems that the table is not retaining all the entries.

Thought it was due to return limits but its only returning 20 items, so technically the query should be taking place on the events servers.

Any advice is appreciated. 

[Brian_Wheeldon]

Hi Wesley,

Please keep in mind that the Analytic Events have a short shelf life. They are normally purged after two weeks, so I would expect your Analytics Query to return the same result whether 2 weeks, or 2 months, or one year is queried.

Rather than use an Analytics Query, your best bet is to use the metrics visible in the Metric Browser to do this calculation. These metrics are calculated from the Events every minute, and are aggregated and persisted for a year by default.

An example Metric Path is:

End User Experience|Geo|United Kingdom|Page Requests per Minute

Use the REST API to fetch the "Sum" of this metric over the one year period for your calculation.

Regards,

[Michael_Perlst1]

Great point @Brian.Wheeldon !

Assuming event retention is taken into consideration you might try an ADQL query like below @Anonymous 

SELECT appkey,
geocountry AS "GEO",
count(*) AS "CT" 
FROM browser_records
WHERE
geocountry != "United States"
ORDER BY appkey DESC, GEO DESC, CT DESC
LIMIT 1000, 1000, 1000