週次KPIレポートを confluenceへUpするためにやったこと Tokyo.R #82 前田和寛(kazutan) 2019-10-26

はじめに 2 / 40

はじめに 自己紹介 名前/アカウント 前田和寛(Maeda Kazuhiro) @kazutan Twitter GitHub Qiita, etc... 所属 LINE Fukuoka株式会社 開発センター DataLabs DataAnalysisチーム マネージャー 3 / 40

はじめに 業務 LINEグループにあるデータを活用して様々な課題にアプローチ サービス関連 サービスの成長につながる分析 施策の効果検証など オペレーション関連 オペレーションの可視化 オペレーションの効率化につながる分析など データ分析環境関連 分析環境の準備・構築 ライブラリ・パッケージ開発など 4 / 40

はじめに 今日のお話 私が担当したサービス関連の分析業務で、実際に構築した事例を紹介 具体的なデータおよびコードは社内情報のため伏せてます 量が多いので、かいつまんで説明します 詳細の実装については個人的に質問してください 5 / 40

1.今回の課題 6 / 40

1.今回の課題 背景(かんたんに) 弊社のとあるサービスにて 過去8週分のweeklyでの各KPI指標を毎週算出してレポートしていた UU、訪問数、その他... 以下のようなリクエストが もっと長期的な流れを見たい 異常値かどうか判断したい 将来予測も出してほしい 7 / 40

1.今回の課題 課題(かんたんに) 大枠の処理は以下の通り weelky KPIsを算出 算出したKPIスコアを用いてモデリング fitting 予測値と実測値から異常値を検出 forecast レポート作成 各KPI指標をplot 関係者が閲覧できる適切な場所へレポート作成 8 / 40

1.今回の課題 要件整理 KPI算出 データはすべて分析用DBにある でもデータがかなり大きい KPI指標によって参照テーブルや集計ロジックが異なる 単一のクエリだけですべて算出するのは厳しい 各KPI指標ごとにSQLでデータを切り出して、Rでいい感じにまとめる いい感じにまとめたら、中間テーブルへ書き出す 9 / 40

1.今回の課題 モデリング KPIテーブルからデータを読み込み、Rでモデリング prophetを利用 レポートに必要なスコアをまとめ、中間テーブルへ書き出す レポート作成の際はこのテーブルだけを読み込めばいいようにする レポート作成 plot作成 ggplot2を採用 予測範囲を外れている実測値には予めフラグ処理 レポート作成 R Markdown で雛形を作成 レポート設置 confluence上に設置 conflrを活用 10 / 40

1.今回の課題 アプローチ 11 / 40

2.前処理とKPI算出 12 / 40

2.前処理とKPI算出 対象処理 KPI算出 データはすべて分析用DBにある でもデータがかなり大きい KPI指標によって参照テーブルや集計ロジックが異なる 単一のクエリだけですべて算出するのは厳しい 各KPI指標ごとにSQLでデータを切り出して、Rでいい感じにまとめる いい感じにまとめたら、中間テーブルへ書き出す 13 / 40

2.前処理とKPI算出 ここでのアプローチ 14 / 40

2.前処理とKPI算出 glueとpurrrを使ったクエリ実行(1) glueパッケージ https://glue.tidyverse.org/ 一部パラメータ化された文字列(雛形)に対して、後から文字列を挿入 同一のKPIを算出する場合、ロジックは同一 これを出したい期間ごとにクエリを作成すればいい 15 / 40

2.前処理とKPI算出 SQLファイルの例: select (略) from (略) where date between {start_date} and {end_date} Rでの実行例: # 上述 kpi1_sql_base 格納 kpi1_sql glue(kpi1_sql_base, start_date = "20191001", end_date = "20191007") でも、これだと各期間でひとつずつやっていくのが面倒 16 / 40

2.前処理とKPI算出 glueとpurrrを使ったクエリ実行(2) purrrも活用 先程のRスクリプトを以下のように変更: # 開始日 終了日 準備 start_date c("20191001", "20191008", "20191015") end_date c("20191007", "20191014", "20191022") # 関数 定義 f_create_sql_kpi function(sql, start_date, end_date) { glue(sql, start_date = start_date, end_date = end_date) } # 作成 kpi1_sql map2(start_date, end_date, f_create_sql_kpi, sql = kpi1_sql_base ) 17 / 40

2.前処理とKPI算出 glueとpurrrを使ったクエリ実行(3) どうせならクエリ実行もやってしまえばいい # 関数 変更 f_create_kpi function(sql, start_date, end_date) { sql glue(sql, start_date = start_date, end_date = end_date) df ( sql 実行 df 出 処理 書 ) return(df) } # 一気 kpi1_df map2_dfr(start_date, end_date, f_create_kpi, sql = kpi1_sql_base) 18 / 40

2.前処理とKPI算出 算出した指標をまとめ、KPIテーブルに書き込む 詳細は省略 KPIごとにdfが存在するので、あとはRでハンドリングすればOK ハンドリングが終われば、RからDBへ書き込み処理 なお弊社には社内ライブラリでDBにかんたんに接続してread/writeできる関数が準備して あります 19 / 40

3.モデリング 20 / 40

3.モデリング 対象処理 モデリング KPIテーブルからデータを読み込み、Rでモデリング prophetを利用 レポートに必要なスコアをまとめ、中間テーブルへ書き出す レポート作成の際はこのテーブルだけを読み込めばいいようにする 21 / 40

3.モデリング ここでのアプローチ 22 / 40

3.モデリング prophetとpurrrによるfitting(1) KPIテーブル(tbl_kpis )は以下のような感じ: start_date end_date var_name value 20191001 20191007 UU 13612467 20191008 20191014 UU 11466342 20191015 20191022 UU 16245072 20191001 20191007 visits 40083382 20191008 20191014 visits 30704666 23 / 40

3.モデリング prophetとpurrrによるfitting(1) tidyなデータにしているので、var_name でnestしてprophetしたい まずはfittingしてreportingに必要な指標を取得する関数を準備 # prophet 関数 準備 fit_by_prophet function(df, (prophet 引数 準備) ) { fit prophet(df = df, (prophet 引数 引 渡 ) ) future make_future_dataframe(fit, (各種設定)) pred predict(fit, future) res pred %>% select(ds, starts_with("yhat")) %>% left_join(fit$history, by = c("ds" = "ds")) %>% select( c(floor, t, y_scaled)) return(res) } 24 / 40

3.モデリング prophetとpurrrによるfitting(2) あとは以下のような感じでnestしてpurrrのmapしていけばOK fits_weekly_kpi kpi_table %>% group_by(var_name) %>% nest() %>% mutate(pred = map(data, fit_by_prophet, (各種 設定 ) )) %>% select(var_name, pred) %>% unnest() %>% mutate(start_date = ymd(ds)) %>% select(start_date, var_name, y, yhat, yhat_lower, yhat_upper) 25 / 40

3.モデリング 結果をKPIテーブルに書き込む 詳細は省略 上記のコードでdfができるので、それをまるっと書き込む なお弊社には社内ライブラリでDBにかんたんに接続してread/writeできる関数が準備して あります 26 / 40

4.レポート作成 27 / 40

4.レポート作成 対象処理 レポート作成 plot作成 ggplot2を採用 予測範囲を外れている実測値には予めフラグ処理 レポート作成 R Markdown で雛形を作成 レポート設置 confluence上に設置 conflrを活用 28 / 40

4.レポート作成 ここでのアプローチ 29 / 40

4.レポート作成 ggplot2によるKPI可視化(1) 今回のplotに利用する変数は以下の通り: var_name: KPI指標の名称 start_date: 週の開始日 y: 実測の集計値 yhat: fitさせた予測値 yhat_upper, yhat_lower: 予測のCI上限, 下限 今回は加え、yがyhat_upper, yhat_lowerの区間から外ているかどうかのフラグ変数 (outlier)を作成 これらを一気にやる関数を準備 30 / 40

4.レポート作成 ggplot2によるKPI可視化(2) 準備した関数はこんな感じ: plot_kpi function(df, target_var, plot_title, decimal = FALSE) { # 変数名 使 filter & 変数追加 df_for_plot filter(df, var_name target_var) %>% mutate(outliers = case_when( y > yhat_upper ~ "upper", y < yhat_lower ~ "lower", is.na(y) ~ NA_character_, TRUE ~ "middle" )) subtitle_text paste("対象 :", min(filter(df_for_plot, !is.na(y))$start_dat "-", max(filter(df_for_plot, !is.na(y))$start_dat 31 / 40

4.レポート作成 (続き) #ggplot p ggplot(df_for_plot) + # geoms geom_ribbon(aes(x = start_date, ymin = yhat_lower, ymax = yhat_upper), fill = "#0000ff33") + geom_line(aes(x = start_date, y = yhat), color = "#0000ffaa") + geom_point(aes(x = start_date, y = y, color = outliers), show.legend = FALSE, na.rm = TRUE) + # scales scale_x_date(date_breaks = "1 month", date_labels = "%Y/%m") + scale_y_continuous(labels = if (!decimal) scales comma else waiver()) + scale_color_manual(values = c("red", "black", "blue", "gray")) + # labels and themes theme_bw() + labs(title = plot_title, subtitle = subtitle_text, caption = "点 実測値 実線 帯 予測値 \n将来8週間 予測 \n日 x = "", y = "") # output p } 32 / 40

4.レポート作成 ggplot2によるKPI可視化(3) 仕上がりはこんな感じになる: 33 / 40

4.レポート作成 レポート用Rmd作成 内容自体はシンプルなので詳細は省略 setupチャンクで予測値を含むKPI指標テーブルを読み込む 上述の関数に上記テーブルデータと必要な引数をセットするchunkを準備 これを各KPIごとに準備してあげればOK 34 / 40

4.レポート作成 レポートの設置場所はどうする? とりまく状況 弊社ではConfluence Wikiが最も利用されている すでに多くの社員が利用していて、社内共通のプラットフォームで利用できる でも、コピペでWiki編集するのはつらすぎる 解決策 conflrパッケージ https://github.com/line/conflr LINEが公開しているパッケージ Rmdから直接conflrへUpload可能 RStudioのAddinから実行すればOK!!!!!!! 35 / 40

まとめ 36 / 40

まとめ 課題の確認 weelky KPIsを算出 算出したKPIスコアを用いてモデリング fitting 予測値と実測値から異常値を検出 forecast レポート作成 各KPI指標をplot 関係者が閲覧できる適切な場所へレポート作成 37 / 40

まとめ 達成するためのアプローチ 38 / 40

まとめ 今回の限界および課題 ホントは細かい部分でもっと色々やってる 発表の尺や公表できない部分があり省略してます 実際はこの3ステップをRScriptにそれぞれまとめている そしてそれをオーガナイズするRScriptを準備して実行してる この仕組みで、conflrでの作業がどうしても手作業が残った 構築した当時、conflrはAddinからしかUpできなかった 現在ではAddin経由しなくてもRのコマンドで実行可能 Jenkinsやdroneなどと組み合わせて完全自動化できる!! 39 / 40

Enjoy! 40 / 40