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 []
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 []
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")
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")