Using date filters with Google Analytics

I have a question about using date filters with Google Analytics. I searched through the forums and found some somewhat related topics but no answer to my question.

I want to use a FROM and TO date filter on our dashboards to also filter Google Analytics questions. I built them with the visual editor and the resulting query looks like this:

{
  "ids": "ga:ABC",
  "start-date": "30daysAgo",
  "end-date": "today",
  "metrics": "ga:users",
  "dimensions": "",
  "segment": "gaid::-2",
  "include-empty-rows": false,
  "max-results": 10000
}

Manually entering dates into the "start-date" and "end-date" does the trick, yet using the curly brackets to created a variable such as {{CALENDAR_START}} (like I do in SQL queries) doesn't seem to work when a Google Analytics source is selected. Is there any way to work around that?

Stretch goal: Ideally, I'd like to use the coalesce function described in Github to also set a relative default value for the date values: coalesce([[{{CALENDAR_START}},]] date_add(CURRENT_DATE, INTERVAL -1 month)) With MySQL this works like a charm and it would be great to also get this to work with Google Analytics queries.

Thanks in advance for any pointers and suggestions you might be able to give me.

Hi @ChristophD
That's currently not supported:
https://github.com/metabase/metabase/issues/6320 - upvote by clicking :+1: on the first post
GA is fairly limited in terms of what you can do. Metabase cannot do more than what the database does, so unless those functions exists in the database, then there's nothing Metabase can do.

1 Like

@flamber, thanks again for the quick reply. I was afraid of hearing that but had hoped I had simply missed something.

For your reference, in Chartio (which we're transitioning away from as Atlassian will stop supporting it on March 1, 2022) the use a concept that's very similar to the variables that Metabase supports in MySQL queries (whereby {CALENDAR.END} is what links the variable to the dashboard filter):

"dateRanges":
- "endDate": "{CALENDAR.END}"
  "startDate": "{CALENDAR.START}"
"includeEmptyRows": !!bool "true"
"metrics":
- "expression": "ga:newUsers"
"pageSize": !!int "1000" 

I assume they use a pre-processing step which converts the variable to the actual date which is then used to query the GA database. Maybe that's an approach that Metabase could consider as well.