bigquery daily shared tables and different date selectors (ga4 app exports)

[JFMO]

Hi there, 

we are trying to set up a GA4 bigquery integration (marketplace block does not work, we have too many changes to that and we also have App and Web data, for which this is not initally built. And we dont want to use sessionized data atm.)

So we integrated the events_* tables as one view and kindof solved the nested structure. 

But now we are stuck with the table structure on bigqery , which is daily sharded tables.

This means we need to query with a table suffix wildcard for each day. This way we can define a date range we want data from. So we defined 7 days.
But we most often dont want to look at timeframes like “the last 7 days”. 

• the problem is: we need to have the always filter to get the initial query working.
  (the always_filter fills in the values for date_start and date_end).
• as soon as we want to do YTD, YoY we have a problem with any always filter settings.

I would be happy for any idea, what we could do different, to solve this.

Thanks!

view: (i changed projectID and datasetID names)

  derived_table: {
    sql: SELECT * FROM `project.dataset.events_*`
      WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", {% date_start date_filter %}) AND 
  FORMAT_DATE("%Y%m%d", {% date_end date_filter %});;
  }



  filter: date_filter {
    type: date
    label: "Date"
  }

  dimension: event_date {
    hidden: yes
    type: string
    description: "The date on which the event was logged (YYYYMMDD format in the registered 
    timezone of your app)."
    sql: ${TABLE}.event_date ;;

  }

  dimension_group: date {
    view_label: "Date"
    description: "use this date dimension to generate time series"
    type: time
    timeframes: [
      raw, time, date, week, month, day_of_month, month_num, quarter, year]
    sql: PARSE_DATE("%Y%m%d", ${event_date}) ;;

we need in the model also this to get a start and end date filter for the query to initially work.

explore: mo_ecommerce {
  label: "MO-GA4 APP & WEB NEW"
  always_filter: {
    filters: [mo_ecommerce.date_filter: "7 days"]
  }
  join ....

Thanks!