How do I avoid hitting the "50000 rows in the SQL query" limit when creating custom metrics?

Hello @...,

There is a data threshold of 50,000 rows on the underlying SQL in Explore. Explore is only able to query 50,000 rows of data at a time and displays the Network Error if that limit is exceeded. Bear in mind that this limit doesn't necessarily translate to 50,000 rows on the query itself, it also depends on the number of columns included, type of data calculated, etc.

Using standard calculated metrics in queries will add to the number of rows that need to be queried in SQL because it adds to the complexity of the queries.

To resolve this issue, narrow the query scope to prevent the exceeding of the data threshold. You can narrow the query scope by adjusting existing date and data filters or adding new ones. In Explore, query filters are applied to the data before calculated metrics/attributes are applied, so filtering down your data before it is sent to the calculation engine is the best way to prevent these errors. Below are some options for adding filters to your query to make it perform better

Adding a redundant data filter

In the example above, you have a calculated metric to only count specific tickets. You can add data filters to narrow down the data before it gets sent to the calculation engine.

For example, adding a query filter on the [Changes - Field name] attribute set to "ACD Anzahl Outboundcalls" in your query above should make this query run more efficiently.

Adjusting an existing date filter

1. Select an existing date filter on your query (for example, Ticket created - Date).
2. Make the defined date range smaller (for example Yesterday) to reduce the data computation required.

Adding a new date filter

Follow the steps in Editing dates and date ranges to add a new date filter with a specific date range.
If the Network error message persists, make the date range smaller.

Hello  @...,

Thanks for the help!

I am very aware of that 50000 row limit, since I hit it pretty much every time when I create a query using custom metrics in the updates dataset. Usually I can solve it with some additional time filters but in this case it didn't help, because if I need to get the data for a year, I don't really want to get it for each day individually and then add it up later... ;) 

I was stuck because I expected the line "[Changes - Field name]="ACD Anzahl Outboundcalls"" to already limit the query to those cases. An additional query filter for [Changes - Field name]="ACD Anzahl Outboundcalls" actually solved the problem :)