Learning to Code for Technical Marketers

David Krevitt

Lover of laziness, connoisseur of lean-back capitalism. Potentially the #1 user of Google Sheets in the world.

If you prefer video, check out this discussion of coding for marketers with my friend Ryan Stewart, a marketer and founder of the WEBRIS digital agency:

 

The one thing we want to avoid as technical marketers, is the rabbit hole of shiny object syndrome.

Shiny object syndrome pulls us deeper and deeper into learning a new technical skill.  

It often forces us to lose sight of the actual reason we were learning it in the first place.

Friends, I have spent dozens of days of my life in this rabbit hole.  

I knew I was in a rabbit hole when:

I spent days on end learning how to code in Python or Javascript…when I could’ve accomplished the same thing in Google Sheets in 5 minutes.

I knew I was in a rabbit hole when:

I found myself explaining the fancy way I did an analysis to a colleague / client / boss… and they just nodded their head and stared into space.  

Please, heed this warning

Do not squander the precious days of your life learning technical skills that ultimately are not critical to your continued success.

Along those lines, I’m happy to share three key questions I ask myself for any project that we undertake – questions that help us avoid the ignominious rabbit hole.

These questions help us focus on getting stuff done with the minimum viable technical stack.

These questions identify how much “coding” is really necessary – and whether that means spreadsheet formulasSQL statements or a Python script.

These questions keep us out of the weeds:

  1. What’s the earliest version of this I can share with someone?
  2. Is this the best stack for the job?
  3. Will I be able to explain this / train someone on it?

Our projects generally revolve around analyzing marketing data, so throughout this post we’ll focus on a technical SEO example that’s close to our hearts:

Tagging an organic search keyword with ‘searcher intent.’

It’s an interesting data challenge, because it can be done in literally any stack I mentioned above.  But whether you’ll ultimately solve it in Google Sheets, SQL or Python is highly dependent on scale.  

Let’s dive into how we’d lay out the project, using those 3 questions as our guideposts.  

1: What’s the earliest version I can share?

To avoid mistakenly careening into a rabbit hole, it helps to get a 2nd opinion.

Sharing your work early with clients / colleagues will help you validate that what you’re building is at least going in the right direction (aka you’re not in the wrong rabbit hole).

As an example

Let’s get into our example: the common SEO data analysis task of tagging keywords with “searcher intent.” 

Understanding why people are coming to our site organically helps us design the site properly to match their expectations – answering the question:

Are people organically coming to this page looking for information, to buy something, or something else?

For example – “google sheets tutorial” would be an informational keyword.  

“Google sheets custom dashboard” would be a transactional keyword, since the searcher is likely looking to purchase something custom.

Knowing this can help you focus your content on what searchers are looking for, rather than what you think they’re looking for.

So the key technical challenge here is:

  1. Input a list of mappings (keyword contains ‘tutorial’ -> tag as informational keyword)
  2. Input a list of search keywords (from Google Search Console or elsewhere)
  3. Output a list of the mappings (informational, transactional, etc) that apply to each inputted search keyword

Like I mentioned above, this challenge can be solved handily in either Google Sheets, Apps Script, SQL, Python (really any language).  

So what’s our first draft?  

No matter what stack we choose, we’ll ultimately probably use something called regex (for regular expressions) to apply keyword intent tags.

Regex can be run in Sheets, SQL, or Python – so a quick prototype in Sheets can be a foundation that we can build on.

This is the case for almost any marketing data analysis – you can prove the concept at a small / medium scale with Google Sheets formulas, then scale up from there if need be.

For example, if we wanted to tag keywords for a 1 million page site, Sheets is unlikely to handle the entire dataset gracefully.

But it can help you nail down your business logic on a 10,000 page sample, which you can then port into a database as you scale up.

The basic logic we’d apply for tagging a keyword as “informational” in Sheets would be:

=if( regexmatch(“search keyword”,”how to|tutorial”) , ”informational”, …))

That formula tags any keyword containing either “how to” or “tutorial” (which makes the regexmatch true) as having an “informational” intent:

Having a prototype in Sheets allows you to at least validate your intent tagging logic on a segment of ~100k keywords, to make sure the output will be helpful to your team.

2: Is this the best stack for the job?

So do we stop at Google Sheets after validating our prototype, or do we push onwards to SQL or Python?

It all depends on the size of data that we’re working with.

If we generally work on sites smaller than 10k pages, then Sheets will do just fine.

If we’re much higher than that (say 100k+ pages), then Sheets will eventually grind to a half – we’ll want to move to a database platform like BigQuery for this analysis.

Get it together!

Regardless of whether we choose to stay in Sheets or move to a database, the important thing is getting all of our data in one place, so that we can analyze it consistently.  

The deepest rabbit hole you can fall into is not being able to repeat your work at will – chasing ghosts as you try to put the pieces back together.

In Sheets

So if we’re working in Sheets, we’d use a Add-ons like Blockspring, Search Analytics for Sheets or Supermetrics to pull in keyword data from Google Search Console directly into our Sheet.

In SQL

If we’re working in BigQuery, we use our own BigQuery Recipes internal tool to pull data from the GSC API into our project.  

Just for fun, here’s how we’d execute that same keyword intent tagging Regex in BigQuery SQL:

CASE

WHEN regexp_contains(search_query, {{informational_intent_regex}}) THEN ‘informational’

WHEN regexp_contains(search_query, {{transactional_intent_regex}}) THEN ‘transactional’

        ELSE ‘untagged’ END as keyword_search_intent

Note: If you’ve been eager to learn SQL in BigQuery, pick up our free course here.

Or in Python

Please, my friends – if you’re writing a Python script that you plan on sharing with your team, please set it to run on your database!  

That way, your team can more easily reproduce your results, vs having to assemble your dataset from the ground up before running the script.

You can easily use Google Cloud Functions to run a script on your BigQuery project.  

If you’re curious, we’d write a similar Regex matching script in Python like so:

informational_regex = re.compile(‘informational_regex’)

transactional_regex = re.compile(‘transactional_regex’)

for search_query in search_queries:

    if informational_regex.match(search_query):

        intent = “informational”

    if transactional_regex.match(search_query):

        intent = “transactional”

Etc…

These approaches (Sheets, SQL and Python scripting) all output the exact same results, and are roughly the same number of lines of code / formulas.

So it all comes down to data size, as well your personal comfort with each toolbelt.  

Because you still need to ask yourself…

3: Will I be able to explain this / train a colleague on it?

As we like to say at CIFL: “SOP or it didn’t happen.”

*See our Website Quality Audit for an example of how we write data analysis SOPs.

If you can’t clearly delineate what’s happening under the hood of your data analysis process, you’re in trouble.  

Trouble when it comes to training new team members.  Trouble when it comes to getting buy-in from your boss / clients / customers.  

So we must ask: can the final results of your analysis be shared easily?  

Can the rest of your team reproduce the analysis on their own, without you?

In Google Sheets

Does your Sheet have a ‘Getting Started’ tab, that lays out the exact steps someone can take to reproduce your analysis?

Does it have a ‘Summary’ tab, that contains everything folks truly need to know about the results?

In SQL

We highly recommend using a framework like DBT when building SQL models.

DBT allows you to store your SQL files in neat folders of a Github repo, that can be accessed by anyone that needs them.  

No more storing SQL queries in random files on your desktop!

And, most importantly, if you’re using a database like BigQuery, it’s super easy to produce reports with a visualization tool like Data Studio:

In Python

Just like SQL models built with DBT, we highly recommend that any Python scripts you write live on Github, rather than on your local machine.  

Your local machine is an intellectual cul-de-sac where good scripts turn bad.

Publishing a Git repo forces you to write a Readme (which can contain key links to results), and generally makes you appear to your team / clients that you’ve got your act together.

In closing

If you think you’re going into a rabbit hole right now, hang up and dial 911.  

OK, maybe don’t do that – but definitely step back for a moment, take a deep breath or a walk around the block, and then come back to your keyboard.

If you’re not rushing your work, you’re at a much lower risk

But to wrap up, for the maximum Shiny-object-syndrome risk deterrent, ask yourself these four questions before kicking off a project:

  1. Can we get all of our data in one place, to make life easier here?
  2. What’s the earliest version of this I can share with someone?
  3. Is this the laziest tool for the job?
  4. Will I be able to explain this / train a colleague on it / publish it?

Hope these help you keep your head out of technical rabbit holes!

If SQL ends up being your path of choice, our Getting Started with BigQuery course is here to help.

And if you’re curious to see how our team builds these tools internally, inquire about working with CIFL.

Wanna get more done?

✋ I can help with that…you can find everything we've ever built in this single Template Vault.

CRACK THE VAULT