def get_cached_sql_result_as_dict(sql,db_conn):
if db_conn['db_type'] not in ('sql_server','mysql'):
raise Error('db_type error')
unique_key=helper_math.md5(','.join(sorted(db_conn.values()))+sql)
cached_result=helper_mysql.get_dict_of_raw_collection_from_key(oem_name='',category='', key='',\
sub_key=unique_key,date='',table_name='raw_data_cache_sql_result',db_conn=None)
if cached_result:
print 'get from cache:',unique_key
return cached_result
if db_conn['db_type']=='sql_server':
cached_result=helper_sql_server.fetch_dict(conn_config=db_conn,sql=sql)
else:
cached_result=helper_mysql.fetch_dict(db_conn=db_conn,sql=sql)
helper_mysql.put_collection_with_value(collection=cached_result,oem_name='',category='',key='',sub_key=unique_key, \
table_name='raw_data_cache_sql_result',date='',created_on=None,db_conn=None)
print 'push into cache:',unique_key
return cached_result
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 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
