There are two keys on which you can filter BQL queries:filters and post_filters. Both can be used in the same query, but they have different behaviors:

• filters are used to filter both dimensions and metrics.
• post_filters are used to filter on metrics aggregated by dimensions.

Determining the type of filter to use in your query depends on the content and your goal for the data. This page provides an example of filtering the same data with filters and then with post_filters to demonstrate their functionality.

Using Post_filters

To understand post_filters, let's first look at a sample of filtering the following RealKeywords data with only filters.

📘

If you want to understand all the details!

Metrics are stored in Botify databases by (url, keyword, device, country) tuple, as they are in the Google Search Console data model. This means the above data table is already aggregated by "intermediate" dimensions, as opposed to "final" dimensions (shown further on this page).

Read about the Google Search Console data model in this botify-labs blog post.

The following query finds the total number of clicks by country for keywords containing botify:

{
  "collections": ["search_console"],
  "period": ["2023-01-01", "2023-01-31"],
  "dimensions": ["country"],
  "metrics": ["search_console.period_0.count_clicks"],
  "filters": {
    "and": [
      {
        "field": "keyword",
        "predicate": "contains",
        "value": "botify"
      }
    ]
  }
}

Clicks are aggregated by sum on the country dimension in the result:

Filtering with Additional filters

If we add an additional filters on clicks > 10, our BQL becomes:

{
  "collections": ["search_console"],
  "period": ["2023-01-01", "2023-01-31"],
  "dimensions": ["country"],
  "metrics": ["search_console.period_0.count_clicks"],
  "filters": {
    "and": [
      {
        "field": "keyword",
        "predicate": "contains",
        "value": "botify"
      },
      {
        "field": "search_console.period_0.count_clicks",
        "predicate": "gt",
        "value": 10
      }
    ]
  }
}

With this request, data is filtered first withclicks > 10:

and then aggregated by sum on the selected dimension: countryin the result:

Notice the results filter out Italy and some clicks in France with low-traffic keywords.

Filtering the Metric with post_filters

Let's try the same thing with post_filters:

{
  "collections": ["search_console"],
  "period": ["2023-01-01", "2023-01-31"],
  "dimensions": ["country"],
  "metrics": ["search_console.period_0.count_clicks"],
  "filters": {
    "and": [
      {
        "field": "keyword",
        "predicate": "contains",
        "value": "botify"
      }
    ]
  },
  "post_filters": {
    "field": "search_console.period_0.count_clicks",
    "predicate": "gt",
    "value": 10
  }
}

In this case, the data is aggregated first on final dimensions (country in this example):

and then filtered on the resulting metrics to get:

for a final result of: