広告/統計/アニメ/映画 等に関するブログ

広告/統計/アニメ/映画 等に関するブログ

Pamdasで特定列が条件を満たすデータをグループ単位で抜き出す

Webサイトのアクセスログを分析していると

コンバージョンに至ったユニークブラウザだけ抜き出してアクセス履歴を抜き出したい

ということがあると思います。

これが意外と面倒だったので備忘録的にまとめておきたいと思います。

マーケティングオートメーションのメールからコンテンツにアクセスした人のリストを抜き出す

「~/hogehoge_1/」というページにアクセスしたデータを抜き出すにはログデータの「URL」列に対して文字列検索をします。例えば、str.containsを使うのが良いでしょう。

hoge_list = df["url"].str.contains("hogehoge_1/')

更に、マーケティングオートメーションツール(例えばマルケト等)で配信したメールのリンクURLに「mkt」というパラメータを振り振っていたとして、Marketo経由で(=条件1)かつ「~/hogehoge_1/」にアクセスした(条件=2)ような行(レコード)を抜き出すには、「&」でつなげます。

hoge_list = df["url"].str.contains("mkt") & df["url"].str.contains("hogehoge_1/")

但し、このままでは「~/hogehoge_1/」にマルケトメール経由でアクセスした履歴が終えるだけで、他にどのページを見たか?という情報が消えてしまいます。

そこで、「~/hogehoge_1/」にアクセスした「IPアドレスの一覧」1を一旦取得し、リスト化します。

hoge_ip_list = df[hoge_list]["ip"]

コンバージョンしたリストを元に元のデータフレームにラベルをはる

普通にこのIPアドレスリストを使ってデータフレームから抜き出しても良いのですが、コンバージョンの種類が1つではないなど、今後の汎用性を考えると、元のデータフレームにラベルを付与する方が良いでしょう。

一度データフレーム化し、インデックスをリセットしています。そうしないとラベルが正しく作成できません。

hoge_ip_list_df = pd.DataFrame(lhoge_ip_list)
hoge_ip_list_df = hoge_ip_list_df.reset_index(drop=True)

コンバージョンしたIPアドレスリストの個数を「len(~.index)」を使って計測し、その数だけ「hogehoge_cv」 という文字列を作成。

hoge_label = pd.DataFrame({"hoge_cv":["hogehoge_cv"] * len(hoge_ip_list_df.index)},columns=["hoge_cv"])

hoge_cv」という列名でコンバージョンしたIPアドレスリストのデータフレームに列を追加します。

hoge_ip_list_df["hoge_cv"] = hoge_label

コンバージョンしたIPアドレスリストのデータフレームと元のデータフレームをleeft_joinすれば、条件を満たすものだけが残ります。

df_mkt = pd.merge(hoge_ip_list_df,df,how="left")

CSVファイル化すればExcelユーザーに共有可能です。

df_mkt.to_csv("df_mkt_all.csv")
同様に別のコンバージョンもラベル化した場合

他のCVのラベルも作った場合はそれらのIPアドレス一覧データフレームをmergeしていけば良いでしょう。

hoge_ip_list_1_2_df = pd.merge(hoge_ip_list_df,hoge_ip_list_2_df,how="outer")

メールで配信したコンテンツの閲覧だけ欲しいとき

全ページではなく、メールで配信したコンテンツの閲覧状況だけに更に絞りたい場合。 先程のデータの中から配信コンテンツをマーケティングオートメーションのメール経由で閲覧したレコードだけ集計してconcatで縦に結合していけば絞れます。

df_mrkt_mail2 = df_mkt[df_mkt["url"].str.contains("mkt") & df_mkt["url"].str.contains("hoge_mail_2/")]
df_mrkt_mail3 = df_mkt[df_mkt["url"].str.contains("mkt") & df_mkt["url"].str.contains("hoge_mail_3/")]

df_mkt_mails = pd.concat([df_mkt_mail2,df_mkt_mail3], ignore_index=False)

パラメータなしのURLが欲しいとき

パラメータ付きURL以外にパラメータなしのURLが欲しいときは、str.splitを使い「?」で分割し、その1つ目の要素(つまりPython的には0番目)を抜き出して、新しい列「path」として插入します。

df_mkt["path"] = df_mkt["url"].str.split("?").str.get(0)

  1. 別にユニークブラウザでも構いません。BtoBを意識してIPアドレスにしてみました。