Filtering Google Analytics data in load script

[Anonymous]

Hi all,

I need to filter data from different Google Analytics data connections in my load script.

I have seen examples of filtering using "where" SQL statement but I've always got error when tried.

For example, how could I edit this script:

GARsummaryM:

LOAD

    dim_yearMonth,

    metric_newUsers

FROM [lib://GAR-summary-mensual (escorpesqs_divisadero)]

(qvx);

to get only data matching dim_yearMonth = '201701' or metric_newUsers > 1000?

Regards,

Suni

[Anonymous]

Hello,

Did you try with:

GARsummaryM:

LOAD

    dim_yearMonth,

    metric_newUsers

FROM [lib://GAR-summary-mensual (escorpesqs_divisadero)] (qvx)

WHERE dim_yearMonth = '201701' or metric_newUsers > 1000 ;

[stevedark]

Hi Matias,

The issue with that approach is all of the data will need to be served up by Google to then be ignored by the query.  With a single dimension at a monthly granularity this probably wouldn't be a problem - but if you had a different query it would be a nightmare.

To do this properly the limitation needs to go in the URL that is passed to Google.  This could be done in the connection, but it would not be possible to change it from one query to the next.

The only other approach is to disable "Standard Mode" which will allow you to modify the parameters of the query on the fly - but this is a security risk.

There is more on standard mode usage here: Load Web File dynamically

There are other places where this is referenced also on the Community.

Steve

[Anonymous]

Hi Matias,

It simply works!

I wasn't able to locate the WHERE clause in the right place .

Many thanks,

[Anonymous]

Hi Matias,

I understand the point, unfortunately, IT team doesn't approve Standard Mode disabling so I have to perform a huge amount of request and it is going to be very painful to update data when needed.

You can directly filter data in the Analytics request through "filter" parameter but a limitation of characters exists so anyway, I needed to be capable of using filtering inside the load script.

Kind regards,

Asuncion