Author: johannes

Hi, I am Johannes! 🚀Digital Nomad, Former Head of Growth @Truly (trulyexperiences.com), Former PayPal Global SEO lead & Paid Media marketer, 500 Startups mentor & growth consultant. In my spare time, I love 🚴‍♂️, ✈️🌏🗺,🎾. Get in touch: via Email or Tweet @joradig.

What marketing really is

It seems that something’s really happened with the reputation of marketing in the last couple of years.

The rise of Silicon Valley and with it tech companies, the lean startup movement and others have had a significant impact on a lot of things – including how most companies approach marketing.

Some people say this movement is also responsible for giving a bad name to what people consider marketing.

It seems like some people don’t even want to take this profession because of a lack of social status.

I tell you: Don’t listen to them. You can be a marketer with pride.

What most people THINK marketing is, is wrong.

You, fellow marketer, have the power to change the course of your company, but you need to first make sure your company understands what your job entails.

What do you believe Marketing means?

Marketing is a lot more than just putting ads on Facebook.

“Marketing is […] the business process of identifying, anticipating and satisfying customers‘ needs and wants.” (Wikipedia)

Maybe read it again.

This doesn’t say “a way to try to sell some shitty product that people don’t want by showing it to them over and over again”.

No.

In fact, it says the total opposite.

  1. Marketing is about identifying and anticipating customers needs and wants
  2. Marketing is about satisfying customers’ needs and wants

Wow, that’s big, right?

Let’s look at an even more popular concept, the Marketing Mix consisting of the 4Ps (McCarthy in 1964):

  1. Product: “The product aspects of marketing deal with the specifications of the actual goods or services, and how it relates to the end-user’s needs and wants. The product element consists of product design, new product innovation, branding, packaging, labelling.”
  2. Price: “This refers to the process of setting a price for a product.”
  3. Place: “This refers to how the product gets to the customer (aka distribution).”
  4. Promotion: “This fourth P is focused on providing a message to get a response from consumers.”

🤯

Did you read it?

Yes, PRODUCT is the first P in the Marketing Mix. PRODUCT IS A PART OF MARKETING. 👀

Next time you speak to your product manager, say: hey, did you know that you are a marketer?

I can already see the look of DISGUST on their face.

So, how did that happen?

I can only imagine.

Marketing is a big job which involves a lot of areas. Naturally, responsibilities are getting divided.

Usually, someone looks after product. Someone else will work on the physical distribution of your product. Someone else probably takes on pricing, and so on.

And then someone else looks after what’s left of marketing.

But here’s the problem: How are they meant to do their job when their responsibilities have been dismantled?

Someone else decides: the needs and wants the product solves, how the product is distributed and how much it’s sold for.

All that’s left for the marketer is to create a message that generates awareness and persuades the customer. It’s really only marketing communication.

That’s why most marketers are f#cked.

But that’s what people refer to when they mean marketing. Isn’t it? Yes, it is even what most MARKETERS do. It’s like they accepted self crippling.

So are you really surprised that clever people figured out that “marketing” crippled to one P does not work?

Peter Thiel and the PayPal Mafia were right: In order to succeed, the 4 Ps need to be aligned, because they are intertwined.

So they said: “Let’s identify an unmet need in the market, and create a great product to solve their pain points. Then let’s “hack” eBay and be exactly where our customers are. And also, fuck it, we don’t need “marketing”.”

Everything is great – except the illusion that they didn’t need marketing. What they did is exactly what a great marketer would do.

Maybe they didn’t spend money on advertising. Maybe they didn’t hire a PR agency. But I hope we know now that this isn’t the same as marketing, merely a small part of marketing communication.

So what’s my whole point of all this?

To define what good marketing is, we don’t need to give it a new name.

I do it myself, and call myself a “Growth Consultant”. That’s because of the misconceptions that people have about marketing.

But really, all I do is marketing.

But the way it’s supposed to be done. I call it: No-bs marketing.

ps. Thanks to Louis Grenier‘s conversation with John James on his podcast “Everyone HATES Marketers”, which led me to write this.

Shopify: How To Auto-Apply Discounts From Facebook Ads To Boost Your Conversion Rates

Are you currently running ads on Facebook to drive revenue for your Shopify store and giving people a discount?

Are you doing it like this?

Screenshot Credit – EinsteinMarketer

“Go get it, with promo code 20EXTRA at checkout.”

WTF, Nike?

They’re making their customers ENTER A CODE MANUALLY AT CHECKOUT.

WHY? WHY? WHY?

Nobody likes to TYPE IN discount codes – and if you make people do it, you’re missing out on orders – period.

We want to make it as easy as possible and reduce friction to improve your conversion rates.

Tip: A great framework for those wanting to learn more about the behavioural principles behind this (reducing friction = improve ability + boosting motivation), I recommend BJ Fogg’s model.

In this article, you’ll learn the following – which in combination had a significant impact on my clients’ conversion rates:

  1. How to auto-apply discounts & coupons in Shopify using a link
  2. How to send people to the right page in your store
  3. Why leveraging Facebook’s tracking parameters is better than using the “Website URL” field
  4. Ways to show your audience that a discount has been applied on your site
  5. A ready-to-use popup with no other tools required you can use right off the bat

Without further ado, let’s get started…

Auto-Applying Discounts from your ads

This is quite straightforward. However, Shopify doesn’t explain much about it… so if you ended up getting lost like me, you found help 🤠

  1. After creating your discount code under Admin > Discounts, click on the discount code itself, find the button “Promote” at the top and click it.

You’ll find a link to “Get a shareable link” – click it.

So you can see how this system works – in order to apply a discount code, you just need to append it to this URL:

https://yourstoreurl.com/discount/[DISCOUNT_CODE]

Let me show you another useful feature: You may want to send people to a certain page and not your homepage. You can do this by adding a parameter to the URL:

https://yourstoreurl.com/discount/[DISCOUNT_CODE]?redirect=/products/awesome_product

which will send your visitors to https://yourstoreurl.com/products/awesome_product and apply the discount code.

How do I do this with Facebook?

You could just take it easy and use this as your landing page URL – but trust me, I have a better solution.

Instead of doing that, we’ll use Facebooks ‘tracking’ functionality.

Why? The website URL is part of your Facebook Post.

Why Facebook Post? Isn’t this for ads?

All ads are technically posts, too (some people refer to them as “dark posts”). And all social activity – likes, comments, shares – are saved for each specific post.

Which means if you’re making any change to the post, for example changing the URL, you’re creating a new post. And social activity starts at 0!

In most cases, you don’t want that, because you want to benefit from any social proof (likes, comments, shares) that the ad (post) has already received. Trust me, this affects your click-through rates A LOT. 😎

So keep your website URL as generic as possible – let’s not add the redirect here, neither do we add other URL parameters (e.g. UTM).

We’ll do it like this:

Website URL. We’re keeping it as generic as possible, so just the base URL for the discount code.

What’s next?

For our additional parameters, we’re using Facebooks “URL parameters” feature.

This is something you can change between ads – BUT the post itself can remain the same, meaning that you can channel all ad spend to one post to channel engagement or re-use old posts in ads and benefit from previous social engagement for social proof. 🤩

Technically this creates different URLs, but Facebook does not seem to bother. Doesn’t it feel good to outsmart Facebook? 🤗

Another benefit – for carousels with multiple URLs, you don’t need to add your UTM tags to each URL separately. Easier to manage!

So back to the URL parameters, what do we set here? Have a look at my example…

You’ll find this bit in the “Tracking” section for your ad

You can see that I’ve added the standard UTM tracking codes AND our redirect parameter at the end. And that’s it! We’re good to go here.

I’d recommend testing this by using the preview function and “share a link” to show the ad in your own newsfeed. From there, click the CTA and see if a) you get to the right page and b) if Shopify has applied the discount at checkout.

Make sure you’re previewing if the discount works

That’s a good first step, but still isn’t great – because your customers have no idea they’re getting a discount at this point, right?

Showing to your Audience that a Discount Has Been Applied

This is such a crucial part of doing discounting – because what do you think customers will do if they come to your page and DON’T SEE ANY INDICATION ON YOUR SITE they’re getting a discount?

They get confused. Their motivation is depleting. They need to think.

And bamm, they drop off!

Even if you’re communicating in your ads that your discount will be “applied at checkout” or similar – it’s just not as good as *actually* telling them on your website.

I’m not suggesting your customers are idiots, but still: make it idiot-proof. Keep it as simple and obvious as possible.

Conversion will go up, guaranteed. 🚀

So how can we do that?

There are millions of ways to do this. You could…

  • show a popup
  • display a message at the top of the screen
  • add a message somewhere else on the page
  • show discounted *NEW* price of your products (I strongly recommend this!)
  • a floating unicorn
  • and so on!

Unfortunately, I can’t cover all of them in this article. 😅

I will show you one specific example (a popup) which you can literally copy and paste to your site, and it works. So bear with me 😎

All of these methods have one thing common: everything starts with the same TRIGGER:

The trigger: Someone visiting your website using a link which applies a discount

We need to configure a trigger that understands that we’ve got a customer coming from Facebook (for this example), who’s got a certain Discount Code.

In order to do this, we’ll simply leverage our UTM Parameters.

Step 1: Save UTM Parameters as Google Tag Manager Variables

In order to save them as a variable in GTM, we’ll first have to save our UTM parameters in your visitors localStorage.

Local Storage: A bit like cookies 🍪, but more delicious and easier to digest. More about it here.

This needs a helper function – simply create a Custom HTML Tag, give it a descriptive name and paste the code below.

Helper function to capture your variables

Here’s the magic code… 🧙‍♂️

<script type="text/javascript">
/// get the url and the key to find
var url = window.location.href;
var keyToFind = 'utm_source';
var keyToFind2 = 'utm_campaign';
var keyToFind3 = 'utm_content';
/// is there a utm source ?
if(url.indexOf(keyToFind + '=') > -1)
{
var valueFound = url.substr(url.indexOf(keyToFind + '=') + keyToFind.length + 1).split('&')[0];
localStorage.setItem(keyToFind, valueFound);      
}
if(url.indexOf(keyToFind2 + '=') > -1)
{
var valueFound = url.substr(url.indexOf(keyToFind2 + '=') + keyToFind2.length + 1).split('&')[0];
localStorage.setItem(keyToFind2, valueFound);      
}
  if(url.indexOf(keyToFind3 + '=') > -1)
{
var valueFound = url.substr(url.indexOf(keyToFind3 + '=') + keyToFind3.length + 1).split('&')[0];
localStorage.setItem(keyToFind3, valueFound);      
}
</script>

By the way, you can easily add other variables here (like UTM_medium). Just add them to the variables in the above code, and create another if function to look for a keyToFind4 and so on.

Use PageView as the trigger and save it. And we’re good to go! 🙌

Next up, we need to save our UTM parameters as variables.

Simply go to variables in Google Tag Manager, add a new variable with Custom Javascript, and create a variable for each of your UTM parameters like so…

Each UTM Parameter needs its own variable

Here’s the small bit of code we need:

function()
{
return localStorage.getItem('utm_campaign');
}

In order to amend this for your other UTM parameters, just change the variable. E.g. for UTM_source, use:

function()
{
return localStorage.getItem('utm_source');
}

You should end up with at least these 3 UTM parameters as separate variables:

3 new variables. Woop! 😎

Nice job so far – these variables are going to be our triggers.

NOTE: These variables will remain in your visitor’s browser until they clear the storage manually (delete their cookies). If you don’t want this, you could use the SessionStorage instead. Just replace it in our Helper function and the Custom Javascript to retrieve our Variables.

Let’s move on!

Step 2: Use your new triggers to show your visitors that a discount has been applied

As a said, there are a lot of ways to do this.

You could use a popup tool like Sumo or JustUno, for example.

Or you could have a bar at the top as part of your template which you hide it at default – only showing it when a discount is applied.

In this quick tutorial, I’m going to only use Google Tag Manager to create a simple popup. Thanks to Marthijn Hoiting for his awesome little popup (check it out!) which I’ve only slightly modified here.

We’re creating a popup, which will look like this:

How to do it?

Simply create a new HTML tag in Google Tag Manager 🤠

Just copy and paste the following:

<style>
.overlay {
    position: absolute;
    top: 0;
    left: 0;
    right: 0;
    bottom: 0;
    background-color: rgba(0, 0, 0, 0);
    background: url(data:;base64,iVBORw0KGgoAAAANSUhEUgAAAAIAAAACCAYAAABytg0kAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAgY0hSTQAAeiYAAICEAAD6AAAAgOgAAHUwAADqYAAAOpgAABdwnLpRPAAAABl0RVh0U29mdHdhcmUAUGFpbnQuTkVUIHYzLjUuNUmK/OAAAAATSURBVBhXY2RgYNgHxGAAYuwDAA78AjwwRoQYAAAAAElFTkSuQmCC) repeat scroll transparent\9;
    z-index: 9999;
    color: #fff;
    transition: opacity 0ms;
}

.popup {
    margin: 0px;
    padding: 20px;
    text-align: center;
    height: 150px;
    background: #AFEEEE;
    border-radius: 5px;
    width: 400px;
    position: fixed;
    left: 50%;
    top: 50%;
    transform: translate(-50%, -50%);
    color: #000;
}

.popup h2 {
    margin-top: 5px;
    color: #333;
}

.popup .closeFB {
    position: absolute;
    top: 0px;
    right: 15px;
    transition: all 200ms;
    font-size: 30px;
    font-weight: bold;
    text-decoration: none;
    color: #333;
}

</style>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>

<script>
    
$(document).ready(function() {
    
        $('body').prepend('<div class="overlay"><div class="popup"><h2>Congrats, you get 20% off.</h2><a class="closeFB" href="#">×</a><div class="content"><p>Your discount will be auto-applied at checkout 🤩</p></div></div></div>');

        $('.closeFB').click(function() {
            $('.overlay, .popup').fadeOut(500);
        });
});

</script>

And that’s it! 🤡

NOTE: You probably only want to show the popup once to your visitor. Follow Marthijn’s tutorial to disable showing the popup if the user has closed it before. Check the section “Check if the user has closed the Facebook popup” – you can use the exact same code for my variation.

Next up, we need to set a trigger! Let’s set up our example:

I’m assuming we’re using “fb” (facebook) for utm_source, “retargeting” as our utm_campaign and “20off” as our utm_content, like so:

https://yourstoreurl.com/discount/[DISCOUNT_CODE]?redirect=/products/awesome_product&utm_source=fb&utm_medium=ad&utm_campaign=retargeting&utm_content=20off

Note: You’re probably using utm_medium as well, but I’m going to ignore this here as it’s not necessary.

Next, create a new triggerand configure the page view event as follows:

Save it – and voila! It’s done.

Works for me! 🤓

Now you can run Facebook ads to your Shopify store, apply the discount and let your customers know about it. 🤩

No more manually typing in codes, no more guessing. Your (prospective) customers will love this. Now go and get these conversions up 🚀

Questions or Feedback? Leave a comment!

Hacking Keyword Research: How to Generate Relevant Keywords 5X Faster

Keyword Research. Whether it’s for SEO or Paid Search…

… really kinda sucks, doesn’t it? 😟

I think it’s incredibly boring and tedious: Definitely not my favourite part of being an SEO freelancer. But also, it’s super important!

So what can we do? 🧐

To make my own life easier, I’ve created a template in Google Sheets which allows me to do keyword research at least 5X faster, with better results.

I’ve used it with dozens of my clients in the last 12 months – and it’s never let me down. Over time, I made a lot of improvements and I decided that my baby is ready to see the world.

So today I’m sharing it with you… 100% free. 🥳🥳🥳

There are just 2 things you need to use it:

  1. 19 mins time to watch my instruction video on YouTube. (Pro tip: watch it on 1.5-2X and you’ll need even less time. 🤓)
  2. A SEMRush Pro, Guru or Business Plan (or trial). If you don’t have one, you can sign up here to get a free 7 day trial with no strings attached: Click Here to Sign Up (Disclaimer: I’ll receive a small commission if you decide to pay for it)

Sounds awesome, where can I start?

First, watch my video for instructions:

Access the sheet here: Click here and make a copy of the sheet

Can you tell me how this works before I waste 20 mins of my life?

Only 19 mins! But hey, I understand! Stay with me…

What is the output of this keyword template?

With this sheet, you will be able to create a list of hundreds to thousands of RELEVANT keywords for your website.

Hundreds to thousands, is that a lot? Yes, and actually QUANTITY isn’t the goal here. This is not a size comparison!

What my template allows you is to actually FILTER your keywords quickly to a manageable number. Some may like more, some less – this is totally up to you. I usually work with around 500 keywords, but this depends on the size of your website.

Here’s a little teaser of the output…

Example output from one of my clients

Is it just keywords, or will I have more data?

Great question amigo! 🤠

There’s A LOT more data that you’ll have available. This is a sheet for the geeks, misfits and data noodlers out there.

Here’s what you’ll have in one neat overview sheet for all of your keywords:

  • The search volume
  • The keyword difficulty
  • Paid Search metrics such as Competition & CPC
  • Number of search results
  • What is the highest rank of any of the domains you’ve put in (a maximum of 6 including your own can be compared)
  • What is the rank for each domain, and how much traffic do these domains receive for each term

But isn’t that just SEMRush data I have anyway?

Yap, there’s no magic new data here. 🤗

What the sheet does is saving you about 16 steps in between, and putting it all into 1 sheet almost fully automatically:

  • No need to de-duplicate keywords
  • No need to manually combine your competitors ranking data
  • Easy filters to narrow down your keyword selection
  • A table you can use directly to create graphs for Share of Voice, Keyword Opportunity etc.

In short, saving you a hell lot of time… 👹

You’ve mentioned filters. What exactly can I filter my data on?

I’m glad you ask, my friend. 🤓

You’ll be able to filter based on all important SEMRush metrics, plus a little magic dust 🧙‍♂️:

  • Monthly Search Volume (sometimes size does count)
  • Min. & Max. Keyword Difficulty (filter for easier opportunities first)
  • Min. & Max. CPC (yes, this also works for paid search!)
  • Min. & Max. Competition

And additional metrics which you can only easily get with my sheet…🧙‍♂️

  • Filter between Brand, Non-Brand or both keywords
  • Filter for Questions only (for English only)
  • Filter keywords based on the number of words they consist of
  • Filter based on the best rank of any competitor (helps to keep relevancy of your selection higher)
  • Filter out NEGATIVE keywords (up to 50!)
All these filters at your finger tips…

Is it really fully automated?

Almost! 😎

The only thing you need to do manually is download a CSV file of ranking data for each of the domains you’re looking at and import it into the sheet.

This only about 5mins. Promise 🤗

But what about Keyword grouping?

You’ve paid attention young padawan, haven’t you? 🤗

Keyword grouping is indeed a feature in my sheet, you might have seen it in my little GIF above 🤩

Is this also automated? NOPE! 😐

Sorry! You’ll still have to group your keywords manually. The sheet makes it a little easier, which I explain at the end of my video.

So far, I have not found a solution to do it (semi-)automatically. Have some good ideas? Leave a comment below!

All this data in Google Sheets, isn’t that going to be super slow?

Yap, it’s not the perfect tool indeed. I’m working on a faster solution as you’re reading this.

But look at the bright side – it’s fully customisable. There are no limits to your creativity and what you can do with this data… 🥳

Right, can I get access now?

🙌Woop woop, I can’t wait for you to try it out!

First, watch my video with instructions:

Access the sheet: Click here & make a copy

Enjoying it? Got some feedback? Leave a comment! 👇👇👇

No-BS Guide to Getting Started as a Digital Nomad in 2019 🌎💻🏖

Sarah and I embarked on our Digital Nomad journey 8 months ago. So far, we have travelled through 8 countries, had countless incredible experiences, met inspiring people and made new friends. 🙌

What a ride it’s been! But while we had a lot of fun, we also worked a lot – and earned about $17,900 after taxes (we spent about $12,200). 🤑

Will you be able to just do the same? Probably, but we’re not going to tell you it’s easy! But can YOU experience the freedom travelling and location-independent work? Oh yes, you can. 🤩

In this No-BS guide to digital nomadism, we’ll share our learnings to enable you to get started, plan your trip and avoid some of the pitfalls along the way. We are constantly keeping this up to date while we travel further. 🤠

Questions, comments or want to share your own story? We’d love to hear it – just leave a comment at the end! 😊

So… why are we writing this, you may ask?

1. Many people dream of a digital nomad lifestyle, but don’t know how to get started.

Of course, sacking off the 9-5 and working at the beach sounds dreamy, right? It’s not all fairy dust and unicorns, but it’s still pretty awesome. We think more people should start out to chase their dreams – even if it doesn’t turn out to be for them, you must at least try. You don’t want to live a life of regrets, do you? 🤔

2. There’s a lot of bullshit out there. 💩

A lot of digital nomad’s job is literally getting other nomads to consume their content, follow them on instagram or pay for their white paper revealing all the ‘secrets’. Well done for them if this works out, but this is not how we’re earning our money – so we hope we can provide a slightly different perspective.

Lastly, before we get started… 🙏

… we know we are incredibly privileged to be able to live this lifestyle.

To a big extent, this is only possible because of where we come from (Germany, UK), what our passports allow us to do and also because we’ve got a security net to fall back into. If shit hits the fan, we can go home, and things will be fine.

Not everyone – in fact, the majority of people in the world – have this. If this is you, we still hope there’s something useful in this guide for you. But please understand where we come from. Again, we’re enormously grateful for that. And we’re trying to give back where we can (e.g. by helping local communities, such as the Amigos De Santa Cruz).

Beach office is the best office (at a Beach in Holbox, Mexico).

How to get started – even without savings 💰

Yes, it’s possible – you can start travelling without savings. 

Without savings, what does that really mean? 

Well, you should always have enough money to buy a flight back home, wherever you are. That should be a no-brainer. But beyond that, you don’t need much more. Is it good to have some savings? Absolutely! And we encourage you to have some (to be precise, around 3months of “all in costs” in whatever country you’re living in). 

Cutting Accommodation Costs

Wherever you are in the world, the main cost factor is going to be your accommodation.

Even if you’re planning to stay in dorm beds in hostels you’ll have to plan for around $15-$40 a night (prices obviously vary greatly depending where you are), it’s a cost that’s best to avoid. It will slowly but surely drain your (if existent) savings and be a stress factor especially when you’re starting out.

There are 2 alternatives that we can highly recommend and we think are under-utilised by nomads, especially when you’re starting out:

Work Exchanges – e.g. via Workaway

Workaway is a platform that offers jobs for travellers all around the world. The jobs range from helping with specific tasks like optimising websites & marketing to teaching yoga, helping out in hostels, farms and other places. Not all these jobs are great and often you are simply a replacement for a more expensive local workforce, which is not the purpose of the programme. But you can also find some real gems which are perfect for nomads who are starting out, so make sure you don’t dismiss this option!

Here’s are Workaways we did in the last 6 months:

Naps in the Costa Rican jungle with views of the Pacific Ocean 😍
  • 3 weeks optimising the websites of Los Elementos Adventure Center & local charity Amigos De Santa Cruz (at Lake Atitlan, Guatemala) for SEO and conversion rate; in return we received a fantastic accommodation by the lake and a financial contribution to the costs of food. We worked around 15-20 hours per week and hence still had plenty of time to work online and explore the beauties of the region.
  • 1 week working with Jon at Casa Nautika to optimise his website and help with his marketing approach; in return we received a great accommodation in the jungle close to surf paradise Dominical, Costa Rica
  • 3 weeks working at El Jaguar Hostel, Bocas Del Toro Panama where we helped with guests check-in/out as well as at the bar. We had to work 4 hours each for 5 days a week, which sounds long, but we were able to spend plenty of time during these hours working in our digital jobs (and enjoying this amazing place). We received a private room and even a financial contribution to our food expenses (as well as cheap drinks at the bar 😁)
  • 2.5 months (!) doing a work exchange at Palmar at Red Frog Beach, Bocas Del Toro, Panama. Palmar is in an absolute dream location surrounded by a beautiful beach and a lush jungle. Sarah taught yoga there, and Johannes made bonfires and ensured the guests were happy. The expectation is that you work around 25 hours a week – but honestly, it doesn’t even feel like work. It’s like hanging out with friends. We loved it there because we could also work in a nearby co-working space at Selina Hostel Red Frog, whilst still having plenty of free time. We also had a private room at Palmar’s staff house and great discounts on food and drinks. An amazing experience!
3 months on this dreamy beach in Panama were well spent

You can see, of our 8 months travelling we only paid for our accommodation about half the time, which saved us a whopping $3,500 in total (assuming an average price of $40 for 2 per night, a good reference for Central America).

But it’s NOT only about the money you save! There is another benefit of doing workaways as a digital nomad:  You won’t be lonely! Digital Nomad life can feel very lonely – it’s probably one of the most mentioned negative aspects of this lifestyle. If you’re doing workaways, you’re guaranteed to meet with locals and travellers alike. We enjoyed this a lot. While volunteering in exchange for board means you don’t have the freedom to work all day, you’ll certainly have some great, life enriching experiences and make new friends from around the world.

A couples membership to the Workaway platform for 1 year is a mere $54 and is WELL worth the price. There are hundreds of thousands of opportunities waiting for you all over the world! You can sign up using our code here.

Housesitting – e.g. via TrustedHousesitters

View from our TrustedHousesitters balcony in Panama

Housesitting is another fantastic way to save money on accommodation. Essentially, the idea is that you either look after houses/apartments when they are empty or, and more commonly, you take care of people’s pets while they are gone. 

You’d be surprised about the places you can find on this platform. It is MINDBLOWING!  Especially if you are in the US, Australia, NZ and Europe, you’ll find a lot of insane places. But also in less developed countries there are often a few places (and less applicants) available.

So far, we have only done 1 housesitting via TrustedHousesitters.com (there aren’t so many opportunities in Central/South America which is where we’ve been so far). It was a fantastic experience – we looked after 2 cats in a 32nd floor apartment with incredible views in Panama City. We enjoyed high speed internet, Netflix and the comfiest bed of our whole journey. And spending time with the cats (Blackie and Fatty) was super nice too – especially if you’re missing your pets at home.

If you’re a bit more flexible with where you’re staying, you can find some real gems. Sits are between a few days up to a few weeks.  

TrustedHousesitters costs $119 for a year long membership. You can use our referral link here to get 25% off that price.

How to choose the right remote work for you? 🤷‍♀️🤷‍♂️

This is a super important part of this article because we think people often get this wrong, or go about it in the wrong way. It’s a very simple, but essential question – what type of remote job are you going to do?

Before we talk about specific professions, let’s look at it from a broader perspective: what is actually most important for you?

Stability vs. Freedom

The first question you should ask yourself is: how important is freedom for you – i.e. when and how much to work, and related to that, how well are you coping with uncertainty (will I get paid next month?). 

Don’t lie to yourself. If you’re the type of person who freaks out about not knowing what you’ll do next week, then unstable, gig-based freelance job aren’t right for you. If freedom and working on your own schedule is elementary for you, you’re probably fine with some uncertainty as well.

Below we’ve outlined what we deem to be the main remote options:

(1) Become a full time remote employee for 1 company

This is the ‘high stability, high routine, low flexibility’ option. 

You land a job at a company that has started to embrace remote working (and likes the fact they can save office costs). With this location-freedom, you will have some more restrictions, which typically include: 

  • working set core hours in a specific time zone or even 9-5 in a specific time zone (e.g. for sales jobs)
  • being available for calls, meetings, slack within this set time 
  • set team meetings or stand ups

The benefits are clear: the good (especially bigger) companies here invest in your development (training etc.), they likely pay you a flight or 2 per year to visit their head offices or for company-wide retreats and most importantly, you’ll earn a stable salary. Benefits can include a contribution to a co-working space, equipment, generous holiday policy etc. 

Great examples of companies who hire remotely are Zapier, Automattic, Basecamp & Hotjar.

Freedom vs. Stability; what’s more important?

(2) Freelance project-based  

This is the ‘high flexibility, low routine, low stability’ option.

As a freelancer, you typically can decide yourself when you’re working – or you need to work your arse off for a specific amount of time. If you’re doing different projects, you’ll hardly have a routine.  It can create challenges and isn’t everyones game, but it has certain benefits. If you don’t want to missing out on happy hour or the best surf for a time zone dictated hangout call then freelancing is the way forward.

Here’s some tips on getting started:

  • There are plenty of sites offering freelance gigs, the one most Digital Nomad guides direct you to is Upwork. From our experience: don’t waste your time with it. Unless your skill is super super niche and there’s only 3 other people out of the 12 million! 😱 (yes that’s correct, 12 million) freelancers with a profile on the site, it’s now just too huge to to stand out especially when you’re starting out!
  • If you want to use a platform to find clients CloudPeeps is much better. Yes, there are fewer jobs on offer than Upwork and you have to pay a small monthly fee AND % of your wage but the quality of the jobs is much higher and the competition for them much lower. Sarah sourced 2 freelance clients through CloudPeeps.
  • Don’t waste your life with applying to jobs on sites such as WeWork Remotely, Remote.co, Workew etc. As much as the remote opportunities on these sites sound amazing they are few and far between and just imagine the number of people dreaming of the remote life who will be your competition. After many wasted hours writing applications to these kind of jobs (which in hindsight were a drop in the ocean of wannabe nomadic applicants) it was time to look for new ways to score clients. 
  • Don’t underestimate the power of LinkedIn. There are hundreds of companies out there desperate to find someone reliable to produce something for them whether that’s content, SEO recommendations or social media posts. They also don’t have time to sift through a million profiles on a freelancer site. Imagine if someone capable with great references dropped right in front of them (slid into their DMs as it’s known in some circles!).
  • Think outside the box. Make a list of all the companies in your niche (edtech for example) and reach out directly to the few you’d want to work with offering services as a freelancer. Cold outreach can feel weird and embarrassing at first but when you realise that the worst that can happen is someone saying no, it really isn’t that much of a big deal. Asking someone for work is not going to put you in a position any worse than you’re already in if you don’t have any work in the first place!
  • The power of your network. Don’t forget about your existing network. Who have you worked for that may pay for your skills on a freelance basis? Do you know anyone with a startup who is desperate for an expert in your field? Leveraging the network you already have is a great way to score work. Tell people you’ve gone freelance and that you’re happy to be recommended for work. Johannes has brought on all his clients through recommendations based on his previous “non nomad” work.

(3) Part time working online

This kind of job gives you more stability than freelance projects but slightly less flexibility as you usually need to work specific hours. However taking a part time job, as an online teacher for example, will make sure you can maintain a more steady stream of income.

There are tonnes of different part time jobs such as social media managers and translators. Sarah has most experience in teaching, specifically teaching English online.

Here are some tips if you’re interested in this kind of work:

  • There are hundreds of companies in China looking for online English teachers. Depending on how many hours you are willing to take on you can earn up to $3,000 a month doing it. The only downside to this is you, of course, need fast internet speed. The struggle can often be real when finding a place to stay which offers this. Tip: Find an accommodation near a co-work space as this can be a more reliable option for getting through those classes jitter free!
  • A great site to find jobs teaching children in China is HiOffer 
  • Sarah had a great experience using DaDaABC. They have a really user friendly platform and the classes are one on one rather than group. They even have a new feature where you can say you’re ‘available right now’ and you will get assigned classes even if it’s not your regular teaching hours. If you have a degree and teaching qualification (and speak English to a native level) this could be a great option. You can use this referral link to sign up.
  • If you aren’t so keen on teaching children, another great website to find students who are looking to learn every language imaginable is Preply 
  • Classroom of dreams 🤩
  • Part time is perfect! Maximise time enjoying the place you are!

(4) Do your own thing: Become an Entrepreneur

Thinking about running or even starting your own business while travelling as a digital nomad? It’s a tricky one!

As if founding a company wasn’t hard enough when you’re back home, doing it from another continent, usually far away from your customers and without a routine is even harder. But that’s not to say it isn’t possible, and there is a whole range of options out there. From doing arbitrage on Amazon to launching a SAAS company, the possibilities are endless. You can even start a business to tell other nomads how to start their own business 😉 

Whether this is a ‘high freedom, high stability’ option or the opposite is completely up to you. Many people do work a lot more than they would in normal jobs – and there will never be a time when you’re actually ‘done’ with your job. As an entrepreneur, you will always have work to do. Whether this is something for you or not, try it out. But don’t lie to yourself – it’s certainly not for everyone.

Types of Digital Nomad Jobs 👩‍💻

So what type of digital nomad jobs exist and where are they on the freedom <> stability spectrum?

We’ve already touched on a few jobs above, but to give you a better sense of what you could be doing, here’s a list of the most popular nomad professions:

Copywriting & Content Marketing

If you’re working as a copywriter, you’ll typically help companies with their blog, marketing copy,  product descriptions and more. Content marketing encompasses the distribution of these assets through social media and other channels.

Social Media Manager 

As a social media manager, it’s typically your job to manage a company’s facebook, instagram, twitter, pinterest and/or snapchat profiles and make sure your community is growing and stays engaged.

Social Media Influencer 

Social Media influencers are either bloggers, YouTubers or Instagramers who managed to build up a sizeable audience in a particular niche (or not so niche…) and use their reach / credibility in order to promote products.

Affiliate Marketer / Blogger 

Affiliate marketers have an existing audience for example through a blog or website and use this audience to promote products. Through the sale of their products, they earn a commission. 

SEO / Performance Marketer 

This is broadly what Johannes is doing. As an SEO or Performance marketer, you help companies to increase their Organic Search traffic (SEO) and/or optimise their Paid Channels such as Facebook Ads or Google Adwords.

Translation 

If you work in translation, you’re extremely well versed in at least 2 languages and translate between them – usually written, but it could also be recorded.

Graphic Designer 

Graphic Designers are well… designing all sorts of things. From websites to company logos or a whole CI (corporate identity), there is huge demand for this sort of creative work.

Software Engineer / Programmer 

Oh well, don’t we all wish we’d taken a different route at some point in our education and learned how to programme? Software engineers can make a lot of money to build or improve websites & applications. The demand is still huge, and the market is by no means saturated. 

Virtual Assistant 

VAs are helping clients with literally everything – from emails, organising travel to getting the laundry picked up. 

Language Teacher 

Last but not least, one of Sarah’s jobs is working as an English teacher for Chinese kids. This is a huge and growing market. Did you know that Dada, one of the big players in China, has raised over $860M? Insane, right…

Jobs overview: Average Income x Freedom x Stability 💸

We spoke quite a bit about “freedom” vs “stability”, and would like to add another dimension to our quick overview of all the jobs: average income.

We know this can differ WILDLY. Please, if you don’t feel like we hit the mark with this classification, feel free to leave a comment and share your own story. But be nice. At the end of the day ‘averages’ always have outliers to either side, and the same applies to our generalisation on the freedom <> stability scale.

Typical Digital Nomad jobs on our Freedom – Stability – Average Income Scale

Where to go 🗺

Once you’ve decided what route you want to go down for the work side of things, it’s time to decide on your destinations!

Consider, whether you’ll stay in one place for a few months or whether you’d prefer to move a bit faster. When deciding on your first destination, think about the following: 

  • How much do you realistically want to spend on rent? Your best bet for short term lets is Airbnb and you usually get a discount if you stay longer than a week. In general South East Asia and South America are where you will get more for your money. For example, in Medellin (DN hub of Colombia) you can rent a studio apartment in a great area for as little as $20 a night. Check your options and what you can get for your money. 
  • What’s the climate like? Another important thing to consider! Let’s be honest, most nomads are looking to escape the European/North American winter and are in search of warmer climes. This is why so many nomad hot spots have sprung up in tropical climates like Indonesia, Thailand, Colombia and Mexico. But that’s not for everyone and you may want to choose a place to ski or snowboard. There are no limits here!
Reason we chose Panama during European winter?…..it’s 30 degrees all year round
  • Is community important to you and do you want to connect with other remote workers?  If you want to meet like minded people who are also working online it’s probably better to stick to one of the digital nomad ‘hubs’ around the world. The Nomadlist should give you plenty of inspiration.
  • What do you want to do in your down time and what basic amenities do you need? Beach or city life? Is the location easy to get to? Think about what your ideal destination offers you. For us, being outdoors is important to run, cycle, surf – but we also like to mix it up with some city life every now and then. We also look for places with yoga so Sarah can teach and Johannes can stretch his 6″4′ frame (if you’re interested in Sarah’s teaching, check out her videos here). The beauty of this lifestyle is that you don’t have to settle to just one.
Yoga on the beach in Mexico

Where to Stay 🛌

Alright, let’s move on on where to stay if you can’t find a suitable workaway or housesitting opportunity.

The main options are – apart from buying your own RV or boat, which is something we may do in the future 🙃:

Airbnb

Airbnb’s can be a great choice because often you can ‘live like a local’, have plenty of your own space and don’t have to share the Wifi (!) with anyone. 

Recently, AirBnB has started to push more and more into the ‘hotel’ and managed apartment business, so if you don’t want to be in a hotel, make sure you read the descriptions / reviews properly. We’ve had outstanding experiences on Airbnb (see our list of favourites) as well as a few let-downs.

Here are some tips to avoid disappointment:

  • Ask before about the Wifi speed, and ideally have them provide a screenshot of the speed (that is, unless you don’t care about this, of course)
  • Check the prices including all of the additional charges (service charge, cleaning etc. can add up a lot, especially if you’re only staying a few days)
  • Make sure you have plenty of private spaces as opposed to sharing the places where you *could* work with the owner or other renters 
  • If you’re planning to stay longer and you want to book this ahead of time, just ask for discount. Airbnb hosts can then send you a special offer – and it’s normal to get a good discount especially it’s a place that’s not always booked.

Some awesome Airbnbs we stayed at in Mexico / Central America:

  • Lhesh and Helena’s place in Antigua, Guatemala, $32 a night. The most beautiful Airbnb we stayed in on our whole trip, we felt like we were staying with old friends! Lhesh and Helena rent out the master bedroom of their beautiful house (with swimming pool!) in a private condo. Every single detail has been taken care of & it’s a labour of love, we had our own bathroom, walk in wardrobe and balcony with views of the surrounding volcanos. Dreamy to say the least. You can get more info about the room and the tours they offer on their website 
  • The Green House in Tulum, Mexico If you go off the main tourist road there’s an abundance of awesome local stays. We enjoyed a whole house (larger than our London flat!) for $27 a night. This included our own bikes to get around, small garden, widescreen TV, fastest internet we had in Mexico and all the cooking utensils to make a gourmet feast! 
  • An old bin storage unit in Holbox, Mexico $37 a night. Ok so this is not a digital nomad paradise, the whole island isn’t really as it has sketchy internet EVERYWHERE! This cabana however was beautiful, our host had spent so much time renovating it and it was by far the most stylish place we have ever stayed with our own front garden with huge hippy hammock. The place doesn’t even have WiFi so this is more a recommendation for if you want a digital detox for a few days while in Mexico!  

You haven’t used Airbnb before? Feel free to use our link and get up to $50 in travel credit.

  • The cutest old bin storage unit EVER!
  • Tulum green house complete with green bikes

Hostels

A straight forward option for travellers with limited budget.

The good 👍: The benefits include that you’ve got some life going on around you and you can meet other travellers. It’s a good choice if you feel a bit lonely and need to fill up your social batteries. 

The bad 👎: Hostels don’t usually provide a great work environment (yet!). They can be loud, the wifi is often 💩and private rooms are relatively expensive. If you want to keep the budget down and sleep in a dorm, make sure you lock your expensive gear away somewhere 🙂

Slowly, hostels are waking up to the ‘digital nomad’ trend though: One tip for nomads is Selina, a massively growing hostel chain targeting digital nomads. 

Selina has been super useful for us since they always have a co-working space with free coffee, good desks and decent internet.

So even if the atmosphere might not be exactly what you’re looking for, just stay in another place nearby for a short ‘commute’. We did this at Red Frog, Panama, where we stayed at the amazing Palmar Beach Lodge and commuted to Selina Red Frog to use their co-work. Selina usually charges around $10 per day to use the co-work, but they give big discounts if you’re using it for longer. Make sure you connect with the manager (who usually works in the same space too) to get even better rates 😄 

Selina combines accommodation, co-work and is often at great surf spots, like Playa Venao in Panama.

Hotels

We barely ever stay in hotels, and when we did, it usually was a characterless disappointment!

You neither have the ‘local experience’ of an AirBnB, nor are you able to connect with other travellers like in hostels. But if you’re looking for a super comfy bed and a bit more luxury, this may be an option for you. 

If you do decide to look for a hotel (or even a hostel), you may want to check out Booking.com. If you’ve been living under a rock and have never used it before, feel free to use our link to get $20 off your first booking 🙃

Tips on what to buy & to pack 🧳

Sarah & I have both become massive packing nerds after a few months of travelling, and we’d love to share what gear we think is absolutely crucial.

Packing Principles

But first off, some principles.

  1. Embrace minimalism – take as LITTLE as you can
  2. Invest in high quality, practical & versatile gear (if you buy shice, you buy twice)
  3. Don’t get too attached to any of it 🙂 

1. Embrace minimalism (and packing cubes!)

Don’t freak out! You don’t need to prepare for every eventuality and circumstance.

The Minimalists say “just in case” are the most dangerous words in English language. Well, maybe that’s taking it a bit far – but there’s a lot of truth in it. Trust us, having a small backpack that you can just throw over your shoulder to move from place to place is heaven compared to carrying a HUGE backpack of stuff you haven’t even worn. 

2. Invest in high quality, practical & versatile gear

Self explanatory, right? 🙂It’s more sustainable and better for your wallet to buy some good stuff. Check our list of gear below to get some inspiration.

3. Don’t get too attached to any of it 🙂 

You will lose things. Clothes will be ruined by careless laundry shops. Things might get stolen. It happens!

Does it really matter though? It shouldn’t, because it isn’t about the stuff that you own. Obviously, be careful – especially with your tech stuff. It can be harder to replace these and you should get an insurance for your most expensive items (we use BackMeUp, get £5 by using our code RADIG088). Goes without saying, but make sure you back up your work.

Looking for something more concrete? Right, here you go – Johannes is opening his backpack for you! 

What did we pack

Johannes’ set up. Total weight: ~14kg including laptop.

Backpack & Organisation

Backpack: Nomatic 40L Travel bag.

I bought this as a gift for myself for Christmas. It’s a good bag. The best one on the market? I’m not sure.

I’ve had a few quality issues (e.g. with the chest belt) but overall it’s definitely functional.

Packing Cubes: No brand from an outdoor shop in Panama City

Say what? One of the most important items we’ve started using a few months ago. It makes packing/unpacking so much easier, keeps everything neatly organised and maximises the space in your backpack. Especially if it only opens from the top.

Clothing

  1. 2 sleeve less T-Shirts, 1 surf rash guard from Billabong
  2. 5 T-Shirts – 2x Merino from Super Natural which I absolutely love (fast drying, soft and anti-bacterial) , 2 from Labfresh (they are stain & odour resistant but still not perfect for hot weather), 1 long sleeve from Uniqlo
  3. 3 Shirts – 2 Long sleeve (Uniqlo & Labfresh), 1 short sleeve from a market in Myanmar
  4. 2 Shorts, 1 Swim Shorts, 1 Sports Short (can also be used for swimming)
  5. 1 Jeans
  6. Rain jacket from Uniqlo, 1 Puffer Jacket from Patagonia, 1 winter hat (much needed when I went to Moscow, now useful on ultra air-conditioned night buses😄) 
  7. Shoes: pair of Nike trainers, hiking boots from VivoBarefoot which I love, flip flops from Reef
  8. Underwear: 5x incredible, super soft, fast drying & anti-bacterial JustWears 😍😍😍 , 3 pairs of socks

Other useful gear (9)

  • 1 Microfiber Towel (Noname)
  • 1 Silk Sleeping Bag Liner from Cocoon
  • 1 Belt from Uniqlo
  • 1 Water Bottle from Avex
  • Sunglasses from Dot Dash
  • Cap (thanks to my friend Martin for the gift!)
  • Lightweight, packable 10L day pack from Matador (Sarah has the Amazon version for half the price which works just as fine)

Technical Gear

My tech in action – of course with an espresso and a delicious piece of Arequipe tart (in Medellín, Colombia).
  1. Apple Macbook Pro 13″
  2. Roost Laptop Stand. Probably best on the market.
  3. Apple Magic Keyboard
  4. Simple Microsoft Cable-Mouse (no need for another battery!)
  5. Apple Airpods
  6. Some standard in-ear headphones (for full concentration)
  7. UPRIGHT Go – LOVE it. Helps to sit straight and avoid back problems.
  8. Apple iPhone 6S
  9. Firefox OS Phone (backup if my iPhone gets stolen)
  10. ancient Amazon Kindle Touch. does the job!
  11. ANKER portable charger

Why digital Nomadism sucks 🤮

Behind every picture perfect social media post you don’t see the hours of admin which has gone into researching a place, how to get there, where to stay, what to do, IF THEY HAVE WIFI!

You don’t see people living this lifestyle post about the (usually pretty arduous) bus and plane journeys with no leg room, the inevitable sickness on the road, sunburn, mosquito bites, diarrhoea, disgusting places to stay which were listed as 4 star etc. etc……

That’s all before we get on to the work side of things; the inevitable instability of most remote work, deadlines, money stresses, constant admin, insurance, finances. Of course you have all of these things to deal with in a conventional lifestyle but when it’s across borders, currencies and legal systems things can start to get a bit complicated.

The other weird thing about this lifestyle (unless you are super organised and have a plan of where you want to spend each month of the year) is the constant feeling of being in limbo. If you aren’t staying in a place that long it can be hard to make connections, you aren’t really a traveler but you aren’t a local either and that can be a weird feeling.

Obviously not having your nearest and dearest around you to share in all your wonderful new experiences can be tough too especially when major life stepping stones are being completed by your besties back home. Which leads on to the major malaise of the nomad; loneliness.

If you choose to embark on this lifestyle alone it can be super lonely, but of course there are ways to avoid this. You just have to be a bit more proactive than perhaps you’d normally be, seek out social opportunities, stay in places with a community feeling/ the opportunity to meet other like minded people.

The lack of routine can also be a challenge for some people as can be the discipline to set yourself aside enough time to fulfil your work commitments.

Hitting a deadline without support from a team combined with crappy internet can be tough sometimes……

Why digital Nomadism is awesome 😍

You are in complete control of your own life. Completely. That is a feeling you just never have when you are restricted to a set number of holiday days and are counting down until your next trip.

Want to have a beer on a Monday morning after a quick dip in the ocean? Go ahead. Don’t feel in the party mood on a Saturday night just because it’s the day everyone allocates to let off steam? Then get that work project completed. Being in control of doing what you want when you want and never having to be a slave to the alarm clock or a monotonous commute is a win in itself! There’s nothing greater than feeling you are beating the system and living a better life for it!

The shear number of experiences you have in a short space of time is mind blowing and you are constantly stimulated by new surroundings, cultures and people. Spending your days climbing volcanoes, diving with whale sharks and turtles, meeting inspiring people from across the globe and generally just experiencing this beautiful planet we inhabit in all its raw and precious glory is something you just don’t get when you live the same day over and over again back home.

If you love travel (which understandably not everybody does and we realise that) this lifestyle leaves you feeling invigorated, challenged and undeniably ALIVE. 

Many people who still don’t really understand the concept often ask what we’re running away from, when we will start a ‘serious life’ and our answer to that is simply…

“We don’t travel to escape life, we travel so life doesn’t escape us”.

  • Our first beer in Colombia!
  • Guatapé, Colombia

Still worried about making the leap? One last tip from us: use Tim Ferriss’ Fear-Setting exercise. It helps you to conquer your fears by defining what you are actually worried about. Go give it a try!

So that’s it from us! If digital nomadism is your dream, we hope we’ve given you some useful tips to get started.

Questions, comments or want to share your own story? We’d love to hear it – just leave a comment below! 😍

How to Reference Columns By Name in Google Sheets Query() Function [Quick Bite]

Hi there!

In my previous articles, I’ve mainly focused on in-depth, lengthy content. Hereby I am starting a new series of more digestible articles – delicious bites of marketing & tech delicacies 😋.

Today, this one is about Google Sheet’s Query Function. This function is nothing short but amazing. For me, it’s the nail in the coffin for MS Excel 😃 But decide for yourself!

👉 Want to go straight to the SOLUTION on how to reference by column name? Click here >>>

Here are a few advantages of Google Query over other functions:
  • simplifies complex functions and hence improves adaptability & readability
  • it’s using the same principles as SQL – so no need to re-think!
  • it’s often faster than using complex INDEX/MATCH or VLOOKUP
Cons:
  • You can’t easily ‘sort’ selections which use query(), the sort needs to be part of the query() itself
  • If you have never used SQL before, it might appear more complex first. But don’t give up here!

Alright, I won’t go into a more detail, but if you’re interested on how to use Query() – here’s a great article from CodingIsForLosers – ‘A weapon of mass laziness‘.

Today, I’d like to present a nifty solution to a problem I recently encountered – querying column names!

The problem: You can’t Query the Column Header by Name in Google Spreadsheets

In query() language, it is not possible to reference the header of a column you’d like to include in your query.

What do I mean with that?

Let’s use Sample table with the following information:

If I want to get the average salary of this data set, my query() is as follows as ‘salary’ is in column “E”:
=query(A1:G9,”select avg(E)”)
Pretty simple, right?
However, what if the raw data changes, and the ‘salary’ column is not in column ‘D’ anymore? Or someone just accidentally adds another column in?

In a normal function, Google Sheets would shift the reference accordingly – but not here. It will break the function. Not fun! 

The solution: Query by Column Name

We just need to combine our query() function with the substitute() and match() function, and we’ll be able to reference the actual name of the column. So if the table structure changes, our formula still works! 😁
Let’s use our query from above:
=query(A1:G9,"select avg(E)")
We need to replace the ‘E’ reference with something that’s more versatile, based on the column header name. Let’s dig into it!

1. First, we need a formula that returns the position of the column.

We can use the ADDRESS() in combination with MATCH() for that. ADDRESS() returns the cell position as a string. The structure is as follows:

ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

  1. row: this can be “1”, even if your header column is not in row 1 – because it just depends on the range that you provide.
  2. column: this is unknown, so we’ll use the MATCH() function to find the number of the column we want to reference (e.g. in our example”salary”).
  3. [absolute_relative_mode] – optional, “1” be default. 1 is row and column absolute(e.g. $A$1), 2 is row absolute and column relative (e.g. A$1), 3 is row relative and column absolute (e.g. $A1), and 4 is row and column relative (e.g. A1). We are using 4 in this example to keep it fixed.
  4. use_a1_notation – optional, “TRUE” as default.
  5. sheet – optional, absent by default. Needs to be changed if you’re referencing a different sheet (in our case we don’t).

What do we need to set for MATCH()?

Here’s the MATCH() structure: MATCH(search_key, range, [search_type])

  • search_key – The value to search for. We’re using “salary” in our example.
  • range – This is the range of all of the possible header you want to reference. Note, it must be one-dimensional (e.g. A1:F1). In our case, it’s just 1$:$1. ($ to make the reference absolute).
  • search_type – optional,  1 by default. We want to make sure the function is searching for an exact match and the range is not sorted, so we’re using 0

Ok, so 🥁.. here’s our function:

=ADDRESS(1,MATCH("salary",$1:$1,0),4)

Cool beans! However, we don’t want E1, we only want to return the column letter (E).

2. Remove the row number from the returned cell.

We’ll just wrap the function with a simple SUBSTITUTE() function to replace ‘1’ (with nothing): =SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","") 

Note: This only works if you’re header column is in the first row. There are ways to make this more flexible, e.g. by searching for the first number in the string (the row), and remove everything after that (w/ LEFT() function). Add a comment if you’re having problems with that!

3. Combine it back into the Query() function

Adding this into the query function looks a bit complicated, but there’s a standard format for this:

  • The referenced column must be wrapped in single quotes (”) for strings (if you’re referencing numbers, you don’t need that)
  • Close and re-open the query with double quotes (“”)
  • Use ampersands (&) to add the referenced cell to the query string
Here’s how it looks like: 
=query(A1:G9,"select avg("&SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","")&") ")
Looking good…!

Nice… so what’s so cool about this? Well, I mentioned that it makes the whole spreadsheet more versatile and less error prone. However, there’s more.

Bonus: How to use query() with drop down fields

Wouldn’t it be cool to have drop downs to get different values from our data range in case we need them?

Let’s assume we’re interested not only in the average salary, but also the average age.  How do we get that?

We just need to change the value of our input cell (D10 in my example). And we can use a dropdown by going to Data>Data validation and use the “List of Items” functionality.

Dropdown w Google Sheet

We can even use this to change the aggregate function. maybe we’re interested in the MAX, MIN and SUM as well? Well, here you go. We’re just using another cell to provide these as drop downs, and then reference in the query:

=query(A1:G9,"select "&D11&"("&SUBSTITUTE(ADDRESS(1,MATCH(D10,$1:$1,0),4),"1","")&") ")

Here’s my sample spreadsheet if you’d like to play around or make a copy for yourself: Click to see sample spreadsheet

All working? Congrats! Now go and make some awesome spreadsheets 🤠 and if it was useful, please leave a comment, tweet me at @joradig or drop me a line on LinkedIn. It really keeps me going and motivated.

Thanks!
Johannes

The Ultimate Guide on Personalising your Website for Free Using Google Tag Manager [Updated May ’18]

Good Marketing is all about creating a customer experience that’s relevant to a person’s intent. It’s the reason you turn to Amazon for one product, but end up buying 3. It’s why you love browsing your Netflix, and turn to Spotify to discover new tunes.

But you’re probably not working for Netflix, and don’t have a team of data scientists and programmers developing a recommendation engine. So where do you start creating an experience that’s relevant to your visitor’s intent? **Update May ’18 to include my slides about this topic**

Continue reading The Ultimate Guide on Personalising your Website for Free Using Google Tag Manager [Updated May ’18]

3 Biggest Mobile Ad Tracking Pitfalls Explained & How to Deal With Them [Updated June ’19]

Missing 30% of Mobile Revenue

3 Biggest Mobile Ad Tracking Pitfalls Explained & How to Avoid them

Updated June ’19 – Thanks to Chris Shuptrine from Adzerk for helping me update this article! 

At its start, mobile advertising suffered from a major flaw: tracking performance was a nightmare. And how has that changed in the 10+ years we’ve had smartphones?

Well, not that much.

In fact, due to privacy regulations and moves by Apple and Google to limit cookie tracking, it’s arguably gotten more difficult.

In this post I’m going to outline the major handicaps you should watch out for when doing mobile ad attribution – and how to address them. This insight is based on my time as PayPal EMEA’s Display & Mobile Advertising Manager, where, as you can expect, we spent a lot on mobile ads. 

Don’t hesitate to add comments or questions… I am certainly not covering every aspect here and tried to keep things simple. A ‘thank you’ goes to the team at Conversant UK, who have been instrumental in getting these insights

Continue reading 3 Biggest Mobile Ad Tracking Pitfalls Explained & How to Deal With Them [Updated June ’19]