[マーケティング] RFM分析

!sw_vers

!python -V

from IPython.core.display import HTML

style = """
<style>
    .dataframe thead tr:only-child th {
        text-align: right;
    }

    .dataframe thead th {
        text-align: left;
        padding: 5px;
    }

    .dataframe tbody tr th {
        vertical-align: top;
        padding: 5px;
    }

    .dataframe tbody tr:hover {
        background-color: #ffff99;
    }

    .dataframe {
        background-color: white;
        color: black;
        font-size: 16px;
    }

</style>
"""
HTML(style)

%matplotlib inline
%config InlineBackend.figure_format = 'svg'

import numpy as np
import pandas as pd
import scipy

import calendar
import random

import matplotlib.pyplot as plt

import faker
from faker import Faker


seed = 123
random_state = 123

random.seed(seed)
np.random.seed(seed)


from watermark import watermark

print(watermark(python=True, watermark=True, iversions=True, globals_=globals()))

import os
import sqlite3

db_file_name = "test_data.db"

# 古いファイルが存在すれば削除
if os.path.isfile(db_file_name):
    os.remove(db_file_name)

with sqlite3.connect(db_file_name) as conn:
    cur = conn.cursor()

    # Users テーブルの作成
    cur.execute(
        """
    CREATE TABLE Users (
        UserID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL,
        Birthday TEXT,
        Job TEXT
    );
    """
    )

    # Items テーブルの作成
    cur.execute(
        """
    CREATE TABLE Items (
        ItemID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT,
        Price INTEGER
    );
    """
    )

    # PurchaseHistory テーブルの作成
    cur.execute(
        """
    CREATE TABLE PurchaseHistory (
        PurchaseID INTEGER PRIMARY KEY AUTOINCREMENT,
        UserID INTEGER NOT NULL,
        ItemID INTEGER NOT NULL,
        PurchaseDate TEXT NOT NULL,
        Quantity INTEGER NOT NULL,
        FOREIGN KEY (UserID) REFERENCES Users(UserID),
        FOREIGN KEY (ItemID) REFERENCES Items(ItemID)
    );
    """
    )

    cur.close()
    conn.commit()

with sqlite3.connect(db_file_name) as conn:
    cur = conn.cursor()

    # テーブル一覧を取得
    cur.execute(
        """
        SELECT name FROM sqlite_master WHERE type='table';
    """
    )

    table_list = cur.fetchall()

    cur.close()

for i, table in enumerate(table_list):
    print(f"{i + 1}. {table[0]}")

with sqlite3.connect(db_file_name) as conn:
    sqlite_master_df = pd.read_sql_query("SELECT * FROM sqlite_master", conn)

sqlite_master_df.head()

Faker.seed(seed)
fake = Faker("jp-JP")

user_num = 100
item_num = 3


with sqlite3.connect(db_file_name) as conn:
    cur = conn.cursor()

    # ユーザーテーブルの作成
    for _ in range(user_num):
        name = fake.name()
        birthday = fake.date_of_birth()
        job = fake.job()

        cur.execute(
            f"INSERT INTO Users (Name, Birthday, Job) VALUES (?, ?, ?);",
            (name, birthday, job),
        )

    # アイテムテーブルの作成
    cur.execute(f"INSERT INTO Items (Name, Price) VALUES (?, ?);", ("歯ブラシ", 100))
    cur.execute(f"INSERT INTO Items (Name, Price) VALUES (?, ?);", ("歯磨き粉", 200))
    cur.execute(f"INSERT INTO Items (Name, Price) VALUES (?, ?);", ("柔軟剤", 500))

    cur.close()
    conn.commit()

from scipy.stats import poisson

for _lambda in [0.5, 1, 3]:
    x = np.arange(10)
    mean, var, skew, kurt = poisson.stats(_lambda, moments="mvsk")
    plt.plot(x, poisson.pmf(x, _lambda), marker="o", label=f"$\\\\lambda$={_lambda} ,mean={mean}")

plt.xlabel("$k$")
plt.ylabel("$\\\\text{Poisson}(k|\lambda)$")
plt.grid()
plt.legend()
plt.show()

import numpy as np
import matplotlib.pyplot as plt

from scipy.stats import gamma

for a, b in zip([1, 3, 15], [2, 2 / 3, 2 / 15]):
    x = np.linspace(gamma.ppf(0.0001, a, scale=b), gamma.ppf(0.9999, a, scale=b), 100)
    mean, var, skew, kurt = gamma.stats(a, scale=b, moments="mvsk")
    plt.plot(x, gamma.pdf(x, a, scale=b), label=f"$\\\\alpha$={a}, $\\\\beta$={b:.2f},mean={mean}")

plt.xlim([0, 7])
plt.xlabel("$k$")
plt.ylabel("$\\\\text{Gamma}(k|\\\\alpha, \\\\beta)$")
plt.grid()
plt.legend()
plt.show()

import numpy as np
import matplotlib.pyplot as plt

from scipy.stats import gamma
from scipy.stats import poisson

import datetime
from dateutil.relativedelta import relativedelta

# 購入履歴の数
purchase_history_size = 60

# 仮の現在日付の設定
current_date = datetime.date(2024, 7, 1)

# 現在から60ヶ月前までの日付のリスト
datetime_list = [current_date - relativedelta(months=i + 1) for i in range(purchase_history_size)]

# 三つの商品のガンマ分布のパラメタ
# - 歯ブラシ
#   - alpha : 1
#   - bata  : 1
# - 歯磨き粉
#   - alpha : 3
#   - bata  : 1.5 / 3
# - 柔軟剤
#   - alpha : 5
#   - bata  : 0.5 / 5

gamma_a_list = [1, 3, 5]
gamma_b_list = [1, 1.5 / 3, 0.5 / 5]

with sqlite3.connect(db_file_name) as conn:
    cur = conn.cursor()

    # 各商品に対して購買履歴を作成
    for a, b, _item_id in zip(gamma_a_list, gamma_b_list, range(1, item_num + 1)):

        # それぞれの商品をユーザーが購入する個数を計算
        # 各ユーザー分のlambdaをガンマ分布から乱数を生成
        lambda_list = gamma.rvs(a, scale=b, size=user_num, random_state=random_state)

        # 各ユーザーごとに設定されたlambdaを持つポアソン分布から乱数を生成
        for _lambda, _user_id in zip(lambda_list, range(1, user_num + 1)):

            # ポアソン分布から乱数の生成
            history_list = poisson.rvs(_lambda, size=purchase_history_size, random_state=random_state)

            for _created_at, _history in zip(datetime_list[::-1], history_list[::-1]):

                # 購入された商品の個数が1個以上であれば、データベースにINSERT
                if _history > 0:
                    _year = pd.to_datetime(_created_at).year
                    _month = pd.to_datetime(_created_at).month
                    _random_day = random.choice([i + 1 for i in range(calendar.monthrange(_year, _month)[1])])

                    # 年は固定で、日付だけランダムに設定
                    _random_created_at = f"{_year}-{_month}-{_random_day}"

                    # 購入履歴をデータベースに insert
                    cur.execute(
                        f"INSERT INTO PurchaseHistory (UserID, ItemID, Quantity, PurchaseDate) VALUES (?, ?, ?, ?);",
                        (_user_id, _item_id, int(_history), _random_created_at),
                    )

    cur.close()
    conn.commit()

with sqlite3.connect(db_file_name) as conn:
    users_df = pd.read_sql_query("SELECT * FROM Users", conn).set_index("UserID", drop=True)

users_df.head()

with sqlite3.connect(db_file_name) as conn:
    items_df = pd.read_sql_query("SELECT * FROM Items", conn).set_index("ItemID", drop=True)

items_df.head()

with sqlite3.connect(db_file_name) as conn:
    purchase_history_df = pd.read_sql_query("SELECT * FROM PurchaseHistory", conn).set_index("PurchaseID", drop=True)

purchase_history_df.PurchaseDate = pd.to_datetime(purchase_history_df.PurchaseDate)
purchase_history_df["diff_days"] = purchase_history_df.PurchaseDate.apply(
    lambda x: (pd.to_datetime(current_date) - x).days
)
purchase_history_df.head()

purchase_history_df.groupby(["UserID", "ItemID"])["Quantity"].agg(["mean", "std", "count"]).head(15)

recnecy_df = (
    purchase_history_df.sort_values(by=["UserID", "ItemID", "PurchaseDate"])
    .groupby(["UserID", "ItemID"])
    .tail(1)
    .reset_index(drop=True)[["UserID", "ItemID", "diff_days"]]
)

recnecy_df = recnecy_df.rename(columns={"diff_days": "recency"})
recnecy_df.head()

frequency_df = (
    purchase_history_df[purchase_history_df.diff_days < 100]
    .groupby(["UserID", "ItemID"])
    .Quantity.agg(["count"])
    .reset_index()
)

frequency_df = frequency_df.rename(columns={"count": "frequency"})
frequency_df.head()

monetary_df = pd.merge(purchase_history_df, items_df, on="ItemID", how="left")
monetary_df["total"] = monetary_df.apply(lambda x: x.Quantity * x.Price, axis=1)
monetary_df = monetary_df[monetary_df.diff_days < 100].groupby(["UserID", "ItemID"]).total.agg(["sum"]).reset_index()

monetary_df = monetary_df.rename(columns={"sum": "monetary"})
monetary_df.head()

_temp = pd.merge(recnecy_df, frequency_df, on=["UserID", "ItemID"], how="left").fillna(0)
rfm_df = pd.merge(_temp, monetary_df, on=["UserID", "ItemID"], how="left").fillna(0)
rfm_df.head()

target_ItemID = 2

rfm_df[rfm_df.ItemID == target_ItemID].recency.hist()
plt.xlabel(f"Recency (ItemID = {target_ItemID})")
plt.show()

rfm_df[rfm_df.ItemID == target_ItemID].frequency.hist()
plt.xlabel(f"Frequency (ItemID = {target_ItemID})")
plt.show()

rfm_df[rfm_df.ItemID == target_ItemID].monetary.hist()
plt.xlabel(f"Monetary (ItemID = {target_ItemID})")
plt.show()

plt.scatter(x=rfm_df[rfm_df.ItemID == 1].recency, y=rfm_df[rfm_df.ItemID == 1].frequency)
plt.xlabel(f"Recency (ItemID = 1)")
plt.ylabel(f"Frequency")
plt.grid()
plt.show()

plt.scatter(x=rfm_df[rfm_df.ItemID == 2].recency, y=rfm_df[rfm_df.ItemID == 2].frequency)
plt.xlabel(f"Recency (ItemID = 2)")
plt.ylabel(f"Frequency")
plt.grid()
plt.show()

plt.scatter(x=rfm_df[rfm_df.ItemID == 3].recency, y=rfm_df[rfm_df.ItemID == 3].frequency)
plt.xlabel(f"Recency (ItemID = 3)")
plt.ylabel(f"Frequency")
plt.grid()
plt.show()

fig = plt.figure(figsize=(12, 8))

###################################################
ax1 = fig.add_subplot(131, projection="3d")

target_ItemID = 1

_rfm_df = rfm_df[rfm_df.ItemID == target_ItemID]
x = _rfm_df.recency
y = _rfm_df.frequency
z = _rfm_df.monetary

ax1.scatter(x, y, z, s=20, color="blue", label="RFM analysis")

ax1.set_title(f"RFM (ItemID = {target_ItemID})")
ax1.set_xlabel("Recnecy")
ax1.set_ylabel("Frequency")
ax1.set_zlabel("Monetary")

###################################################
ax2 = fig.add_subplot(132, projection="3d")

target_ItemID = 2

_rfm_df = rfm_df[rfm_df.ItemID == target_ItemID]
x = _rfm_df.recency
y = _rfm_df.frequency
z = _rfm_df.monetary

ax2.scatter(x, y, z, s=20, color="blue", label="RFM analysis")

ax2.set_title(f"RFM (ItemID = {target_ItemID})")
ax2.set_xlabel("Recnecy")
ax2.set_ylabel("Frequency")
ax2.set_zlabel("Monetary")

###################################################
ax3 = fig.add_subplot(133, projection="3d")

target_ItemID = 3

_rfm_df = rfm_df[rfm_df.ItemID == target_ItemID]
x = _rfm_df.recency
y = _rfm_df.frequency
z = _rfm_df.monetary

ax3.scatter(x, y, z, s=20, color="blue", label="RFM analysis")

ax3.set_title(f"RFM (ItemID = {target_ItemID})")

ax3.set_xlabel("Recnecy")
ax3.set_ylabel("Frequency")
ax3.set_zlabel("Monetary")

plt.show()