Improving Query Performance Using the Filter Parameter for Google Analytics

In some cases, query performance can be speedied up thanks to the filter parameter. In this guide, we'll consider an example where it helped to reduce load time from 30 minutes to just one minute.

Here's the original request:

SELECT parseDate(w."date",'yyyyMMdd') AS Datum ,
       w."date",
       w.hour,
       'WINDELBAR' AS "Reason Code" ,
       w.pagePath,
       w.landingPagePath,
       w.exitPagePath,
       w.previousPagePath,
       w.nextPagePath,
       CAST(entrances as integer) AS entrances,
       CAST(exits as integer) AS exits,
       CAST(pageviews as integer) AS pageviews
FROM (
    EXEC analytics.get(
        profile=>'1234567',
        startDate=> '2014-01-01',
        endDate=> '2014-01-07',
        metrics=>'entrances,exits,pageValue,pageviews',
        dimensions=>'date, hour, pagePath,landingPagePath,exitPagePath,
previousPagePath,nextPagePath'
    )
) w
WHERE w."date" != '(other)' and w.pagePath Like '%login%'

To optimize query performance, we can take the filter w.pagePath Like '%login%' and move it into a filter parameter for analytics.get:

filters=>'ga:pagePath=@login;ga:date!@other'

As a result, the query will take just a minute to run!