Simple group by type query - how to do with insights

I have the fields ‘site_id’ and ‘device’. Both strings. device has 3 possible values, site_id has several hundreds.

I want to query percentage or total numbers of devices per site_id something like site1-phone -> xxxx , site1-desktop -> yyy, site1-tablet -> zzzz, site2 -phone ->ttt

Is this possible?

Provided these custom attributes are being reported to Insights this should be possible.

I am not entirely clear what you mean by the total number of devices per site. I think you want to know how many of transactions or pageviews for each site were done by desktops, tablets or phones. I am also assuming this has multiple sites and devices all in a single app. If I am wrong about these assumptions please let us know.

To get the total pageviews in a time window for an app you can do this:

SELECT count(*) FROM PageView WHERE appName='My Application' SINCE 1 hour ago

You can then get the counts of each device for each site with:

SELECT count(*) FROM PageView WHERE appName='My Application' AND device='desktop' AND site_id='1' SINCE 1 hour ago

SELECT count(*) FROM PageView WHERE appName='My Application' AND device='phone' AND site_id='1' SINCE 1 hour ago

SELECT count(*) FROM PageView WHERE appName='My Application' AND device='tablet' AND site_id='1' SINCE 1 hour ago

This will give you the pageview counts of each device type for the selected site_id in the time window. This should give you the required information to make the percentages.

Again, please correct any false assumptions being made.

1 Like

Hi Luke,

As I wrote site_id has several 100s values so I will need to do several 1000s queries like this to get the data. I don’t think its the optimal solution.

  • Nir
1 Like

Simply put, there is no GROUP BY function in NRQL at this time. It is true that you must make many queries to get the data with the method I described. There is a way to automate these queries to get this data more quickly.

We have an API to perform Insights queries. You can find details about this on our docs site on the page Querying Your Data Remotely.

You can write a script that checks the total pageview count, then iterates through each combination of phone, tablet and desktop views for each site_id. This would allow you to get the 1000s of unique site_id/device_type pageview counts you require.

Hi Nir,

While we don’t have group by support in NRQL, we do have a FACET keyword which provides similar functionality. If you take advantage of faceting, I think you can get your answer in three queries, once for each device:

SELECT count(*) FROM PageView WHERE device = 'phone' FACET site_id LIMIT 200
SELECT count(*) FROM PageView WHERE device = 'tablet' FACET site_id LIMIT 200
SELECT count(*) FROM PageView WHERE device = 'desktop' FACET site_id LIMIT 200

Those three queries will each give you a table for the specified device type, listing the total count of PageViews for that device type broken out by site_id, with up to 200 different sites. You may need to adjust the limit value based on the total number of sites you have.

Hope that helps you get the answers you need out of Insights!


Thanks ! that was very helpful

  • Nir

Is there any plan to allow to FACET by more than one attribute? Along this theme I was trying to get distinct combinations of Country and Account. I wrote a NRQL with a FACET by CountryCode and then uniques( on our custom attribute AccountName, but it just returns the data in JSON. It’d be nice if Insights had a default “Grid Table” if possible to show the JSON in table format. Then we slap it on a Dashboard in a usable fashion. I know I’m jumping off on a tangent but it’d also be nice to have a Bar Graph option for any data structure that offers a Pie Chart. Sometimes this gives a better visualization of the nominal value for each data point vs. just purely being as a % of the whole.

@shaney I can’t say if we’re going to be supporting FACET for multiple attributes, but I’ll go ahead and submit a request to our Product team. You have some other good suggestions in here as well: JSON responses in table format and bar graphs where pie charts are normally available. I’ll make sure Product is aware of these, too. Thanks for help us improve Insights!

1 Like

I moved a post to a new topic: Facet over more than one attribute

A post was split to a new topic: How can I get a count of usage of all my routes?

Just an update on this older thread. Per our documentation, you can now facet on up to 5 attributes. More here:

1 Like