⇦ Home / Tech Posts

Common NewRelic RUM queries for web performance optimization

Web Performance

16th Sep, 2024

Every time I have to sift through NewRelic RUM data when optimizing a website’s speed, I have to go through past notes, reports and messages. I want to use this blog post to collect the most common queries & other information I use as a starting point for RUM data analysis.

Major NewRelic RUM tables and the data they contain

NewRelic RUM captures the data in the following tables:

PageView

This table contains various Navigation Timing API metrics at page-load. This means it does not capture SPA route changes and does not capture the newer core web vital metrics. A single row in this table represents a single page-load.

With the availability of PageViewTiming, I mostly find the data in this table redundant except for the following causes:

to track speed specific to only full page loads.
to classify the captured speed data basis a custom attribute (that may be set via the newrelic.setCustomAttribute call)

PageViewTiming

This table contains metrics like First Contentful Paint (FCP), Largest Contentful Paint (LCP), Interation to Next Paint (INP), Cumulative Layout Shift (CLS). Individual metrics are captured in separate rows. So, a single user visit may translate into multiple rows. These metrics are also captured beyond the page-load so the core web vitals that aren’t page-load specific (eg: Interaction to Next Paint (INP)) are captured accurately. The table also captures Network Information API metrics when capturing the Largest Contentful Paint event.

Querying the data from this table is useful when optimizing the core web vitals.

AjaxRequest

Whenever any XHR / Ajax request is made by the browser, metrics for the request like its response status, response body size, response duration are captured within this table. Additional metrics like javascript execution time and time taken by the browser-side callbacks for the request are also captured.

Querying the data from this table is useful when optimizing rich interactivity conversion events (eg: Add to cart).

BrowserInteraction

This table contains the total time taken, the total number of XHR requests and the amount of time spent in JavaScript execution on the browser for a SPA route change. It also contains metrics related to initial page load. But, given the availability of the same from PageView and PageViewTiming, I find them redundant.

The data in this table is useful when optimizing SPA route transitions.

Additional Information: Each of the four tables above contain a column session that can be used to understand the metrics across a single user-session that may involve visiting one or more pages.

Queries to understand the nature of the traffic

Desktop vs Mobile page-loads (for SPAs, this does not translate into # of page views):

select 
  count(*) 
from PageView facet deviceType 
SINCE last week

Browser-wise page-loads:

select 
  count(*) 
from PageView facet userAgentName 
since last week

Country-wise page-loads:

select 
  count(*) 
from PageView facet countryCode 
since last week

Median network round-trip time:

select 
  median(networkRtt) 
from PageViewTiming where 
  largestContentfulPaint is not null
since last week

Wifi vs cellular network users:

select 
  count(*) 
from PageViewTiming where 
  largestContentfulPaint is not null 
facet networkType 
since last week

Queries to understand the page load / route change speed

LCP, start-render and server-response speed for initial page load and route change:

select 
  percentile(largestContentfulPaint, 75) as 'LCP',
  percentile(firstPaint, 75) AS 'Start Render', 
  percentile(timeToFirstByte, 75) AS 'Server Response Speed' 
from PageViewTiming  
  facet deviceType
since last week

LCP, start-render and server-response speed compared across page-types:

select 
  percentile(largestContentfulPaint, 75) as 'LCP',
  percentile(firstPaint, 75) AS 'Start Render', 
  percentile(timeToFirstByte, 75) AS 'Server Response Speed' 
from PageViewTiming where 
  deviceType = 'Mobile' 
facet cases(
  where pageUrl like 'https://<baseurl>/<category_1>%' as 'Blog', 
  where pageUrl like 'https://<baseurl>/<category_2>%' as 'Listing', 
  where (pageUrl like 'https://<baseurl>/<category_3_1>%' or 
    pageUrl like 'https://<baseurl>/<category_3_2>%') as 'Detail')
since last week

For SPAs, determine the time it takes for route change (say from listing page to detail page) and what part of that time is consumed by execution of JavaScript (not loading of JavaScript files that also may happen during the route change):

select 
  percentile(duration, 75), 
  percentile(jsDuration, 75) 
from BrowserInteraction where 
  previousUrl like 'https://<baseurl>/<listing>/%' and 
  targetUrl like 'https://<baseurl>/<product_detail>/%' and
  deviceType = 'Mobile' and
  category = 'Route change'
since last week

Identify the time taken by an API request (and its callbacks) to finish during on-page interaction (eg: clicking an Add to wishlist button):

select 
  percentile(timeToLoadEventStart, 75) as 'API response time', 
  percentile(timeToLastCallbackEnd, 75) as 'API + API callbacks complete', 
  percentile(timeToSettle, 75) as 'API + All callbacks complete',  
  percentile(jsDuration, 75) as 'JS execution time' 
from AjaxRequest where 
  pageUrl like 'https://<baseurl>/<product_detail>/%' and 
  requestUrl like '<baseurl>/<conversion_api>' and
  deviceType = 'Mobile'
since last week

Query to analyze the LCP core web vital

Classify a certain page’s LCP by the element that was considered as an LCP element:

SELECT 
  percentile(largestContentfulPaint, 75), 
  count(*) 
from PageViewTiming 
  where largestContentfulPaint is not null and 
  pageUrl like 'https://<baseurl>/<category>/%' and 
  timingName = 'largestContentfulPaint' 
  and deviceType = 'Mobile' 
facet elementId 
order by count(*) 
since 7 days ago

Queries to analyze the INP core web vital

Classify a certain page’s INP by the page’s loadState:

SELECT 
  percentile(interactionToNextPaint, 75), 
  count(*) 
from PageViewTiming 
  where interactionToNextPaint is not null and 
  pageUrl like 'https://<baseurl>/<category>/%' and 
  timingName = 'interactionToNextPaint'
  and deviceType = 'Mobile' 
facet loadState 
order by count(*) 
since 7 days ago

Classify a certain page’s INP by the target element that was interacted with by the user resulting in the INP event:

SELECT 
  percentile(interactionToNextPaint, 75), 
  count(*) 
from PageViewTiming 
  where interactionToNextPaint is not null and 
  pageUrl like 'https://<baseurl>/<category>/%' and 
  timingName = 'interactionToNextPaint'
  and deviceType = 'Mobile' 
facet eventTarget 
order by count(*) 
since 7 days ago

Query to analyze the CLS core web vital

Classify a certain page’s CLS by the element causing the largest shift:

SELECT 
  percentile(cumulativeLayoutShift, 75), 
  count(*) 
from PageViewTiming 
  where cumulativeLayoutShift is not null and 
  pageUrl like 'https://<baseurl>/<category>/%' and 
  timingName = 'cumulativeLayoutShift'
  and deviceType = 'Mobile' 
facet largestShiftTarget 
order by count(*) 
since 7 days ago

Query to classify the data based on custom attribute

A custom attribute (eg - version of a page, whether a page is served from cache or no, etc) can be sent to NewRelic RUM via the call newrelic.setCustomAttribute('cache', cacheHit);
This data gets stored in a column by the name of the custom attribute within the PageView table and can be used to classify the data as following:

select 
  percentile(backendDuration, 75) 
from PageView 
  facet cache
since last week