엑셀 실시간 키워드 분석, 초보자도 가능한 자동화 대시보드
엑셀 키워드 분석 대시보드 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- ✨ 엑셀 키워드 분석 대시보드 템플릿회원자료
안녕하세요, 여러분! 오빠두엑셀 전진권입니다.
오늘은 키워드 분석에 많은 시간과 비용을 투자하고 계신 분들을 위하여, 엑셀만으로 키워드 분석을 자동화하는 대시보드를 준비했습니다!😎
엑셀 기본 기능만으로 제작한 키워드 분석 자동화 대시보드를 업무에 바로 활용해보세요!😎 엑셀의 파워쿼리 기본 기능만 활용하여, 네이버 검색광고 + 데이터랩 API를 연동해서 네이버, 쇼핑, 구글, 쿠팡의 연관 검색어와 트렌드를 한 번에 분석하는 대시보드 사용법에 대해 알아보겠습니다. (본 대시보드는 윈도우 엑셀 2016 이후 모든 버전에서 사용 가능합니다. Mac 엑셀에서는 사용할 수 없어요!❌)
오빠두Tip : 오늘 강의에서는 키워드 분석 대시보드를 사용법에 대해 알아봅니다. 대시보드를 제작하는 전체 과정은 아래 위캔두 멤버십 라이브 전체 영상을 확인해주세요!👇
개인 정보 수준 변경하기
- [데이터] 탭 - [쿼리 옵션]으로 이동합니다.
[데이터] 탭 - [쿼리 옵션]으로 이동합니다. - 전역 그룹에서 [개인 정보]를 선택한 후, '개인 정보 수준'으로 [항상 개인 정보 수준 설정을 무시]를 체크하고 쿼리를 다시 실행합니다.
전역 그룹의 [개인 정보]를 선택한 후, '항상 개인 정보 수준 설정을 무시'를 체크합니다.
네이버 검색광고 API키 신청하기
- 예제파일을 실행한 후, 화면 상단에 표시되는 [콘텐츠 사용] 버튼을 클릭해서 서식에 사용된 파워 쿼리 기능을 활성화합니다.
콘텐츠 사용 버튼을 클릭해서 파워쿼리를 활성화합니다. - 마지막 시트인 [API 정보] 시트로 이동한 후, B5셀을 클릭하거나 아래 링크를 클릭하여 네이버 검색광고 홈페이지로 이동합니다. 이후 [네이버 아이디로 로그인] 버튼을 클릭하여 사용중인 네이버 아이디로 로그인합니다.
네이버 검색광고 페이지에 로그인합니다. - 로그인 후, [광고 플랫폼] - [도구] - [API 사용 관리] 영역으로 이동합니다.
[광고 플랫폼] - [도구] - [API 사용 관리] 영역으로 이동합니다. - [네이버 검색광고 API 서비스 신청] 버튼을 클릭하면, 아래 그림과 같이 CUSTOMER_ID, 엑세스라이선스, 비밀키가 발급됩니다. 각 값을 복사하여 엑셀 서식으로 붙여넣습니다.
API키와 비밀키를 발급받은 후, 엑셀 서식에 값을 붙여넣습니다. 오빠두Tip : 복사/붙여넣기 시, 복사한 값의 양 끝으로 보이지 않는 공백문자가 있으면 오류가 발생할 수 있습니다. 공백문자 없이, 깔끔하게 값만 붙여넣는 것을 주의하세요!
네이버 데이터랩 API키 신청하기
- [API 정보] 시트에서 B6셀을 클릭하거나 아래 링크를 클릭하여 네이버 데이터랩 개발자 센터로 이동한 후, 사용중인 네이버 아이디로 회원을 완료하면 아래 그림과 같이 데이터랩 개발자센터 페이지가 표시됩니다.
네이버 개발자 센터 페이지로 이동합니다. - 화면 상단의 [Application] - [Application 등록]으로 이동합니다.
[Application] - [Application 등록] 으로 이동합니다. - 다음과 같이 애플리케이션 이용 신청서를 작성합니다. 이용 신청서를 모두 작성하면 아래 그림과 같이 오른쪽에 체크아이콘이 표시됩니다.
① 애플리케이션이름 : 엑셀키워드분석 (또는 본인이 알기 쉬운 이름으로 자유롭게 작성)
② 사용 API : 데이터랩 (검색어트렌드), 데이터랩 (쇼핑인사이트)
③ 서비스 환경 : WEB 설정 → http://oppadu.com
(http:// 로 반드시 시작하고, www는 빼고 입력하는 것을 주의합니다.)
그림과 같이 API 신청서를 작성합니다. - [등록하기] 버튼을 클릭한 후, 발급된 Client ID와 Client Secret 값을 (4), (5) 항목에 붙여넣기하면 키워드 분석 대시보드를 사용할 준비가 모두 끝납니다.
엑셀 키워드 분석 대시보드 사용법
- 등록한 API키로 대시보드 활성화 : API키를 모두 등록한 후, [데이터] 탭 - [쿼리 및 연결] 버튼을 클릭하면 우측에 '쿼리 및 연결' 패널이 표시됩니다.
[데이터] - [쿼리 및 연결]을 클릭하여 쿼리 및 연결 패널을 표시합니다. - 쿼리 및 연결 패널에서 느낌표로 표시되는 '검색광고 키워드' 쿼리를 우클릭한 후, [편집] 을 클릭해서 파워쿼리 편집기를 실행합니다.
검색광고 키워드 쿼리를 우클릭 - [편집]을 클릭해서 파워쿼리 편집기를 실행합니다. - 편집기 상단에 표시되는 [자격증명 편집] 또는 [개인정보 보호]에 대한 버튼을 클릭한 후, 익명으로 엑세스 권한을 설정합니다.
[자격 증명 편집] 버튼을 클릭한 후, 익명으로 권한을 설정합니다. - 쿼리의 느낌표가 모두 사라질 때까지, 나머지 쿼리에도 동일한 방법으로 권한을 모두 설정합니다.
동일한 방법으로 나머지 쿼리의 권한을 모두 설정합니다. - [파일] 탭 - [닫기 및 로드]를 클릭해서 파워쿼리 편집기를 종료합니다.
[파일] - [닫기 및 로드]로 파워쿼리 편집기를 종료합니다. - 대시보드 사용하기 : [키워드 검색] 시트로 이동한 후, 검색할 키워드와 시작/종료일을 입력합니다.
시작일, 종료일, 검색할 키워드를 입력합니다. 오빠두Tip : 시작일은 쇼핑인사이트에서 제공하는 2017년 8월 1일 이후 날짜로 입력합니다. 그 이전 날짜로 시작일을 입력하면 오류가 발생하니 주의해주세요! - 값을 모두 입력한 후, [데이터] 탭 - [모두 새로고침]을 클릭하거나 단축키 Ctrl + Alt + F5 를 동시에 눌러 쿼리를 새로고침하면, 네이버/네이버쇼핑/쿠팡/구글의 키워드 데이터 취합을 시작합니다.
[데이터] - [모두 새로고침]을 클릭하거나 Ctrl + Alt + F5 로 쿼리를 새로고침합니다. - 이제 버튼을 클릭해서 자유롭게 데이터를 분석해보세요! Ctrl 키를 누른상태로 버튼을 클릭해서 여러 항목을 편리하게 비교/분석할 수 있습니다.
슬라이서를 클릭해서 편리하게 데이터를 비교/분석할 수 있습니다. - 네이버 쇼핑 분석 시트 : 네이버 쇼핑 분석 시트에서는 각 키워드를 검색했을 시, 네이버 쇼핑 상단에 표시되는 제품 목록을 확인할 수 있습니다.
- M365 버전에서는 IMAGE 함수를 사용해서 URL의 이미지를 엑셀에 바로 출력할 수 있습니다. J3셀을 선택한 후, 아래와 같이 IMAGE 함수를 입력하면 URL의 이미지가 한 번에 불러와집니다.
= IMAGE (URL주소)
Image 함수를 사용하면 URL의 이미지가 셀에 바로 출력됩니다. 오빠두Tip : 엑셀 이전 버전 사용자는 아래에 정리해드린 xIMAGE 함수 사용 방법을 참고하세요! - 검색광고 분석 시트 : 검색광고 분석 시트에서는 특정 단어를 포함하거나 검색량이 특정 숫자보다 많은 키워드를 편리하게 필터링해서 분석할 수 있습니다. 단, FILTER 함수는 엑셀 2021 이후 버전에서만 사용 가능한 점을 참고해주세요!😉
특정 단어, 검색량 기준으로 실시간 필터를 적용할 수 있습니다. 오빠두Tip : FILTER 함수 사용법은 아래 5분 기초 영상 강의에서 자세히 정리했습니다!👇
(엑셀 이전버전) xImage 함수 사용법
- 엑셀에서 단축키 Alt + F11 키를 동시에 누르거나 [개발도구] - [Visual Basic] 버튼을 클릭하여 매크로 편집기를 실행합니다.
Alt + F11 을 동시에 누르거나 Visual Basic 버튼을 클릭해서 매크로 편집기를 실행합니다. 오빠두Tip : 만약 개발도구가 보이지 않을 경우, 리본메뉴를 우클릭 - '리본메뉴 사용자 지정'에서 개발도구를 활성화합니다. - [삽입] 탭 - [모듈]을 클릭해서 새 모듈을 추가합니다.
[삽입] - [모듈]을 클릭해서 새로운 모듈을 추가합니다. - 아래 링크를 클릭해서 xImage 함수 페이지로 이동한 후, 페이지 중간에 있는 "xImage 명령문 전체 코드"를 드래그해서 복사합니다.
xIMAGE 함수 코드를 복사합니다. - 복사한 코드를 비어있는 모듈에 붙여넣습니다.
새 모듈에 복사한 코드를 붙여넣기합니다. - 매크로 편집기를 종료한 후, 시트에서 다음과 같이 xIMAGE 함수를 입력 후 실행하면 URL 주소의 이미지가 시트에 출력됩니다.
=xIMAGE(URL주소)
매크로 편집기를 종료하면, 앞으로 시트에서 xIMAGE 함수를 사용할 수 있습니다. 오빠두Tip : xIMAGE 함수는 URL의 이미지를 직접 받아옵니다. 따라서 함수 여러 개를 동시에 입력하면 처리속도가 느려질 수 있으니 주의해서 사용하세요! - xIMAGE 함수는 매크로 함수이므로, 완성된 파일은 '매크로 포함 통합문서'로 저장해야 합니다. [파일] 탭 - [다른 이름으로 저장]으로 이동한 후, 파일 형식을 '매크로 사용 통합문서'로 저장합니다.
매크로 사용 통합문서로 파일을 저장합니다.
대시보드에 사용된 파워쿼리 마스터 함수
각 함수를 만드는 방법과 대시보드를 제작하는 전체 과정은 위캔두 멤버십 라이브 영상에서 자세히 다루었으니 아래 링크를 확인해주세요!👇
① 함수 제작 과정 (위캔두 프리미엄)
② 대시보드 제작 과정 (위캔두 플러스)
대시보드에 사용된 키워드 분석 관련 파워쿼리 마스터 함수는 아래 링크에서 확인할 수 있습니다. (함수 변경이 있을 시, 링크를 통해 제공할 예정이오니 변경사항은 아래 링크를 참고하세요!😉)
- [데이터] 탭 - [쿼리 옵션]으로 이동합니다.
엑셀 파일 다운로드 받아서 순차적으로 입력해보았는데, 네이버 쇼핑, 네이버 쇼핑 리스트에서 쿼리 오류가 발생하였습니다ㅜㅜ
그래서 댓글 확인해보고 댓글에 새로 올려주신 엑셀 파일 다시 다운받고, 혹시 몰라 데이터 원본 설정에서 권한 모두 지우기까지 해봤는데도 쿼리 자격 증명 하나씩 설정하는 단계에서
Expression.Error: Web.BrowserContents 가져오기와 일치하는 내보내기가 없습니다. 모듈 참조를 누락했는지 확인하세요.
위와 같은 오류가 발생합니다..ㅜㅜ
해당 오류는 사용하고 계신 엑셀 파워쿼리의 버전으로 인해 발생합니다.
Web.BrowserContent 함수는 2020년 8월 업데이트 된 함수이므로, 2016 버전을 사용하고 계실 경우 엑셀을 최신 버전으로 업데이트 후 다시 실행하면 잘 동작할겁니다. :) 한번 확인해보시겠어요?
감사합니다.
다만, 키워드를 입력해서 새로고침 조회를 돌려보았을 때, 네이버 쇼핑 분석, 검색광고분석 시트는 정상적으로 나오는데 키워드 검색 대시보드 화면의 정보값이 변동이 없네요..
쿼리 및 연결을 눌러보니 데이터렙 성별/기기별/연령별/키워드요약에 오류가 있다고 뜨고 누르고 들어가면 첨부한 두번째 이미지와 같이 보여집니다..ㅜ
파워쿼리 함수 목록 중 NaverDataLab_Gender 함수의 키워드로 '우버택시'를 입력 후 실행해서 함수의 결과값이 잘 반환되는지 확인해보시길 바랍니다. :)
감사합니다.
저도 새로 다운 받았는데 같은 증상입니다.
너무나 잘 사용하고 있었습니다.
ㅠㅠ
지난 업데이트 이후 발생하던 오류를 모두 수정하여 1.4 버전으로 파일을 새롭게 업로드했습니다.
1. (오류 수정) count, offset 등 인수 범위를 벗어났습니다.
2. 비어있음 오류 수정
아래 링크에서 완성파일을 다운로드 받아 다시 실행해보시겠어요? :)
실시간 키워드 분석, 엑셀로 자동화하세요: 초보자도 가능한 무료 템플릿! - 오빠두엑셀 (oppadu.com)
감사합니다.
빠르게 응대해 주셔서 감사합니다.
오늘 다시 다운 받아서 사용하려고 하였는데
이런 현상이 발생합니다.
감사합니다~
남겨주신 오류는 사용하고 계신 엑셀 파워쿼리의 버전으로 인해 발생합니다.
Web.BrowserContent 함수는 2020년 8월 업데이트 된 함수인데요.
따라서 엑셀 2016 버전을 사용하고 계실 경우 엑셀을 최신 버전으로 업데이트 후 다시 실행하면 잘 동작할겁니다. 🙂
한번 확인해보시겠어요?
감사합니다.
업그레이드 후 사용해볼께요^^
정말 감사합니다~
오피스 2021 새로 설치 후 API 등 새로 입력 후 사용하려고 하였는데
아래 사진의 현상이 발생되네요.
ㅠㅠ
Web.BrowserContents 함수는 2020년에 새롭게 추가되었습니다.
따라서 엑셀을 처음 설치한 환경에서는 해당 함수가 누락될 수 있습니다.
[파일] - [계정]에서 엑셀을 최신 버전으로 업데이트해서 다시 실행해보시겠어요? :)
감사합니다.
ㅠㅠ
- 기존에 사용하던 파일에서 클라이언트 id, API키 등을 복사합니다.
- 예제파일을 새로 다운로드 받은 후, 기존에 사용하던 클라이언트 ID 등을 입력합니다.
- 데이터 - 쿼리 및 연결에서 커스텀함수 목록 중 NaverShoppingList 함수를 우클릭 - 편집으로 이동합니다.
- 파워쿼리 편집기가 실행되면 키워드에 원하는 키워드를 입력한 후 호출 버튼을 클릭합니다. (나머지 다른 함수도 동일한 과정으로 테스트)
위 과정으로 실행하면 함수 개별 단계에서 오류가 발생하는지 확인할 수 있습니다. :)감사합니다.
바쁘신 와중에도 친절한 답변 감사합니다.
설명하신 절차로 설치하는 과정을 캡쳐하였습니다.
동일한 상황으로 보여지는데, 네이버 쇼핑 부분의 연결이 안되고 있는 상태이며,
API 쿼리 연결시 여전히 네이버쇼핑 부분의 연결이 안되고 있습니다.
현재 엑셀 상태는 최신버전으로 업데이트 되어 있는 상태에서 API 쿼리시 "호환성 경고" 창이 뜨는 상황이며(1번사진),
키워드 입력하였을 때
바쁘실텐데 감사합니다.
먼저 버전 차이로 인해 오류가 발생하는 부분을 확인해주셔서 감사드립니다.
Web.BrowserContents 함수로 쿼리를 구현하려고 했으나, 지속적으로 오류가 발생하는 것으로 보여서
이전 버전의 함수로 롤백하여 파일을 2.0 버전으로 새로 업데이트 하였습니다.
새로운 버전의 파일에서 한번 사용해보시겠어요? 감사합니다.
항상 좋은 정보와 좋은 컨텐츠 너무감사합니다.^^
질문이있어서 글을 남깁니다.
엑셀 다운받아서 진행하는도중 에러가 발생하여 질문드립니다.
Formula.Firewall: 쿼리 '주요플랫폼키워드' (단계 '제거된 열 수')에서 함께 사용할 수 없는 개인 정보 수준의 데이터 원본에 액세스하는 중입니다. 이 데이터 조합을 다시 만드세요.
이런에러가 발생됩니다.
어떻게 해야할지 몰라서 질문드립니다.
파워쿼리 편집기에서 [홈] - [데이터 원본 설정]으로 이동한 후, 현재 통합문서와 전역문서의 데이터 권한을 모두 지웁니다.
이후 데이터 연결 설정을 자격증명: 익명, 개인정보 수준: 없음 으로 변경 후 다시 실행해보세요.
만약 설정을 변경해도 오류가 계속 발생한다면, 쿼리 편집기에서 [파일] - [옵션 및 설정] - [쿼리 옵션] 으로 이동한 후, 현재 통합문서의 개인정보 설정을 '모두 무시하고 잠재적으로 성능 향상'을 체크해서 실행해보시길 바랍니다.
감사합니다.
먼저 항상 감사드립니다.
사진이 두장만 올라가기에 사진 올립니다.
일단 쿼리 연결시 강사님께서 설명하신 내용으로 진행하였습니다.
"2번 첨부사진"의 부분 쿼리연결이 안되고 있습니다.
감사합니다~
- 검색광고분석 탭의 키워드상세분석 내용이 나오지 않습니다.
이건 어떻게 해결해야 할까요?키워드 상세-키워드요약 부분에서 오류가 나는데요,
오류를 보니 '게시글 발행량' 에서 Error 표기가 떠있고,
404 오류가 떠있네요.
NaverBlogCount 함수에서 문제가 된 거 같은데
깃헙에 있는 최근 함수로 수정해도 에러가 그대로 발생합니다.
확인 부탁드리겠습니다
오류 전문
DataSource.Error: Web.Contents가 'https://s.search.naver.com/p/blog/search.naver?where=blg&api_type=1&query=%EB%A7%88%EB%9D%BC%ED%83%95&dup_remove=1&nso=so:r,p:1y,a:all&nx_search_query=%EB%A7%88%EB%9D%BC%ED%83%95'(404)에서 콘텐츠를 가져오지 못했습니다. Not Found
세부 정보:
DataSourceKind=Web
DataSourcePath=https://s.search.naver.com/p/blog/search.naver
Url=https://s.search.naver.com/p/blog/search.naver?where=blg&api_type=1&query=%EB%A7%88%EB%9D%BC%ED%83%95&dup_remove=1&nso=so:r,p:1y,a:all&nx_search_query=%EB%A7%88%EB%9D%BC%ED%83%95
쿼리 옵션에서 개인정보 설정을 변경해보세요.
[데이터] 탭 - [쿼리 옵션] - '전역' 그룹의 [개인 정보] 에서 '항상 개인 정보 수준 설정을 무시'를 체크 후 다시 실행해보시길 바랍니다.
그림과 함께한 설명은 아래 링크를 참고해주세요.
엑셀 키워드 분석 대시보드 템플릿 - 오빠두엑셀
감사합니다.