Basic Command Line Commands

Let’s talk basic command line commands today! I’ve shared some recent command line articles on Google PageSpeed Insights on Command Line (CLI) and Running Google Lighthouse programmatically, in addition to older posts like Screaming Frog Command Line (CLI) automation.

I want to share some more fundamental components of command line and batch scripting: basic command line commands! (Spoiler alert: this article, like the others only covers Windows Command Line, not Mac or Linux.)

Getting Started With Command Line / CMD

To do anything with command line commands, you’ll first need to open up the Command Prompt! This is an easy start: click into your search bar (if you have Windows 10 or recent) and type “CMD.”

The Command Prompt app should pop up as the best match – click it!

Opening up command prompt for basic command line commands

I certainly don’t recommend doing this every time. For convenience, you can right click the Command Prompt icon in your taskbar once open, and pin it for quick access. (Similar for Start Menu if that’s your fancy.)

Once Command Prompt is open, you should see some information about the version of Windows you’re running, the copyright statement, and your default current directory below.

The Command Prompt (CMD) for basic command line commands

Basic Commands & Parameters in CLI / CMD

Surprisingly, I feel there’s a gentle learning curve in using command prompt vs. some other programming languages or settings. One of the first things you can do learn the basic “super user” kind of commands in command line.

What’s Here? Using the “dir” command

The “dir” command in CLI/CMD isn’t far from Hello world! The “dir” command will show you information about the folders and files in your current directory.

You may often find that the default current directory for Command Line is C:\Users\[YOUR USER NAME HERE]

The line containing the single period represents the current directory, and the line containing two periods represents the parent directory above your current. (More often than not, this will be the C:\Users\ directory.)

From left to right, the dates and times should represent when the files or folders were created. If the line is a folder, you’ll see the “<DIR>” notation. If the line is a file, the file size in bytes will be shown, along with the folder or file name on the far right. At bottom, a summary line will be displayed.

Helpers: Parameters in Basic Command Line Commands

Parameters are the first building block to doing fun things with basic command line commands. Parameters may also be known as options or arguments, depending on the setting.

Parameters can take the shape of words, symbols or letters, or sometimes a mix of these. Adding parameters can help you customize the output of your commands in command line.

Google PageSpeed Insights on Command Line (CLI)

Welcome back to more in adventures in Command Line! Previously, we were looking at running Google Lighthouse programmatically.

Today, we’re going to explore a similar exercise- running Google PageSpeed Insights on Windows Command Line (CLI)! Running PageSpeed insights on Command Line was probably the easiest self-coding experience I’ve had to-date. (More on the hard part later. 🙂 )

3 Prep Steps for Running PageSpeed Insights on Command Line

Unlike Screaming Frog Command Line automation, there are very few parameters or options to mess with for running data on a single URL. To run a single URL on PageSpeed Insights from Command Line:

  • Ensure CURL works in your command line (test CURL https://www.sampledomainhere.com) and check that the HTML source code is returned
    • Most recent Windows machines should already have CURL ready out of the box for CLI usage
  • Visit the Google PageSpeed Insights API documentation page
    • Get an API key (free) and create a project for credentials (also free) if needed
  • Copy the code sample into a code editor or text editor

Running Your First URL on PageSpeed Insights CLI

Is CLI for PageSpeed insights easy? Yes!

Effortless? No.

Here’s the out-of-the-box code sample from Google:

curl https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://developers.google.com

If you try to run that out of the box on Windows, you’ll like run into one or more of these errors:

  • 429 error: Too many requests / exhausted
  • Invalid syntax: Command(s) not recognized

First, you’ll need to append your API key to the end of the URL.

curl https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://www.zldoty.com&key=yourAPIKeyHere

Here’s what did the trick for me: placing escape characters in front of any special operators. Specifically, the ampersand (&) and equals (=) signs have special functions in CMD that need to be escaped with a carrot (^).

curl https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url^=https://www.zldoty.com^&key^=yourAPIKeyHere

Great, CLI Works! What’s next?

If everything works, you should see a flurry of JSON code scroll through the command prompt window – hooray! But here’s the rub, what do you with this information?

Some extra code will help us capture the output into a file:

curl https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url^=https://www.zldoty.com^&key^=yourAPIKeyHere -o fileName.json

Adding -o for output and specifying a file name will populate a table in CLI during processing with download progress stats. After the file has downloaded (sample runs took about 18 seconds), you should be able to see it in the default CLI directory in Windows File Explorer. (Typically C:\Users\youUserName)

Stay tuned for some more updates on how to scale this process across multiple URLs and extracting insights from the JSON file!

Actually Running Google Lighthouse Programmatically

Are you ready to run Google Lighthouse programmatically? This challenge has weighed on me for years. But with the help of some resources and friends, I’d love to show you a start-to-finish guide of truly running Google Lighthouse programmatically.

Programmatic Means Prep Work

First, my personal machine runs Windows 10, so my apologies to Mac & Linux users out there. Here’s what you need to get started, and huge shout-out to Upbuild for cutting the first part of the path here.

  • Install Node: https://nodejs.org/en/download/
  • After installation, search your start menu for “Command Prompt” or CMD
  • Assuming Node was installed correctly, and your command line is correctly configured, the following line in Command Prompt should install a Batch Lighthouse utility, a splendid bit of programming by Mike Stead
npm install lighthouse-batch -g

With this first round of prep, you can go get a list of URLs! For example, you might use Screaming Frog Command Line (CLI) automation for some pages.

JSON Files: Step 1 of Running Google Lighthouse Programmatically

The fastest way to run Lighthouse Batch programmatically is to use the -s method, shown below. Essentially the -s option allows you to paste in a comma-separated list of URLs to run.

Whether you’re working in Screaming Frog, or getting Google Search Console export data, you’ll want to use Convert.Town to transform column data into comma-separated values. (Hat tip again to Upbuild for this find.)

When you’re ready, open up command line, and let’s get Google Lighthouse running programmatically!

Here’s what your script should look like:

lighthouse-batch -s https://www.domain.com/page-1/, https://www.domain.com/page-2/, https://www.domain.com/page-3/, https://www.domain.com/page-4/, https://www.domain.com/page-5/ 

Hit enter, and watch it go! You may notice that the Command Line may take a few seconds to spool everything up before it starts running. After a few weeks of field-testing, I’ve been able to do up to 110 URLs consistently at a time without tripping an error.

You’ll see lots of wonderful code scrolling through the window, and after it finishes, you should have some JSON files in C:\Users\[YOUR USERNAME]\report\lighthouse

The Tricky Part of Running Google Lighthouse Programmatically

So if I ended this article here, it would be a poor summary of an Upbuild blog post. However! I found that while Google Sheets was indeed a brilliant solve, it didn’t meet my needs.

Here’s what I encountered in Google Sheets:

  • I may have broken some quotas for running scripts 🙂
  • The sheet took a long time to refresh queries
  • Because Google Sheets is less stable for large amounts of interactive data, it was slow to work with
  • Because the queries auto-refreshed, it wasn’t super handy for having a snapshot in time for later manipulation
  • Also, not being able to work with data locally (vs an FTP) didn’t go with my personal workflow very well

Again, huge ups to Upbuild for doing way more effort than I ever did on this! I just found that my needs diverged with the use case. Here’s what I did differently.

Larger Lighthouse Scale? Back to JSON

Here’s my bone to pick with trying run Lighthouse programmatically: JSON files make no sense to me as an SEO strategist and practitioner. (I get that JSON is great for developers for many reasons, but I’m not them.)

Sadly, Lighthouse only offers JSON or HTML reports, as far as I know. (If you know otherwise, please reach out! Would love to buy you a coffee. 🙂 ) So, that means you have to embrace the JSON.

I need tabular data, specifically, Excel exports to share with marketing and development teams for fixes from the report. So here’s where we are: we have a bunch of JSON files sitting on the hard drive.

This means command line batch script might be a good fit. Is it possible? How do we do it?

Command Line Batch Scripting to the Rescue!

In very simple terms:

  • We have information in File A (JSON) that needs to be extracted, and
  • Transformed into tabular (CSV) form, and
  • Both executed in a programmatic fashion

I unsuccessfully tried a number of manual batch script options, but if we go looking, there’s a solution to get us on our way! Meet JQ, a command line processor for JSON. Particularly, JQ can extract data from a JSON file and transform it into a CSV!

If we mesh JQ with some traditional batch scripting, we can achieve the requirements above.

A Little More Prep Work to Get Programmatic

You can download JQ here or at the link above (same URL.) There is some attention to detail here. In order to make JQ work properly in Command Line / Batch Script, you’ll need to copy the address of where you installed JQ and add it to your Path. Here’s a quick video showing you how to do it on Windows.

Once you have JQ successfully downloaded, installed and tested, it’s time to begin some scripting.

Programmatic Magic With Batch Script

Here’s both the fun and challenging part! In order to achieve a successful result, we need to lay out how our script will work in “psuedocode”. (Using pseudocode very loosely here as instructions to how the program should function.) Pseudocode follows.

Psuedocode for Programmatic Lighthouse Batch Script

For each JSON file (Sample JSON files attached) in directory C:\Users\yourusername\report\lighthouse, perform the following:

  • Extract the keys and values from this sub-section of each json file: audits > uses-rel-preload > details > items (We’re looking for url and wastedMS, and the values underneath)
  • Convert the extracted JSON data to a CSV, and place it in newly created folder named “uses-rel-preload” (without quotation marks) within C:\Users\yourusername\report\lighthouse. The CSV file should be named the same as the JSON file name (without the .json file extension)
  • Within the newly created CSV, insert two new columns to the left of “url” and “wastedMS”. Title the new column A “auditItem” and the new column B “sourceURL”.
  • In column A, populate “uses-rel-preload” (without qoutation marks) into each row where there are values in Columns C & D.
  • In column B, populate the CSV file name for each row where there are values in Columns C & D.
  • If there are no values in the JSON file from Step 1, create a new CSV with the JSON file name (no JSON file extension) in the new folder under C:\Users\yourusername\report\lighthouse (from step 2), with first column header “url”, second column header “wastedMS” and a single row below, with both cells containing “N/A”.
  • Within the newly created CSV, insert two new columns to the left of “url” and “wastedMS”. Title the new column A “auditItem” and the new column B “sourceURL”.
  • In column A, populate “uses-rel-preload” (without qoutation marks) into the first blank row.
  • In column B, populate the CSV file name into the first blank row.
  • Repeat/loop this action for every JSON file in the starting directory, but do not create a new folder in Step 2 for each following run, continue to use the same “uses-rel-preload” folder.
  • After the loop has completed for all files in the starting directory, Change directories to the new uses-rel-preload folder in Step 2. (C:\Users\yourusername\report\lighthouse\uses-rel-preload)
  • In that directory, combine all the CSV files (except for the header row in 2nd and subsequent CSV files, do not include the header row after the first file) into a new file titled uses-rel-preload.csv.

Batch Script for Programmatic Lighthouse Data Extraction

Alright, fire up your favorite text or code editor, and customize this to your needs! Personally, I will call the file via Command Prompt. Also, special thanks to Albin for his help making the JQ and CSV transformation.

:: Housekeeping and settings
TITLE
echo off
:: Enable replacing variables with values in the for loop
setlocal enabledelayedexpansion
:: Clear the screen
cls
:: Specify the working directory where the loop needs to perform
cd "C:\Users\yourusername\report\lighthouse"
:: For all the items (declare the working variable) in the set: active directory previously specified, all files matching *wildcard - all json files
for /f "tokens=*" %%A in ('dir /b /a-d *.json ') do ( 

	echo %%A
	set inputfile=%%A
	echo !inputfile!
	set inputfilename=%%~nA
	del !inputfilename!.csv_temp
	del !inputfilename!.csv

:: Parse the JSON with jq, extracting the specific details from the audit items and recommendations

:: Call JQ and a filter file that transforms the data into a temporary CSV file 
	type !inputfile! | jq .audits.\"uses-rel-preload\".details.items | jq -r -f filter.jq>>!inputfilename!.csv_temp

:: Create the first row of the CSV
	echo auditItem,sourceURL,url,wastedMs>!inputfilename!.csv

:: Check for empty files and provide fallback values if there are no recommendations
	for /f %%x in ("!inputfilename!.csv_temp") do set size=%%~zx
	if not !size! gtr 0 echo uses-rel-preload,!inputfile!,N/A,N/A>>!inputfilename!.csv

:: For all lines, make csv and add additional column
	for /F "skip=1 tokens=1,2 delims=," %%i in (!inputfilename!.csv_temp) do ( 			
			set url=%%i
			set wastedms=%%j
			set url=!url:"=!
			set wastedms=!wastedms:"=!
			echo uses-rel-preload,!inputfile!,!url!,!wastedms!>>!inputfilename!.csv
		)

:: Clear the temporary CSV files out of the working directory
	del !inputfilename!.csv_temp

	)

:: Make a new folder to house the files
mkdir uses-rel-preload


:: Move all CSV files into the audit type folder
move *.csv "uses-rel-preload"

:: Change the working directory
cd "C:\Users\yourusername\report\lighthouse\uses-rel-preload"

:: Start merging the individual files into a single CSV for the Lighthouse audit
:: Set a counter to 1
set cnt=1

:: For each file that matches *.csv, do the following loop
for %%i in (*.csv) do (
:: Call the counter 1 it's the first time running
  if !cnt!==1 (
:: Push the entire file complete with header into uses-rel-preload.csv - this will also create uses-rel-preload.csv
    for /f "delims=" %%j in ('type "%%i"') do echo %%j >> uses-rel-preload.csv
:: Otherwise, make sure we're not working with the uses-rel-preload.csv file and
  ) else if %%i NEQ uses-rel-preload.csv (
:: push the file without the header into uses-rel-preload.csv
    for /f "skip=1 delims=" %%j in ('type "%%i"') do echo %%j >> uses-rel-preload.csv
  )
REM increment count by 1
  set /a cnt+=1
)

Additionally, here’s the filter file referenced in the code.

def tocsv:
    (map(keys)
        |add
        |unique
        |sort
    ) as $cols
    |map(. as $row
        |$cols
        |map($row[.]|tostring)
    ) as $rows
    |$cols,$rows[]
    | @csv;

tocsv

Ok! There’s a lot going on. Let’s make sure a few important details aren’t missed:

  • This assumes that you left the JSON files in the default export location, C:\Users\yourusername\report\lighthouse\uses-rel-preload
  • This also assumes that you know how to run a batch script.
  • It’s not necessary per se, but for simplicity, I highly recommend you run and save the batch script files and resources all in the same directory as cited above
    • You’ll just have to be thoughtful about file path references if you spread resources/calls out.

The Output

Here’s what you should have:

  • The JSON files in the original directory
  • A new folder with the CSV files that contain URLs referenced in the Lighthouse uses-rel-preload report
    • The rows will contain N/A if the audit was empty, or had no results
  • Also within the folder should be a report-level CSV with all the CSVs containing all preload recommendations for all page you ran, combined into one sitewide file! Hooray!

Limitations on this Programmatic Lighthouse Approach

The astute will notice that this approach only covers one audit! Therein lies the downside to this approach, you’ll need to customize and program out additional code/files for other audits.

Assuming that you program out all the audits, you’ll want to create an audit summary folder within the first batch script file. This can be done with the mkdir directive in the script.

Additionally for each audit, you’ll want to copy the single overarching audit file into the audit summary folder. Using similar script as the last half of the above, you can combine all the audits into a single CSV that can easily be manipulated or ingested.

Google Search Console: Download & Export All Website Data

Another day, another blog post? This is a rarity for sure. At the moment, I’m starting to hit my stride on getting a few old items done in R. 🙂 If I keep writing more blog posts, I’ll need to start taking more photos again for the feature covers!

Quickly sharing how I was able to use R and Google Search Console to download & export all of my website’s search analytics data.

Why Use R to Download & Export all Search Console Data?

Even for the novice SEO, this is likely a well-tread frustration. Valuable data to make critical business decisions is sitting in Google Search Console. But you can’t access it at scale!

It’s either hidden behind the abysmal UI export limit, or you have to do so much filtering that’s impossible to scale the data extraction.

SearchConsoleR: Easy Google Search Console API for Unlimited Data Access

Similar to my previous post (How to Bulk Submit Sitemap URLs in Google Search Console) this article looks at the splendid SearchConsoleR package in R. See the link above for prerequisites.

The code to get all your data in Google Search Console is really quite brief. I’ve listed it below, as well a link to the Github gist.

Note that if it’s your first time using RStudio, you’ll need to use install.packages() to load up the necessary dependencies.

# Load packages
library(searchConsoleR)
library(dplyr)
library(ggplot2)
library(writexl)

# Authorize & choose Google profile
scr_auth()

# Specify website --- CLIENT
website <- "https://www.sample.com"
dimensions <- c("page", "query")

ttl_queries <- 
  search_analytics("https://www.sample.com", 
                   "2019-08-01", "2019-08-31", 
                   c("query", "page"), 
                   searchType="web", rowLimit = 100000)

# Write the data frame to an XLSX file
write_xlsx(ttl_queries, "ttl_queries.xlsx")

If the code has run correctly, you should have a (potentially) massive Excel file at your disposal! The possibilities are endless – data crunching in Excel, piping to a database or some other visualization software. Enjoy!

How to Bulk Submit Sitemap URLs to Google Search Console

Well, howdy! As with many of my posts lately, I find myself saying that’s it’s been awhile since I’ve posted something. It’s true!

Today, I’d like to walk you through how to bulk submit sitemap URLs to Google Search Console using R and RStudio. Before we begin the process of bulk submitting sitemap URLs, you’ll need a few prerequisites:

Prerequisites to Bulk Sitemap URL Submission: R Setup

  • Download and install the appropriate version of R for your system: https://cran.r-project.org/
  • For Windows users, search for “rtools”, download and install from the official cran.r-project.org site.
    • Mac/other OS users may need to as well for their respective OS. However, my primary machine is Windows (described above) and I’ve been able to use RStudio without issue on a Mac, without installing RTools. Doesn’t mean you don’t need it. ¯\_(ツ)_/¯
  • Download and install RStudio from the official site: https://rstudio.com/products/rstudio/download/

SearchConsoleR & Bulk Sitemap Submission in Google Search Console

How we bulk submit XML Sitemap URLs in Google Search Console is made possible by the excellent R package made by Mark Edmondson. Why R and SearchConsoleR? Approachability.

Many readers may find this method much more approachable than trying to get API projects, API keys, and other confusing configuration details just right, to work in Python/Jupyter notebooks. You’ll see what I mean in a minute.

Code for Bulk Sitemap URL Submission in Google Search Console

Below is sample R code for bulk sitemap URL submission in Google Search Console, using R and SearchConsoleR. You can also view my Github gist here.

# Load packages
library(searchConsoleR)
library(dplyr)
library(ggplot2)

# Authorize & choose Google profile
scr_auth()

# Specify website --- FULL URL
website <- "https://sample.com"

# Submit all manually found sitemaps
add_sitemap("https://www.sample.com","https://www.sample.com/sitemap_index.xml")
sys.sleep(1)

add_sitemap("https://www.sample.com","https://www.sample.com/page-sitemap.xml")
sys.sleep(1)

add_sitemap("https://www.sample.com","https://www.sample.com/post-sitemap.xml")
sys.sleep(1)

The code is fairly straightforward, and sadly, not automated. I’ll be working on an automated, scalable way of submitting all Sitemap URLs for all websites in a given Search Console account.

The one custom touch I’d note here is providing the one second system sleep to proactively avoid running afoul of rate limits, if you’re submitting a lot of URLs.

The Simple SEO Strategy & Plan Template

A quick Google search tells me that a lot of folks have already put together some fine SEO strategy plans & templates.

Here’s why I’m offering a different take on a SEO strategy plan template:

Websites are offering SEO launch plans, not ongoing search engine optimization management templates.

The Problem with a “Launch-Only” SEO Strategy / Plan

A study of millions of search results found that only 5% of pages will rank in the top 10 positions within 1 year. (Ahrefs) This data points suggests a deep disconnect between learning and executing SEO.

Moreover: this means many practitioners don’t understand how to manage SEO strategy on an ongoing basis.

It’s an understandable problem. I told a colleague this week that teaching tactics is easy, but teaching strategy is the hard part. (Like boiling the ocean?) Put another way:

Successful marketers don’t teach others how to do, they teach them how to think.

Viewing SEO Strategies & Plans Through an Ongoing Lens

If we’re teaching SEOs how to think, we won’t concern ourselves with an audit tool list. Rather, we’ll look at how we can apply a simple framework to any website and draw from an action plan template. Let’s groove.

There are distinct phases of SEO campaigns:

  • Launch/takeover
  • Growth
  • Maintenance
  • Recovery

We view SEO strategy purely through the lens of performance, with monthly (m/m) and year-on-year (Y/Y) time frames.

Launch / Takeover

Not included in our quadrants above, this phase is pretty self-explanatory. You can look at SEO audits, and other launch-focused plans & templates.

Growth Mode for SEO

If performance is up m/m and Y/Y, we’re in growth mode! We’ll only approach certain tasks with the emphasis of creating new content and expanding our search footprint. Troubleshooting is not something we’ll be concerned with often.

Growth mode as a constant state is rare! I’m fortunate to have some sites that spend quite a bit of time in this mode, but it doesn’t last forever. You’ll see the same for many top agencies. That’s why we have more than one mode. 🙂

In growth mode- you’re only concerned about maintaining growth trajectory. This typically means you’re focused on 3 things:

  • Keyword/marketplace research
  • Content creation
  • Internal linking from new content

Maintenance Mode in SEO

Its representation in 50% of the quadrant is a decent showing for reality. Many campaigns spend most of their time in this area; how you spend maintenance time dictates success or failure.

Maintenance mode, by our definition above, states that you’re up either m/m or Y/Y, but not both at the same time. Specifically, there are two reasons to be in maintenance mode: seasonality or breaking out of a plateau of results.

Maintenance Mode: Seasonality

You’ll find that if you’re trending up Y/Y, but not up m/m, that seasonality is often in play. Some examples:

  • A luxury retailer in January, after Christmas sales & gift-giving season
  • A travel company in April, after Spring Break
  • A DWI law firm in August, after July 4th festivities
  • A kids clothing company in September, after back-to-school

How will you know if seasonality is really a factor? You can maintain a database of key branded & non-branded terms and their search interest, using the pseudo-API to automate Google Trends analysis with Colab notebooks.

Maintenance Mode: Breaking out of a Plateau

Plateaus happen for so many reasons. If you’re breaking out of a plateau, you may be flat/down Y/Y, but up m/m.

In these instances, you may continue most of your growth mode activities, but you’ll also be looking to enhance existing content:

  • Determining if CTR engagement in-SERP is satisfactory, via custom CTR curves
  • Boosting existing content by enhanced internal linking from other pages, via internal linking analysis
  • Populating or enhancing metadata, from bread-and-butter descriptions to image alt tags or structured data / schema markup
  • Issuing redirects for decommissioned content, or eliminating redirect chains due to faulty internal linking

Recovery Mode in SEO

Let’s be real, no one wants to preside over a campaign that dips into recovery mode. If you’re in-house, the CMO wants answers last week, if you’re agency-side, the AD won’t back you up until the ship’s right, if you’re a consultant…well, the client is living in your voicemails.

It’s tough, but also happens – here’s the set list you’re looking at:

  • Troubleshooting & competitive analyses
  • Rewriting content or overhauling content
  • Filing spam or malware reports

Next Steps

This post deserves a better ending, but it’s 10 minutes past my son’s last feed of the night, and I need to go warm his bottle and feed him! But this post desperately needs (and maybe might get) some touchups to make this more detailed, insightful and action-oriented.

Bulk Google Trends Data Automation With a Pseudo-API

For Google Colab notebooks, and a simple CSV with Column Header Keywords, and up to 50 topics. Adjust the timeframe as needed.

pip install pytrends

import pytrends
import io
from pytrends.request import TrendReq
import pandas as pd
from google.colab import  drive
from google.colab import files

uploaded = files.upload()

pytrend = TrendReq()
for keys in uploaded:
  name = keys
df2 = pd.read_csv(io.BytesIO(uploaded[name]))

topics = df2.Topic
dataframe = pd.DataFrame()
i = 0
for topic in topics:
  if isinstance(topic,str):
    pytrend.build_payload(kw_list=[topic], timeframe='today 12-m')
    data = pytrend.interest_over_time()
    df = pd.DataFrame(data)
    if i == 0:
      dataframe = df
      del dataframe['isPartial']
      i = i + 1
    keyword_colum = df[topic]
    dataframe[topic] = keyword_colum
print(dataframe)  
dataframe.to_csv("keywords_trends.csv", encoding='utf-8-sig')
files.download("keywords_trends.csv")

Special thanks to Fozan again for assistance.

Automating Search Interest & Trends Data Access with Python

pip install pytrends

import pytrends
from pytrends.request import TrendReq
import pandas as pd # Sets up the Pandas data analysis library
import time
import datetime
from datetime import datetime, date, time
from google.colab import  drive
from google.colab import files

pytrend = TrendReq()

pytrend.build_payload(kw_list=['digital marketing', 'digital media', 'interactive marketing', 'digital advertising'], timeframe='today 5-y', geo='US')

data = pytrend.interest_over_time()
df = pd.DataFrame(data)
df.to_csv('pytrends_data.csv', encoding='utf-8-sig')
files.download('pytrends_data.csv')

Google Colab notebook for automating download of Trends search interest data, inspired from this Search Engine Land writeup. Special thanks to Fozan for his help with populating the CSV download portion. 🙂

Account Service & Management for Marketing & Advertising

Recently I wrote about digital marketing agency strategy & planning – 5 pillars or behaviors top performers should exhibit.

Today, I want to look at account service & management for marketing / advertising agencies.

From an agency perspective, client management is at best a continually perplexing challenge. For agency owners and executives – the joy of growing relationships is often clouded by financial pressure.

In fact, I would readily wager that most pain & suffering in any given agency comes mismatched expectations with clients. More specifically, a selective application of the Pareto Principle (or 80/20) rule tells us 80% of our headaches at a digital marketing agency come from 20% of the clients.

There’s doubtless plenty of discussion to be had managing expectations, or about revenue diversification. But let’s look at a couple of frameworks A) financial and B) operational that can help simplify client service.

First- are you managing your client portfolio by profitability and touch?

There are two easy squares in the field above: top left and bottom right.

First, if you have a high touch, low profitability client – you can either A) raise retainer rates/prices, or B) fire them. You can try to reset expectations, but many high touch clients that cause pain do not respond well to this. One way you can automatically bake this logic in is by testing a recurring percentage increase to your retainer with these and/or other client types.

Second, if you have a low touch, high profitability client – keep and protect them! They’re rare birds.

Generally, it’s desirable to have high profitability clients over low- yet strangely for both of the quadrants above (L/L, H/H) a potential solve is automation. (With different purposes.)

Low touch, low profitability clients aren’t inherently bad – there are a lot of niche, perhaps even respectable agencies built on these kinds of clients. High touch, high profitability clients are only going to be sustainable if you become the likes of a big box agency.

Either way, you need to A) scale your capabilities to get more similar low touch clients (L/L), or find ways to get your time back against high touch clients (H/H). Especially so you can excel at the 5 pillars of agency strategy!

One small step forward you can take in automation is prioritizing work to become proactive, vs. blindly following retainers & SOWs until your teeth fall out from stress-clenching.

If you can automate portfolio dashboards of your clients – you can always have a real-time picture of what needs to happen in your client’s businesses. (Ex: a digital / performance marketing engagement that could include organic or paid media is shown below.)

Via Data Studio, Power BI, Tableau, etc., you can avoid overplaying winning accounts and quickly servicing trouble accounts before damage becomes significant.

This kind of system can start with high-level traffic and engagement figures. However, this method also holds promise for you to automate wins and insights for clients, drilling down to page and keyword insights that could be automated or outsourced so you can proactively send victories to clients, to maintain healthy temperature and momentum in your engagement.

One other way you try to reduce service burdens is to move to a JIRA-style ticketing system with your clients for requests – and personal interaction comes from you, on your terms.

I’m sure this won’t make any Pulitzer nomination roundups (rightly so), but this has been enjoyable to right, as I explore more ideas about excellence in marketing and account service.

Marketing Data Lakes & Warehouses

My most recent post covered strategy & planning for digital marketing agencies, specifically from an SEO perspective.

In my mind, one of the most compelling questions was this: why aren’t there more success stories with SEO agencies? From my young perspective, I call out 5 things successful entrants can do for the best shot at success.

We may also find that these pillars of success (ex: depth of product vision, operational efficiency) rarely exist in isolation.

That brings us to marketing data lakes and warehouses! Perhaps popularized by Informatica, data lakes are essentially all your data tossed into one space- hooray!

In their ebook, Informatica notes that data lakes are well-suited for large enterprises at $100MM+ and above. I would largely agree (for many reasons) and would also assert this leaves ample learning opportunity for brands below that threshold.

For the large enterprise above, there are often prerequisites to reaching that level. Specifically, you’ll often need to achieve certain sophistication before advancing to a level of ad hoc ML analyses of unstructured data in the cloud.

If we’re being honest, I’m probably bending and abusing some terminology here, but bear with me! For the vast majority of businesses (which also site below that revenue or capitalization threshold) – there’s a lot of power in data lakes, for the purpose of building small data – focused data warehouses.

To the points of my earlier posts, you need cohesive data that can paint a clear, end-to-end picture of your digital business. To that end, consider the following:

With the proper platforms, API scripts/pulls and storage, you can amass all of your key marketing data into one, or a few areas. Depending on your tactical execution, SQL joins or other BI/dashboarding software can be used to unite your data by URL.

From this picture, you can easily pivot, filter, splice, etc. to see what has been performing, how, and where the impact is going to your business.

Ex: in one picture, you can immediately juxtapose log file activity on your site with Search Console data to see where Google got hung in your site, as well as quickly identifying how much it’s impacting your visibility, and also quickly seeing which keywords are most affecting your business.

To take it a step further, this can be scaled across multiple clients, where you may write the client name or vertical into separate columns into all of your respective data pulls. This also facilitates large-scale rollups of your data. It’s my estimation that a compelling example of this may be Merkle’s quarterly digital marketing reports.

As with previous posts, I hope to continue peeling back this onion of excellence in marketing. However, my next planned immediate posts may pivot slightly to operational efficiency in the context of providing excellent client service.