fbpx skip to Main Content

Recording of the workshop




How to fill out the SEO KPI Excel


Use SpyFu to fill in the keyword ranking information by clicking on the black hat below:

SpyFu (@spyfu) | Twitter

Have questions? Join the slack channel here & schedule a call with Adi here.


Why this excel?


As a decision maker, this excel will help you to determine if your content creation endeavors, budget and team are a profit center or cost center, this excel will help you to quickly review and evaluate the impact they’re having on the company’s bottom line.

As a content creator or SEO, this excel is designed to help you communicate the value of your SEO initiatives to various stakeholders and decision makers within the company. While you may love spending 1-5 hours diving into the weeds and looking at endless graphs of data, your CEO, CPO, CMO and VPs may only have a few minutes per month to dedicate to hearing how you and your team are doing and what you’re doing to help the business grow.

Keyword rankings

What are Keywords?

Keywords are the words and phrases that a user types into a search engine. If you rank at the top of the algorithms 200 – 500 criteria for a keyword your content will appear at the top of the search engine results. But what if you don’t? What if there is a keyword or phrase that your ideal client types into Google that you want to rank for, but don’t? Then you should watch the two embedded videos below.

How to find your best keywords:

The first video will help you to discover which words will be best for your business AND easiest for you to rank for.

The second video will help you to figure out the exact type of content to create to reach your SEO goals faster:

Where & how to track your keyword wins & losses:

Beginner: If you are just getting started, you may want to focus on ranking for five keywords related to variations of your brand name and use each “competitor” column to copy & paste the top three URLs that currently hold the top three spaces for each keyword.

Advanced: This KPI excel only gives you the space to track your current ranking on a few keywords. I have clients who track anywhere from 5 to 30,000 keywords per month. When this is the case we use this section to track the number of keywords they rank for on SERP 1, SERP 2 and SERP 3-5. This is very easy to find in SpyFu

Backlinks

What are backlinks?

When one webpage links to another webpage, it’s called a backlink. When the backlink comes from a website that has high domain authority (basically a website with a high amount of credibility) it can quickly increase the credibility of your own site, thereby leading to significant improvements in your SEO.

How to find backlink opportunities:

  1. Apply to be interview on 9×90.co – last year I launched a podcast that gets each guest on the show, 8 high quality backlinks with one simple interview. You can apply to be a guest on the show at 9×90.co
  2. Signup to receive alerts to be a podcast guest at RadioGuestList.com. Here you will receive regular notifications about shows that are looking for new guests. What’s the difference between this and 9×90? Most of the shows on the Radio Guest List only publish to one platform instead of 8, so it will take 8 shows to get the same number of backlinks as you do with one 9×90 interview.
  3. If you signup to be a HARO source you will receive 3 emails each day, 5 days a week from reporters looking to quote a professional in their next publication. Respond to these for a chance to be featured in top tier publications.
  4. If you’d like to know how SpyFu can help you to identify more backlink opportunities watch the video below:

Where & how to track it:

Beginner: If you have zero backlinks, start by using this section to keep track of who you reached out to for a podcast feature or publication each month.

Once you begin to get backlinks you can break this into two sections: the list of backlinks you pitched to win and the backlinks you actually scored this month.

The objective is to show progress month over month.

Advanced: By this point you should have a list of contacts you reach out to for a feature or guest interview request. As an SEO savant you know that it’s best to have these backlinks dispersed evenly throughout the months so you can use this section to drop in who you plan to publish interviews with, when.

The objective is to show that you have a concrete plan in place to grow these month over month.

Organic traffic (as a percentage)

What is organic traffic?

Organic traffic includes all website visitors coming to your website from a search engine, as a result of your amazing content ranking at the top of the search engine results when the person typed in their query. 

What are the other types of traffic?

Direct traffic includes all website visitors coming to your website through:

  1. Typing your URL into their website browser
  2. Links shared through a private messaging platform (ex: when I sent a contact test links via Telegram Messenger they showed up as “direct traffic” in google analytics).
  3. Existing bookmarks

Referral traffic includes all website visitors coming to your website from links on other websites.

Social traffic includes all website visitors coming to your website from any social media platform.

How to find what percentage of your traffic comes from organic methods:

You can find your organic traffic via Google Analytics or SpyFu (photo example of SpyFu dashboard below)

Using SpyFu you can see how your number compares to that of your top three competitors or business role models and record all of these numbers in your KPI excel. This number usually helps decision makers to see that there is room for growth with your organic traffic acquisitions.

Monetary value of this organic traffic

What is the monetary value of your organic traffic?

This is how much it would cost to purchase the traffic you are currently getting from the keywords you are currently ranking for.

How to find the monetary value of your organic traffic:

You can find this number by typing your URL into SpyFu, it is labeled “EST MONTHLY SEO CLICK VALUE”.

How to track the monetary value of your organic traffic:

This is one of my favorite numbers to track because many of the people responsible for a paid ads channels scoff at SEO until they see this number … unless your monthly click value is less than your monthly AdWords budget. When that’s the case, it’s time to kick it up a notch with your SEO initiatives.

Thanks to SpyFu you’re able to view the monetary value of your traffic as well as your top three competitors and their role models.


NOTE: The rest of the line items in the KPI excel can only be extracted by you for your verified website.


Percentage of traffic that completes a goal

What is the percentage of traffic that completes a goal?

In Google Analytics you can define a goal for your users and then track what percentage of your traffic completes this goal.

How do you find this?

Well, first you have to define your goal. Here is how to create and track a goal in Google Analytics.

Where & how to record goal completion:

You can only track this for your own business, and not your competitors. If you have multiple goals, you may want to create additional rows to keep track of how each of your goals completion rates improve month over month.

Bounce rate

What is a bounce rate?

bounce is a single-page session on your site. In Analytics, a bounce is calculated specifically as a session that triggers only a single request to the Analytics server, such as when a user opens a single page on your site and then exits without triggering any other requests to the Analytics server during that session.

https://support.google.com/analytics/answer/1009409?hl=en

The bounce rate can tell you when your marketing messaging, content descriptions and other pre-website impressions do not align with what a visitor expected to see on the website.

If and when you experience a high bounce rate you want to look at :

  1. What is driving traffic to that page that is misleading people to expect something different?
  2. What is on that page that might be driving people away?

How to find your bounce rate:

You can find the bounce rate by clicking Behavior > Site Content > All Pages

From here, you will see the Bounce Rates of specific pages listed in the 6th column.

Where & how to track your bounce rate:

Beginner: If you’re new to SEO you may want to just keep track of your average bounce rate across the site and drop it into this excel.

Advanced: If you’re a bit more serious about tracking and improving the performance of specific pages, then you will want to create additional rows to keep track of the bounce rate for each of those pages month over month.

Entrance pages (aka landing pages, aka entrances)

What is an entrance page?

An entrance page is the page through which a website visitor entered your website. (If you were a restaurant this would be the doors through which customers enter into the restaurant).

When an entrance page differs from the pages you have ranking organically OR direct traffic to (via ads, social media posts, etc), you can safely conclude that a website visitor either bookmarked that page or sent it directly to one of their connections. This type of information can tell you quite a bit about what your website visitors value most amongst your content.

Where do you find the list of your entrance pages?

These pages can be found under Behavior > Site Content > Landing Pages

How do you track entrance pages as a KPI?

If you have multiple entrance pages, you want to add multiple rows to keep track of how this list and the volume of traffic entering through these pages, changes month over month.

Exit Pages

What is an exit page?

An exit page is the page through which a website visitor leaves your website. If it is the confirmation page after they’ve completed their purchase, this is fine. If it is a page that is supposed to be encouraging someone to click through to another page to make a purchase or book a service, this is a problem. It could mean that the page is broken or has something driving potential buyers away scared.

Where do you find your list of exit pages?

These pages can be found under Behavior > Site Content > Exit Pages

How do you track exit pages as a KPI?

If you have multiple exit pages, you want to add multiple rows to keep track of how this list and the volume of traffic exiting through these pages, changes month over month.

Session Time

What is session time?

A session is a group of user interactions with your website that take place within a given time frame. For example a single session can contain multiple page views, events, social interactions, and e-commerce transactions.

https://support.google.com/analytics/answer/2731565?hl=en

How do you find your website’s session times?

To find your average session time go to Audience > Overview

To find your session time per page go to Behavior > Site Content > Content Drill down

Click “Avg. Time on Page” to view the pages that have the longest and shortest time.

What should you do with this information?

Study the pages with the longest time to see what you can extrapolate from the data. Study the pages with the shortest session time and come up with ways to improve the pages to encourage people to spend more time on those pages (or repurpose / delete them).

How & where to track your session time

  1. Keep track of your average session time across the website, in this KPI excel.
  2. Either by adding rows, or creating a new excel, record the session time of your best and worst performing pages. Track how these improve as you make changes to the content on each of these pages.

Page views

What are page views?

Page views can give an indication of how popular a post or page is. But having a high number of page views for a post doesn’t necessarily mean it is popular. Is it a good thing that you have a lot of page views per visit? Does it mean that people like to read a lot of pages on your site? Or does it mean that they can’t find what they’re looking for?

https://yoast.com/what-are-pageviews-in-google-analytics/

Where do you find your page view count?

To find your page views go to Behavior > Site Content > All Pages

Then click on Page views to look at the most viewed or least viewed pages.

How to track this data:

Add rows to the excel to track how the page views of your best and worst performing pages, change overtime.

What should you do with this data?

The number of page views increasing month over month will be of interest to executives wanting a high level recap of what you’re doing. But for this data to be useful to you and your content creation team, you will want to look at how the average amount of time on these pages varies. If the page views are high but the “avg. time on page” is low and the bounce rate and exit % are high, this would indicate that people are not finding what they are looking for. Such pages should be revised asap.

Crawl Errors

What is a crawl error?

Crawl errors occur when a search engine tries to reach a page on your website but fails at it. Let’s shed some more light on crawling first. Crawling is the process where a search engine tries to visit every page of your website via a bot. A search engine bot finds a link to your website and starts to find all your public pages from there. The bot crawls the pages and indexes all the contents for use in Google, plus adds all the links on these pages to the pile of pages it still has to crawl. Your main goal as a website owner is to make sure the search engine bot can get to all pages on the site. Failing this process returns what we call crawl errors.

https://yoast.com/crawl-errors/

How do you find your crawl errors?

If you do not have Google Search Console linked with your Google Analytics account yet, follow this guide to get started.

Once these two free tools are linked, follow this guide to identify and fix your crawl errors.

How do you track this number?

This number is recorded month over month to show decision makers how your SEO is improving not only due to data backed decisions with your content creation, but also improving the technical side of your website’s SEO. Ideally this number should be at zero, but you want to keep it in the excel to have an opportunity to consistently remind execs of the importance of your technical SEO team mates.

Financial Impact

Financial impact should be at the forefront of every decision maker’s mind. Listing off these specific numbers at the bottom of your excel will help the key decision makers to see how your SEO initiatives have impacted revenue growth.

Gross Revenue

What is gross revenue?

When gross revenue is recorded, all income from a sale is accounted for on the income statement. There is no consideration for any expenditures from any source.

https://www.investopedia.com/ask/answers/102714/what-are-difference-between-gross-revenue-reporting-and-net-revenue-reporting.asp

Where do you find your gross revenue?

Depending on how your company is structured this is a number that (at least) the CMO and CFO should both know. If you’re in a larger company they might not tell it to you until you show them the excel. Even then, be prepared for them to say that you only need to track CAC.

If it’s a startup and your employee #5, or you’re the founder, trying to figure this out for yourself checkout this resource page by Investopedia.

Where & how to track it:

Simply put your total gross revenue as of the start date of your SEO campaign. If someone wants to see the revenue growth as a percentage, or dive deeper into the numbers they can speak with the CFO directly. This is purely here to show how SEO has had a positive impact on the company’s finances.

Revenue growth with SEO

CAC

What is CAC?

photo source: https://blog.hubspot.com/service/what-does-cac-stand-for

How do you find your CAC?

Depending on how your company is structured this is a number that (at least) the CMO and CFO should both be able to readily tell you.

Where & how to track it:

Typically as the percentage of traffic coming in organically climbs, the cost of advertising drops. This should lead your CAC to decrease as your SEO improves.

You can track your CAC for each marketing channel by adding additional rows here, or you can ask whoever manages your marketing budget for the month’s average CAC.

Churn

What is churn?

The churn rate, also known as the rate of attrition or customer churn, is the rate at which customers stop doing business with an entity. It is most commonly expressed as the percentage of service subscribers who discontinue their subscriptions within a given time period. It is also the rate at which employees leave their jobs within a certain period. For a company to expand its clientele, its growth rate (measured by the number of new customers) must exceed its churn rate.

https://www.investopedia.com/terms/c/churnrate.asp

How do you calculate it?

  1. Choose a set period of time
  2. Calculate the number of customers acquired during this period.
  3. Calculate the number of customers lost (aka churned) during this same period.
  4. Divide the number of customers lost by the number of customers acquired.
  5. Multiply that number by 100 to get the value as a percentage.

How do you keep track of churn in this excel?

Here I would track churn as a % month over month. As your content develops in a way that is designed to retain customers, this number should go down. Tracking it here allows you to look at how your changes to your content have impacted your customers retention which, in my opinion, is really exciting.

LTV

What is LTV?

LTV, also known as CLTV stands for customer life time value.

Customer lifetime value is the metric that indicates the total revenue a business can reasonably expect from a single customer account. It considers a customer’s revenue value, and compares that number to the company’s predicted customer lifespan. Businesses use this metric to identify significant customer segments that are the most valuable to the company.

https://blog.hubspot.com/service/how-to-calculate-customer-lifetime-value

Example: if you run a school, you can reasonably expect the parents of a student to be paying customers until their child graduates. In this case your CLTV would be the tuition that those parents pay, minus

How do you calculate the LTV?

Purchase Frequency x Average Order Value x Average Customer Lifespan – (Acquisition Cost + Retention Cost)

https://www.clickz.com/how-to-calculate-customer-lifetime-value-cltv-the-complete-guide/228128/

How to track your LTV:

Use the above calculation to track how your LTV changes month over month. As your content leads more users to be retained for longer, the average lifetime of a user will increase, thereby steadily increasing your LTV.

This is such a key number to the success of a business, that when you present the data in this exact order, you are showing how SEO has impacted each of the most important financial corners of the business.

When you sit at your 5 month checkpoint and show how your changes in keyword rankings and backlinks relate to the LTV, you’re shutting down all of the skeptics who thought SEO was useless because this number shows that SEO is the largest growth engine in your company.


How to fill out the growth framework


Part 1

Part 2

How to implement the Hall Play:

If you need help, call Adi

Work with MD9


Eager to work with MD9? Use code Poppe27 to get 5% off any service booked before Monday.

GRO

SEO

SMO

CRO

Call Adi

Looking for more free resources like this?

9apple Resource Portal
Adi’s personal checklists

Thank you to everyone who made this event possible


Julio Poppe – EIR of WeWork Labs

Thank you to Vianda Box for providing us with amazing & authentic #VenezuelanFood, to keep us energized throughout the #SEOWorkshop.

Thank you to Alejandro Russo for sharing your amazing Candela with us. {For those of you who do not know Candela, it has one of the most beautiful bottle designs I have ever seen. Candela is an exotic spiced rum that brings to life the 500-yr old recipe of the Taino People. It is a divine combination of #DominicanRum, natural spices and honey. Candela is currently only available in the most exquisite hotel and retail locations across the US. Join us on Thursday to experience this amazing drink for yourself.}

Snapchat
Phone
Email
Snapchat
AdiSoozin
Phone
Email
Back To Top