2021/10/13

開発なしで別プロダクトの新規施策を実現したという特殊な話

こんにちは、クラッソーネでアプリケーションエンジニアをしている菅野です。
10月からワールドトリガー3期のアニメも始まったので充実した生活を送れそうな予感がしてます。

さて、弊社もご多分にもれず多くのやりたいことに対してエンジニアの~~トリオン~~リソースが全然追いついていない比較的小さな規模の組織です。
今回はそんな状況の中オペレーション変更を伴う短期的な仮説検証に早期に対応したという話題で、とある事情から開発なしでアドホックに新規施策のログ蓄積とモニタリングを実現した事例について具体的な手法を交えて紹介していきたいと思います。

前提条件(当時の状況)

まず前提としてざっと以下のような状況になってます。割とあるあるなんじゃないでしょうか。

会社: アーリーステージのスタートアップ
プロダクト: ユーザーからの申し込みで始まるマッチングサービス
注力領域: GMVを高めるための要因分析と仮説検証
希望リードタイム: なる早(！)
想定リードタイム: 不明
- 開発チームはスクラムでPBI(タスク)をさばいているため、他タスクの内容・状況によっては依頼したところで相対的に提供時期が遅くなる可能性があった
データ基盤: なし
- BIツールは全社的に導入しており、参照データはアプリケーションDBのレプリカとなっている
私: 別事業の別プロダクトにて専任開発者
- 対象プロダクトは「自分の庭先」ではないため、知識・コンテクストの面で当然十分にキャッチアップできていない、かつ本件にかかる対応にリソースを割きづらい状態

具体的にやりたい施策

電話によるユーザーサポートで得られた新設ヒアリング項目をBIツール上でモニタリングして仮説検証を行いたい。

つまり素直に要件を検討するとコアとなる申込の周辺データとしてイベントテーブルの新設とそのCRUD操作が必要な依頼であり、かつ短期的な仮説検証に対する開発要求です。
したがって役目を果たしたら他の箇所へ影響を残さないように削除できるようにしておく必要もありそうです。

先述の状況を踏まえるとこの開発自体のROIはキャッチアップコストも含めてあまり高いものにはならなそうですね。

さて、どうやって実現させていくのがいいでしょうか。

実際にやったこと

今回のケース、結論から言うとサポート用の申込備考欄に所定のCSVフォーマットでテキスト行を入力してもらい、BIツールにてSQLを駆使して擬似的なイベントレコードの形に整形の上で抽出する、というなんとも泥臭くてプリミティブな対応を取ることにしました。

決してベストな対処ではないものの、データボリュームや概算の見積りなどもろもろ踏まえて現実的な落としどころ判断した、という感じです。

※ ただし実際のところ「運用でカバー」の極致みたいな危うい対応になるので、十分な開発リソースを準備できたり、適切なDWHが構築済みの場合はこんなことせずに素直に進めていくのがいいでしょう。間違いなく中長期で安定運用していける施策ではないと思います。

1. フォーマットを決める

まずはオペレーションチームに入力したい内容についてのヒアリングを行い、入力データのフォーマットについて関係者で検討していきます。

そして検討の結果、以下のルールのもとでヒアリング内容を申込の備考欄に対して入力していく運用に決まりました。

新設項目の入力開始は特定日から実施する
新設項目は備考欄に行ごとに入力していく
新設項目は備考欄先頭行から入力していく
新設項目は種類に応じて各行以下のフォーマットで入力する(当然ながら中身は実際のデータとは異なりますが、形はこのままです)
- 固定PREFIX, イベント種別1, イベント内容1, イベント内容2, イベント内容3, イベント内容4, 登録日
- 固定PREFIX, イベント種別2, イベント内容1, イベント内容2,登録日

参照したいイベントの区分は複数種類あるため イベント種別 の「列」を用意してます。
また、それぞれで指標となる項目数が異なることにも留意が必要です。

2. 取り決めに従い運用

上述のルールに従いまずは1週間ほど運用してもらいます。

3. 結果を見る

さて、いよいよ蓄積されてきたデータをBIツール上で抽出してみます。
試行錯誤の末、なんやかんやあって以下のような問い合わせ内容に落ち着きました。
(ちなみにRDBMSはMySQL 8系です)

with new_entry_events as (
    select
        e.id as entry_id,
        substring_index(e.memo, “\n”, 1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(e.memo, “\n”, 1) like ‘固定PREFIX%’
    union all
    select
        e.id as entry_id,
        substring_index(substring_index(e.memo, “\n”, 2), “\n”, -1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(substring_index(e.memo, “\n”, 2), “\n”, -1) like ‘固定PREFIX%’
    union all
    select
        e.id as entry_id,
        substring_index(substring_index(e.memo, “\n”, 3), “\n”, -1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(substring_index(e.memo, “\n”, 3), “\n”, -1) like ‘固定PREFIX%’
    union all
    select
        e.id as entry_id,
        substring_index(substring_index(e.memo, “\n”, 4), “\n”, -1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(substring_index(e.memo, “\n”, 4), “\n”, -1) like ‘固定PREFIX%’
    union all
    select
        e.id as entry_id,
        substring_index(substring_index(e.memo, “\n”, 5), “\n”, -1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(substring_index(e.memo, “\n”, 5), “\n”, -1) like ‘固定PREFIX%’
    union all
    select
        e.id as entry_id,
        substring_index(substring_index(e.memo, “\n”, 6), “\n”, -1) as new_entry_event
    from
        entries e
    where
        e.memo like ‘%固定PREFIX,%’
        and e.created_at > ‘2021-01-01’
        and substring_index(substring_index(e.memo, “\n”, 6), “\n”, -1) like ‘固定PREFIX%’
)

select
    new_entry_events.entry_id as ‘申込ID’,
    substring_index(new_entry_events.new_entry_event, “,”, 1) as ‘取得イベント‘,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 2), “,”, -1) as ‘イベント区分‘,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 3), “,”, -1) as ‘イベント内容1’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 4), “,”, -1) as ‘イベント内容2’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 5), “,”, -1) as ‘イベント内容3’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 6), “,”, -1) as ‘イベント内容4’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 7), “,”, -1) as ‘発生日‘,
    concat('https://dummy.crassone.co.jp/ops/', new_entry_events.entry_id) as ‘管理画面URL’
from new_entry_events
where new_entry_events.new_entry_event like ‘固定PREFIX,イベント種別1%’
union distinct
select
    new_entry_events.entry_id as ‘申込ID’,
    substring_index(new_entry_events.new_entry_event, “,”, 1) as ‘取得イベント‘,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 2), “,”, -1) as ‘イベント区分‘,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 3), “,”, -1) as ‘イベント内容1’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 4), “,”, -1) as ‘イベント内容2’,
    null as ‘イベント内容3’,
    null as ‘イベント内容4’,
    substring_index(substring_index(new_entry_events.new_entry_event, “,”, 5), “,”, -1) as ‘発生日’,
    concat('https://dummy.crassone.co.jp/ops/', new_entry_events.entry_id) as ‘管理画面URL’
from new_entry_events
where new_entry_events.new_entry_event like ‘固定PREFIX,イベント種別2%’
order by 申込ID

いきなりヤバそうなクエリが出てきました。一体なにをしているのでしょうか？

解説

まず、このSQLは大きくイベントの事前整形処理とそのCSVの分解処理に大別されます。 new_entry_events を形成する with 句と地のselect文ですね。
それぞれ以下のような処理になってます。

1. 事前整形

まずは with 句の中身です。
備考欄(entries.memo)という申込データに付随するtextカラムから1行ずつ合致するCSV行を抽出していきます。
1行ずつというところがキモで、それぞれ union 句で接続された select 文は以下のことを行ってます。

select
    e.id as entry_id, /* 申込IDを抽出 */
    substring_index(e.memo, “\n”, 1) as new_entry_event /* 備考欄1行目のみを抽出 */
from
    entries e
where
    e.memo like ‘%固定PREFIX,%’ /* 特定の文字列を含む備考欄を対象 */
    and e.created_at > ‘2021-01-01’ /* 施策の開始日以降を対象 */
    and substring_index(e.memo, “\n”, 1) like ‘固定PREFIX%’ /* 備考欄1行目に特定文字列を含むレコードを対象 */

ひとつひとつの select 文では備考欄の特定行目のみを抽出しており、それをカバレッジ十分と思われる数だけ「ガッチャンコ」してる感じです。

substring_index 関数がネストしていますが、これは最初の関数の呼び出しで指定の行目以前の文字列をすべて取得し、続く呼び出しでさらにそのうち最後の行内容のみを取得する、という構造になっています。

存在するレコード状態とクエリ取得結果は以下のような対応になっています。

申込ID	備考
1	固定PREFIX,イベント種別1,ああああ,いいいい,うううう,ええええ,2021-01-01 固定PREFIX,イベント種別2,かかかか,きききき,2021-01-03 あのイーハトーヴォのすきとおった風、夏でも底に冷たさをもつ青いそら、うつくしい森で飾られたモリーオ市、郊外のぎらぎらひかる草の波。
2	固定PREFIX,イベント種別1,ささささ,しししし,すすすす,せせせせ,2021-01-02 山路を登りながら、こう考えた。智に働けば角が立つ。情に棹させば流される。意地を通せば窮屈だ。

entry_id	new_entry_event
1	固定PREFIX,イベント種別1,ああああ,いいいい,うううう,ええええ,2021-01-01
1	固定PREFIX,イベント種別2,かかかか,きききき,2021-01-03
2	固定PREFIX,イベント種別1,ささささ,しししし,すすすす,せせせせ,2021-01-02

ご覧の通り、これで text 型のカラムから対象となるCSV文字列行を集めて擬似的な表として抽出できたことになります。

2. CSVの列分解

ここまでくると後は簡単で、 イベント種別 ごとにCSV文字列を分解して合体させればよい訳なので特に内容は大丈夫でしょう。

この段階で抽出結果は以下の通りとなり、備考欄のそれぞれのCSV行が構造化された擬似的な表として抽出できたことになります。

申込ID	固定PREFIX	イベント種別	イベント内容1	イベント内容2	イベント内容3	イベント内容4	発生日
1	固定PREFIX	イベント種別1	ああああ	いいいい	うううう	ええええ	2021-01-01
1	固定PREFIX	イベント種別2	かかかか	きききき			2021-01-03
2	固定PREFIX	イベント種別1	ささささ	しししし	すすすす	せせせせ	2021-01-02