Set date range limit on google analytics query

[warrencowan]

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

[DavidMoss]

Have you tried editing the query in the Advanced Editor. I posted a similar solution here http://community.powerbi.com/t5/Desktop/Power-Query-M-filter-data-by-Dynamic-Date-PQ-not-DAX-Start-o...

[andre · Accepted solution]

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.

[twentyninefifty]

Here's the actual solution:

1) Build your query at a high granularity or just bring in the dates - forget your more detailed metrics for now.

2) Load that data and then filter using the dropdown filters.

3) At the top 'Cube Tools' > 'Manage' > 'Add Items' - now add your more granular metrics here and only the ones in the date frame will be brought in

[djohnson]

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. 

[gil3456]

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

[warrencowan]

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.

[andre]

@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]

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. 

[intrasight]

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.

[hson]

i agree - will Microsoft add these features in a future release?

[Anonymous]

Maybe you can create an Idea on the Ideas board? I'd surely vote for it. It shouldnt be that much trouble eiter, because the GA API calls have room for these parameters.

[Greg_Deckler]

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.

[SHDJason]

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.