About Metric Filtering

Filtering metrics using BQL is a powerful tool, and can be used to obtain very specific metrics. Because it is very permissive and powerful, we need to be mindful of how it works, because sometimes the result you get is not always what you expect. For example, filters can be applied to metrics at various levels, from global filters to local filters that are applied to only one metric in your queries.

In the following parts, we will learn about metric filtering through a step-by-step guide that reproduces what is on the first three steps of the Analytics Overview funnel (Known Pages, Crawl & Render, and Index) in a single BQL query.

Building Analytics Overview's Funnel Requests

The Analytics Overview funnel gives insights about your website's visibility to search engines, what makes up for its current rank, and visits and revenue. Let's jump into BQL to learn how to build the first three steps.

Step 1: Pages Crawled by Botify

Our first step is to know how many pages were crawled by Botify. After selecting a crawl, we can easily retrieve this data through the field count_urls_crawl. Let's build the BQL request for this step:

{
    "collections": [
      "crawl.20230101"
    ],
    "query": {
      "dimensions": [],
      "metrics": [
        "crawl.20230101.count_urls_crawl"
      ]
    }
}

Now that we have our value, we have the first step of the funnel. Keep this request in mind because we'll build on it in the next steps.

Step 2: Known Pages Crawled by Google

The crawl collection provides access to URLs crawled by Googlebot, and we want to know the number of URLs crawled by Botify that are also crawled by Google. We can filter on the field crawl.YYYYMMDD.search_engines.google.crawls.count to learn if the URL was included in a recent Google crawl.

However, we still need to get our first metric unfiltered to keep the exact count of pages crawled by Botify. While we could do two separate queries for each metric, filtering one query and leaving the other unfiltered, getting all metrics in a single BQL query is more efficient. This is where we introduce our first metric-level filter to the query. This filter only applies to the metric where it's defined, enabling the fetching and filtering of multiple metrics in a single query instead of one query per metric.

{
    "collections": [
      "crawl.20230101"
    ],
    "query": {
      "dimensions": [],
      "metrics": [
        "crawl.20230101.count_urls_crawl",
        {
          "field": "crawl.20230101.count_urls_crawl",
          "filters": {
              "field": "crawl.20230101.search_engines.google.crawls.count",
              "predicate": "gt",
              "value": 0
          }
        }
      ]
    }
}

Step 3: Indexable Pages Crawled by Google

This step of the funnel fetches indexable pages crawled by Google. Since we already know what pages are crawled by Google as a result of Step 2, we can combine this filter with another one to learn if the URL is indexable (through crawl.20230131.compliant.is_compliant).

The format of the filters object in metric-level filtering is identical to the other types of filtering, so we can use two filters wrapped in an and block.

This is our final BQL request:

{
    "collections": [
        "crawl.20230101"
    ],
    "query": {
        "dimensions": [],
        "metrics": [
            // Step 1: Pages crawled by Botify
            "crawl.20230101.count_urls_crawl",
            // Step 2: Known Pages crawled by Google
            {
                "field": "crawl.20230101.count_urls_crawl",
                "filters": {
                    "field": "crawl.20230101.search_engines.google.crawls.count",
                    "predicate": "gt",
                    "value": 0
                }
            },
            // Step 3: Indexable Pages crawled by Google
            {
                "field": "crawl.20230101.count_urls_crawl",
                "filters": {
                    "and": [
                        {
                            "field": "crawl.20230101.search_engines.google.crawls.count",
                            "predicate": "gt",
                            "value": 0
                        },
                        {
                            "field": "crawl.20230101.compliant.is_compliant",
                            "value": true
                        }
                    ]
                }
            }
        ]
    }
}

Now we should obtain three results, each representing one of the first three steps of the funnel, that you can compare to Analytics Overview.

Filtering Functions

When trying to apply a filter to a function, you cannot apply it directly to the function:

{
    "function": "avg",
    "args": ["crawl.YYYYMMDD.depth"],
    "filters": {
        "field": "segments.pagetype.value",
        "predicate": "eq",
        "value": "product"
    }
}

This will return an API error. Instead, the filter must be applied to each of the function's arguments:

{
    "function": "avg",
    "args": [
        {
            "field": "crawl.YYYYMMDD.depth",
            "filters": {
                "field": "segments.pagetype.value",
                "predicate": "eq",
                "value": "product"
            }
        }
    ],
}

This allows you to apply a different filter to each function argument if needed.