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, 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()
sources[publisher][source][report.previous_user.install_date][param] += user_accumulative[param] if len(user_transactions) > 0: sources[publisher][source][report.previous_user.install_date]["Paying"] += 1 transactions[publisher][source].append(user_transactions) for d in user_dau.keys(): if user_dau[d] is not None: dau[publisher][source][d]["Users"] += 1 dau[publisher][source][d]["Revenue"] += user_dau[d] # if len(user_transactions) > 0: # print(dau[publisher][source]) # Цикл обработки данных while report.get_next_event(): # Переносим пользовательские данные в общие и обнуляем пользователськие парамтеры if report.is_new_user(): flush_user_data() user_accumulative = dict.fromkeys(accumulating_parameters, 0) user_transactions = [] for dt in rrule(DAILY, dtstart=period_start, until=period_end + timedelta(days=days_since_install)): user_dau[dt.date()] = None ltv = 0 previous_day_in_game = 0 publisher = report.current_user.publisher source = report.current_user.source # Определяем день после установки day_in_game = report.get_time_since_install(measure="day") # Если день изменился, то заполняем дни от предыдущего до нынешнего предыдущим LTV if day_in_game > previous_day_in_game:
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()