import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from tqdm import tqdm
from pprint import pprint

payments = pd.read_csv("data/payments.csv")
payments.head()

persents = pd.read_csv("data/persents.csv", index_col=0)
persents.head()

registrations = pd.read_csv("data/registrations.csv", index_col=0)#.dropna()
registrations.head()

budget = pd.read_excel("data/Рекламный бюджет январь 2021.xlsx")
budget.loc[0:5, 'media_source'] = 'Media_source_1'
budget.loc[6:7, 'media_source'] = 'Media_source_2'
budget

df = payments.join(persents.set_index("Payment_types"), on="Payment_types")
df['cost'] = df['real_cost'] * (100 - df['Share, %']) / 100
df = df.join(registrations.set_index('account_id'), on="account_id").dropna()
df

d = {
    ("Media_source_1",       "install",            "Brazil"): "BRA_MS1_install",
    ("Media_source_1",      "purchase",            "Brazil"): "BRA_MS1_purchase",
    ("Media_source_1",       "install",            "Russia"): "RUS_MS1_install",
    ("Media_source_1",      "purchase",            "Russia"): "RUS_MS1_purchase",
    ("Media_source_1",       "install",            "Ukrane"): "UKR_MS1_install",
    ("Media_source_1",      "purchase",            "Ukrane"): "UKR_MS1_purchase",
    ("Media_source_2",       "install",            "Brazil"): "BRA_MS2_install",
    ("Media_source_2",       "install",  "English_speaking"): "ENG_MS2_install",
}

T = []
for l in budget.iloc:
    source, campaign_type, target, *_, spend, _ = l
    if source == 'Total':
        break
    cmp = d[source, campaign_type, target]
    tmp_df = df[df['campaign'] == cmp]
    T.append((source, campaign_type, target, spend / tmp_df['cost'].sum()))

T_df = pd.DataFrame(T, columns=["Media Source", 'Campaign type', 'Target', "Time, (months)"])
T_df = T_df.set_index(["Media Source", 'Campaign type', 'Target'])
T_df.to_csv('tables/payback-period.csv')
T_df.to_markdown('tables/payback-period.md')

fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)
ax.grid()
# ax.set_xlim(1, 70)
ax.set(xlabel='Срок окупаемости, (месяцы)', 
       ylabel='Рекламная компания',
       title='Оптимальный срок окупаемости')

plt.xticks(rotation='vertical')
sns.barplot(ax=ax, data=T_df, x="Time, (months)", hue=T_df.index)
plt.legend(title="");
plt.savefig("pics/payback-period.png")

T_sorted_df = T_df.sort_values(by='Time, (months)')
T_sorted_df.to_csv('tables/payback-period.sorted.csv')
T_sorted_df.to_markdown('tables/payback-period.sorted.md')

fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)
ax.grid()
ax.set(xlabel='Срок окупаемости, месяцы', ylabel='Рекламная компания', title='Оптимальный срок окупаемости')

# plt.xticks(rotation='vertical')
sns.barplot(ax=ax, data=T_sorted_df, x="Time, (months)", hue=T_df.index)
plt.legend(title="");
plt.savefig("pics/payback-period.sorted.png")

fig, ax = plt.subplots(1, 1, figsize=(4,6), dpi=160)
ax.grid()
ax.set(title='Оптимальный срок окупаемости, \nсгруппированный по типам рекламных кампаний')

sns.boxplot(ax=ax, data=T_sorted_df, y='Time, (months)', hue="Campaign type");
plt.savefig("pics/payback-period.ct.png")

fig, ax = plt.subplots(1, 1, figsize=(4,6), dpi=160)
ax.grid()
ax.set(title='Оптимальный срок окупаемости, \nсгруппированный по рекламным сетям')

sns.boxplot(ax=ax, data=T_sorted_df, y='Time, (months)', hue="Media Source");
plt.savefig("pics/payback-period.ms.png")

from datetime import date, datetime, timedelta

df = registrations.dropna()
df = payments.join(df.set_index("account_id"), on="account_id").dropna()
df = df.join(persents.set_index("Payment_types"), on="Payment_types")
df['cost'] = df['real_cost'] * (100 - df['Share, %']) / 100
df['payment_date'] = pd.to_datetime(df['payment_date'], format="%Y-%m-%d %H:%M:%S").dt.date
df['created_date'] = pd.to_datetime(df['created_date'], format="%Y-%m-%d").dt.date
df = df[df['campaign'].str.contains("purchase")]
table = df[["account_id", "created_date", "payment_date", "cost", "campaign"]]
table

campaigns = list(table['campaign'].unique())
accounts = list(table['account_id'].unique())
payments = list(table['payment_date'].unique())

result_table = table.groupby(by=["campaign","account_id","created_date","payment_date"], as_index=False).sum()

dates = [7,14,30]
dates = [i for i in range(1, 32)]
ARPUs = np.zeros((len(campaigns), len(dates)))

for i, c in enumerate(campaigns):
    cond1 = result_table['campaign'] == c
    num_accounts = len(result_table[cond1])
    
    for acc in accounts:
        cond2 = result_table['account_id'] == acc
        cond = np.logical_and(cond1, cond2)
        
        tmp_table = result_table[cond]
        
        
        s = np.zeros(len(dates))
        for j,d in enumerate(dates):
            cond = tmp_table['payment_date'] <= tmp_table['created_date'] + timedelta(days=d)
            s[j] += tmp_table[cond]['cost'].sum()
#         print(s)
        ARPUs[i] += s 
    ARPUs[i] /= num_accounts
# print(ARPUs)

df_ARPU = pd.DataFrame(ARPUs.T, columns=campaigns)
# df_ARPU
df_ARPU['days'] = list(map(lambda x: f"{x} days" if x > 1 else f"{x} day", dates))
df_ARPU_melted = df_ARPU.melt('days', var_name='Campaign type', value_name='ARPU')
df_ARPU.iloc[[6, 13, 29]].to_csv('tables/arpu.csv')
df_ARPU.iloc[[6, 13, 29]].to_markdown('tables/arpu.md')

fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)
ax.grid()
# ax.set_xlim(1, 70)
ax.set(xlabel='Days', ylabel='Average revenue per user')
ax.set(title='ARPU, \nсгруппированный по рекламным компаниям')

# for i, c in enumerate(campaigns):
plt.xticks(rotation=70)
sns.lineplot(ax=ax, data=df_ARPU_melted, x='days', y='ARPU', hue='Campaign type');
plt.savefig("pics/arpu.png")

d = {
    ("Media_source_1",       "install",            "Brazil"): "BRA_MS1_install",
    ("Media_source_1",      "purchase",            "Brazil"): "BRA_MS1_purchase",
    ("Media_source_1",       "install",            "Russia"): "RUS_MS1_install",
    ("Media_source_1",      "purchase",            "Russia"): "RUS_MS1_purchase",
    ("Media_source_1",       "install",            "Ukrane"): "UKR_MS1_install",
    ("Media_source_1",      "purchase",            "Ukrane"): "UKR_MS1_purchase",
    ("Media_source_2",       "install",            "Brazil"): "BRA_MS2_install",
    ("Media_source_2",       "install",  "English_speaking"): "ENG_MS2_install",
}

CPI = list()
tmp_budget = budget.groupby(by=["media_source", "Campaign_type",'Target']).sum()
for (MS, Type, Target), c in d.items():
    cond = registrations['campaign'] == c
    num_installs = cond.sum()
    
    b = tmp_budget.loc[MS, Type, Target]['Spend, USD']
    
    CPI.append((MS, Type, Target, b / num_installs))

df = pd.DataFrame(CPI, columns=["Media Source", 'Campaign type', 'Target', "CPI"])
df = df.set_index(["Media Source", 'Campaign type', 'Target'])
df = df.sort_values(by='CPI')
df.to_csv('tables/cpi.sorted.csv')
df.to_markdown('tables/cpi.sorted.md')

fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)
ax.grid()
ax.set(title='Фактический CPI')
sns.barplot(ax=ax, data=df, x='CPI', hue=df.index)
plt.legend(title="");
plt.savefig("pics/cpi.sorted.png")

compare_budget = budget.dropna().rename(
    {
        'media_source':"Media Source", 
        'Campaign_type':'Campaign type',
    }, axis=1).set_index(["Media Source", 'Campaign type', 'Target'])[['Installs','Spend, USD']]
compare_df = df.join(compare_budget)
compare_df['Plan CPI'] = compare_df['Spend, USD'] / compare_df['Installs']
compare_df['Deviation'] = abs(compare_df['CPI'] - compare_df['Plan CPI'])
compare_df

fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)
ax.grid()
ax.set(title='Отклонения фактического CPI от бюджета')
sns.barplot(ax=ax, data=compare_df, y='Deviation', hue=df.index)
plt.legend(title="");
plt.savefig("pics/cpi.deviation.png")

	payment_date	Payment_types	real_cost	account_id
0	2021-01-01 00:00:56	Payment_system_5	9.650000	2907221
1	2021-01-01 00:01:48	Payment_system_5	0.955872	3228373
2	2021-01-01 00:03:42	Payment_system_5	3.026930	318552
3	2021-01-01 00:04:27	Payment_system_2	99.990000	3832817
4	2021-01-01 00:06:41	Payment_system_5	1.432550	7229767

	Payment_types	Share, %
0	Payment_system_1	20.7
1	Payment_system_2	15.0
2	Payment_system_3	58.5
3	Payment_system_4	0.0
4	Payment_system_5	24.0

	account_id	created_date	campaign
0	3842380.0	2021-01-01	BRA_MS1_install
1	3842381.0	2021-01-01	NaN
2	3842382.0	2021-01-01	NaN
3	3842383.0	2021-01-01	NaN
4	3842384.0	2021-01-01	BRA_MS1_install

	media_source	Campaign_type	Target	Plan, USD	Installs	Spend, USD	Deviation, USD
0	Media_source_1	install	Brazil	100	10460	99.12	0.88
1	Media_source_1	purchase	Brazil	400	1081	398.67	1.33
2	Media_source_1	install	Russia	200	11894	246.30	-46.30
3	Media_source_1	purchase	Russia	500	1441	616.77	-116.77
4	Media_source_1	install	Ukrane	100	6424	99.43	0.57
5	Media_source_1	purchase	Ukrane	500	242	348.13	151.87
6	Media_source_2	install	Brazil	100	152	81.15	18.85
7	Media_source_2	install	English_speaking	100	371	99.95	0.05
8	Total	NaN	NaN	2000	32065	1989.52	10.48

	payment_date	Payment_types	real_cost	account_id	Share, %	cost	created_date	campaign
330	2021-01-01 15:10:26	Payment_system_5	2.445710	3861811	24.0	1.858740	2021-01-07	RUS_MS1_purchase
339	2021-01-01 15:22:00	Payment_system_5	2.445710	3861811	24.0	1.858740	2021-01-07	RUS_MS1_purchase
827	2021-01-01 17:16:09	Payment_system_5	9.624570	3844647	24.0	7.314673	2021-01-01	BRA_MS1_install
837	2021-01-01 17:17:45	Payment_system_5	9.624570	3844647	24.0	7.314673	2021-01-01	BRA_MS1_install
1922	2021-01-02 01:08:32	Payment_system_5	0.955872	3845613	24.0	0.726463	2021-01-02	RUS_MS1_purchase
...	...	...	...	...	...	...	...	...
301124	2021-12-29 17:14:22	Payment_system_4	2.620000	3924840	0.0	2.620000	2021-01-29	RUS_MS1_install
301169	2021-12-29 18:55:43	Payment_system_4	0.100000	3924840	0.0	0.100000	2021-01-29	RUS_MS1_install
301243	2021-12-29 21:22:10	Payment_system_4	0.310000	3924840	0.0	0.310000	2021-01-29	RUS_MS1_install
301471	2021-12-30 08:15:58	Payment_system_4	0.380000	3924840	0.0	0.380000	2021-01-29	RUS_MS1_install
303332	2021-12-31 20:58:48	Payment_system_5	2.309120	3861811	24.0	1.754931	2021-01-07	RUS_MS1_purchase

Библиотеки¶

TODO¶

Данные¶

Payments¶

Persents¶

Registrations¶

Budget¶

Analisys¶

Оптимальный срок окупаемости¶

Определить наиболее успешные кейсы¶

Рекламные кампании¶

Тип рекламных кампаний¶

Рекламная сеть¶

Накопительный ARPU успешных рекламных кампаний¶

Фактический CPI (cost per install)¶

			CPI	Installs	Spend, USD	Plan CPI	Deviation
Media Source	Campaign type	Target
Media_source_1	install	Brazil	0.008648	10460	99.12	0.009476	0.000828
		Ukrane	0.014644	6424	99.43	0.015478	0.000834
		Russia	0.019472	11894	246.30	0.020708	0.001236
Media_source_2	install	English_speaking	0.245577	371	99.95	0.269407	0.023830
Media_source_1	purchase	Brazil	0.350018	1081	398.67	0.368797	0.018780
Media_source_1	purchase	Russia	0.390114	1441	616.77	0.428015	0.037901
Media_source_2	install	Brazil	0.533882	152	81.15	0.533882	0.000000
Media_source_1	purchase	Ukrane	1.338962	242	348.13	1.438554	0.099592