How to build a quick and saucy SEO report...for FREE

Using Google Data Studio [template included]

Whenever I do my in-depth keyword research to highlight the next quarter's focus keywords, I always have this feeling I'm missing something. 

Analytics and tracking. 

Maybe that's because I'm a growth and digital marketer by trait, I always feel the need to track and measure everything. 

But that's the thing, why should SEO (search engine optimisation) be any different? You spend hours researching the top keywords to focus on and create content around.

And for what?

Me building the SEO report...yes I need 5 laptops ;)

We need to be able to see, clearly, how this work pays off.

You: "But Rupert, we already have Google Search Console. What more do you need?"

Sure, Google Search Console (GSC) is great. We can see impressions, clicks, click through rates and average position data in the past 16 months (as of recently it was a 3 month look back window)!

The issue I had with this is that it's hard to see real trends as the data is sorted only by day. So you can't sort for weekly, monthly or yearly trends. And if you work for a B2B business, you'll get serious weekend drop-offs like below. 

3 months of Google Search Console reporting

I tried to work with it and spent an inordinate amount of time exporting individual keyword data sets and adding them to a single graph to get a wholesome view on our SEO.

I tried. Got fed up. It was awful. 

Too much work little payoff 

You (again): "But Rupert, why build your own when there are soooooo many SEO-focused businesses out there with awesome products? Like Ahrefs, Moz, SEMrush, SEOClarity to name a few."

Sure thing.

If you have the cash to spend, go for it. But if you're like many early starters, you need to hack a few things together until you have that cash to burn on a full suite product. 

So I decided to build a workaround. 

A step-by-step guide to building your SEO report

I'm not sure about you, but I'm a visual person.

I want to be able to open up a report and in a split second understand the SEO performance of my focus keywords. A dashboard that shows the overall health of my keywords and much more. 

Below you will find the steps I took to build that dashboard, that has now become the go-to SEO report for our growing team of 20 marketers. 

And just to reiterate for the umpteenth time. IT'S FREE!

Step 1. Google Data Studio

First step to building your saucy SEO report is to connect all the right data sources to Google Data Studio. 

You will want to connect your Google Analytics and Google Searcon Console data sources to the same data studio project. Don't worry it's easy. 

View fullsize

View fullsize

View fullsize

View fullsize

View fullsize

At this stage you're going to want to connect both "Site Impression" and "URL Impression" tables from the search console data source as they provide equally relevant data points. 

Step 2. Report Structure

The most logical way for me to structure the report is as follows.

1. Page 1 - overview page on top performing keywords/queries

2. Page 2 - seo focus keyword 1

3. Page 3 - seo focus keyword 2

4. Page 4 - seo focus keyword 3

Simple. Saucy

Now we have the three data sources connected, it's time to pull some data through. 

The first page gives you an overall picture of which keywords are ranking highest, which are driving the most impressions and which are driving the most clicks to your site. 

Simply, create a table, pull in query data from search console and add in the metrics you want to see, i.e. clicks, impressions, average position. Be sure to compare these data points by clicking compare to the previous period.

High level. But useful

On my first page, I have three of the same tables just sorted differently: 

• by average position and impression - wanted to know which keywords were ranked number 1 and how many impressions they were driving

• by impression - wanted to know the most visible keywords

• by clicks - wanted to know which keywords are driving most clicks

These 3 tables should give you enough of a high-level feel of how your keywords are doing. 

Step 3. Deep dive your focus keywords

These are the best pages. It's important to get them right. They are the heart of the report. 

They're also the most fun to create. 

Before we jump into it, you must ask yourself what you are trying to find out. Which questions are you trying to answer? And how should I present the data to do so?

I came up with these four:

1. What is the average position over time for the focus keyword?

2. How much traffic is the keyword driving to our site?

3. What types of content are people interested in around this keyword?

4. What are the opportunities for growing traffic in this area even more?

These questions helped guide the layout of the deep dive pages. 

Your saucy SEO report that's quick and easy

With this report, the aim was to answer the above questions:

1. What is the average position over time for the focus keyword?

Over the 3 month period, you can see the average position has been improving ever so slightly. The positioning has decreased by ~8% to 5.9%. NB: down is a good thing - down means closer to #1, aka ranked first. 

Data source used: Google Search Console

Filter created: "query" contains [enter focus keyword]

2. How much traffic is the keyword driving to our site?

In the same 3 month period, traffic had increased, plateaued and now decreasing. However, the visualisation could be due to the date layout of YYYww.

Meaning, the days are now bundled into weeks and can potentially cutoff some days. That said, we've had +65k unique pageviews to the site from our focus keywords (woop woop).

Data source used: Google Analytics

Filter created: "landing page" contains [specific url]

3. What types of content are people interested in around this keyword?

So I've blanked this out, for privacy reasons, but what you'll see is a big list of all our highest clicked-on landing pages.

I.e. the ones driving the most amount of traffic. Good thing to understand is why are they clicking on these landing pages? Is it the headline? Does that specific landing page tap into a key question your audience is looking for?

Data source used: Google Search Console

Filter created: "query" contains [enter focus keyword]

4. What are the opportunities for growing traffic in this area even more?

This is a great table to create. I pulled in through search console the query data and showed only the keywords ranking between 4-20, based on the seed keyword.

Data source used: Google Search Console

Filter 1 created: showed queries with average positions between 4-20 (i.e. keywords that have room for improvement).

Filter 2 created: query containing [enter focus keyword]

Step 4. put it all together

Now you've got your front page SEO overview and your deep-dive focus keyword pages, put it all together and you have yourself a fully fledged SEO report. 

Which doesn't cost $100+ per month. Again, IT'S FREE!

Conclusion

In this essay, we covered a lot. 

Talked about the limitations of using platform-only data (using search console only to see performance).

How to connect different data sources into the same data studio sheet.

How to give a good overview of SEO health.

And how to setup deep-dive pages on your focus keywords. 

What we didn't cover was design and layout of your reports. Because that's up to you. You have the tools now. 

Go create your FREE SEO report and get that next promotion because your boss will notice you just saved them at least $1,200 a year. 

Template Giveaway

Best thing to do is connect with me on LinkedIn (I'm currently accepting all connections) and ask for the template there.

.....

As always with my essays, I try to help others through my own learnings. If you got something useful out of my musings then please let me know either with a comment, share or even subscribe on my blog if you're feeling quirky. 

I really appreciate it!

You can find more of my writing about growth, strategy and user acquisition on my site Bright Fund. Alternatively you can find my Medium publication, Bright Fund and give it a 👏.

Are There Really Only 19 Channels For Growth?

19 channels for hockey stick growth

The ever sought after hockey stick growth graph

When strategising on how to grow Dribble, the book "Traction" by Justin Mares and Gabriel Weinberg (which is amazing), was a go-to resource. They did a tonne of research into customer acquisition channels, interviewed startups and founders to hear their success stories on how they grew, and ended up with a pretty concrete list of 19 channels for growth.

The theory behind this list is that they are the only true channel that spur on growth, and within it you can find the 1-2 channels that will scale your business. However, I wanted to dig a little deeper into a few success stories to understand whether we need to add, subtract or keep the same number of channel groups on this list. 

Customer Acquisition Channels

Here they are in all their glory

Let's dive in

Andrew Chen wrote an incredibly fascinating article about "what's next in growth", where he actually looked in the past to predict the future. Personally this article gave me greater foundation in how I foresee the evolving growth landscape. If you don't want to read the whole article here are my key takeaways: 

"technology changes but people stay the same"

-

"ignore quick growth hacks. Only trust ideas that are 100+ years old"

-

"growth opportunities will come from taking classic strategies - the stuff that's been around for 100 years - that are fundamentally anchored on human behavior, and anchoring them on new technologies while executing them"

-

"growth opportunities follow: classic strategies x new platforms x smart execution"

When you think of growth you think cutting edge, you think new, you think tech-enabled. These are all true, however, the channels listed above aren't new concepts. They are not new channels that have only arisen since the launch of Uber, Airbnb, Facebook or Slack. They all came about many years ago - think pre-world wide web - and will remain 100+ years longer. As Andrew Chen quite rightly stated, they are anchored on human behaviour and we all know that hasn't changed...ever.

Let's look at some of the channels we might perceive as "new":

Engineering for growth (side projects)

A beautiful FREE stock photo from Unsplash

Definition: the building of a product or service that can have a positive impact to your core product or service.

Who - Crew and Unsplash

What did they do - Crew gaveaway their remaining high-res photos on a standalone site, Unsplash, with the goal to funnel in a new batch of users. The site was an mvp, they posted it on hackernews and waited. Within 24 hours they maxed out Google Sheets subscriber limit (it's 20k if you don't know) and funneled across a whole new set of users to Crew that envigorated their business. 

Why did they do it - they were running low on cash for Crew and needed to change their business model to stay afloat. Not only does this channel mean one builds a new product to influence another, it is anchored on a seperate growth channel, virality (wom)

Human bahaviour that influenced growth - since they gave away a VALUABLE in-demand resource FOR FREE, the community responded extremely well, spreading the word organically.

Old example of when it came about - the concept of side projects to influence a core project/business is not a new phenomenon. You see this a lot in the music industry. For instance, in 1978 the group KISS decided to run side projects where each band member simultaneously launch with a solo album to help boost the overall bands popularity and sales. Just like Crew and Unsplash, it worked. 

Existing platforms (OPNs)

The now infamous Airbnb cold email to Craigslist-ers about hosting on Airbnb. From the anonymous Jill D...who was most likely Brian Chesky himself

Definition: leveraging existing platforms or networks to grow your product/service. 

Who - Airbnb

What did they do - they found that the majority of their users and the overall market were listing and renting their houses on Craigslist. Ingeniously, they developed a way in which any Airbnb listing was given the option to list on Craigslist (even though it was not allowed), giving them much more reach than organically through their own products. All click throughs were re-directed to Airbnb and now we have a global phenomenon. This was just the beginning...they managed to do A LOT more to make it where they are now

Why did they do it - through market research their target market was already on Craigslist and wanted to leverage their network to bring on growth

Human bahaviour that influenced growth - using OPNs, you leverage any behaviour the existing network has already tapped into. This means the behaviours vary depending on network and whether they already "own" your target market.

Old example of when it came about - Brownie Wise from Tupperware, back in the 1950s, leveraged other peoples networks on a smaller scale (it can also be seen as an affiliate network). By tapping into the small gatherings of women to showcase the Tupperware product and sharing revenues with the women, Wise built a vast network of other peoples networks to grow the brand. 

Virality (word of mouth)

Snapchat logo - how's your Snap inc. stock doing?

Definition: in the case below we are using an organic viral channel, word of mouth. Not to be confused with artificial viral channels such as referrals or viral loops.

Who - Snapchat

What did they do - initially launched the product in college campuses (a select few) in the hope of initiating natural word of mouth. Although this tactic didn't succeed as planned, launching the product in select high schools did. Within a a few months the product became viral with main source of growth being WOM.

Why did they do it - cheap quality growth. It is inherent to the product that the word be spread because people need friends to send (naked pictures) snaps to. The network only gets stronger the bigger it gets. It's also the dream if your product inherently attracts new customers by being freaking awesome. Everyone wishes it, but only the few can capatilise. Has to be both organic to customer experience and expertly executed.

Human bahaviour that influences - Evan Spiegel believes Snapchat "makes communication a lot more human and natural". In this broad statement, he must also be referring to another human behaviour, sex. People enjoy sending NSFW pics to people since they last between 1-10 seconds and given the relative toxic environment high schools can be prone to, Snapchat was a great fit and spread like wild fire. Of course not all snaps were nudes, but you get my drift.

Old example of when it came about - a long long time ago, before internet, advertising, or any other formal marketing technique. If something was good, people told other people. Simple. But in 1970 - George Silverman, a psychologist, pioneered word-of-mouth marketing when he created what he called "teleconferenced peer influence groups" in order to engage physicians in dialogue about new pharmaceutical products. And that is all I will be talking about pharmaceuticals...

Influencer Marketing

Kim and Kanye = influencers

Definition - leveraging the network, influence, brand and trust from a person of interest (someone with a following) to grow your product or service

Who - there are hundreds of big companies who use influencer marketing, from the likes of Nike, Adidas, (pretty much any retailer), but I thought I'd give a little guy a chance - Dribble

What we did - found a niche on Instagram and Youtube across football fan pages of Premier League teams and "signed" their accounts to our "network". This later became a standalone influencer agency called Ninetylabs. We designed all creative to be posted across accounts and managed the campaigns across 100+ influencers totalling a reach of 10m football fans.

Why we did it - drive growth from a very active following and capatilise on the influencers trust. The networks we were creating already owned our target market.

Human behaviour - trusting a peer. The influencers build a following based on trust of their brand, tone of voice and opinions. Can be very powerful when coupled with a product/service that matches their audience

Old example of when channel came about - influencer marketing came about in the late 1800s with the likes of "Aunt Jemima - Pancake Flour", leveraging a fictitious persona to be a trusting face, name and character to sell flour. This was an artificial form of influencer marketing, since they made up the character (similar to Santa Claus from Coca-Cola). A little later in the 1920s Babe Ruth (the baseball player) was used to sell Tobacco chews. That could be the first real example of influencer marketing leveraging his fame and influence across the sporting communities in the US. Very interesting to see now how the adoption rate of influencer marketing has sky rocketed with many agencies popping up, such as, Butter Milk. 

What this means

The common denominator here is that all channels were invented/created/established a long time ago (50-100+ years ago). You can pick and choose any channel on the list and find the roots run deep around the cusps of revolutions and the beginnings of corporations. In light of this, I do believe the list is robust enough to maintain all 19 channels.

From the early days of these channels, the medium or platforms which customers are acquired has changed, however, the human behvaiour that drives said acquisitions has not.

That means one can predict the channels will remain the same while the platforms and technology will differ greatly.

The Future of Growth   

With this in mind, the new technologies will be leveraged across the classic channels to be executed in a way that will drive real growth.

Inspired by @andrewchen

Using the above image, try to imagine ways the classic strategies can work with emerging technologies. Such as:

• influencer marketing with VR

• building a machine learning side project to help facilitate growth 

• leveraging A.I to analyse creative and copy to make better design decisions across your online advertising campaigns.

Today you are starting to see some of the lines blurring, especially in the advertising industry where automation is taking over ad exchanges through programmatic. 

In conclusion, this has lead me to believe the customer acquisition channel list is robust enough to maintain all 19 channels. Each channel has a deep rooted history anchored around human behaviours. This pairing is vital and can be leveraged across any emerging technology, and that's where the innovation will lie. Emerging tech and emerging platforms will be where people win and lose. Keep an eye on how you can use the 19 channels and strategies across the new platforms and tech popping up. There is a movement happening, better get in front of it now. 

In addition, the channels themselves are intentionally quite broad terms and we should actually call them "channel groups". Reason being, you can have a load more sub-channels within each of them e.g. "content marketing" can mean either podcasts, infographics, video, long form blog posts etc etc. So one channel group can represent 5-10 channels, meaning the list could actually have the potential of being 190 in total. Other groupings are: "viral marketing" where you can have organic virality or artificial virality; "influencer marketing" where that can be guest writing on blogs or influencers on social networks. You catch my drift. 

Brains

FYI when using the customer acquisition list to form your growth strategy, in my last essay on the "4 things I wish I knew when cofounding my startup", I reference you should not go through this list in an organised fashion from top to bottom, rather through deductive reasoning by looking at your core business model and specific KPIs to judge where to begin.

E.g. if you're a mobile game whose goal is to hit 1m active users within the first year, 1-1 "sales" maybe won't be that channel to scale your business. however, would probably work if you're a B2B SaaS business selling enterprise business intellignence tools (since they'd probably be looking for 10 enterprise clients).

From there you can use a few growth processes to get the most out of each channel. Such as the G.R.O.W.S process, courtesy of the Growth Tribe.

P.S in light of the 19 channels for growth, the way Justin and Gabriel (yes we're on a first name basis) prioritise where to focus is through bullseye targeting. I will jump into in a follow up essay about this and how I aprroached it with my team.

If you like the above, feel free to check out "how I launched my iOS app" and how I'd improve it. And don't forget to signup to my newsletter for early access to content.