def __init__(self, path_to_bq, client_name, placement, login, password,
params_for_report_dict, path_to_save):
self.path_to_save = path_to_save
self.email_password = password
self.email_user = login
self.placement = placement
self.client_name = client_name
self.bq = BigQuery(path_to_bq)
self.data_set_id = f"{client_name}_Email_{placement}"
"""
params_for_report_dict: {"REPORT": [{"type": "STRING", "description": "desk", "param": "Impressions"},
{"type": "FLOAT", "description": "desk", "param": "clicks"}]}
"""
self.report_dict = {}
for report_name, params in params_for_report_dict.items():
self.report_dict.setdefault(report_name, {})
for element in params:
self.report_dict[report_name].update({
element['param']: {
"type": element['type'],
"mode": "NULLABLE",
"description": element["description"]
}
})
self.tables_with_schema, self.fields = create_fields(
client_name, "Email", self.report_dict, placement)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
class LogDBHandler(logging.Handler):
def __init__(self, path_to_json, db_name, db_table):
logging.Handler.__init__(self)
self.bq = BigQuery(path_to_json)
self.db_name = db_name
self.db_table = db_table
def emit(self, record):
log_msg = record.msg
log_msg = log_msg.strip()
log_msg = log_msg.replace('\'', '\'\'')
exc_params_list = list(record.exc_info[:2])
for number, exc_list_element in enumerate(exc_params_list):
if exc_list_element is not None:
exc_params_list[number] = str(exc_list_element)
exc_class, exc_param = exc_params_list
list_of_json = {
"level_no":
record.levelno,
"line_no":
record.lineno,
"level_name":
record.levelname,
"module":
record.module,
"msg":
log_msg,
"file_name":
record.filename,
"func_name":
record.funcName,
"exc_class":
exc_class,
"exc_param":
exc_param,
"name":
record.name,
"pathname":
record.pathname,
"process":
record.process,
"process_name":
record.processName,
"thread":
record.thread,
"thread_name":
record.threadName,
"status_code":
record.status_code,
"status_msg":
record.status_msg,
"created":
datetime.strftime(datetime.fromtimestamp(record.created),
"%Y-%m-%d %H:%M:%S")
}
self.bq.insert_json(self.db_name, self.db_table, [list_of_json])
def __init__(self, ct_site_id, ct_token, client_name, path_to_bq, date_from, date_to, report_range):
self.__ct_token = ct_token
self.ct_site_id = ct_site_id
self.node_url = "https://api-node9.calltouch.ru/calls-service/RestAPI/requests/"
self.__url = f'http://api.calltouch.ru/calls-service/RestAPI/{ct_site_id}/'
self.bq = BigQuery(path_to_bq)
self.client_name = client_name
self.date_from = date_from
self.date_to = date_to
self.data_set_id = f"{client_name}_Calltouch_{ct_site_id}"
self.report_range = report_range
self.report_dict = {
"CALLS": {
"fields": {
'date': {"type": "DATE", "mode": "NULLABLE", "description": "Call day :DATE"},
'callUrl': {"type": "STRING", "mode": "NULLABLE", "description": "Call URL :STRING"},
'uniqueCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Unique Call :BOOLEAN"},
'utmContent': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Content :STRING"},
'source': {"type": "STRING", "mode": "NULLABLE", "description": "Source :STRING"},
'waitingConnect': {"type": "FLOAT", "mode": "NULLABLE", "description": "Waiting connect :FLOAT"},
'ctCallerId': {"type": "STRING", "mode": "NULLABLE", "description": "Calltouch Caller ID :STRING"},
'keyword': {"type": "STRING", "mode": "NULLABLE", "description": "Keyword :STRING"},
'utmSource': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Source :STRING"},
'sipCallId': {"type": "STRING", "mode": "NULLABLE", "description": "Sip Call ID :STRING"},
'utmCampaign': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Campaign :STRING"},
'phoneNumber': {"type": "STRING", "mode": "NULLABLE", "description": "Phone number :STRING"},
'uniqTargetCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Uniq Call :BOOLEAN"},
'utmMedium': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Medium :BOOLEAN"},
'city': {"type": "STRING", "mode": "NULLABLE", "description": "City :STRING"},
'yaClientId': {"type": "STRING", "mode": "NULLABLE", "description": "Yandex Client_ID :STRING"},
'medium': {"type": "STRING", "mode": "NULLABLE", "description": "Medium :STRING"},
'duration': {"type": "FLOAT", "mode": "NULLABLE", "description": "Call duration :FLOAT"},
'callbackCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Callback call :BOOLEAN"},
'successful': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Successful :BOOLEAN"},
'callId': {"type": "STRING", "mode": "NULLABLE", "description": "Call ID :STRING"},
'clientId': {"type": "STRING", "mode": "NULLABLE", "description": "Google Client_ID :STRING"},
'callerNumber': {"type": "STRING", "mode": "NULLABLE", "description": "Caller number :STRING"},
'utmTerm': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Term :STRING"},
'sessionId': {"type": "STRING", "mode": "NULLABLE", "description": "Session ID :STRING"},
'targetCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Target Call :BOOLEAN"},
'AUTO_PR': {"type": "STRING", "mode": "NULLABLE", "description": "AUTO Tags :STRING"},
'MANUAL': {"type": "STRING", "mode": "NULLABLE", "description": "MANUAL Tags :STRING"}
}}}
self.tables_with_schema, self.fields = create_fields(client_name, "Calltouch", self.report_dict, ct_site_id)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema, self.data_set_id)
def __init__(self, client_id, client_secret, client_name, path_to_bq,
date_from, date_to):
self.client_id = client_id
self.access_token = ""
self.client_secret = client_secret
self.client_name = client_name
self.data_set_id = f"{client_name}_Hybrid"
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.date_from = date_from
self.date_to = date_to
self.url = "https://api.hybrid.ru/"
self.report_dict = {
"CAMPAIGNS": {
"fields": {
"Id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "CampaignID :STRING"
},
"Name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign name :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"Day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"ImpressionCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"ClickCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"Reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"CTR": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "CTR :FLOAT"
},
"id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "CampaignID :STRING"
}
}
},
"ADVERTISER_STAT": {
"fields": {
"Day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"ImpressionCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"ClickCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"Reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"CTR": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "CTR :FLOAT"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "Hybrid", self.report_dict, None)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
class Hybrid:
def __init__(self, client_id, client_secret, client_name, path_to_bq,
date_from, date_to):
self.client_id = client_id
self.access_token = ""
self.client_secret = client_secret
self.client_name = client_name
self.data_set_id = f"{client_name}_Hybrid"
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.date_from = date_from
self.date_to = date_to
self.url = "https://api.hybrid.ru/"
self.report_dict = {
"CAMPAIGNS": {
"fields": {
"Id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "CampaignID :STRING"
},
"Name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign name :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"Day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"ImpressionCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"ClickCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"Reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"CTR": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "CTR :FLOAT"
},
"id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "CampaignID :STRING"
}
}
},
"ADVERTISER_STAT": {
"fields": {
"Day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"ImpressionCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"ClickCount": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"Reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"CTR": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "CTR :FLOAT"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "Hybrid", self.report_dict, None)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
def hybrid_auth(self, **kwargs):
body = {
"grant_type": "client_credentials",
"client_id": self.client_id,
"client_secret": self.client_secret
}
body.update(kwargs)
keys = requests.post(self.url + 'token', body).json()
return keys['access_token']
# def check_token_expires(self):
# try:
# keys = json.load(open(self.path_to_json + f"{self.client_name}.json", "r"))
# token_life_remaining = (datetime.fromtimestamp(keys['token_life']) - datetime.now()).seconds
# if token_life_remaining <= 60:
# access_token = self.hybrid_auth(refresh_token=keys['refresh_token'])
# return access_token
# else:
# return keys['access_token']
# except FileNotFoundError:
# access_token = self.hybrid_auth()
# return access_token
def get_request(self, method, **kwargs):
headers = {"Authorization": f"Bearer {self.access_token}"}
response = requests.get(self.url + method,
headers=headers,
params=kwargs).json()
return response
def get_campaigns(self):
access_token = self.hybrid_auth()
Hybrid.access_token = access_token
campaigns = self.get_request("v3.0/advertiser/campaigns")
return campaigns
def get_campaign_stat(self, campaign_ids_list):
total_stat = []
for ids in campaign_ids_list:
params = {
"from": self.date_from,
"to": self.date_to,
"campaignId": ids
}
stat = self.get_request("v3.0/campaign/Day", **params)
if list(stat.keys()) != ['Statistic', 'Total']:
sys.exit(list(stat.keys()))
for element in stat['Statistic']:
element['id'] = ids
total_stat.append(element)
return total_stat
def get_advertiser_stat(self):
params = {"from": self.date_from, "to": self.date_to}
stat = self.get_request("v3.0/advertiser/Day", **params)
if list(stat.keys()) != ['Statistic', 'Total']:
sys.exit(list(stat.keys()))
return stat['Statistic']
def get_hybrid_report(self):
campaigns = self.get_campaigns()
campaign_df = pd.DataFrame(campaigns)
campaign_ids = campaign_df['Id'].tolist()
self.bq.insert_difference(campaign_df, self.fields, self.data_set_id,
f"{self.client_name}_Hybrid_CAMPAIGNS", 'Id',
'Id', "%Y-%m-%d")
campaign_stat = self.get_campaign_stat(campaign_ids)
campaign_stat_df = pd.DataFrame(campaign_stat)
self.bq.data_to_insert(campaign_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_Hybrid_CAMPAIGN_STAT",
"%Y-%m-%dT%H:%M:%S")
advertiser_stat = self.get_advertiser_stat()
advertiser_stat_df = pd.DataFrame(advertiser_stat)
self.bq.data_to_insert(advertiser_stat_df, self.fields,
self.data_set_id,
f"{self.client_name}_Hybrid_ADVERTISER_STAT",
"%Y-%m-%dT%H:%M:%S")
return []
class Calltouch:
def __init__(self, ct_site_id, ct_token, client_name, path_to_bq, date_from, date_to, report_range):
self.__ct_token = ct_token
self.ct_site_id = ct_site_id
self.node_url = "https://api-node9.calltouch.ru/calls-service/RestAPI/requests/"
self.__url = f'http://api.calltouch.ru/calls-service/RestAPI/{ct_site_id}/'
self.bq = BigQuery(path_to_bq)
self.client_name = client_name
self.date_from = date_from
self.date_to = date_to
self.data_set_id = f"{client_name}_Calltouch_{ct_site_id}"
self.report_range = report_range
self.report_dict = {
"CALLS": {
"fields": {
'date': {"type": "DATE", "mode": "NULLABLE", "description": "Call day :DATE"},
'callUrl': {"type": "STRING", "mode": "NULLABLE", "description": "Call URL :STRING"},
'uniqueCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Unique Call :BOOLEAN"},
'utmContent': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Content :STRING"},
'source': {"type": "STRING", "mode": "NULLABLE", "description": "Source :STRING"},
'waitingConnect': {"type": "FLOAT", "mode": "NULLABLE", "description": "Waiting connect :FLOAT"},
'ctCallerId': {"type": "STRING", "mode": "NULLABLE", "description": "Calltouch Caller ID :STRING"},
'keyword': {"type": "STRING", "mode": "NULLABLE", "description": "Keyword :STRING"},
'utmSource': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Source :STRING"},
'sipCallId': {"type": "STRING", "mode": "NULLABLE", "description": "Sip Call ID :STRING"},
'utmCampaign': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Campaign :STRING"},
'phoneNumber': {"type": "STRING", "mode": "NULLABLE", "description": "Phone number :STRING"},
'uniqTargetCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Uniq Call :BOOLEAN"},
'utmMedium': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Medium :BOOLEAN"},
'city': {"type": "STRING", "mode": "NULLABLE", "description": "City :STRING"},
'yaClientId': {"type": "STRING", "mode": "NULLABLE", "description": "Yandex Client_ID :STRING"},
'medium': {"type": "STRING", "mode": "NULLABLE", "description": "Medium :STRING"},
'duration': {"type": "FLOAT", "mode": "NULLABLE", "description": "Call duration :FLOAT"},
'callbackCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Callback call :BOOLEAN"},
'successful': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Successful :BOOLEAN"},
'callId': {"type": "STRING", "mode": "NULLABLE", "description": "Call ID :STRING"},
'clientId': {"type": "STRING", "mode": "NULLABLE", "description": "Google Client_ID :STRING"},
'callerNumber': {"type": "STRING", "mode": "NULLABLE", "description": "Caller number :STRING"},
'utmTerm': {"type": "STRING", "mode": "NULLABLE", "description": "UTM Term :STRING"},
'sessionId': {"type": "STRING", "mode": "NULLABLE", "description": "Session ID :STRING"},
'targetCall': {"type": "BOOLEAN", "mode": "NULLABLE", "description": "Target Call :BOOLEAN"},
'AUTO_PR': {"type": "STRING", "mode": "NULLABLE", "description": "AUTO Tags :STRING"},
'MANUAL': {"type": "STRING", "mode": "NULLABLE", "description": "MANUAL Tags :STRING"}
}}}
self.tables_with_schema, self.fields = create_fields(client_name, "Calltouch", self.report_dict, ct_site_id)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema, self.data_set_id)
def __get_pages(self, date_from, date_to):
params = {'clientApiId': self.__ct_token, 'dateFrom': date_from, 'dateTo': date_to, 'page': 1, 'limit': 1000}
pages = requests.get(self.__url, params=params).json()['pageTotal']
return pages
def get_node_id(self):
response = requests.get(self.__url + "getnodeid/", params={'clientApiId': self.__ct_token})
return response.json()['nodeId']
def get_forms(self, date_from, date_to):
node_id = self.get_node_id()
url = f"https://api-node{node_id}.calltouch.ru/calls-service/RestAPI/requests"
params = {'clientApiId': self.__ct_token, 'dateFrom': date_from, 'dateTo': date_to}
list_of_calls = requests.get(url, params=params).json()
# TODO: {'errorCode': '500', 'message': 'При обработке вашего запроса произошла ошибка. Обратитесь в службу технической поддержки.'}
return list_of_calls
def get_calls(self, date_from, date_to):
i = 1
total_result = []
pages = self.__get_pages(date_from, date_to)
keys = ['callId', 'callerNumber', 'date', 'waitingConnect', 'duration', 'phoneNumber', 'successful',
'uniqueCall', 'targetCall', 'uniqTargetCall', 'callbackCall', 'city', 'source', 'medium', 'keyword',
'callUrl', 'utmSource', 'utmMedium', 'utmCampaign', 'utmContent', 'utmTerm', 'sessionId', 'ctCallerId',
'clientId', 'yaClientId', 'sipCallId', 'callTags', 'callUrl']
while i <= pages:
params = {'clientApiId': self.__ct_token, 'dateFrom': date_from, 'dateTo': date_to, 'page': i,
'limit': 1000, 'withCallTags': True}
list_of_calls = requests.get(self.__url + "calls-diary/calls", params=params).json()['records']
i += 1
for call in list_of_calls:
data = call.copy()
for key, values in call.items():
if key not in keys:
data.pop(key)
elif key == 'callTags':
for one in values:
if one['type'] == 'AUTO-PR':
data["AUTO_PR"] = ",".join(one['names'])
elif one['type'] == 'MANUAL':
data["MANUAL"] = ",".join(one['names'])
data.pop("callTags")
total_result.append(data)
return total_result
def get_calltouch_report(self):
for report in self.report_dict:
if report in self.report_range:
if report == 'CALLS':
calls = self.get_calls(self.date_from, self.date_to)
if not calls:
return []
calls_df = pd.DataFrame(calls).fillna(0)
self.bq.data_to_insert(calls_df, self.fields, self.data_set_id,
f"{self.client_name}_Calltouch_{self.ct_site_id}_{report}",
"%d/%m/%Y %H:%M:%S")
elif report == 'FORMS':
pass
return []
class Email:
def __init__(self, path_to_bq, client_name, placement, login, password,
params_for_report_dict, path_to_save):
self.path_to_save = path_to_save
self.email_password = password
self.email_user = login
self.placement = placement
self.client_name = client_name
self.bq = BigQuery(path_to_bq)
self.data_set_id = f"{client_name}_Email_{placement}"
"""
params_for_report_dict: {"REPORT": [{"type": "STRING", "description": "desk", "param": "Impressions"},
{"type": "FLOAT", "description": "desk", "param": "clicks"}]}
"""
self.report_dict = {}
for report_name, params in params_for_report_dict.items():
self.report_dict.setdefault(report_name, {})
for element in params:
self.report_dict[report_name].update({
element['param']: {
"type": element['type'],
"mode": "NULLABLE",
"description": element["description"]
}
})
self.tables_with_schema, self.fields = create_fields(
client_name, "Email", self.report_dict, placement)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
def get_email(self,
email_from,
subject_filter,
host='imap.yandex.ru',
port=993):
mail = imaplib.IMAP4_SSL(host, port)
mail.login(self.email_user, self.email_password)
mail.select('INBOX')
date_from = datetime.strftime(datetime.today() - timedelta(days=1),
"%d-%b-%Y")
date_to = datetime.strftime(datetime.today(), "%d-%b-%Y")
status_search, search_result = mail.uid('search', None,
f'(FROM "{email_from}")',
f'(SINCE "{date_from}")',
f'(BEFORE "{date_to}")')
list_of_ids_str = search_result[0].decode("utf-8").split(" ")
if list_of_ids_str[0] == '':
return []
else:
list_of_ids_int = [int(num) for num in list_of_ids_str]
list_for_download = list(
set(list_of_ids_int).difference(set(list_of_ids_int)))
list_of_file_names = []
for letter_id in list_for_download:
mail.select('INBOX')
status_result, letter_data = mail.uid('fetch', letter_id,
'(RFC822)')
raw_email = letter_data[0][1]
email_message = email.message_from_bytes(raw_email)
subject_list = decode_header(email_message['Subject'])
code = subject_list[0][1]
subject = subject_list[0][0].decode(code)
if subject_filter in subject:
for part in email_message.walk():
if part.get_content_maintype() == 'multipart':
continue
if part.get('Content-Disposition') is None:
continue
file_name = part.get_filename()
fp = open(f"{self.path_to_save}{file_name}", 'wb')
fp.write(part.get_payload(decode=True))
fp.close()
list_of_file_names.append(file_name)
mail.close()
return list_of_file_names
def insert_email_to_bq(self, list_of_file_names, list_name):
for file_name in list_of_file_names:
pass
return []
def delete_download_files(self, list_of_file_names):
for file_name in list_of_file_names:
os.remove(self.path_to_save + file_name)
return []
def __init__(self, counter, access_token, client_name, path_to_bq,
date_from, date_to):
self.__request_url = f"https://api-metrika.yandex.net/management/v1/counter/{counter}/"
self.__source = 'visits'
self.date_from = date_from
self.date_to = date_to
self.counter_id = counter
self.client_name = client_name
self.data_set_id = f"{client_name}_YMLogs_{counter}"
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.__access_token = access_token
self.date_range = slice_date_on_period(date_from, date_to, 1)
self.fields = 'ym:s:visitID,ym:s:counterID,ym:s:date,ym:s:isNewUser,ym:s:clientID,ym:s:pageViews,' \
'ym:s:visitDuration,ym:s:bounce,ym:s:regionCity,ym:s:UTMCampaign,ym:s:UTMContent,' \
'ym:s:UTMMedium,ym:s:UTMSource,ym:s:UTMTerm,ym:s:deviceCategory'
self.report_dict = {
"YMLogs": {
"fields": {
"ym_s_visitID": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Visit ID :INTEGER"
},
"ym_s_counterID": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Counter ID :INTEGER"
},
"ym_s_date": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Date :DATE"
},
"ym_s_isNewUser": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Is new user :INTEGER"
},
"ym_s_clientID": {
"type": "STRING",
"mode": "NULLABLE",
"description": "YA client ID :STRING"
},
"ym_s_pageViews": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Page views :INTEGER"
},
"ym_s_visitDuration": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Visit duration :FLOAT"
},
"ym_s_bounces": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Bounce :INTEGER"
},
"ym_s_regionCity": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Region city :STRING"
},
"ym_s_UTMCampaign": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMCampaign :STRING"
},
"ym_s_UTMContent": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMContent :STRING"
},
"ym_s_UTMMedium": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMMedium :STRING"
},
"ym_s_UTMSource": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMSource :STRING"
},
"ym_s_UTMTerm": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMTerm :STRING"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "YMLogs", self.report_dict, counter)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
class YMLogs:
def __init__(self, counter, access_token, client_name, path_to_bq,
date_from, date_to):
self.__request_url = f"https://api-metrika.yandex.net/management/v1/counter/{counter}/"
self.__source = 'visits'
self.date_from = date_from
self.date_to = date_to
self.counter_id = counter
self.client_name = client_name
self.data_set_id = f"{client_name}_YMLogs_{counter}"
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.__access_token = access_token
self.date_range = slice_date_on_period(date_from, date_to, 1)
self.fields = 'ym:s:visitID,ym:s:counterID,ym:s:date,ym:s:isNewUser,ym:s:clientID,ym:s:pageViews,' \
'ym:s:visitDuration,ym:s:bounce,ym:s:regionCity,ym:s:UTMCampaign,ym:s:UTMContent,' \
'ym:s:UTMMedium,ym:s:UTMSource,ym:s:UTMTerm,ym:s:deviceCategory'
self.report_dict = {
"YMLogs": {
"fields": {
"ym_s_visitID": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Visit ID :INTEGER"
},
"ym_s_counterID": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Counter ID :INTEGER"
},
"ym_s_date": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Date :DATE"
},
"ym_s_isNewUser": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Is new user :INTEGER"
},
"ym_s_clientID": {
"type": "STRING",
"mode": "NULLABLE",
"description": "YA client ID :STRING"
},
"ym_s_pageViews": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Page views :INTEGER"
},
"ym_s_visitDuration": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Visit duration :FLOAT"
},
"ym_s_bounces": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Bounce :INTEGER"
},
"ym_s_regionCity": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Region city :STRING"
},
"ym_s_UTMCampaign": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMCampaign :STRING"
},
"ym_s_UTMContent": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMContent :STRING"
},
"ym_s_UTMMedium": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMMedium :STRING"
},
"ym_s_UTMSource": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMSource :STRING"
},
"ym_s_UTMTerm": {
"type": "STRING",
"mode": "NULLABLE",
"description": "UTMTerm :STRING"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "YMLogs", self.report_dict, counter)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
def __request(self, method, request_type, **kwargs):
headers = {
"Authorization": 'OAuth ' + self.__access_token,
"Host": 'api-metrika.yandex.net',
'Content-Type': 'application/x-yametrika+json',
'date1': self.date_from
}
params = kwargs
if 'oauth_token' not in params:
params['oauth_token'] = self.__access_token
response = requests.request(request_type,
self.__request_url + method,
params=params,
headers=headers)
return response
def evaluate(self):
method = "logrequests/evaluate/"
evaluate_response = self.__request(method,
request_type="GET",
date1=self.date_from,
date2=self.date_to,
fields=self.fields,
source=self.__source).json()
return evaluate_response
def get_request(self, date_from, date_to):
self.date_from = date_from
self.date_to = date_to
request_id = self.logrequestID()
parts = self.log_requests(request_id)
data = self.download(request_id, parts)
return data
def logrequestID(self):
method = "logrequests/"
log_request_id_response = self.__request(method,
request_type="POST",
date1=self.date_from,
date2=self.date_to,
fields=self.fields,
source=self.__source).json()
return log_request_id_response['log_request']['request_id']
def log_requests(self, request_id):
method = f"logrequest/{request_id}"
log_requests_response = self.__request(method,
request_type="GET").json()
if log_requests_response['log_request']['status'] == 'created':
time.sleep(30)
return self.log_requests(request_id)
return log_requests_response['log_request']['parts']
def download(self, request_id, parts):
all_data = []
for part in parts:
part_id = part['part_number']
method = f"logrequest/{request_id}/part/{part_id}/download/"
download = self.__request(method, request_type="GET")
download_data = self.__get_data(download)
all_data += download_data
return all_data
def __get_data(self, response):
data_in_string = response.text.split('\n')
get_data_list = []
for string in data_in_string:
get_data_list.append(string.split('\t'))
df = pd.DataFrame(get_data_list[1:-1], columns=get_data_list[0])
result = list(df.T.to_dict().values())
return result
def get_report(self):
for date_from, date_to in self.date_range:
request_result = self.get_request(date_from, date_to)
request_result_df = pd.DataFrame(request_result).fillna(0)
insert_data = self.bq.data_to_insert(
request_result_df, self.fields, self.data_set_id,
f"{self.client_name}_YMLogs_{self.counter_id}_YMLogs",
"%Y-%m-%d")
assert insert_data == [], "Data not insert"
return []
def __init__(self, token, account, client_name, date_from, date_to,
path_to_bq):
self.date_from = date_from
self.date_to = date_to
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.token = token
self.account = account
self.client_name = client_name
self.data_set_id = f"{self.client_name}_Facebook_{self.account[4:]}"
self.date_range = slice_date_on_period(date_from, date_to, 5)
self.report_dict = {
"ADS_STAT": {
"fields": {
"clicks": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"impressions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"spend": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p100_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p25_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p50_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_30_sec_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p75_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p95_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_thruplay_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_avg_time_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"conversions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"clicks": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"impressions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"reach": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"spend": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p100_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p25_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p50_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_30_sec_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p75_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p95_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_thruplay_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_avg_time_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"conversions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
},
"ADSETS_STAT": {
"fields": {
"reach": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"frequency": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "Facebook", self.report_dict, account[4:])
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
class Facebook:
def __init__(self, token, account, client_name, date_from, date_to,
path_to_bq):
self.date_from = date_from
self.date_to = date_to
self.path_to_bq = path_to_bq
self.bq = BigQuery(path_to_bq)
self.token = token
self.account = account
self.client_name = client_name
self.data_set_id = f"{self.client_name}_Facebook_{self.account[4:]}"
self.date_range = slice_date_on_period(date_from, date_to, 5)
self.report_dict = {
"ADS_STAT": {
"fields": {
"clicks": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"impressions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"spend": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p100_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p25_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p50_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_30_sec_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p75_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p95_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_thruplay_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_avg_time_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"conversions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"clicks": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"impressions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"reach": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"spend": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p100_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p25_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p50_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_30_sec_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p75_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_p95_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_thruplay_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"video_avg_time_watched_actions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"conversions": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
},
"ADSETS_STAT": {
"fields": {
"reach": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"adset_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"campaign_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"ad_id": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"frequency": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
},
"date_start": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Copies :STRING"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "Facebook", self.report_dict, account[4:])
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
def check_headers(self, headers):
if (headers['total_cputime'] >= 50) or (headers['total_time'] >= 50):
print("Пришло время для сна.")
time.sleep(60 * 60)
def get_statistics(self, level):
gs_result = []
gs_fields = {
"campaign": "",
}
d = "clicks,impressions,spend,video_p100_watched_actions,video_p25_watched_actions," \
"video_p50_watched_actions,video_30_sec_watched_actions,video_p75_watched_actions," \
"video_p95_watched_actions,video_thruplay_watched_actions,video_avg_time_watched_actions," \
"conversions,ad_id,ad_name,campaign_id,campaign_name,adset_id,adset_name,actions,unique_actions"
gs_url_method = f"v6.0/{self.account}/insights?"
for date_from, date_to in self.date_range:
gs_batch = []
get_statistic_time_range = "{'since':'%s','until':'%s'}" % (
date_from, date_to)
gs_batch.append(
self.create_betch(gs_url_method,
time_range=get_statistic_time_range,
fields=gs_fields,
limit=50,
level=level,
time_increment=1))
gs_result_list, stat_headers = self.get_batch_data(
gs_batch, [], gs_url_method)
for gs_element in gs_result_list:
gs_middle_list = {}
for gs_element_key, gs_element_value in gs_element.items():
if "video_" in gs_element_key:
gs_middle_list[gs_element_key] = gs_element_value[0][
'value']
elif "actions" in gs_element_key:
for gs_action_key in gs_element_value:
gs_action_key_re = re.sub(
'[.]', '_', gs_action_key['action_type'])
gs_middle_list[gs_action_key_re] = gs_action_key[
'value']
else:
gs_middle_list[gs_element_key] = gs_element_value
gs_result.append(gs_middle_list)
self.check_headers(stat_headers)
return gs_result
def get_paging_data(self, gpd_result_data, gpd_headers):
gpd_list = []
gpd_result_list = []
for gpd_result in gpd_result_data:
gpd_middle_data = json.loads(gpd_result['body'])
for header in gpd_result['headers']:
if header['name'] == 'X-Business-Use-Case-Usage':
gpd_headers = json.loads(
header['value'])[self.account[4:]][0]
try:
gpd_md = gpd_middle_data['data']
except KeyError:
sys.exit(
f"middle_data ->{gpd_middle_data}<-, result_data ->{gpd_result_data}<-, result ->{gpd_result}<-"
)
if gpd_md:
for gpd_element in gpd_middle_data['data']:
gpd_list.append(gpd_element)
gpd_result_list.append(gpd_middle_data['paging'])
else:
continue
return gpd_list, gpd_result_list, gpd_headers
def create_next_paging_request(self, cnpr_result_paging, cnpr_url_method):
cnpr_batch = []
for cnpr_next_link in cnpr_result_paging:
if "next" in cnpr_next_link:
cnpr_next_params = cnpr_next_link['next'].split('?')[1]
cnpr_batch.append({
"method":
"GET",
"relative_url":
cnpr_url_method + cnpr_next_params
})
return cnpr_batch
def get_batch_request(self, gbr_batch):
gbr_jBatch = json.dumps(gbr_batch)
gbr_params = {"access_token": self.token, "batch": gbr_jBatch}
gbr_result_data = requests.post("https://graph.facebook.com/",
params=gbr_params).json()
return gbr_result_data
def get_data_with_filtering(self,
gdwf_campaign_ids_slice,
method,
fields,
filter_by,
operator,
limit=500):
gdwf_result_list = []
gdwf_total_result = []
gdwf_batch = []
gdwf_url_method = f"v6.0/{self.account}/{method}?"
gdwf_count_campaign_ids_slice = len(gdwf_campaign_ids_slice)
for gdwf_num, gdwf_ids_slice in enumerate(gdwf_campaign_ids_slice, 1):
for gdwf_ids in gdwf_ids_slice:
gdwf_filtering = "[{field:'%s',operator:'%s',value:%s}]" % (
filter_by, operator, gdwf_ids)
gdwf_batch.append(
self.create_betch(gdwf_url_method,
filtering=gdwf_filtering,
limit=limit,
fields=fields))
gdwf_result_list, gdwf_headers = self.get_batch_data(
gdwf_batch, gdwf_result_list, gdwf_url_method)
for gdwf_one in gdwf_result_list:
gdwf_total_result.append(gdwf_one)
if gdwf_num != gdwf_count_campaign_ids_slice:
self.check_headers(gdwf_headers)
return gdwf_total_result
def create_betch(self, cb_url_method, **kwargs):
cb_params = []
for cb_key, cb_value in kwargs.items():
cb_params.append(f'{cb_key}={cb_value}')
params_in_string = '&'.join(cb_params)
return {
"method": "GET",
"relative_url": cb_url_method + params_in_string
}
def get_batch_data(self, gbd_batch, gbd_batch_result_list, gbd_url_method):
gbd_jBatch = json.dumps(gbd_batch)
gbd_params = {"access_token": self.token, "batch": gbd_jBatch}
gbd_result = requests.post("https://graph.facebook.com/",
params=gbd_params).json()
gbd_middle_list, gbd_result_paging, gbd_headers = self.get_paging_data(
gbd_result, gpd_headers={})
for gbd_element in gbd_middle_list:
gbd_batch_result_list.append(gbd_element)
if gbd_result_paging:
gbd_batch_result_list, gbd_headers = self.get_other_data(
gbd_batch_result_list, gbd_result_paging, gbd_url_method,
gbd_headers)
return gbd_batch_result_list, gbd_headers
def get_data_no_filtering(self, method, **kwargs):
result_list = []
url_method = f"v6.0/{self.account}/{method}?"
batch = [self.create_betch(url_method, **kwargs)]
result_list = self.get_batch_data(batch, result_list, url_method)
return result_list
def get_other_data(self,
other_result_list,
result_paging,
url_method,
headers=None):
if headers is None:
headers = {}
next_pages_batch = self.create_next_paging_request(
result_paging, url_method)
result_data = self.get_batch_request(next_pages_batch)
middle_list, result_paging, headers = self.get_paging_data(
result_data, headers)
print(headers)
for element in middle_list:
other_result_list.append(element)
if result_paging:
return self.get_other_data(other_result_list, result_paging,
url_method, headers)
return other_result_list, headers
def get_facebook_report(self):
campaign_stat = self.get_statistics("campaign")
if not campaign_stat:
return []
campaign_stat_df = pd.DataFrame(campaign_stat).fillna(0)
self.bq.data_to_insert(campaign_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_Facebook_CAMPAIGN_STAT",
"%Y-%m-%d")
ads_sets_stat = self.get_statistics("adset")
ads_sets_stat_df = pd.DataFrame(ads_sets_stat).fillna(0)
self.bq.data_to_insert(ads_sets_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_Facebook_ADSETS_STAT",
"%Y-%m-%d")
ads_stat = self.get_statistics("ad")
ads_stat_df = pd.DataFrame(ads_stat).fillna(0)
self.bq.data_to_insert(ads_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_Facebook_ADS_STAT",
"%Y-%m-%d")
def __init__(self, access_token, account_id, client_id, client_name,
path_to_bq, date_from, date_to):
self.__access_token = access_token
self.data_set_id = f"{client_name}_VKontakte_{client_id}"
self.__v = "5.101"
self.__method_url = "https://api.vk.com/method/"
self.account_id = account_id
self.bq = BigQuery(path_to_bq)
self.client_id = client_id
self.client_name = client_name
self.date_from = date_from
self.date_to = date_to
self.report_dict = {
"CAMPAIGNS": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
"type": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign type :STRING"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign name :STRING"
}
}
},
"ADS": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"campaign_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
"goal_type": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goal type :INTEGER"
},
"cost_type": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Cost type :INTEGER"
},
"category1_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Category1 ID :INTEGER"
},
"category2_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Category2 ID :INTEGER"
},
"age_restriction": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Age restriction :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Ad name :STRING"
},
"ad_format": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad format :INTEGER"
},
"ad_platform": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Ad platform :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"spent": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Spent :FLOAT"
},
"impressions": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"clicks": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"join_rate": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join to group :INTEGER"
},
"campaign_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
'lead_form_sends': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead form :INTEGER"
},
'goals': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goals :INTEGER"
}
}
},
"ADS_STAT": {
"fields": {
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"clicks": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"impressions": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"join_rate": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join to group :INTEGER"
},
"reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"spent": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Spent :FLOAT"
},
'lead_form_sends': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead form :INTEGER"
},
'goals': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goals :INTEGER"
}
}
},
"SEX_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"AGE_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"SEX_AGE_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"CITIES_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City name :STRING"
}
}
},
"POST_REACH": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"reach_subscribers": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach subscribers :INTEGER"
},
"reach_total": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach total :INTEGER"
},
"links": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Link :INTEGER"
},
"to_group": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "To group :INTEGER"
},
"join_group": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join group :INTEGER"
},
"report": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Report :INTEGER"
},
"hide": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Hide :INTEGER"
},
"unsubscribe": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Unsubscribe :INTEGER"
},
"video_views_start": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video start :INTEGER"
},
"video_views_3s": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 3s :INTEGER"
},
"video_views_10s": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 10s :INTEGER"
},
"video_views_25p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 25p :INTEGER"
},
"video_views_50p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 50p :INTEGER"
},
"video_views_75p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 75p :INTEGER"
},
"video_views_100p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 100p :INTEGER"
}
}
},
"LEAD_FORMS": {
"fields": {
"form_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Form ID :INTEGER"
},
"group_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Group ID :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Name :STRING"
},
"title": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Title :STRING"
},
"description": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Description :STRING"
},
"site_link_url": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Site link URL :STRING"
},
"url": {
"type": "STRING",
"mode": "NULLABLE",
"description": "URL :STRING"
}
}
},
"LEADS": {
"fields": {
"lead_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead ID :INTEGER"
},
"user_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "User ID :INTEGER"
},
"date": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"first_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "First name :STRING"
},
"phone_number": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Phone number :STRING"
},
"patronymic_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Patronymic name :STRING"
},
"last_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Last name :STRING"
},
"email": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Email :STRING"
}
}
},
"GROUP_ACTIVITY": {
"fields": {
"copies": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Copies :INTEGER"
},
"hidden": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Hidden :INTEGER"
},
"likes": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Likes :INTEGER"
},
"subscribed": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Subscribed :INTEGER"
},
"unsubscribed": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Unsubscribed :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Site link URL :DATE"
}
}
},
"GROUP_AGE_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Age param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_CITIES_STAT": {
"fields": {
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City name :STRING"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City ID :STRING"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_COUNTRIES_STAT": {
"fields": {
"code": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country code :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country name :STRING"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country ID :STRING"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_SEX_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Gender param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_SEX_AGE_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Age & Gender param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "VKontakte", self.report_dict, client_id)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
"https://oauth.vk.com/authorize?client_id=7446867&display=page&redirect_uri=http://localhost:8000/auth/vkontakte&scope=pages,ads,offline,groups,stats,email&response_type=code&v=5.103"
"https://oauth.vk.com/access_token?client_id=7446867&client_secret=3yU3omb66HfxRlujOKxJ&redirect_uri=http://localhost:8000/auth/vkontakte&code=26ffc758fd72e8f81c"
class VKontakte:
def __init__(self, access_token, account_id, client_id, client_name,
path_to_bq, date_from, date_to):
self.__access_token = access_token
self.data_set_id = f"{client_name}_VKontakte_{client_id}"
self.__v = "5.101"
self.__method_url = "https://api.vk.com/method/"
self.account_id = account_id
self.bq = BigQuery(path_to_bq)
self.client_id = client_id
self.client_name = client_name
self.date_from = date_from
self.date_to = date_to
self.report_dict = {
"CAMPAIGNS": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
"type": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign type :STRING"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Campaign name :STRING"
}
}
},
"ADS": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"campaign_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
"goal_type": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goal type :INTEGER"
},
"cost_type": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Cost type :INTEGER"
},
"category1_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Category1 ID :INTEGER"
},
"category2_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Category2 ID :INTEGER"
},
"age_restriction": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Age restriction :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Ad name :STRING"
},
"ad_format": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad format :INTEGER"
},
"ad_platform": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Ad platform :STRING"
}
}
},
"CAMPAIGN_STAT": {
"fields": {
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"spent": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Spent :FLOAT"
},
"impressions": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"clicks": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"join_rate": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join to group :INTEGER"
},
"campaign_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Campaign ID :INTEGER"
},
'lead_form_sends': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead form :INTEGER"
},
'goals': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goals :INTEGER"
}
}
},
"ADS_STAT": {
"fields": {
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"clicks": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Clicks :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"impressions": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Impressions :INTEGER"
},
"join_rate": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join to group :INTEGER"
},
"reach": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach :INTEGER"
},
"spent": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Spent :FLOAT"
},
'lead_form_sends': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead form :INTEGER"
},
'goals': {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Goals :INTEGER"
}
}
},
"SEX_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"AGE_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"SEX_AGE_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"CITIES_STAT": {
"fields": {
"impressions_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Imp rate :FLOAT"
},
"clicks_rate": {
"type": "FLOAT",
"mode": "NULLABLE",
"description": "Clicks rate :FLOAT"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Param value :STRING"
},
"ad_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City name :STRING"
}
}
},
"POST_REACH": {
"fields": {
"id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Ad ID :INTEGER"
},
"reach_subscribers": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach subscribers :INTEGER"
},
"reach_total": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Reach total :INTEGER"
},
"links": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Link :INTEGER"
},
"to_group": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "To group :INTEGER"
},
"join_group": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Join group :INTEGER"
},
"report": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Report :INTEGER"
},
"hide": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Hide :INTEGER"
},
"unsubscribe": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Unsubscribe :INTEGER"
},
"video_views_start": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video start :INTEGER"
},
"video_views_3s": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 3s :INTEGER"
},
"video_views_10s": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 10s :INTEGER"
},
"video_views_25p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 25p :INTEGER"
},
"video_views_50p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 50p :INTEGER"
},
"video_views_75p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 75p :INTEGER"
},
"video_views_100p": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Video 100p :INTEGER"
}
}
},
"LEAD_FORMS": {
"fields": {
"form_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Form ID :INTEGER"
},
"group_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Group ID :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Name :STRING"
},
"title": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Title :STRING"
},
"description": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Description :STRING"
},
"site_link_url": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Site link URL :STRING"
},
"url": {
"type": "STRING",
"mode": "NULLABLE",
"description": "URL :STRING"
}
}
},
"LEADS": {
"fields": {
"lead_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Lead ID :INTEGER"
},
"user_id": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "User ID :INTEGER"
},
"date": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
},
"first_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "First name :STRING"
},
"phone_number": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Phone number :STRING"
},
"patronymic_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Patronymic name :STRING"
},
"last_name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Last name :STRING"
},
"email": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Email :STRING"
}
}
},
"GROUP_ACTIVITY": {
"fields": {
"copies": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Copies :INTEGER"
},
"hidden": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Hidden :INTEGER"
},
"likes": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Likes :INTEGER"
},
"subscribed": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Subscribed :INTEGER"
},
"unsubscribed": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Unsubscribed :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Site link URL :DATE"
}
}
},
"GROUP_AGE_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Age param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_CITIES_STAT": {
"fields": {
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City name :STRING"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "City ID :STRING"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_COUNTRIES_STAT": {
"fields": {
"code": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country code :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"name": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country name :STRING"
},
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Country ID :STRING"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_SEX_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Gender param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
},
"GROUP_SEX_AGE_STAT": {
"fields": {
"value": {
"type": "STRING",
"mode": "NULLABLE",
"description": "Age & Gender param :STRING"
},
"count": {
"type": "INTEGER",
"mode": "NULLABLE",
"description": "Count of users :INTEGER"
},
"day": {
"type": "DATE",
"mode": "NULLABLE",
"description": "Day :DATE"
}
}
}
}
self.tables_with_schema, self.fields = create_fields(
client_name, "VKontakte", self.report_dict, client_id)
self.bq.check_or_create_data_set(self.data_set_id)
self.bq.check_or_create_tables(self.tables_with_schema,
self.data_set_id)
"https://oauth.vk.com/authorize?client_id=7446867&display=page&redirect_uri=http://localhost:8000/auth/vkontakte&scope=pages,ads,offline,groups,stats,email&response_type=code&v=5.103"
"https://oauth.vk.com/access_token?client_id=7446867&client_secret=3yU3omb66HfxRlujOKxJ&redirect_uri=http://localhost:8000/auth/vkontakte&code=26ffc758fd72e8f81c"
def __request(self, method, request_type='get', **kwargs):
if "access_token" not in kwargs:
params = {'access_token': self.__access_token, 'v': self.__v}
for key, value in kwargs.items():
params[key] = value
else:
params = kwargs
request_data = requests.request(request_type,
self.__method_url + method,
params=params)
return self.__get_errors(request_data, method, request_type, params)
def __get_errors(self, response, method, request_type, params):
if response.status_code == 200:
if 'error' in response.json():
if response.json()['error']['error_code'] == 100:
for param in response.json()['error']['request_params']:
if param['value'] == 'ads.getPostsReach':
print("error")
time.sleep(3)
return self.__request(method, request_type,
**params)
error_msg = response.json()['error']['error_msg']
error_code = response.json()['error']['error_code']
raise Exception(
f"Error code: {error_code}. Error msg: {error_msg}")
elif 'response' in response.json():
return response.json()['response']
else:
raise Exception("Это что-то новое")
else:
raise Exception("Status code not 200", response.status_code,
response.content)
def get_campaigns(self):
campaigns = self.__request('ads.getCampaigns',
request_type='get',
account_id=self.account_id,
include_deleted=1,
client_id=self.client_id)
keys = ["id", "type", "name"]
campaigns = pop_keys(keys, campaigns)
campaign_ids = [campaign_id['id'] for campaign_id in campaigns]
campaigns_df = pd.DataFrame(campaigns).fillna(0)
self.bq.insert_difference(
campaigns_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_CAMPAIGNS", 'id',
'id', "%Y-%m-%d")
return campaign_ids, campaigns_df
def get_ads(self, campaign_ids):
campaign_ids = my_slice(campaign_ids, 100)
ads_list = []
for campaign_ids_list in campaign_ids:
campaign_ids_string = ",".join([str(x) for x in campaign_ids_list])
ads = self.__request('ads.getAds',
request_type='get',
account_id=self.account_id,
campaign_ids=f"[{campaign_ids_string}]",
client_id=self.client_id)
ads_list += ads
time.sleep(2)
keys = [
"id", "campaign_id", "goal_type", "cost_type", "category1_id",
"category2_id", "age_restriction", "name", "ad_format",
"ad_platform"
]
ads = pop_keys(keys, ads_list)
return ads
def get_demographics(self, demographics_list_ids, limit=2000):
data_keys = {"sex": [], "age": [], "sex_age": [], "cities": []}
demographics_list = my_slice(demographics_list_ids, limit)
for demographics_id_list in demographics_list:
demographics_ids_string = ",".join(
[str(x) for x in demographics_id_list])
demographics_response = self.__request('ads.getDemographics',
request_type='get',
account_id=self.account_id,
ids_type="ad",
ids=demographics_ids_string,
period="day",
date_from=self.date_from,
date_to=self.date_to)
for one in demographics_response:
for element in one['stats']:
for key, value in element.items():
if key in ['sex', 'age', 'sex_age', 'cities']:
for one_element in value:
arr = one_element.copy()
arr[one['type'] + '_id'] = one['id']
arr['day'] = element['day']
data_keys[key].append(arr)
return data_keys['sex'], data_keys['age'], data_keys[
'sex_age'], data_keys['cities']
def get_day_stats(self, ids_type, list_of_ids, limit=2000):
day_stat_list = []
ids_list = my_slice(list_of_ids, limit)
for ids_stat_list in ids_list:
ids_stat_string = ",".join([str(x) for x in ids_stat_list])
day_stats = self.__request('ads.getStatistics',
request_type='get',
account_id=self.account_id,
ids_type=ids_type,
ids=ids_stat_string,
period="day",
date_from=self.date_from,
date_to=self.date_to)
for DayStat in day_stats:
for stat in DayStat['stats']:
stat[DayStat['type'] + "_id"] = DayStat['id']
day_stat_list.append(stat)
time.sleep(2)
keys = [
"day", "spent", "impressions", "clicks", "reach", "join_rate",
"campaign_id", 'lead_form_sends', 'goals', "ad_id"
]
day_stat_list = pop_keys(keys, day_stat_list)
return day_stat_list
def get_leads_forms(self, group_id):
lead_forms = self.__request("leadForms.list",
request_type='get',
group_id=group_id)
keys = [
"form_id", "group_id", "name", "title", "description",
"site_link_url", "url"
]
lead_forms = pop_keys(keys, lead_forms)
return lead_forms
def get_all_leads(self, group_id):
result = []
lead_forms = self.get_leads_forms(group_id)
forms = [form['form_id'] for form in lead_forms]
for form_id in forms:
leads = self.get_leads_form_id(group_id, form_id)
time.sleep(1)
for lead in leads:
middle_list = {}
for lead_key, lead_value in lead.items():
if lead_key == 'answers':
for element in lead_value:
middle_list[
element['key']] = element['answer']['value']
elif lead_key == 'date':
middle_list[lead_key] = datetime.strftime(
datetime.fromtimestamp(int(lead_value)),
"%Y-%m-%d")
else:
middle_list[lead_key] = lead_value
result.append(middle_list)
keys = [
"lead_id", "user_id", "date", "first_name", "phone_number",
"patronymic_name", "last_name", "email"
]
result = pop_keys(keys, result)
return result
def get_leads_form_id(self,
group_id,
form_id,
next_page_token=None,
result_list=None):
if result_list is None:
result_list = []
params = {"group_id": group_id, "form_id": form_id, "limit": 1000}
if next_page_token is not None:
params['next_page_token'] = next_page_token
leads = self.__request("leadForms.getLeads",
request_type='get',
**params)
result_list += leads['leads']
if "next_page_token" in leads:
next_page_token = leads['next_page_token']
time.sleep(3)
return self.get_leads_form_id(group_id,
form_id,
next_page_token=next_page_token)
return result_list
def get_group_stat(self, group_id):
date_from = (datetime.strptime(self.date_from, "%Y-%m-%d") +
timedelta(hours=0, minutes=0, seconds=0)).timestamp()
date_to = (datetime.strptime(self.date_to, "%Y-%m-%d") +
timedelta(hours=23, minutes=59, seconds=59)).timestamp()
stats = self.__request("stats.get",
request_type='get',
group_id=group_id,
timestamp_from=date_from,
timestamp_to=date_to,
extended=1,
stats_groups="activity,visitors")
result = {
"activity": [],
"age": [],
"cities": [],
"countries": [],
"sex": [],
"sex_age": []
}
for stat in stats:
day = datetime.strftime(
datetime.fromtimestamp(stat['period_from']), "%Y-%m-%d")
stat['activity']['day'] = day
result['activity'].append(stat['activity'])
stat = stat['visitors']
for visitor_stat_key, visitor_stat_value in stat.items():
if visitor_stat_key in [
"age", "cities", "countries", "sex", "sex_age"
]:
for element in visitor_stat_value:
element['day'] = day
result[visitor_stat_key] += visitor_stat_value
return result["age"], result["cities"], result["countries"], result[
"sex"], result["sex_age"], result["activity"]
def get_groups(self, list_of_groups=None, offset=0):
if list_of_groups is None:
list_of_groups = []
groups = self.__request('groups.get',
request_type='get',
filter="admin,editor,moder,advertiser",
count=1000,
offset=offset,
extended=1)
count = groups['count']
list_of_groups += groups['items']
if len(list_of_groups) < count:
offset += 1000
return self.get_groups(list_of_groups=list_of_groups,
offset=offset)
return list_of_groups
def get_my_user_id(self):
my_user_id = self.__request('users.get', request_type='get')
my_user_id = my_user_id[0]
return my_user_id['id'], my_user_id['first_name'], my_user_id[
'last_name']
def post_reach(self, ids_type, list_of_ids):
day_stat_list = []
while list_of_ids:
day_stats = self.__request('ads.getPostsReach',
request_type='get',
account_id=self.account_id,
ids_type=ids_type,
ids=list_of_ids.pop(0))
day_stat_list += day_stats
time.sleep(3)
return day_stat_list
def report_lead_forms(self, group_id):
leads_forms = self.get_leads_forms(group_id)
leads_forms_df = pd.DataFrame(leads_forms).fillna(0)
table_id = f"{self.client_name}_VKontakte_{self.client_id}_LEAD_FORMS"
self.bq.get_delete_query(
f"DELETE FROM `{self.data_set_id}.{table_id}` WHERE form_id != ''")
self.bq.data_to_insert(leads_forms_df, self.fields, self.data_set_id,
table_id, "%Y-%m-%d")
def report_leads(self, group_id):
leads = self.get_all_leads(group_id)
leads_df = pd.DataFrame(leads).fillna(0)
# TODO: Проверять лиды за прошедший период (по датам)
self.bq.insert_difference(
leads_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_LEADS", 'lead_id',
'lead_id', "%Y-%m-%d")
def report_post_reach(self, ads_df):
post_ad_ids = ads_df[ads_df['ad_format'] == 9]['id'].tolist()
post_reach = self.post_reach("ad", post_ad_ids.copy())
post_reach_df = pd.DataFrame(post_reach).fillna(0)
table_id = f"{self.client_name}_VKontakte_{self.client_id}_POST_REACH"
self.bq.delete_and_insert(post_reach_df,
self.fields,
self.data_set_id,
table_id,
"%Y-%m-%d",
id=post_ad_ids)
def report_group_stat(self, group_id):
age, cities, countries, sex, sex_age, activity = self.get_group_stat(
group_id)
sex_df = pd.DataFrame(sex).fillna(0)
self.bq.data_to_insert(
sex_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_SEX_STAT",
"%Y-%m-%d")
age_df = pd.DataFrame(age).fillna(0)
self.bq.data_to_insert(
age_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_AGE_STAT",
"%Y-%m-%d")
sex_age_df = pd.DataFrame(sex_age).fillna(0)
self.bq.data_to_insert(
sex_age_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_SEX_AGE_STAT",
"%Y-%m-%d")
cities_df = pd.DataFrame(cities).fillna(0)
self.bq.data_to_insert(
cities_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_CITIES_STAT",
"%Y-%m-%d")
activity_df = pd.DataFrame(activity).fillna(0)
self.bq.data_to_insert(
activity_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_ACTIVITY",
"%Y-%m-%d")
countries_df = pd.DataFrame(countries).fillna(0)
self.bq.data_to_insert(
countries_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_GROUP_COUNTRIES_STAT",
"%Y-%m-%d")
def report_demographics(self, ads_ids):
sex, age, sex_age, cities = self.get_demographics(ads_ids, 100)
sex_df = pd.DataFrame(sex).fillna(0)
self.bq.data_to_insert(
sex_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_SEX_STAT",
"%Y-%m-%d")
age_df = pd.DataFrame(age).fillna(0)
self.bq.data_to_insert(
age_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_AGE_STAT",
"%Y-%m-%d")
sex_age_df = pd.DataFrame(sex_age).fillna(0)
self.bq.data_to_insert(
sex_age_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_SEX_AGE_STAT",
"%Y-%m-%d")
cities_df = pd.DataFrame(cities).fillna(0)
self.bq.data_to_insert(
cities_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_CITIES_STAT",
"%Y-%m-%d")
def report_campaigns_stat(self, campaign_ids):
campaign_stat = self.get_day_stats("campaign", campaign_ids, 100)
campaign_ids_with_stat = [
campaign_id['campaign_id'] for campaign_id in campaign_stat
]
campaign_stat_df = pd.DataFrame(campaign_stat).fillna(0)
self.bq.data_to_insert(
campaign_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_CAMPAIGN_STAT",
"%Y-%m-%d")
return campaign_ids_with_stat
def report_ads(self, campaign_ids_with_stat):
ads = self.get_ads(campaign_ids_with_stat)
ads_ids = [ad_id['id'] for ad_id in ads]
ads_df = pd.DataFrame(ads).fillna(0)
self.bq.insert_difference(
ads_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_ADS", 'id', 'id',
"%Y-%m-%d")
return ads_ids, ads_df
def report_ads_stat(self, ads_ids_with_stat):
ads_stat = self.get_day_stats("ad", ads_ids_with_stat, 100)
ads_stat_df = pd.DataFrame(ads_stat).fillna(0)
self.bq.data_to_insert(
ads_stat_df, self.fields, self.data_set_id,
f"{self.client_name}_VKontakte_{self.client_id}_ADS_STAT",
"%Y-%m-%d")
def __init__(self, path_to_json, db_name, db_table):
logging.Handler.__init__(self)
self.bq = BigQuery(path_to_json)
self.db_name = db_name
self.db_table = db_table