cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
warrencowan Regular Visitor
Regular Visitor

Set date range limit on google analytics query

Does anyone now how to set the date range for the Google analytics connector in Power Bi desktop.

 

Its a very powerful connector but when I create a query, it pulls it back all rows for all time which goes back 2.5 years.

 

Its nice to know I can get that if I need it, but its massive overkill for a last 30 days analysis on sales and makes for huge, slow and unwieldy data sets.

 

I just cant see a date range limiter in the connector, or figure it out in the advanced query editor!

 

Anyone got any suggestions? 

 

Thanks in advance, Warren

1 ACCEPTED SOLUTION

Accepted Solutions
andre
Advisor

Re: Set date range limit on google analytics query

When you bring the Time attribute in your query, you have the ability to right click on the time field and specify the filter condition (Before, After, etc.) after the filter has been applied you can click on a gear box next to the Filtered Rows step in the Applied Steps section on the right and tweak it if you need to.

 

12 REPLIES 12
andre
Advisor

Re: Set date range limit on google analytics query

When you bring the Time attribute in your query, you have the ability to right click on the time field and specify the filter condition (Before, After, etc.) after the filter has been applied you can click on a gear box next to the Filtered Rows step in the Applied Steps section on the right and tweak it if you need to.

 

warrencowan Regular Visitor
Regular Visitor

Re: Set date range limit on google analytics query

Thanks Andre, super helpful of you!. Can I ask a couple of follow up questions for clarity. 

 

The only place I could find what you suggested was in the edit queries section, where I went to to the column for date, clicked on the drown arrow in the 'date' column header, and filtered that way using the filter fucntion.

 

Was that what you mean't?

 

If so, does this filter the data post query, in which case my query to Google analytics still a large 'all time' one? If yes, can I chnage the actually query to Google Analytics, to speed up the query itself.

 

The latter is probably the larger issue for me, as the query can run for half an hour at a time.

Super User
Super User

Re: Set date range limit on google analytics query

If you edit your query, add something from the Time category and filter on that, then my understanding is that this will only bring down the unfiltered information from the service into the model, just like how it works with all other data sources. If you filter as part of the query, then only the unfiltered data comes into the model.

 

This is different if you didn't filter in the query itself but rather built a report after the fact and did your filtering there. In this case, your data model would have all of the data.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


andre
Advisor

Re: Set date range limit on google analytics query

@warrencowan this is a good question, I have not tested it on large enough data sets where I could notice wheter all of the data are brought back first or wheter the filter condition is sent as a parameter to Google before the data start flowing in.

 

warrencowan Regular Visitor
Regular Visitor

Re: Set date range limit on google analytics query

I think that is how it works Andre.

 

Once the query is filtered on the date field, the message dialogue suggests it is indeed pulling back fewer rows from the API and taking less time.

 

I think there's a bit of fiddling to do though, as you cant specify the filter until the query is built, so its best to build it in the qeury manager, where you work with the preview data. Rather than than the get data fucntion in the report designer.

 

Andre & Smoupre, very many thanks for your input. Smiley Very Happy

Highlighted
intrasight Member
Member

Re: Set date range limit on google analytics query

Really, the lack of date range parameters on this integration makes it near useless. GA doesn't return leaf-level data, so the only metrics which will propery aggregate are counts like pageviews. Also, not having true GA filters and segments is a show-stopper. This integration is basically eye-candy. Tableau made some (but not nearly as many) of the same mistakes in their integration.

gil3456 New Member
New Member

Re: Set date range limit on google analytics query

Hi Andre - I'm right clicking the date field and don't see the ability to apply a filter. Would it be possible for you to screen shot this and post.

 

Thanks

djohnson Regular Visitor
Regular Visitor

Re: Set date range limit on google analytics query

Correct, this works on helping to reduce sample sizes. Just a couple of caveats:

 

1. Filter dates are static and thus, you don't have the ability to specify dynamic stuff like 'last 90 days' - so you will need to update this from time-to-time

2. I have seen PowerBI reset all the datatypes and filters when adding additional additional columns to the dataset. So, after adding a new column (dimension/metric) and your report "blows-up" - go back and check the datatypes and filters. 

SHDJason Regular Visitor
Regular Visitor

Re: Set date range limit on google analytics query

This is only partially true. In the case of a large dataset, Power BI will query the full dataset, then only load the records that match the filter definition. However, this does not really solve the problem because GA will return sampled data if the request is too large. Unfortunately, there does not seem to be a way to stop P BI from making a full dataset request becaue there is no way to set the GA start and end date parameters. Consequently, the data that is downloaded could be the result of a very high sampling rate even if you are only interested in a small slice of data. 

 

The Power BI team should really make it possible to set the additional GA parameters like start date, end date, segment ID. They are pretty important when working with the Google Analytics api.