Google Analytics:
Hello everyone, I am Vikram from viksolve.com and in today’s special article, we are going to talk about Big Query and Google Analytics. We are talking about Image SEO throughout the month. It is the month of January 2024. And our target is to cover every aspect and topic of Image SEO throughout the month. But sometimes, doing the same topic over and over again gets a little boring. So today, we will look at Big Query for entertainment. Right? We will not do light work for entertainment.
We will do more technical work for entertainment. In this article of SEO last month, we talked about how Google has made a small update. And if your Google Analytics property has more than 25 billion events and you are consuming a lot of Google’s data, then Google can reduce your Google Analytics property’s data retention setting from 14 months to 2 months. So we thought, why not? And in fact, I didn’t think. You people told me. I said that if you say, then we will cover the topic of connecting Big Query with Google Analytics. So we are going to see the process of connecting Google Analytics with Big Query on your demand. But before seeing this process, we will have to understand what Big Query is. Because we are not developers.
We are coming from a non-developer background. So we will have to see what Big Query is. You have a Google Excel sheet. We all know what happens in an Excel sheet. There are rows and columns. And what happens between these two? Cells. So Big Query is a very big Excel sheet. A very big Google sheet. In which there is no limit of rows, columns and cells. In the Google sheet, I think, the limit of cells is around 10 million. Correct me if I am wrong.
I have never seen a Google sheet with 10 million cells. But there is no such limit in Big Query. You can store as much data as you want. And you can use the stored data. So this was what Big Query is. Now why should you use Big Query with Google Analytics? Number 1. Big Query is fast. Whenever you see a Google Analytics report. Or you use Google Analytics with Google Looker Studio. With Google Data Studio.
Also Read:
Image SEO Series | Part 1 – Resolution and Format | January 2024 | Learn Image SEO Like A Pro
SEO Last Month January 2024 | Latest Updates From Google Search, Google Ads | Viksolve
Image SEO Series | Part 2 – Image CDN | Image Caching | Image Optimization | Learn Image SEO Like Pro | Viksolve News
How One Metal Strip Ended an Entire Airline
Then you know how long it takes for the report to be made. It takes a lot of time to refresh the data. But when you use Big Query, your report is made in seconds. That is why Big Query works very fast. Number 2. You are the owner of this data. You can take the data of Google Big Query and host it on any other data platform. You can use it. It’s totally yours. You cannot export the data of Google Analytics. Number 3. Obviously size. If the data of your project, website and events is too much. And Google Analytics is not storing that data.
Then you can store your data in your Big Query project indefinite time. In Google Analytics, you are not getting the option to save more than 2 months and 14 months. After 14 months, your data will be removed. So if you set Google Analytics with your Big Query project. Then you can store your data indefinite time. You can transfer that data. So these were some benefits of using Google Big Query. But there is a small drawback that it is a paid feature.
You have to pay for storing your data on Big Query. You have to pay extra for storing the data. And you have to use that data. To use Big Query, you have to go to Google Developer Console. And to use this project in Google Developer Console. You have to have a paid billing account. The good thing is that using Big Query is not very costly. You can see the pricing page in the screenshot.
These charges are not very high. It is charged in Peso. And if your project is a decent size website. In which 1-2 million users are coming. Then also you can use Big Query in a good way. Your bill is not going to be very high. But still, because it is a paid product. It is important to inform you beforehand. So now let’s come to the computer screen. And let’s see how you will use Big Query with Google Analytics.
How you will store the data. And after this, how you will use it. How you will make reports using Google Looker Studio. Now, Big Query is a database. If you want to do any query in this database. If you want to use it natively. Like a programmer or a developer. Then you can use SQL language. What is SQL? I don’t know. I am not a developer. But we are not developers. I will show this process only by using Google Looker Studio.
How you can make a report by looking at the data store in your Google Big Query project. Let’s go to the computer screen and complete this process. So we have come to the computer screen. And to follow this article, you will need 3 tools. No. 1 is Google Developer Console. No. 2 is Google Analytics. And no. 3 is, we will use Google Looker Studio and Data Studio. Good name, Google Data Studio. I still believe in it. So first of all, we will make a Big Query project in Google Developer Console. And after this, we will come to Google Analytics.
We have come to Google Developer Console. If this seems very unfamiliar to you. If you have never used Google Developer Console. Then there is no need to worry. We will do each process step by step. Do not use it too much. Obviously, Google Developer Console as it is known from the word. It is for developers. But we are non-developers. Ordinary people also use it for their work. So to run any project on BigQuery. To use a lot of data there. You have to give commands in SQL language. But we are SQL. Whatever is the correct name. I am not a developer. But to use it. We will not use the SQL command.
We will bring the data through Google Analytics. In Google’s Big Query project. And from here, using our Looker Studio. We will make a straight report. So right now, we are making a new project. To make a new project. You will click here on this drop-down. If you have created an account for the first time.
Then you will get the option to create a new project. But if you have not done it. Then you click on this drop-down. And then click on New Project. I will give a name to this new project. Tutorial GA4. GA4 looks better. After this, if you want. You can give the name of the organization. To organize the project better. But right now.
I will leave all these details as default. Only the project name will remain. Tutorial GA4. It is important to remember this. It will be useful later. So click on the Create button here. And this project will be created. It takes some time. So we have created the project. And we have selected the project. Now we are in this project called Tutorial GA4. You can see here. And after this. We have to create a BigQuery project. For this, we will go here.
We will click on this hamburger menu. And after this. You will have to scroll a little bit here. And here you can see the BigQuery name. Okay. You can see this menu called BigQuery. You will click on this. On BigQuery Studio. Here you have to enable the BigQuery API. I click on it. Now you can see BigQuery Studio here. And you can see a lot of options here. You don’t have to think about all of them one by one.
We leave it as it is. If you drill down here. Then you can see the external connection options. We won’t look into all this. For now. For now. Let’s go to our Google Analytics dashboard. And we will complete the rest of the process there. Your work is done here. So we have come to the Google Analytics account. And here it is very easy to link with BigQuery. It is important to understand a few things.
That’s why we come to this part. We will click on the admin button. In our GA4 account. GA4 and Google Analytics are the same thing. So we have clicked on the admin button here. And we are a little new on this dashboard. We will look at the side here. Right. Here you can see the product link section. And here. You will see an option called BigQuery links. Okay. Click on it. This is the fourth option. Right now. Maybe it will change in the future. Don’t worry. Don’t be afraid. Click on BigQuery links. And here we can see the link button. We will click on this link button. And here. With the email ID.
You have just logged in to Google Developer Console. You have to work here with the same email ID. I forgot to tell you a small thing. With any Google account. You have made your account on Google Developer Console. And this project is new. With the same Google account. You have to log in to Google Analytics. If your website is not linked with this Google account. In Google Analytics account.
Then you should link. And after this. We will complete this process. So let’s see again. You have come to the admin button. On the admin button. You have clicked on BigQuery links. Click on the link again. And here. Link to a BigQuery project. You can see the manage option. Choose a BigQuery project. I will click on this. And automatically. All my projects on Google Developer Console.
They are all visible here. Now we have made a project called Tutorial GA4. Click on it. And after this. I will click on the confirm button. And after this. I have to select the data location. Where I want to store my data exactly. Here I select Asia South 2. After this. I will click on the next button. And here you can see.
The data stream and event. Which is going to be saved here. All the details are visible here. If you want to configure the data stream and event. You can select here. Which stream do you actually want to store here. If your Google Analytics account project.
There is more than one stream in that project. So you can select any one stream. Which you actually want to store. In a BigQuery project. And if you don’t want to do it. You can uncheck it here. Currently here in this account. There is only one stream in this demo project. So I will let it be selected from here. And after this.
I click on the apply button. And after this. Here you can see two options. Daily and streaming. Daily means. This project. The data of Google Analytics from this project. It will be stored once a day. In your BigQuery project. If you select streaming. As soon as someone visits your website. Someone hit. An event happened in your Google Analytics account. Similarly, within seconds. It will be saved in your BigQuery project. Now, your instinct will tell you that you want to see the data live.
The sooner it is stored, the better. So, you select streaming. Well, you can do that. But there is a big difference here, which is the budget. When you select streaming, your billing increases in Google’s BigQuery project. When you select daily, the billing of the project remains low. So, Google’s BigQuery projects have three parameters for billing. 1. How frequently the data is being stored. 2. How much data you have stored. 3. How many times you are querying the data. So, for example, if we select streaming here, the data that will be hit on your Google Analytics website will be saved on your BigQuery project immediately. But your project’s billing will also increase.
You will have to pay more. If you select daily instead of streaming, the Google Analytics data will be saved once a day. And it will be free for you. So, if you want to host your website’s data for free and save costs, you will select daily. For now, I will select streaming and save it. Because I have just created a new project. If I set it up on daily, the data will be saved at night and the article will be delayed. So, I will leave it on streaming. As soon as there are some visits on this website, the data will automatically start showing there. So, I will leave it on streaming and click on the save button.
You can see the daily option in the export type below. You can leave it as checkout. But again, as I am saying again and again, if you select streaming, you have to pay more. So, if you have a normal GA4 project, where you don’t need to see live data, then don’t set it on streaming, but leave it on daily. I will click on the save button. Now, I will give myself 5-10 minutes. And in 5-10 minutes, I hope that the website which is linked to this project, will automatically get some visits. And the data, our BigQuery, will come to the dashboard. So, now it’s been 10 minutes. And now, if I come and see, in this window named Tutorial GA4, we came to BigQuery Studio, remember? Here, you can see our project already.
If I click on this button and scroll down, you can see something here. Till now, when we made this project, there was only one option named External Connection. Now, here you can see queries, notebook, and analytics 349966815. Now, I have not made all these other options. I promise you, as soon as I left the dashboard here, it automatically created all these options because we connected the Google Analytics property here. And when the data of this Google Analytics property started coming, all the other properties were automatically created here. Anyway, in all these options, you don’t have to go to many other places. And in fact, if you know SQL language, then you can go to these properties and run your queries. But to make our report, we will go directly to Google Data Studio or Google Looker Studio. But before that, let’s see a little bit.
We have to show a small thing. We click on this Analytics option and click on the Events Intraday dataset that is visible here. And you will see that here you can see this data. Here you can see the schema of this data. Details are visible. And then after this, preview is visible. Let’s load this preview. Here you can see the information that is coming from this property. Okay? There won’t be much data here because we have just created this project and linked it. But still, the data that was supposed to come has come.
Now, let’s come to Google Looker Studio. After coming to Google Looker Studio, you will have to create a new report. For this, we will click on the Create button and then click on Report. And then we will have to add our data source here. Remember, we have to add BigQuery data source. So, here we will search for Big. If you have never used Google Data Studio or Looker Studio before, you don’t have much knowledge, then there are many articles related to our data studio on this channel.
There is a whole playlist. You can go and see it. You can see its link in the top right corner. Right now, BigQuery has come here. I will click on it. And here we have to select which project. Tutorial GA4. Because we are using Tutorial GA4 for this tutorial. As soon as I click on it, you can see the Analytics property here. Right? This is the same number that you can see here. Analytics 3499. Everything is in sync. Everything has to be done from the same Google account. Remember, I just told you that the Google account from which you have created your Developer Console account, you have to open Analytics from that.
You have to add Google Looker Studio from that. Okay? So, here is Tutorial GA4, Data Set, Analytics, and then this number. I will click on it. And after this, I have to select this table. And then here, the data of events level has come. I will add it. Let it load. It may take some time. Here we will click on Add to Report button. And then the data has come here. Okay? I will delete it. And after this, we will make another chart. Let’s say I picked a table. And let’s put it here. Let’s say event name. Now, obviously, the data here is very less because I have just created this project 10-15 minutes ago. I have set it up. But you can easily see that the refresh rate is very fast. I will quickly add a data control.
I will add it like a data controller. And if I change the date to this year to date, it will load very quickly because the data is coming from BigQuery. So, in this way, you can connect your Google Analytics property with BigQuery. And from BigQuery, you can connect Google Looker Studio and make great reports on Google Looker Studio. So, I hope that after today, you will not be afraid of BigQuery. It’s not that big.
It is small for our work. It will work for our work. Through Google Looker Studio, you will not have to learn SQL language. So, let’s meet in the next article. This was the entertainment part with Image SEO’s normal topics. I hope that you are entertained. Enjoy your Sunday. Thank you so much for reading this article. My name is Vikram from viksolve.com. And I will see you next time. Take care.
