Adding STAT Reports to Google Sheets Using App Scripts

Adding STAT Reports to Google Sheets Using App Scripts – Moz

Moz logo

Menu open

Menu close

Products

Moz Pro

Moz Pro Home

Moz Local

Moz Local Home

STAT

Mozscape API

Free SEO Tools

Competitive Research

Link Explorer

Keyword Explorer

Domain Analysis

MozBar

More Free SEO Tools

Learn SEO

Beginner’s Guide to SEO

SEO Learning Center

Moz Academy

SEO Q&A

Webinars, Whitepapers, & Guides

Blog

Why Moz

Agency Solutions

Enterprise Solutions

Small Business Solutions

Case Studies

The Moz Story

New Releases

Log out

Products

Moz Pro

Your All-In-One Suite of SEO Tools

The essential SEO toolset: keyword research, link building, site audits, page optimization, rank tracking, reporting, and more.

Learn more

Try Moz Pro free

Moz Local

Complete Local SEO Management

Raise your local SEO visibility with easy directory distribution, review management, listing updates, and more.

Learn more

Check my presence

STAT

Enterprise Rank Tracking

SERP tracking and analytics for SEO experts, STAT helps you stay competitive and agile with fresh insights.

Learn more

Book a demo

Mozscape API

The Power of Moz Data via API

Power your SEO with the proven, most accurate link metrics in the industry, powered by our index of trillions of links.

Learn more

Get connected

Compare SEO Products

Free SEO Tools

Competitive Research

Competitive Intelligence to Fuel Your SEO Strategy

Gain intel on your top SERP competitors, keyword gaps, and content opportunities.

Find competitors

Link Explorer

Powerful Backlink Data for SEO

Explore our index of over 40 trillion links to find backlinks, anchor text, Domain Authority, spam score, and more.

Get link data

Keyword Explorer

The One Keyword Research Tool for SEO Success

Discover the best traffic-driving keywords for your site from our index of over 500 million real keywords.

Search keywords

Domain Analysis

Free Domain SEO Analysis Tool

Get top competitive SEO metrics like Domain Authority, top pages, ranking keywords, and more.

Analyze domain

MozBar

Free, Instant SEO Metrics As You Surf

Using Google Chrome, see top SEO metrics instantly for any website or search result as you browse the web.

Try MozBar

More Free SEO Tools

Learn SEO

Beginner’s Guide to SEO
The #1 most popular introduction to SEO, trusted by millions.
Read the Beginner’s Guide

How-To Guides
Step-by-step guides to search success from the authority on SEO.
See All SEO Guides

SEO Learning Center
Broaden your knowledge with SEO resources for all skill levels.
Visit the Learning Center

Moz Academy
Upskill and get certified with on-demand courses & certifications.
Explore the Catalog

On-Demand Webinars
Learn modern SEO best practices from industry experts.
View All Webinars

SEO Q&A
Insights & discussions from an SEO community of 500,000+.
Find SEO Answers

August 7-9, 2023
Lock in Super Early Bird savings for MozCon

Snag tickets

Blog

Why Moz

Small Business Solutions
Uncover insights to make smarter marketing decisions in less time.
Grow Your Business

The Moz Story
Moz was the first & remains the most trusted SEO company.
Read Our Story

Agency Solutions
Earn & keep valuable clients with unparalleled data & insights.
Drive Client Success

Case Studies
Explore how Moz drives ROI with a proven track record of success.
See What’s Possible

Enterprise Solutions
Gain a competitive edge in the ever-changing world of search.
Scale Your SEO

New Releases
Get the scoop on the latest and greatest from Moz.
See What’s New

New Feature: Moz Pro
Surface actionable competitive intel

Learn More

Moz Pro

Moz Local

Moz Local Dashboard

Mozscape API

Mozscape API Dashboard

Moz Academy

Avatar

Moz Home

Notifications

Account & Billing

Manage Users

Community Profile

My Q&A

My Videos

Log Out

By: Michael MacMillan
January 12, 2022

Adding STAT Reports to Google Sheets Using App Scripts

Moz Tools
|
SEO Reporting

The author’s views are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz.

The team at MacMillan Search has generated a lot of value by combining automated STAT reports with Google Sheets. From adding ranking details to other tool’s outputs, to giving the content teams up-to-date “People Also Ask” reports, the end result has proven to be a great time-saver in our week-to-week SEO workflows by reducing manual work and providing standard outputs that easily integrate with any spreadsheet.
Why did we create this script?
STAT’s wealth of keyword rankings details is very useful for enterprise SEOs to understand both the macro and micro details of their rankings. Google Sheets is one of the most common cloud-based spreadsheets platforms, and is easy to share between teams and organizations. That’s why SEOs use both of these tools regularly when analyzing keyword data.
Despite this, documentation on how to integrate STAT into Google Sheets is limited. To address this gap, we created our own script!
It’s proven useful for several reasons:
Not everyone likes CSVs: We leverage the STAT reports to provide clients with direction. Having to download a CSV and open it every week isn’t for everyone. With this script, you can set a weekly ticket with a link to the spreadsheet, and review the output regularly.It saved us time: SEO is a marathon, not a sprint. When we identify an opportunity, there is ongoing work that will have us reviewing reports regularly. The weekly ticket approach to review a spreadsheet shaves some time off of each task, and over the course of the engagement, this saved time adds up. Cleaner output: Using Vlookups, Uniques, etc., you can create a summary page of this information, highlighting what clients and/or readers care about. You can also integrate this information with other data sources. Create automation without using an API: Automation, when done correctly, saves time. Using this script with triggers opens the door to automation.
How to implement this script
1) Create a report in STAT
The STAT knowledge base has a great resource on reports. The only thing we would get specific on is the naming of the report and the recipient email.
Naming
What you name your report is not as important as keeping it clear and concise. This makes scaling to other projects with similar reports cleaner and easier. You will also use this report name as one of the variables in the scripts.
We also suggest placing the company or project name at the end of the report name in parentheses (e.g. “(MacMillan Search)”). This makes it easier to find the report in your email.
Recipient email
It’s important to use a Gmail-enabled email for the account where you’ll be building the sheet. This way, Google has an easier time getting the app script to extract the CSV from the email.

Scheduling
For our clients, weekly data is the most useful — enough detail to spot trends, but not so much that it becomes just noise to be ignored. For reports with limited fluctuations (e.g. People Also Ask), monthly might be satisfactory.
Timing
Select “Run this report immediately” to confirm that your report works, right after creating the script. This way, you’re ready to set your triggers and let the data flow.
The rest of the settings are specific to what details you want from your report.
2) Create a Google Sheet and add the script
Create a new sheet in Google Drive under the account associated with your report’s recipient email. Then you’re ready to add the script:
1. Under the menu “Tools”, select “<> Script editor”.

2. Paste the script below into the “Script editor”.
3. A few things will need to be edited to work with your data:
var COMPANY_NAME updated to the company or project name you used while creating the STAT Reportvar REPORT_NAME updated to the name of your report minus the company name and parenthesesvar SHEET_NAME updated to the name of the sheet (the tab on the bottom) in the spreadsheet
4. Confirm the Script works by saving it, refreshing the sheet, and when the menu “Manual Update” loads, select “Import Keywords”.
5. The first time you run this you will get an “Authorization Required” pop-up:

Select “Continue”, follow the steps, and select “Import Keywords” under the menu again.

Your spreadsheet should now be populated with all of the details from your CSV.
3) Automate the population with triggers
Setting this sheet up to automatically update as the report comes out is very easy using Apps Script “Triggers”. To set up the triggers:
1. Go back into the “Script Editor”
2. Select the “alarm” icon “Triggers”

3. Select “Add Trigger”.
4. Select the function “importKws”.
5. Select event source “Time-driven”.
6. Select type of time-based trigger “Week Timer” for weekly reports, “Month Timer” for monthly reports, etc.
7. In our time zone, our reports usually come out late Sunday, so we pick early Monday morning:

8. Click “Save”

The result is a spreadsheet that regularly updates, populated by an emailed STAT report.

We’ve found many uses for this script — anywhere we reference rank. And, since a project might take time to get implemented, we can provide current ranking information without leveraging the API.
We’re curious to learn how you leverage it as well. If you find the script useful, reach out to us on LinkedIn and let us know what you’re using it for.
Click here to copy the script!

About Michael MacMillan —
Michael is an expert in SEO strategy, specializing in strategy implementation for B2B software and service companies. 13 years of experience in SEO and 22 in digital marketing have seen Mike in both agency leadership and in-house marketing roles, ultimately leading him to work with some of the largest SaaS companies as Principal Consultant of MacMillan Search.

Large-scale rank tracking and SERP analytics.

Book your STAT demo today!

Semrush Authority Score Explained

How to Create an Effective Analytics Dashboard (and How Semrush Can Help)

How to Write Copy that Generates Results [Semrush Research]

What are Stop Words? A Guide to Stop Words (with List)

Whiteboard Friday – “Chasing Your Tail”

Avoiding Zero-click Searches to Get More Traffic

类似文章