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 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 listview(request):
result = ""
users = []
for u in User.objects.all():
videos = Video.objects.filter_for_user.filter(archived=False).count()
# published
videos_published = u.videos.filter(published=Video.PUBLIC,
archived=False).count()
demo_videos = Video.objects.filter(
video__team__owner=u, service_identifier="2rtGFAnyf-s").count()
if videos == 0:
color = "red"
elif videos > 0 and videos_published == 0 or demo_videos == videos:
color = "darkorange"
else:
color = "green"
user = [
"<span style = 'color:" + color + "'>" + helpers.userlink(u) +
"</span>", "<b>" + str(videos) + "</b> videos",
"<b>" + str(videos_published) + "</b> published",
"<b>" + str(demo_videos) + "</b> demos"
]
users.append(user)
result += helpers.table(users)
return SimpleTemplateResponse("insights/base.html", {
"title": "All Users",
"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 listview_sales(request):
result = ""
users = []
for u in User.objects.extra(
select=
{
'campaign_name':
'SELECT name FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id',
'country':
'SELECT country FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id',
'referrer':
'SELECT referrer FROM users_usercampaigndata WHERE users_usercampaigndata.user_id = auth_user.id',
'num_videos':
'SELECT COUNT(*) FROM videos_video JOIN users_team ON (videos_video.team_id = users_team.id) WHERE users_team.owner_id = auth_user.id AND videos_video.archived != True',
'num_videos_published':
'SELECT COUNT(*) FROM videos_video JOIN users_team ON (videos_video.team_id = users_team.id) WHERE users_team.owner_id = auth_user.id AND videos_video.archived != True AND videos_video.published = 1',
}).order_by('-date_joined'):
user = [
"<span>" + helpers.userlink(u) + "</span>",
u.email,
"" + str(u.country if u.country else '') + "",
"" + str(u.campaign_name if u.campaign_name else '') + "",
referral_link(u.referrer),
"<b>" + str(u.num_videos) + "</b> v",
"<b>" + str(u.num_videos_published) + "</b> p",
"<b>" + str(u.date_joined.date()) + "</b>",
]
users.append(user)
result += helpers.table(users)
return SimpleTemplateResponse("insights/base.html", {
"title": "All Users (Sales List)",
"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 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
})