Contents
Prologue
SEO, the final frontier–
–or so we’ve been told.
See, the story of how and why I got into SEO is a long and boring one.
However, it’s also necessary.
Mainly to understand why anyone in their right mind would get into search engine optimization.
So, Dorothies of the world – buckle your seatbelts and strap your shoes ’cause
Kansas is Going AU REVOIR
(Cypher, The Matrix, always poetic in his hubris)
Interlude
The Earth was a broiling mass of energy and matter, intertwined in such a way as to confuse ongoing passerby in a cosmic dance of a seemingly progressively infinite – yet ultimately doomed chain of events that will eventually lead to its downfall.
Like Entropy.
Fast forward billions of years, and we have the World Wide Web. Tim-Berners Lee’s vision of how future communication should look like.
Then comes the dot-com bubble, Web 2.0 and the proliferation of non-Silicon-Valley-exclusive websites.
And finally, ‘the’ Google.
A tool so powerful that one of its creators had to move the idea from Russia to the U.S. in order to prevent it from falling into the wrong hands.
Which it did anyways.
A Place Called Home
Around that time, some Balkan states were still transitioning from agrarian to industrial societies.
That’s not really true, but the lack of resources, scarcity mindset (then 100% justified) and political corruption made it look like these countries were light years behind the economies of the developed world.
A fact still deeply rooted in some pockets of Balkan governance, but I digress.
Back then, people were still figuring out how to put food on the table amidst the ongoing hardships triggered by the untimely dissolve of ‘Yugoslavia,’ and with it, the dream of a unified federation-state regardless of ethnicity and personal beliefs.
Jobs were scarce, compensations were laughable and skills were low and untethered to the real demands of the market.
At the same time, morale was virtually non-existent, and a large chunk of the state capital was mostly either spent irrationally – or simply stolen or gone.
In a way, almost everyone in the then so-called ‘developed world’ had a head start over us, both on a national and individual level to boot.
Meanwhile, Back at the Ranch…
As websites got more and more complex, people got smarter. However, Google got smarter than the same people who were in turn trying to outsmart Google.
A perpetual cycle of unprecedented disappointments, cheap wins and excruciatingly exhausting mental labor.
Out of which one particular skill broke away and started a life of its own.
SEO.
Now, search engine optimization existed way before Google. In fact, you could argue that SEO was born simultaneously with the launch of the first website ever, but that’s irrelevant.
What’s true is that Google was the main driving force behind the popularization of this ‘particular skillset.’ In other words, a combination of
seemingly eclectic skills performed for a single, unifying goal: to RANK
(quoting myself, not self-indulgent in the slightest)
SEO.
Blue Pill
Regardless of the Balkan economy slowly recovering, young, smart and ambitious people were still held back by the hell-bent crab mentality of their parents and the lack of, well, just about everything.
At this point in time, I was 19 and inexperienced as all hell. Deep down however, I had a desire to do something with my life that transcended weighing tables and serving cocktails to the fringes of society.
So naturally, I drew up a plan.
It involved staying at a friend’s house, studying a relatively difficult major and working my ass off to get enough money to cover all expenses – including rent, food and utilities.
Needless to say, it didn’t work.
Time was limited and the money just wasn’t there.
Beaten by reality, I tucked my tail between my legs and went back to my old ways, which kind of sucked.
But there had to be more than that in life, right?
Outsourcing Everything
According to Tim Ferris, everything in life can be outsourced, including life itself.
Why even do something and waste valuable time when someone else can do that for you for less than your time’s worth?
Enter freelance sites.
While initially complex to develop and get people to use them, these business models have been proven to be very lucrative once and if they got off the ground.
What they do is connect clients with freelancers, at scale, and profit up to 20% of every transaction being the intermediary and all. On top of that, they also sell subscriptions, job tokens, premium accounts and more.
However, recent reports have shown that most clients are located within the States, while most freelancers on these platforms come from developing countries like Bangladesh, Pakistan – and yes – even my own country of residence North Macedonia as well.
It’s outsourcing heaven for clients, and outsourcing haven for freelancers.
But if you’re here, it’s most likely that you’re familiar with all of that.
The point I’m trying to make is I got my start on Elance.
‘A New Hope’
Ever since high school, the one thing I’ve been kind of good at was writing – creative writing in particular.
However, I was also good at problem-solving and digging up novel solutions to familiar or newly-found problems. Whoops, that’s two things.
Meanwhile, my hospitality career went nowhere so I quit that shit for good.
And guess what: a certain freelance platform called ‘Elance’ offered tons of jobs for the right candidates given they knew English or were sufficiently skillful at other stuff, like web dev or SEO.
At that point in time, I had zero ideas about what that was or how to do actual SEO.
So I set out to take on one of the two things I was somewhat good at.
Writing.
Arguing Semantics
English is an EASY language to learn. However, English is also a HARD language to master.
Roughly around that time, I knew English mostly from cartoons and Hollywood. N.A.’s cultural assimilation initiative was now working in my favor, for once.
However, as soon as I landed some writing projects here and there, I quickly realized what erroneous bubble I was living in.
My writing knowledge was limited. Sure, I knew proper syntax, spelling and the occasional phrase or saying, but something just didn’t quite add up. Things like prepositions, phrasal verbs and colloquial speech were as foreign to me as a 15th-century arquebus would’ve been to the Oracle of Delphi.
Although, she could see into the future.
Anyways.
It took a real while to learn how to use all of that properly in a sentence. Even now, parts of English grammar still elude me to this very day.
But that’s not what I wanted to talk about.
Race to the Bottom
Compared to working in F&B, freelance writing was a definite and welcome improvement at that. You could make your own hours, work from home, work without pants, and even wear pants without actually working!
It’s just, it was too good to be true. There had to be a catch.
And there was.
While offering complete freedom in how you chose to do your work, the average market pay was, in lack of a better word, uninspiring. Plus, the competition was extremely fierce and freelancers hurried to lower their rates in hopes of landing more gigs over their competitors.
Quantity was toppling quality at an exponentially worrying rate.
The thing with closed ‘barter’ economies like the now-defunct Elance, sans the potential added value at the end (if any), is that most of them work as a zero-sum game. Fixed amounts of jobs yield a fixed number of contractors. Without invoking the Pareto principle, I’d go out on a limb with a guess that around 20% of freelancers get 80% of all the projects – and that’s being sufficiently generous.
The rest are left off fighting for scraps.
Regardless, freelance writing was still a better bet than whatever job opportunities were vacant in the local economy at that time.
The Plot Thickens
Back home, my family went broke. Things started looking pretty desperate while I was often forced having to choose between paying college tuition and eating food.
To top it all off, I also broke up with my then-girlfriend, got diagnosed with type 1 diabetes, and as I would later find out, was administering the wrong insulin therapy for well over three or four years.
Fuck. As entropy would have it: whatever ‘it’ was, I wasn’t having ‘it.’
From here on out, it got from bad to worse to bad again, meaning it eventually got better. I dropped out of college and went on to take up a different major, this time with a brazen determination to go all the way regardless of any external pushbacks.
Meanwhile, freelance gigs were cozily lining up in the funnel while an unknown existential dread was looming over my head like a potent, gray cloud over a brown cartoon coyote.
But it made sense. I was steadily becoming part of the N.A. economy by virtue of just being there, present, earning my seat at the table while eventually hoping one day to transmute scarcity into abundance.
And, oh boy, was I close.
Above the Fold
After carefully honing the craft of writing like a blindered mule tied up across an augmented reality carrot, I eventually morphed into doing content writing, copywriting and every other writing form imaginable the online marketplace was in demand back then.
Including SEO content writing.
This is where it got really interesting. I was introduced to ranking as the #1 factor that actually moved the needle in online businesses, which was in turn closely tied to keywords and keyword research.
However, keywords don’t just come in out of nowhere. To perform a valid keyword research campaign, you’d needed tools like Google Keyword Planner or a combination of multiple ones like kws everywhere + kw sheeter to get the monthly search volumes in as well.
Back in the day, both kws everywhere and kw sheeter used to be free, so I used to call this duo a poor man’s keyword research full stop. In a fit of plebeian valor – I even dared to tweet about it once:
And I even got four likes. Pewdiepie move out of the way.
Meanwhile, my knowledge of search engine optimization was growing by the day, as I welcomingly stuffed bits and pieces of information into my brain like dried breadcrumbs into an undersized thanksgiving turkey cavity.
For example, did you know there is a significant difference between H1 tags and Title tags? Apparently, Title tags DO appear in SERPs (search engine results page), but NOT in the actual content on the page. Contrary, H1 tags DO appear in the page’s content, but NOT in the SERP!
However, unleashing the true power of SEO required more than just theory.
Pun notwithstanding, it required EXCEllence.
Spreadsheets
I used to dread spreadsheets. They were as scary to me as a malignant stomach ulcer was to a Mexican food aficionado.
When I learned what you can do with them however, everything changed and life got easier as a result.
Here’s a simple one.
Page URL to root domain
Say for example you have several hundred page URLs in bulk, but now you need to extract the root domain out of the full URLs for each one.
In Excel, you can do this with a simple formula:
"=LEFT(A1,SEARCH("/",A1,9))"
Note: delete quotes and interchange ‘,’ with ‘;’ depending on your Excel version to get it working
Applied across all rows, this formula will return the root domain for each URL in formula form. To transform them into text values, copy the results and PASTE them into a new column using the Paste Values option from the Paste Special dropdown menu.
This is especially useful if you work with large sets of page URLs from Screaming Frog, Ahrefs or Semrush.
Combine multiple cell values into one cell
How about:
"$B$3&" - "&TEXT(B4,"#%")&CHAR(10)&$C$3&" - "&TEXT(C4,"#%")&CHAR(10)&$D$3&" - "&TEXT(D4,"#%")&CHAR(10)&$E$3&" - "&TEXT(E4,"#%")&CHAR(10)&$F$3&" - "&TEXT(F4,"#%")"
the protein sequence for turmeric curcumin does indeed look strange
I won’t even try to explain this one, but what it essentially does is it combines multiple cell values into a single cell, five in particular, and then lists these values one under the other in a new row in the same cell.
The ‘$’ sign denotes an absolute cell value, so the cell number doesn’t change when applying the fill handle function to all remaining rows in the spreadsheet.
It’s definitely a clumsy solution to a problem I had with formatting data from Google Trends and into Infogram (web-based infographic creation software), but it’s still better than entering and sorting out all of the values manually.
Remove text string from middle of a text string in a cell
What about removing a certain number of text characters from the middle of a text string in a cell?
Turns out, that’s possible too:
"=REPLACE(A1;1;10;"")"
you’d think you’d use REMOVE to remove, but REPLACE works just as well
Here, the REPLACE function helps us replace any number of text characters in the middle of a text string with nothing, effectively deleting it. ‘A1’ denotes the cell to which the function is executed. The first number (‘1′) refers to where the text string begins, while the second number (’10’) is referring to the length of the string – including whitespace.
Where’d you actually use this? Personally, I found a great use for it in removing a particular set of identical text characters across a large number of cells filled with an identical text string.
Seriously, try it out. Works wonders and saves time like a heavily potent double-whammy but without the setback.
Additional Tips & Tricks
This chapter is all about solving micro-problems encountered in the wild effectively and on a budget – if any.
Free Content Ideas
To start off, let’s consider getting content ideas on a ‘0’ budget:
- Open browser
- Enter a competitor URL in the address bar, for ex. ‘https://competitor1.com’
- Add the following at end of URL: ‘sitemap_index.xml’
- Your address now looks like this: ‘https://competitor1.com/sitemap_index.xml’
Boom! Now you get to ‘milk’ some free content ideas from your competitors effectively and on a zero budget. You can further sort these out by date, image count and more.
Note: some sitemaps will be uploaded under different names, for ex. ‘sitemap.xml’, ‘sitemap_1.xml’, etc. Try some of the combinations above and eventually you’ll get it right.
Scraping ‘People Also Ask’
Here’s how you can get multiple ‘people also ask’ queries in from Google:
- Download ‘Scrape’ for Chrome – https://chrome.google.com/webstore/detail/scraper/mbigbapnjcgaffohmbkdlecaccepngjd?hl=en
- Type your query in and do a Google Search
- Click on some of the ‘People Also Ask’ results (bottom-up preferable)
- Open the Scrape plugin
- Enter //g-accordion-expander/div/div and select Xpath
- Click Scrape
- Click copy to clipboard
- Paste in Excel or GSheets
- Use function LEN to calculate the length of characters in all cells
- Copy column and paste/replace with ‘values only’
- Sort from Smallest to Largest
Note: the path ‘//div[contains(@id,’ eobd_’)]/div’ is not currently working at the time of publishing this post.
Filtering out Gmail messages
Sorting and filtering out messages in your Gmail can save you lots of time which you can then allocate to creating outdated Photoshop memes:
- Open up Gmail
- Go to ‘Settings’
- Choose ‘See All Settings’
- Choose ‘Labels’
- Choose ‘Create New Label’ and enter a new label name
- Now go to ‘Filters and Blocked Messages’
- Click ‘Create New Filter’
- In the ‘Form’ field, enter the email (one) or emails (multiple) to filter out and direct all messages from your preferred email addresses into a single label, like so: example@company.com OR example2@company.com OR example3@company.com… OR exampleN@company.com (the OR operator allows you to enter multiple email addresses to get them picked up in a single label without creating additional filters)
- Once done entering your email addresses, click ‘Create Filter’
- From the menu, check the following boxes: ‘Skip the inbox (archive it),’ ‘Apply the label – then choose your new label,’ ‘Never send it to SPAM’ and ‘Also Apply Filter to N matching conversations’
- Click ‘Create Filter’
…and done. Check your new label to see if the filters work, and that’s about it.
Conclusion
Phew. That should roughly give you a general idea of what approaches I use to get things moving in the SEO world. It’s intentionally devoid of theory because diving down in the trenches, in my opinion, is way more important than arguing E-A-T with a bunch of monocle-sporting folk who actually have the budget to run paid ads instead of organic campaigns any day of the week – including Tuesday.
Anyways, that’s a wrap.
For any questions, comments, concerns or answers to the meaning of life or the existence of Free Will, you can get in touch with me at
business[@]adrijanarsovski[dot]com
Until next time,
Adrijan