데이터 설명

Standard 패키지를 생성하고 사이트 데이터를 신청하면 아래의 3가지 종류의 데이터가 제공됩니다.

  • 사이트 방문 로그
  • 사이트 유입 검색어
  • 방문자 검색어 요약

데이터는 오브젝트 스토리지 버킷에 파일로 제공되며, 서버의 PostgreSQL DB의 'da_han'이라는 DB의 테이블에 데이터로 저장되어 제공됩니다.

DB에는 파일에 해당하는 테이블뿐 아니라, 대시보드 성능을 위해 추가한 테이블들도 존재하며, 대시보드 작성을 편리하게 하기 위해 테이블의 주요 칼럼 이름은 한글로 되어 있습니다(제공되는 데이터 스펙은 추후 변경될 수 있습니다).

사이트 방문 로그 : 방문자의 행동 로그

네이버 애널리틱스를 통해 수집된 사이트의 방문 로그입니다.

매일 데이터가 새로 수집되면 오브젝트 스토리지 버킷의 'site_log' 디렉터리 하위의 날짜별, 사이트 ID별 디렉터리에 파일이 저장되며, PostgreSQL DB의 da_han이라는 DB에 만들어진 아래의 테이블에도 데이터가 저장이 됩니다.

  • da_site : 신청한 사이트ID와 사이트이름을 저장하는 테이블

    CREATE TABLE da_site(
     사이트id varchar(100) NOT NULL,
     사이트이름 varchar(300),
     constraint pk_da_site primary key(사이트id)
    ) TABLESPACE ts_da;
    
  • da_site_log : 네이버 애널리틱스에서 수집하는 사이트 방문 로그 raw 데이터 저장 테이블

    CREATE TABLE da_site_log(
     사용자key varchar(100) NOT NULL,
     방문일시 timestamp NOT NULL,
     uri varchar(1000),
     페이지제목 varchar(1000),
     레퍼러 varchar(10000),
     pc_모바일 varchar(10),
     wcs_user_agent varchar(1000),
     단말기종류 varchar(100),
     ua_os varchar(100),
     ua_os_major varchar(100),
     ua_os_name varchar(100),
     ua_name varchar(100),
     ua_major varchar(100),
     ua_minor varchar(100),
     국가 varchar(10),
     지역_시도 varchar(100),
     인코딩 varchar(10),
     언어 varchar(20),
     event_category varchar(100),
     event_action varchar(1000),
     conversion_type varchar(100),
     conversion_value varchar(1000),
     유입검색어 varchar(500),
     방문일자 date NOT NULL,          -- 추가한 칼럼(폴더명)
     사이트id varchar(100) NOT NULL,  -- 추가한 칼럼(폴더명)
     레퍼러사이트 varchar(100),             -- 추가한 칼럼
     운영체제 varchar(200),                 -- 추가한 칼럼
     브라우저 varchar(200)                   -- 추가한 칼럼
    )  PARTITION BY RANGE (방문일자) TABLESPACE ts_da;
    CREATE TABLE da_site_log_201911 PARTITION OF da_site_log FOR VALUES FROM ('2019-11-01') TO ('2019-12-01');
    CREATE TABLE da_site_log_201912 PARTITION OF da_site_log FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');
    
    update da_site_log set 레퍼러사이트 =  split_part(레퍼러, '/', 3)
                         운영체제 = ua_os_name || ' ' || ua_os_major,
                         브라우저 = ua_name || ' ' || ua_major
    where 방문일자 = $방문일자;
    
  • da_site_log_date : 날짜별 방문 로그(방문자수, 페이지뷰) 집계 테이블(대시보드 성능을 위해 추가한 테이블 )

    CREATE TABLE da_site_log_date (
     사이트id varchar(100) NOT NULL,
     방문일자 date NOT NULL,
     방문자수 bigint,
     페이지뷰 bigint,
     요일_번호 integer,
     요일 char(1),
     요일_정렬 varchar(5),
     constraint pk_da_site_log_date primary key(사이트id, 방문일자)
    ) TABLESPACE ts_da;
    
    INSERT INTO da_site_log_date
    SELECT 사이트id, 방문일자, count(distinct 사용자key), count(1),
         EXTRACT(DOW FROM 방문일자),
         case when  EXTRACT(DOW FROM 방문일자) = 0 then '일'
              when  EXTRACT(DOW FROM 방문일자) = 1 then '월'
              when  EXTRACT(DOW FROM 방문일자) = 2 then '화'
              when  EXTRACT(DOW FROM 방문일자) = 3 then '수'
              when  EXTRACT(DOW FROM 방문일자) = 4 then '목'
              when  EXTRACT(DOW FROM 방문일자) = 5 then '금'
              when  EXTRACT(DOW FROM 방문일자) = 6 then '토' end ,
         case when  EXTRACT(DOW FROM 방문일자) = 0 then EXTRACT(DOW FROM 방문일자) || ' ' ||'일'
              when  EXTRACT(DOW FROM 방문일자) = 1 then EXTRACT(DOW FROM 방문일자) || ' ' ||'월'
              when  EXTRACT(DOW FROM 방문일자) = 2 then EXTRACT(DOW FROM 방문일자) || ' ' ||'화'
              when  EXTRACT(DOW FROM 방문일자) = 3 then EXTRACT(DOW FROM 방문일자) || ' ' ||'수'
              when  EXTRACT(DOW FROM 방문일자) = 4 then EXTRACT(DOW FROM 방문일자) || ' ' ||'목'
              when  EXTRACT(DOW FROM 방문일자) = 5 then EXTRACT(DOW FROM 방문일자) || ' ' ||'금'
              when  EXTRACT(DOW FROM 방문일자) = 6 then EXTRACT(DOW FROM 방문일자) || ' ' ||'토' end
    FROM da_site_log
    WHERE 방문일자 = $방문일자
    GROUP BY 사이트id, 방문일자
    
  • da_site_log_hour : 시간대별 방문 로그(방문자수, 페이지뷰) 집계 테이블(대시보드 성능을 위해 추가한 테이블 )

    CREATE TABLE da_site_log_hour (
     사이트id varchar(100) NOT NULL,
     방문일자 date NOT NULL,
     방문자수 bigint,
     페이지뷰 bigint,
     시간 char(2),
     요일_번호 integer,
     요일 char(1),
     요일_정렬 varchar(5),
     constraint pk_da_site_log_hour primary key(사이트id, 방문일자, 시간)
    ) TABLESPACE ts_da;
    
    INSERT INTO da_site_log_hour
    SELECT 사이트id, 방문일자, count(distinct 사용자key), count(1),
         lpad(EXTRACT(HOUR FROM 방문일시)::char(2), 2, '0') as 시간,
         EXTRACT(DOW FROM 방문일자),
         case when  EXTRACT(DOW FROM 방문일자) = 0 then '일'
              when  EXTRACT(DOW FROM 방문일자) = 1 then '월'
              when  EXTRACT(DOW FROM 방문일자) = 2 then '화'
              when  EXTRACT(DOW FROM 방문일자) = 3 then '수'
              when  EXTRACT(DOW FROM 방문일자) = 4 then '목'
              when  EXTRACT(DOW FROM 방문일자) = 5 then '금'
              when  EXTRACT(DOW FROM 방문일자) = 6 then '토' end ,
         case when  EXTRACT(DOW FROM 방문일자) = 0 then EXTRACT(DOW FROM 방문일자) || ' ' ||'일'
              when  EXTRACT(DOW FROM 방문일자) = 1 then EXTRACT(DOW FROM 방문일자) || ' ' ||'월'
              when  EXTRACT(DOW FROM 방문일자) = 2 then EXTRACT(DOW FROM 방문일자) || ' ' ||'화'
              when  EXTRACT(DOW FROM 방문일자) = 3 then EXTRACT(DOW FROM 방문일자) || ' ' ||'수'
              when  EXTRACT(DOW FROM 방문일자) = 4 then EXTRACT(DOW FROM 방문일자) || ' ' ||'목'
              when  EXTRACT(DOW FROM 방문일자) = 5 then EXTRACT(DOW FROM 방문일자) || ' ' ||'금'
              when  EXTRACT(DOW FROM 방문일자) = 6 then EXTRACT(DOW FROM 방문일자) || ' ' ||'토' end
    FROM da_site_log
    WHERE 방문일자 = $방문일자
    GROUP BY 사이트id, 방문일자, 시간;
    

사이트 유입 검색어 : 유입 검색어의 네이버 검색 통계

네이버 애널리틱스를 통해 수집된 사이트의 방문 로그에 포함된 유입 검색어 중, 네이버를 통해 검색된 검색어에 대한 네이버 통합검색의 사용자 특성별 수치 데이터입니다.

즉 네이버에서 '네이버 클라우드', '네이버 클라우드 플랫폼' 등을 검색해서 사이트에 방문한 사용자가 있는 경우, '네이버 클라우드', '네이버 클라우드 플랫폼' 등의 검색어가 당일 하루 동안 네이버에서 검색된 전체 수치를 성별/연령대 등의 단위로 집계한 데이터입니다.

오브젝트 스토리지 버킷의 'site_kwd' 디렉터리 하위의 날짜별, 사이트 ID별 디렉터리에 파일이 저장되며, da_han이라는 DB의 da_site_kwd라는 테이블에 데이터가 저장됩니다.

  • da_site_kwd : 사이트 방문 로그의 유입 검색어에 대한 네이버 통합 검색의 데이터 저장 테이블

    CREATE TABLE da_site_kwd(
     검색어 varchar(500) NOT NULL,
     검색일자 date NOT NULL,
     pc_모바일 varchar(10) NOT NULL,
     성별 varchar(10),
     연령대코드 varchar(10),
     검색수 int,
     사이트id varchar(100) NOT NULL,     -- 추가한 칼럼(폴더명)
     연령대  varchar(10)                 -- 추가한 칼럼
    ) PARTITION BY RANGE (검색일자) TABLESPACE ts_da;
    CREATE TABLE da_site_kwd_201911 PARTITION OF da_site_kwd FOR VALUES FROM ('2019-11-01') TO ('2019-12-01');
    CREATE TABLE da_site_kwd_201912 PARTITION OF da_site_kwd FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');
    
    -- 코드값 매핑
    update da_site_kwd  set 연령대 = '0-12' where 연령대코드 = 1' and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '13-18' where 연령대코드 = 2 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '19-24' where 연령대코드 = 3 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '25-29' where 연령대코드 = 4 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '30-34' where 연령대코드 = 5 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '35-39' where 연령대코드 = 6 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '40-44' where 연령대코드 = 7 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '45-49' where 연령대코드 = 8 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '50-54' where 연령대코드 = 9 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '55-59' where 연령대코드 = 10 and 검색일자 = $검색일자;
    update da_site_kwd  set 연령대 = '60-' where 연령대코드 = 11 and 검색일자 = $검색일자;
    

방문자 검색어 요약 : 방문자의 검색 활동 통계

유입 검색어를 통해 사이트를 방문한 사용자가 유입 검색어를 입력하기 전/후 일정 시간 동안 네이버에서 검색한 검색어에 대한 검색 수치 데이터입니다.

즉 네이버에서 '네이버 클라우드'를 검색해서 사이트에 방문한 사용자가 있는 경우, 그 사용자가 '네이버 클라우드'라는 검색어를 입력하기 전/후 일정 시간동안 검색한 '클라우드', 'NCP', 'AWS' 등의 검색어에 대한 방문자들의 검색어 수치 데이터입니다.

  • da_visitor_kwd_sum : 사이트를 방문한 사용자가 같은 날 네이버에서 검색한 데이터 저장 테이블

    CREATE TABLE da_visitor_kwd_sum(
     검색일자 date NOT NULL,
     검색어 varchar(500),
     검색수 bigint,
     사이트id varchar(100) NOT NULL    -- 추가한 칼럼(폴더명)
    ) PARTITION BY RANGE (검색일자) TABLESPACE ts_da;
    CREATE TABLE da_visitor_kwd_sum_201911 PARTITION OF da_visitor_kwd_sum FOR VALUES FROM ('2019-11-01') TO ('2019-12-01');
    CREATE TABLE da_visitor_kwd_sum_201912 PARTITION OF da_visitor_kwd_sum FOR VALUES FROM ('2019-12-01') TO ('2020-01-01');
    
  • da_visitor_kwd_sum_top : da_visitor_kwd_sum 데이터 중 일별로 검색 수가 높은 상위 1000개의 데이터만 추출해 저장하는 테이블(대시보드 성능을 위해 추가한 테이블)

    CREATE TABLE da_visitor_kwd_sum_top(
     검색일자 date NOT NULL,
     검색어 varchar(300) NOT NULL,
     검색수 bigint,
     사이트id varchar(100) NOT NULL,
     constraint pk_da_visitor_kwd_sum_top primary key(사이트id, 검색일자, 검색어)
    ) TABLESPACE ts_da;
    
    -- 날짜/사이트별 top 1000
    INSERT INTO da_visitor_kwd_sum_top
    SELECT 검색일자, 검색어, 검색수 , 사이트id
    FROM da_visitor_kwd_sum
    WHERE 검색어 is not null and 검색일자 = $검색일자 and 사이트id = $사이트id
    ORDER BY 검색수 desc limit 1000;
    

데이터 삭제

블록 스토리지 용량 확인

DB 데이터들은 제공되는 블록 스토리지에 저장이 되는데, 블록 스토리지는 2TB가 제공되므로 불필요한 로그 데이터는 삭제하여 최신 데이터가 저장될 수 있게 해야 합니다.

① 서버에 접속하여 df -h 명령어를 입력하여 /das로 마운트된 블록 스토리지 사용량을 조회할 수 있습니다.

불필요한 데이터 삭제

Raw 데이터들은 월 단위 파티션 테이블에 저장되므로, 파티션 단위로 월 단위 데이터를 삭제할 수 있습니다. 다른 테이블들은 날짜를 조건으로 오래된 데이터를 삭제할 수 있으며, 사이트 ID와 같은 특정 조건으로도 데이터를 삭제할 수 있습니다.

 -- 월 단위 파티션 삭제
 DROP TABLE da_site_log_201912;
 DROP TABLE da_site_kwd_201912;
 DROP TABLE da_visitor_kwd_sum_201912;

 -- 특정 날짜 이전 데이터 삭제
 DELETE FROM da_site_log_date WHERE 방문일자 < '2020-01-01';
 DELETE FROM da_site_log_hour WHERE 방문일자 < '2020-01-01';
 DELETE FROM da_visitor_kwd_sum_top WHERE 검색일자 < '2020-01-01';

 -- 특정 사이트 데이터 삭제
 DELETE FROM da_site_log WHERE 사이트id = '15cacccfe';

""에 대한 건이 검색되었습니다.

    ""에 대한 검색 결과가 없습니다.

    처리중...