def content_overview(dt, week_ucis, content_id, title): content_by_week = query_content_weekly(content_id) content_data = [(x['started-views'], int(x['viewing-time'] * x['weekly-active-user'])) for x in content_by_week] total_views = sum([x[0] for x in content_data]) # this is already measured in minutes total_viewtime = sum([x[1] for x in content_data]) started_views_this_week = week_ucis.count() content_hour_of_day = normalize(view_count_by_hour_of_day(week_ucis), started_views_this_week) content_day_of_week = normalize(view_count_by_day_of_week(week_ucis), started_views_this_week) weekly_active_user = user_number(week_ucis) completion_ratio = avg_completion_ratio(week_ucis) user_viewtime = avg_user_viewtime(week_ucis) device_type = top_tag_by_view_count(week_ucis, 'device') res = { "id": content_id, 'inventoryID': content_id, 'title': title, "datetime": dt.replace(tzinfo=pytz.UTC), "content-total-viewing-time": total_viewtime, "content-total-started-views": total_views, "hour-of-day": content_hour_of_day, "day-of-week": content_day_of_week, "weekly-active-user": weekly_active_user, "content-completion": completion_ratio, "viewing-time": user_viewtime, "device-type": device_type, } print "#"*10 print content_id print res r.db('gettv_insight_api').table('content_overview').insert([res], conflict='replace').run()
def users_package_overview(ucis): """ send in ucis and get its users, then group by package info """ package_names = ["T1", "T2", "T3"] users = spark_io.get_users() user_num = users.count() user_packages = users\ .groupBy('basicPackage')\ .count()\ .collect() user_packages = {x['basicPackage']: x['count'] for x in user_packages} tag_users = ucis\ .select('userID')\ .distinct()\ .collect() tag_users = set([x['userID'] for x in tag_users]) # defined udf for filter # if return if user is a tag user is_in_tag_user = func.udf(lambda x: x in tag_users, BooleanType()) tag_user_packages = users\ .filter(is_in_tag_user(users.userID))\ .groupBy('basicPackage')\ .count()\ .collect() tag_user_packages = {x['basicPackage']: x['count'] for x in tag_user_packages} tag_user_packages_norm = normalize([(x, tag_user_packages[x]) for x in package_names], len(tag_users)) user_packages_norm = normalize([(x, user_packages[x] - tag_user_packages[x]) for x in package_names], user_num - len(tag_users)) return tag_user_packages_norm, user_packages_norm
def run_daily_kpis(timestamp, week_ucis, channel_id): date_string = timestamp.strftime('%Y-%m-%d') started_views = view_count(week_ucis) weekly_active_user = user_number(week_ucis) user_viewtime = avg_user_viewtime(week_ucis) views_by_action = normalize(action_type_view_count(week_ucis), started_views, 'count') complete_views = avg_finished_program_by_user(week_ucis) completion_ratio = avg_completion_ratio(week_ucis) weekly_top_program = top_programs_by_view_count(week_ucis, 10) weekly_top_genre = normalize(top_genre_by_view_count(week_ucis, 10), started_views, 'count') res = { "id": date_string + '_' + channel_id, 'channelID': channel_id, "datetime": timestamp.replace(tzinfo=pytz.UTC), "viewing-time": user_viewtime, "weekly-active-user": weekly_active_user, "started-views": started_views, "comlete-views": complete_views, "content-completion": completion_ratio, "top-programs": weekly_top_program, "top-genres": weekly_top_genre } print "#" * 10 print channel_id print res r.db('telenortv_insight_api').table('channel_by_week').insert([res]).run() print date_string print "#" * 10
def run_weekly_kpis(timestamp, week_ucis, content_id, title): date_string = timestamp.strftime('%Y-%m-%d') started_views = view_count(week_ucis) print started_views, "started views" weekly_active_user = user_number(week_ucis) user_viewtime = avg_user_viewtime(week_ucis) views_by_action = normalize(action_type_view_count(week_ucis), started_views) complete_views = avg_finished_program_by_user(week_ucis) completion_ratio = avg_completion_ratio(week_ucis) res = { "id": date_string + '_' + content_id, "title": title, 'inventoryID': content_id, "datetime": timestamp.replace(tzinfo=pytz.UTC), "viewing-time": user_viewtime, "weekly-active-user": weekly_active_user, "started-views": started_views, "comlete-views": complete_views, "content-completion": completion_ratio, } print "#"*10 print content_id print res r.db('gettv_insight_api').table('content_by_week').insert([res], conflict='replace').run() print date_string print "#"*10
def genre_by_hour_of_day(week_ucis, genre): genre_ucis = week_ucis.filter(week_ucis.category == genre) genre_hour_of_day = view_count_by_hour_of_day(genre_ucis) genre_sum = sum([x[1] for x in genre_hour_of_day]) return { "data": normalize(genre_hour_of_day, genre_sum), "id": "{}-hour-of-day".format(genre.replace('/', '-')) }
def run_daily_kpis(timestamp, users): daily_ucis = spark_io.get_daily_interactions(timestamp) date_string = timestamp.strftime('%Y-%m-%d') started_views = view_count(daily_ucis) daily_active_user = user_number(daily_ucis) user_viewtime = avg_user_viewtime(daily_ucis) views_by_action = normalize(action_type_view_count(daily_ucis), started_views) complete_views = avg_finished_program_by_user(daily_ucis) completion_ratio = avg_completion_ratio(daily_ucis) daily_top_program = top_programs_by_view_count(daily_ucis, 10) daily_top_genre = normalize( top_tag_by_view_count(daily_ucis, 'category', 10), started_views) daily_top_channel = normalize( top_tag_by_view_count(daily_ucis, 'channelName', 10), started_views) week_ucis = spark_io.get_weekly_interactions(timestamp) last_week_ucis = spark_io.get_weekly_interactions(timestamp - timedelta(days=7)) weekly_hibernation = user_hibernation(week_ucis, last_week_ucis) new_users = weekly_new_user(users, timestamp) total_users = unique_user(users, timestamp) res = { "id": date_string, "datetime": timestamp.replace(tzinfo=pytz.UTC), "new-user": new_users, "unique-user": total_users, "viewing-time": user_viewtime, "daily-active-user": daily_active_user, "started-views": started_views, "views-by-action": views_by_action, "complete-views": complete_views, "content-completion": completion_ratio, "user-hibernation": weekly_hibernation, "top-programs": daily_top_program, "top-genres": daily_top_genre, "top-channels": daily_top_channel, } print res r.db('telenortv_insight_api')\ .table('by_day')\ .insert([res], conflict='replace').run() print date_string print "#" * 10
def service_overview(dt): spark_io = SparkParquetIO() week_ucis = spark_io.get_weekly_interactions(dt) total_views = week_ucis.count() total_viewtime = week_ucis.groupBy().sum('duration').collect()[0]['sum(duration)'] / 60 hour_of_day = normalize( view_count_by_hour_of_day(week_ucis), total_views) day_of_week = normalize( view_count_by_day_of_week(week_ucis), total_views) device_overview = normalize( top_tag_by_view_count(week_ucis, 'device'), total_views) device_conpletion_ratio = top_tag_by_completion_ratio(week_ucis, 'device') location_overview = normalize( top_tag_by_view_count(week_ucis, 'city'), total_views) ################### # this frist one should be wraped with genre title count as well genre_by_views = normalize( top_tag_by_view_count(week_ucis, 'category', row_limit= 20), total_views) ################### genre_by_viewtime = normalize( top_tag_by_total_viewtime(week_ucis, 'category', row_limit=20), total_viewtime) genre_by_completion_ratio = top_tag_by_completion_ratio(week_ucis, 'category', row_limit=20) genre_by_user_viewtime = top_tag_by_user_viewtime(week_ucis, 'category', row_limit=20) # about users user_complete_view = user_by_complete_views(week_ucis) user_viewtime = user_by_viewtime(week_ucis, 7) action_type_overview = top_tag_by_total_viewtime(week_ucis, 'actionType') action_type_overview = normalize( action_type_overview, sum([x[1] for x in action_type_overview]) ) res = [ {"id": "hour-of-day", "data": hour_of_day}, {"id": "day-of-week", "data": day_of_week}, {"id": "genre-by-started-views", "data": genre_by_views}, {"id": "genre-by-viewtime", "data": genre_by_viewtime}, {"id": "genre-by-user-viewtime", "data": genre_by_user_viewtime}, {"id": "genre-by-completion-ratio", "data": genre_by_completion_ratio}, {"id": "device-by-started-views", "data": device_overview}, {"id": "device-by-completion-ratio", "data": device_conpletion_ratio}, {"id": "location-by-started-views", "data": location_overview}, {"id": "genre-by-completion-ratio", "data": genre_by_completion_ratio}, {"id": "user-by-complete-views", "data": user_complete_view}, {"id": "user-by-viewtime", "data": user_viewtime}, {"id": "action-type-overview", "data": action_type_overview} ] for x in res: print x r.db('gettv_insight_api').table('overview').insert(res, conflict='replace').run()
def top_genre_vod(ucis, vod): vod_with_views = ucis\ .filter(ucis.actionType==vod)\ .groupBy(ucis.inventoryID)\ .count()\ .collect() res = seq(vod_with_views)\ .map(lambda x: (vod_genre(x['inventoryID']), x['count']))\ .filter(lambda (genre, count): genre)\ .group_by(lambda (genre, count): genre)\ .map(lambda (genre, g_list): (genre, sum([x[1] for x in g_list])))\ .order_by(lambda (g, c): -c)\ .to_list() vod_sum = sum([x[1] for x in res]) return { "data": normalize(res, vod_sum)[:10], "id": "{}-genre-by-views".format(vod) }
def run_vod_kpis(ucis, view_type): started_views = view_count(ucis) week_ucis = ucis.filter((dt_end - timedelta(days=6) < ucis.firstEvent) & (ucis.firstEvent < dt_end + timedelta(days=1))) week_ago_ucis = ucis.filter((dt_end - timedelta(days=13) < ucis.firstEvent) & (ucis.firstEvent < dt_end - timedelta(days=6))) weekly_active_user = user_number(week_ucis) total_active_user = user_number(ucis) total_viewtime = total_viewing_time(ucis) user_viewtime = avg_user_viewtime(week_ucis) weekly_hibernation = user_hibernation(week_ucis, week_ago_ucis) top_program = top_programs_in_vod(ucis, 20) top_channel = normalize(top_tag_by_view_count(ucis, 'channelName'), started_views) hour_of_day = normalize(view_count_by_hour_of_day(ucis), started_views) day_of_week = normalize(view_count_by_day_of_week(ucis), started_views) tag_user_package, user_package = users_package_overview(ucis) package_overview = { "{} user".format(view_type): tag_user_package, "linear TV user": user_package } res = [{ "title": 'started-views', "id": 'started-views', "started-views": started_views }, { "title": 'weekly-active-user', "id": 'weekly-active-user', "weekly-active-user": weekly_active_user }, { "title": 'total-active-user', "id": 'total-active-user', "total-active-user": total_active_user }, { "title": 'total-viewing-time', "id": 'total-viewing-time', "total-viewing-time": total_viewtime }, { "title": 'viewing-time', "id": 'viewing-time', "viewing-time": user_viewtime }, { "title": 'user-hibernation', "id": 'user-hibernation', "user-hibernation": weekly_hibernation }, { "title": 'top-programs', "id": 'top-programs', "data": top_program }, { "title": 'top-provider', "id": 'top-provider', "data": top_channel }, { "title": 'hour-of-day', "id": 'hour-of-day', "data": hour_of_day }, { "title": 'day-of-week', "id": 'day-of-week', "data": day_of_week }, { "title": 'package-overview', "id": 'package-overview', "data": package_overview }] r.db('telenortv_insight_api').table(view_type).insert( res, conflict='replace').run()
def service_overview(dt): spark_io = SparkParquetIO() week_ucis = spark_io.get_weekly_interactions(dt) total_views = week_ucis.count() total_viewtime = week_ucis.groupBy().sum( 'duration').collect()[0]['sum(duration)'] / 60 hour_of_day = normalize(view_count_by_hour_of_day(week_ucis), total_views) day_of_week = normalize(view_count_by_day_of_week(week_ucis), total_views) # about channels channel_by_views = normalize( top_tag_by_view_count(week_ucis, 'channelName', row_limit=20), total_views) channel_by_viewtime = normalize( top_tag_by_total_viewtime(week_ucis, 'channelName', row_limit=20), total_viewtime) channel_by_completion_ratio = top_tag_by_completion_ratio(week_ucis, 'channelName', row_limit=20) channel_by_user_viewtime = top_tag_by_user_viewtime(week_ucis, 'channelName', row_limit=20) # about genres ################### # this frist one should be wraped with genre title count as well genre_by_views = normalize( top_tag_by_view_count(week_ucis, 'category', row_limit=20), total_views) genre_by_title_count = normalize([(x[0], title_count_for_genre(x[0])) for x in genre_by_views], total_title_count()) genre_overview = {"views": genre_by_views, "titles": genre_by_title_count} ################### genre_by_viewtime = normalize( top_tag_by_total_viewtime(week_ucis, 'category', row_limit=20), total_viewtime) genre_by_completion_ratio = top_tag_by_completion_ratio(week_ucis, 'category', row_limit=20) genre_by_user_viewtime = top_tag_by_user_viewtime(week_ucis, 'category', row_limit=20) # about users user_complete_view = user_by_complete_views(week_ucis) user_viewtime = user_by_viewtime(week_ucis, 7) action_type_overview = top_tag_by_total_viewtime(week_ucis, 'actionType') action_type_overview = normalize(action_type_overview, sum([x[1] for x in action_type_overview])) basic_package_by_viewing_time = basic_package_user_viewing_time(week_ucis) primeium_package_by_viewing_time = primeium_package_user_viewing_time( week_ucis) package_overview = basic_additional_package_overview() res = [{ "id": "user-package-overview", "data": package_overview }, { "id": "basic-package-user-viewing-time", "data": basic_package_by_viewing_time }, { "id": "premium-package-user-viewing-time", "data": primeium_package_by_viewing_time }, { "id": "hour-of-day", "data": hour_of_day }, { "id": "day-of-week", "data": day_of_week }, { "id": "channel-by-completion-ratio", "data": channel_by_completion_ratio }, { "id": "channel-by-views", "data": channel_by_views }, { "id": "channel-by-viewtime", "data": channel_by_viewtime }, { "id": "channel-by-user-viewtime", "data": channel_by_user_viewtime }, { "id": "genre-overview", "data": genre_overview }, { "id": "genre-by-viewtime", "data": genre_by_viewtime }, { "id": "genre-by-user-viewtime", "data": genre_by_user_viewtime }, { "id": "genre-by-completion-ratio", "data": genre_by_completion_ratio }, { "id": "user-by-complete-views", "data": user_complete_view }, { "id": "user-by-viewtime", "data": user_viewtime }, { "id": "action-type-overview", "data": action_type_overview }] print res r.db('telenortv_insight_api').table('overview').insert( res, conflict='replace').run()