def new_report(min_app_version="5.1"):
sql = """
select ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
from sop_events.events_ios
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql_events=sql, min_app_version=min_app_version)
events = {}
while report.get_next_event():
if report.is_new_user():
# Анализируется последние событие каждого игрока
last_event_class = report.previous_event.__class__.__name__
# Оно добавляется в список последних событий
if last_event_class not in events.keys():
events[last_event_class] = 0
events[last_event_class] += 1
ordered_events = OrderedDict(sorted(events.items(), key=lambda t: t[1]))
# Рисовка гистограммы
plt.figure(figsize=(15, 6))
plt.barh(range(len(list(ordered_events.values()))),
list(ordered_events.values()))
plt.yticks(range(len(list(ordered_events.values()))),
list(ordered_events.keys()))
plt.savefig("last_event_histo.png")
plt.show()
def new_report(os=OS.ios,period_start=datetime.strptime("2018-08-20", "%Y-%m-%d").date(), period_end=str(datetime.now().date()),
days_since_install=28,
min_app_version="4.7", exact=False):
"""
Расчет накопительного ARPU и ROI по паблишерам и трекинговым ссылкам(источникам)
:param period_start: начало периода
:param period_end: конец периода
:param days_since_install: рассчитное кол-во дней после установки
:param min_app_version: мин версия приложения
:param exact: точное соответствие версии приложения
:return:
"""
# Приводим границы периода к виду datetime.date
if isinstance(period_start, str):
period_start = datetime.strptime(period_start, "%Y-%m-%d").date()
if isinstance(period_end, str):
period_end = datetime.strptime(period_end, "%Y-%m-%d").date()
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
and
(ios_ifv<>"" or ios_ifa <>"")
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql_events=sql,min_app_version=min_app_version,exact=exact,get_installs=False)
# ПАРАМЕТРЫ
parameters = []
# Запись пользовательских данных в общие
def flush_user_data():
while report.get_next_event():
if report.is_new_user():
pass
flush_user_data()
df = pd.DataFrame(index=[0],
columns=[])
df.fillna(0, inplace=True)
# Вывод
df.fillna("", inplace=True)
print(df.to_string())
writer = pd.ExcelWriter(" " + ".xlsx")
df.to_excel(excel_writer=writer)
writer.save()
def new_report(os=OS.ios,
pattern="ВНИМАНИЕ! ЗАДАЙТЕ ПАТТЕРНЫ ВРУЧНУЮ.",
days_since_install=28,
min_app_version="4.7",
min_install_version="6.5",
min_quest=None,
exact=False,
classic_retention=True):
"""
Расчет накопительного ARPU и ROI по паблишерам и трекинговым ссылкам(источникам)
:param period_start: начало периода
:param period_end: конец периода
:param days_since_install: рассчитное кол-во дней после установки
:param min_app_version: мин версия приложения
:param exact: точное соответствие версии приложения
:return:
"""
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
where
(
event_name = "CityEvent" and ( event_json like '{"Quest%'
or
event_json like "%BuyDecoration%"
or
event_json like "%InitGameState%"
or
event_json like "%BuyHealth%"
or
event_json like "%BuyPremiumCoin%Success%"
)
or event_name = "Match3Events" and (
event_json like "%BuyPremiumCoinM3%Success%"
or
event_json like "%CompleteTargets%"
or
event_json like "%FailGame%"
)
)
and
(ios_ifv<>"" or ios_ifa <>"")
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql_events=sql,
min_app_version=min_app_version,
exact=exact,
get_installs=True,
min_install_version=min_install_version)
# ПАТТЕРНЫ
# ВАЖНО! МНОЖЕСТВА ПОЛЬЗОВАТЕЛЕЙ, ПОДХОДЯЩИХ ПОД ПАТТЕРНЫ НЕ ДОЛЖНЫ ПЕРЕСЕКАТЬСЯ! (есть вывод по пропущенным)
'''
patterns_list = [
pattern_BuyDecoration(completed_once=False, min_completions=2),
pattern_BuyDecoration(completed_once=True),
pattern_BuyDecoration(completed_once=False, max_completions=0, same_day_pattern=False)
]
''' '''
patterns_list = [
pattern_BuyHealth(completed_once=False, min_completions=2),
pattern_BuyHealth(completed_once=True),
pattern_BuyHealth(completed_once=False, max_completions=0, same_day_pattern=False)
]
'''
patterns_list = [
pattern_WinRow(completed_once=False,
min_completions=2,
same_session_pattern=True),
pattern_WinRow(completed_once=True, same_session_pattern=True),
pattern_WinRow(completed_once=False,
max_completions=0,
same_day_pattern=True,
same_session_pattern=False)
]
# ПАРАМЕТРЫ
parameters = ["Retention Period", "Pattern", "Users"]
accumulating_parameters = [
str(i) + "d" for i in range(0, days_since_install + 1)
]
parameters += accumulating_parameters
# ПЕРИОДЫ РЕТЕНШЕНА
retention_periods = ["0", "1-2", "3-6", "7-13", "14-27", "28+", "Never"]
pattern_analysis = {}
for retention_period in retention_periods:
pattern_analysis[retention_period] = {}
for pattern_name in [pattern.name for pattern in patterns_list]:
pattern_analysis[retention_period][pattern_name] = dict.fromkeys(
["Users"] + accumulating_parameters, 0)
# функции ретеншена
def get_retention_period(d):
if d == 0:
return "0"
elif d < 3:
return "1-2"
elif d < 7:
return "3-6"
elif d < 14:
return "7-13"
elif d < 28:
return "14-27"
else:
return "28+"
def get_max_retention_day(user_entries_list,
classic_retention=classic_retention):
if classic_retention:
for i in range(1, len(user_entries_list)):
if user_entries_list[i] == 0:
return i - 1
else:
for i in range(len(user_entries_list) - 1, -1, -1):
if user_entries_list[i] > 0:
return i
def make_rolling_retention(user_entries_list):
for i in range(len(user_entries_list) - 1, 0, -1):
if user_entries_list[i - 1] < user_entries_list[i]:
user_entries_list[i - 1] = user_entries_list[i]
return user_entries_list
# Пользовательские параметры
user_session_events = []
user_day_events = []
previous_day_in_game = 0
if min_quest:
min_quest = CityEventsQuest(quest_id=min_quest,
action="Take",
datetime=None)
user_retention = [0] * (days_since_install + 1)
user_events = {}
for i in range(0, (days_since_install + 1)):
user_events[i] = []
# Запись пользовательских данных в общие
def flush_user_data():
user_found_pattern = False
# проверяем каждый паттерн
for pattern in patterns_list:
# постепенно собираем данные в один массив для паттерном, которые рассматриваются на всех событиях в куче
user_accumulating_events = []
# выполнение паттерна на отрезке ретеншена
user_pattern_completion = dict.fromkeys(retention_periods, 0)
# по каждому дню лайфтайма
for day in range(0, days_since_install + 1):
user_day_accumulated_events = []
for session_events in user_events[day]:
# паттерн по каждой сессии
if pattern.same_day_pattern and pattern.same_session_pattern:
user_pattern_completion[get_retention_period(
day)] += int(pattern.is_followed(session_events))
user_day_accumulated_events += session_events
user_accumulating_events += session_events
# паттер по дыннм с одного дня
if pattern.same_day_pattern and not pattern.same_session_pattern:
user_pattern_completion[get_retention_period(day)] += int(
pattern.is_followed(user_day_accumulated_events))
# паттерн по всем данным всех дней
if not pattern.same_day_pattern:
user_pattern_completion[get_retention_period(day)] += int(
pattern.is_followed(user_accumulating_events))
# суммарное выполнение паттерна
overall_pattern_completions = 0
# окончательный период выполнения паттерна
final_completion_period = None
for period in retention_periods:
# если паттерн на совершение события, то берем первый период его выполнения
if not final_completion_period and user_pattern_completion[
period] > 0 and (pattern.max_completions is not None
and pattern.max_completions > 0
or pattern.max_completions is None):
final_completion_period = period
overall_pattern_completions += user_pattern_completion[period]
# если паттерн выполнен меньше минимума или больше максимума раз - выходим, не подходит
if (pattern.max_completions and pattern.max_completions < overall_pattern_completions) or \
pattern.min_completions > overall_pattern_completions:
continue
# паттерн на не выполнения события
if not final_completion_period and pattern.max_completions == 0 and overall_pattern_completions == 0:
final_completion_period = "Never"
# если выбрали подходящий период, добавляем в общие данные показатели ретеншена и увеличиваем кол-во юзеров
if final_completion_period:
for i in range(0, get_max_retention_day(user_retention) + 1):
pattern_analysis[final_completion_period][pattern.name][
str(i) + "d"] += user_retention[i]
pattern_analysis[final_completion_period][
pattern.name]["Users"] += 1
user_found_pattern = True
# if pattern.name == "1)Построили одну декораций" and final_completion_period == "7-13":
# print(report.previous_user.user_id, user_retention)
# print(pattern_analysis[final_completion_period][pattern.name])
# for day in range(0, days_since_install + 1):
# print(user_events[day])
if not user_found_pattern:
Pattern.users_not_covered_with_pattern.add(
report.previous_user.user_id)
# ЦИКЛ ОБРАБОТКИ ДАННЫХ
while report.get_next_event():
if report.is_new_user():
if not classic_retention:
user_retention = make_rolling_retention(user_retention)
user_day_events.append(user_session_events)
user_events[day_in_game] = user_day_events
flush_user_data()
user_day_events = []
user_session_events = []
previous_day_in_game = 0
user_retention = [0] * (days_since_install + 1)
for day in range(0, days_since_install + 1):
user_events[day] = []
# определяем день в игре и увеличиваем ртеншен
day_in_game = report.get_time_since_install("day")
user_retention[day_in_game] = 1
# if report.current_user.user_id == "D35DACD8-F579-4A08-9E27-58CF7D193E1F":
# print(previous_day_in_game, day_in_game, user_retention)
if isinstance(report.current_event, CityEventsQuest) and min_quest:
if CityEventsQuest.compare_quests(report.current_event.quest,
min_quest) == -1:
continue
# если новая сессия, добавляем список событий сессии в общие события игрока
if isinstance(report.current_event, CityEventsInitGameState):
user_day_events.append(user_session_events)
# если новый день, добавляем данные по дню в общие данные
if day_in_game != previous_day_in_game:
user_events[previous_day_in_game] = user_day_events
previous_day_in_game = day_in_game
user_day_events = []
user_session_events = []
user_session_events.append(report.current_event)
# if report.current_user.user_id == "D35DACD8-F579-4A08-9E27-58CF7D193E1F":
# print(report.current_event.__class__)
# print(day_in_game, user_day_events)
# for day in user_events:
# print(user_events[day])
flush_user_data()
print("Инсталлов:", report.total_users)
print("Не прошли по паттернам:",
len(Pattern.users_not_covered_with_pattern))
# for retention_period in retention_periods:
# for pattern_n in [pattern.name for pattern in patterns_list]:
# print(retention_period, pattern_n, pattern_analysis[retention_period][pattern_n])
df = pd.DataFrame(index=[], columns=parameters)
for retention_period in pattern_analysis.keys():
for pattern in pattern_analysis[retention_period].keys():
if pattern_analysis[retention_period][pattern]["Users"] > 0:
for i in range(0, days_since_install + 1):
pattern_analysis[retention_period][pattern][
str(i) + "d"] = str(
round(
pattern_analysis[retention_period][pattern][
str(i) + "d"] * 100 /
pattern_analysis[retention_period][pattern]
["Users"], 1)) + "%"
df = df.append(
{
"Retention Period": retention_period,
"Pattern": pattern,
**pattern_analysis[retention_period][pattern]
},
ignore_index=True)
df.fillna(0, inplace=True)
df.sort_values(by=["Retention Period", "Pattern"],
ascending=True,
inplace=True)
# Вывод
print(df.to_string(index=False))
string = "ClassicRetention" if classic_retention else "RollingRetention"
writer = pd.ExcelWriter("Retention Pattern Hypothesis/" +
Pattern.filename + " " + string + ".xlsx")
df.to_excel(excel_writer=writer, index=False)
writer.save()
def new_report(os=OS.ios, days_left=7, app_version="6.0", exact=True):
'''
Глобальные конверсии по квестам и уровням в них и отвалы с монетами на момент отвала
!!!ВАЖНО!!! Порядок и положение туториалов меняется в зависимости от версии игры
:param days_left: кол-во дней, после которого человек отвалился
:param min_app_version: минимальная версия приложения
:param exact: точное соответствие версии
:return:
'''
last_event_date = Data.get_last_event_date(os)
sql = """
SELECT ios_ifv, ios_ifa, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
WHERE (event_name="Match3Events"
OR (event_name="CityEvent" and (
event_json like "%StartGame%"
or event_json like '{"Quest%'
or event_json like "%InitGameState%"
or event_json like "%BuyPremiumCoin%Success%"))
OR (event_name="Tutorial"))
order by ios_ifa,ios_ifv, event_datetime
"""
report = Report(sql_events=sql, user_status_check=True, min_app_version=app_version, exact=exact,
get_installs=True, max_install_version=app_version, min_install_version=app_version)
parameters = ["Retention", "Retention %", "Quest loss", "Level loss", # "Loss",
"Last event loss", "loss %", "FinishGame", "Coins"]
levels_list = get_levels_list(fail=True)
opened_game = 1
started_levels = set()
completed_levels = set()
finished_levels = set()
started_quests = set()
finished_quests = set()
started_tutorials = set()
finished_tutorials = set()
last_game_event = None
loss_premium_coins = {}
# в зависимости от версии порядок туториалов меняется
real_tutorial_order = []
funnel_data = {}
for lvl in levels_list:
funnel_data[lvl] = dict.fromkeys(parameters, 0)
def flush_user_info():
for lvl in started_levels:
funnel_data["Start " + lvl]["Retention"] += 1
for lvl in completed_levels:
funnel_data["Finish " + lvl]["Retention"] += 1
for lvl in finished_levels:
funnel_data["Finish " + lvl]["FinishGame"] += 1
for quest in started_quests:
funnel_data["Start " + quest]["Retention"] += 1
for quest in finished_quests:
funnel_data["Finish " + quest]["Retention"] += 1
for tutor in started_tutorials:
funnel_data["Start " + tutor]["Retention"] += 1
for tutor in finished_tutorials:
funnel_data["Finish " + tutor]["Retention"] += 1
# Отвалы
if get_timediff(report.previous_user.last_enter.date(), last_event_date,
measure="day") >= days_left:
last_game_point = None
# Отвал по последнему событию
if last_game_event.__class__ is Match3StartGame:
last_game_point = "Start " + last_game_event.level_num
elif last_game_event.__class__ is Match3FailGame:
last_game_point = "Fail " + last_game_event.level_num
elif last_game_event.__class__ in (Match3CompleteTargets, Match3FinishGame):
last_game_point = "Finish " + last_game_event.level_num
elif last_game_event.__class__ is CityEventsQuest:
if last_game_event.action == "Take":
last_game_point = "Start " + last_game_event.quest
elif last_game_event.action in ("Сomplete", "Complete"):
last_game_point = "Finish " + last_game_event.quest
elif last_game_event.__class__ is Tutorial:
if last_game_event.status == "Start":
last_game_point = "Start " + last_game_event.tutorial_code
elif last_game_event.status == "Finish":
last_game_point = "Finish " + last_game_event.tutorial_code
funnel_data[last_game_point]["Last event loss"] += 1
# РАСПРЕДЕЛЕНИЕ МОНЕТ УШЕДШИХ
if last_game_point not in loss_premium_coins.keys():
loss_premium_coins[last_game_point] = []
loss_premium_coins[last_game_point].append(report.previous_user.premium_coin)
# ОСТАЛОСЬ: ПОСЧИТАТЬ СРЕДНЕЕ И +- СИГМЫ
while report.get_next_event():
# следующий игрок, обновление данных таблицы, сброс персональных данных
if report.is_new_user():
flush_user_info()
opened_game += 1
started_levels = set()
completed_levels = set()
finished_levels = set()
started_quests = set()
finished_quests = set()
started_tutorials = set()
finished_tutorials = set()
if report.current_event.__class__ is Match3StartGame:
started_levels.add(report.current_event.level_num)
last_game_event = report.current_event
elif report.current_event.__class__ is Match3FailGame:
last_game_event = report.current_event
elif report.current_event.__class__ is Match3CompleteTargets:
completed_levels.add(report.current_event.level_num)
last_game_event = report.current_event
elif report.current_event.__class__ is Match3FinishGame:
finished_levels.add(report.current_event.level_num)
last_game_event = report.current_event
elif report.current_event.__class__ is CityEventsQuest:
if report.current_event.action == "Take":
started_quests.add(report.current_event.quest)
elif report.current_event.action in ("Сomplete", "Complete"):
finished_quests.add(report.current_event.quest)
last_game_event = report.current_event
quest_string = report.current_event.action + " " + report.current_event.quest
if quest_string not in real_tutorial_order:
real_tutorial_order.append(quest_string)
elif report.current_event.__class__ is Tutorial:
if report.current_event.status == "Start":
started_tutorials.add(report.current_event.tutorial_code)
elif report.current_event.status == "Finish":
finished_tutorials.add(report.current_event.tutorial_code)
last_game_event = report.current_event
'''
if report.current_event.status == "Start" and report.current_user.installed_app_version==app_version:
if "Start "+report.current_event.tutorial_code not in real_tutorial_order:
real_tutorial_order.append("Start "+report.current_event.tutorial_code)
real_tutorial_order.append("Finish "+report.current_event.tutorial_code)
'''
indent = " " if report.current_event.status == "Start" else " "
tutor_string = report.current_event.status + indent + report.current_event.tutorial_code
if tutor_string not in real_tutorial_order and report.current_user.installed_app_version == app_version:
real_tutorial_order.append(tutor_string)
flush_user_info()
print(levels_list)
print(real_tutorial_order)
levels_list = get_levels_list(fail=True, tutorial_order=real_tutorial_order)
print(levels_list)
df = pd.DataFrame(index=levels_list,
columns=parameters)
for lvl in levels_list:
print(lvl)
for param in parameters:
df.at[lvl, param] = funnel_data[lvl][param]
# print(lvl,df.at[lvl, "Retention"],funnel_data[lvl]["Retention"])
df = df.fillna(0)
previous_quest_value = report.total_users
sum_loss = sum(df["Last event loss"])
for level in df.index.values:
if df.at[level, "Retention"]:
df.loc[level, "Retention %"] = str(round(df.at[level, "Retention"] * 100 / report.total_users, 1)) + "%"
diff = round((previous_quest_value - df.at[level, "Retention"]) * 100 / report.total_users, 1)
# Квесты и туториалы
if len(level[8:]) > 4:
df.loc[level, "Quest loss"] = "-" + str(diff) + "%"
previous_quest_value = df.at[level, "Retention"]
# Уровни
else:
if "Fail" not in level:
df.loc[level, "Level loss"] = "-" + str(diff) + "%"
if "Finish" in level and df.at[level, "Retention"]:
df.loc[level, "FinishGame"] = str(
round(df.at[level, "FinishGame"] * 100 / df.at[level, "Retention"], 1)) + "%"
if df.at[level, "Last event loss"]:
df.loc[level, "loss %"] = str(round(df.at[level, "Last event loss"] * 100 / sum_loss, 1)) + "%"
if level in loss_premium_coins.keys():
df.loc[level, "Coins"] = str(
int((sum(loss_premium_coins[level]) / len(loss_premium_coins[level])))) + " +-" + str(
int(sigma(loss_premium_coins[level])))
df["Level loss"].replace(0, "", inplace=True)
df["Quest loss"].replace(0, "", inplace=True)
df["loss %"].replace(0, "", inplace=True)
df["Retention"].replace(0, "", inplace=True)
df["Retention %"].replace(0, "", inplace=True)
df["FinishGame"].replace(0, "", inplace=True)
df["Last event loss"].replace(0, "", inplace=True)
df["Coins"].replace(0, "", inplace=True)
print("Loss after", days_left, "days.")
print("Users opened game:", opened_game)
print(df.to_string())
for level in df.index.values:
if "Start" in level:
value = level[7:]
new_level = "Start " + value
df = df.rename(index={level: new_level})
writer = pd.ExcelWriter("QuestsFunnel " + app_version + ".xlsx")
df.to_excel(excel_writer=writer, sheet_name=app_version)
writer.save()
event_json like "%BuyPremiumCoin%Success%"
or
event_json like "%InitGameState%"
)
)
and
(ios_ifv<>"" or ios_ifa <>"")
)
order by ios_ifa, ios_ifv, event_datetime
"""
# Формируем отчет, учитывая установки в данный период/данной версии с нужными параметрами для кластеризации
report = Report(os=OS.ios, sql_events=sql, min_app_version=min_install_version, exact=exact,
get_installs=True,
installs_parameters=["publisher_name", "tracker_name", "install_datetime", "app_version_name"],
installs_period=[period_start, period_end],
min_install_version=min_install_version, max_install_version=max_install_version
)
# параметры
parameters = ["Installs", "Paying"]
accumulating_parameters = [str(i) + "d" for i in range(0, days_since_install + 1)]
parameters += accumulating_parameters
sources = {}
transactions = {}
dau = {}
user_dau = {}
for dt in rrule(DAILY, dtstart=period_start, until=(period_end + timedelta(days=days_since_install))):
dau[dt.date()] = {"Users": 0, "Revenue": 0}
user_dau[dt.date()] = None
def new_report(short_info=True,
first_session=False,
user_status=True,
detailed_events=list(),
install_app_version="5.5",
exact_app_version=True):
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql,
user_status_check=True,
min_app_version=install_app_version,
exact=exact_app_version)
# ФАЙЛЫ ВЫВОДА
output_file = None
output_file_returned = None
output_file_not_returned = None
if not first_session:
output_file = open("UserSessions.txt", "w")
else:
output_file_returned = open("UserSessions.txt", "w")
output_file_not_returned = open("UserSessions.txt", "w")
# ПАРАМЕТРЫ
skip_user_events = None
returned_user = False
output_string = ""
events_string = ""
first_session_status = ""
while report.get_next_event():
# НОВЫЙ ПОЛЬЗОВАТЕЛЬ
if report.is_new_user():
# Создаем текст сессий на вывод
output_string += "\n\nNew user: "******"\n"
if report.previous_user.user_id in test_devices_ios:
output_string += "TESTER\n"
output_string += events_string
if user_status:
if first_session:
output_string += first_session_status
else:
output_string += report.previous_user.get_status()
# Вывод в файлы
if report.previous_user.first_enter.date() >= datetime.date(
2018, 6, 25):
if not first_session:
output_file.write(output_string)
elif first_session and returned_user:
output_file_returned.write(output_string)
elif first_session and not returned_user:
output_file_not_returned.write(output_string)
# Обнуление параметров
returned_user = False
output_string = ""
events_string = ""
first_session_status = ""
# ПЕРВЫЕ СЕССИИ
if first_session:
# Вернулся ли пользователь
if report.get_time_since_install("day") > 0:
returned_user = True
# Первая ли сессия
if report.current_user.first_session:
first_session_status = report.current_user.get_status()
# Добавление текста события
if (first_session
and report.current_user.first_session) or not first_session:
if report.current_event.__class__ is CityEventsInitGameState:
events_string += "---\n"
if short_info and (not detailed_events or detailed_events
and report.current_event.__class__.__name__
not in detailed_events):
events_string += str(
report.current_event.datetime
) + " " + report.current_event.to_short_string() + "\n"
else:
events_string += str(
report.current_event.datetime
) + " " + report.current_event.to_string() + "\n"
def new_report(game_point="0030", min_app_version="5.3", exact=True):
'''
Подробный разбор событий, предшествующих отвалу
:param game_point: Номер квеста или уровня
:param min_app_version: мин версия приложения
:param exact: должна ли версия приложения обязательно совпадать с минимальной
:return:
'''
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql_events=sql,
min_app_version=min_app_version,
exact=exact)
df = pd.DataFrame(index=[0], columns=["Event", "Users", "Users %"])
df.fillna(0, inplace=True)
# ПАРАМЕТРЫ
previous_quest = get_last_loc_quest(game_point)
loc = previous_quest[:5]
quest = previous_quest[5:]
levels = loc_quest_level[loc][quest]
orders = list(itertools.permutations(levels))
user_orders = None
user_events = []
df_orders = {}
list_orders = {}
for order in orders:
df_orders[order] = pd.DataFrame(columns=["Event", "Users", "Users %"])
df_orders[order].fillna(0, inplace=True)
list_orders[order] = []
in_area = False
while report.get_next_event():
if report.is_new_user():
in_area = False
if user_events:
user_orders = corresponding_orders(user_events, orders)
user_events = []
if issubclass(report.current_event.__class__, Match3Event) and in_area:
user_events.append(report.current_event.__class__.__name__ + " " +
str(report.current_event.level_num))
else:
if report.current_event.__class__ is CityEventsQuest and report.current_event.quest_id == previous_quest:
print(report.current_event.to_string())
if report.current_event.action == "Take":
in_area = True
# \u0421omplete, ага, жесть
elif report.current_event.action in ("Сomplete", "Complete"):
in_area = False
else:
print(report.current_event.action,
report.current_event.action.__class__)
if in_area:
user_events.append(report.current_event.__class__.__name__)
# Вывод
df.fillna("", inplace=True)
print(df.to_string())
writer = pd.ExcelWriter("DetailedFunnel " + min_app_version + "+.xlsx")
df.to_excel(excel_writer=writer)
writer.save()
def new_report(start=1,
quantity=100,
days_left=None,
days_max=3,
install_app_version="6.5",
exact=False,
report_app_version=None):
if not report_app_version:
report_app_version = install_app_version
if not days_left:
if days_max >= 28:
days_left = 14
elif days_max >= 14:
days_left = 7
elif days_max >= 7:
days_left = 3
else:
days_left = 999
last_event_date = Data.get_last_event_date()
days_max = int(days_max)
sql = """
SELECT ios_ifa,ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
WHERE
(event_name = "Match3Events"
or (event_name = "CityEvent"
and (event_json like "%StartGame%"
)
)
)
and
(ios_ifa <>"" or ios_ifv <>"")
order by ios_ifv, event_datetime,ios_ifv
"""
max_install_version = None if not exact else install_app_version
report = Report(sql_events=sql,
user_status_check=True,
exact=exact,
min_app_version=install_app_version,
get_installs=True,
min_install_version=install_app_version,
max_install_version=max_install_version)
left_par = "Left " + str(days_left) + "+ days"
levels = get_level_names(start, quantity)
df = pd.DataFrame(index=levels,
columns=[
"Started", "Finished", "Start Convertion",
"Clean Start", "Clean Finish", left_par,
"Difficulty", "Clean Difficulty", "Attempts",
"Clean Attempts", "Purchases Sum", "Purchases",
"First purchase", "ARPU", "Dust", "Dust on hands"
])
df = df.fillna(0)
def start_finish():
if report.current_event.__class__ is Match3StartGame:
started_levels.add(current_level)
elif report.current_event.__class__ in (Match3FinishGame,
Match3CompleteTargets):
finished_levels.add(current_level)
def clean_start_finish():
if report.current_event.__class__ is Match3StartGame:
if report.current_app_version >= report_app_version:
if report.current_event.start_bonuses.first or \
report.current_event.start_bonuses.second or \
report.current_event.start_bonuses.third:
return False
else:
df.loc[current_level, "Clean Start"] += 1
return True
elif report.current_event.__class__ is Match3CompleteTargets:
# проверка на чистую игру на этой версии приложения
if report.current_app_version >= report_app_version and clean_start:
# если не использовал молоток, то чисто
if report.current_event.ingame_bonuses == 0:
df.loc[current_level, "Clean Finish"] += 1
return True
else:
# если использовал - отбираем чистый старт
df.loc[current_level, "Clean Start"] -= 1
return False
return clean_start
def difficulty():
if report.current_event.__class__ is Match3StartGame:
# в сложнотсти считаем все старты уровней
df.loc[current_level, "Difficulty"] += 1
def attempts():
if report.current_event.__class__ is Match3CompleteTargets:
# количество попыток пройти уровень
if user_attempts[current_level] == 0:
user_attempts[current_level] = 1
level_attempts[current_level].append(user_attempts[current_level])
elif report.current_event.__class__ is Match3FailGame:
# Увеличиваем счетчик попыток
user_attempts[current_level] += 1
def clean_attempts():
if report.current_event.__class__ is Match3CompleteTargets:
if report.current_app_version >= report_app_version:
if clean_start and report.current_event.ingame_bonuses == 0:
# количество чистых попыток пройти уровень
if user_clean_attempts[current_level] == 0:
user_clean_attempts[current_level] = 1
level_clean_attempts[current_level].append(
user_clean_attempts[current_level])
elif report.current_event.__class__ is Match3FailGame:
if report.current_app_version >= report_app_version:
if clean_start and report.current_event.ingame_bonuses == 0:
user_clean_attempts[current_level] += 1
def purchases():
if report.current_event.__class__ is Match3BuyPremiumCoin and report.current_event.status == "Success":
df.loc[current_level, "Purchases"] += 1
df.loc[current_level,
"Purchases Sum"] += get_price(report.current_event.purchase,
money="rub")
# Считаем первые покупки игроков
if first_purchase:
df.loc[current_level, "First purchase"] += 1
return False
return first_purchase
def dust():
if report.current_event.__class__ is Match3FinishGame:
# Добавляем собранную на уровне пыль
collected_dust[current_level].append(
int(report.current_event.game_currency_count))
# Добавляем пыль на руках у игроков
user_dust[current_level].append(report.current_user.game_coin)
# Стартовые параметры
level_attempts = dict.fromkeys(levels)
level_clean_attempts = dict.fromkeys(levels)
user_attempts = dict.fromkeys(levels, 0)
user_clean_attempts = dict.fromkeys(levels, 0)
collected_dust = dict.fromkeys(levels)
user_dust = dict.fromkeys(levels)
user_clean_start = dict.fromkeys(levels)
for level in levels:
level_attempts[level] = []
level_clean_attempts[level] = []
collected_dust[level] = []
user_dust[level] = []
user_clean_start[level] = []
started_levels = set()
finished_levels = set()
first_purchase = True
current_level = None
clean_start = True
while report.get_next_event():
# Событие М3 - может смениться уровень
if report.current_event.__class__ in (Match3BuyPremiumCoin,
Match3CompleteTargets,
Match3FinishGame,
Match3StartGame, Match3FailGame):
current_level = report.current_event.level_num
# Если уровень из списка рассматрвиаемых
if current_level in levels:
# if report.get_timediff(measure="day")>=1 or report.is_new_user():
# print(report.current_user.user_id, report.get_time_since_install(), report.get_time_since_last_enter(), report.current_user.entries[-1], report.is_new_user() )
# Нвоый пользователь
if (report.is_new_user() and not report.previous_user.is_skipped()
) or (report.get_time_since_install() > days_max):
# print("flush",report.is_new_user(), (report.get_time_since_install() > days_max))
# если вышло время, то работаем с текущим пользователем
if report.is_new_user():
user = report.previous_user
user_str = "previous"
else:
user = report.current_user
user_str = "current"
for level in started_levels:
df.loc[level, "Started"] += 1
for level in finished_levels:
df.loc[level, "Finished"] += 1
# Проверка на отвал
# При отсутствии максимального дня в игре (для среза выборки) берется макс дата из базы данных.
if (not days_max and get_timediff(user.last_enter.date(), last_event_date,
measure="day") >= days_left) or \
(days_max and get_timediff(user.last_enter.date(),
user.install_date + timedelta(
days=days_max), measure="day") > days_left):
if started_levels:
for level in list(started_levels - finished_levels):
df.loc[level, left_par] += 1
# сброс личных параметров
started_levels = set()
finished_levels = set()
user_attempts = dict.fromkeys(levels, 0)
user_clean_attempts = dict.fromkeys(levels, 0)
first_purchase = True
if not report.is_new_user() and report.get_time_since_install(
user="******") > days_max:
report.skip_current_user()
continue
start_finish()
clean_start = clean_start_finish()
difficulty()
attempts()
clean_attempts()
first_purchase = purchases()
dust()
# Финальные рассчеты
df["Start Convertion"] = round(df["Started"] * 100 / report.total_users, 1)
df["Difficulty"] = round(100 - df["Finished"] * 100 / df["Difficulty"], 1)
df["Clean Difficulty"] = round(
100 - df["Clean Finish"] * 100 / df["Clean Start"], 1)
df["ARPU"] = round(df["Purchases Sum"] / df["Started"], 1)
for level in levels:
# проверка на выбросы в попытках
if len(level_attempts[level]) > 0:
level_attempts[level] = outliers_iqr(level_attempts[level],
level + " attempts",
multiplier=10)
df.loc[level, "Attempts"] = round(
1 - 1 /
(sum(level_attempts[level]) / len(level_attempts[level])),
3) * 100
if len(level_clean_attempts[level]) > 0:
level_clean_attempts[level] = outliers_iqr(
level_clean_attempts[level],
level + " clean attempts",
multiplier=4)
df.loc[level, "Clean Attempts"] = round(
1 - 1 / (sum(level_clean_attempts[level]) /
len(level_clean_attempts[level])), 3) * 100
# Проверка на выбросы в значениях пыли
if len(collected_dust[level]) > 0:
if len(collected_dust[level]) > 10:
collected_dust[level] = outliers_iqr(collected_dust[level],
level + " collected dust",
multiplier=5)
df.loc[level, "Dust"] = round(
sum(collected_dust[level]) / float(len(collected_dust[level])),
1)
if len(user_dust[level]) > 0:
if len(user_dust[level]) > 10:
user_dust[level] = outliers_iqr(data_list=user_dust[level],
where=level + " user dust",
max_outliers=False,
min_outliers=True,
multiplier=15)
user_dust[level] = outliers_iqr(user_dust[level],
level + " user dust",
multiplier=5)
df.loc[level, "Dust on hands"] = round(
sum(user_dust[level]) / float(len(user_dust[level])), 1)
# Печать
print("Total users:", report.total_users)
print("Testers:", len(report.testers))
print(df.to_string())
writer = pd.ExcelWriter("Levels " + str(start) + "-" +
str(start + quantity - 1) + " " +
report_app_version + " " + str(days_max) + "days" +
".xlsx")
df.to_excel(excel_writer=writer)
writer.save()
del report
def new_report(max_level=121, days=[2, 3], min_app_version=["6.0","6.3"], exact=True, users_last_day=False):
days = list(map(int, days))
progress = {}
users=dict.fromkeys(min_app_version,0)
# с 1 (0, чтобы точнее были деления) по n (включительно) + 3 финишера (15?)
levels = list(range(0, max_level + 2))
for version in min_app_version:
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
where event_name = "Match3Events" and ( event_json like "%CompleteTargets%" or event_json like "%FinishGame%")
order by ios_ifa, ios_ifv, event_datetime
"""
report = Report(sql_events=sql, min_app_version=version, exact=exact)
# ПАРАМЕТРЫ
finished_levels = set()
previous_day = None
progress[version]={}
for day in days:
progress[version][day] = [0] * len(levels)
while report.get_next_event():
day_in_game = report.get_time_since_install(measure="day")
if report.is_new_user() or (not users_last_day and (previous_day and day_in_game != previous_day)):
# НЕ КАЖДОЕ СОБЫТИЕ А ТОЛЬКО КОГДА МЕНЯЕТСЯ ДЕНЬ, либо только когда меняется пользователь, если только для тех, у кого это последний день
if previous_day in days and finished_levels:
max_user_level = int(max(finished_levels))
# учитываем финишеры
if max_user_level < 9000:
progress[version][previous_day][min(max_user_level, max_level)] += 1
else:
progress[version][previous_day][max_level + 1] += 1
if report.is_new_user():
finished_levels = set()
previous_day = None
finished_levels.add(report.current_event.level_num)
previous_day = day_in_game
print(version,"Total users:", report.total_users)
users[version]=report.total_users
del report
# Рисовка гистограммы
max_on_screen = 4
columns = min(2,len(days))
#columns=2
lines_wanted = 2
plots_left = len(days)
screens = int(math.ceil(plots_left / max_on_screen))
for i in range(screens):
lines = lines_wanted if plots_left >= max_on_screen else math.ceil(plots_left / columns)
fig, axs = plt.subplots(lines, columns, figsize=(18, 8), facecolor='w', edgecolor='k')
fig.subplots_adjust(hspace=.5, wspace=.2)
if columns > 1:
axs = axs.ravel()
for subplot, day in enumerate(days[i * max_on_screen: min((i + 1) * max_on_screen, len(days))]):
if columns>1:
ax=axs[subplot]
else:
ax = axs
for version in min_app_version:
ax.bar(levels, progress[version][day], alpha=0.5, label=version)
ax.legend()
ax.set_xticks(list(chain(range(0, max_level + 1, 10), [max_level + 2])))
ax.set_xticklabels(list(range(0, max_level + 1, 10)) + ["X"])
ax.set_title("day " + str(day))
plt.savefig("progress_histo_absolute" + str(min_app_version) + " (" + str(i) + ").png")
plt.show()
plots_left -= columns * lines
# Рисовка гистограммы
max_on_screen = 4
columns = min(2,len(days))
#columns = 2
lines_wanted = 2
plots_left = len(days)
screens = int(math.ceil(plots_left / max_on_screen))
for i in range(screens):
lines = lines_wanted if plots_left >= max_on_screen else math.ceil(plots_left / columns)
fig, axs = plt.subplots(lines, columns, figsize=(18, 8), facecolor='w', edgecolor='k')
fig.subplots_adjust(hspace=.5, wspace=.2)
if columns>1:
axs = axs.ravel()
for subplot, day in enumerate(days[i * max_on_screen: min((i + 1) * max_on_screen, len(days))]):
if columns > 1:
ax = axs[subplot]
else:
ax = axs
for version in min_app_version:
ax.bar(levels, [round(user_count*100/users[version],1) for user_count in progress[version][day]], alpha=0.5, label=version)
ax.legend()
ax.set_xticks(list(chain(range(0, max_level + 1, 10), [max_level + 2])))
ax.set_xticklabels(list(range(0, max_level + 1, 10)) + ["X"])
ax.set_title("day " + str(day))
plt.savefig("progress_histo " + str(min_app_version) + " (" + str(i) + ").png")
plt.show()
plots_left -= columns * lines
def new_report(min_app_version="6.3", exact=False):
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
where
(
(event_name = "Match3Events" and ( event_json like "%FinishGame%"))
or
(event_name = "CityEvent" and
(event_json like "%BuyDust%" or
event_json like "%UpdateBuilding%" or
event_json like "%BuyDecoration%" or
event_json like "%Quest%" or
event_json like "%StartGame%" or
event_json like "%Button%Dust%" or
event_json like "%Button%Quest%" or
event_json like "%InitGameState%" or
event_json like "%Restore%"
)
)
)
and
(ios_ifv<>"" or ios_ifa <>"")
order by ios_ifa, event_datetime , ios_ifv
"""
report = Report(sql_events=sql,
min_app_version=min_app_version,
exact=exact,
user_status_check=True)
df = pd.DataFrame(index=[0], columns=[])
df.fillna(0, inplace=True)
# ПАРАМЕТРЫ
parameters = [
"Дошли до квеста", "Потратили пыль", "Собрано пыли", "Куплено пыли",
"Потрачено пыли", "Средняя пыль на руках (не плат)",
"Средняя пыль на руках (плат)", "Собрано пыли в среднем",
"Средняя трата пыли", "Популярные объекты"
]
quests_list = get_locquests_list()
df = pd.DataFrame(index=quests_list, columns=parameters)
# df.fillna(0, inplace=True)
#buy_decor и restore отвечают за факт покупки декорации/восстановления мусора в предыдущих событиях, которые не были обработаны. после следующего обновления количества валюты, они будут обнулены.
#в дальнейшнем систему нужно поменять, т.к. должны отправляться стоимости после покупки и не нужно будет ждать обновления баланса валюты
current_quest = None
previous_quest = None
buy_decor = False
coins_decor = 0
buy_decor_quest = None
restore = False
coins_restore = 0
restore_quest = None
#Пользовательские параметры
quests = {}
user_started_quests = set()
user_spent_on_quest = dict.fromkeys(quests_list, 0)
user_restore_on_quest = dict.fromkeys(quests_list, 0)
user_spends_dust = False
user_got_dust_on_quest = 0
user_dust_on_hands = dict.fromkeys(quests_list, 0)
for quest in quests_list:
quests[quest] = dict.fromkeys(parameters, 0)
quests[quest]["Популярные объекты"] = {}
quests[quest]["Средняя трата пыли"] = []
quests[quest]["Средняя пыль на руках (не плат)"] = []
quests[quest]["Средняя пыль на руках (плат)"] = []
quests[quest]["Собрано пыли в среднем"] = []
#Запись пользовательских данных в общие
def flush_user_data():
for quest in user_started_quests:
quests[quest]["Дошли до квеста"] += 1
#Платящий или не платящий
if user_spends_dust:
quests[quest]["Средняя пыль на руках (плат)"].append(
user_dust_on_hands[quest])
else:
quests[quest]["Средняя пыль на руках (не плат)"].append(
user_dust_on_hands[quest])
#if quest > "loc01q07":
#print(quest, report.previous_user.user_id, user_dust_on_hands[quest])
#траты пыли
for quest, spent in user_spent_on_quest.items():
if spent > 0:
quests[quest]["Потрачено пыли"] += spent
quests[quest]["Средняя трата пыли"].append(spent)
if spent > 0 or user_restore_on_quest[quest] > 0:
quests[quest]["Потратили пыль"] += 1
while report.get_next_event():
if report.is_new_user():
#Обнуляем пользовательские данные, добавляем их в общие
flush_user_data()
user_started_quests = set()
user_spent_on_quest = dict.fromkeys(quests, 0)
user_restore_on_quest = dict.fromkeys(quests_list, 0)
user_spends_dust = False
previous_quest = None
current_quest = "loc00q00"
buy_decor = False
coins_decor = 0
buy_decor_quest = None
restore = False
coins_restore = 0
restore_quest = None
#Определеяем текущий квест пользователя.
# ИЗ-ЗА ПРОЕБА, ПРИ UPDATEBUILDING КВЕСТ НЕ ОТСЫЛАЕТСЯ, ПРОСТО НЕ БУДЕМ МЕНЯТЬ
if report.current_event.__class__.__name__ in {
CityEventsBuyDust.__name__, CityEventsBuyDecoration.__name__,
CityEventsQuest.__name__, CityEventsButton.__name__
}:
previous_quest = current_quest
current_quest = report.current_event.quest
elif report.current_event.__class__.__name__ in {
Match3FinishGame.__name__, CityEventsStartGame.__name__
}:
previous_quest = current_quest
current_quest = get_locquest(report.current_event.level_num)
#Добавляем новый квест и параметры начала квеста
if current_quest and current_quest not in user_started_quests:
if previous_quest:
quests[previous_quest]["Собрано пыли в среднем"].append(
user_got_dust_on_quest)
user_got_dust_on_quest = 0
user_dust_on_hands[current_quest] = report.current_user.game_coin
user_started_quests.add(current_quest)
# Покупка пыли
if report.current_event.__class__ is CityEventsBuyDust:
bought = report.current_user.game_coin - report.current_user.previous_game_coin
quests[current_quest]["Куплено пыли"] += bought
#Обновление значения пыли после событий покупки/восстановления, определение потраченой суммы (добавить restore, когда обновят)
if (buy_decor or restore) and report.current_event.__class__ in (
CityEventsBuyDecoration, CityEventsButton, CityEventsBuyDust,
CityEventsStartGame, CityEventsInitGameState):
spent = 0
if not report.is_new_user():
if buy_decor:
spent = coins_decor - report.current_user.game_coin
coins_decor = 0
elif restore:
spent = coins_restore - report.current_user.game_coin
coins_restore = 0
if spent > 0:
if buy_decor:
if buy_decor_quest != "loc01q04":
user_spends_dust = True
user_spent_on_quest[buy_decor_quest] += spent
if purchase not in quests[buy_decor_quest][
"Популярные объекты"].keys():
quests[buy_decor_quest]["Популярные объекты"][
purchase] = 0
quant = 1
if purchase == "road":
quant = int(spent / 10)
quests[buy_decor_quest]["Популярные объекты"][
purchase] += quant
buy_decor = False
elif restore:
user_spends_dust = True
user_spent_on_quest[restore_quest] += spent
restore = False
if report.current_event.__class__ is CityEventsBuyDecoration and report.current_event.status == "Success":
buy_decor = True
coins_decor = report.current_user.game_coin
buy_decor_quest = current_quest
purchase = report.current_event.purchase
# обрабатываем восстановление мусора
if report.current_event.__class__.__name__ == CityEventsRestore.__name__:
'''or (
not report.is_new_user() and (
report.current_user.game_coin < report.current_user.previous_game_coin) and
report.current_app_version == "6.0" and not buy_decor):'''
user_spends_dust = True
if "Restore" not in quests[current_quest][
"Популярные объекты"].keys():
quests[current_quest]["Популярные объекты"]["Restore"] = 0
quests[current_quest]["Популярные объекты"]["Restore"] += 1
user_restore_on_quest[current_quest] = 1
if not buy_decor:
restore = True
coins_restore = report.current_user.game_coin
restore_quest = current_quest
# конец уроня, зарабатывание пыли
if report.current_event.__class__ is Match3FinishGame:
user_got_dust_on_quest += report.current_event.game_currency_count
quests[current_quest][
"Собрано пыли"] += report.current_event.game_currency_count
flush_user_data()
# Перенос данных в таблицу
for quest in quests.keys():
if quest == "loc02q10":
print(quest, quests[quest]["Средняя пыль на руках (плат)"])
if quest == "loc02q11":
print(quest, quests[quest]["Средняя пыль на руках (плат)"])
quests[quest]["Средняя трата пыли"] = array_average(
quests[quest]["Средняя трата пыли"],
name=quest + " " + "Средняя трата пыли",
multiplier=5)
quests[quest]["Средняя пыль на руках (плат)"] = array_average(
quests[quest]["Средняя пыль на руках (плат)"],
name=quest + " " + "Средняя пыль на руках (плат)",
multiplier=4)
quests[quest]["Средняя пыль на руках (не плат)"] = array_average(
quests[quest]["Средняя пыль на руках (не плат)"],
name=quest + " " + "Средняя пыль на руках (не плат)",
multiplier=4)
quests[quest]["Собрано пыли в среднем"] = array_average(
quests[quest]["Собрано пыли в среднем"],
name=quest + " " + "Собрано пыли в среднем",
multiplier=4)
top_objects = ""
sorted_list = list(
sorted(quests[quest]["Популярные объекты"].items(),
reverse=True,
key=lambda x: x[1]))
if len(quests[quest]["Популярные объекты"].keys()) > 0:
for index, (purchase, p_count) in enumerate(sorted_list):
if index < 5:
top_objects += purchase + ": " + str(p_count)
if index < min(
4,
len(quests[quest]["Популярные объекты"].keys()) - 1):
top_objects += ", "
quests[quest]["Популярные объекты"] = top_objects
for param in parameters:
if quests[quest][param]:
df.at[quest, param] = quests[quest][param]
# Вывод
df.fillna(0, inplace=True)
pd.options.display.max_colwidth = 150
print(df.to_string())
s = "" if exact else "+"
writer = pd.ExcelWriter("DustDynamics " + min_app_version + s + ".xlsx")
df.to_excel(excel_writer=writer)
writer.save()
#Рисуем гистограммы
plt.figure(1, figsize=(18, 8))
ax = plt.subplot(111)
ax.bar(range(len(quests_list)),
df["Средняя пыль на руках (плат)"],
alpha=0.5,
label="Средняя пыль на руках в начале")
ax.bar(range(len(quests_list)),
df["Собрано пыли в среднем"],
alpha=0.5,
label="Собрано пыли в среднем")
ax.bar(range(len(quests_list)),
df["Средняя трата пыли"],
alpha=0.5,
label="Средняя трата пыли")
ax.set_xticks(range(len(quests_list)))
ax.set_xticklabels(quests_list)
for tick in ax.get_xticklabels():
tick.set_rotation(90)
ax.legend()
plt.title("Тратящие пыль")
plt.savefig("Dust Dynamics плат" + min_app_version + ".png")
plt.show()
plt.figure(2, figsize=(18, 8))
ax = plt.subplot(111)
ax.bar(range(len(quests_list)),
df["Средняя пыль на руках (не плат)"],
alpha=0.5,
label="Средняя пыль на руках в начале")
ax.bar(range(len(quests_list)),
df["Собрано пыли в среднем"],
alpha=0.5,
label="Собрано пыли в среднем")
ax.set_xticks(range(len(quests_list)))
ax.set_xticklabels(quests_list)
for tick in ax.get_xticklabels():
tick.set_rotation(90)
ax.legend()
plt.title("Не тратящие пыль")
plt.savefig("Dust Dynamics не плат" + min_app_version + ".png")
plt.show()
def new_report(start=1, quantity=121, min_app_version="5.3",exact=True):
sql = """
SELECT ios_ifa,ios_ifv, event_name, event_json, event_datetime, app_version_name
FROM sop_events.events_ios
WHERE event_name = "Match3Events"
order by ios_ifa,ios_ifv, event_datetime
"""
report = Report(sql_events=sql, min_app_version=min_app_version, exact=exact)
levels = get_level_names(start, quantity)
df = pd.DataFrame(index=levels,
columns=["Hammer", "First", "Second", "Third", "Buy steps"])
df = df.fillna(0)
# Параметры
user_hammer = 3
user_first = 3
user_second = 3
user_third = 3
while report.get_next_event():
if report.is_new_user():
# Бесплатные бонусы
user_hammer = 3
user_first = 3
user_second = 3
user_third = 3
current_level = report.current_event.level_num
if current_level in levels and current_level:
if report.current_event.__class__ is Match3StartGame:
# На 11 уровне дается первый бонус, он бесплатен при переигрывании
if current_level != "0011":
if user_first <= 0:
df.loc[current_level, "First"] += int(report.current_event.start_bonuses.first)
else:
user_first -= int(report.current_event.start_bonuses.first)
# На 16 уровне дается второй бонус, он бесплатен при переигрывании
if current_level != "0016":
if user_second <= 0:
df.loc[current_level, "Second"] += int(report.current_event.start_bonuses.second)
else:
user_second -= int(report.current_event.start_bonuses.second)
# На 19 уровне дается третий бонус, он бесплатен при переигрывании
if current_level != "0019":
if user_third <= 0:
df.loc[current_level, "Third"] += int(report.current_event.start_bonuses.third)
else:
user_third -= int(report.current_event.start_bonuses.third)
elif report.current_event.__class__ is Match3CompleteTargets:
# На 7 уровне дается молоток, он бесплатен при переигрывании
if current_level != "0007":
if user_hammer <= 0:
df.loc[current_level, "Hammer"] += report.current_event.ingame_bonuses
else:
user_hammer -= report.current_event.ingame_bonuses
# При завершении уровней на обучение бонусу отбирается 1 бонус при его использовании
if report.previous_event.__class__ is Match3StartGame:
if report.previous_event.level_num == "0011":
user_first -= int(report.previous_event.start_bonuses.first)
elif report.previous_event.level_num == "0016":
user_second -= int(report.previous_event.start_bonuses.second)
elif report.previous_event.level_num == "0019":
user_third -= int(report.previous_event.start_bonuses.third)
elif report.previous_event.level_num == "0007":
user_hammer -= report.current_event.ingame_bonuses
if report.previous_event.__class__ is Match3FailGame:
df.loc[current_level, "Buy steps"] += 1
# Докупка ходов
elif report.current_event.__class__ is Match3FailGame:
df.loc[current_level, "Buy steps"] += report.current_event.fail_count
# Вывод
print(df.to_string())
writer = pd.ExcelWriter("Level spend coins " + str(start) + "-" + str(start + quantity - 1) + ".xlsx")
df.to_excel(excel_writer=writer)
writer.save()
def new_report(level_num="0030", report_app_version="5.3"):
# БАЗА ДАННЫХ
sql = """
SELECT ios_ifa, ios_ifv, event_name, event_json, event_datetime
FROM sop_events.events_ios
WHERE app_version_name={}
AND event_name = "Match3Events" and event_json like "%{}%"
order by ios_ifa, ios_ifv, event_datetime
""".format(report_app_version, level_num)
report = Report(sql_events=sql)
df = pd.DataFrame(
index=[0],
columns=[
"Start", "Finish", "Time", "Win", "Lose", "Steps used",
"Steps left", "Steps total", "Goal 1", "Goal 2", "Goal 1 total",
"Goal 2 total", "B 1", "B 2", "B 3", "Hammer", "Got coins", "Red",
"Yellow", "Blue", "Green", "White", "Black", "SmallLightning_h",
"SmallLightning_v", "FireSpark", "FireRing", "BigLightning_h",
"BigLightning_v", "SphereOfFire", "Stone", "Weight", "Lamp",
"TwoStarBonus", "StarBonus", "Box_l1", "Box_l2", "Box_l3",
"Carpet_l1", "Carpet_l2", "Chain_l1"
])
df.fillna("", inplace=True)
# ПАРАМЕТРЫ
index = -1
current_session = None
while report.get_next_event():
# Начало М3
if report.current_event.__class__ is Match3StartGame and report.current_event.level_num == level_num:
index += 1
# Начало игры
df.loc[index, "Start"] = report.current_event.datetime
# Общее кол-во данных шагов
df.loc[index,
"Steps total"] = round(int(report.current_event.turn_count),
0)
# Цели 1 2
df.loc[index, "Goal 1 total"] = str(
report.current_event.targets_list[0].target) + ": " + str(
report.current_event.targets_list[0].target_count)
if report.current_event.targets_list[1]:
df.loc[index, "Goal 2 total"] = str(
report.current_event.targets_list[1].target) + ": " + str(
report.current_event.targets_list[1].target_count)
# Взятые стартовые бонусы
df.loc[index,
"B 1"] = int(report.current_event.start_bonuses.first)
df.loc[index,
"B 2"] = int(report.current_event.start_bonuses.second)
df.loc[index,
"B 3"] = int(report.current_event.start_bonuses.third)
# Проверка на совпадение сессии
current_session = report.current_event.session_id
elif report.current_event.__class__ is Match3CompleteTargets and report.current_event.session_id == current_session:
# Выполнение целей
df.loc[index, "Finish"] = report.current_event.datetime
# Время на уровне
df.loc[index, "Time"] = str(
int(df.loc[index, "Finish"].timestamp() -
df.loc[index, "Start"].timestamp())) + "s"
# Победа
df.loc[index, "Win"] = 1
# Использованные и оставшиеся ходы
df.loc[index, "Steps used"] = report.current_event.turn_count
df.loc[index, "Steps left"] = df.loc[
index, "Steps total"] - report.current_event.turn_count
# Цели 1 2
df.loc[index, "Goal 1"] = str(
report.current_event.targets_list[0].target_count)
if report.current_event.targets_list[1]:
df.loc[index, "Goal 2"] = str(
report.current_event.targets_list[1].target_count)
# Собранная пыль
df.loc[index,
"Got coins"] = report.current_event.game_currency_count
# Использование молотка
df.loc[index, "Hammer"] = report.current_event.ingame_bonuses
# Собранные элементы
add_elements(df, index, report.current_event)
elif report.current_event.__class__ is Match3FinishGame and report.current_event.session_id == current_session:
# Конец уровня (перезапись)
df.loc[index, "Finish"] = report.current_event.datetime
# Получено пыли (дополнение после magic time)
df.loc[index,
"Got coins"] = report.current_event.game_currency_count
elif report.current_event.__class__ is Match3FailGame and report.current_event.session_id == current_session:
# Конец уровня
df.loc[index, "Finish"] = report.current_event.datetime
# Поражение
df.loc[index, "Lose"] = 1
# Время на уровне
df.loc[index, "Time"] = str(
int(df.loc[index, "Finish"].timestamp() -
df.loc[index, "Start"].timestamp())) + "s"
# Использованные и оставшиеся ходы
df.loc[index, "Steps used"] = report.current_event.turn_count
df.loc[index, "Steps left"] = df.loc[
index, "Steps total"] - report.current_event.turn_count
# Цели 1 2
df.loc[index, "Goal 1"] = str(
report.current_event.targets_list[0].target_count)
if report.current_event.targets_list[1]:
df.loc[index, "Goal 2"] = str(
report.current_event.targets_list[1].target_count)
df.loc[index, "Hammer"] = report.current_event.ingame_bonuses
add_elements(df, index, report.current_event)
# Вывод
df.fillna("", inplace=True)
print(df.to_string())
writer = pd.ExcelWriter("Detailed level " + str(level_num) + ".xlsx")
df.to_excel(excel_writer=writer)
writer.save()