ディレクターがSQL書いてキーワード分析に取組んでみた

#目次

• やってることの説明（プロジェクト概要）
• 最終的なアウトプット
• 工夫した点
  • 頻出単語
  • トレンドワード
  • wikipedia
• 使い道・フィードバック
• 今後の展開

#プロジェクト概要説明
　GMOアドマーケティングでは広告配信を行う上で、WEBサイトのコンテンツを解析し、コンテンツにマッチさせた広告を配信しています。
そこで解析した内容を活用し、さらに「広告訴求したいページ（広告主サイト）を閲覧しているユーザーがどんな内容に興味を持っているのか？」を分析することで、ユーザーの興味を事後的に類推する試みを行いました。

#最終的なアウトプット
上記の概要図内の、以下がアウトプットとなります。

1. 特徴キーワード
2. インサイトグラフ

※「インサイトグラフ」のイメージ図

#分析手順

1. WEBサイトコンテンツの収集・クローリング

2. 収集したコンテンツ(文章)をキーワード(単語)に分割し、文章毎におけるキーワードの特徴量(重要度)をポイント付け

3. キーワード毎の以下を分析し、特徴が出るように加工

   • 特徴量(重要度)ポイント
   • 出現回数
   • 広告主サイトコンテンツを読了したユーザー数（ユーザー分子）
   • 収集した全WEBサイトにおける接触ユーザー数（ユーザー分母）

4.ビジュアライズ

このうち、１〜２は既に実施済みでデータが揃っていましたため、今回は３〜４について触れたいと思います。
※MeCabやWord 2 Vecを使った自然言語処理や、最近ですとテキストマイニングを行ってくれる便利なAPIがIBM Watson,google,Microsoft Azure等各クラウドサービスから出ており、比較的ハードル低く文章解析を行えるようになっています。

#工夫した点
２までの状態で、データベース(Google BigQuery)には以下テーブルが存在します。

[A] 解析済コンテンツデータ( Analysis_data )

id(コンテンツID)	URL	text(コンテンツ文章)	word(単語と単語の特徴量)
bc0da25d3c9dda3ec	https://hoge.com/aaa	MeCabへユーザー辞書を追加する方法	["MeCab:0.374687","ユーザー辞書:0.109837","形態素解析：0.092837","追加:0.0372574"]
236e32fb880c61b	https://example.com/123	プライドが邪魔!?男性が「好意がある女性」を避けてしまう理由	["女性:0.184182","好意:0.114299","プライド:0.109852","周り:0.095720","男性:0.090242"]
0c24c49ad9d608a	https://test.jp/bbb	シンガポールの楽しい「写真映えスポット」4選	["マーライオン:0.300851","シンガポール:0.224016","写真:0.094375","写真映えスポット:0.037698"]
7181e1a735d14b	https://sample.co.jp/222	キャスター付けたら使いやすい!可動式にして動線が良くなった暮らしの実例	["キャスター:0.346787","実例:0.123786","可動式:0.122526","台車:0.081578"]

[B]他サイト含むWEBサイトアクセスログ（ All_log ）

|id(コンテンツID)|URL|user_id|
|:--|:--|:--|--:|
|7181e1a735d14b|https://sample.co.jp/222|pzzygJ8GPLANLoT8|
|5df8f8a9031d|https://qiita.com/aaa|Cy5CRevXE1kqc2Bt|
|0c24c49ad9d608a|https://test.jp/bbb|pzzygJ8GPLANLoT8|
|a933bfd6q2d31|https://techblog.gmo-ap.jp/|ZfkWMSc3sMXvJFJq|

[C]広告主サイトのWEBサイトアクセスログ（ advertiser_log ）

user_id	read_flg
oJGbvrkR7RrjEHyr	0
pzzygJ8GPLANLoT8	1
sXAf0VvAn6xmkluE	1
4ywA9tt0fXDO9M0I	0

「広告訴求したいページ（広告主サイト）を閲覧しているユーザーがどんな内容に興味を持っているのか？」
を分析したいので、
　1.広告主サイトを閲覧しているユーザーadvertiser_log.user_idが
　2.他のサイトでどのような内容に触れているかAnalysis_data.wordを付け合わせ
　3.単語(キーワード)毎の閲覧ユーザー数
を出すことで、より多くの「広告主サイトを閲覧しているユーザー」が外部サイトで興味を持っているキーワードが何かを調べます。

今回はあるファッションECサイトの例で分析してみることにします。

#standardSQL
WITH
  # 広告主訪問ユーザー
  USER AS (
    SELECT
      user_id
    FROM
      `advertiser_log`
    WHERE
      read_flg = 1
  ),
  # コンテンツを訪れたコンテンツIDとユーザー数
  ARTICLE_IDS_USER_NUM AS (
    SELECT
      id,
      COUNT(*) AS count
    FROM
      All_log
    GROUP BY id
   ),
　# 単語を含んだ記事と広告主訪問ユーザー
  KEY_ARTICLE_USER AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SPLIT(tmp, ':')[OFFSET(1)] AS importance,
      miun.id AS id,
      miun.count AS user_count
    FROM
      `Analysis_data`
    # joinし訪問ユーザー数取得
    INNER JOIN ARTICLE_IDS_USER_NUM AS miun
            ON miun.id = id
    WHERE
      id IN (
        SELECT
          id
        FROM
          ARTICLE_IDS_USER_NUM
     )
    ORDER BY
     miun.count DESC,
     key DESC
    )

SELECT
    key,
    importance,
    user_count
  FROM
     KEY_ARTICLE_USER
  ORDER BY
     user_count DESC

より濃いデータ抽出を行うために、read_flg = 1を指定し、広告主サイトを読了したユーザーを対象に絞り込みました。

しかしながらこの結果で得られるキーワード毎のユーザー数は以下のようになります。
どのコンテンツにも登場しそうな、一般的なキーワードが列挙されており特徴を捉えられていません。

key	importance	user_ct
こと	0.021883	121893
1	0.036271	84974
もの	0.025256	51238
2019	0.011956	31047
方	0.014465	29862
中	0.009523	26354
女性	0.010214	20908

そこで、一般的なキーワードはよく出現しますので、それらをノイズ除去するために「頻出単語」を割り出して、省けるようにします。

###頻出単語
上記の抽出結果のkeyとimportanceの通り、「単語と単語の特徴量」を把握できています。
この特徴量は、各文章毎に文章内におけるその単語の重要性（文章構成上の意味合いの強さ）を表すポイントです。
そのためキーワード毎に集計し、「全文章のポイント平均が低いキーワード」＝「どの文章内においても重要度(特徴量)が低いキーワード」ということになります。

 SELECT
   RANK() OVER (ORDER BY COUNT(*) DESC) AS rank,
   key,
   AVG(importance) AS importance,
   COUNT(*) AS count,
 FROM ENTITY_TYPE_EXCEPT
 GROUP BY
   key
ORDER BY
 rank;

上記のようにキーワード毎の平均ポイントを出すことで、以下の結果を得ます。

rank	key	importance	count
1	こと	0.021883	128693
2	1	0.036271	84974
3	もの	0.025256	51238
4	方	0.014465	29862
5	事	0.0131292	22441
6	中	0.009523	26354
7	女性	0.059770	21893
このテーブルを先ほどの結果とJOINしてあげ、importance > 0.05 rank > 100など低い値をフィルタしてあげますと頻出単語（ノイズ）が省かれた結果を得ることができます。

key	importance	rank	user_count	article_count
コーディネート	0.0556	251	709	16,292
神田沙也加	0.1341	207	7,895	1,351
彼女	0.0540	204	112	177,199
髪型	0.0703	189	749	15,605
プチプラ	0.0552	179	1,264	9,469
容疑者	0.0720	177	363	29,694
沢尻エリカ	0.1031	175	938	12,631
画像	0.0803	168	138	328,551
壇蜜	0.1184	163	282	11,650
レディースコーデ	0.0943	360	190	126,157

かなり頻出単語がフィルタされました。今回はファッション関連の商品ページを見ているユーザーの興味ワードを分析していたため、当初の仮説通りファッション関連のワードが浮かび上がってきました。

しかし、まだ一部のノイズワードが混ざってしまっています。
それは「トレンドワード」です。
トレンドワードは、特定の広告主のサイトに訪問したことがある人がよく閲覧しやすいわけではなく、全ユーザーの傾向として特定の時期に閲覧回数が増えるため、これが混ざっていると特徴としては捉えにくくなってしまいます。
そのため、「トレンドワード」を定義し、それを除外するようにします。

###トレンドワード
今回使用した[B]他サイト含むWEBサイトアクセスログ（ All_log ） のテーブルは日毎にテーブルを作成しBigQueryに保存していましたため、この日毎の出現回数の差分を比較し、増加率を求めたいと思います。
キーワード毎の増加率を上記の結果にJOINします。

#standardSQL
CREATE TEMPORARY FUNCTION
  TABLE_YMDH() AS (FORMAT_TIMESTAMP("%Y%m%d%H", TIMESTAMP "2019-12-06 07:00:00", "UTC"));
CREATE TEMPORARY FUNCTION
  CURRENT_YMD() AS (FORMAT_DATE("%Y%m%d", DATE (2019,12,04)));
CREATE TEMPORARY FUNCTION
  PREV_YMD() AS (FORMAT_DATE("%Y%m%d", DATE (2019,12,03)));
WITH
  CURRENT_ENTITYS AS (
    SELECT
      SPLIT(REGEXP_REPLACE(entities, r'[\[\]\"]', '')) AS entities
    FROM
      `Analysis_*` AS c
    WHERE
      _TABLE_SUFFIX = TABLE_YMDH()
	  AND FORMAT_TIMESTAMP("%Y%m%d", created, "Asia/Tokyo") = CURRENT_YMD()
  ),
  CURRENT_ENTITY_SPLIT AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SAFE_CAST(SPLIT(tmp, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS importance,
    FROM
     CURRENT_ENTITYS
  ),
  CURRENT_ENTITY_TYPE_EXCEPT AS (
    SELECT
      key,
      importance
    FROM CURRENT_ENTITY_SPLIT
    WHERE key != ''
  ),
  CURRENT_TMP AS (
     SELECT
       key,
       COUNT(*) AS count
     FROM CURRENT_ENTITY_TYPE_EXCEPT
     GROUP BY
       key
   ),
  PREV_ENTITYS AS (
    SELECT
      SPLIT(REGEXP_REPLACE(entities, r'[\[\]\"]', '')) AS entities
    FROM
      `Analysis_*` AS c
    WHERE
      _TABLE_SUFFIX = TABLE_YMDH()
	  AND FORMAT_TIMESTAMP("%Y%m%d", created, "Asia/Tokyo") = PREV_YMD()
  ),
  PREV_ENTITY_SPLIT AS (
    SELECT
      SPLIT(tmp, ':')[OFFSET(0)] AS key,
      SAFE_CAST(SPLIT(tmp, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS importance
    FROM
      PREV_ENTITYS
  ),
  PREV_ENTITY_TYPE_EXCEPT AS (
    SELECT
      key,
      importance
    FROM PREV_ENTITY_SPLIT
    WHERE key != ''
  ),
  PREV_TMP AS (
     SELECT
       key,
       COUNT(*) AS count
     FROM PREV_ENTITY_TYPE_EXCEPT
     GROUP BY
       key
   )
   SELECT
     ct.key,
     ct.count AS ct_count,
     pt.count AS pt_count,
     ct.count /  pt.count AS rate_up
   FROM CURRENT_TMP AS ct
   LEFT OUTER JOIN PREV_TMP AS pt
   ON ct.key = pt.key
   WHERE
   ((ct.count > pt.count OR pt.key IS NULL) )
   ORDER BY rate_up DESC, ct.count DESC

key	importance	rank	user_count	article_count	rate_up
コーディネート	0.0556	251	709	16,292	1.431
神田沙也加	0.1341	207	7,895	1,351	15.357
彼女	0.0540	204	112	177,199	1.341
髪型	0.0703	189	749	15,605	1.501
プチプラ	0.0552	179	1,264	9,469	1.133
容疑者	0.0720	177	363	29,694	12.747
沢尻エリカ	0.1031	175	938	12,631	15.357
画像	0.0803	168	138	328,551	1.429
壇蜜	0.1184	163	282	11,650	23.500
レディースコーデ	0.0943	360	190	126,157	1.203

最終的に得られたrate_upについて、rate_up < 10 など増加率の高いワードをフィルタし除いてあげるとトレンドワードを省くことができます。

さらに、それでも「画像」「彼女」といった曖昧な単語が混ざってしまっております。
頻出単語で省ければ良いのですが、他の単語と比較するとそこまで頻出しないということもあり除きにくいワードでした。
そこで、wikipediaのデータをうまく活用することにします。

###wikipedia
wikiediaには「曖昧さ回避」というカテゴリーが存在します。
これは文脈によって内容が異なる単語（曖昧な単語）を判別しやすくするためのラベルづけとしてWwikipediaが設定しているものです。

この「曖昧さ回避」ラベルがついている単語はノイズになりやすいので、除外できるようにします。
wikipediaのデータをBigQueryで使えるようにする方法については、こちらで紹介しています。
Wikipediaデータ MySQLを経由してBigQueryにLoadする

key（単語）を軸にJOINし、フィルタできるようにします。

key	importance	rank	user_count	article_count	rate_up	wiki
コーディネート	0.0556	251	709	16,292	1.431	0
彼女	0.0540	204	112	177,199	1.341	1
髪型	0.0703	189	749	15,605	1.501	0
プチプラ	0.0552	179	1,264	9,469	1.133	0
画像	0.0803	168	138	328,551	1.429	1
レディースコーデ	0.0943	360	190	126,157	1.203	0

#使い道・フィードバック

上記の通り上手くノイズ除去していくとそれなりに意味がありそうなデータがアウトプットできました。
最終的なアウトプットで想定していた1. 特徴キーワードです。

key	importance	rank	user_count	article_count	rate_up	wiki
コーディネート	0.0556	251	709	16,292	1.431	0
髪型	0.0703	189	749	15,605	1.501	0
プチプラ	0.0552	179	1,264	9,469	1.133	0
レディースコーデ	0.0943	360	190	126,157	1.203	0
クリスマスデート	0.0871	394	168	141,868	1.577	0
…	…	…	…	…	…	…
ニットタイトスカート	0.1660	846	363	1,006	1.400	0

これをgoogleデータポータルや、スプレッドシートに出力し、バブルチャートでビジュアライズしたものが以下の2. インサイトグラフになります。

縦軸「キーワード特徴量」はimportance
横軸「訪問ユーザー数」はuser_count(広告主サイト訪問ユーザー数)
バブルの大きさはarticle_countはキーワードが含まれるコンテンツの総数です。

こうしてみると、

• 自社サイト(広告主サイト)へ訪問しているユーザーが、他サイトではどんな内容（キーワード）に興味を持っているのか？
• そのキーワードはよく出てくるワードなのか、ニッチなワードなのか？

を調べられるので、キーワード分析からユーザーインサイトを発掘することができ、さらにそのキーワードに対しての重要性も判断できることになります。

実際にこちらのレポートを広告主の方にご覧いただいたところ、実際のWEBサイトコンテンツ戦略にかなり当てはまっており、かつ自社では把握できていなかったユーザーインサイトの発掘につながったとのフィードバックでした。
#今後の展開

• 近いキーワード毎にクラスタリング
• キーワードのジャンル毎にラベルづけ
• Google Trendsや、Googleキーワードプランナーなどの検索ボリュームのデータと付け合わせ

等できると、より分析のしがいがあるなと感じています。

明日は@sandatsさんの　GKEとCloud SQLを使ってhelmでRedmineを建ててみる　のお話です。
お楽しみに！