データ説明

Standardパッケージを作成してサイトデータを申請すると、サイト訪問ログ、サイト流入検索キーワード、訪問者検索キーワードの要約データが提供され、ショッピングデータを申請するとショッピングインサイトデータが提供されます。

データはオブジェクトストレージバケットにファイルで提供され、SupersetサーバPostgreSQL DBの'da_han'というDBのテーブルに保存されて、Supersetダッシュボードで確認できるように提供されます。

DBにはファイルに該当するテーブルだけでなく、ダッシュボード性能のために追加したテーブルも存在し、ダッシュボード作成を便利にするためにテーブルの主なカラム名は日本語になっています(提供されるデータスペックは今後変更される場合があります)。

サイト訪問ログ:訪問者の行動ログ

NAVER Analyticsで収集されたサイトの訪問ログです。

毎日、前日のデータが新しく収集されると、オブジェクトストレージバケットの'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:NAVER Analyticsで収集するサイト訪問ログの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),        -- 追加したカラム
     OS varchar(200),            -- 追加したカラム
     ブラウザ varchar(200),            -- 追加したカラム
     追加情報 VARCHAR,                -- vpropで収集された情報
     会員ID VARCHAR(100),             -- vpropで収集された情報   
     商品ID VARCHAR(100),             -- vpropで収集された情報 
     商品カテゴリID VARCHAR(100),      -- vpropで収集された情報 
     追加情報_その他 VARCHAR             -- vpropで収集された情報
    )  PARTITION BY RANGE (訪問日) TABLESPACE ts_da;
    CREATE TABLE da_site_log_202011 PARTITION OF da_site_log FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
    
    update da_site_log setリファラサイト =  split_part(リファラ, '/', 3)
                         OS = 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、訪問日、時間;
    

サイト流入検索キーワード:流入検索キーワードのNAVER検索統計

NAVER Analyticsで収集されたサイトの訪問ログに含まれた流入検索キーワードのうち、NAVERで検索された検索キーワードに対するNAVER統合検索のユーザー特性別の数値データです。

つまり、NAVERで「NAVERクラウド」、「NAVERクラウドプラットフォーム」などを検索してサイトに訪問したユーザーがいる場合、「NAVERクラウド」、「NAVERクラウドプラットフォーム」などの検索キーワードが当日一日NAVERで検索された全体数値を、性別/年齢層などの単位で集計したデータです。

オブジェクトストレージバケットの'site_kwd'ディレクトリの下位の日付別、サイトID別ディレクトリにファイルが保存され、da_han DBのda_site_kwdというテーブルにデータが保存されます。

  • da_site_kwd:サイト訪問ログの流入検索キーワードに対するNAVER統合検索のデータ保存テーブル

    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_202011 PARTITION OF da_site_kwd FOR VALUES FROM ('2020-11-01') TO ('2020-12-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 検索日 = $検索日;
    

訪問者の検索キーワード要約:訪問者の検索活動統計

流入検索キーワードでサイトを訪問したユーザーが、流入検索キーワードを入力する前/後の一定時間の間、NAVERで検索した検索キーワードに対する検索数値データです。

つまり、NAVERで「NAVERクラウド」を検索してサイトに訪問したユーザーがいる場合、そのユーザーが「NAVERクラウド」という検索キーワードを入力する前/後の一定時間の間、「クラウド」、「NAVERクラウドプラットフォーム」、「AWS」などの検索キーワードを検索した場合、この検索キーワードに対する訪問者の検索数値データです。

オブジェクトストレージバケットの'visitor_kwd_sum'ディレクトリの下位の日付別、サイトID別ディレクトリにファイルが保存され、da_han DBの以下の二つのテーブルにデータが保存されます。

  • da_visitor_kwd_sum:サイトを訪問したユーザーが同じ日にNAVERで検索したデータ保存テーブル

    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');
    
  • 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;
    

ショッピングインサイト

NAVER Shoppingで発生した検索キーワードに対する商品カテゴリの性別/年齢層別のクリックデータです。

毎日、前日のデータが新しく収集されると、オブジェクトストレージバケットの'datalab_shopping'ディレクトリに日付別のショッピングインサイトのデータファイルが保存され、'datalab_shopping_cat'ディレクトリには申請したカテゴリに対するカテゴリコードのデータファイルが保存されます。

  • da_datalab_shopping_cat:申請したショッピングカテゴリに対するコードデータを保存するテーブル

    CREATE TABLE da_datalab_shopping_cat_id(
      カテゴリid varchar(10),
      上位カテゴリid varchar(10),
      カテゴリ名 varchar(300),
      カテゴリレベル int,
      最終カテゴリの有無 char(1),
      表示順序 int,
      サービス使用の有無 char(1),
      constraint pk_da_datalab_shopping_cat_id primary key(カテゴリid)
    ) TABLESPACE ts_da;
    
  • da_datalab_shopping:申請したショッピングカテゴリに属する商品に対する検索キーワードの性別/年齢層別のクリックデータを保存するテーブル

    CREATE TABLE da_datalab_shopping(
      クリック日 date NOT NULL,
      pc_モバイル varchar(10) NOT NULL,
      検索キーワード varchar(300) NOT NULL,
      カテゴリid varchar(10),
      性別 varchar(10),
      年齢層コード varchar(10),
      クリック数 int,
      年齢層 varchar(10), -- 追加したカラム
      カテゴリ名 varchar(300), -- 追加したカラム
      カテゴリレベル int, -- 追加したカラム
      分類1カテゴリid varchar(10), -- 追加したカラム
      分類1カテゴリ名 varchar(300), -- 追加したカラム
      分類2カテゴリid varchar(10), -- 追加したカラム
      分類2カテゴリ名 varchar(300), -- 追加したカラム
      分類3カテゴリid varchar(10), -- 追加したカラム
      分類3カテゴリ名 varchar(300) -- 追加したカラム
    ) PARTITION BY RANGE (クリック日) TABLESPACE ts_da;
    CREATE TABLE da_datalab_shopping_202011 PARTITION OF da_datalab_shopping FOR VALUES FROM ('2020-11-01') TO ('2020-12-01');
    

データ削除

ブロックストレージ容量確認

DBデータは提供されるブロックストレージに保存されますが、ブロックストレージは2TBが提供されるため、不要なログデータは削除して最新データが保存されるようにしてください。

① サーバにアクセスしてdf -hコマンドを入力し、/dasにマウントされたブロックストレージの使用量を照会できます。

不要なデータ削除

Rawデータは月単位のパーティションテーブルに保存されるため、パーティション単位に月単位データを削除できます。 他のテーブルは日付を条件として古いデータを削除できます。サイトIDのような特定条件でもデータを削除できます。

ただし、大量のデータを削除する場合、DBの性能低下の原因となります。

 -- 月単位のパーティション削除
 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';

に対する検索結果は~件です。 ""

    に対する検索結果がありません。 ""

    処理中...