ぼくは明日、昨日のじぶんに頼りたい

明日のためのメモです。

Chromeのアクセス履歴を可視化してみる

SQL PostgreSQL Docker

１年間に自宅PCやスマートフォンで検索、閲覧した履歴を分析できたら自分が今年興味あったことなど振り返れるかなと思いました。可視化というレベルのことはできてません。
（ちなみに仕事は完全に会社の端末でアカウント等も別のものを利用しているので対象外となります）

Googleから取得した自分のデータ

「Google データ エクスポート」で以下をエクスポート

  • 「Chrome」を選択

    • Bookmarks(HTML)
    • BrowserHistory(JSON)
      • 見た感じ１年分（2020/12/30があったので）はエクスポートされました。（今回は１年分あればOKなので、深堀してません）
    • ReadingList(HTML)

  • 「マイ アクティビティ」

    • 検索(HTML)
      • JSONを指定して２回ほどエクスポートしてみたのですが、なぜかHTMLで出力されてしまった

利用するデータとしては、結果的に上記で取得した「BrowserHistory」(JSON) と「%LOCALAPPDATA%\Google\Chrome\User Data\Default\Bookmarks」(JSON) を用いることになりました。

そして私の技術力のおかげで、単純なSQLで集計することになりました。

集計準備

各JSONデータをPostgreSQL(14.1)に突っ込んで単純なSQLで集計します。

以下の集計データを保存するテーブルを適当に用意しました。

-- BrowserHistory
CREATE TABLE browser_histories (
  browser_history_id SERIAL NOT NULL,
  favicon_url text,
  page_transition text,
  title text,
  url text NOT NULL,
  host text NOT NULL,
  path text,
  query text,
  hash text,
  client_id text,
  time_usec bigint NOT NULL,
  access_datetime timestamp NOT NULL,
  PRIMARY KEY (browser_history_id)
);

以下を参考にjqで整えて登録することにしました。
dev.to

$ docker exec -it postgres14 sh

# jqインストール
$ apt -y update
$ apt -y install jq

# jsonデータを取り込みやすいように変換
$ cat /var/tmp/BrowserHistory.json | jq -cr '."Browser History"[]' | sed 's/\\[tn]//g' | sed 's/\\"/\\\\"/g' > /var/tmp/BrowserHistory_output.json

# 以下はじかれた
# "title":""#...
# # Color red='\0...
# 単純にシングルクォートを置換しようとするとほかのシングルクォートのところがダメになり、
# めんどくさくなり、タイトルにpathみたいなの書かれていたgithubのアクセスのタイトルを手動で削除した
# https://github.com/a01361/a01361.github.io

# データ仮置きテーブル作成
$ psql -h localhost -p 5432 postgres -U postgres -c "CREATE TABLE tmp_browser_history_json (data jsonb);"

# インポート
$ cat /var/tmp/BrowserHistory_output.json | psql -h localhost -p 5432 postgres -U postgres -c "COPY tmp_browser_history_json (data) FROM STDIN;"

-- 仮置きテーブルから登録:browser_histories
INSERT INTO browser_histories
  (favicon_url, page_transition, title, url, host, path, query, hash, client_id, time_usec, access_datetime) 
SELECT
  data->>'favicon_url' AS favicon_url,
  data->>'page_transition' AS page_transition,
  data->>'title' AS title,
  data->>'url' AS url,
  -- host
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '/.*','') AS host,
  -- with path
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '\?.*', '') AS path,
  -- with query
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '\#.*', '') AS query,
  -- with hash
  REGEXP_REPLACE(data->>'url', 'https?://','') AS hash,
  data->>'client_id' AS client_id,
  CAST(data->>'time_usec' AS bigint) AS time_usec,
  TO_TIMESTAMP(CAST(data->>'time_usec' AS bigint) / 1000000) AS access_datetime
FROM tmp_browser_history_json;

sedをぱっと使えるようになりたいです。来年の課題とします。
集計して気づきましたが、「with path」のとこでhash(#)が含まれるようになってしまっていました。来年の課題とします。

集計結果

対象期間は、2021/1/1 00:00:00から2021/12/29 23:59:59までとしました。

年間にアクセスしたChrome経由でWebアクセスした件数：77,323件
年間にアクセスしたユニークなホスト数：4,605件
年間にアクセスしたユニークなパス数（クエリーパラメータを除いたもの）：22,602件
年間にアクセスしたユニークなパス数（クエリーパラメータを含めたもの）：30,681件

年間にアクセスしたホストのトップ15

# ホスト 件数
1 www.google.com 8714
2 mail.google.com 4228
3 github.com 2859
4 connpass.com 2667
5 ap-northeast-1.console.aws.amazon.com 2654
6 chrome: 2647
7 qiita.com 2632
8 news.yahoo.co.jp 2615
9 www.amazon.co.jp 2318
10 www.udemy.com 1817
11 console.aws.amazon.com 1727
12 www.youtube.com 1328
13 us-west-2.console.aws.amazon.com 1040
14 trello.com 1038
15 blog.hatena.ne.jp 1022

Google検索のアクセスが全体の11%と予想通りで、次点のGmailが予想より見てしまっているという感想です。
あとは3%台でGitHub、AWSコンソールやQiitaとエンジニアっぽいサイトにアクセスしてるみたいです。
connpassは今年も緊急事態宣言下があり、いろいろ参加してみたいと思いやってみたので色々探した結果上位に位置したのだと思います。ただ、スマホアプリでアクセスしているTwitterやYahooはここに現れてきていないことに注意したいと思います。
「Speaker Deck」でクエリーパラメータ含まない時に同一パスでのアクセスが多くて、よく参照しているものがあったのかと思いましたが、「?slide=3」とスライドごとにクエリーパラメータで表示されていたオチでした。

Gmailの閲覧が多かった対策でもないですが、不要なメールマガジンの配信は停止しました。（毎年恒例ではあります）

ブックマークの整理

ブックマークを整理するために以下のテーブルを適当に用意しました。
インポートするデータはGoogleからエクスポートしたものではなく、「%LOCALAPPDATA%\Google\Chrome\User Data\Default\Bookmarks」のJSONデータです。

-- Bookmarks
CREATE TABLE bookmarks (
  bookmark_id SERIAL NOT NULL,
  date_added_msec bigint NOT NULL,
  date_added_datetime timestamp NOT NULL,
  guid text NOT NULL,
  id text NOT NULL,
  name text,
  type text,
  url text,
  host text,
  path text,
  query text,
  hash text,
  PRIMARY KEY (bookmark_id)
);

先ほどと同じくjqで整えて登録しました。

$ docker exec -it postgres14 sh

# jsonデータを取り込みやすいように変換
$ cat /var/tmp/Bookmarks | jq -cr "recurse | select(.children?) | .children[]" | sed 's/\\[tn]//g' | sed 's/\\"/\\\\"/g' > /var/tmp/Bookmarks.json

# データ仮置きテーブル作成
$ psql -h localhost -p 5432 postgres -U postgres -c "CREATE TABLE tmp_bookmark_json (data jsonb);"

# インポート
$ cat /var/tmp/Bookmarks.json | psql -h localhost -p 5432 postgres -U postgres -c "COPY tmp_bookmark_json (data) FROM STDIN;"

jqで上手くいかないところがあり以下に助けてもらいました。
namonakimichi.hatenablog.com
jqをぱっと使えるようになりたいです。来年の課題とします。

-- 仮置きテーブルから登録:bookmarks
INSERT INTO bookmarks
  (guid, id, name, type, url, host, path, query, hash, date_added_msec, date_added_datetime) 
SELECT
  data->>'guid' AS guid,
  data->>'id' AS id,
  data->>'name' AS name,
  data->>'type' AS type,
  data->>'url' AS url,
  -- host
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '/.*','') AS host,
  -- with path
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '\?.*', '') AS path,
  -- with query
  REGEXP_REPLACE(REGEXP_REPLACE(data->>'url', 'https?://',''), '\#.*', '') AS query,
  -- with hash
  REGEXP_REPLACE(data->>'url', 'https?://','') AS hash,
  CAST(data->>'date_added' AS bigint) AS date_added_msec,
  -- https://stackoverflow.com/questions/51343828/how-to-parse-chrome-bookmarks-date-added-value-to-a-date
  TO_TIMESTAMP((-11644473600000 + CAST(data->>'date_added' AS bigint) / 1000) / 1000) AS date_added_datetime
FROM tmp_bookmark_json;

トラップではないですが起算日(date_added)のところは以下に助けてもらいました。
stackoverflow.com

雑にアクセス履歴とのぶつけて件数を確認します。ブックマークからのアクセスという意味ではなく、ブックマークに登録されているURLにアクセスしているかを調べています。

-- ブックマーク登録しているURLの利用状況
SELECT
  bookmarks.bookmark_id,
  bookmarks.date_added_datetime,
  bookmarks.name,
  groupby_path.path,
  groupby_path.count AS p_count,
  groupby_path.last_access_datetime AS p_last_access_datetime,
  groupby_query.query,
  groupby_query.count AS q_count,
  groupby_query.last_access_datetime AS p_last_access_datetime,
  groupby_hash.hash,
  groupby_hash.count AS h_count,
  groupby_hash.last_access_datetime AS p_last_access_datetime
FROM bookmarks
LEFT JOIN LATERAL (
  SELECT
    browser_histories.path,
    COUNT(*) AS count,
    MAX(browser_histories.access_datetime) AS last_access_datetime
  FROM browser_histories
  WHERE browser_histories.path = bookmarks.path
  GROUP BY browser_histories.path
) AS groupby_path
  ON TRUE
LEFT JOIN LATERAL (
  SELECT
    browser_histories.query,
    COUNT(*) AS count,
    MAX(browser_histories.access_datetime) AS last_access_datetime
  FROM browser_histories
  WHERE browser_histories.query = bookmarks.query
  GROUP BY browser_histories.query
) AS groupby_query
  ON TRUE
LEFT JOIN LATERAL (
  SELECT
    browser_histories.hash,
    COUNT(*) AS count,
    MAX(browser_histories.access_datetime) AS last_access_datetime
  FROM browser_histories
  WHERE browser_histories.hash = bookmarks.hash
  GROUP BY browser_histories.hash
) AS groupby_hash
  ON TRUE
ORDER BY
groupby_path.count DESC NULLS LAST, 
groupby_query.count DESC NULLS LAST, 
groupby_hash.count DESC NULLS LAST

登録されていたブックマークの件数：1,739件
今年1年でブックマークに登録しているURLでアクセスしていない件数：1,568件

なんとなくわかってはいましたが９割いらない可能性があるブックマークでした。
必要なら検索して見つけられる可能性があるので、機械的に削除しまってもいいのですが。
ただ、ブックマークした日で並べてその当時どのようなことに興味を持っていたのかは確認してから削除したいなと思いました。おそらく一生消せないやつかもしれません。
何も片付いてない。