Refresh Google Analytics Bigquery Export

Question

Is it possible to refresh the Google Analytics BigQuery Export? I'm currently getting double the amount of bounces I should be on one day and I have no idea why (it's not double in GA)

Thanks

It did seem to be an issue with the wildcard. Not entirely sure why it was only a problem with one day though? — Aaron Harris

Willian Fuks Willian Fuks · Accepted Answer · 2017-06-16T11:57:21

This might be happening because in your wildcard selection you ended up querying the "ga_sessions" and "intraday" tables at the same time.

Sometimes it happens that the ga_sessions table is created and the intraday is not deleted which results in your wildcard selecting both tables.

I usually add this condition to my WHERE clause in order to select only one of the tables, like so:

FROM `dataset_id.ga_sessions*`
WHERE
1 = 1
AND CASE WHEN (REGEXP_CONTAINS(_table_suffix, 'intraday') AND REGEXP_EXTRACT(_table_suffix, r'.*_(.*)') BETWEEN "20170601" AND "20170602" ) THEN TRUE
  WHEN (NOT REGEXP_CONTAINS(_table_suffix, 'intraday') AND REGEXP_EXTRACT(_table_suffix, r'.*_(.*)') BETWEEN "20170525" AND "20170531") THEN TRUE END

If you want to select from previous "X" days until today, this might work (just replace X by how many days you want to go back in time, like 30 days for instance):

WHERE
1 = 1
AND CASE WHEN (REGEXP_CONTAINS(_table_suffix, 'intraday') AND REGEXP_EXTRACT(_table_suffix, r'.*_(.*)') BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))) THEN TRUE
  WHEN (NOT REGEXP_CONTAINS(_table_suffix, 'intraday') AND REGEXP_EXTRACT(_table_suffix, r'.*_(.*)') BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL X DAY))AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY))) THEN TRUE END

Data that happened until yesterday I query for the "intraday" table. Other tables I scan through only consolidated ga_sessions.

Refresh Google Analytics Bigquery Export

1 Answers