Connect with us

MARKETING

Adding STAT Reports to Google Sheets Using App Scripts

Published

on

Adding STAT Reports to Google Sheets Using App Scripts

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:

Advertisement
  1. 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.

  2. 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.

  3. 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.

  4. 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.

Advertisement

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”

1642005555 95 Adding STAT Reports to Google Sheets Using App Scripts

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 Report

  • var REPORT_NAME updated to the name of your report minus the company name and parentheses

  • var SHEET_NAME updated to the name of the sheet 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:

Advertisement
1642005555 966 Adding STAT Reports to Google Sheets Using App Scripts

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”

    1642005555 669 Adding STAT Reports to Google Sheets Using App Scripts

    3. Select “Add Trigger”

    4. Select the function “importKws”

    5. Select event source “Time-driven”

    Advertisement

    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:

    1642005555 618 Adding STAT Reports to Google Sheets Using App Scripts

    8. Click “Save”

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

      1642005555 231 Adding STAT Reports to Google Sheets Using App Scripts

      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.


Source link
Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address

MARKETING

How To Combine PR and Content Marketing Superpowers To Achieve Business Goals

Published

on

A figure pulls open a dress shirt to reveal the term PR on a Superman-like costume, reflecting the superpower resulting from combining content and PR.

A transformative shift is happening, and it’s not AI.

The aisle between public relations and content marketing is rapidly narrowing. If you’re smart about the convergence, you can forever enhance your brand’s storytelling.

The goals and roles of content marketing and PR overlap more and more. The job descriptions look awfully similar. Shrinking budgets and a shrewd eye for efficiency mean you and your PR pals could face the chopping block if you don’t streamline operations and deliver on the company’s goals (because marketing communications is always first to be axed, right?).

Yikes. Let’s take a big, deep breath. This is not a threat. It’s an opportunity.

Advertisement

Reach across the aisle to PR and streamline content creation, improve distribution strategies, and get back to the heart of what you both are meant to do: Build strong relationships and tell impactful stories.

So, before you panic-post that open-to-work banner on LinkedIn, consider these tips from content marketing, PR, and journalism pros who’ve figured out how to thrive in an increasingly narrowing content ecosystem.

1. See journalists as your audience

Savvy pros know the ability to tell an impactful story — and support it with publish-ready collateral — grounds successful media relationships. And as a content marketer, your skills in storytelling and connecting with audiences, including journalists, naturally support your PR pals’ media outreach.

Strategic storytelling creates content focused on what the audience needs and wants. Sharing content on your blog or social media builds relationships with journalists who source those channels for story ideas, event updates, and subject matter experts.

“Embedding PR strategies in your content marketing pieces informs your audience and can easily be picked up by media,” says Alex Sanchez, chief experience officer at BeWell, New Mexico’s Health Insurance Marketplace. “We have seen reporters do this many times, pulling stories from our blogs and putting them in the nightly news — most of the time without even reaching out to us.”

Acacia James, weekend producer/morning associate producer at WTOP radio in Washington, D.C., says blogs and social media posts are helpful to her work. “If I see a story idea, and I see that they’re willing to share information, it’s easier to contact them — and we can also backlink their content. It’s huge for us to be able to use every avenue.” 

Advertisement

Kirby Winn, manager of PR at ImpactLife, says reporters and assignment editors are key consumers of their content. “And I don’t mean a news release that just hit their inbox. They’re going to our blog and consuming our stories, just like any other audience member,” he says. “Our organization has put more focus into content marketing in the past few years — it supports a media pitch so well and highlights the stories we have to tell.”

Storytelling attracts earned media that might not pick up the generic news topic. “It’s one thing to pitch a general story about how we help consumers sign up for low-cost health insurance,” Alex says. “Now, imagine a single mom who just got a plan after years of thinking it was too expensive. She had a terrible car accident, and the $60,000 ER bill that would have ruined her financially was covered. Now that’s a story journalists will want to cover, and that will be relatable to their audience and ours.” 

2. Learn the media outlet’s audience

Seventy-three percent of reporters say one-fourth or less of the stories pitched are relevant to their audiences, according to Cision’s 2023 State of the Media Report (registration required).

PR pros are known for building relationships with journalists, while content marketers thrive in building communities around content. Merge these best practices to build desirable content that works for your target audience and the media’s audiences simultaneously.

WTOP’s Acacia James says sources who show they’re ready to share helpful, relevant content often win pitches for coverage. “In radio, we do a lot of research on who is listening to us, and we’re focused on a prototype called ‘Mike and Jen’ — normal, everyday people in Generation X … So when we get press releases and pitches, we ask, ‘How interested will Mike and Jen be in this story?’” 

3. Deliver the full content package (and make journalists’ jobs easier)

Cranking out content to their media outlet’s standards has never been tougher for journalists. Newsrooms are significantly understaffed, and anything you can do to make their lives easier will be appreciated and potentially rewarded with coverage. Content marketers are built to think about all the elements to tell the story through multiple mediums and channels.

Advertisement

“Today’s content marketing pretty much provides a package to the media outlet,” says So Young Pak, director of media relations at MedStar Washington Hospital Center. “PR is doing a lot of storytelling work in advance of media publication. We (and content marketing) work together to provide the elements to go with each story — photos, subject matter experts, patients, videos, and data points, if needed.”   

At WTOP, the successful content package includes audio. “As a radio station, we are focused on high-quality sound,” Acacia James says. “Savvy sources know to record and send us voice memos, and then we pull cuts from the audio … You will naturally want to do someone a favor if they did you one — like providing helpful soundbites, audio, and newsworthy stories.”  

While production value matters to some media, you shouldn’t stress about it. “In the past decade, how we work with reporters has changed. Back in the day, if they couldn’t be there in person, they weren’t going to interview your expert,” says Jason Carlton, an accredited PR professional and manager of marketing and communications at Intermountain Health. “During COVID, we had to switch to virtual interviewing. Now, many journalists are OK with running a Teams or Zoom interview they’ve done with an expert on the news.”

BeWell’s Alex Sanchez agrees. “I’ve heard old school PR folks cringe at the idea of putting up a Zoom video instead of getting traditional video interviews. It doesn’t really matter to consumers. Focus on the story, on the timeliness, and the relevance. Consumers want authenticity, not super stylized, stiff content.”

4. Unite great minds to maximize efficiency

Everyone needs to set aside the debate about which team — PR or content marketing — gets credit for the resulting media coverage.

At MedStar Washington Hospital Center, So Young and colleagues adopt a collaborative mindset on multichannel stories. “We can get the interview and gather information for all the different pieces — blog, audio, video, press release, internal newsletter, or magazine. That way, we’re not trying to figure things out individually, and the subject matter experts only have to have that conversation once,” she says.

Advertisement

Regular, cross-team meetings are essential to understand the best channels for reaching key audiences, including the media. A story that began life as a press release might reap SEO and earned media gold if it’s strategized as a blog, video, and media pitch.

“At Intermountain Health, we have individual teams for media relations, marketing, social media, and hospital communications. That setup works well because it allows us to bring in the people who are the given experts in those areas,” says Intermountain’s Jason Carlton. “Together, we decide if a story is best for the blog, a media pitch, or a mix of channels — that way, we avoid duplicating work and the risk of diluting the story’s impact.”

5. Measure what matters

Cutting through the noise to earn media mentions requires keen attention to metrics. Since content marketing and PR metrics overlap, synthesizing the data in your team meetings can save time while streamlining your storytelling efforts.

“For content marketers, using analytical tools such as GA4 can help measure the effectiveness of their content campaigns and landing pages to determine meaningful KPIs such as organic traffic, keyword rankings, lead generation, and conversion rates,” says John Martino, director of digital marketing for Visiting Angels. “PR teams can use media coverage and social interactions to assess user engagement and brand awareness. A unified and omnichannel approach can help both teams demonstrate their value in enhancing brand visibility, engagement, and overall business success.”

To track your shared goals, launch a shared dashboard that helps tell the combined “story of your stories” to internal and executive teams. Among the metrics to monitor:

  • Page views: Obviously, this queen of metrics continues to be important across PR and content marketing. Take your analysis to the next level by evaluating which niche audiences are contributing to these views to further hone your storytelling targets, including media outlets.
  • Earned media mentions: Through a media tracker service or good old Google Alerts, you can tally the echo of your content marketing and PR. Look at your site’s referral traffic report to identify media outlets that send traffic to your blog or other web pages.
  • Organic search queries: Dive into your analytics platform to surface organic search queries that lead to visitors. Build from those questions to develop stories that further resonate with your audience and your targeted media.
  • On-page actions: When visitors show up on your content, what are they doing? What do they click? Where do they go next? Building next-step pathways is your bread and butter in content marketing — and PR can use them as a natural pipeline for media to pick up more stories, angles, and quotes.

But perhaps the biggest metric to track is team satisfaction. Who on the collaborative team had the most fun writing blogs, producing videos, or calling the news stations? Lean into the natural skills and passions of your team members to distribute work properly, maximize the team output, and improve relationships with the media, your audience, and internal teams.

“It’s really trying to understand the problem to solve — the needle to move — and determining a plan that will help them achieve their goal,” Jason says. “If you don’t have those measurable objectives, you’re not going to know whether you made a difference.”

Advertisement

Don’t fear the merger

Whether you deliberately work together or not, content marketing and public relations are tied together. ImpactLife’s Kirby Winn explains, “As soon as we begin to talk about (ourselves) to a reporter who doesn’t know us, they are certainly going to check out our stories.”

But consciously uniting PR and content marketing will ease the challenges you both face. Working together allows you to save time, eliminate duplicate work, and gain free time to tell more stories and drive them into impactful media placements.

Register to attend Content Marketing World in San Diego. Use the code BLOG100 to save $100. Can’t attend in person this year? Check out the Digital Pass for access to on-demand session recordings from the live event through the end of the year.

HANDPICKED RELATED CONTENT:

Cover image by Joseph Kalinowski/Content Marketing Institute

Source link

Advertisement
Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Trends in Content Localization – Moz

Published

on

Trends in Content Localization - Moz

Multinational fast food chains are one of the best-known examples of recognizing that product menus may sometimes have to change significantly to serve distinct audiences. The above video is just a short run-through of the same business selling smokehouse burgers, kofta, paneer, and rice bowls in an effort to appeal to people in a variety of places. I can’t personally judge the validity of these representations, but what I can see is that, in such cases, you don’t merely localize your content but the products on which your content is founded.

Sometimes, even the branding of businesses is different around the world; what we call Burger King in America is Hungry Jack’s in Australia, Lays potato chips here are Sabritas in Mexico, and DiGiorno frozen pizza is familiar in the US, but Canada knows it as Delissio.

Tales of product tailoring failures often become famous, likely because some of them may seem humorous from a distance, but cultural sensitivity should always be taken seriously. If a brand you are marketing is on its way to becoming a large global seller, the best insurance against reputation damage and revenue loss as a result of cultural insensitivity is to employ regional and cultural experts whose first-hand and lived experiences can steward the organization in acting with awareness and respect.

Source link

Advertisement
Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

How AI Is Redefining Startup GTM Strategy

Published

on

How AI Is Redefining Startup GTM Strategy

AI and startups? It just makes sense.

(more…)

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

Trending

Follow by Email
RSS