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