def _consumer_loyalty_interactions_summary(report_date, from_date, to_date,
business_account_id: int,
db_date_format):
# Dates are converted to MySQL DB date (as per config param). This is to avoid sending %d kind of string
# for SQLAlchemy to handle
if not dt.validate_date_format(report_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(from_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(to_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
arguments = locals()
sql = """select
store_id
,interaction_type
,loyalty_currency
,currency
,time_zone
,interaction_type_category
,sum(currency_value) total_value
,count( *) number_of_events
, count(distinct business_consumer_id ) distinct_consumer_events
from pika_dm.rt_consumer_events
where business_account_id ={business_account_id}
and interaction_date between \'{from_date}\' and \'{to_date}\'
group by
store_id
,interaction_type
,loyalty_currency
,currency
,time_zone
,interaction_type_category
"""
formated_sql = sql.format(**arguments)
print(formated_sql)
try:
results = db.engine.execute(text(formated_sql), ())
result_set = results.fetchall()
except Exception as e:
raise e
df = pd.DataFrame(result_set)
if df.empty is True:
return pd.DataFrame(None)
df.columns = results.keys()
return df
def __consumer_tags_summary(report_date, from_date, to_date,
business_account_id: int):
# Dates are converted to MySQL DB date (as per config param). This is to avoid sending %d kind of string
# for SQLAlchemy to handle
print(to_date, from_date)
if not dt.validate_date_format(report_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(from_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(to_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
arguments = locals()
sql = """select gender, recency,coalesce(age_group,'UNDEFINED') age_group, frequency, monetary, clv_1year_level,clv_3year_level,
badge , attributed_store_id,
count(distinct(consumer_id)) count,
SUM(
CASE
WHEN (registered_on BETWEEN \'{from_date}\' AND \'{to_date}\') THEN 1
else 0
end ) new_consumer_count
from pika_dm.dim_business_consumer
where business_account_id = {business_account_id}
and (\'{report_date}\' between start_date AND end_date )
and (registered_on between \'{from_date}\' and \'{to_date}\')
group by gender, recency,age_group, frequency, monetary, clv_1year_level,clv_3year_level,
badge,attributed_store_id"""
formated_sql = sql.format(**arguments)
print(formated_sql)
try:
results = db.engine.execute(formated_sql, ())
result_set = results.fetchall()
except Exception as e:
raise e
df = pd.DataFrame(result_set)
if df.empty is True:
return None
df.columns = results.keys()
return df
def __reward_activity_trend(business_account_id, from_date, to_date,
date_params_cd, store_ids):
# return {"message": "there is no get request for the API post"}, 404
print(business_account_id, from_date, to_date, date_params_cd, store_ids)
if not dt.validate_date_format(from_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
from_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(to_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
to_date, current_config.REPORT_DB_DATE_FORMAT)
arguments = locals()
reward_dict = {}
################## Main json data ###################
reward_dict['report_name'] = 'reward_trend'
reward_dict['date_param_cd'] = date_params_cd
reward_dict['aggregation_level'] = 'daily'
reward_dict['store_id'] = store_ids
report_duration_dict = dict()
start_date = from_date
end_date = to_date
report_duration_dict['start_date'] = start_date
report_duration_dict['end_date'] = end_date
reward_dict['report_duration'] = report_duration_dict
######### have to use to_date and from date here
sql = """select interaction_date as date,interaction_redeem_count as redemptions,
interaction_redeem_points as points_redeemed,interaction_purchase_count as purchases,
interaction_earn_points as points_earned,interaction_checkin_count as check_ins ,
consumer_new_count as new_customers
from pika_dm.agg_buss_store_stats_daily
where interaction_date between '2014-08-01' and '2014-12-31'
and business_account_id={business_account_id} """
if store_ids is None:
a = ' group by interaction_date,interaction_redeem_count,interaction_redeem_points, ' \
' interaction_purchase_count,interaction_earn_points,interaction_checkin_count,consumer_new_count '
s = sql + a
else:
# sql_query = 'select name from studens where id in (' + ','.join(map(str, l)) + ')'
s = sql + ' and store_id in (' + ','.join( map(str, store_ids)) + ') group by interaction_date,interaction_redeem_count,interaction_redeem_points, ' \
' interaction_purchase_count,interaction_earn_points,interaction_checkin_count,consumer_new_count '
formatted_sql = s.format(**arguments)
print(formatted_sql)
data_dict = {}
sub_list = []
try:
results = db.engine.execute(formatted_sql)
result_set = results.fetchall()
print(result_set)
except Exception as e:
raise e
for data in result_set:
data_dict = {
'date': str(data['date']),
'redemptions': str(data['redemptions']),
'points_redeemed': str(data['points_redeemed']),
'purchases': str(data['purchases']),
'points_earned': str(data['points_earned']),
'check_ins': str(data['check_ins']),
'new_customers': str(data['new_customers'])
}
print(data_dict)
sub_list.append(data_dict.copy())
print(sub_list)
print(sub_list)
reward_dict['data'] = sub_list
output_result = json.dumps(reward_dict)
loaded_json = json.loads(output_result)
return loaded_json
def __reward_summary(business_account_id, date_param_cd, lower_bound,
upper_bound, prev_lower_bound, prev_upper_bound):
# Dates are converted to MySQL DB date (as per config param). This is to avoid sending %d kind of string
# for SQLAlchemy to handle
if not dt.validate_date_format(lower_bound,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
lower_bound, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(upper_bound,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
upper_bound, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(prev_lower_bound,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
prev_lower_bound, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(prev_upper_bound,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
prev_upper_bound, current_config.REPORT_DB_DATE_FORMAT)
main_dict = {}
arguments = locals()
main_dict['api_name'] = 'reward_summary'
main_dict['date_param_cd'] = date_param_cd
###################### current ########################
sql = """select interaction_date as date,interaction_redeem_count as redemptions,
interaction_redeem_points as points_redeemed,interaction_purchase_count as purchases,
interaction_earn_points as points_earned,interaction_checkin_count as check_ins ,
consumer_new_count as new_customers
from pika_dm.agg_buss_store_stats_daily
where interaction_date between '2014-08-01' and '2014-12-31'
and business_account_id={business_account_id}
group by interaction_date,interaction_redeem_count,interaction_redeem_points,
interaction_purchase_count,interaction_earn_points,interaction_checkin_count,consumer_new_count """
formatted_sql = sql.format(**arguments)
print(formatted_sql)
data_dict = {}
sub_list = []
try:
results = db.engine.execute(formatted_sql)
result_set = results.fetchall()
print(result_set)
except Exception as e:
raise e
for data in result_set:
data_dict = {
'redemptions': str(data['redemptions']),
'points_redeemed': str(data['points_redeemed']),
'purchases': str(data['purchases']),
'points_earned': str(data['points_earned']),
'check_ins': str(data['check_ins']),
'new_customers': str(data['new_customers'])
}
print(data_dict)
#sub_list.append(data_dict.copy())
#print(sub_list)
#print(sub_list)
main_dict['current'] = data_dict
###################### PREVIOUS #####################################
sql = """select interaction_date as date,interaction_redeem_count as redemptions,
interaction_redeem_points as points_redeemed,interaction_purchase_count as purchases,
interaction_earn_points as points_earned,interaction_checkin_count as check_ins ,
consumer_new_count as new_customers
from pika_dm.agg_buss_store_stats_daily
where interaction_date between '2014-08-01' and '2014-12-31'
and business_account_id={business_account_id}
group by interaction_date,interaction_redeem_count,interaction_redeem_points,
interaction_purchase_count,interaction_earn_points,interaction_checkin_count,consumer_new_count """
formatted_sql = sql.format(**arguments)
print(formatted_sql)
data_dict = {}
sub_list = []
try:
results = db.engine.execute(formatted_sql)
result_set = results.fetchall()
print(result_set)
except Exception as e:
raise e
for data in result_set:
data_dict = {
'redemptions': str(data['redemptions']),
'points_redeemed': str(data['points_redeemed']),
'purchases': str(data['purchases']),
'points_earned': str(data['points_earned']),
'check_ins': str(data['check_ins']),
'new_customers': str(data['new_customers'])
}
print(data_dict)
# sub_list.append(data_dict.copy())
# print(sub_list)
# print(sub_list)
main_dict['previous'] = data_dict
output_result = json.dumps(main_dict)
loaded_json = json.loads(output_result)
return loaded_json
def __consumer_activites_timeseries_daily(report_date,
from_date,
to_date,
business_account_id: int,
granularity='daily'):
if not dt.validate_date_format(report_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(from_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(to_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
arguments = locals()
# revenue, points_earned, visits(unique member counts)
sql = """select
BC.gender,
BC.recency,
BC.frequency,
BC.monetary,
BC.clv_1year_level,
BC.clv_3year_level,
CE.interaction_type,
CE.interaction_type_category,
SUM(BC.lifetime_spent_type1) total_lifetime_spent,
AVG(BC.avg_spent_per_trnx_type1) avg_spent_per_transaction,
COUNT(distinct CE.consumer_id) consumer_count
from
rt_consumer_events CE
inner join
dim_business_consumer BC
on CE.business_consumer_s_key =BC.s_key
where CE.business_account_id=14
and CE.store_id =63
and CE.interaction_date BETWEEN '2013-01-01' AND '2018-12-31'
group by
BC.gender,
BC.recency,
BC.frequency,
BC.monetary,
BC.clv_1year_level,
BC.clv_3year_level,
CE.interaction_type,
CE.interaction_type_category
"""
formated_sql = sql.format(**arguments)
# get the left table for series
all_date_df = pd.DataFrame(
DateModel.get_days_between_dates(from_date, to_date))
# get all tags - series in this case
all_tags_df = pd.DataFrame(ConsumerTagsModel.get_tags())
# all KPI list
all_kpi_df = pd.DataFrame(InteractionTypeModel.get_all_combination())
# get real data
try:
results = db.engine.execute(formated_sql, ())
result_set = results.fetchall()
except Exception as e:
raise e
data_df = pd.DataFrame(result_set)
all_tags_data = pd.merge()
def __consumer_tags_counts_trends_dly(report_date, from_date, to_date,
business_account_id: int,
db_date_format):
# Dates are converted to MySQL DB date (as per config param). This is to avoid sending %d kind of string
# for SQLAlchemy to handle
if not dt.validate_date_format(report_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(from_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
if not dt.validate_date_format(to_date,
current_config.REPORT_DB_DATE_FORMAT):
raise ValueError(
"report_date: %s failed validation. Expected Format is %s" %
report_date, current_config.REPORT_DB_DATE_FORMAT)
arguments = locals()
# sql = """select gender, recency,coalesce(age_group,'UNDEFINED') age_group,
# DATE_FORMAT(registered_on,\'%Y-%m-%d\') registered_on
# , frequency, monetary, clv_1year_level,clv_3year_level,
# badge , attributed_store_id,
# count(distinct(consumer_id)) total_consumer_count,
# SUM(
# CASE
# WHEN (registered_on BETWEEN \'{from_date}\' AND \'{to_date}\') THEN 1
# else 0
# end ) new_consumer_count
# from dim_business_consumer
# where business_account_id = {business_account_id}
# and (\'{report_date}\' between sys_start_date AND sys_end_date )
# and (registered_on between \'{from_date}\' and \'{to_date}\')
# group by gender, recency,age_group, registered_on, frequency, monetary, clv_1year_level,
# clv_3year_level,badge,attributed_store_id"""
#
sql = """ select
store_id attributed_store_id,
DATE_FORMAT(interaction_date,\'%Y-%m-%d\') 'date',
interaction_checkin_count checkin,
interaction_purchase_count purchase,
interaction_registration_count registration,
consumer_new_count new_consumers,
consumer_total_count existing_consumers,
consumer_platinum_count platinum,
consumer_high_risk_count high_risk,
interaction_redeem_count redemption,
time_zone
from
pika_dm.agg_buss_store_stats_daily
where business_account_id = {business_account_id}
and (interaction_date between '2014-05-24' and '2014-12-31') """
formated_sql = sql.format(**arguments)
print(formated_sql)
try:
results = db.engine.execute(text(formated_sql), ())
result_set = results.fetchall()
except Exception as e:
raise e
df = pd.DataFrame(result_set)
if df.empty is True:
return None
df.columns = results.keys()
return df