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
})
