Google Search Console has every bit of data you need about your website, from performance to security. But to dig deeper and visualize it better, it’s smart to move this data to BI tools like Power BI. Let’s explore the different ways to do this.
Methods to connect Google Search Console to Power BI
Coupler.io automated connection
With Coupler.io, you can connect Google Search Console to Power BI in just a few minutes, and the data automatically updates in Power BI without any manual intervention. This Power BI connector allows you to schedule these updates as often as every 15 minutes so that your reports and dashboards always have the latest data. The process is simple to set up without any coding. Additionally, you can add multiple data sources to create individual or multi-source dashboards easily.
Manual export
You’ll manually transfer data from Google Search Console and import it to Power BI without any additional costs. This method gives you full control of the data transfer process. At the same time, frequent updates require significant time and effort and are susceptible to errors, like mishandling data or formatting issues.
Using BigQuery Power BI connector
Power BI lacks a direct connector for Google Search Console, but it does have a BigQuery connector. To connect Google Search Console to Power BI, you first transfer data from GSC with BigQuery and then connect BigQuery to Power BI. As BigQuery is a data warehouse, it can handle large datasets easily, and you can also automate the data transfer process. However, this method can be cost-intensive as BigQuery comes with data storage and query costs, which can add up depending on usage.
Using GSC API
Connecting Google Search Console to Power BI via the GSC API is a customized approach for data analysis. You can customize data queries for detailed insights, ensuring Power BI dashboards reflect specific and current data. You’ll start by setting up a project in the Google API console. Once you give users access, they can pull search performance data directly into Power BI. However, this method requires technical knowledge for API integration and ongoing maintenance to adjust to API updates.
Integrate Google Search Console into Power BI with Coupler.io
Coupler.io is a data automation tool that automates the data transfer process from multiple sources to destinations like Power BI, Looker Studio, Google Sheets, and more. It is a simple 3-step process to connect Google Search Console to Power BI.
Step 1: Collect data
To get started, click on Proceed in the form below.
Now, create a Coupler.io account for free and set up the settings for Google Search Console.
- Basic settings – site and report type
- Report period – start date and end date
- Dimensions – select one or more dimensions like date, country, device, and more to filter your data accordingly
- Advanced settings – search results type, aggregated data, and data state
Step 2: Organize and transform data
In this step, you can format your data using the data transformation features:
- Column management – Rearrange, add, or remove columns to focus on relevant website metrics for your analysis.
- Filter – Narrow down data based on specific criteria, like indexation status or page clicks, for targeted performance analysis.
- Sort – Order the data by specific columns like clicks or impressions to identify top-performing or underperforming pages.
- Custom formulas – Create new formulas like a performance index by combining all the values of clicks, impressions, and positions into a single score.
Once again, click on Proceed and move on to the destination settings.
Copy the integration URL and run the importer.
Go to Power BI and select ‘Web’ from the list of Get data options. Now, paste it into Power BI.
After establishing the connection, you’ll see the preview of your data. You can click on ‘Transform Data’ to manipulate the data further using the Power Query editor and then load it. We’ll directly click on ‘Load’ as the data is already transformed in Coupler.io.
Step 3: Schedule data transfers
Let’s now go back to Coupler.io to set up an automatic data refresh schedule. You can specify the interval frequency from 15 minutes to monthly.
You connected Google Search Console to Power BI. Now, you have near real-time data in Power BI to build self-updating SEO performance dashboards and make informed decisions.
How to understand your website data with a ready-to-use SEO dashboard
You can also use a pre-built SEO dashboard template to view your website data. If building a dashboard is confusing or time-consuming, then this template is perfect for you.
With ?this dashboard, you can instantly view key metrics such as Indexed pages, impressions, clicks, and CTR right at the top. It makes tracking the trends in clicks and impressions over time easy. Choose start and end dates to compare data across different periods. Use filters to refine data by device, page, and page folder, to get better insights.
Click on ‘Get this dashboard for free’ and follow the instructions in the Readme tab. You’ll have the SEO analytics data in the dashboard in just a few minutes.
Real-life use cases of Google Search Console data in Power BI
Below are a couple of use cases to help you understand your website data better.
Use case 1: Identify traffic winners and losers
With frequent minor and major Google updates, monitoring changes in website rankings is vitally important. You can identify the traffic winners and losers from the SEO dashboard above.
The Best Performers are pages that have increased clicks when compared to clicks from the past month. On the flip side, Worst Performers are pages that lost clicks. Focusing on pages with a 15% change in clicks helps you spot the real winners and losers. It identifies the significant changes so that you know where to put your effort to make a difference. These charts guide you to adjust and adapt your SEO approach and content, based on what users and Google prefer.
Use case 2: Check the Google indexation status
To make sure your website’s pages are showing up on Google, you need to track their indexation status regularly. It’s important to check when E-commerce websites add new products or after Google updates.
In the above SEO dashboard, you’ll be able to track the number of indexed pages and the % increase in them.
But for more details about which pages are indexed and which are not, you should build a detailed Google indexation status report.
Similar to the previous section, connect your Google Search Console account. Select the report type ‘URLs index performance’ and include the list of URLs.
Now, in the Transform data step, select only two columns – inspectURL and indexStatusResult.verdict.
You can load this table to Power BI or any other destination. Do not forget to automate the data transfer to have real-time index status for your pages.
Load data from Google Search Console to Power BI manually
Open Google Search Console and log in with the Google account associated with your website. Access the specific report you are interested in. For example, performance reports for search analytics data.
Use the available filters to select the date range, query type, page, country, device, and more to configure the report as required.
Look for an export button in the report view. Google Search Console typically allows you to export data in various formats, such as Google Sheets, CSV, or Excel.
Launch Power BI Desktop on your computer. If you don’t have it installed, download it from the Microsoft Store. Click on ‘Get Data’ from the Home ribbon.
Select ‘Text/CSV’ if you exported your data as a CSV file or ‘Excel’ for an Excel file.
Now, upload the performance report downloaded from the Search Console. As this report will have multiple sheets, you can select one. Click ‘Open’ to load data from Google Search Console to Power BI.
Once the data is loaded, you can use Power BI’s Query Editor to transform the data by renaming columns, changing data types, or removing unnecessary columns.
With the data imported, you can now use Power BI’s visualization tools to create reports and dashboards based on your Google Search Console data.
Export data from Google Search Console to Power BI using BigQuery
To export data from Google Search Console to Power BI using BigQuery, follow the steps below.
This method has 2 parts:
- Bulk data export from Google Search Console to BigQuery
- Connect BigQuery to Power BI using the native connector
While there are other ways to export data from GSC to BigQuery like API and third-party solutions, bulk data export is more cost-effective and easy to use for one-time tasks.
Bulk data export from Google Search Console to BigQuery
Log in to Google Search Console. Navigate to Settings and select ‘Bulk data export‘. Note that only owners or verified owners can perform bulk data exports.
Give the export destination details like cloud project ID, Dataset name, and Dataset location. To get the project ID, create a Google Cloud Project in BigQuery and activate the BigQuery API. For more details, refer to the instructions.
After configuring your export settings, confirm and click on ‘Set up export.’
The export will begin within 48 hours of launch. GSC will automatically export data daily to your BigQuery tables.
Connect BigQuery to Power BI using the native connector
Open Power BI Desktop and click ‘Get Data‘. Click on ‘More‘ to find and select ‘Google BigQuery‘ connector.
Sign in to Power BI with an organizational account or service account. Click on ‘Connect.’
In the Navigator window, select the BigQuery project, dataset, and table to load the data from.
Choose one option to load the data from the below.
- Import mode – you’ll get a copy of the data selected from BigQuery.
- DirectQuery – This will establish a live connection and the data will automatically be updated in Power BI.
After you load data from Google Search Console to Power BI, it’s ready for visualization.
What is the best way to do this?
The bulk manual transfer is free, easy, and is best for one-time transfers. However, it becomes time-consuming and prone to errors for regular updates. While the BigQuery method automates the process partly, the high storage costs make it less ideal for tight budgets. Wheras, the GSC API offers direct, customizable connections, but coding skills are required to set it up.
Finally, Coupler.io offers an easy and automated way to connect Google Search Console to Power BI. You’ll always have near real-time data in your reports and dashboards. It also supports 50+ sources and multiple destinations to create detailed multi-source reports.
