Wie kann man bei Universal Analytics mehrere ‘ga_session’-Tabellen mit ‘_table_suffix’ abfragen?

Jeder Tag der Google Analytics-Daten in BigQuery wird in einer separaten Tabelle gespeichert.

Wenn Sie nur Daten von einem Tag benötigen (z. B. 1. August 2017), sieht die from-Klausel in Ihrer Abfrage etwa so aus:

select *
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

In den meisten Fällen werden Sie jedoch einen größeren Zeitraum abfragen müssen. Hierfür können Soe  _table_suffix nutzen. Weitere Einzelheiten zur allgemeinen Verwendung finden Sie hier, aber um mehrere Tabellen mit Google Analytics-Daten abfragen zu können, müssen Sie sich nur die Beispielabfragen ansehen, die ich Ihnen hier vorstelle.

Beachten Sie, dass Sie statische Daten, dynamische Daten für einen rollierenden Zeitraum (die letzten 90 Tage) oder eine Kombination aus statischen und dynamischen Daten verwenden können. Es ist auch möglich, die Intraday-Tabelle in Ihre Abfrage einzubeziehen.

Alle Tabellen im Datensatz

Dies ist die einfachste Variante. Verwenden Sie einfach das Sternchen als Platzhalter, um jede Tabelle im Datensatz zu finden, die mit ga_sessions_ beginnt. Hinweis: Dies schließt auch die Intraday-Tabelle ein.

select *
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`

Statischer Datumsbereich

Wenn Sie eine Analyse für einen statischen Datenbereich durchführen, sollten Sie feste Anfangs- und Enddaten verwenden. In diesem Beispiel wählen wir den 1. und 3. August 2017.

select *

from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where
  _table_suffix between '20170801' and '20170803'

Dynamischer Datumsbereich

In diesem Beispiel wählen wir einen rollierenden Zeitraum der letzten 30 Tage: heute minus 30 Tage bis einschließlich gestern.

select *
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where
  _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 30 day))
  and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Kombination von statischen und dynamischen Daten

Wir wissen, dass wir 366-Tage-Tabellen in unserem Beispieldatensatz haben, also könnten wir hier ein festes Enddatum verwenden (20170801), aber normalerweise bevorzuge ich eine Kombination aus einem festen Startdatum und einem dynamischen Enddatum (in diesem Fall: heute minus eins).

Wenn neue Daten zu unserem Datensatz hinzugefügt werden, werden sie automatisch in unsere Abfrage aufgenommen. In diesem Beispiel wählen wir den Datenbereich vom 1. August 2016 bis einschließlich gestern aus. BigQuery findet heraus, ob die Tabellen tatsächlich existieren und gibt nur Daten für Tabellen zurück, die den Bedingungen entsprechen und tatsächlich existieren.

select
  *
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where
  _table_suffix between '20160801' 
  and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

Intraday-Tabellen

Für jede Google Analytics-Ansicht, die in BigQuery exportiert wird, wird auch eine ga_sessions_intraday_-Tabelle mehrmals am Tag exportiert. Beachten Sie das unterschiedliche Format für historische Daten (gestern und später):

 ga_sessions_yyyymmdd 

gegenüber Intraday:

ga_sessions_intraday_yyyymmdd

Die einfachste Möglichkeit, sowohl Intraday-Daten als auch historische Daten in Ihre Abfrage aufzunehmen, ist die Verwendung des Platzhalters in Kombination mit einem breiteren _table_suffix-Filter. Mit dieser Bedingung wird jede Tabelle in den Datensatz aufgenommen, die mit ga_sessions_ beginnt und ein Datum im Format yyyymmdd enthält.

select
  *
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`
where
  regexp_extract(_table_suffix, r'[0-9]+') between format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
  and format_date('%Y%m%d', current_date())