Working with GA4 data in BigQuery can be a real pain even for SQL experts. Fortunately there is
a zero coding solution called ga4sql built by optimization up which is clicks you can build
instant reports for BigQuery so you can save time and focus on insights instead. Let's see how.
Again right now we are focusing only on querying Google analytics for data if you need General SQL queries
or conduct analysis we've got you covered in another video, there we walk you through the
process of accessing and starting a project in BigQuery and show you how to effectively
ask chat GPT for the answers you need. In fact we suggest you to use both tools like GA4 SQL for
querying and Chat GPT for analysis ,so make sure to check that out. To use the tool head over to
ga4sql.com ,we are going to cover live examples but first let's have a quick overview of the
interface. Make sure to select Google analytics 4 for your table name you can add a new table
ID which can be fetched from your BigQuery details section you can select date ranges,
you also have custom date range to query your metrics or Dimensions simply click on
the respective bar and select your metric and do the same four dimensions. You can always offer the
interrogation mark to know what the dimension or metric is all about. another interesting feature
are filters which allows you to filter your data based on the dimension and metric that
you have added in you can also sort your data by ascending or descending order and lastly you
can also fetch your custom dimensions. Then you have to click on the generate query button and
the code will appear here copy the code and go and paste it into BigQuery. Let's begin with a
simple query for this we'll be using the BigQuery sample dataset for ga4 assuming that you already
have a BigQuery project, if not please refer to our other video. In your BigQuery project select your
table, go to details, hover over your table ID and copy your table ID in GA4 SQL raise suitable ID
in table name to avoid any config with your data in selections select your date and delete it. Now
we'll select the date you can look at the date range available for your data sets right here.
We'll select the custom edit range and I'll look at the year 2020 starting from the 1st of December
took the last month of January 2021. When it comes to our dimensions in metrics I'm
interested in looking at our number of sessions for each of our events. Click on metrics, select
sessions, in dimension search for event name and that will be it for now generate your query
here's a code copy your code. Now let's compose a new query and we'll go in new tab
I'll delete this and let's paste our code.
Always make sure to look here at the top right side for the size of your query to make sure
to keep your costs down. Now we are not dealing with gigabytes so we'll be fine and again this
is just best practice because right now we are dealing with a public data set and we will incur
no costs. Let's run our query and here we have our list of event names and their number of sessions.
For our next example I'm interested in recreating the landing page report
and we'll keep our current top settings for metrics let's add in sessions engage sessions.
I'm also interested in the average engagement time per user let's also add our page views.
we'll also add in our total users our number of new users and lastly also our total revenue.
for the dimension let's choose the landing page dimension. You also have the option of reordering
your metrics let's start switch page views and after our niche sessions we look at the number
of total users then our number of new users and they should be fine. Let's generate our query:
copy code and paste it into BigQuery. Let's run our code and here is our landing page report. Now
I'd like to order our landing page reports by our number of page views for this we can go to
sort by the select our field screen page views and select descending order let's regenerate our query
Now our landing page report is sorted by our screen page views
I'd like to take this a step further by focusing only on the landing pages receiving traffic from
our advertising campaigns. Let's add our session source medium dimension and now
that we've selected it we can filter it to look only at CPC, generate the query and copy your code.
Again please pay attention to this message our query is processing right now around 1000
megabytes which equates to about one gigabyte and one gigabyte is still within the free tier.
Again don't worry because we are using a free BigQuery public data set so no matter what
you'll not pay anything this is just to instill best practices so you don't make mistakes when
querying your company's data. Let's run our query and here we have our results with our
landing pages filtered by our traffic coming from our advertising campaigns with Google CPC.
Lastly let's look at some ways that we can limit our costs and this will be a great opportunity for
us to look at how to use chat GPT in combination with GA4 SQL two ways we can limit our cost is
first to look at shorter date ranges and also if you look at our results we can also limit costs by
limiting the number of columns that we have in our results not rows, so in this example we have the
Assassin Source medium column I'd like to keep the session Source medium filter but drop to column in
our end results to limit our date ranges we could go to GA4 SQL and change our date ranges but I
would like to just run everything with chat GPT after copying and pasting our code I ask chat GPT
to drop the session Source medium column in the end results and show data only for the month of
January 2021 chat GPT returned me a solution let's copy the code and let's paste this in BigQuery
and you can see that we've went from 1000 megabytes or one gigabyte down to 396 megabytes.
Let's run the query and here are our results. All right we come to the conclusion of this
video and we hope that you found it useful. Please share your thoughts with us and see you next time.