If you're looking for a way to quickly calculate percentiles in your data, BigQuery offers convenient functions to do just that. One of these is PERCENTILE_DISC, which allows you to calculate a discrete percentile for a given dataset. Let’s explore how to use this function with GA4 (Google Analytics 4) data.
PERCENTILE_DISC computes a specific value from the dataset that corresponds to the specified percentile. This is especially useful when you want to understand what value defines, for example, the 75th percentile of your metrics, such as session duration or conversion counts.
Suppose you have a table with user event data from your website, and you want to find out what session duration corresponds to the 90th percentile for a particular day. Using PERCENTILE_DISC, you can easily retrieve this value.
The attached image shows just how simple and convenient it is to use this function in BigQuery. The syntax is intuitive, making it accessible even for those who have never worked with SQL before.
Calculating percentiles can be helpful in various scenarios: for instance, in analyzing user behavior to understand what session duration represents the "upper" value for your audience; in identifying anomalies by pinpointing users with very high or low session durations, which may indicate issues with your content or UX; and in marketing optimization, knowing how much time users spend on the site up to the 90th percentile can help you tailor your campaigns with target metrics.
If you work with GA4 to BigQuery exports, be sure to check out my SQL cheat sheet.
