def update_forex_rates(self):
app.logger.info('Getting forex rates')
response = requests.get('https://api.fixer.io/latest?base=EUR')
data = response.json()
rates = data['rates']
now = datetime.now(pytz.timezone('Europe/Copenhagen'))
for currency, rate in rates.iteritems():
forex_rate = ForExRates.select(ForExRates.c.currency == currency).execute().first()
if forex_rate:
ForExRates.update().where(ForExRates.c.currency == currency).values(rate=rate, updated_at=now).execute()
else:
db_conn.execute(ForExRates.insert(), currency=currency, rate=rate)
def backend_event():
account = request.args.get('account')
event = request.args.get('event')
created_at = datetime.utcnow()
value = request.args.get('value')
status = request.args.get('status')
db_conn.execute(BackendEvent.insert(),
account=account,
event=event,
created_at=created_at,
value=value,
status=status)
return __get_gif()
def email_send():
account = request.args.get('account')
recipient = request.args.get('recipient')
sender = request.args.get('sender')
subject = request.args.get('subject')
tracking_uuid = request.args.get('tracking_uuid')
send_date = datetime.utcnow()
db_conn.execute(Emails.insert(),
account=account,
recipient=recipient,
sender=sender,
subject=subject,
tracking_uuid=tracking_uuid,
send_date=send_date)
return __get_gif()
def replicate_pb_transactions(self):
app.logger.info('Starting replication on PB')
account_name = config['pb_account_name']
last_id = select([
func.max(Transactions.c.original_id)
]).where(Transactions.c.account == account_name).execute().first()
last_id = last_id[0] if last_id[0] else 0
charges = []
with SSHTunnelForwarder(
(config['pb_ssh_host'], int(config['pb_ssh_port'])),
ssh_username=config['pb_ssh_username'],
ssh_password=config['pb_ssh_password'],
remote_bind_address=('127.0.0.1', 3306),
local_bind_address=('127.0.0.1', 3307)) as server:
connection_string = config['pb_connection_string'] + '?charset=utf8'
source_engine = create_engine(connection_string)
source_conn = source_engine.connect()
sql = text('''SELECT * FROM maxpay_charge_new
WHERE maxpay_charge_new.id > :last_id
ORDER BY maxpay_charge_new.id ASC
LIMIT :count''')
result = source_conn.execute(sql, last_id=int(last_id),
count=100).fetchall()
for charge in result:
sql = text(
'''SELECT temp_users.webid as webid, webid.country AS country FROM temp_users
JOIN webid ON webid.web_id = temp_users.webid
WHERE temp_users.cust_id = :customer_id''')
user_info = source_conn.execute(
sql, customer_id=charge['merchant_user_id']).first()
if not user_info:
sql = text(
'''SELECT users.webid AS webid, webid.country AS country FROM users
JOIN webid ON webid.web_id = users.webid
WHERE users.customer_id = :customer_id''')
user_info = source_conn.execute(
sql, customer_id=charge['merchant_user_id']).first()
if not user_info:
user_info = dict(webid=None, country=None)
charge_temp = dict(charge)
charge_temp['webid'] = user_info['webid']
charge_temp['webid_country'] = user_info['country'].title(
) if user_info['country'] else None
charges.append(charge_temp)
source_conn.close()
values = []
for charge in charges:
app.logger.info('Inserting transaction id = %i, account = %s' %
(charge['id'], account_name))
print 'Inserting transaction id = %i, account = %s' % (
charge['id'], account_name)
created_at = charge['date_created'] - timedelta(hours=1)
response = json.loads(charge['charges_response'])
mid_name = response['custom_fields']['custom_mid_name']
values.append(
dict(account=account_name,
merchant_user_id=charge['merchant_user_id'],
transaction_type=charge['transaction_type'],
mode=charge['mode'],
code=int(charge['code']) if charge['code'] else 0,
amount=charge['amount'],
currency=charge['currency'],
card_holder=charge['card_holder'],
brand=charge['brand'],
bank=charge['bank'],
level=charge['level'],
type=charge['type'],
bin=charge['bin'],
last=charge['last'],
exp_month=charge['exp_month'],
exp_year=charge['exp_year'],
bank_id=charge['bank_id'],
bank_authcode=charge['bank_authcode'],
bank_time=charge['bank_time'],
charge_time=charge['charge_time'],
token=charge['token'],
reference=charge['reference'],
base_reference=charge['base_reference'],
transaction_unique_id=charge['transaction_unique_id'],
fraudulent=charge['is_fraudalerts'],
created_at=created_at,
response=charge['charges_response'],
webid=charge['webid'],
country=charge['webid_country'],
original_id=charge['id'],
status=charge['status'],
mid_name=mid_name))
if values:
db_conn.execute(Transactions.insert(), values)
app.logger.info('Finished replication on PB')
def replicate_bb_chargebacks(self):
app.logger.info('Starting replication on BB')
account_name = config['bb_account_name']
last_id = select([
func.max(Chargebacks.c.original_id)
]).where(Chargebacks.c.account == account_name).execute().first()
last_id = last_id[0] if last_id[0] else 0
connection_string = config['bb_connection_string'] + '?charset=utf8'
source_engine = create_engine(connection_string)
source_conn = source_engine.connect()
sql = text(
'''SELECT maxpay_chargeback_new.*, maxpay_charge_new.merchant_user_id FROM maxpay_chargeback_new
JOIN maxpay_charge_new ON maxpay_chargeback_new.base_reference = maxpay_charge_new.reference
WHERE maxpay_chargeback_new.id > :last_id
ORDER BY maxpay_chargeback_new.id ASC
LIMIT :count''')
result = source_conn.execute(sql, last_id=int(last_id),
count=100).fetchall()
chargebacks = []
for chargeback in result:
sql = text(
'''SELECT temp_users.webid as webid, webid.country AS country FROM temp_users
JOIN webid ON webid.web_id = temp_users.webid
WHERE temp_users.cust_id = :customer_id''')
user_info = source_conn.execute(
sql, customer_id=chargeback['merchant_user_id']).first()
if not user_info:
sql = text(
'''SELECT users.webid AS webid, webid.country AS country FROM users
JOIN webid ON webid.web_id = users.webid
WHERE users.customer_id = :customer_id''')
user_info = source_conn.execute(
sql, customer_id=chargeback['merchant_user_id']).first()
if not user_info:
user_info = dict(webid=None, country=None)
chargeback_temp = dict(chargeback)
chargeback_temp['webid'] = user_info['webid']
chargeback_temp['webid_country'] = user_info['country'].title(
) if user_info['country'] else None
chargebacks.append(chargeback_temp)
source_conn.close()
values = []
for chargeback in chargebacks:
app.logger.info('Inserting chargeback id = %i, account = %s' %
(chargeback['id'], account_name))
created_at = chargeback['date_created'] - timedelta(hours=1)
response = json.loads(chargeback['charge_response'])
mid_name = response['transaction']['custom_fields'][
'custom_mid_name']
card_brand = response['transaction']['card']['brand']
values.append(
dict(account=account_name,
merchant_user_id=chargeback['merchant_user_id'],
webid=chargeback['webid'],
country=chargeback['webid_country'],
original_id=chargeback['id'],
status=chargeback['status'],
type=chargeback['type'],
mode=chargeback['mode'],
amount=chargeback['amount'],
bank_time=chargeback['bank_time'],
currency=chargeback['currency'],
bank_id=chargeback['bank_id'],
bank_authcode=chargeback['bank_authcode'],
bank_update_time=chargeback['bank_update_time'],
reference=chargeback['reference'],
base_reference=chargeback['base_reference'],
transaction_unique_id=chargeback['transaction_unique_id'],
created_at=created_at,
mid_name=mid_name,
response=chargeback['charge_response'],
time=chargeback['time'],
brand=card_brand))
if values:
db_conn.execute(Chargebacks.insert(), values)
app.logger.info('Finished replication on BB')
def replicate_bb_users(self):
app.logger.info('Starting replication on BB users')
account_name = config['bb_account_name']
last_id = select([
func.max(Users.c.original_id)
]).where(Users.c.account == account_name).execute().first()
last_id = last_id[0] if last_id[0] else 0
connection_string = config['bb_connection_string'] + '?charset=utf8'
source_engine = create_engine(connection_string)
source_conn = source_engine.connect()
sql = text('''SELECT * FROM users
WHERE users.id > :last_id
ORDER BY users.id ASC
LIMIT :count''')
result = source_conn.execute(sql, last_id=int(last_id),
count=100).fetchall()
users = []
for user in result:
sql = text('''SELECT country FROM webid WHERE web_id = :webid''')
webid_info = source_conn.execute(sql, webid=user['webid']).first()
if not webid_info:
webid_info = dict(country=None)
user_temp = dict(user)
user_temp['country'] = webid_info['country'].title(
) if webid_info['country'] else None
users.append(user_temp)
source_conn.close()
values = []
for user in users:
app.logger.info('Inserting users id = %i, account = %s' %
(user['id'], account_name))
created_at = user['created_date'] - timedelta(hours=1)
values.append(
dict(account=account_name,
customer_id=user['customer_id'],
email=user['email'],
fname=user['fname'],
lname=user['lname'],
webid=user['webid'],
country=user['country'],
pubid=user['pubid'],
subid=user['subid'],
utm_medium=user['utm_medium'],
utm_term=user['utm_term'],
utm_content=user['utm_content'],
utm_campaign=user['utm_campaign'],
referrer_url=user['referrer_url'],
ip_address=user['ip_addr'],
click_id=user['click_id'],
user_agent=user['user_agent'],
original_id=user['id'],
created_at=created_at))
if values:
db_conn.execute(Users.insert(), values)
app.logger.info('Finished replication on BB users')
def replicate_bb_log_data(self):
app.logger.info('Starting replication on BB log_data')
account_name = config['bb_account_name']
last_id = select([
func.max(LogData.c.original_id)
]).where(LogData.c.account == account_name).execute().first()
last_id = last_id[0] if last_id[0] else 0
connection_string = config['bb_connection_string'] + '?charset=utf8'
source_engine = create_engine(connection_string)
source_conn = source_engine.connect()
sql = text('''SELECT * FROM log_data
WHERE log_data.id > :last_id
AND log_data.created_date > '2017-12-31 23:59:59'
ORDER BY log_data.id ASC
LIMIT :count''')
result = source_conn.execute(sql, last_id=int(last_id),
count=400).fetchall()
log_data = []
for datum in result:
sql = text('''SELECT country FROM webid WHERE web_id = :webid''')
webid_info = source_conn.execute(sql, webid=datum['webid']).first()
if not webid_info:
webid_info = dict(country=None)
log_data_temp = dict(datum)
log_data_temp['country'] = webid_info['country'].title(
) if webid_info['country'] else None
log_data.append(log_data_temp)
source_conn.close()
values = []
for datum in log_data:
app.logger.info('Inserting log_data id = %i, account = %s' %
(datum['id'], account_name))
created_at = datum['created_date'] - timedelta(hours=1)
values.append(
dict(account=account_name,
step=datum['step'],
customer_id=datum['cust_id'],
email=datum['email'],
fname=datum['fname'],
lname=datum['lname'],
webid=datum['webid'],
country=datum['country'],
pubid=datum['pubid'],
subid=datum['subid'],
utm_medium=datum['utm_medium'],
utm_term=datum['utm_term'],
utm_content=datum['utm_content'],
utm_campaign=datum['utm_campaign'],
referrer_url=datum['referrer_url'],
ip_address=datum['ip_addr'],
click_id=datum['click_id'],
user_agent=datum['user_agent'],
original_id=datum['id'],
created_at=created_at))
if values:
db_conn.execute(LogData.insert(), values)
app.logger.info('Finished replication on BB log_data')
def get_bb_reports(self):
ad_account = AdAccount(config['facebook_bb_ad_account_id'])
denmark_now = datetime.now(
pytz.timezone('Europe/Copenhagen')).replace(tzinfo=None)
for campaign in ad_account.get_campaigns():
insights = campaign.get_insights(params={
'level':
'ad',
'date_preset':
'today',
'breakdowns':
['hourly_stats_aggregated_by_advertiser_time_zone']
},
fields=[
'impressions',
'inline_link_clicks',
'inline_link_click_ctr',
'relevance_score',
'spend',
'campaign_name',
'adset_name',
'ad_name',
'actions',
'date_start',
'date_stop',
'clicks',
'cpc',
'cpm',
'ctr',
])
for insight in insights:
hour_range = insight[
'hourly_stats_aggregated_by_advertiser_time_zone']
time_from = hour_range.split(' - ')[0]
time_to = hour_range.split(' - ')[1]
mnl_time_from = insight.get('date_start') + ' ' + time_from
mnl_time_to = insight.get('date_stop') + ' ' + time_to
mnl_time_from = datetime.strptime(mnl_time_from,
'%Y-%m-%d %H:%M:%S')
mnl_time_to = datetime.strptime(mnl_time_to,
'%Y-%m-%d %H:%M:%S')
denmark_time_from = mnl_time_from - timedelta(hours=8)
denmark_time_to = mnl_time_to - timedelta(hours=8)
if denmark_time_to > denmark_now: # if period is not yet done, skip
continue
facebook_report = FacebookAdReports.select(
and_(
FacebookAdReports.c.campaign_name == insight.get(
'campaign_name'), FacebookAdReports.c.ad_set_name
== insight.get('adset_name'),
FacebookAdReports.c.ad_name == insight.get('ad_name'),
FacebookAdReports.c.since == denmark_time_from,
FacebookAdReports.c.until ==
denmark_time_to)).execute().first()
if facebook_report is not None: # if entry already exists, skip
continue
offsite_conversion = 0
complete_registrations = 0
relevance_score = insight.get('relevance_score').get(
'score') if insight.get('relevance_score') else 0
actions = insight.get('actions') if insight.get(
'actions') else []
for action in actions:
if action['action_type'] == 'offsite_conversion':
offsite_conversion = action['value']
elif action[
'action_type'] == 'offsite_conversion.fb_pixel_complete_registration':
complete_registrations = action['value']
db_conn.execute(
FacebookAdReports.insert(),
account='budgetbear.net',
campaign_name=insight.get('campaign_name'),
ad_set_name=insight.get('adset_name'),
ad_name=insight.get('ad_name'),
account_currency='USD',
clicks=insight.get('clicks'),
cpc=insight.get('cpc'),
cpm=insight.get('cpm'),
ctr=insight.get('ctr'),
impressions=insight.get('impressions'),
inline_link_clicks=insight.get('inline_link_clicks'),
inline_link_click_ctr=insight.get('inline_link_click_ctr'),
spend=insight.get('spend'),
relevance_score=relevance_score,
offsite_conversion=offsite_conversion,
complete_registrations=complete_registrations,
since=denmark_time_from,
until=denmark_time_to)
def get_previous_bb_reports(self):
from analytics import metadata
from sqlalchemy import Table
FacebookAdDateQuery = Table('facebook_ad_date_query',
metadata,
autoload=True)
ad_date_query = FacebookAdDateQuery.select().execute().first()
query_date = ad_date_query['query_date']
end_of_query = '2018-02-14'
if query_date == end_of_query:
return
ad_account = AdAccount(config['facebook_bb_ad_account_id'])
mnl_now = datetime.now(pytz.timezone('Asia/Hong_Kong'))
for campaign in ad_account.get_campaigns():
insights = campaign.get_insights(params={
'level':
'ad',
'time_range': {
'since': query_date,
'until': query_date
},
'breakdowns':
['hourly_stats_aggregated_by_advertiser_time_zone']
},
fields=[
'impressions',
'inline_link_clicks',
'inline_link_click_ctr',
'relevance_score',
'spend',
'campaign_name',
'adset_name',
'ad_name',
'actions',
'date_start',
'date_stop',
'clicks',
'cpc',
'cpm',
'ctr',
])
for insight in insights:
hour_range = insight[
'hourly_stats_aggregated_by_advertiser_time_zone']
time_from = hour_range.split(' - ')[0]
time_to = hour_range.split(' - ')[1]
mnl_time_from = insight.get('date_start') + ' ' + time_from
mnl_time_to = insight.get('date_stop') + ' ' + time_to
mnl_time_from = datetime.strptime(mnl_time_from,
'%Y-%m-%d %H:%M:%S')
mnl_time_to = datetime.strptime(mnl_time_to,
'%Y-%m-%d %H:%M:%S')
denmark_time_from = mnl_time_from - timedelta(hours=8)
denmark_time_to = mnl_time_to - timedelta(hours=8)
offsite_conversion = 0
complete_registrations = 0
relevance_score = insight.get('relevance_score').get(
'score') if insight.get('relevance_score') else 0
actions = insight.get('actions') if insight.get(
'actions') else []
for action in actions:
if action['action_type'] == 'offsite_conversion':
offsite_conversion = action['value']
elif action[
'action_type'] == 'offsite_conversion.fb_pixel_complete_registration':
complete_registrations = action['value']
db_conn.execute(
FacebookAdReports.insert(),
account='budgetbear.net',
campaign_name=insight.get('campaign_name'),
ad_set_name=insight.get('adset_name'),
ad_name=insight.get('ad_name'),
account_currency='USD',
clicks=insight.get('clicks'),
cpc=insight.get('cpc'),
cpm=insight.get('cpm'),
ctr=insight.get('ctr'),
impressions=insight.get('impressions'),
inline_link_clicks=insight.get('inline_link_clicks'),
inline_link_click_ctr=insight.get('inline_link_click_ctr'),
spend=insight.get('spend'),
relevance_score=relevance_score,
offsite_conversion=offsite_conversion,
complete_registrations=complete_registrations,
since=denmark_time_from,
until=denmark_time_to)
now = datetime.now(pytz.timezone('Europe/Copenhagen'))
next_query_date = (datetime.strptime(query_date, '%Y-%m-%d') +
timedelta(days=1)).strftime('%Y-%m-%d')
FacebookAdDateQuery.update().where(
FacebookAdDateQuery.c.query_date == query_date).values(
query_date=next_query_date, updated_at=now).execute()
