def read_file_to_delete_segments(file_path):
read_df = None
try:
# Skip row 2 ([1]) tha indicates if field is mandatory or not
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
except:
return {"message": "File Path '{}' is not found".format(file_path)}
access_token = authenticate()
if "message" in access_token:
return access_token
segment_id_list = read_df["Segment ID"]
ref_id_list = read_df["Ref ID"]
segment_name_list = read_df["Segment Name"]
region_list = read_df["Region"]
fee_list = read_df["Fee"]
ttl_list = read_df["TTL"]
status_list = read_df["Status"]
write_delete_segment_result_list = []
for segment_id in segment_id_list:
delete_output = delete_segment(access_token, segment_id)
write_delete_segment_result_list.append(delete_output)
os.remove(file_path)
file_name_with_extension = file_path.split("/")[-1]
file_name = file_name_with_extension.split(".xlsx")[0]
# print("Ref ID Length: {}".format(len(ref_id_list)))
# print("Segment Name Length: {}".format(len(segment_name_list)))
# print("Region Length: {}".format(len(region_list)))
# print("Fee Length: {}".format(len(fee_list)))
# print("TTL Length: {}".format(len(ttl_list)))
# print("Segment ID Length: {}".format(len(segment_id_list)))
# print("Category Result Length: {}".format(len(write_category_result_list)))
# print("Add Segment Result Length: {}".format(len(write_add_segment_result_list)))
write_df = pd.DataFrame({
"Segment ID":
segment_id_list,
"Ref ID":
ref_id_list,
"Segment Name":
segment_name_list,
"Region":
region_list,
"Fee":
fee_list,
"TTL":
ttl_list,
"Status":
status_list,
"Delete Segment Result":
write_delete_segment_result_list
})
return write_excel.write(write_df, file_name + "_output_delete_segments",
SHEET_NAME)
def read_file_to_edit_segment_rates(file_path):
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
segment_id_list = read_df["Segment ID"]
brand_list = read_df["Brand"]
partner_id_list = read_df["Partner ID"]
advertiser_id_list = read_df["Advertiser ID"]
price_list = read_df["Price"]
price_type_list = read_df["Price Type"]
write_output_list = []
output_dict = {}
number_of_segments = len(segment_id_list)
rates_to_push_list = []
auth_code = authenticate()
if (auth_code == None):
return{'message':"ERROR: getting TTD Auth Code. Please check .sh file if credentials are correct."}
row_num = 0
for segment_id in segment_id_list:
brand = brand_list[row_num]
partner_id = partner_id_list[row_num]
advertiser_id = advertiser_id_list[row_num]
price = price_list[row_num]
price_type = price_type_list[row_num]
rates_to_push_list, append_rate_to_push_output = append_rates_to_push(brand, segment_id, partner_id, advertiser_id, price, price_type, rates_to_push_list)
if "api_error" in append_rate_to_push_output:
output_dict[row_num] = append_rate_to_push_output["api_error"]
row_num += 1
add_rates_output = add_rate(auth_code, rates_to_push_list)
if "api_error" in add_rates_output:
add_rates_output = add_rates_output["api_error"]
loop_counter = 0
while loop_counter < number_of_segments:
if loop_counter in output_dict:
write_output_list.append(output_dict[loop_counter])
else:
write_output_list.append(add_rates_output)
loop_counter += 1
write_df = pd.DataFrame({
"Segment ID": segment_id_list,
"Brand":brand_list,
"Partner ID": partner_id_list,
"Advertiser ID": advertiser_id_list,
"Price": price_list,
"Price Type": price_type_list,
"Output": write_output_list
})
return write_excel.write(write_df, "DONOTUPLOAD_The_Trade_Desk_Edit_Rates", SHEET_NAME)
def get_query_all():
global output
output = {}
global url
write_name = []
write_description = []
write_id = []
write_private_client_id = []
query_response = None
try:
oauth = authenticate()
if (oauth == None):
return {
'message':
"ERROR: authenticating Yahoo API. Please check .sh file if credentials are correct."
}
request_to_send = requests.get(url=url, auth=oauth)
print("Query Request: {}".format(request_to_send.url))
variables.logger.warning("{} Query Request: {}".format(
datetime.datetime.now().isoformat(), request_to_send.url))
query_response = request_to_send.json()
# print("Query Response: {}".format(query_response))
for segment in query_response:
read_child_segment(segment['name'], segment)
for segment_id in output:
output_segment = output[segment_id]
write_id.append(segment_id)
write_name.append(output_segment["name"])
write_description.append(output_segment["description"])
write_private_client_id.append(output_segment["private_client_id"])
# output_list = row.split("|")
# write_id.append(output_list[0])
# write_name.append(output_list[1])
# write_description.append(output_list[2])
write_df = pd.DataFrame({
"Segment ID": write_id,
"Segment Name": write_name,
"Segment Description": write_description,
"Private Client ID": write_private_client_id
})
return write_excel.write(write_df, "DONOTUPLOAD_Yahoo_" + "Query",
SHEET_NAME)
except:
if not query_response is None:
return {"message": "ERROR: " + query_response["message"]}
else:
return {
"message": "ERROR: Unknown error running function for Yahoo"
}
def query_all_fullname(auth_code):
query_data = get_query_all(auth_code)
segment_dict = store_segment_in_dict(query_data)
provider_id_list = []
provider_elementid_list = []
parent_elementid_list = []
display_name_list = []
buyable_list = []
description_list = []
full_name_list = []
for row in query_data["Result"]:
provider_id_list.append(row["ProviderId"])
provider_elementid_list.append(row["ProviderElementId"])
parent_elementid_list.append(row["ParentElementId"])
display_name_list.append(row["DisplayName"])
buyable_list.append(row["Buyable"])
description_list.append(row["Description"])
for provider_id_counter in range(len(provider_id_list)):
parent_element_id = parent_elementid_list[provider_id_counter]
display_name = display_name_list[provider_id_counter]
full_name = get_full_segment_name(parent_element_id, display_name,
segment_dict)
full_name_list.append(full_name)
write_df = pd.DataFrame({
"Data Provider Id": provider_id_list,
"Segment ID": provider_elementid_list,
"Parent Segment ID": parent_elementid_list,
"Segment Name": display_name_list,
"Segment Description": description_list,
"Buyable": buyable_list,
"Full Segment Name": full_name_list,
})
return write_excel.write(
write_df,
"DONOTUPLOAD_The_Trade_Desk_Query_All_Segments_Full_Names_Only")
def get_all_segments():
access_token = authenticate()
session = get_session(access_token)
segment_dict = {}
# taxonomy_id_list = get_taxonomy_ids(access_token, session)
# for taxonomy_id in taxonomy_id_list:
# segment_dict = get_segments(access_token, session, taxonomy_id, segment_dict)
segment_dict = get_segments(access_token, session, EYEOTA_TAXONOMY_ID,
segment_dict)
segment_key_list = segment_dict.keys()
uniques_list = []
segment_id_list = []
segment_name_list = []
segment_retail_cpm_list = []
segment_code_list = []
segment_buyable_list = []
segment_wholesale_cpm_list = []
segment_updated_on_list = []
segment_visibility_list = []
segment_revenue_share_pct_list = []
segment_organization_permission_list = []
segment_agencies_permissions_list = []
segmetn_advertisers_permissions_list = []
for segment_name in segment_key_list:
segment = segment_dict[segment_name]
segment_uniques = segment["uniques"]
segment_retail_cpm = segment["retail_cpm"]
segment_id = segment["id"]
segment_code = segment["code"]
segment_buyable = segment["buyable"]
segment_wholesale_cpm = segment["wholesale_cpm"]
segment_updated_on = segment["updated_on"]
segment_visibility = segment["visibility"]
segment_revenue_share_pct = segment["revenue_share_pct"]
segment_organization_permissions = segment["organization_permissions"]
segment_agencies_permissions = segment["agencies_permissions"]
segment_advertisers_permissions = segment["advertisers_permissions"]
uniques_list.append(segment_uniques)
segment_id_list.append(segment_id)
segment_name_list.append(segment_name)
segment_retail_cpm_list.append(segment_retail_cpm)
segment_code_list.append(segment_code)
segment_buyable_list.append(segment_buyable)
segment_wholesale_cpm_list.append(segment_wholesale_cpm)
segment_updated_on_list.append(segment_updated_on)
segment_visibility_list.append(segment_visibility)
segment_revenue_share_pct_list.append(segment_revenue_share_pct)
segment_organization_permission_list.append(
segment_organization_permissions)
segment_agencies_permissions_list.append(segment_agencies_permissions)
segmetn_advertisers_permissions_list.append(
segment_advertisers_permissions)
write_df = pd.DataFrame({
"mediamath_segment_id":
segment_id_list,
"segment_name":
segment_name_list,
"eyeota_segment_id":
segment_code_list,
"buyable":
segment_buyable_list,
"retail_cpm":
segment_retail_cpm_list,
"wholesale_cpm":
segment_wholesale_cpm_list,
"uniques":
uniques_list,
"updated_on":
segment_updated_on_list,
"visibility":
segment_visibility_list,
"revenue_share_pct":
segment_revenue_share_pct_list,
"organization_permissions":
segment_organization_permission_list,
"agencies_permissions":
segment_agencies_permissions_list,
"advertisers_permissions":
segmetn_advertisers_permissions_list
})
return write_excel.write(write_df, "DONOTUPLOAD_MediaMath_query_all")
def read_file_to_retrieve_custom_segments(file_path):
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
segment_id_list = read_df["Segment ID"]
partner_id_list = read_df["Partner ID"]
advertiser_id_list = read_df["Advertiser ID"]
write_provider_id_list = []
write_brand_list = []
write_parent_id_list = []
write_segment_name_list = []
write_segment_description_list = []
write_buyable_list = []
write_segment_full_name_list = []
write_cpm_list = []
write_currency_list = []
write_percent_of_media_list = []
write_output_list = []
auth_code = authenticate()
if (auth_code == None):
return{'message':"ERROR: getting TTD Auth Code. Please check .sh file if credentials are correct."}
# get all segment information
segment_json = get_query_all(auth_code)
segment_dict = store_segment_in_dict(segment_json)
segment_formatted_dictionary = {}
for row in segment_json['Result']:
provider_id = str(row['ProviderId'])
provider_element_id = str(row['ProviderElementId'])
parent_element_id = str(row['ParentElementId'])
display_name = str(row['DisplayName'])
buyable = row['Buyable']
description = str(row['Description'])
audience_size = str(row['AudienceSize'])
# loop to get full segment name
display_name = get_full_segment_name(parent_element_id, display_name, segment_dict)
segment_formatted_dictionary[provider_element_id] = {
"provider_id":provider_id,
"parent_id":parent_element_id,
"segment_name":display_name,
"segment_description":description,
"buyable":buyable,
"segment_full_name":display_full_name
}
row_num = 0
rates_dict = None
for segment_id in segment_id_list:
try:
segment_id = int(segment_id)
except:
pass
partner_id = partner_id_list[row_num]
advertiser_id = advertiser_id_list[row_num]
str_segment_id = str(segment_id)
# indicate if there is already an error for output
retrieve_output = False
rates_dict = get_segments_rates(auth_code, partner_id, advertiser_id, segment_id, rates_dict)
# check if segment details can be found
try:
segment_detail = segment_formatted_dictionary[str_segment_id]
write_provider_id_list.append(segment_detail["provider_id"])
write_parent_id_list.append(segment_detail["parent_id"])
write_segment_name_list.append(segment_detail["segment_name"])
write_segment_description_list.append(segment_detail["segment_description"])
write_buyable_list.append(segment_detail["buyable"])
write_segment_full_name_list.append(segment_detail["segment_full_name"])
# else if segment detail cannot be found
except:
write_provider_id_list.append(None)
write_parent_id_list.append(None)
write_segment_name_list.append(None)
write_segment_description_list.append(None)
write_buyable_list.append(None)
write_segment_full_name_list.append(None)
write_output_list.append("Segment cannot be found!")
retrieve_output = True
# check if segment id has rates
# if rate is not found, find its parent segment for rate until the parentmost segment has a rate
rate_found = False
while not rate_found:
try:
segment_rates = rates_dict[str_segment_id]
write_brand_list.append(segment_rates["Brand"])
write_cpm_list.append(segment_rates["CPM_Price"])
write_currency_list.append(segment_rates["CPM_CurrencyCode"])
write_percent_of_media_list.append(segment_rates["PercentOfMediaCost"])
# only indicate message if segment can be found (no output message added yet)
if not retrieve_output:
write_output_list.append(None)
rate_found = True
# segment id does not have rates
except:
try:
str_segment_id = segment_dict[str_segment_id]["parent_element_id"]
if not str_segment_id in rates_dict:
rates_dict = get_segments_rates(auth_code, partner_id, advertiser_id, str_segment_id, rates_dict)
# no more parent_id
except:
write_brand_list.append(None)
write_cpm_list.append(None)
write_currency_list.append(None)
write_percent_of_media_list.append(None)
# only indicate message if segment can be found (no output message added yet)
if not retrieve_output:
write_output_list.append("Segment Rates for Partner '{}' is not found!".format(partner_id))
# given up on finding rate since there isn't any parent id anymore
rate_found = True
row_num += 1
write_df = pd.DataFrame({
"Data Provider ID": write_provider_id_list,
"Segment ID": segment_id_list,
"Parent Segment ID": write_parent_id_list,
"Segment Name": write_segment_name_list,
"Segment Description": write_segment_description_list,
"Buyable": write_buyable_list,
"Brand":write_brand_list,
"Segment Full Name": write_segment_full_name_list,
"Partner ID": partner_id_list,
"Advertiser ID": advertiser_id_list,
"CPM Price": write_cpm_list,
"CPM Currency Code": write_currency_list,
"Percent Of Media Rate": write_percent_of_media_list,
"Output": write_output_list
})
return write_excel.write(write_df, "DONOTUPLOAD_The_Trade_Desk_Partner_Rates", SHEET_NAME)
def read_file_to_add_or_edit_segments(file_path, function):
file_name = file_path[7:len(file_path)-5]
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
rates_created_list = {}
segment_id_list = read_df['Segment ID']
parent_segment_id_list = read_df['Parent Segment ID']
segment_name_list = read_df['Segment Name']
segment_description_list = read_df['Segment Description']
buyable_list = read_df['Buyable']
brand_list = read_df['Brand']
partner_id_list = read_df['Partner ID']
advertiser_id_list = read_df['Advertiser ID']
price_list = read_df['Price']
price_type_list = read_df['Price Type']
data_provider_list = []
segment_full_path_list = []
ttd_account_manager_list = []
date_list = []
output_list = []
rates_output_list = []
rates_output_dict = {}
number_of_segments = len(segment_id_list)
rates_to_push_list = []
auth_code = authenticate()
if (auth_code == None):
return{'message':"ERROR: getting TTD Auth Code. Please check .sh file if credentials are correct."}
segment_json = get_query_all(auth_code)
segment_dict = store_segment_in_dict(segment_json)
# full_path_dict = {"bumcust":"Bombora > Custom", "eyecustomseg":"Custom Segment", "464":"Branded","eyeotabranded":"Branded > Eyeota"}
row_num = 0
for segment_id in segment_id_list:
parent_segment_id = parent_segment_id_list[row_num]
try:
parent_segment_id = str(int(parent_segment_id))
except:
pass
segment_name = segment_name_list[row_num]
segment_description = segment_name_list[row_num]
buyable = buyable_list[row_num]
brand = brand_list[row_num]
partner_id = partner_id_list[row_num]
advertiser_id = advertiser_id_list[row_num]
price = price_list[row_num]
price_type = price_type_list[row_num]
# full_path_keys = full_path_dict.keys()
segment_id_str = str(segment_id)
try:
segment_id_str = str(int(segment_id))
except:
pass
segment_dict[segment_id_str] = {"display_name":segment_name,"parent_element_id":parent_segment_id}
segment_dict_keys = segment_dict.keys()
# Gets segment full path
if parent_segment_id in segment_dict_keys:
# parent_segment_path = segment_dict[parent_segment_id]
# segment_full_path = "{} > {}".format(parent_segment_path, segment_name)
# # Adds the current segment to the full path dict
# full_path_dict[segment_id] = segment_full_path
# print("Segment Full Path: {}".format(segment_full_path))
segment_full_path = get_full_segment_name(parent_segment_id, segment_name, segment_dict)
segment_full_path_list.append(segment_full_path)
else:
segment_full_path_list.append("Error!!! Parent Segment ID Not Found!!")
data_provider_list.append("eyeota")
ttd_account_manager_list.append(None)
date_list.append(None)
output = add_or_edit(auth_code, segment_id, parent_segment_id, segment_name, buyable, segment_description, function)
if "api_error" in output:
output = output["api_error"]
output_list.append(output)
rate_output = None
# creating segment without issues (OK), create rates
if output == "OK":
if function == "Add":
# segments right below the root custom segment should add rate
if parent_segment_id == "bumcust" or parent_segment_id == "eyecustomseg" or parent_segment_id == "ttdratetest_partnerID_rate" or parent_segment_id == "464" or parent_segment_id == "eyeotabranded":
rates_to_push_list, rate_output = append_rates_to_push(brand, segment_id, partner_id, advertiser_id, price, price_type, rates_to_push_list)
rates_created_list[segment_id_str] = None
if "api_error" in rate_output:
rates_output_dict[row_num] = rate_output["api_error"]
# if parent segment is not bumcust, eyecustomseg, or ttdratetest_partnerID_rate, add the segment rate
elif parent_segment_id not in rates_created_list:
rates_to_push_list, rate_output = append_rates_to_push(brand, segment_id, partner_id, advertiser_id, price, price_type, rates_to_push_list)
rates_created_list[segment_id] = None
if "api_error" in rate_output:
rates_output_dict[row_num] = rate_output["api_error"]
if rate_output is None:
rates_output_dict[row_num] = None
row_num += 1
add_rates_output = None
if len(rates_to_push_list) > 0:
add_rates_output = add_rate(auth_code, rates_to_push_list)
if "api_error" in add_rates_output:
add_rates_output = add_rates_output["api_error"]
loop_counter = 0
while loop_counter < number_of_segments:
if loop_counter in rates_output_dict:
rates_output_list.append(rates_output_dict[loop_counter])
else:
rates_output_list.append(add_rates_output)
loop_counter += 1
write_df = pd.DataFrame({
"Data Provider ID": data_provider_list,
"Segment ID": segment_id_list,
"Parent Segment ID": parent_segment_id_list,
"Segment Name": segment_name_list,
"Segment Description": segment_description_list,
"Buyable": buyable_list,
"Segment Full Path": segment_full_path_list,
"CPM": price_list,
"Partner ID": partner_id_list,
"Advertiser ID": advertiser_id_list,
"TTD Account Manager": ttd_account_manager_list,
"Campaign Live Date": date_list,
"Price Type": price_type_list,
"Brand": brand_list,
"Output": output_list,
"Rates Output": rates_output_list
})
return write_excel.write(write_df, "DONOTUPLOAD_The_Trade_Desk_{}_{}".format(function, file_name), SHEET_NAME)
def read_file_to_retrieve_batch_id_status(file_path):
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
batch_id_checked = {}
write_segment_id_list = []
write_segment_name_list = []
write_segment_description_list = []
batch_id_list = read_df["Batch ID"]
write_brand_list = []
write_partner_id_list = []
write_advertiser_id_list = []
write_price_list = []
write_currency_list = []
write_processing_status_list = []
write_approval_status_list = []
write_error_list = []
write_batch_id_list = []
auth_code = authenticate()
if (auth_code == None):
return{'message':"ERROR: getting TTD Auth Code. Please check .sh file if credentials are correct."}
segment_json = get_query_all(auth_code)
segment_dict = store_segment_in_dict(segment_json)
segment_formatted_dictionary = {}
for row in segment_json['Result']:
provider_id = str(row['ProviderId'])
provider_element_id = str(row['ProviderElementId'])
parent_element_id = str(row['ParentElementId'])
display_name = str(row['DisplayName'])
buyable = row['Buyable']
description = str(row['Description'])
audience_size = str(row['AudienceSize'])
# loop to get full segment name
display_name = get_full_segment_name(parent_element_id, display_name, segment_dict)
segment_formatted_dictionary[provider_element_id] = {
"name":display_name,
"buyable":buyable,
"description":description
}
for batch_id in batch_id_list:
if not batch_id in batch_id_checked:
batch_id_status_output = retrieve_batch_id_status(auth_code, batch_id)
if "api_error" in batch_id_status_output:
write_segment_id_list.append(None)
write_segment_name_list.append(None)
write_segment_description_list.append(None)
write_brand_list.append(None)
write_partner_id_list.append(None)
write_advertiser_id_list.append(None)
write_price_list.append(None)
write_currency_list.append(None)
write_batch_id_list.append(None)
write_processing_status_list.append(None)
write_approval_status_list.append(None)
write_error_list.append(batch_id_status_output["api_error"])
else:
processing_status = batch_id_status_output["ProcessingStatus"]
approval_status = batch_id_status_output["ApprovalStatus"]
data_rates_output = batch_id_status_output["DataRates"]
for data_rate_output in data_rates_output:
segment_id = data_rate_output["ProviderElementId"]
write_segment_id_list.append(segment_id)
segment = segment_formatted_dictionary[segment_id]
write_segment_name_list.append(segment["name"])
write_segment_description_list.append(segment["description"])
brand_id = data_rate_output["BrandId"]
if brand_id == BOMBORA_BRAND_ID:
brand_id = "bombora"
elif brand_id == EYEOTA_BRAND_ID:
brand_id = "eyeota"
write_brand_list.append(brand_id)
return_partner_id = None
try:
return_partner_id = data_rate_output["PartnerId"]
except:
pass
write_partner_id_list.append(return_partner_id)
return_advertiser_id = None
try:
return_advertiser_id = data_rate_output["AdvertiserId"]
except:
pass
write_advertiser_id_list.append(return_advertiser_id)
write_price_list.append(float(data_rate_output["CPMRate"]["Amount"]))
write_currency_list.append(data_rate_output["CPMRate"]["CurrencyCode"])
write_batch_id_list.append(batch_id)
write_processing_status_list.append(processing_status)
write_approval_status_list.append(approval_status)
write_error_list.append(None)
batch_id_checked[batch_id] = None
write_df = pd.DataFrame({
"Segment ID":write_segment_id_list,
"Segment Name": write_segment_name_list,
"Segment_Description": write_segment_description_list,
"Brand": write_brand_list,
"Partner ID": write_partner_id_list,
"Advertiser ID": write_advertiser_id_list,
"Price": write_price_list,
"Currency": write_currency_list,
"Batch ID": write_batch_id_list,
"Processing Status": write_processing_status_list,
"Approval Status": write_approval_status_list,
"Error": write_error_list
})
return write_excel.write(write_df, "DONOTUPLOAD_The_Trade_Desk_BatchId_Status", SHEET_NAME)
def read_file_to_edit_segments(file_path):
read_df = None
try:
# Skip row 2 ([1]) tha indicates if field is mandatory or not
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
except:
return {"message": "File Path '{}' is not found".format(file_path)}
access_token = authenticate()
if "message" in access_token:
return access_token
categories_json = get_categories(access_token)
categories_dict_by_name = store_category_in_dict_by_name(categories_json)
segment_id_list = read_df["Segment ID"]
ref_id_list = read_df["Ref ID"]
segment_name_list = read_df["Segment Name"]
region_list = read_df["Region"]
fee_list = read_df["Fee"]
ttl_list = read_df["TTL"]
status_list = read_df["Status"]
write_segment_id_list = []
write_status_list = []
write_category_result_list = []
write_edit_segment_result_list = []
row_counter = 0
for segment_name in segment_name_list:
segment_id = segment_id_list[row_counter]
region = region_list[row_counter]
data_provider_id = "67"
if region.lower() == "apac":
data_provider_id = "11399"
status = status_list[row_counter]
index_of_separator = segment_name.rfind(" - ")
category_success = True
# Unable to find separator
if index_of_separator == -1:
segment_id_list.append(None)
write_category_result_list.append(
"No separator ' - ' is found in the Segment Name")
write_add_segment_result_list.append(None)
category_success = False
else:
child_segment_name = segment_name[index_of_separator + 3:]
category_name = segment_name[0:index_of_separator]
selected_categories_dict_by_name = categories_dict_by_name[
data_provider_id]
if category_name in selected_categories_dict_by_name:
category_id = selected_categories_dict_by_name[category_name]
else:
parent_category_id = None
category_name_list = category_name.split(" - ")
# if category does not exist, split the category name and check each and add if not available
is_parentmost = True
category_name_to_check = ""
temp_parent_category_id = None
for category_part_name in category_name_list:
if is_parentmost:
category_name_to_check = category_part_name
else:
category_name_to_check = category_name_to_check + " - " + category_part_name
# print("Category Name to Check: {}".format(category_name_to_check))
if category_name_to_check in selected_categories_dict_by_name:
temp_parent_category_id = selected_categories_dict_by_name[
category_name_to_check]
else:
temp_parent_category_id = add_category(
access_token, category_part_name, data_provider_id,
region, temp_parent_category_id)
if temp_parent_category_id == None:
segment_id_list.append(None)
write_category_result_list.append(
"Error creating category: {}".format(
category_part_name))
write_edit_segment_result_list.append(None)
category_success = False
# category creation success
else:
selected_categories_dict_by_name[
category_name_to_check] = temp_parent_category_id
is_parentmost = False
# if childmost category creation is successful
if category_success:
category_id = temp_parent_category_id
# category has been created/found, to create segment
if category_success:
write_category_result_list.append("OK")
ref_id = ref_id_list[row_counter]
fee = fee_list[row_counter]
ttl = ttl_list[row_counter]
fee_and_ttl_is_numeric = True
try:
fee = float(fee)
ttl = int(ttl)
except:
fee_and_ttl_is_numeric = False
segment_id_list.append(None)
write_edit_segment_result_list.append(
"TTL and Fee have to be numeric")
if status.lower() == "active":
status = "active"
else:
status = "inactive"
write_status_list.append(status)
if fee_and_ttl_is_numeric:
status_code, output = edit_segment(
access_token, segment_id, data_provider_id, region,
category_id, ref_id, fee, ttl, child_segment_name,
status)
if status_code == 200:
write_segment_id_list.append(output)
write_edit_segment_result_list.append("OK")
else:
write_segment_id_list.append(None)
write_edit_segment_result_list.append(output)
row_counter += 1
os.remove(file_path)
file_name_with_extension = file_path.split("/")[-1]
file_name = file_name_with_extension.split(".xlsx")[0]
# print("Ref ID Length: {}".format(len(ref_id_list)))
# print("Segment Name Length: {}".format(len(segment_name_list)))
# print("Region Length: {}".format(len(region_list)))
# print("Fee Length: {}".format(len(fee_list)))
# print("TTL Length: {}".format(len(ttl_list)))
# print("Segment ID Length: {}".format(len(segment_id_list)))
# print("Category Result Length: {}".format(len(write_category_result_list)))
# print("Add Segment Result Length: {}".format(len(write_add_segment_result_list)))
write_df = pd.DataFrame({
"Segment ID":
write_segment_id_list,
"Ref ID":
ref_id_list,
"Segment Name":
segment_name_list,
"Region":
region_list,
"Fee":
fee_list,
"TTL":
ttl_list,
"Status":
write_status_list,
"Category Result":
write_category_result_list,
"Edit Segment Result":
write_edit_segment_result_list
})
return write_excel.write(write_df, file_name + "_output_edit_segments",
SHEET_NAME)
def processJsonOutput(auth_code, json_output, function):
# try:
write_provider_id = []
write_provider_element_id = []
write_parent_element_id = []
write_segment_name = []
write_display_name = []
write_buyable = []
write_brand = []
write_description = []
write_audience_size = []
write_cpm_price = []
write_cpm_currency_code = []
write_percent_of_media_rate = []
segment_dictionary = store_segment_in_dict(json_output)
rates_dict = get_segments_rates(auth_code, None, None, None, None, None)
# Print results
for row in json_output['Result']:
provider_id = str(row['ProviderId'])
provider_element_id = str(row['ProviderElementId'])
parent_element_id = str(row['ParentElementId'])
display_name = str(row['DisplayName'])
buyable = row['Buyable']
description = str(row['Description'])
audience_size = str(row['AudienceSize'])
cpm_price = None
cpm_currency_code = None
percent_of_media_rate = None
brand = None
segment_rate = get_segment_rate(provider_element_id, rates_dict,
segment_dictionary)
try:
cpm_price = segment_rate["CPM_Price"]
cpm_currency_code = segment_rate["CPM_CurrencyCode"]
percent_of_media_rate = segment_rate["PercentOfMediaCost"]
brand = segment_rate["Brand"]
except:
cpm_price = "Segment is a custom segment which requires Partner ID. Please use Retrieve Segment Rates function for this segment."
# loop to get full segment name
full_display_name = get_full_segment_name(parent_element_id,
display_name,
segment_dictionary)
try:
provider_element_id = int(provider_element_id)
except:
pass
try:
parent_element_id = int(parent_element_id)
except:
pass
write_provider_id.append(provider_id)
write_provider_element_id.append(provider_element_id)
write_parent_element_id.append(parent_element_id)
write_segment_name.append(display_name)
write_description.append(description)
write_buyable.append(buyable)
write_brand.append(brand)
write_display_name.append(full_display_name)
write_audience_size.append(audience_size)
write_cpm_price.append(cpm_price)
write_cpm_currency_code.append(cpm_currency_code)
write_percent_of_media_rate.append(percent_of_media_rate)
write_df = pd.DataFrame({
"Data Provider ID":
write_provider_id,
"Segment ID":
write_provider_element_id,
"Parent Segment ID":
write_parent_element_id,
"Segment Name":
write_segment_name,
"Segment Description":
write_description,
"Buyable":
write_buyable,
"Brand":
write_brand,
"Full Segment Name":
write_display_name,
"CPM Price":
write_cpm_price,
"CPM Currency Code":
write_cpm_currency_code,
"Percent of Media Rate":
write_percent_of_media_rate
})
return write_excel.write(write_df,
"DONOTUPLOAD_The_Trade_Desk_" + function)
def read_all_to_add_segments(file_path):
start_time = time.time()
token = authenticate()
read_df = None
try:
# Skip row 2 ([1]) tha indicates if field is mandatory or not
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
except:
return {"message": "File Path '{}' is not found".format(file_path)}
account_token_list = read_df["Account"]
segment_id_list = read_df["Eyeota Segment ID"]
segment_name_list = read_df["Segment Name"]
segment_description_list = read_df["Segment Description"]
cpm_list = read_df["CPM"]
lifetime_list = read_df["Lifetime"]
write_remarks_list = []
row_counter = 0
for segment_name in segment_name_list:
account_token = account_token_list[row_counter]
segment_id = segment_id_list[row_counter]
segment_description = segment_description_list[row_counter]
try:
cpm = float(cpm_list[row_counter])
lifetime = int(lifetime_list[row_counter])
add_segment_request = requests.post(
SEGMENT_URL,
headers={
'Content-Type': 'application/json',
'Authorization': "Bearer " + token
},
json={
"account_token": account_token,
"partner": PARTNER,
"segment_name": segment_name,
"segment_description": segment_description,
"partner_segment_id": str(segment_id),
"billing_through_tubemogul": "T",
"cpm": cpm,
"retention_window_in_days": lifetime
})
print("Add Segment URL: {}".format(add_segment_request.url))
variables.logger.warning("{} Add Segment URL: {}".format(
datetime.datetime.now().isoformat(), add_segment_request.url))
add_segment_json = add_segment_request.json()
if add_segment_request.status_code == 200:
write_remarks_list.append(add_segment_json["segment_id"])
else:
write_remarks_list.append(add_segment_json)
except:
write_remarks_list.append(
"account_token is incorrect (shouldn't be only numbers), CPM is not decimal or Lifetime is not integer"
)
row_counter += 1
# Check if token is expired
start_time, token = check_token_timeout(start_time, token)
write_df = pd.DataFrame({
"Account": account_token_list,
"Eyeota Segment ID": segment_id_list,
"Segment Name": segment_name_list,
"Segment Description": segment_description_list,
"CPM": cpm_list,
"Lifetime": lifetime_list,
"Remarks": write_remarks_list
})
file_name_with_extension = file_path.split("/")[-1]
file_name = file_name_with_extension.split(".xlsx")[0]
os.remove(file_path)
return write_excel.write(write_df, file_name + "output_add_segments",
SHEET_NAME)
def read_all_to_edit_segments(file_path):
start_time = time.time()
token = authenticate()
read_df = None
try:
# Skip row 2 ([1]) tha indicates if field is mandatory or not
read_df = pd.read_excel(file_path, sheet_name=SHEET_NAME, skiprows=[1])
except:
return {"message":"File Path '{}' is not found".format(file_path)}
account_token_list = read_df["Account"]
segment_id_list = read_df["Eyeota Segment ID"]
segment_name_list = read_df["Segment Name"]
segment_description_list = read_df["Segment Description"]
status_list = read_df["Status"]
cpm_list = read_df["CPM"]
lifetime_list = read_df["Lifetime"]
adcloud_segment_id_list = read_df["AdCloud Segment ID"]
write_remarks_list = []
row_counter = 0
for segment_id in segment_id_list:
account_token = account_token_list[row_counter]
segment_name = segment_name_list[row_counter]
segment_description = segment_description_list[row_counter]
adcloud_id = adcloud_segment_id_list[row_counter]
try:
status = status_list[row_counter]
if status:
status = "active"
else:
status = "inactive"
cpm = float(cpm_list[row_counter])
lifetime = int(lifetime_list[row_counter])
edit_segment_request = requests.put(SEGMENT_URL + "/" + str(adcloud_id),
headers={
'Content-Type':'application/json',
'Authorization':"Bearer " + token
},
json={
"account_token":account_token,
"partner": PARTNER,
"segment_name": segment_name,
"segment_description": segment_description,
"partner_segment_id": str(segment_id),
"billing_through_tubemogul":"T",
"cpm": cpm,
"retention_window_in_days": lifetime,
"status": status
}
)
print("Edit Segment URL: {}".format(edit_segment_request.url))
if edit_segment_request.status_code == 200:
write_remarks_list.append("Edited successfully")
else:
write_remarks_list.append(edit_segment_request.json())
except:
write_remarks_list.append("CPM is not decimal or Lifetime is not integer")
row_counter += 1
# Check if token is expired
start_time, token = check_token_timeout(start_time, token)
write_df = pd.DataFrame({
"Account":account_token_list,
"Eyeota Segment ID":segment_id_list,
"Segment Name":segment_name_list,
"Segment Description":segment_description_list,
"Status": status_list,
"CPM":cpm_list,
"Lifetime":lifetime_list,
"Remarks":write_remarks_list
})
file_name_with_extension = file_path.split("/")[-1]
file_name = file_name_with_extension.split(".xlsx")[0]
os.remove(file_path)
return write_excel.write(write_df, file_name + "output_edit_segments")