R7K12 Integration with Google BigQuery¶
Google BigQuery is a cloud service that processes huge volumes of data at extremely high speed. When you use it, you do not need to rent a server or pay for support. The cost of BigQuery is significantly lower than the cost of renting even the most basic server. Unlike other similar systems, the service lets you analyze large volumes of information directly in the cloud. With BigQuery, you can process terabytes of data in a matter of seconds. This database can store an enormous amount of data. This means that all of your statistics from all sources (all of your website traffic) for every day can accumulate, be stored in this cloud, and be used for analytics and visualizations. The next advantage is ease of use. In any other database management system (DBMS), in addition to knowing SQL, you would have to spend a long time figuring out the finer points of administration and database settings. With BigQuery, Google has taken on all the administrative work. This service has no settings, indexes, table engines, timeouts, or foreign keys. Only one encoding is supported: UTF-8. To work with BigQuery, it is enough to know how to load data into BigQuery and to have a basic knowledge of SQL. Why would you need this, and who needs it?
- First of all, you need it when Google Analytics does not analyze all of the data, but only a small sample (which in some cases may be no more than 1% of all data). You cannot base your analysis on such data.
- The second obvious case is when you exceed any of the Google Analytics data collection limits, and Google Analytics has quite a lot of them (https://developers.google.com/analytics/devguides/collection/ios/v3/limits-quotas?hl=ru#overview).
- The third case is when analytics is performed in a third-party tool, such as Microsoft Power BI, etc.
What you need for the integration:
- Your BigQuery account. If you do not have one, you need to create it.
- Access to R7K12. Signing in with your Google account is required for security and to provide a high-quality service.
- Important: the account used to access Google BigQuery and R7K12 must be the same.
How do you get started with Google BigQuery?¶
Sign in to Google Cloud Platform. Accept the terms of the free trial.
¶
Next, enter your payment details. On first launch, the system will offer to activate a free trial and give you a $300 credit for 12 months.
¶
Google asks for payment details to make sure you are not a robot. You will not be charged until you sign up for a paid subscription yourself. Find the “Select a project” button at the top and click it. A window for selecting the scope will appear.
¶
Click “Create project”.
Go to the billing section and link a billing account.
Then select and link a billing account for your project.
The project is now linked to the billing account you just created.
The next step is to enable the BigQuery API. In the dashboard, click “Products and services” (the button in the upper-left corner shaped like a circle with three horizontal lines) and go to “APIs and services”.
Here you will see information about the enabled APIs. If you have no enabled APIs and services, you need to enable them. Click “+ Enable APIs and services”.
Find and select “BigQuery API” from the suggested libraries.
To work in the Google BigQuery interface, you need to enable the “BigQuery API”. To do this, click “Enable”.
You will be taken to the menu of this API.
At this point, you are done working with Google Cloud Platform. You can close the tab.
Managing datasets in Google BigQuery¶
Go to the Google BigQuery interface and create a Dataset into which data from R7K12 will be loaded every day. The data structure in Google BigQuery consists of a project with a dataset containing tables. The Dataset name is the name of the campaign (website) in Latin characters; it may contain digits, and underscores are used instead of spaces. To do this, select the project (your project) into which the data will be loaded.
In the Dataset ID field, you need to set the dataset name; in the example below, we named it “test1”.
When you create a dataset in BigQuery, the dataset name must be unique within each project. A dataset name can:
- contain up to 1024 characters;
- contain letters (uppercase or lowercase), digits, and underscores.
Dataset names cannot:
- start with a digit or underscore;
- contain spaces or special characters (for example, &, @, or%).
Setting up the integration in R7K12¶
Sign in to the R7K12 end-to-end analytics system with a Google account that has access to both R7K12 and Google BigQuery. Select your project.
On the “Integrations” tab, select “Create” for the Google BigQuery integration setting.
Creating a connection to Google BigQuery:
- Select the Google BigQuery account to use: click “Select account”. In the window that appears, click “Add account” to sign in.
2. Sign in with the Google account you used earlier to create the project in BigQuery.
3. Select the data project you created in the “Select a project” field.
4. In the “Select a database” field, select the Dataset you created earlier in Google BigQuery. Specify the date from which you want to export data (the export date must not be earlier than the date the project was created in R7K12). Then click the “Analytics data transfer” toggle.
5. To export all project data from R7K12 to Google BigQuery, you need to create three integrations: visit data (sessions), cost data (cost), and deal data (deals).
In the table prefix field, enter the table name for visit data, for example “sessions”. Select the “Visit data” dataset and click the “Create” button.
6. You have created a connection to Google BigQuery. After that, the created integration will appear in the list of connected accounts. The list of connected accounts shows which login was used to create the connection and which project, dataset, and table the data is sent to.
Now you need to export the cost and deal data if you want to see this data in Google BigQuery. To do this, repeat steps 1-5 twice (to create the deal and cost datasets). In step 5, when selecting a specific dataset, specify the corresponding table prefix (“sessions” or “cost”). Since you have already signed in once, your account will be in the list of added accounts; you will only need to select it for the new integration.
After that, you should have three integrations for these datasets in the list of connected accounts. You can view their structure (data schema) at the link Data structure in Google BigQuery.
In the integration settings menu, by clicking the three-dot button, you can pause data transfer, delete the integration, and view the queue.
To view the data loading status by day, open the integration management menu by clicking the three dots on the right, then select “Queue”. Detailed information about the data loading status is shown here. If for some reason the data needs to be reloaded, click “Restart task”.
If the integration is set up correctly, data will be loaded into Google BigQuery every day into the table you set up the export to. The data is stored as daily tables: the date is appended to the name (Table ID) of each table, as shown in the screenshot.
The number of daily tables it contains is shown in parentheses next to the table prefix. It turns out that one large table for a certain period consists of smaller tables, each containing data for just one day.
