def vk_refresh():
from doc_token import get_tokens
passwords = get_tokens()
token = passwords['vk']
campaigns = get_vk(token=token,
method='ads.getCampaigns',
params='account_id=1604608801')
camps_ids = ",".join([str(i['id']) for i in campaigns['response']])
date_to = str(datetime.date.today())
get_campaign_stats = get_vk(
token=token,
params=
f'ids_type=campaign&date_from=2020-04-25&date_to={date_to}&period=day&account_id=1604608801&ids={camps_ids}'
)
campaigns_dict = {str(i['id']): i['name'] for i in campaigns['response']}
time.sleep(1)
vk_table = campaign_to_table(get_campaign_stats, campaigns_dict)
time.sleep(1)
columns = [
'cmp_id', 'capmaign', 'date', 'cost', 'impressions', 'clicks', 'reach'
]
vk_df = pandas.DataFrame(vk_table, columns=columns)
time.sleep(1)
respose = get_vk_ads(token=token)
utms = dict_vk(respose)
vk_df['umt_campaing'] = vk_df['cmp_id'].apply(lambda x: utms[int(x)][0])
vk_df['project'] = vk_df['cmp_id'].apply(lambda x: utms[int(x)][1])
vk_df['date'] = vk_df['date'].apply(
lambda x: pandas.Timestamp(datetime.datetime.strptime(x, '%Y-%m-%d')))
return vk_df
def y_direct_refresh():
from doc_token import get_tokens
token = get_tokens()
dimestions = "ym:ad:<attribution>DirectOrder,ym:ad:<attribution>DirectPhraseOrCond"
metrics = "ym:ad:<currency>AdCost,ym:ad:clicks"
try:
bq_yandex = gbq_pd('YandexAds', 'marketing_bi')
q_last_date = "SELECT date(max(date)) as last_date FROM `kalmuktech.marketing_bi.YandexAds`"
last_date = bq_yandex.df_query(q_last_date).iloc[0, 0].date()
date = str(last_date)[:10]
except:
date = '2020-04-10'
bq_yandex = gbq_pd('YandexAds', 'marketing_bi')
clean_last = f"Delete FROM `kalmuktech.marketing_bi.YandexAds` where date = '{date}'"
last_date = bq_yandex.df_query(clean_last)
header_str = dimestions + "," + metrics
header_str = header_str.replace(":", "_")
header_str = header_str.replace("<attribution>", "")
header_str = header_str.replace("<currency>", "")
column_headers = header_str.split(",") + ['date']
caps = token['yandex']['caps']
caps_dirc = direct_acc(**caps)
probk_list = caps_dirc.get_lists(date, dimestions, metrics)
direct_table_full = pandas.DataFrame(probk_list, columns=column_headers)
if probk_list == []:
return []
direct_table_full['date'] = direct_table_full['date'].apply(
pandas.Timestamp)
cross_data = get_match_table(ym_class=caps_dirc)
utm_data = match_dict(cross_data)
final_table_yandex = add_utm_to_df_yandex(direct_table_full, utm_data)
return final_table_yandex
def refresh_personal_reports():
log = ""
token = get_tokens()
wf_regs_table = gbq_pd('report_regs', datasetId='wf_bi')
query_compaines = """
SELECT
users.user_id,
if(notif, users.email, False) as notif_mail,
users.phone as phone,
from_unixtime(users.create_date) as reg_date,
if(users.last_date> 0, from_unixtime(last_date),from_unixtime(create_date)) as last_dt,
if(last_date >0, '1+', 0) as visit_count,
showcase.vitrins,
showcase.domain,
ifnull(reg_sourse.reg_donner, 'Прямая') as reg_donner,
reg_sourse.reg_donner_id,
concat("https://workface.ru/ru/company/",company_id) as company_url
FROM `users`as users
left join (
SELECT
user_id as usr,
if(sum(email_messages+email_deals+email_relationships)>0, True,False) as notif
FROM `users_notifications`
group by user_id
) as nots on nots.usr = users.user_id
left join (
SELECT
user_id,
deals.showcase_id as reg_donner_id,
domain as reg_donner
FROM `deals` as deals
inner join users on users.create_date = deals.dt_create
join showcase_info as vitrins on deals.showcase_id = vitrins.showcase_id
) as reg_sourse on reg_sourse.user_id = users.user_id
left join (
SELECT
user_shw.showcase_id as showcase_id,
user_id,
domain,
1 as vitrins,
company_id
FROM (
SELECT
showcase_id,
user_id,
cmp_id as company_id
FROM (
SELECT
good_offer_id as showcase_id,
company_id as cmp_id
FROM `good_offers`
where is_showcase = 2) as jnt_cmp
join `companies` as cmp on cmp.company_id = jnt_cmp.cmp_id) as user_shw
JOIN showcase_info on showcase_info.showcase_id = user_shw.showcase_id
) as showcase on showcase.user_id = users.user_id
"""
regs_query = query_df(query_compaines, token['wf_base'])
wf_regs_table.replace(regs_query)
q_add_visits = """
SELECT
user_id,
notif_mail,
phone,
reg_date,
last_dt,
visits as visit_count,
vitrins,
domain,
reg_donner,
reg_donner_id,
company_url
FROM `kalmuktech.wf_bi.report_regs` as regs
left join (SELECT user_id as users_v, ifnull(sum(ga_sessions),0) as visits FROM `kalmuktech.wf_bi.users_id` as u
left join `kalmuktech.wf_bi.user_sess` as uses on u.u_id = uses.ga_dimension1
group by user_id) as user_visits on user_visits.users_v = regs.user_id
"""
res_plus_visits = wf_regs_table.df_query(q_add_visits)
wf_regs_table.replace(res_plus_visits)
log += f"По таблице report_regs обновилось {len(res_plus_visits)} строк \n"
wf_vitr_table = gbq_pd('report_virs', datasetId='wf_bi')
query_compaines = """
SELECT
FROM_UNIXTIME(create_date) as date,
user_id,
phone,
domain,
ifnull(deals.sums, 0 ) as deal_sums,
ifnull(deals.deals_cnt, 0) as deal_cnt,
ifnull(regs.reg_cnt, 0) as reg_cnt,
ifnull(goods.goods_cnt, 0) as goods
FROM (
SELECT
good_offer_id as showcase_id,
company_id
FROM `good_offers`
where is_showcase = 2
) as showcase
LEFT JOIN (
SELECT
showcase_id as shw_id,
sum(total_price) as sums,
count(distinct deal_id) as deals_cnt
FROM `deals`
GROUP BY shw_id
) AS deals on deals.shw_id = showcase.showcase_id
LEFT JOIN (
SELECT
count(distinct user_id) as reg_cnt,
reg_srs.showcase_id as reg_donner_id
FROM `deals` as reg_srs
inner join users on users.create_date = reg_srs.dt_create
GROUP BY reg_donner_id
) AS regs on regs.reg_donner_id = showcase.showcase_id
LEFT JOIN (
SELECT
count(distinct good_offer_id) as goods_cnt,
showcase_id as v_goods_ids
FROM `good_offers`
GROUP BY showcase_id
) as goods on goods.v_goods_ids = showcase.showcase_id
LEFT JOIN (
SELECT users.user_id as user_id,
users.phone as phone,
create_date,
company_id as cmp
FROM `users`
left join companies on users.user_id = companies.user_id
where company_id is not null
) as users on users.cmp = showcase.company_id
LEFT JOIN (
SELECT
showcase_id as sh_id,
domain
FROM `showcase_info`
) as showcase_info on showcase_info.sh_id = showcase.showcase_id
"""
regs_query = query_df(query_compaines, token['wf_base'])
wf_vitr_table.replace(regs_query)
log += f"По таблице report_virs обновилось {len(regs_query)} строк \n"
q_add_Ga_regs = """SELECT
date,
user_id,
phone,
domain,
deal_sums,
deal_cnt,
ifnull(regs, 0) as reg_cnt,
goods
FROM `kalmuktech.wf_bi.report_virs` as v
left join (SELECT ga_hostname, count(distinct user_id ) as regs FROM `kalmuktech.wf_bi.wf_hosts` as w
inner join kalmuktech.wf_bi.users_id as u on u.u_id = w.ga_dimension1
group by 1) as g on g.ga_hostname = v.domain"""
regs_query = wf_vitr_table.df_query(q_add_Ga_regs)
wf_vitr_table.replace(regs_query)
log += f"По таблице report_virs обновилось {len(regs_query)} строк \n"
query_ann = """
SELECT
utm_source,
utm_campaign,
date(date) as date,
CONCAT('https://officeicapru.amocrm.ru/leads/detail/',conts_id) AS all_leads,
status AS failed,
sale as sold,
sum
from (select conts_id as conts_id,
phone,
status,
utm_source,
utm_campaign,
utm_term,
date,
sale,
sum
from
(SELECT
DISTINCT (lead_id) as conts_id,
phone,
status,
utm_source,
utm_campaign,
utm_term,
date,
sale
FROM (
SELECT
lead_id,
status,
sale,
phone AS ml
FROM
kalmuktech.marketing_bi.base_amo_leads AS AMO_leads
JOIN
kalmuktech.marketing_bi.base_amo_contacts AS cncts
ON
cncts.conts_id = AMO_leads.contact_id
WHERE
phone != 'None') AS lead_st
JOIN (
SELECT
min(ga_date) as date,
phone,
ga_source as utm_source,
ga_campaign as utm_campaign,
ga_keyword as utm_term,
FROM (
SELECT
phone,
metrika_client_id
FROM
`kalmuktech.marketing_bi.callibri_data` AS data
WHERE
metrika_client_id IS NOT NULL
AND phone IS NOT NULL ) AS data
JOIN
`kalmuktech.marketing_bi.base_ga_cookie` AS ga
ON
data.metrika_client_id = ga.ga_dimension1
group by
2,3,4,5
) AS colibr_mail
ON
colibr_mail.phone = lead_st.ml) as tbl
LEft join (SELECT sum(otruzka_sum)/100 as sum, phone as phone_ms FROM `kalmuktech.marketing_bi.ms_sold` group by phone) as ms_sold on ms_sold.phone_ms = tbl.phone
)
union ALL
SELECT
utm_source,
utm_campaign,
date(date) as date,
CONCAT('https://officeicapru.amocrm.ru/leads/detail/',conts_id) AS all_leads,
status AS failed,
sale as sold,
0 as sum
FROM (
SELECT
DISTINCT (lead_id) as conts_id,
email,
status,
utm_source,
utm_campaign,
utm_term,
date,
sale
FROM (
SELECT
lead_id,
status,
sale,
email AS ml
FROM
kalmuktech.marketing_bi.base_amo_leads AS AMO_leads
JOIN
kalmuktech.marketing_bi.base_amo_contacts AS cncts
ON
cncts.conts_id = AMO_leads.contact_id
WHERE
email != 'None') AS lead_st
JOIN (
SELECT
min(ga_date) as date,
email,
ga_source as utm_source,
ga_campaign as utm_campaign,
ga_keyword as utm_term,
FROM (
SELECT
email,
metrika_client_id
FROM
`kalmuktech.marketing_bi.callibri_data` AS data
WHERE
metrika_client_id IS NOT NULL
and email IS NOT NULL
AND phone IS NULL ) AS data
JOIN
`kalmuktech.marketing_bi.base_ga_cookie` AS ga
ON
data.metrika_client_id = ga.ga_dimension1
group by
2,3,4,5)
AS colibr_mail
ON
colibr_mail.email = lead_st.ml)
union ALL
SELECT
ga_source,
ga_campaign,
date(ga_date),
CONCAT('https://officeicapru.amocrm.ru/leads/detail/',id) AS all_leads,
status AS status,
sale as sold,
0 as sum
FROM
kalmuktech.marketing_bi.base_tilda_forms as tilda
JOIN
`kalmuktech.marketing_bi.base_ga_cookie` AS ga
ON
tilda.cookie = ga.ga_dimension1
"""
Ann_report = gbq_pd('Ann_report', 'marketing_bi')
dates_str = Ann_report.df_query(query_ann)
import pandas
def transform_sourse(data):
dict_of_dims_date = []
for i in data.iterrows():
if i[1][1] == '(not set)' and i[1][0] == 'google':
i[1][0] = 'Google СЕО Поиск'
elif i[1][1] == '(not set)' and 'yandex' in i[1][0]:
i[1][0] = 'Яндекс СЕО Поиск'
elif i[1][0] == '(direct)':
i[1][0] = 'Прямые'
dict_of_dims_date.append(i[1])
return pandas.DataFrame(dict_of_dims_date)
dates_str = transform_sourse(dates_str)
Ann_report.replace(dates_str)
log += f"По таблице Ann_report обновилось {len(dates_str)} строк \n"
return log
def shop_icap_tables():
log = ""
token = get_tokens()['wf_base']
shop_i_cap_GA = ga_connect('195060854')
start = '2020-04-01'
end = str(datetime.date.today())
params = {
'dimetions': [
{
'name': 'ga:dimension1'
},
{
'name': 'ga:dimension2'
},
{
'name': 'ga:date'
},
],
'metrics': [{
'expression': 'ga:sessions'
}],
'dates': {
'startDate': f'{start}',
'endDate': f'{end}'
}
}
i_cap_DF_GA = shop_i_cap_GA.report_df(**params)
columns = [i.replace(':', '_') for i in i_cap_DF_GA.columns]
i_cap_DF_GA.columns = columns
i_cap_DF_GA = df_proc(i_cap_DF_GA)
df_cooks = i_cap_DF_GA.drop(columns=['ga_sessions'])
params = {
'dimetions': [{
'name': 'ga:dimension2'
}, {
'name': 'ga:date'
}, {
'name': 'ga:fullReferrer'
}, {
'name': 'ga:source'
}, {
'name': 'ga:medium'
}, {
'name': 'ga:campaign'
}, {
'name': 'ga:keyword'
}],
'metrics': [{
'expression': 'ga:sessions'
}],
'dates': {
'startDate': f'{start}',
'endDate': f'{end}'
}
}
cooks_2_sourese = shop_i_cap_GA.report_df(**params)
columns = [i.replace(':', '_') for i in cooks_2_sourese.columns]
cooks_2_sourese.columns = columns
cooks_2_sourese = df_proc(cooks_2_sourese)
cooks_2_sourese = cooks_2_sourese.drop(columns=['ga_sessions'])
cooks_2_sourese_table = gbq_pd('shop_cooks_sourse', 'marketing_bi')
cooks_2_sourese_table.replace(cooks_2_sourese)
log += f"По таблице shop_cooks_sourse обновилось {len(cooks_2_sourese)} строк \n"
df_cooks_table = gbq_pd('shp_icap_wf_cooks', 'marketing_bi')
df_cooks_table.replace(df_cooks)
log += f"По таблице shp_icap_wf_cooks обновилось {len(df_cooks)} строк \n"
def query_df(qry, token):
devDB = token
cnx = mysql.connect(**devDB)
cursor = cnx.cursor()
cursor.execute(qry)
resula = [i for i in cursor]
field_names = [i[0] for i in cursor.description]
cursor.close()
cnx.close()
db_data_df = pd.DataFrame(resula, columns=field_names)
return db_data_df
get_buyers = """SELECT
u.user_id AS user_id,
u.phone
FROM
users AS u
JOIN
`companies` AS c
ON
c.user_id = u.user_id
INNER JOIN (
SELECT
DISTINCT consumer_profile_id AS cmp_id
FROM
`deals`
WHERE
supplier_company_id = 46) AS cmp
ON
cmp.cmp_id = c.company_id"""
companies = query_df(get_buyers, token)
wf_buyers = gbq_pd('wf_buyers_icap', 'marketing_bi')
wf_buyers.replace(companies)
log += f"По таблице wf_buyers_icap обновилось {len(companies)} строк \n"
return log
import pandas
import os
import requests
import json
import datetime
from doc_token import get_tokens
passwords = get_tokens()
token = passwords['facebook']
def fb_add_x(cell, fld):
ld_click = 0
if type(cell) == list:
for i in cell:
if i['action_type'] == fld:
ld_click = int(i['value'])
return ld_click
def utm_to_colums_full(column):
sourse_list = []
medium_list = []
project_list = []
for row in column:
if row and 'utm_source' in row:
utm = row.split('?')
domain = row.split("/")[2]
tag_list = [y.split('=') for y in utm[-1].split('&')]
tags= {i[0]:i[1] for i in tag_list}
sourse_list.append(tags['utm_source'])
import os
import pandas
from apiclient.discovery import build
from google.cloud import bigquery
from google.oauth2 import service_account
import datetime
from pd_gbq import *
import mysql.connector as mysql
from doc_token import get_tokens
token = get_tokens()
class ga_connect:
log = ""
# Задаём ключ из файла
credentials = service_account.Credentials.from_service_account_file(
'kalmuktech-5b35a5c2c8ec.json', )
analytics = build('analyticsreporting', 'v4', credentials=credentials)
def __init__(self, viewId):
# Оптределяем аккаунт откуда бер1м данные
self.viewId = viewId
def request(self, dates, metrics, dimetions, filters=[]):
# Забираем сырые данные
return ga_connect.analytics.reports().batchGet(
body={
'reportRequests': [{
"samplingLevel": "LARGE",
def call_hole():
passwords = get_tokens()
callibri_connect = callibri(token=passwords['callibri'])
date2 = datetime.datetime.today().date() - datetime.timedelta(days=1)
date1 = date2 - datetime.timedelta(days=5)
callibri_data = callibri_connect.get_stats(date1, date2)
cal_ph = {
i['phone']: (i['date'][:10] + ' ' + i['date'][11:19])
for i in callibri_data['channels_statistics'][0]['calls']
}
SCOPES = [
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/documents'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'kalmuktech-5b35a5c2c8ec.json', SCOPES)
service = build('docs', 'v1', credentials=credentials)
current_token = get_new_token(
"1V1gX11RDYJf4ZVFCEOqp-kY5j6weApl_oEFkv2oZzW4")
amo_connect = get_AMO(current_token['access_token'])
r_dt = datetime.datetime.today()
t_dt = datetime.datetime(r_dt.year, r_dt.month, r_dt.day)
date1 = t_dt - datetime.datetime(1970, 1, 1) - datetime.timedelta(days=6)
date2 = t_dt - datetime.datetime(1970, 1, 1) - datetime.timedelta(days=1)
date1_s = str(int(date1.total_seconds()))
date2_s = str(int(date2.total_seconds()))
fresj_cnts = amo_connect.get_big_amo('contacts')
import string
def get_custom_phone(cstms, fld='Телефон'):
for i in cstms:
if 'name' in i and i['name'] == fld:
phn = ''
for j in i['values'][0]['value']:
if j in string.digits:
phn += j
return phn
cnt_map = {
get_custom_phone(i['custom_fields']): i['id']
for i in fresj_cnts
}
matches = []
for i in cal_ph:
if i in cnt_map:
pass
else:
matches.append(i)
matches = []
for i in cal_ph:
if i in cnt_map:
pass
else:
matches.append(i)
losts = {}
for i in matches:
losts[i] = cal_ph[i]
if len(matches) > 0:
message = 'Контакты не попавшие в амо:\n'
for i, e in losts.items():
message += f'{i} созданый {e}\n'
else:
message = 'Нет пропавших контактов'
chats = [247391252, 482876050]
token = "1461276547:AAECMSMOMW1Zah3IEXeAyGAsBVJD0ktM86E"
method = "sendMessage"
url = "https://api.telegram.org/bot{token}/{method}".format(token=token,
method=method)
for i in chats:
data = {"chat_id": i, "text": message}
requests.post(url, data=data)
