データ説明
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';
