Nowadays, the amount of data handled is increasing every day, and the applications for big data are also increasing. When implementing
marketing
measures, it is becoming increasingly important to analyze large amounts of data more quickly.
One big data analysis service that can be used is Google BigQuery, provided by Google. It is an inexpensive and easy-to-use tool that anyone can use, yet it is a high-performance service.
Therefore, this time, we will introduce Google BigQuery in an easy-to-understand manner for those who are new to Google BigQuery, what kind of service it is, its functions and features, the benefits of using it, and examples of its use.
What is Google BigQuery?
Google BigQuery is a product provided as one of Google’s
cloud
services, “Google Cloud Platform (GCP).”
To understand Google BigQuery, first understand Google Cloud Platform.
What is Google Cloud Platform?
Google Cloud Platform is a general term for a group of services provided by Google on the cloud.
Google Cloud consists of physical equipment, such as computers and hard disk drives, and virtual resources, such as virtual machines, operating in Google’s data centers around the world. Google Cloud Platform is a cloud service that allows you to use software and hardware products in Google Cloud.
For example, by using Google Cloud Platform when you want to develop an application, you do not need to prepare the necessary hardware or
servers
for development yourself. You can focus on development by minimizing costs and effort.
Features of Google BigQuery
Google BigQuery is a service available on Google Cloud Platform. Simply put, it is a tool that can instantly analyze large amounts of data. Generally classified as a data warehouse.
With Google BigQuery, even big data of several TB (terabytes) or several PB (petabytes) can be analyzed at ultra-high speed. For comparison, 1TB is 1,024GB (gigabytes) and 1PB is 1,024TB.
Google BigQuery can be used for big data analysis. For example, in the marketing field, website access analysis data is used to improve advertising and improve analysis of website customer behavior.
As it is a cloud service, it is serverless, easy to implement, scalable, and has excellent cost performance. Another feature is that it can be smoothly integrated with various services provided by Google Cloud Platform.
Google BigQuery was originally a data analysis tool called “Dremel” within Google. Google used Dremel to analyze big data. After that, Dremel’s functions were made available to general users as Google BigQuery.
What you can do with Google BigQuery
Introducing what you can do with Google BigQuery.
Google BigQuery allows for big data analysis as well as large-scale queries. A query is an instruction to a database. Here, let’s review the word “query”, which is essential to understanding Google BigQuery.
What is a query?
Query is written as “query” in English and has meanings such as “to inquire” or “to visit.” When used in
the IT
field, it refers to an inquiry about data or a request for processing to some kind of system. Requests are expressed in characters according to the specified format.
For example, a request for “please process it like this” regarding data extraction or updating is expressed as a character string. Request by specifying the table to be processed, data extraction conditions, sorting method, etc.
What you can do with Google BigQuery
Google BigQuery processes many queries by users around the world. Large queries are distributed and processed across thousands of servers.
This allows Google BigQuery to:
- data analysis
Google BigQuery allows you to analyze huge amounts of data. In recent years, it has become possible to obtain a variety of data, including web access
logs
, so the types and opportunities for data analysis have increased. Among these, it is especially suitable for analyzing huge amounts of data at ultra-high speed. Since real-time analysis is also possible, it is useful for quick management decisions and strategy formulation.
- data storage
It can also be used as a place to store large amounts of data. You can store and process huge amounts of data such as access analysis data and CSV files.
- Cooperation with various Google-provided tools
It can be easily integrated with tools provided by Google, expanding its range of uses. For example, it can be linked with the access analysis “Google Analytics”, and it can also be linked with the BI tool “Google Data Portal”.
The benefits of collaboration include the convenience of being able to perform analyzes that could not be performed with Google Analytics alone by using Google BigQuery, and the ability to visually display the analysis results of Google BigQuery in Google Data Portal. will increase.
Features of Google BigQuery
What features does Google BigQuery have? Here we will introduce four features.
Fast data processing speed
As mentioned earlier, Google BigQuery can perform data analysis at high speed even with large amounts of data such as TB or PB.
Importing your own data into Google BigQuery storage is faster than querying external data sources. It is useful not only for high-speed analysis of huge amounts of data, but also for real-time analysis.
High performance and low price = high cost performance
Google BigQuery is high-performance and inexpensive, so it is said to be highly cost-effective to use. Being able to easily analyze huge amounts of data is a great deal compared to preparing a server from scratch. Furthermore, compared to other similar services, it is comparable in terms of ease of use, scale, and speed.
So, what about the price? To begin with, Google BigQuery pricing is determined by two things: analysis fees for query processing and storage usage fees.
Analysis fees vary depending on the frequency of query processing, so if the frequency is infrequent, the price will likely be lower. In general, it is assumed that storage will not be used and it will only be used for analysis, so it is thought that many companies can use it at a low cost.
There is also a free usage period, so we recommend that you try it out first to see how it performs. Analysis fees are free up to 1TB per month, and storage fees are free up to 10GB per month. Another benefit is that you can estimate the cost in advance, so you can check the cost-effectiveness in advance.
Please note that even after the free period ends, the analysis fee is charged only when the service is activated, so if you use a pay-as-you-go model, you will only be charged while the service is activated. This means that it is cheaper than similar services. We will introduce the pricing model later.
No need for database expertise
Google BigQuery can be used without any database expertise. This is because Google BigQuery’s competitors are data warehouses made by other companies. A data warehouse refers to a large-scale data analysis system.
A typical data warehouse often requires database expertise. For example, in order to start analysis, it is usually necessary to build a server and optimize settings. However, Google BigQuery can be used serverless and does not require database tuning, so once you learn how to use it, you can analyze it.
However, since data processing is often performed by writing SQL, which is a database language, it is necessary to understand SQL. Google BigQuery can basically be started on a web browser and processes data by writing SQL in a form on a web screen. The screen structure is simple, and there is a “query editor” at the top of the screen, where you can write and execute queries.
SQL is a language that allows commands to select data that meets specified conditions from a large amount of data, and to add or delete data. There are only four basic SQL statements: data acquisition, data addition, data update, and data deletion. Execute queries using these languages.
It is said that SQL is not a very difficult language, and it seems to be common in recent years for marketers to start learning SQL in order to take advantage of Google BigQuery.
Can be linked with various Google services
Google BigQuery has the advantage of being able to integrate with various services provided by Google, making it easy to utilize data when implementing marketing measures.
Examples of services that can be linked with Google BigQuery include Google Spreadsheets, Google Analytics, and Google Firebase. Let’s take a look at what each can do by working together.
- What you can do by linking with Google Sheets
Google Sheets is a service that allows you to perform spreadsheet calculations on a web browser. You can create charts and spreadsheets online that can be done using Microsoft Excel.
By linking Google BigQuery with Google Sheets, you can improve the operational efficiency of reporting work. For example, you can create graphs in Google Sheets using data extracted from Google BigQuery. Generally, BI tools are linked to visualize data analysis results from Google BigQuery, but if you want to easily create graphs, you can conveniently use Google Sheets.
- What you can do by linking with Google Analytics
Google Analytics is a website access analysis service. By exporting user web behavior data obtained by Google Analytics to Google BigQuery, you can combine it with other data loaded into Google BigQuery for analysis. By linking with Google BigQuery, you can perform more detailed analysis.
- What you can do by linking with Google Firebase
Google Firebase is
a platform
that allows you to quickly develop mobile apps. Not only can you develop mobile apps at high speed, but it also has a wide range of functions that are useful for mobile app development and operation, such as digitizing the behavior of users who use the application.
By linking with Google BigQuery, advanced usage such as analyzing logs such as user behavior obtained by Firebase with Google BigQuery becomes possible. It is thought that you will be able to understand things that you could not know with Firebase alone.
Why Google BigQuery can process large-scale data at high speed
Why is Google BigQuery capable of processing so fast? Google explains that this is due to the following two unique mechanisms.
1. Column data store
One is that it is a column-type data store.
When performing query processing, in a normal database, data is generally read “row by row” and “horizontally”. However, in the case of Google BigQuery, it seems that the data is read “column by column” and “vertically”.
Why does this enable faster speeds? When loading data horizontally, the entire data must be loaded. On the other hand, if you read data vertically, you only need to read the columns of data that you need to read. In other words, it is said that traffic can be minimized because only the data in the column that is the target of the query is accessed.
Furthermore, since data contained in the same column is highly similar, there is little variation in data, resulting in a high compression rate. Generally, if there are repeating patterns in the data, compression is applied to reduce the amount of data, and the more repeating patterns there are, the higher the compression rate will be. Google BigQuery uses a mechanism that reads data column by column and vertically, which means that query processing can be performed with high compression rates.
2. Tree architecture
Another point is that it is a tree architecture. A tree architecture is a structure in which queries are spread out like a tree.
When processing a query, the root server receives the query. A large number of leaf servers then actually perform the query processing. By spreading queries like a tree from this root server to each leaf server, it is possible to process data in parallel. By processing in parallel, large-scale data can be processed at high speed.
How to use Google BigQuery
If you are using Google BigQuery for the first time, you can get started using the following steps. Start with a free trial.
1. Access the official website of Google BigQuery
Visit
the official Google BigQuery website
.
2. Select “Google BigQuery Free Trial”
Click the “Google BigQuery Free Trial” button on the top page, and when the screen changes, follow the instructions and enter the necessary account information, payment information, etc.
First, you need to register with Google Cloud Platform (GCP), and a credit card is required for registration.
After starting the free trial, you may find yourself wanting to put usage on hold for the time being. In that case, you may be worried that you will be automatically charged when you enter your credit card information, but it is clearly stated that you will not be automatically charged even after the free trial period ends.
Once the input is complete, registration with Google Cloud Platform will be completed and you will be able to use Google BigQuery.
3. Choose Google BigQuery
The Google Cloud Platform screen will open. A navigation menu will then appear on the left side of the screen, so select the “Google BigQuery” item.
Now you can finally start using Google BigQuery. Create a project, load data, and execute queries.
Google BigQuery pricing
Let’s check the fees for using Google BigQuery.
There are two types of fees: analysis fees for processing queries and storage fees for storing data. Free tiers are available for analysis fees and storage fees. Analytics is free up to 1 TB per month of query data processed, and storage is free up to 10 GB per month.
1.Analysis fees for processing queries
There are two pricing models available for analysis pricing: on-demand pricing and flat rate pricing. On-demand pricing means you’re charged based on the number of bytes processed by each query, meaning you pay as you go.
A flat rate fee allows you to use a slot for a fixed amount by purchasing a slot, which is a virtual CPU, and means that you have purchased dedicated processing capacity that can be used to execute queries. Flat-rate pricing is for long-term users and can be offered at discounted prices depending on the contract type, and you can even combine both pricing models.
2.Storage charges for storing data
Storage charges are the costs required to store the data you load into Google BigQuery. There are two pricing models: active storage and long-term storage.
Data that has been modified within the past 90 days is eligible for active storage, and data that has not been modified within the past 90 days is eligible for long-term retention. Long-term storage fees are automatically reduced by approximately 50%.
The above information is as of March 2023, and prices are subject to change, so please check the official website when signing a contract.
■Successful use case of Google BigQuery
Google BigQuery is already being used effectively both in Japan and overseas. Here we introduce four examples.
- results
For marketing, we use Google Analytics to analyze website access. The access history data is transferred to Google BigQuery, and advertising campaigns are evaluated using a machine learning model by combining the advertising media’s performance data with the customer’s contract performance data from the internal system.
By unifying our data analysis platform to Google BigQuery, we are now able to understand the correlation between websites, advertisements, and contracts, and understand whether our customer attraction activities have directly led to contracts.
- results
By migrating data that had previously been handled in an on-premises environment to Google BigQuery, the company was able to increase the amount of data it could handle by 10 times. Data analysis used to take about two hours, but with Google BigQuery it can now be completed in two to three minutes. As a result, the number of reports that can be generated has increased tenfold.
We have also been able to conduct new experiments such as linking and analyzing web access logs with order data.
- results
We decided to introduce Google Cloud, with an eye toward introducing Google BigQuery, as an environment for collecting and analyzing the vast amounts of purchasing and behavioral data we possess.
Previously, we had been using an app to link and analyze user data and POS purchasing data, but at the time, each analysis took 30 to 40 seconds, which was too time-consuming, and we were unable to fully consider new measures. It was a situation where I couldn’t do it.
After replacing the data analysis platform with Google BigQuery, one analysis was completed in 5 seconds. You can now review your measures based on accurate analysis and plan your next strategy smoothly.
- results
In light of the accelerating business environment, the company believed that data-driven decision-making based on data-based facts was essential. In order to speed up decision-making, we believed that we needed an environment in which employees could access the data they needed, when they needed it.
Therefore, when building a data utilization platform, we designed a system centered around Google BigQuery, which marketers had a proven track record of using. As a result, anyone can now easily search the accumulated data, and data utilization has rapidly progressed. It has become possible to analyze large-scale data that was not possible with conventional systems, and progress has been made in solving problems.
Google BigQuery can process large-scale data at high speed in all cases, and it can be seen that the benefits of being easy to use for anyone are being taken advantage of. One of its major features is that it is easy to use, especially for people who are not experts in data analysis, such as marketing personnel.
summary
- Google BigQuery is a data warehouse provided as one of Google’s cloud services, “Google Cloud Platform (GCP).”
- In the marketing field, website access analysis data is used to improve advertisements and improve analysis of website customer behavior.
- Google BigQuery allows data analysis, data storage, and collaboration with various Google-provided tools.
- Features of Google BigQuery include “fast data processing speed”, “high performance and low price = high cost performance”, “no need for database expertise”, “can be linked with various Google services”, etc. .
- Google BigQuery’s ability to process large-scale data at high speed is said to be largely due to two mechanisms: the columnar data store and tree architecture.
- Google BigQuery fees include analysis fees and storage fees. There are two types of pricing models: a pay-as-you-go model and a flat-rate model for analysis fees, and active storage and long-term storage for storage fees.