import helper_sql_server import helper_mysql import helper_regex import config current_date=helper_regex.date_add(helper_regex.get_date_str_now(),-1) def c(oem_name='',category='',key='',sub_key='',date='',table_name='raw_data',db_conn=None): if isinstance(date,str) or not date: return helper_mysql.get_raw_collection_from_key(oem_name=oem_name,category=category,key=key,sub_key=sub_key,date=date,table_name=table_name,db_conn=db_conn) return helper_mysql.get_raw_collection_from_key_date_range(oem_name=oem_name,category=category,key=key,sub_key=sub_key,begin_date=min(date),end_date=max(date),table_name=table_name,db_conn=db_conn) if __name__ =='__main__': pass
import helper_regex import helper_mysql import config from random import randint #Read and make override user data from gumilive def get_user_ids_created_by_date(target_date): start_time=helper_regex.date_add(target_date,0)+' 00:00:00' end_time=helper_regex.date_add(target_date,1)+' 00:00:00' fault_id = randint(1,100) sql=r''' SELECT id FROM gumi_live.auth_user WHERE date_joined>='%s' and date_joined<'%s'; ''' % (start_time,end_time) #sql=r''' #SELECT id FROM gumi_live.auth_user WHERE date_joined>='%s' and date_joined<'%s' UNION SELECT '%d' FROM gumi_live.auth_user; #''' % (start_time,end_time,1) result_set=helper_mysql.fetch_set(sql,config.conn_stat_gumi_live) result_set=set([str(i) for i in result_set]) return result_set if __name__=='__main__': print get_user_ids_created_by_date(helper_regex.get_date_str_now()) print helper_regex.get_date_str_now()
def export(date_length=30): user_login_history={} user_last_login_date={} today=helper_regex.date_add(helper_regex.get_date_str_now(),-17) start_time=helper_regex.date_add(today,-date_length)+' 05:00:00' end_time=helper_regex.date_add(today,-1)+' 05:00:00' # user_id -> msisdn sql=r''' SELECT [user_id],replace([user_name],'@shabik.com','') as msisdn FROM [mozone_user].[dbo].[Profile] with(nolock) where [creationDate]>='%s' and [creationDate]<'%s' and user_name like '%%shabik.com%%' ''' % (start_time,end_time) user_id_to_msisdn=helper_sql_server.fetch_dict(conn_config=config.conn_stc,sql=sql) # new user user_id new_user_collection=user_id_to_msisdn.keys() new_user_collection=set([str(user_id) for user_id in new_user_collection]) # subscription status sql=r''' select distinct '0'+replace(msisdn,'+966','')+'@shabik.com' as [user_name] into #tmp from db86.shabik_mt.dbo.accounts with(nolock) where is_deleted=0 SELECT [user_id] FROM [mozone_user].[dbo].[Profile] with(nolock) where [creationDate]>='%s' and [creationDate]<'%s' and user_name like '%%shabik.com%%' and user_name in ( select user_name from #tmp ) drop table #tmp ''' % (start_time,end_time) user_id_in_sub=helper_sql_server.fetch_set(conn_config=config.conn_stc,sql=sql) user_id_in_sub=set([str(user_id) for user_id in user_id_in_sub]) for i in range(date_length,-17,-1): date_temp=helper_regex.date_add(today,-i) shabik_5_collection=helper_mysql.get_raw_collection_from_key(oem_name='STC',category='moagent', \ key='app_page_only_shabik_5_daily_visitor_unique',sub_key='', \ date=date_temp,table_name='raw_data',db_conn=None) shabik_5_collection=shabik_5_collection & new_user_collection for user_id in shabik_5_collection: user_login_history.setdefault(user_id,'') user_login_history[user_id]+='5' user_last_login_date.setdefault(user_id,'') user_last_login_date[user_id]=date_temp shabik_360_collection=helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_daily_visitor_unique',sub_key='', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None) shabik_360_collection=shabik_360_collection & new_user_collection for user_id in shabik_360_collection: user_login_history.setdefault(user_id,'') user_login_history[user_id]+='6' user_last_login_date.setdefault(user_id,'') user_last_login_date[user_id]=date_temp #calculate """ target_groups_names=[ '1.More than 2 weeks users using Shabik 360 (Totally New User to Shabik) [only using 360]', '2.Users who Shifted from Shabik360 to Shabik 5 [for each at least using 3 days, still in sub]', '3.Unsubscribed users of Shabik 360 [last using 360 for >=7 days and then unsub]', '4.Users who uses Shabik 5 more than 2 weeks [actually is online for >=14 days]', '5.Users who shifted from Shabik 5 to Shabik 360 [for each at least using 3 days, still in sub]', '6.User base of new user in last 50 days, which is used to generate above lists', ] target_groups=[ [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(6{14,})$')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(6{3,}5{3,}$)')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(6{7,}$)') and user_id in user_id_in_sub], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(5{14,}$)')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(5{3,}6{3,}$)') and user_id in user_id_in_sub], [user_id for user_id,sequence in user_login_history.iteritems()], ] target_groups_names={ 'User only use Shabik 360': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(6+)$')], 'User only use Shabik 360 [more than 10d]': , 'User only use Shabik 5', 'User only use Shabik 5 [more than 10d]', 'User use both Shabik 360 / Shabik 5', 'User used both and choosed Shabik 5 [recently used only Shabik 5 for 5d]', 'User used both and choosed Shabik 5 [recently used only Shabik 360 for 5d]', } target_groups=[ [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(6{10,})$')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(5+)$')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(5{10,})$')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)') and helper_regex.extract(sequence,r'(5{5,})$')], [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)') and helper_regex.extract(sequence,r'(6{5,})$')], ] """ threshold_of_settle_down='5' target_groups={ '1.new_user': [user_id for user_id,sequence in user_login_history.iteritems()], '2.new_user_start_from_5': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(5)')], '3.new_user_start_from_360': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(6)')], '4.new_user_only_5': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(5+)$')], '5.new_user_only_360': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'^(6+)$')], '6.new_user_both': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)')], '7.new_user_both_and_finally_5': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)') and helper_regex.extract(sequence,'(5{'+threshold_of_settle_down+',})$')], '8.new_user_both_and_finally_360': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)') and helper_regex.extract(sequence,'(6{'+threshold_of_settle_down+',})$')], '9.new_user_both_and_not_stable': [user_id for user_id,sequence in user_login_history.iteritems() if helper_regex.extract(sequence,r'(56|65)') and not helper_regex.extract(sequence,'(5{'+threshold_of_settle_down+',})$') and not helper_regex.extract(sequence,'(6{'+threshold_of_settle_down+',})$')], } #export keys=sorted(target_groups.keys()) for key in keys: user_id_collection=target_groups[key] print key print 'size:',len(user_id_collection) print '[last login date - msisdn - sub status - login history]' user_id_collection.sort(key=lambda user_id:user_last_login_date[user_id],reverse=True) for user_id in user_id_collection: print user_last_login_date[user_id],'\t',user_id_to_msisdn[user_id],'\t','sub' if user_id in user_id_in_sub else 'unsub','\t',user_login_history[user_id] for key in keys: user_id_collection=target_groups[key] print '==',key,'==' print 'size:',len(user_id_collection) print 'unsub:',len([user_id for user_id in user_id_collection if not user_id in user_id_in_sub]) """
def stat_login(): global date_min,date_max,base_user_sets oem_name='All' stat_category='daily_active_user_retain' db_name='raw_data_login_trend' # you can change day range (30 days) date_max=helper_regex.date_add(helper_regex.get_date_str_now(),-1) date_min=helper_regex.date_add(date_max,-30) for i in range(1,10000): current_date=helper_regex.date_add(date_min,i) print 'current date',current_date if current_date>date_max: break # new user set from db (overall daily active user) new_user_set=gumi_helper_user.get_user_ids_created_by_date(current_date) # daily active user SG active_user_sg = helper_mysql.get_raw_collection_from_key(oem_name='Gumi_puzzle', \ category='user',key='live_log_by_country_daily_uid_unique_collection_id',sub_key='SG', \ date=current_date, \ table_name='raw_data',db_conn=None) # daily active user US active_user_us = helper_mysql.get_raw_collection_from_key(oem_name='Gumi_puzzle', \ category='user',key='live_log_by_country_daily_uid_unique_collection_id',sub_key='US', \ date=current_date, \ table_name='raw_data',db_conn=None) # daily active user PL active_user_pl = helper_mysql.get_raw_collection_from_key(oem_name='Gumi_puzzle', \ category='user',key='live_log_by_country_daily_uid_unique_collection_id',sub_key='PL', \ date=current_date, \ table_name='raw_data',db_conn=None) # daily active user Unknow IP active_user_zz = helper_mysql.get_raw_collection_from_key(oem_name='Gumi_puzzle', \ category='user',key='live_log_by_country_daily_uid_unique_collection_id',sub_key='ZZ', \ date=current_date, \ table_name='raw_data',db_conn=None) base_user_sets={ 'pt-new-user-':new_user_set, 'pt-new-user-SG':new_user_set & active_user_sg, 'pt-new-user-US':new_user_set & active_user_us, 'pt-new-user-PL':new_user_set & active_user_pl, 'pt-new-user-ZZ':new_user_set & active_user_zz } for k,user_set in base_user_sets.iteritems(): k=k.replace('*','') # calculate total print 'user base of',k,':',len(user_set) key='active_user_initial_%s_total_unique' % (k,) #sub_key = k[-2:] #if sub_key.find('-')>-1: # sub_key='' helper_mysql.put_raw_data(oem_name,stat_category,key,'',len(user_set),db_name,current_date) helper_mysql.put_collection(collection=user_set,oem_name=oem_name,category=stat_category, \ key=key,sub_key='',date=current_date,table_name=db_name) # calculate ranges=[(1,8,1),(1,30,7),(1,60,14)] for r in ranges: start=r[0] end=r[1] step=r[2] accumulative_logined_user={ 'pt':set([]), } for i in range(start,end,step): print start print end logined_user={ 'pt':set([]), } for day_delta in range(i,i+step): target_date=helper_regex.date_add(current_date,day_delta) collection = helper_mysql.get_raw_collection_from_key(oem_name='Gumi_puzzle', \ category='user',key='live_log_daily_uid_unique_collection_id',sub_key='', \ date=target_date, \ table_name='raw_data',db_conn=None) logined_user['pt'] = logined_user['pt'] | collection for k1,v1 in logined_user.iteritems(): accumulative_logined_user[k1] |= v1 for k,user_set in base_user_sets.iteritems(): k=k.replace('*','') logined_user_temp=set([]) if k.find('pt')>-1: logined_user_temp=logined_user['pt'] accumulative_logined_user_temp=accumulative_logined_user['pt'] base_user_logined_user= user_set & logined_user_temp key='daily_active_user_'+str(step)+'_day_logined_%s_total_unique' % (k,) helper_mysql.put_raw_data(oem_name,stat_category,key,i,len(base_user_logined_user),db_name,current_date) base_user_no_logined_user= user_set - accumulative_logined_user_temp key='daily_active_user_'+str(step)+'_day_no_logined_%s_total_unique' % (k,) helper_mysql.put_raw_data(oem_name,stat_category,key,i,len(base_user_no_logined_user),db_name,current_date) return
def export(): today=helper_regex.date_add(helper_regex.get_date_str_now(),-1) # new user stc start_time=helper_regex.date_add(today,-30)+' 05:00:00' end_time=helper_regex.date_add(today,-1)+' 05:00:00' sql=r''' SELECT [user_id],phone FROM [mozone_user].[dbo].[Profile] with(nolock) where [creationDate]>='%s' and [creationDate]<'%s' and user_name like '%%shabik.com%%' and phone not like '+966%%' and phone<>'' ''' % (start_time,end_time) new_user_msisdn_dict=helper_sql_server.fetch_dict(conn_config=config.conn_stc,sql=sql) new_user_msisdn_dict=dict((str(i),j) for i,j in new_user_msisdn_dict.iteritems()) print len(new_user_msisdn_dict) # old user stc date_length=30 start_time=helper_regex.date_add(today,-90)+' 05:00:00' end_time=helper_regex.date_add(today,-30)+' 05:00:00' sql=r''' SELECT [user_id],phone FROM [mozone_user].[dbo].[Profile] with(nolock) where [creationDate]>='%s' and [creationDate]<'%s' and user_name like '%%shabik.com%%' and phone not like '+966%%' and phone<>'' ''' % (start_time,end_time) old_user_msisdn_dict=helper_sql_server.fetch_dict(conn_config=config.conn_stc,sql=sql) old_user_msisdn_dict=dict((str(i),j) for i,j in old_user_msisdn_dict.iteritems()) print len(old_user_msisdn_dict) # daily active user set date_temp=helper_regex.date_add(today,-1) target_sets={ 'JME':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='JME', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'S60-3':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='S60-3', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'S60-5':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='S60-5', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'Android':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='Android', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'iOS':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='iOS', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'BlackBerry':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_by_morange_version_type_daily_user_unique',sub_key='BlackBerry', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), 'All Client':helper_mysql.get_raw_collection_from_key(oem_name='Shabik_360',category='moagent', \ key='app_page_daily_visitor_unique',sub_key='', \ date=date_temp,table_name='raw_data_shabik_360',db_conn=None), } for k,total_active_collection in target_sets.iteritems(): old_user_msisdn_set=set([msisdn for user_id,msisdn in old_user_msisdn_dict.iteritems() if user_id in total_active_collection]) new_user_msisdn_set=set([msisdn for user_id,msisdn in new_user_msisdn_dict.iteritems() if user_id in total_active_collection]) print print '## Non-STC Old Users',k for msisdn in list(old_user_msisdn_set)[0:300]: if len(msisdn)>10: print msisdn print print '## Non-STC New Users',k for msisdn in list(new_user_msisdn_set)[0:300]: if len(msisdn)>10: print msisdn