In [1]:
#!/usr/bin/env python
from googleads import adwords
import pandas as pd
from StringIO import StringIO
In [2]:
class adwords_accounts(object):
def __init__(self, start, end, version='v201705'):
self._page_size = 500
self._client = adwords.AdWordsClient.LoadFromStorage()
self._version = version
self._all_accounts = {}
self._new_accounts_df = None
self._start = start
self._end = end
self._max_operations = 3000
def pull_accounts(self):
mcc = self._client.GetService('ManagedCustomerService', version=self._version)
offset = 0
selector = {
'fields': ['CustomerId', 'Name'],
'predicates': [{'field' : 'ExcludeHiddenAccounts',
'operator' : 'EQUALS' ,
'values' : 'true'
}],
'paging': {
'startIndex': str(offset),
'numberResults': str(self._page_size)
}
}
more_pages = True
while more_pages:
page = mcc.get(selector)
if 'entries' in page and page['entries']:
for account in page['entries']:
self._all_accounts[account['customerId']] = account['name']
offset += self._page_size
selector['paging']['startIndex'] = str(offset)
more_pages = offset < int(page['totalNumEntries'])
def get_accounts(self, filter_old=True):
if not self._all_accounts:
self.pull_accounts()
df = pd.DataFrame(self._all_accounts.items())
df.columns = ['account_id', 'account_name']
if filter_old:
df = df[df.account_name.str.contains('^[a-z]{2}\_jobseeker\_usd\_.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_aed\_.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_app\_promo.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_app\_active.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_disabled.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_display\_.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_content\_.*', regex=True, na=False)]
df = df[~df.account_name.str.contains('^.*\_dsa\_.*', regex=True, na=False)]
df['country'] = df.account_name.apply(lambda x: x[0:2])
self._new_accounts_df = df.sort_values('country')
def get_all_countries(self):
if self._new_accounts_df is None:
self.get_accounts()
return self._new_accounts_df.groupby('country')['country'].count().to_dict()
def get_non_eta_ads(self, account_id):
self._client.SetClientCustomerId(account_id)
report_downloader = self._client.GetReportDownloader(version=self._version)
qry = ('SELECT CampaignId, CampaignStatus, AdGroupId, AdGroupStatus, Id, AdType, Impressions, Clicks, Cost FROM '
'AD_PERFORMANCE_REPORT WHERE Status IN [ENABLED] AND CampaignStatus in [ENABLED, PAUSED] '
'AND AdGroupStatus in [ENABLED, PAUSED] DURING {0}, {1}').format(self._start, self._end)
stream_data = report_downloader.DownloadReportAsStringWithAwql(qry, 'CSV',
skip_report_header=True, skip_column_header=False,
skip_report_summary=True, include_zero_impressions=True)
report_data = StringIO(stream_data)
report_df = pd.DataFrame.from_csv(report_data, sep=',').reset_index()
report_df = report_df.sort_values('Impressions', ascending=True)
return report_df[report_df['Ad type']=='Text ad']
def get_all_ads(self, account_id):
self._client.SetClientCustomerId(account_id)
report_downloader = self._client.GetReportDownloader(version=self._version)
qry = ('SELECT CampaignId, AdGroupId, Id, AdType, Impressions, Clicks, Cost FROM '
'AD_PERFORMANCE_REPORT WHERE Status IN [ENABLED] AND CampaignStatus in [ENABLED, PAUSED] '
'AND AdGroupStatus in [ENABLED, PAUSED] DURING {0}, {1}').format(self._start, self._end)
stream_data = report_downloader.DownloadReportAsStringWithAwql(qry, 'CSV',
skip_report_header=True, skip_column_header=False,
skip_report_summary=True, include_zero_impressions=True)
report_data = StringIO(stream_data)
report_df = pd.DataFrame.from_csv(report_data, sep=',').reset_index()
report_df = report_df.sort_values('Impressions', ascending=True)
return report_df
def percent_standard_text_ad(self, ads):
grp = ads.groupby('Ad type')['Ad ID'].count()
enum = 1.*grp.get('Text ad', 0)
denom = sum(grp)
if denom==0:
return 9+enum
return round(enum/denom, 2)
def country_accounts(self, country):
accounts = self._new_accounts_df[self._new_accounts_df.country==country]
return accounts.set_index('account_id')['account_name'].to_dict()
def formatter(self, data):
tmp = pd.DataFrame(data.items())
tmp.columns = ['account', 'standart_text_ad_percentage']
tmp['standart_text_ad_percentage'] = tmp['standart_text_ad_percentage']*100
return tmp.set_index('account')
def country_standard_text_ad_coverage(self, country, clean=False):
accounts = self.country_accounts(country)
account_coverage = {}
for i in accounts:
ads = a.get_all_ads(i)
account_coverage[accounts[i]] = self.percent_standard_text_ad(ads)
if not clean:
return account_coverage
else:
return self.formatter(account_coverage)
def country_standard_text_ad_coverage_strict(self, country, clean=False):
accounts = self.country_accounts(country)
account_coverage = {}
for i in accounts:
ads = a.get_all_ads(i)
ads['ID'] = ads['Campaign ID'].astype(str) + ads['Ad group ID'].astype(str)
sta = ads[ads['Ad type']=='Text ad']
eta = ads[ads['Ad type']!='Text ad'] # contains (Extended) Dynamic search ad
gsta = sta.groupby('ID')['Ad type'].count().to_frame()
gsta.columns = ['n_sta']
geta = eta.groupby('ID')['Ad type'].count().to_frame()
geta.columns = ['n_eta']
print pd.merge(gsta, geta, how='left', left_index=True, right_index=True)
account_coverage[accounts[i]] = self.percent_standard_text_ad(ads)
if not clean:
return account_coverage
else:
return self.formatter(account_coverage)
def country_standard_text_ad_coverage_all(self):
frames = []
for i in self.get_all_countries():
if i in ('ae', 'ar', 'at', 'au', 'be', 'bh', 'br', 'ca', 'ch'):
continue
tmp = self.country_standard_text_ad_coverage(i, clean=True)
frames.append(tmp)
return pd.concat(frames)
def delete_multiple_ads(self, account_id, df_data, verbose=False):
self._client.SetClientCustomerId(account_id)
ad_group_ad_service = self._client.GetService('AdGroupAdService', version=self._version)
# construct operations
operations = []
for i, j in zip(df_data['Ad group ID'], df_data['Ad ID']):
operations.append({'operator': 'REMOVE',
'operand': {'xsi_type': 'AdGroupAd',
'adGroupId': i,
'ad': {'id': j}
}
})
# start deleting
for i in xrange(0, len(operations), self._max_operations):
tmp_operations = operations[i:i+self._max_operations]
result = ad_group_ad_service.mutate(tmp_operations)
if verbose:
for ad in result['value']:
print ('Ad with id "{0}" and type "{1}" was deleted.'.format(ad['ad']['id'], ad['ad']['Ad.Type']))
In [3]:
a = adwords_accounts('20171001', '20171020')
In [ ]:
countries = a.get_all_countries()
In [ ]:
x = a.country_standard_text_ad_coverage_strict('ca', True)
In [126]:
#x.to_csv('v.csv')
In [6]:
x
Out[6]:
In [73]:
non_etas = a.get_non_eta_ads('8745534751')
In [72]:
a.delete_multiple_ads(8745534751, non_etas)
In [71]:
len(non_etas)
Out[71]:
In [74]:
non_etas
Out[74]:
In [ ]: