Alex Hoffmann, Managing Partner at Passion Digital and self-confessed tech nerd talks through how to integrate data from STAT into Google Looker Studio in this blog post. Alex oversees our Organic Marketing team and supports the entire agency with data and analytics, check out his other how to blog posts for more technical advice.
The Integration Problem I Found With STAT
STAT has always been my all-time favourite ranking tool – Rob Bucci created a platform that is made for SEO analysts who want to sink their teeth into the data! My only issue with the programme was integrating said data into Google Looker Studio, the tool we use to compile real-time performance for our clients. I found the current STAT connectors available limiting and VERY slow – no one wants to load a dashboard an hour before a client call!
So we took matters into our own hands and came up with a solution using a Node.js application, Google Cloud SQL database and Google App Engine. Let’s get into it – you’ll see just how easy it can be!
What You Will Need to Get This up and Running
- Access to the STAT API: To fetch the list of websites that are currently being tracked, the script needs to interact with the STAT API. Therefore, you need to have valid credentials to access the STAT API and permission to fetch the list of websites
- Access to Cloud SQL: The script needs to create two tables for each valid website in the Cloud SQL database. Therefore, you need to have access to Cloud SQL and permission to create tables in the database
- Access to Google App Engine: The syncSite() function is triggered by a cron job set up in Google App Engine. Therefore, you need to have a good understanding of how cron jobs work and how to set them up in Google App Engine
- Understanding of SQL queries: To pull data from the Cloud SQL database, you need to write SQL queries. Therefore, you need to have a good understanding of SQL queries and how to write them
- Access to Google Looker Studio: To pull data from the Cloud SQL database into Google Looker Studio, you need to have access to Google Looker Studio and permission to create custom queries
How to Integrate Moz STAT to GDS
The syncSite() function is a key part of the Node.js application that synchronises data between two web services: STAT and Cloud SQL. Once this function is run, the script fetches the list of websites from STAT. However, only websites that are currently being tracked are considered valid. This is determined by the getAllSitesSTAT() function.
For each valid website, the script creates two tables in the Cloud SQL database. One table is used to store the keywords being tracked for that website and the other is used to store the daily rankings for each keyword. Keywords are stored only once in the database, as their rankings will change over time. Each time syncSite() is called, the latest ranking score for each keyword is stored in the appropriate table.
The syncSite() function is triggered by a cron job set up in Google App Engine. The job calls the /synchronize endpoint using an HTTP request. This endpoint is accessible only via the App Engine cron job, which is configured to run the syncSite() function every day at 6:00 pm GMT. This ensures that all tracked websites are synchronised with the Cloud SQL database daily.
Because the cron job runs automatically, any new websites added to STAT will be automatically added to their own Cloud SQL tables. This eliminates the need for manual intervention to ensure that all tracked websites are synchronised properly.
The script includes error handling to help identify and resolve any issues that arise during synchronisation. The logger.js file contains code for logging errors with Bunyan to Google Cloud Monitoring. This ensures that any issues with the synchronisation process can be addressed quickly, minimising any impact on the web services being synchronised.
The website’s keyword and ranking tables can then be pulled into Google Looker Studio with this custom query.
Step-By-Step Setup
With all that in mind, here’s how you set everything up.
1. Create a Google account
- Go to the Google Sign Up page
- Fill in the required information
- Choose a username and password for your account
- Agree to the terms and conditions and click “Create Account”
- Verify your account by following the instructions sent to your phone or email
2. Setting up Google App Engine
- Go to the Google Cloud Console
- Create a new project by clicking on the dropdown menu in the top left corner and select “New Project”
- Give your project a name and click “Create”
- Once your project is created, select it from the project dropdown menu
- Go to the App Engine section of the sidebar and click “Dashboard”
- Follow the instructions to enable billing for your project if it’s not already enabled
- Once billing is enabled, go back to the App Engine dashboard and click “Create Application”
- Select your region and click “Create App”
3. Setting up Google Cloud SQL database
- Go to the Google Cloud Console
- Create a new Cloud SQL instance by clicking on the “SQL” section of the sidebar and then click “Create Instance”
- Choose the database engine you want to use (MySQL, PostgreSQL, etc.)
- Choose a name and region and set the machine type, storage and backup options
- Create a root password
- Click “Create”
- Go to the “Users” tab and create a new user with the appropriate permissions
- Go to the “Databases” tab and create a new database
- Note down connection name, database name, username and password, as you’ll need these to connect to your Cloud SQL instance from your Node.js application
Once you have these accounts in place, fork the repository and go through the read me.
Now that you have the application up and running on Google App Engine, you can sit back and let the cron job do its thing. All sites you’re tracking in STAT will sync to the Google Cloud SQL database. From there you can use this SQL code within Google Looker Studio:
How to Add Custom Query
- Open Google Looker Studio in your web browser and login to your Google account
- Click on the “Create” button in the top left corner of the screen
- Choose “Data Source” from the dropdown menu
- In the “Connect to Data” screen, search for “Cloud SQL”
- Select “Cloud SQL (MySQL)” or “Cloud SQL (PostgreSQL)” depending on the database engine you’re using and click “Connect”
- In the “Authenticate” screen, select your project from the dropdown menu and click “Connect”
- In the “Configure Data Source” screen, choose “Custom Query” as the connection type
- Enter the instance connection name, database name, username and password for your Cloud SQL instance
- In the “Query” field, enter the SQL query you want to use to retrieve data from your Cloud SQL database
- Click the “Validate” button to check that the query is valid
- Click “Create Field” to create calculated fields based on your query
- If you want to add any filters, click “Add a Filter” and select the fields you want to use as filters
- Click “Save” to save your data source
- You can now use this custom query data source in your reports and dashboards in Google Looker Studio
FAQs
Q: What is the syncSite() function?
A: The syncSite() function is a key part of a Node.js application that synchronises data between two web services: STAT and Cloud SQL.
Q: What does the script do when the syncSite() function is called?
A: When the syncSite() function is called, the script fetches the list of websites from STAT. For each valid website (determined by the getAllSitesSTAT() function), the script creates two tables in the Cloud SQL database.
Q: What is the purpose of having two tables created for each valid website in the Cloud SQL database?
A: One table is used to store the keywords being tracked for that website and the other is used to store the daily rankings for each keyword.
Q: How often does the cron job trigger the syncSite() function?
A: The cron job is set up to run the syncSite() function every day at 6:00 pm GMT.
Q: Does the syncSite() function require manual intervention to add new websites to the Cloud SQL database?
A: No, any new websites added to STAT will be immediately added to their own Cloud SQL tables, as the cron job runs automatically.
Q: Does the script include error handling?
A: Yes, the script includes error handling to help identify and resolve any issues that arise during synchronisation.
Q: How can the website’s keyword and ranking tables be pulled into Google Looker Studio?
A: The website’s keyword and ranking tables can be pulled into Google Looker Studio using a custom query – see the “Custom Query” section above.
And there you have it – we solved the STAT-GDS connectivity conundrum. Here at Passion, we pride ourselves on our proactivity and problem solving. Whether you need an innovative approach to your web performance, technical SEO support or something else, we can help you Imagine Better digital marketing. Get in touch with our team of experts today.