def __init__(self):
authorization_data = AuthorizationData(
developer_token=config.developer_token(),
authentication=OAuthAuthorization(client_id=config.oauth2_client_id(),
oauth_tokens=config.developer_token()),
)
self.client = super(BingReportClient, self).__init__(service='ReportingService',
authorization_data=authorization_data,
environment='production', version='v12')
def create_sdk_client(service, account_id):
# Creates SOAP client with OAuth refresh credentials for services
LOGGER.info('Creating SOAP client with OAuth refresh credentials for service: %s, account_id %s',
service, account_id)
authentication = get_authentication()
# Instance require to authenticate with Bing Ads
authorization_data = AuthorizationData(
account_id=account_id,
customer_id=CONFIG['customer_id'],
developer_token=CONFIG['developer_token'],
authentication=authentication)
return CustomServiceClient(service, authorization_data=authorization_data)
def create_sdk_client(service, account_id):
LOGGER.info('Creating SOAP client with OAuth refresh credentials')
authentication = OAuthWebAuthCodeGrant(
CONFIG['oauth_client_id'],
CONFIG['oauth_client_secret'],
'') ## redirect URL not needed for refresh token
authentication.request_oauth_tokens_by_refresh_token(CONFIG['refresh_token'])
authorization_data = AuthorizationData(
account_id=account_id,
customer_id=CONFIG['customer_id'],
developer_token=CONFIG['developer_token'],
authentication=authentication)
return CustomServiceClient(service, authorization_data)
def create_sdk_client(service, account_id):
LOGGER.info('Creating SOAP client with OAuth refresh credentials for service: %s, account_id %s',
service, account_id)
authentication = OAuthDesktopMobileAuthCodeGrant(
client_id=CONFIG['oauth_client_id'],
env='production'
) ## redirect URL not needed for refresh token
authentication.request_oauth_tokens_by_refresh_token(CONFIG['refresh_token'])
authorization_data = AuthorizationData(
account_id=account_id,
customer_id=CONFIG['customer_id'],
developer_token=CONFIG['developer_token'],
authentication=authentication)
return CustomServiceClient(service, authorization_data=authorization_data)
def create_sdk_client(service, account_id):
LOGGER.info(
"Creating SOAP client with OAuth refresh credentials for service: %s, account_id %s",
service,
account_id,
)
authentication = OAuthWebAuthCodeGrant(
CONFIG["oauth_client_id"], CONFIG["oauth_client_secret"], ""
) # redirect URL not needed for refresh token
authentication.request_oauth_tokens_by_refresh_token(CONFIG["refresh_token"])
authorization_data = AuthorizationData(
account_id=account_id,
customer_id=CONFIG["customer_id"],
developer_token=CONFIG["developer_token"],
authentication=authentication,
)
return CustomServiceClient(service, authorization_data=authorization_data)
def update_AWS():
# Download latest Database data
df_bing = pd.read_sql_query('''SELECT * FROM microsoft_spend;''', cnx)
# Get dates
max_db_date_plus_one = str(
pd.to_datetime(max(df_bing['Date'])) + timedelta(1))[:10]
yesterday = str(datetime.datetime.today() - timedelta(1))[:10]
yesterday_year = int(yesterday[:4])
yesterday_month = int(yesterday[5:7])
yesterday_day = int(yesterday[8:10])
# Update if necessary
if max_db_date_plus_one < yesterday:
print("Date last updated: " + max_db_date_plus_one)
# Get new data
print("Loading the web service client proxies...")
authorization_data = AuthorizationData(
account_id=None,
customer_id=None,
developer_token=DEVELOPER_TOKEN,
authentication=None,
)
reporting_service_manager = ReportingServiceManager(
authorization_data=authorization_data,
poll_interval_in_milliseconds=5000,
environment=ENVIRONMENT,
)
# In addition to ReportingServiceManager, you will need a reporting ServiceClient
# to build the ReportRequest.
reporting_service = ServiceClient(
service='ReportingService',
version=13,
authorization_data=authorization_data,
environment=ENVIRONMENT,
)
authenticate(authorization_data)
main(authorization_data)
# Import new data csv, clean, and add columns
# Import current csv with microsoft API data
df_bing = pd.read_csv('Bing_Ads_Data.csv', header=9)
df_bing['AllRevenue'] = df_bing['AllRevenue'].fillna(0).apply(
remove_comma).astype(float)
df_bing['Revenue'] = df_bing['Revenue'].fillna(0).apply(
remove_comma).astype(float)
df_bing['AllConversions'] = df_bing['AllConversions'].astype(float)
df_bing['Conversions'] = df_bing['Conversions'].astype(float)
# remove Microsoft copyright at bottom of csv
extra_text = '©2020 Microsoft Corporation. All rights reserved. '
df_bing = df_bing[df_bing['TimePeriod'] != extra_text].copy()
print(df_bing.tail())
df_grouped = df_bing.groupby(['TimePeriod', 'CampaignName'],
as_index=True)[[
'Spend', 'Impressions', 'Clicks',
'Revenue', 'Conversions'
]].sum().reset_index()
df_grouped['Channel'] = 'Bing'
df_grouped = df_grouped.rename(columns={'CampaignName': 'Campaign'})
df_grouped = df_grouped.merge(campaign_map,
how='left',
on=['Channel', 'Campaign'])
df_grouped['Category'] = df_grouped['Category'].fillna(
'SEM - Text - Product')
df_grouped = df_grouped[[
'Channel', 'TimePeriod', 'Campaign', 'Category', 'Spend',
'Impressions', 'Clicks', 'Conversions', 'Revenue'
]].copy()
df_grouped['Tuesday_Week'] = df_grouped['TimePeriod'].apply(
tuesday_week)
df_grouped = df_grouped.rename(columns={'TimePeriod': 'Date'})
# Append to existing table
# sqlEngine = create_engine('mysql+pymysql://root:@127.0.0.1/test', pool_recycle=3600)
dbConnection = cnx.connect()
tableName = 'microsoft_spend'
try:
frame = df_grouped.to_sql(tableName,
dbConnection,
if_exists='replace')
except ValueError as vx:
print(vx)
except Exception as ex:
print(ex)
else:
print("Table %s updated successfully." % tableName)
finally:
dbConnection.close()
else:
print("Data already updated")