SEO 키워드 분석 자동화, 구글 시트 함수로 경쟁사 데이터 추출하고 관리하는 법

 

SEO 최적화의 성공은 경쟁사보다 한 발 앞서 ‘성장 기회’가 있는 키워드를 발굴하고, 이를 체계적으로 관리하는 능력에 달려 있습니다. 수동으로 경쟁사 웹사이트를 분석하고 데이터를 복사-붙여넣기 하는 방식은 시간 낭비가 심하고, 방대한 데이터 속에서 의미 있는 인사이트를 놓치기 쉽습니다.

구글 시트(Google Sheets)의 강력한 데이터 통합 함수인 IMPORTXML과 IMPORTRANGE, 그리고 QUERY 함수를 결합하면, 경쟁사의 웹사이트 정보를 자동으로 추출하고, 우리의 데이터와 비교 분석하며, SEO 키워드 전략을 완벽하게 자동화할 수 있습니다. 이는 애드센스 승인 후 고수익을 달성하는 데이터 기반 전략의 핵심입니다.

지금부터 구글 시트 함수를 활용하여 경쟁사 데이터를 자동으로 추출하고, 키워드 분석을 효율적으로 관리하는 실전 비법을 안내합니다.

1. IMPORTXML을 이용한 경쟁사 데이터 자동 추출 (웹 크롤링)

 

경쟁사 웹사이트의 핵심 데이터를 수동으로 복사할 필요 없이, IMPORTXML 함수를 사용하여 구글 시트로 실시간으로 가져옵니다. 이는 경쟁사의 최신 콘텐츠 발행 현황, 제목, 메타 설명 등 SEO 핵심 요소를 모니터링하는 데 필수적입니다.

1.1. 경쟁사 포스팅 제목 목록 가져오기

 

• 목표: 경쟁사 블로그의 최신 포스팅 제목 목록을 추출합니다.

• 핵심: 웹페이지에서 제목 요소의 XPath 경로를 정확하게 찾는 것이 중요합니다. (크롬 개발자 도구의 ‘Copy XPath’ 기능 사용)

• 수식 예시: =IMPORTXML("경쟁사 URL", "//h2[@class='post-title']/a/text()")

  • 설명: URL에서 클래스가 ‘post-title’인 모든 h2 태그 아래의 링크 텍스트를 추출합니다.

1.2. 메타 설명(Meta Description) 및 H1 태그 추출

 

메타 설명은 SEO에 직접적인 영향을 주며, 경쟁사의 콘텐츠 의도를 파악하는 데 중요합니다.

• 목표: 경쟁사 포스팅 URL(A열)의 메타 설명을 추출합니다.

• 수식 예시: =IMPORTXML(A2, "//meta[@name='description']/@content")

  • 설명: A2 셀의 URL에서 <meta name="description"> 태그의 content 속성 값을 가져옵니다.

1.3. 데이터 오류 방지 및 자동 확장 (IFERROR + ARRAYFORMULA)

 

웹사이트 구조가 변경되거나 서버 문제로 데이터 추출에 실패할 경우 #N/A 오류가 발생합니다. 또한, 수많은 URL에 대해 일괄적으로 추출해야 하므로 ARRAYFORMULA를 결합합니다.

• 배열 수식: =ARRAYFORMULA(IF(ISBLANK(A2:A), "", IFERROR(IMPORTXML(A2:A, "//h1/text()"), "오류/데이터 없음")) )

  • A열에 URL이 없으면 공백(“”)을, 오류가 발생하면 “오류/데이터 없음”을 표시하여 안정적인 데이터 관리를 가능하게 합니다.

2. 키워드 난이도 및 기회 지표 관리 자동화

 

추출한 경쟁사 데이터를 우리의 내부 데이터(키워드 난이도, 검색량 등)와 결합하여 어떤 키워드가 ‘공략할 가치’가 있는지 판단해야 합니다.

2.1. VLOOKUP 대신 INDEX+MATCH를 이용한 키워드 난이도 매칭

 

키워드 분석 툴에서 추출한 키워드 난이도 데이터를 내부 시트에 정리해두고, 경쟁사 포스팅에서 추출한 키워드와 매칭시킵니다.

• 목표: 경쟁사 키워드(C열)를 기준으로, 내부 ‘키워드 난이도’ 시트(B 시트)에서 난이도 점수(D열)를 가져옵니다.

• 수식 예시: =INDEX(B!D:D, MATCH(C2, B!A:A, 0))

  • 설명: 경쟁사 키워드가 B 시트의 키워드 목록(A열) 중 몇 번째 행에 있는지 MATCH로 찾고, 그 위치를 INDEX로 난이도 점수 열(D열)에 적용하여 값을 가져옵니다.

2.2. QUERY 함수로 ‘저난이도 고검색량’ 키워드 선별

 

경쟁사가 이미 포스팅했더라도, 난이도가 낮고 검색량이 높은 키워드는 우리에게도 기회가 될 수 있습니다. QUERY 함수를 이용하여 해당 키워드만 선별합니다.

• 데이터 가정: A열(키워드), B열(경쟁사 포스팅 여부), C열(검색량), D열(난이도 점수)

• 쿼리 예시: =QUERY(A:D, "SELECT A, C, D WHERE D < 50 AND C > 1000 AND B = 'N' ORDER BY C DESC", 1)

  • 설명: 난이도 점수가 50 미만이고(D열), 검색량이 1,000회 초과이며(C열), 아직 포스팅하지 않은(‘N’인 B열) 키워드를 검색량 순으로 정렬하여 ‘황금 키워드’ 목록을 생성합니다.

3. SEO 데이터 통합 보고서 자동화

 

경쟁사 데이터와 내부 데이터를 하나의 보고서로 통합하여, 전략적 인사이트를 도출하는 대시보드를 구축합니다.

3.1. IMPORTRANGE를 이용한 통합 보고서 구축

 

여러 팀원이나 프로젝트별로 분리된 시트(예: 콘텐츠 팀의 발행 목록, SEO 팀의 순위 추적)를 하나의 마스터 시트로 통합합니다.

• IMPORTRANGE 결합: ={IMPORTRANGE("시트1 ID", "Data!A:C"); IMPORTRANGE("시트2 ID", "Data!A:C")}

  • 설명: 두 개의 서로 다른 시트에서 동일한 구조의 데이터(A:C)를 가져와 하나의 열로 병합합니다.

3.2. 조건부 서식으로 경쟁 상황 시각화

 

경쟁사 분석 데이터와 우리 데이터를 나란히 두고, 경쟁사 대비 우리의 상태를 시각적으로 강조합니다.

• 활용: 경쟁사 포스팅의 글자 수(E열)와 우리 포스팅의 글자 수(F열)를 비교하여, 우리 글자 수가 경쟁사보다 20% 이상 적을 경우(품질 위험) F열에 경고 서식을 적용합니다.

• 수식 예시: =$F2 < $E2 * 0.8 (F열 값이 E열 값의 80% 미만일 경우 서식 적용)

3.3. REGEXMATCH로 핵심 정보 검색 자동화

 

경쟁사의 제목이나 URL 목록에서 특정 키워드 패턴(예: ‘2025년’, ‘최신’)을 포함하고 있는지 REGEXMATCH로 검사하여, 경쟁사가 최근 어떤 트렌드를 공략하고 있는지 빠르게 파악합니다.

• 수식 예시: =REGEXMATCH(제목 셀, "2025년|최신|업데이트")

4. 자동화 환경의 안정성 확보 및 관리

 

경쟁사 분석 자동화 환경을 지속적으로 안정적으로 운영하기 위한 팁입니다.

• IMPORTXML의 한계 인식: IMPORTXML은 웹사이트 서버 상태나 구조 변경에 매우 취약합니다. 데이터 로딩이 느리거나 #N/A 오류가 반복되면 경쟁사의 웹 구조가 바뀌었는지 확인해야 합니다.

• 트리거 및 새로고침: IMPORTXML은 캐시되어 실시간 업데이트가 안 될 수 있습니다. Apps Script를 이용하여 주기적으로 데이터를 ‘새로고침’하는 버튼을 만들거나, 시간 기반 트리거를 설정하여 자동 업데이트를 강제하는 것이 좋습니다.

결론: 데이터 기반 경쟁 우위 확보

 

구글 시트의 IMPORTXML, IMPORTRANGE, QUERY, INDEX+MATCH 조합은 경쟁사 웹사이트를 수동으로 분석하는 시대의 종말을 고합니다. 이 함수들을 통해 경쟁사의 핵심 SEO 데이터를 자동으로 추출하고, 우리의 내부 키워드 데이터와 결합하며, 전략적 기회가 있는 키워드를 즉시 선별하는 시스템을 구축할 수 있습니다.

지금 바로 구글 시트의 데이터 자동화 기능을 마스터하여, 경쟁사보다 빠르고 정확한 데이터 기반 전략으로 SEO 성과를 극대화하시길 바랍니다.