見出し画像

ChatGPTでAMCのクエリを書いてみた。ユーザーはコンバージョンする前に何回広告をクリックしているのか？

ubun inc.

こんにちは。ウブン広告チームの田中です。

ChatGPTのおかげで、広告運用担当者が自らクエリを書いてAMCで分析することが増えてきました。本記事では、実際にChatGPTでクエリを生成し、そのクエリを使ってAMCからデータを抽出し、広告データの分析を行ってみたいと思います。

分析のテーマはユーザーのクリック回数です。アトリビューション分析の前提は、ユーザーが複数回広告をクリックしていることです。では、実際にユーザーは何回くらい広告をクリックしているのでしょうか？ChatGPTとAMCを使って分析してみましょう。

ChatGPTにより非エンジニアでも自然言語でクエリが書けるように

ChatGPTの登場により、非エンジニアでも自然言語を用いて簡単にクエリを書くことができるようになりました。これまでデータ分析に必要だった専門的な知識やスキルが、ChatGPTのおかげで大幅に軽減され、幅広いユーザーがデータ分析の世界にアクセスできるようになりました。これにより、データ分析を活用した戦略立案や意思決定が、より多くの人々にとって現実的なものとなっています。

AMCでのデータ提供形式とクエリの必要性

Advertising Management Console (AMC) では、データがログ形式で提供されます。そのため分析を行うためにクエリが必要であり、従来は利用の障壁となっていました。特に非エンジニアにとっては、クエリのゼロイチの作成が難しく、AMCを活用することが困難でした。しかし、ChatGPTの登場により、この問題が解消されつつあります。

こちらの記事でもAMCを使った時間帯別のパフォーマンス分析をおこなっています。是非ご覧ください。

実際にChatGPTにクエリを書いてもらう

プロンプト生成には少々苦労しました。シンプルな質問に対しては簡単にクエリを生成できるのですが、やや複雑な処理が必要な場合は日本語の微妙なニュアンスの違いによって生成されるクエリが変わってしまいます。

試行錯誤の結果、最終的なプロンプトは以下のようになりました。テーブル名、カラム名はマスキングしてます。

[コンテンツの詳細]
このコンテンツはmysqlのqueryです。

[table]
table_name：aaa
column(data)：111(string),222(long),333(timestamp),444(string)
※このテーブルは常に222=1でフィルタリングしてください。

table_name：bbb
column(data)：777(timestamp),888(string),999(boolean)
※このテーブルは常に888 isnot null でフィルタリングしてください。

[C1]＝：まずユーザーごとのクリック回数を算出し、さらにクリック回数ごとのユーザー数とクリック数を算出してください。※クリック数は333をカウントし算出してください。
[C2]＝：まず2023年2月にconversionしたユーザー一覧を作り、その一覧に含まれるユーザーに限定して[C1]を実行してください。
[C3]＝：999=trueに限定して[C2]を実行してください。
[C4]＝：conversionから遡及して14日以内のclickのみを対象として[C3]を実行してください。

> run commands [C1][C2][C3][C4]

まず、求めているアウトプットがクエリであることを明確に定義します。次に、テーブル名とカラムを入力します。最後に、必要なクエリを4段階に分けて入力していきます。

全ての条件を一度に含めると、日本語の文章が長くなり、ChatGPTに求めているクエリを作成させることが困難でした。このように4段階に分けることで、非エンジニアの私でもChatGPTが理解できるプロンプトを書くことができました。

それでは、順番に見ていきましょう。

ユーザーは何回広告をクリックしているのか？

第1段階[C1]では、シンプルにユーザーのクリック数の分布を求めています。クリック数のカウント方法やログの前処理は、プロンプト上で定義しています。

ChatGPTから戻ったクエリを実際に使用して、2023年1月～2月の期間にあるアカウントのデータを取得してみました。ユーザーごとの平均広告クリック回数は1.4回で、その分布は以下のようになりました。

2か月間で、76%のユーザーは広告を1回しかクリックしていません。さらに、2回クリックしたユーザーが15%、3回クリックしたユーザーが5%で、合計すると3回以内で96%のシェアを占めています。

ほとんどのユーザーが1回しか広告をクリックしないというのは、アトリビューション観点では不都合な真実感がありますが、このデータにはコンバージョンに至らないユーザーも多く含まれています。コンバージョンしないユーザーの広告クリック回数は1回が多く、全体の平均を大きく押し下げているのではないでしょうか。

そこで、2月にコンバージョンしたユーザーに絞って、同じデータを再度取得してみましょう。

コンバージョンしたユーザーは何回広告をクリックしているのか？

第2段階[C2]では、まず2月にコンバージョン（CV）したユーザーの一覧を作成し、それを第1段階[C1]のクエリに結合（ジョイン）しています。先ほどと同じアカウントで再度データを取得してみた結果、平均クリック回数は1.6回にわずかに上昇しました。

クリック回数1回のみのユーザー比率はやや減少しましたが、大きな傾向に変化はありませんでした。リピートユーザーは何度も広告をクリックせず即決していることが想定されるため、さらに新規コンバージョンユーザーに絞って同じ分析を行ってみましょう。

新規コンバージョンしたユーザーは何回広告をクリックしているのか？

AMCのコンバージョンログには新規コンバージョンであるかどうかのフラグが付いています。こちらのフラグを使ってフィルタリングし、[C2]を実行しました。

平均クリック回数は1.7回にあがりました。その分布は下記です。

クリック回数が1回のみのユーザーの比率はさらに減少しました。ここまでの結果をまとめると、このアカウントでは新規コンバージョンユーザーにおいても平均クリック回数が1.7回と少ないことがわかります。このことから、アトリビューション分析による運用最適化は、あまり大きな改善インパクトが期待できないことが示唆されます。

ただし、この結果はあくまで一つのアカウントにおける分析結果であり、他のアカウントや業界によっては異なる傾向があるかもしれません。

これまでの分析では、クリックとコンバージョンの時間的関係性が考慮されていませんでした。最後に、対象とするクリックをコンバージョンから遡及日（lookback window）でフィルタリングしてみましょう。これにより、コンバージョンに直接関連するクリックだけを対象にした分析が可能になります。

【遡及期間＝14日】新規コンバージョンしたユーザーは何回広告をクリックしているのか？

第4段階[C4]のクエリは残念ながら最終的には成功しませんでした。遡及期間を限定する部分は下記のようなクエリでしたが、この部分でエラーが出てしまいました。

AND click.event_dt >= DATE_SUB(conversion.event_dt, INTERVAL 14 DAY)

AMCで発生したエラーは下記です。

The workflow could not be executed because it was invalid: The SQL query could not be parsed: Encountered "INTERVAL 30" at line 22, column 55.

ChatGPTにエラーをそのまま戻してみるとクエリを書き直してくれました。その結果概要部分は下記に書き換わりました。
AND click.event_dt >= TIMESTAMPADD(DAY, -30, conversion.event_dt)

再度AMCに投げてみたところ、今度は違うエラーが発生しました。
The workflow could not be executed because it was invalid: Data type INTERVAL_DAY is not supported.

その後、エラーとクエリの書き換えが無限ループして解決には至りませんでした。クエリの問題というよりはAMCのデータベースの仕様との衝突のような気もしますが非エンジニアの私はここでギブアップ。。

まとめ

ChatGPTは自然言語からうまくクエリを生成してくれますが、クエリが複雑になるほど微妙な日本語のニュアンスの違いで出力が異なることがあります。そのため、プロンプトの書き方が重要です。ただし、正解となるクエリが分からない場合、プロンプトの試行錯誤も難しいというジレンマが存在します。

今回はGPT4を使ってますが、同じプロンプトでも日にちが変わると異なるクエリが戻りました。クエリが正しいかどうかはやはりまだ人が判断する必要があります。

ビジネスサイドの誰もがChatGPTでクエリを書いて必要なデータを取り出せる世界はもう少し先のようですが、クエリを学び始める人にはすでに非常に有用な状態です。

分析結果については、アカウントごとの商品特性によると思われますが、今回の分析では多くのユーザーがコンバージョンに至るまでに広告を1回しかクリックしていないことがわかりました。この場合、短期的な直接効果に注目して広告運用の最適化を行うことがむしろ合理的と考えられます。ただし、他のアカウントや業界によっては異なる傾向があるため、広告戦略の最適化を検討する際には、複数のデータを参照して総合的な判断を行うことが重要です。

最後に

ChatGPTを活用してクエリを自ら生成し、分析結果からより本質的な広告運用の最適化を図ることは、今後のデジタルマーケティング人材にとって標準的なスキルとなると感じました。さらに、ChatGPTを用いて分析結果をドキュメントにまとめる能力も重要となります。

ウブンの広告運用チームもChatGPTとAMCを使ってその先陣を切る存在になっていきたいです。

さて、ウブンでは一緒に働く仲間を募集しています。

デジマ・運用型広告のネクストレベルを追及している方、直近ではキャリアチェンジを考えていなくてもまずはお気軽に情報交換させてください。

長文をお読みいただきありがとうございました。これからも情報発信続けていければと思います。よろしくお願いします！

この記事が気に入ったらサポートをしてみませんか？