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

#目次

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

#プロジェクト概要説明
　GMOアドマーケティングでは広告配信を行う上で、WEBサイトのコンテンツを解析し、コンテンツにマッチさせた広告を配信しています。
そこで解析した内容を活用し、さらに「広告訴求したいページ（広告主サイト）を閲覧しているユーザーがどんな内容に興味を持っているのか？」を分析することで、ユーザーの興味を事後的に類推する試みを行いました。
<img width="919" alt="スクリーンショット 2019-12-17 23.17.47.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/319813/dcf4113e-439f-cd8c-bd09-63820dec49a6.png">


#最終的なアウトプット
上記の概要図内の、以下がアウトプットとなります。
1. 特徴キーワード
2. インサイトグラフ

<img width="740" alt="スクリーンショット 2019-12-17 17.39.33.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/319813/0c50c9ad-ac2f-d8fe-3035-5407c5277a61.png">
※「インサイトグラフ」のイメージ図


#分析手順
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サイトの例で分析してみることにします。

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

```SQL
 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します。

```SQL
#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が設定](https://ja.wikipedia.org/wiki/Category:%E3%81%99%E3%81%B9%E3%81%A6%E3%81%AE%E6%9B%96%E6%98%A7%E3%81%95%E5%9B%9E%E9%81%BF
)しているものです。

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

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. インサイトグラフ`になります。
<img width="740" alt="スクリーンショット 2019-12-17 17.39.33.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/319813/0c50c9ad-ac2f-d8fe-3035-5407c5277a61.png">

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

こうしてみると、

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

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

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

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

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

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