I have setup Analytics Export to BigQuery. Everytime when a new ga_sessions_yyyymmdd gets created I would like to run some queries aggregating some data for future use.
I can't figure out how to do this. Do I have to create a job and trigger it from outside or is there a way to trigger this in BigQuery directly (prefably using the Web UI).
To be fair I've asked this question at StackOverflow too: Can I trigger creation of tables on Analytcs Export completion? But I'll add a Tableau twist here since this is where I will use the data.
My first stab at this data was connecting to the ga_sessions_-tables directly from Tableau. I setup a query (calculating DAU (or rather daily active visitors/devices) looking at visits in the last 7 days) which works fine. But to get running numbers and adding WAU and MAU and from different BigQuery views I would need to executa alot of queries and to be able to store them somehow.
To me it feels much natural to format the data to my needs in BigQuery and store it in custom tables there and then connect to this single table from Tableau.
Is this the right approach or is there something I'm missing? Should I keep a huge extract where I append data every day in Tableau or something (really doesn't sound like a good idea, right?)?
I got a reply at Stack Overflow, but prior to this I started looking into doing it server side. So my solution is to create the aggregations from BigQuery's API and store in a new BigQuery table. I will then use Google Cron to schedule the job of aggregating the data.