R programming language was used to perform site crawling, and the aggregation of Google Analytics and Search Console data—how can this be accomplished solely using online tools?
Once you properly integrate the Google Analytics and Google Search Console services (taking into account SSL and www/non-www considerations), you can begin using reports via the Universal Analytics (UA) through the Traffic > Search Console feature, providing reports on landing pages, countries, devices, and queries. However, you will often find that the data in these reports does not perfectly align with Google Search Console data, and many queries are marked as (not set). Therefore, Secondary Dimension becomes essential in this context.
Search and Page Data
Although a Search Console connection is available for Universal Analytics (UA), this feature is currently not available for Google Analytics 4. As a result, you will need to evaluate Search Console and Google Analytics 4 data separately, or merge the data through different services.
Google Search Console provides page and query data through two different reports (Queries and Page Count).
For the page-query relationship, you can reflect the other dimension as a filter in the report by clicking on either the page or the query.
Of course, you can continuously repeat this process for specific pages and queries, or alternatively, download the relevant data in Google Sheets, Excel, or CSV format and manually merge these files to create a comprehensive overview.
Upon completion of the download, you will receive the following data elements: Search Overview, Devices, Filters, Page Count, Queries, Dates, and Countries, all filtered according to your specified criteria.
.
├── Search overview.csv
├── Devices.csv
├── Filters.csv
├── Page count.csv
├── Queries.csv
├── Dates.csv
└── Countries.csvSo, how can we effectively and accurately display data from Search Console alongside Google Analytics 4 and UA?
Looker Studio and Search - Page Data
We can include Search Console data in Looker Studio through two distinct breakdowns: Site View and URL View.
These two breakdowns present data based on shared content parameters for web, image, video, and news.
Site View consists of the following metrics and dimensions:
| Header | Type |
|---|---|
| Country | Dimension |
| Date | Dimension |
| Device Category | Dimension |
| Google Property | Dimension |
| Query | Dimension |
| Average Position | Metric |
| Clicks | Metric |
| Impressions | Metric |
| Site Click-Through Rate (CTR) | Metric |
URL View consists of:
| Heading | Type |
|---|---|
| Country (Country) | Dimension |
| Date (Date) | Dimension |
| Device Category (Device Category) | Dimension |
| Google Property (Google Property) | Dimension |
| Landing Page (Landing Page) | Dimension |
| Query (Query) | Dimension |
| Impressions (Impressions) | Metric |
| URL Clicks (URL Clicks) | Metric |
| URL CTR (URL Click-Through Rate) | Metric |
As can be seen, both data sources share some common dimensions. Particularly, in the Query (Query) context, by combining both data sources, we can create the desired page-query relationship using the Blended Data Sources (Blended Data Sources) feature1.
BigQuery
Google Analytics 4 provides a BigQuery connection at the property level. To set this up, simply click on the BigQuery Linking link under the property-level features in GA4 and define a project.
If you do not require a continuous data flow, you can set the frequency to daily.
After the connection is defined, you can start viewing Google Analytics data through the BigQuery interface after some time (if you set up a daily data flow, after a day or more).
Business Intelligence Tools and Third-Party Integrations
Tableau, just as in the Data Studio example, it is also possible to acquire and combine metrics and dimensions to form a table using tools such as Microsoft BI and similar business intelligence applications.
On the other hand, it is also possible to use tools such as SuperMerics and PowerMyAnalytics. Of course, it should be remembered that these tools, like BI platforms and programming languages, can access data via API.