def view(request): result = helpers.header("General Stats") # no of accounts all_users = User.objects.all().order_by("username") result += "Number of user accounts: " + str(all_users.count()) + "\n" # general stats videos all_videos = Video.objects.all() all_demos = Video.objects.filter( draft__source__service_identifier="2rtGFAnyf-s").count() all_videos_published = Video.objects.filter(published=Video.PUBLIC) all_demos_published = Video.objects.filter( draft__source__service_identifier="2rtGFAnyf-s", published=Video.PUBLIC).count() result += "Number of videos: " + \ str(all_videos.count()) + " (" + str(all_demos) + " demos) \n" result += "Number of published videos: " + \ str(all_videos_published.count()) + \ " (" + str(all_demos_published) + " demos) \n" result += helpers.header("Activity") # active in last 7 days startdate = datetime.now() enddate = startdate - timedelta(days=7) last_activities = UserActivity.objects.filter( last_seen__range=[enddate, startdate]).order_by('-last_seen') result += "Active in last 7 days: " + str(last_activities.count()) + "\n" # signups in last 7 days last_activities = User.objects.filter( date_joined__range=[enddate, startdate]).order_by('-last_seen') result += "Signups in last 7 days: " + str(last_activities.count()) + "\n" startdate = datetime.now() enddate = startdate - timedelta(days=30) last_activities = UserActivity.objects.filter( last_seen__range=[enddate, startdate]).order_by('-last_seen') result += "Active in last 30 days: " + str(last_activities.count()) + "\n" # signups in last 7 days last_activities = User.objects.filter( date_joined__range=[enddate, startdate]).order_by('-last_seen') result += "Signups in last 30 days: " + str(last_activities.count()) + "\n" return SimpleTemplateResponse("insights/base.html", { "title": "Insights Home", "insight_content": result })
def view(request): result = "" # # Most views # result += helpers.header("Users with the most views on their videos") count = viewsets.all_videos().values('team__owner__username').annotate( score=Sum("total_plays")).order_by('-score') max_rows = 25 result_array = [] for entry in count: result_array.append([ helpers.userlink(entry["team__owner__username"]), str(entry["score"]) ]) max_rows -= 1 if max_rows == 10: break result += helpers.table(result_array, ["username", "plays"]) # # Seen in last 30 days # enddate = date.today() - timedelta(days=30) daily_data = viewsets.user_activity_daily().filter(day__gt=enddate) userlist = {} for data in daily_data: if data.day == data.user.date_joined.date(): continue username = data.user.username if not username in userlist: userlist[username] = [username, 0] userlist[username][1] += 1 userlist = userlist.values() userlist.sort(key=lambda x: -x[1]) userlist = map(lambda x: [helpers.userlink(x[0]), str(x[1]) + " days"], userlist) result += helpers.header("Users seen in last 30 days") result += "Users seen on the same day as they signed up are stripped out<br /> <br />" result += helpers.table(userlist) return SimpleTemplateResponse("insights/base.html", { "title": "User statistics", "insight_content": result })
def view(request): # default to year weeks get = request.GET cohorte_size = get.get('cohorte', 'month') start = get.get('start', '-100') end = get.get('end', '0') # redirect if args are missing if not "cohorte" in get or not "start" in get or not "end" in get: return HttpResponseRedirect("/admin/insights/kpis-cohort/?cohorte=" + cohorte_size + "&start=" + start + "&end=" + end) # convert args cohorte_selector = cohorte_selectors[cohorte_size] start_date = date.today() + timedelta(days=int(start)) end_date = date.today() + timedelta(days=int(end) + 1) # build dict of users users = build_user_info(start_date, end_date) result = "" # info result += helpers.header("Report Details") result += "Cohorte Size: " + cohorte_size + "<br />" result += "Date Range: " + str(start_date) + " " + str(end_date) + "<br />" result += "Total signups in range: " + str(len(users)) groups = group_users(users, cohorte_selector) # activiation section result += helpers.header("Activation (percentage of signed up)") result += build_activation_table(groups) # retention section result += helpers.header("Retention (percentage of signed up)") result += build_retention_table(groups) return SimpleTemplateResponse("insights/base.html", { "title": "KPIs", "insight_content": result })
def build_overall_section(): result = helpers.header("Overall") result += helpers.table([ ['Users', viewsets.all_users().count()], ['Videos (incl. deleted)', viewsets.all_videos().count()], ['Published Videos', viewsets.published_videos().count()], ['Shared Videos', viewsets.shared_videos().count()], ['Upgraded', viewsets.upgraded_users().count()] ]); return result
def view(request): # collect subscriptions result_array = [] for sub in Subscription.objects.all().order_by("plan"): if sub.plan != settings.DEFAULT_PLAN[ "id"] and sub.user.username not in helpers.company_accounts: result_array.append([ helpers.userlink(sub.user), sub.plan, sub.managed_by, sub.notes ]) headers = ["User", "Plan", "Payment Method", "Notes"] result = helpers.table(result_array, headers) result += helpers.header("Plans") # collect Plans def renderplan(plan): features = "" for key, value in plan.iteritems(): if "feature" in key: feature = key.replace("feature_", "") if value: feature = feature else: feature = "<span style='color:lightgray'>" + feature + "</span>" features += feature + " " result = [ "<b>" + plan["name"] + "</b><br />" + plan["id"], humanize.intcomma(plan["price_usd"] / 100), humanize.intcomma(plan["price_eur"] / 100), humanize.intcomma(plan["price_gbp"] / 100), plan['payment_interval'], features, "x" if plan["subscribable"] else "", "x" if plan["listable"] else "", Subscription.objects.filter(plan=plan['id']).count() ] return result array = [] for plan in settings.PLANS_SORTED: array.append(renderplan(plan)) result += helpers.table(array, [ "Name", "USD", "EUR", "GBP", "Interval", "Features", "Subs.", "Listable", "No. Users" ]) return SimpleTemplateResponse("insights/base.html", { "title": "Subscriptions and Plans", "insight_content": result })
def build_graphs(): result = helpers.header("Signups") result += helpers.dategraph( viewsets.all_users(), "date_joined", accumulate=False) #result += helpers.header("Active Users") #result += helpers.dategraph( # viewsets.all_users(), # "date_joined", # accumulate=False) result += helpers.header("Videos (incl. deleted)") result += helpers.dategraph( viewsets.all_videos(), "created", accumulate=False) result += helpers.header("Published Videos") result += helpers.dategraph( viewsets.published_videos(), "created", accumulate=False) result += helpers.header("Shared Videos") result += helpers.dategraph( viewsets.shared_videos(), "created", accumulate=False) result += helpers.header("Plays") result += helpers.dategraph( viewsets.all_daily_stats(), "date", aggregate_field='plays_all') result += helpers.header("Player loads") result += helpers.dategraph( viewsets.all_daily_stats(), "date", aggregate_field='sessions') result += helpers.header("Upgraded") result += helpers.dategraph( viewsets.upgraded_users(), "date_joined", accumulate=False) return result
def view(request): # default to year weeks get = request.GET start = get.get('start', '-100') end = get.get('end', '0') # # redirect if args are missing if not "start" in get or not "end" in get: return HttpResponseRedirect("/admin/insights/kpis-web/?start=" + start + "&end=" + end) # sanitize start and end start = str(int(start) * -1) end = str(int(end) * -1) service = _get_service() result = "" result += helpers.header("Website traffic") result += website_traffic(service, start, end) result += helpers.header("Website traffic sources") result += website_traffic_sources(service, start, end) result += helpers.header("Blog traffic (all urls starting with /blog)") result += blog_traffic(service, start, end) result += helpers.header("Blog traffic sources") result += blog_traffic_sources(service, start, end) result += helpers.header("Player loaded") result += player_loaded(service, start, end) result += helpers.header("Player plays") result += player_played(service, start, end) return SimpleTemplateResponse("insights/base.html", { "title": "KPIs Web", "insight_content": result })
def view(request): # # General Query Building blocks # SELECT_PUBLISHED_REVISIONS = "SELECT COUNT(*) FROM videos_video as v JOIN videos_videorevision as vr ON(v.current_revision_id = vr.id) JOIN users_team as t ON (t.id = v.team_id) JOIN auth_user as u ON (u.id = t.owner_id) " SELECT_PUBLISHED_MARKERS = SELECT_PUBLISHED_REVISIONS + " JOIN videos_marker vm ON (vm.video_revision_id = vr.id)" SELECT_PUBLISHED_CONTENT_BLOCKS = SELECT_PUBLISHED_MARKERS + " JOIN videos_markercontent vmc ON (vmc.marker_id = vm.id)" SELECT_VIDEO_SOURCES = SELECT_PUBLISHED_REVISIONS + " JOIN videos_source as s ON (vr.source_id = s.id)" result = "Staff videos are not included in these statistics. <br />Only published videos are counted." # # Overall stats # result += helpers.header("Overall Stats (Published)") num_videos = get_result(SELECT_PUBLISHED_REVISIONS) num_markers = get_result(SELECT_PUBLISHED_MARKERS) num_blocks = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS) table = [] table.append(["Published Videos", num_videos]) table.append(["Published Markers", num_markers]) table.append(["Published Content Blocks", num_blocks]) table.append([""]) table.append(["Markers per publ. Video", num_markers / num_videos]) table.append(["Content Blocks per publ. Video", num_blocks / num_videos]) table.append(["Content Blocks per publ. Marker", num_blocks / num_markers]) result += helpers.table(table, ["Feature", "All", "Upgraded"]) # # Import Source Stats # result += helpers.header("Import Source Stats (Published)") num_uploaded = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'videopath'") num_youtube = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'youtube'") num_vimeo = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'vimeo'") num_wistia = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'wistia'") num_brightcove = get_result(SELECT_VIDEO_SOURCES + " WHERE s.service like 'brightcove'") table = [["Import Source", 'Videos']] table.append(["Uploaded Files", num_uploaded]) table.append(["Youtube Hosting", num_youtube]) table.append(["Vimeo Hosting", num_vimeo]) table.append(["Wistia Hosting", num_wistia]) table.append(["Brightcove Hosting", num_brightcove]) result += helpers.chart(table, 'column') # # Video Feature Stats # result += helpers.header("Video Feature Stats (Published)") num_custom_colors = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_color_1 != '#424242'") num_continuous_playback = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.continuous_playback = true") num_equal_marker_lengths = get_result( SELECT_PUBLISHED_REVISIONS + "WHERE vr.ui_equal_marker_lengths = true") num_custom_thumbnail = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.custom_thumbnail_id != 0") num_disable_share_buttons = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_disable_share_buttons = true") num_fit_video = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.ui_fit_video = true") num_custom_tracking_code = get_result( SELECT_PUBLISHED_REVISIONS + " WHERE vr.custom_tracking_code != ''") num_password = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.password !=''") # num_iphone_enabled = get_result(SELECT_PUBLISHED_REVISIONS + " WHERE vr.iphone_images > 20") table = [['Feature', 'Videos']] table.append(["Custom Colors", num_custom_colors]) table.append(["Custom Thumbnail", num_custom_thumbnail]) table.append(["Disabled Share Buttons", num_disable_share_buttons]) table.append(["Equal Marker Lengths", num_equal_marker_lengths]) table.append(["Fit Video", num_fit_video]) table.append(["Custom Tracking Code", num_custom_tracking_code]) table.append(["Password Protection", num_password]) table.append(["Continuous Playback", num_continuous_playback]) # table.append(["Iphone enabled", num_iphone_enabled]) result += helpers.chart(table, 'column') # # Content Block Stats # result += helpers.header("Content Block Stats (Published)") num_text_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'text'") num_image_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'image'") num_social_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'social'") num_media_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'media'") num_website_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'website'") num_maps_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'maps'") num_button_block = get_result(SELECT_PUBLISHED_CONTENT_BLOCKS + " WHERE vmc.type = 'simple_button'") table = [["Type", "Amount"]] table.append(["Text ", num_text_block]) table.append(["Image ", num_image_block]) table.append(["Social ", num_social_block]) table.append(["Media ", num_media_block]) table.append(["Website ", num_website_block]) table.append(["Button ", num_button_block]) table.append(["Maps ", num_maps_block]) result += helpers.chart(table, 'pie') return SimpleTemplateResponse("insights/base.html", { "title": "Features", "insight_content": result })
def videoview(request, key): video = Video.objects.get(key=key) result = helpers.header("General Info") result += "User: "******"Overall stats") try: data = video.total_analytics.latest("plays_all") percent_interacting = int( min(100, (float(data.overlays_opened_unique / float(data.plays_all)) * 100))) clicks_per_user = (float(data.overlays_opened_all) / float(data.plays_all)) result += "Plays: " + str(data.plays_all) + "\n" result += "Plays unique: " + str(data.plays_unique) + "\n" result += "Average Session Duration: " + str( data.avg_session_time) + "\n" result += "Clicks on markers: " + str(data.overlays_opened_all) + "\n" result += "Clicks on markers unique: " + str( data.overlays_opened_unique) + "\n" result += "Viewers interacting: " + str(percent_interacting) + "%\n" result += "Average clicks per user: "******"\n" except: result += "No stats available at this time" result += helpers.header("Engagement stats") try: # overall stats querySet = VideoStatistics.objects.filter(videoKey=key, sessionTotal__lte=1800) stats = querySet.aggregate(playingTotal=Sum('playingTotal'), overlayOpenTotal=Sum('overlayOpenTotal'), sessionTotal=Sum('sessionTotal')) num_sessions = str(querySet.count()) result += "Recorded Sessions: " + num_sessions + "\n" result += "Overall session time: " + formatSeconds( stats['sessionTotal']) + " - avg. " + formatSeconds( stats['sessionTotal'] / float(num_sessions)) + "\n" result += "Overall play time: " + formatSeconds( stats['playingTotal']) + " - avg. " + formatSeconds( stats['playingTotal'] / float(num_sessions)) + "\n" result += "Overall overlay time: " + formatSeconds( stats['overlayOpenTotal']) + " - avg. " + formatSeconds( stats['overlayOpenTotal'] / float(num_sessions)) + "\n" result += "<strong>Time spent longer on video: " + str( math.ceil(stats['overlayOpenTotal'] / stats['sessionTotal'] * 100)) + '% </strong>' # sessions with overlay opens result += '<br /><br /><strong>Session with overlay opens</strong><br />' querySet = VideoStatistics.objects.filter(videoKey=key, sessionTotal__lte=1800, overlayOpenTotal__gt=10) stats = querySet.aggregate(playingTotal=Sum('playingTotal'), overlayOpenTotal=Sum('overlayOpenTotal'), sessionTotal=Sum('sessionTotal')) num_sessions = str(querySet.count()) result += "Recorded Sessions: " + num_sessions + "\n" result += "Overall session time: " + formatSeconds( stats['sessionTotal']) + " - avg. " + formatSeconds( stats['sessionTotal'] / float(num_sessions)) + "\n" result += "Overall play time: " + formatSeconds( stats['playingTotal']) + " - avg. " + formatSeconds( stats['playingTotal'] / float(num_sessions)) + "\n" result += "Overall overlay time: " + formatSeconds( stats['overlayOpenTotal']) + " - avg. " + formatSeconds( stats['overlayOpenTotal'] / float(num_sessions)) + "\n" result += "<strong>Time spent longer on video: " + str( math.ceil(stats['overlayOpenTotal'] / stats['sessionTotal'] * 100)) + '% </strong>' except: result += "No data available at this time" result += helpers.header("Video") result += '<iframe width="700px" height="525px" frameborder="0" src="https://player.videopath.com/' + video.key + '" allowfullscreen="" onmousewheel="event.preventDefault();"></iframe>' return SimpleTemplateResponse( "insights/base.html", { "title": "Video '" + video.get_current_revision_or_draft().title + "'", "insight_content": result })
def listview(request): # video plays result = helpers.header("Plays of videos by user in the last 7 days") last_day = date.today() first_day = last_day - timedelta(days=7) count = DailyAnalyticsData.objects.filter( date__gte=first_day, date__lt=last_day)\ .values('video__team__owner__username')\ .annotate(score=Sum("plays_all"))\ .order_by('-score') rows = 0 result_array = [] for entry in count: result_array.append( [entry["video__team__owner__username"], str(entry["score"])]) rows += 1 if rows >= 10: break result += helpers.table(result_array, ["username", "plays"]) # # Popular videos # result += helpers.header("Most popular video the last 7 days") last_day = date.today() first_day = last_day - timedelta(days=7) count = DailyAnalyticsData.objects.filter( date__gte=first_day, date__lt=last_day)\ .values('video_id')\ .annotate(score=Sum("plays_all"))\ .order_by('-score') result_array = [] max_rows = 25 for entry in count: video = Video.objects.get(pk=entry["video_id"]) link = helpers.videolink(video) if link: link.append(entry['score']) result_array.append(link) max_rows -= 1 if max_rows == 0: break result += helpers.table( result_array, ["Title", "User", "Modified", "Plays all", "Plays last 7"]) # # published vids # startdate = datetime.now() enddate = startdate - timedelta(days=30) result += helpers.header("Recently published videos") videos = Video.objects.filter( current_revision__modified__range=[enddate, startdate]).order_by( '-current_revision__modified') result += helpers.videolist(videos) return SimpleTemplateResponse("insights/base.html", { "title": "Videos", "insight_content": result })
def userview(request, username): # load user user = User.objects.get(username=username) # start trial if requested if request.GET.get('trial', False): start_trial.run(user) result = helpers.header("Info") result += "<a href='mailto:" + user.email + "'>" + user.email + "</a> <br />" result += "Signed up " + humanize.naturaltime(user.date_joined) + "<br />" # last seen info try: result += "Last seen " + humanize.naturaltime( user.activity.last_seen) + "<br />" except: pass try: result += "Currently subscribed to " + user.subscription.plan + "<br />" except: pass result += "<br /><a href ='{0}?trial=true'>Start Trial</a>".format( request.path) # billing info result += helpers.header("Billing Adress") try: result += user.payment_details.name + "<br />" result += user.payment_details.street + "<br />" result += user.payment_details.city + "<br />" result += user.payment_details.post_code + "<br />" result += user.payment_details.country + "<br />" except: pass # camaign info result += helpers.header("Campaign Info") try: result += "Country: " + user.campaign_data.country + "<br />" result += "Referrer: " + referral_link( user.campaign_data.referrer) + "<br />" result += "<br />" result += "Name: " + user.campaign_data.name + "<br />" result += "Source: " + user.campaign_data.source + "<br />" result += "Medium: " + user.campaign_data.medium + "<br />" result += "Content: " + user.campaign_data.content + "<br />" result += "Term: " + user.campaign_data.term + "<br />" except: pass result += helpers.header("Video plays") result += helpers.dategraph( viewsets.all_daily_stats().filter(video__team__owner=user), "date", aggregate_field='plays_all') result += helpers.header("Days seen per week") result += helpers.dategraph(user.user_activity_day.all(), "day") # published videos result += helpers.header("Published Videos") videos = Video.objects.filter( team__owner=user, archived=False, published=Video.PUBLIC).order_by('-current_revision__modified') result += helpers.videolist(videos) # unpublished videos result += helpers.header("Unpublished Videos") videos = Video.objects.filter( team__owner=user, archived=False, published=Video.PRIVATE).order_by('-current_revision__modified') result += helpers.videolist(videos) # deleted videos result += helpers.header("Deleted Videos") videos = Video.objects.filter( team__owner=user, archived=True).order_by('-current_revision__modified') result += helpers.videolist(videos) return SimpleTemplateResponse("insights/base.html", { "title": "User '" + username + "'", "insight_content": result })