Design a content gap tool: data sources and core logic

WHAT THIS TESTS: This question evaluates whether you can design a batch data pipeline that ingests noisy third-party datasets, normalizes heterogeneous schemas, and computes a set difference at scale. Interviewers care about your comfort with API constraints, cost engineering, and data modeling more than perfect SEO domain knowledge. They want to see you decompose a vague product request into concrete tables, filters, and joins.

A GOOD ANSWER COVERS: First, data sources. Name at least one commercial SEO API such as Ahrefs, Semrush, or Sistrix for competitor keyword rankings, and pair it with internal data from Google Search Console or your own clickstream logs. Second, normalization. Explain that keywords must be deduplicated and lowercased, that subdomains and country codes need canonicalization, and that geography and device type must be part of the join key. Third, the core logic. Describe a left anti-join or set difference where the competitor dataset is the left table and your domain is the right table, filtered so the competitor ranks in the top 10 or 20 while your domain either does not appear or ranks below position 100. Fourth, cost and scale controls. Mention that these APIs charge per row or per domain, so you might sample head terms, cache results, or run incremental batch jobs weekly rather than daily. Fifth, prioritization. A raw gap list is useless, so layer on search volume, keyword difficulty, and business relevance scores before surfacing results to the product manager.

COMMON WRONG ANSWERS: A major red flag is suggesting direct Google scraping at scale, which violates terms of service and fails on CAPTCHAs. Another is framing the problem as a single cheap SQL query without acknowledging that commercial keyword indexes can contain hundreds of millions of rows and cost thousands of dollars per month to query. Candidates also stumble by omitting the join dimensions of location and device, which causes false positives when a competitor ranks in the UK and you rank in the US. Finally, never deliver a raw diff; if you forget to score and rank the output, you are handing the PM an unactionable firehose.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle ten million keywords efficiently, which pushes you toward columnar storage like Parquet or a warehouse like Snowflake or BigQuery. They might ask what you do when the API lacks long-tail coverage, prompting a discussion of extrapolation or blending with Google Ads Keyword Planner data. Expect a question about incremental updates, where the right answer is to track a high-water mark on crawl date and only ingest changed SERPs. They could also probe freshness versus cost, leading to tiered refresh rates for high-volume keywords versus tail terms.

ONE CONCRETE EXAMPLE: Suppose you are comparing your domain against one competitor in the United States mobile index. You pull the top one hundred thousand keywords where the competitor ranks between position one and twenty from Ahrefs. You pull your own GSC data for the same keyword universe. You join on lowercase keyword, state, and device type. The result set is every keyword where the competitor has a position and your best position is null or greater than one hundred. You then sort that list by monthly search volume multiplied by a relevance score for your product category, returning the top five hundred opportunities.