topic Re: Filtering Google Analytics data in load script in Connectivity & Data Prep

Filtering Google Analytics data in load script

Anonymous — Fri, 02 Apr 2021 08:05:21 GMT

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

Re: Filtering Google Analytics data in load script

Anonymous — Thu, 21 Sep 2017 16:46:01 GMT

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 ;

Re: Filtering Google Analytics data in load script

stevedark — Thu, 21 Sep 2017 19:50:06 GMT

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

Re: Filtering Google Analytics data in load script

Anonymous — Fri, 22 Sep 2017 07:13:48 GMT

Hi Matias,

It simply works!

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

Many thanks,

Re: Filtering Google Analytics data in load script

Anonymous — Fri, 22 Sep 2017 07:20:38 GMT

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