The final two SQL patterns posts focus on the core components of search: content and keywords. Today we’ll analyze how content (URLs) attracts traffic from search, and we’ll wrap up the series by analyzing how users search for that content. The URL dimension is perhaps the most important dimension in SEO because it represents the […]
SEO SQL Pattern #3: Grouping and filtering URLs for content analysis Read More »
A couple of weeks ago, while building out a scoring and segmentation model for a client, I arrived at a situation that Attio’s built-in reporting was not capable of handling. I explored a few options for analyzing prospect data (People and Companies) with varying degrees of success. I wrote this post to help you think
Attio Analytics Tooling: My Search for a Lightweight, Flexible GTM Analytics Stack Read More »
The time dimension is often overlooked in digital analytics. We take it for granted because the whole point of digital marketing is to grow—traffic, customers, and revenue—over time. Performance is inherently a factor of time! This post follows from the post on Pattern #1, which is all about ranking data and does not take time
SEO SQL Pattern #2: Analyzing performance over time Read More »
Google Search Console only provides a few metrics: impressions, clicks, CTR, and average position. The first three are easy to understand, but the average position can trip up even the savviest analyst if they’re not careful. That’s why I started this series on SQL Patterns for SEO with a (perhaps overly) thorough explanation of how
SEO SQL Pattern #1: Aggregating Position Metrics and CTR Curves Read More »
Classification is the generative AI-based pattern I use most frequently. Why? Three reasons: It’s perfect for tackling big tasks where 80% accuracy on 100% of cases is better than 100% accuracy on just 20%. This week alone, I’ve used classification to: Since Google Sheets is one of my go-to tools for small/quick data problems, I
Using ChatGPT in Google Sheets for Classification (custom function included) Read More »
I needed to know when a post was posted… I was recently working with a client who had impressive success driving new leads via leadership posts on LinkedIn. We found a reliable relationship between posting and commercial activity, whether in DMs or on their marketing site. The problem was that I wanted to know exactly
Three Ways to Find the Exact Date of a LinkedIn Post Read More »
At the beginning of every month, Google Search Console sends out its Monthly Search Performance email to site owners. Each month I receive this email, I’m reminded of two things: 1) My site’s performance doesn’t really change much, and 2) These statistics are a great way to demonstrate how to write SQL for the Google
Google Search Console Monthly Performance Emails in SQL Tutorial Read More »
A few days ago, Cyrus Shepard posted an example about using Levenshtein distance to compute the similarity or dissimilarity (aka the edit distance) between two strings. His example showed how to apply Levenshtein distance in Google Sheets in the context of written content, but it inspired me to demonstrate another common application: query similarity. Since
Longtail Query Analysis in BigQuery using Levenshtein Distance Read More »
Discover the differences between the searchdata_url_impression and searchdata_site_impression tables, and learn how they can help you analyze your site’s performance at different levels of granularity.
A Primer on Google Search Console Tables in BigQuery Read More »
In today’s all-or-nothing startup environment, young businesses need to be near-perfect in every aspect to succeed. As startups grow, analytics is the key to success. And yes, data-driven insights can be a game-changer. However, a widespread challenge persists: most startups struggle to integrate analytics seamlessly into their day-to-day business operations. As a founder, you have
Fostering Collaborative Analytics: A Founder’s Guide Read More »
