def index(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity, UserAccountEvent
if request.args.get('activity') == 'activity':
activity_model, activity_date = UserActivity, UserActivity.date_actioned
else:
activity_model, activity_date = UserAccountEvent, UserAccountEvent.event_date
try:
interval_count = int(request.args['interval_count'])
except Exception:
interval_count = 10
interval = request.args.get('interval')
if interval not in ('week', 'month'):
interval = 'week'
cohort = func.date_part(interval, User.date_joined)
cohort_label = func.min(func.date(User.date_joined))
active_interval = (func.date_part(interval, activity_date) - cohort).label('active_interval')
q = readonly_session.query(User).filter(
User.date_joined > LAUNCHDATE, User.refresh_token != '')
if request.args.get('gender') in ('m', 'f'):
q = q.filter(User.gender == request.args['gender'])
if request.args.get('locale') in app.config['ENABLED_LOCALES']:
q = q.filter(User.locale == request.args['locale'])
if request.args.get('age') in ('13-18', '18-25', '25-35', '35-45', '45-55'):
age1, age2 = map(int, request.args['age'].split('-'))
q = q.filter(between(
func.age(User.date_of_birth),
text("interval '%d years'" % age1),
text("interval '%d years'" % age2)
))
active_users = dict(
((c, int(w)), u) for c, w, u in
q.join(
activity_model,
(activity_model.user == User.id) &
(activity_date >= User.date_joined)
).group_by(cohort, active_interval).values(
cohort, active_interval, func.count(func.distinct(activity_model.user))
)
)
table = Table(
[dict(id='cohort', type=date)] +
[dict(id='%s%d' % (interval, i), type=str) for i in range(interval_count)]
)
totals = q.group_by(cohort).order_by(cohort)
for c, l, t in totals.values(cohort, cohort_label, func.count()):
data = []
for i in range(interval_count):
a = active_users.get((c, i), '')
data.append(a and '%d%% (%d)' % (ceil(a * 100.0 / t), a))
table.append([l] + data)
return self.render('admin/retention_stats.html', data=table.encode())
def index_old(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User, UserActivity
user_count = readonly_session.query(func.count(User.id)).\
filter(User.refresh_token != '').scalar()
header = ('user count', 'max lifetime', 'avg lifetime', 'stddev lifetime',
'max active days', 'avg active days', 'stddev active days')
lifetime = func.date_part('days', func.max(UserActivity.date_actioned) -
func.min(UserActivity.date_actioned)).label('lifetime')
active_days = func.count(func.distinct(func.date(
UserActivity.date_actioned))).label('active_days')
activity = readonly_session.query(UserActivity.user, lifetime, active_days).\
group_by(UserActivity.user)
ctx = {}
for key, having_expr in ('all', None), ('1day', lifetime > 1), ('7day', lifetime > 7):
data = activity.having(having_expr).from_self(
func.count('*'),
func.max(lifetime),
func.avg(lifetime),
func.stddev_samp(lifetime),
func.max(active_days),
func.avg(active_days),
func.stddev_samp(active_days)
).one()
table = Table([
dict(id='metric', type=str),
dict(id='value', type=float),
dict(id='%', type=str),
])
pdata = ('%d%%' % (data[0] * 100 / user_count),) + ('',) * 6
table.extend(zip(*(header, map(float, data), pdata)))
ctx['ret_%s_data' % key] = table.encode()
return self.render('admin/retention_stats_old.html', **ctx)
def index(self):
from gviz_data_table import Table
from rockpack.mainsite.services.user.models import User
from .models import AppDownloadRecord
ctx = {}
for action in 'download', 'update':
table = Table((dict(id='date', type=date), dict(id='count', type=long)))
table.extend(
readonly_session.query(AppDownloadRecord).filter_by(action=action).
group_by('1').order_by('1').
values(AppDownloadRecord.date, func.sum(AppDownloadRecord.count))
)
ctx['%s_data' % action] = table.encode()
table = Table([dict(id='date', type=date)] +
[dict(id=i, type=long) for i in 'Total', 'US', 'UK', 'Other'])
def index(self):
from gviz_data_table import Table
try:
period = dict(self.periods)[request.args.get('period')]
except:
period = self.periods[0][1]
dates = {'from': datetime.combine(date.today() - timedelta(days=period), time.min)}
dates['to'] = dates['from'] + timedelta(days=period)
for param in 'from', 'to':
try:
dates[param] = datetime.strptime(request.args[param], '%Y-%m-%d %H:%M')
except:
pass
try:
sortindex = self.counts.index(request.args.get('sort')) + self.sortindex_base
except:
sortindex = self.sortindex_base
try:
limit = int(request.args['limit'])
except:
limit = 20
table = Table(self.get_table_head())
for row in self.get_query(dates['from'], dates['to']).\
order_by('%d desc' % sortindex).limit(limit):
item, counts = self.split_row(row)
table.append(item + [int(i) if i else 0 for i in counts])
selects = ('sort', self.counts), ('period', zip(*self.periods)[0])
return self.render('admin/chart_table.html',
data=table.encode(),
selects=selects,
dates=sorted(dates.items()))
