[MUSIC PLAYING]
MINHAZ KAZI: Welcome to I/O 2023.
I'm Minhaz Kazi.
And in this session, we'll take you on a journey
with three unique individuals who
work at Acme Corp, a totally not made up organization.
Throughout the session, we'll accompany these individuals
as they face certain business challenges.
Kishore, a marketing analyst, is trying
to get more value out of their Google Analytics
implementation.
Musa, a data engineer, is building
a robust and granular marketing reporting solution for users
across the organization.
Robin, the advertising expert, is
working with their internal data science team
to implement advanced use cases, like creating custom audiences
from marketing and first-party CRM data.
We'll see how all three use a key feature of Google
Analytics, the BigQuery export, to solve their problems.
By the end of this session, you'll
learn how to get started with the BigQuery export
feature for Google Analytics.
We will also equip you with tools and resources
to solve business problems.
You can find links to all the tools and resources mentioned
throughout the session at this link.
Now let's get started with Developer's Guide to BigQuery
export for Google Analytics 4.
[MUSIC PLAYING]
In our fictitious world, Acme Corp
is a manufacturing and retail business.
They have several websites and Firebase apps,
including ecommerce stores.
Acme has implemented Google Analytics 4, or GA4 properties,
across their sites and apps for measurement.
Kishore, an ambitious marketing analyst at Acme,
is exploring how they can do more
with the GA4 implementation.
Kishore is a savvy marketer, is great with spreadsheets,
and knows a little bit of SQL.
They realize they can go beyond the GA4 UI
by exporting their Google Analytics data to BigQuery.
The BigQuery export feature lets users
export event level data for their web and app properties
to BigQuery.
This data can be queried using SQL
or be used in different reporting applications
directly.
It is also possible to join the data with other data sources
and build models on top of them.
So Kishore starts with three questions,
how to try out the feature, how much will it cost,
and how to get started.
The quickest way to see how you can benefit from the BigQuery
export feature is to explore the demo data set.
This gives you an idea about the schema
and the structure of the data, what fields are available,
and how the collected data is surfaced.
If you want to run your own queries against the demo data
set, you can use the BigQuery Sandbox
that lets you explore BigQuery capabilities at no cost.
With BigQuery Sandbox, you can query the demo data set
or set up export for your own GA4 property,
without providing a credit card.
Creating a billing account, or enabling billing
for your project.
The Sandbox gives you 10 gigabytes of active storage
and 1 terabyte of process query data each month.
Any tables created will expire after 60 days.
So you get a significant amount of time to try it out.
Now let's see what the demo data set looks like and then run
a few basic queries.
[MUSIC PLAYING]
Over here, we have the GA4 demo data set.
You can see the schema here that lists the fields.
The event parameter is a nested record.
The user properties is also a nested record.
You can see a preview of the data
by clicking the Preview tab.
This will list all the fields, along with samples
of some records.
We can run a query by clicking the Query button.
Let's say we want to see a list of all the events,
along with their count.
So SELECT event_name and count, and then we'll
group this by event_name.
And we'll run the query.
This will give us a list of all the events
that happened on this date and their occurrence count.
We can also run some other sample queries.
The first query will show the list of all event parameters
and count their occurrences.
We'll create a new tab, paste in this query, and run it.
This gives us a list of all event parameters
that are available in the data set.
And the second query gives us the average number
of transactions per purchaser.
So let's go back, paste in the query, run the query,
and now we can see the average number of transactions
per purchaser for the data set.
This is just an example of how you
can explore through the demo data set
and run sample queries.
Once you've evaluated the BigQuery export,
you might have to set it up for long-term usage.
You should be aware of any potential BigQuery cost
implications.
BigQuery has a free tier for storage and querying.
This free tier is available to everyone,
so all Google Analytics users can also make use of it.
The first 10 gigabytes of data stored
is free every month, with $0.02 being charged
for each additional gigabyte.
First, 1 terabyte of query data is free every month,
with each additional terabyte costing
$6.25 or on-demand pricing at $0.04 per slot hour,
with auto scaling for Standard Edition.
If you enable the streaming export for your GA4 property,
it will cost a cent per 200 megabytes.
At this point, Kishore has a good understanding
of what the GA4 BigQuery export feature offers.
She draws up a quick proposal with some cost estimates,
gets approval from her department heads,
and now has a budget to work with.
To get started, she creates a GCB project,
sets up a billing account, goes into her GA4 properties admin
controls, and enables a BigQuery export.
At this point, she starts partnering
with an internal stakeholder to ensure
other folks in her organization can also make use of this data.
[MUSIC PLAYING]
Musa, the data engineer at Acme, is now
tasked with setting up the BigQuery data pipeline
and making it available to other users.
Once the data set is available, the first thing Musa does
is set up access control and custom cost controls
for BigQuery.
BigQuery cost controls can be implemented at project level
and at user level.
The project level cost control ensures
that the organization as a whole does not go
over a certain quota every day.
On the other hand, user-level cost control
enforces specific quota per user.
Setting up these controls ensures predictable billing
for Acme, at the end of the month.
Now, not all interested users in Acme were fluent in SQL.
Since the BigQuery data set can be analyzed with many reporting
applications, that made it easier for Musa
to open up the data set.
For example, many users were already
using Looker Studio within the organization.
Here's a quick demo of how they could
get started with a BigQuery export
data set without knowing any SQL.
[MUSIC PLAYING]
To start, we will go to report.new to create
a new report in Looker Studio.
Here, we'll select the BigQuery connector
and select our Billing Project, and search
for the GA4 demo data set in the public data sets program.
In this demo, we'll try to explore this data set
to see a trend of events over time and across different types
of devices and OS.
First, we'll add a date range control.
We know that this data set had data from early 2021.
So we'll select that date range.
Over here, in this table, we'll add
event names and event count.
As this loads, we can see that there are 17 different types
of events that occurred.
So let's try to see what kind of trend
we had for these events by day.
This will create a line chart based on the total event
counts.
Now to drill down further, we can add the device category,
and then also the OS.
Now, for device category and the OS,
we also want to see how many users
were associated with these devices and OS,
or basically, the user count.
We will add the count of User Pseudo ID to the device table,
and turn this into a bar chart.
We will do the same for the OS.
This gives a holistic view of the data set.
Now we can select Purchase in this table,
and that will cross filter all other chart
elements in this dashboard.
We can drill down further for purchases on mobile devices
and then again, purchases for only Android devices,
and see the trend over time.
So this is an example of how anyone
can use external applications, like Looker Studio,
to analyze their Google Analytics for BigQuery
data set.
No SQL expertise is required.
As users within the organization are using the BigQuery export
data set more and more, Musa gets
hit with a question from multiple users.
Why doesn't the data from BigQuery
match up with the data from the GA UI?
After some initial troubleshooting,
Musa can't find an answer and reaches out
to Kishore to investigate further.
And here's what they find.
The BigQuery export data and the data from the GA4 UI
are not supposed to match.
Based on the GA property settings,
Google Analytics does significant value addition
to the collected data before it reaches the UI.
These value additions can include inclusion
of Google signals, modeling, traffic attribution,
prediction, et cetera.
The UI aims to deliver maximum value to GA users
at the lowest friction.
But for advanced users like Acme,
BigQuery event export is the intended starting point,
where the users can build customized solutions
on top of their collected granular data.
To dig a bit further, here are some ways
the UI and BigQuery export may differ
and what Musa can potentially do to mitigate them
to some extent.
Conversion modeling, behavioral modeling, Google signals,
and traffic attribution data are available at the UI
at an aggregate level, but are not available in BigQuery.
Avoid comparing reports with the other row
or sampled exploration reports with BigQuery expert.
Ensure only active users are counted, instead
of all users in BigQuery.
Implement HLL++ for approximating cardinality
of common matrix, like active user and sessions.
Avoid common mistakes, like tagging errors, implementing
wrong metric calculation, and using the incorrect scope
for dimensions and metrics.
A significant number of users at Acme
are now using the BigQuery export data on a regular basis.
Musa can see the costs rising and potentially hitting
the daily quota for the organization.
To mitigate this, she takes on a new project.
Instead of letting the users query directly
from the BigQuery export data set,
Musa sets up an intermediary optimized data set
that is updated via scheduled query
or materialized view on a daily basis.
This intermediary data set contains flat tables,
with the most used fields surfaced at the top level.
The data set does a lot of preprocessing
and adds fields that would usually be calculated
on the fly with queries.
The new tables are partitioned by date
and clustered by predefined fields, like session ID.
Musa also enables BigQuery BI engine on this new data set,
to take advantage of fast in-memory analysis.
At this point, Musa has a robust data pipeline setup,
where users can use different applications
with optimized tables.
Cost controls ensured that the project stays within budget
while providing fast, performant queries, and dashboards
to the end users.
Now the organization is ready to implement some advanced use
cases.
[MUSIC PLAYING]
Robin, the advertising expert at Acme,
has so far been building audiences
inside Google Analytics and using them
for their Google Ads campaigns.
With the user level data being available in BigQuery,
they realize that it is possible to build even more
effective audiences by joining the Google Analytics
data with their first party CRM data in a privacy safe way.
So Robin works with their internal data science and data
engineering team to create a workflow.
From the BigQuery export data set,
they first filter records with user IDs.
This new data set is then joined with their first party CRM data
within BigQuery, using user ID as a key.
This joint data set acts as the base for their analysis.
The data science team then uses BigQuery ML
to build specific models, including lifetime value,
propensity to purchase, propensity to churn, et cetera.
Along with the user ID, these predicted
values can then be imported into GA, as user properties,
via the data import feature.
Then Robin can use these properties
to create custom audiences.
The custom audiences can then be used in Google Analytics,
as well as in Google Ads.
After setting up this pipeline, Robin
found out about two new features of BigQuery
export available in Q2 2023.
The first feature adds event level traffic source data
that is captured at collection, to the BigQuery event export.
With this feature, these additional fields
will be available in BigQuery event export,
with potentially more fields being available in future.
With the collected traffic source data,
Robin can now build their own traffic attribution model
for their organization.
And the second feature is a completely new type
of data export in a separate table.
It is a daily export of all users, whose GA4 data changed
within the last day, along with summary stats for each user.
The new user data export schema will include user properties,
like lifetime value and predictive metrics, audience
membership, user-scoped custom dimensions.
With this user data export, Robin and their colleagues
will be able to analyze their BigQuery export
data at user level, without additional query work.
This unlocks use cases like joining
additional first-party data at the user level,
and ultimately re-importing this data via data import
for use in linked Google Ads accounts,
as conversions or audiences, for targeting purposes.
Robin realizes that these new features
have opened up opportunities for additional marketing analyzes
and creating business value for Acme.
They're now excited about the upcoming projects
they're going to work on, using the BigQuery export data.
After all of this, Kishore, Musa, and Robin
sat down to reflect on their accomplishments
and share their knowledge with the rest of the organization.
Here's what they summarized.
Anyone using GA4 can use BigQuery export
for detailed or large-scale data analysis,
even without programming or SQL skills.
It does not cost anything to try out the BigQuery future,
and the potential future costs can be managed easily.
Even teams with limited resources
can follow solution guides to solve advanced business
problems with BigQuery export data.
And with that, we say goodbye to our three friends at Acme Corp
and welcome you to try out the BigQuery export
feature for your GA4 property.
Build a custom dashboard.
Create a [INAUDIBLE] model, generate custom audiences,
or try out something that's completely new,
harnessing the power of Google Analytics 4 with BigQuery.
Develop the solution that fits your needs.
You can join the GA Discord server if you have questions.
As I mentioned earlier, all the resources mentioned
throughout this session, along with
the relevant additional information,
are available at this link.
Thank you for watching.
[MUSIC PLAYING]