def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
adwords_client = adwords.AdWordsClient.LoadFromStorage()
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select('CampaignId', 'AdGroupId', 'Id',
'Criteria', 'CriteriaType',
'FinalUrls', 'Impressions',
'Clicks', 'Cost')
# .Select('cuenta', 'campaña', 'estadoDeLaCampaña', 'mes',
# 'moneda', 'clics', 'impresiones', 'coste',
# 'conversiones', 'costeConv')
.From('CRITERIA_PERFORMANCE_REPORT')
# .Where('Status').In('ENABLED', 'PAUSED')
# .During('LAST_7_DAYS')
.Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
sys.stdout,
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False,
include_zero_impressions=True)
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignId', 'AdGroupId', 'Id', 'Criteria',
'CriteriaType', 'FinalUrls', 'Impressions', 'Clicks',
'Cost')
.From('CRITERIA_PERFORMANCE_REPORT')
.Where('Status').In('ENABLED', 'PAUSED')
.During('LAST_7_DAYS')
.Build())
# You can provide a file object to write the output to. For this
#
file = open('data.csv', 'w+')
report_downloader.DownloadReportWithAwql(
report_query, 'CSV', file, skip_report_header=True,
skip_column_header=False, skip_report_summary=True,
include_zero_impressions=True)
file = open('data.csv', 'r')
data = pd.read_csv(file)
for i in range(len(data)):
if data['Impressions'][i] < 10:
pause_campaign(client,data['Campaign ID'][i])
def getConversion(start_date, end_date, client):
output = io.StringIO()
report_downloader = client.GetReportDownloader(version='v201809')
report_query = (adwords.ReportQueryBuilder().Select(
'Date', 'CampaignName', 'ConversionTypeName', 'Conversions').From(
'CAMPAIGN_PERFORMANCE_REPORT').Where('CampaignStatus').In(
'ENABLED', 'PAUSED',
'REMOVED').During(start_date + ',' + end_date).Build())
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
output,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=False)
output.seek(0)
types = {'Conversions': np.float64}
df_conversion = pd.read_csv(output,
low_memory=False,
dtype=types,
keep_default_na=False)
df_conversion['Day'] = df_conversion['Day'].apply(
lambda s: datetime.strptime(s, '%Y-%m-%d').date())
print(df_conversion)
return df_conversion
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('Id', 'AdNetworkType1', 'Impressions')
.From('CRITERIA_PERFORMANCE_REPORT')
.Where('Status').In('ENABLED', 'PAUSED')
.During('LAST_7_DAYS')
.Build())
# Retrieve the report stream.
stream = report_downloader.DownloadReportAsStreamWithAwql(
report_query, 'CSV', skip_report_header=True, skip_column_header=True,
skip_report_summary=True, include_zero_impressions=True)
ad_network_map = collections.defaultdict(int)
try:
while True:
line = stream.readline()
if not line: break
process_line(line, ad_network_map=ad_network_map)
finally:
stream.close()
print('Total impressions by ad network type 1:')
for ad_network_type, impressions in sorted(ad_network_map.items()):
print(('%s: %s' % (ad_network_type, impressions)))
def get_adgroup_report_performance(client, CampaignId):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('Clicks', 'Impressions',
'Cost')
.From('CAMPAIGN_PERFORMANCE_REPORT')
.During('LAST_7_DAYS')
.Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report_downloader.DownloadReportWithAwql(
report_query, 'CSV',open("../user__reporting/campaign_report/campagne.csv", "w"), skip_report_header=True,
skip_column_header=False, skip_report_summary=False,
include_zero_impressions=True)
result= []
input_file = csv.DictReader(open("../user__reporting/campaign_report/campagne.csv"))
for row in input_file:
result.append(row)
report = []
for data in result:
report.append({
"clicks": data['Clicks'],
"cost": data['Cost'],
"impressions": data['Impressions']
})
return report
def run_Hour0fDay_kip_report(key, acc_id, start_date, end_date):
'''
This functioin is using report_downloader = adwords_client.GetReportDownloader(version='v201809') and 'CAMPAIGN_PERFORMANCE_REPORT'
for more detail, please visit:
input is the adwords account ID in dictionary items, start date, and end date, date format is yyymmdd
out put is the campaign level reports
'''
'''
Input variables:
1. key (brand name): from Dictionary Ad_acc. Type: string
2. acc_id (adwords account ID): from Dictionary Ad_acc. Type: string
3. start_date: report start date. Type: yyymmdd
4. end_date: report end date. Type: yyymmdd
Output: csv file
'''
# Define output as a string
output = io.StringIO()
# Initialize client object.
adwords_client = adwords.AdWordsClient.LoadFromStorage(
'.../googleads.yaml')
adwords_client.SetClientCustomerId(acc_id)
report_downloader = adwords_client.GetReportDownloader(version='v201809')
report_query = (adwords.ReportQueryBuilder().Select(
'CampaignId', 'CampaignName', 'Date', 'DayOfWeek', 'HourOfDay',
'Clicks', 'Impressions', 'Cost', 'Conversions',
'ConversionValue').From('CAMPAIGN_PERFORMANCE_REPORT').Where(
'CampaignStatus').In('ENABLED').During(end_date + ',' +
start_date).Build())
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
output,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=False)
output.seek(0)
types = {
'CampaignId': pd.np.int64,
'Clicks': pd.np.float64,
'Impressions': pd.np.float64,
'Cost': pd.np.float64,
'Conversions': pd.np.float64,
'ConversionValue': pd.np.float64
}
df = pd.read_csv(output, low_memory=False, dtype=types, na_values=[' --'])
# delete the first and last column
df['Brand'] = key
# micro amount 1000000
df['Cost'] = df.Cost / 1000000
print(df.head())
return df
def main(client,date,full_date):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignName','CampaignStatus','AdGroupName','AdGroupStatus','AverageCpc','AverageCpe',
'AverageCpm','AverageCpv','Clicks','Conversions','ConversionRate',
'Cost','Ctr','Date','CustomerDescriptiveName','DayOfWeek',
'Device','Impressions','Interactions','VideoViews','VideoQuartile25Rate',
'VideoQuartile50Rate','VideoQuartile75Rate','VideoQuartile100Rate',
'GmailForwards','GmailSaves','Status','Criteria')
.From('GENDER_PERFORMANCE_REPORT') #these are the fields we are pulling for our report
#.Where('Status').In('ENABLED') #further filter the attributes we are pulling using where clause
.During(date) #custom date can be specified (look at age performance report for the syntax)
#.During("20200210,20200210")
.Build())
x=(report_downloader.DownloadReportAsStringWithAwql(
report_query, 'CSV', skip_report_header=True, skip_column_header=False,
skip_report_summary=True, include_zero_impressions=True))
#print(x) #we are printing the report and storing it in a CSV file
f=open("gender_report"+full_date+".csv","w")
f.write(x)
f.close()
#l = full_date.split("-")
filename = "GoogleAds/landing/gender_report/"+"gender_report_"+full_date+".csv"
importtos3.write_to_s3(x,'paragon-datalake',filename)
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder().Select(
'Amount', 'AverageCpm', 'Cost', 'Clicks', 'Impressions', 'AverageCost',
'InteractionTypes', 'ServingStatus',
'TotalAmount').From('CAMPAIGN_PERFORMANCE_REPORT').Where(
'CampaignId').In('6445993347').During('LAST_7_DAYS').Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report = report_downloader.DownloadReportAsStreamWithAwql(
report_query,
'CSV',
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False,
include_zero_impressions=True)
""" response = report.read().decode('utf-8')
s = response.splitlines(0)
x = csv.reader(s)
print(list(x)) """
tab = []
result = report.read().decode('utf-8')
s = result.splitlines(0)
x = csv.reader(s)
data = pd.read_csv(report.read())
print(data.head())
""" reader = csv.DictReader(report, fieldnames = ( "Budget","Avg. CPM","Cost","Clicks", "Impressions,Avg. Cost", "Interaction Types", "Campaign serving status", "Total Budget amount" ))
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201806')
# Create report query.
report_query = (adwords.ReportQueryBuilder().Select(
'CampaignId', 'AdGroupId', 'Id', 'Criteria', 'CriteriaType',
'FinalUrls', 'Impressions', 'Clicks',
'Cost').From('CRITERIA_PERFORMANCE_REPORT').Where('Status').In(
'ENABLED', 'PAUSED').During('LAST_7_DAYS').Build())
print report_downloader.DownloadReportAsStringWithAwql(
report_query,
'CSV',
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False,
include_zero_impressions=True)
# Retrieve the report stream and print it out
report_data = StringIO.StringIO()
stream_data = report_downloader.DownloadReportAsStreamWithAwql(
report_query, 'CSV')
try:
while True:
chunk = stream_data.read(CHUNK_SIZE)
if not chunk: break
report_data.write(
chunk.decode() if sys.version_info[0] == 3
and getattr(report_data, 'mode', 'w') == 'w' else chunk)
print report_data.getvalue()
finally:
report_data.close()
stream_data.close()
def main(client, date, full_date):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select(
'AdGroupName', 'AdGroupStatus', 'AllConversions', 'AverageCpc',
'AverageCpm', 'AverageCpv', 'CampaignName', 'CampaignStatus',
'CityCriteriaId', 'Clicks', 'ConversionRate', 'Conversions',
'Cost', 'Ctr', 'Date', 'CustomerDescriptiveName', 'DayOfWeek',
'Device', 'Impressions', 'InteractionRate', 'Interactions',
'LocationType', 'MetroCriteriaId', 'VideoViews',
'RegionCriteriaId',
'CountryCriteriaId') # we are pulling these fields for our report
.From('GEO_PERFORMANCE_REPORT')
#.Where('CampaignStatus').In('ENABLED') #futher filter our attributes
.During(date) # to provide a custom date ('DURING 20150201,20150301')
.Build())
x = (report_downloader.DownloadReportAsStringWithAwql(
report_query,
'CSV',
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True))
#print(x) #we are printing the report and storing it in a CSV file
f = open("geo_report" + full_date + ".csv", "w")
f.write(x)
f.close()
#l = full_date.split("-")
filename = "GoogleAds/landing/geo_report/" + "geo_report_" + full_date + ".csv"
importtos3.write_to_s3(x, 'paragon-datalake', filename)
def main(client):
output_file = 'day'+str(n)+'.csv'
# print('file made')
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# print('report downloader')
reportCSV = str()
# print('csv')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignName','Impressions', 'Clicks',
'Cost') # add any other required columns
.From('CAMPAIGN_PERFORMANCE_REPORT')
.During(date+','+date)
.Build())
# print('query')
orig_stdout = sys.stdout
with open(output_file, 'w'):
sys.stdout = open(output_file, 'w')
reportCSV = report_downloader.DownloadReportWithAwql(
report_query, 'CSV', sys.stdout, skip_report_header=False,
skip_column_header=False, skip_report_summary=False,
include_zero_impressions=False)
sys.stdout.close()
sys.stdout=orig_stdout
return(reportCSV)
def getCampaignCosts(self):
# Initialize appropriate service.
report_downloader = self.client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select('CampaignId', 'CampaignName',
'AdGroupId', 'AdGroupName'
'Cost').
From(
'ADGROUP_PERFORMANCE_REPORT'
) # https://developers.google.com/adwords/api/docs/appendix/reports
# .Where('Status').In('ENABLED', 'PAUSED')
.During('LAST_7_DAYS').Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
with io.StringIO() as f:
report_downloader.DownloadReportWithAwql(
report_query,
'CSV',
f,
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False,
include_zero_impressions=True)
# f.close()
return f.getvalue()
def main(client, date, full_date):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select(
'AccountDescriptiveName', 'Amount', 'AverageCost', 'AverageCpc',
'AverageCpe', 'AverageCpm', 'AverageCpv', 'AveragePosition',
'BounceRate', 'CampaignName', 'AverageTimeOnSite',
'AverageFrequency', 'AveragePageviews', 'CampaignStatus',
'CampaignTrialType', 'ClickAssistedConversions',
'ClickAssistedConversionsOverLastClickConversions',
'ClickAssistedConversionValue', 'Clicks',
'ContentBudgetLostImpressionShare', 'ContentImpressionShare',
'ContentRankLostImpressionShare', 'Conversions', 'Cost',
'CostPerConversion', 'Ctr', 'CustomerDescriptiveName', 'Date',
'EndDate', 'StartDate', 'GmailForwards', 'GmailSaves',
'GmailSecondaryClicks', 'HasRecommendedBudget',
'ImpressionAssistedConversions',
'ImpressionAssistedConversionsOverLastClickConversions',
'ImpressionAssistedConversionValue', 'ImpressionReach',
'Impressions', 'Interactions', 'InteractionRate',
'InvalidClickRate', 'InvalidClicks', 'IsBudgetExplicitlyShared',
'PercentNewVisitors', 'Period', 'RecommendedBudgetAmount',
'RelativeCtr', 'SearchAbsoluteTopImpressionShare',
'SearchBudgetLostAbsoluteTopImpressionShare',
'SearchBudgetLostImpressionShare',
'SearchBudgetLostTopImpressionShare', 'SearchClickShare',
'SearchExactMatchImpressionShare', 'SearchImpressionShare',
'SearchRankLostAbsoluteTopImpressionShare',
'SearchRankLostImpressionShare',
'SearchRankLostTopImpressionShare', 'SearchTopImpressionShare',
'ServingStatus', 'VideoQuartile75Rate', 'VideoQuartile25Rate',
'VideoQuartile50Rate', 'VideoQuartile100Rate', 'VideoViews',
'VideoViewRate'
) # these are the fields we are using to generate our report
.From('CAMPAIGN_PERFORMANCE_REPORT') #name of the report
#.Where('Status').In('ENABLED') #we can filter the attribiutes we are pulling using where clause
#.During(date) # custom date can be specified ('DURING 20150201,20150301')
.During(date).Build())
x = (report_downloader.DownloadReportAsStringWithAwql(
report_query,
'CSV',
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=True))
#print(x) #we are printing the report and storing it in a CSV file
f = open("campaign_report_" + full_date + ".csv", "w")
f.write(x)
f.close()
#l = full_date.split("-")
filename = "GoogleAds/landing/campaign_report/" + "campaign_report_" + full_date + ".csv"
importtos3.write_to_s3(x, 'paragon-datalake', filename)
def get_report_from_google(ds, **kwargs):
start = datetime.today().date().isoformat().replace("-", "")
end = datetime.now() + timedelta(days=-int(backlogdays))
end = end.date().isoformat().replace("-", "")
ddd = end + ',' + start
if rd.exists(dag_name) == 1:
start = datetime.today().date().isoformat().replace("-", "")
end = datetime.now() + timedelta(days=-the_day)
end = end.date().isoformat().replace("-", "")
ddd = end + ',' + start
# Define output as a string
output = io.StringIO()
# Initialize client object.
adwords_client = adwords.AdWordsClient.LoadFromStorage(
'/usr/local/airflow/keys/YAML_FILE')
adwords_client.SetClientCustomerId(account_id)
report_downloader = adwords_client.GetReportDownloader(version='v201809')
report_query = (adwords.ReportQueryBuilder().Select(
'Id', 'AdGroupId', 'AdGroupName', 'Headline', 'CampaignId',
'CampaignName', 'Date', 'DayOfWeek', 'Clicks', 'Impressions', 'Cost',
'Conversions', 'ConversionValue').From('AD_PERFORMANCE_REPORT').Where(
'CampaignStatus').In('ENABLED').During(ddd).Build())
print(report_query)
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
output,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=False)
output.seek(0)
types = {
'CampaignId': pd.np.int64,
'Clicks': pd.np.int32,
'Impressions': pd.np.int32,
'Cost': pd.np.float64,
'Conversions': pd.np.float64,
'ConversionValue': pd.np.float64
}
df = pd.read_csv(output, low_memory=False, dtype=types, na_values=[' --'])
rd.hset(dag_name, "account_id", account_id)
return df
def main():
start_date = dt.date.today().isoformat().replace('-', '')
end_date = (dt.date.today() - dt.timedelta(days=30)).isoformat().replace(
"-", "")
# retrieve data
output = io.StringIO()
adwords_client = adwords.AdWordsClient.LoadFromStorage(
'../credentials/googleads.yaml')
adwords_client.SetClientCustomerId(ACCOUNT_ID)
report_downloader = adwords_client.GetReportDownloader(version='v201809')
################## BUILD QUERY HERE ##################
report_query = \
(
adwords.ReportQueryBuilder()
.Select('Clicks', 'Id', 'AdGroupId', 'CampaignId', 'Clicks',
'AverageCpc', 'AverageCpm')
.From('AD_PERFORMANCE_REPORT')
.Where('CampaignStatus').In('ENABLED')
.During(end_date + ','+start_date)
.Build()
)
try:
report_downloader.DownloadReportWithAwql(
report_query,
'CSV',
output,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=False)
except googleads.errors.AdWordsReportBadRequestError as error:
raise error
output.seek(0)
types = \
{
'CampaignId': pd.np.int64, 'Clicks': pd.np.float64,
'Impressions': pd.np.float64, 'Cost': pd.np.float64,
'Conversions': pd.np.float64, 'ConversionValue': pd.np.float64
}
df = pd.read_csv(output, low_memory=False, na_values=[' --'])
# df = pd.read_csv(output, low_memory=False, dtype=types, na_values=[' --'])
print(df.head())
df.to_csv("test.csv")
def main(client,fileObject):
report_downloader = client.GetReportDownloader(version='v201705')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('AccountDescriptiveName','Date','Device','CampaignName','AdGroupName','Impressions','Clicks','Cost','Conversions', 'AveragePosition')
.From('ADGROUP_PERFORMANCE_REPORT')
.During('LAST_30_DAYS')
.Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report_downloader.DownloadReportWithAwql(
report_query, 'CSV', fileObject, skip_report_header=True,
skip_column_header=False, skip_report_summary=True,
include_zero_impressions=False)
def get_campaign_report_performance(client, CampaignId, startDate, endDate):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignId', 'Clicks', 'Impressions',
'Cost')
.From('CAMPAIGN_PERFORMANCE_REPORT')
.Where('CampaignId').In(CampaignId)
.During('TODAY')
.Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
dirname = os.path.dirname(__file__)
print(dirname)
filename = "campagne.csv"
path = os.path.join(dirname, "../../uploads/"+filename)
#dirname = os.path.dirname(filename)
#print(os.path.exists(dirname))
#if not os.path.exists(dirname):
# print('creating')
# os.makedirs(dirname)
report_downloader.DownloadReportWithAwql(
report_query, 'CSV',open(path, "w"), skip_report_header=True,
skip_column_header=False, skip_report_summary=False,
include_zero_impressions=True)
result= []
input_file = csv.DictReader(open(path))
for row in input_file:
result.append(row)
report = []
for data in result:
report.append({
"id": data['Campaign ID'],
"clicks": data['Clicks'],
"cost": data['Cost'],
"impressions": data['Impressions']
})
print(report)
return report
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('Clicks', 'Impressions', 'Cost', 'CampaignId', 'CampaignName', 'AdGroupId', 'AdGroupName', 'Id', 'Status')
# .From('CRITERIA_PERFORMANCE_REPORT')
.From('KEYWORDS_PERFORMANCE_REPORT')
.Build())
return report_downloader.DownloadReportAsStringWithAwql(
report_query, 'CSV', skip_report_header=True, skip_column_header=False,
skip_report_summary=True, include_zero_impressions=True)
def prepare_report_query(client: AdWordsClient, data_source: GoogleAdwordsDataSource):
"""
Prepare a query on the data_source defined report (From clause)
with the data_source given clauses
"""
report_downloader = client.GetReportDownloader()
report_query_builder = adwords.ReportQueryBuilder()
# Build select
report_query_builder.Select(data_source.columns)
# Build from
report_query_builder.From(data_source.from_clause)
# Build where
apply_filter(report_query_builder, data_source.parameters)
# Build during
report_query_builder.During(data_source.during.strip())
return report_downloader, report_query_builder.Build()
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201806')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignId', 'AdGroupId', 'Id', 'Criteria',
'CriteriaType', 'FinalUrls', 'Impressions', 'Clicks',
'Cost')
.From('CRITERIA_PERFORMANCE_REPORT')
.Where('Status').In('ENABLED', 'PAUSED')
.During('LAST_7_DAYS')
.Build())
print report_downloader.DownloadReportAsStringWithAwql(
report_query, 'CSV', skip_report_header=False, skip_column_header=False,
skip_report_summary=False, include_zero_impressions=True)
def test__should__be_able_to_get_report_stream__when__choosing_one_query(
self):
# Load secrets via env vars
execfile("../../secrets.py")
conn = GoogleAdWordsConnectionManager(
GoogleAdWordsConnectionSettings(
client_id=os.getenv("adwords_client_id"),
user_agent="Tester",
client_customer_id=os.getenv("adwords_client_customer_id"),
secrets_manager=GoogleAdWordsSecretsManager()))
ad_util = AdWordsReportReader(conn=conn)
report_query = (adwords.ReportQueryBuilder().Select(
'AdNetworkType1', 'Impressions', 'Clicks').From(
'CAMPAIGN_PERFORMANCE_REPORT').During('YESTERDAY').Build())
actual = ad_util.awql_to_dataframe(query=report_query)
print(actual)
expected = (17046, 3)
self.assertEqual(expected, actual.shape)
def test__negative_keyword_reports(self):
# Load secrets via env vars
execfile("../../secrets.py")
conn = GoogleAdWordsConnectionManager(
GoogleAdWordsConnectionSettings(
client_id=os.getenv("adwords_client_id"),
user_agent="Tester",
client_customer_id=os.getenv("adwords_client_customer_id"),
secrets_manager=GoogleAdWordsSecretsManager()))
ad_util = AdWordsReportReader(conn)
report_query = (adwords.ReportQueryBuilder().Select(
'AccountDescriptiveName', 'CampaignId', 'CampaignName',
'CampaignStatus', 'Id', 'KeywordMatchType', 'Criteria').From(
'CAMPAIGN_NEGATIVE_KEYWORDS_PERFORMANCE_REPORT').Build())
actual = ad_util.awql_to_dataframe(query=report_query)
print(actual)
expected = (125493, 7)
self.assertEqual(expected, actual.shape)
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder().Select(
'CampaignId', 'AdGroupId', 'Id', 'Criteria', 'CriteriaType',
'FinalUrls', 'Impressions', 'Clicks',
'Cost').From('CRITERIA_PERFORMANCE_REPORT').Where('Status').In(
'ENABLED', 'PAUSED').During('LAST_7_DAYS').Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
sys.stdout,
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False)
def main(client, date, full_date):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select(
'AverageCost', 'AverageCpc', 'AverageCpe', 'AverageCpm',
'AverageCpv', 'BudgetName', 'BudgetReferenceCount', 'BudgetStatus',
'Clicks', 'Conversions', 'ConversionRate', 'Cost', 'Ctr',
'DeliveryMethod', 'EngagementRate', 'Engagements',
'HasRecommendedBudget', 'Impressions', 'InteractionRate',
'Interactions', 'IsBudgetExplicitlyShared', 'Period',
'RecommendedBudgetAmount',
'RecommendedBudgetEstimatedChangeInWeeklyClicks',
'RecommendedBudgetEstimatedChangeInWeeklyCost',
'RecommendedBudgetEstimatedChangeInWeeklyInteractions',
'RecommendedBudgetEstimatedChangeInWeeklyViews',
'TotalAmount', 'VideoViews', 'VideoViewRate').From(
'BUDGET_PERFORMANCE_REPORT'
) #these are the fields we want in our report
#.Where('Status').In('ENABLED') #here we can filter attributes we have selcted above
.During(
date
) #a custom range can also be specified ('DURING 20150201,20150301')
.Build())
x = (report_downloader.DownloadReportAsStringWithAwql(
report_query,
'CSV',
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=True))
#print(x) # we are printing the report and storing it as a CSV file
f = open("budget_report_" + full_date + ".csv", "w")
f.write(x)
f.close()
#l = full_date.split("-")
filename = "GoogleAds/landing/budget_report/" + "budget_report_" + full_date + ".csv"
importtos3.write_to_s3(x, 'paragon-datalake', filename)
def main(client, date, full_date):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select(
'CampaignName', 'CampaignStatus', 'Engagements', 'EngagementRate',
'AdGroupName', 'AdGroupStatus', 'CriteriaType',
'AllConversionRate', 'AllConversionValue', 'AverageCost',
'AveragePosition', 'Criteria', 'FinalUrls', 'Clicks',
'Impressions', 'Conversions', 'ConversionValue', 'ConversionRate',
'Cost', 'Interactions', 'Date', 'InteractionRate', 'VideoViews',
'VideoViewRate', 'VideoQuartile50Rate', 'VideoQuartile100Rate',
'VideoQuartile25Rate', 'VideoQuartile75Rate', 'SearchPredictedCtr',
'Status', 'CreativeQualityScore', 'ActiveViewCpm', 'ActiveViewCtr',
'ActiveViewImpressions', 'ActiveViewMeasurability',
'CostPerConversion', 'DayOfWeek', 'GmailForwards', 'GmailSaves',
'Device', 'Ctr', 'AverageCpe', 'AverageCpv', 'AverageCpc',
'AverageCpm', 'AllConversions'
) # these are the fields we are using to generate our report
.From('CRITERIA_PERFORMANCE_REPORT') #name of the report
#.Where('Status').In('ENABLED') #we can filter the attribiutes we are pulling using where clause
.During(
date) # custom date can be specified ('DURING 20150201,20150301')
.Build())
x = (report_downloader.DownloadReportAsStringWithAwql(
report_query,
'CSV',
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=True))
#print(x) #we are printing the report and storing it in a CSV file
f = open("criteria_report" + full_date + ".csv", "w")
f.write(x)
f.close()
#l = full_date.split("-")
filename = "GoogleAds/landing/criteria_report/" + "criteria_report_" + full_date + ".csv"
importtos3.write_to_s3(x, 'paragon-datalake', filename)
def download_campaign_perfomance_report(client, during):
# busca todos os ids das contas
accounts_ids = accounts.get_ids(client)
# cria query do report
report_query = (adwords.ReportQueryBuilder().Select(
'AccountDescriptiveName', 'Date', 'CampaignName', 'Device',
'AdNetworkType1', 'Impressions', 'Clicks', 'Cost',
'SearchImpressionShare', 'ContentImpressionShare',
'SearchBudgetLostImpressionShare', 'ContentBudgetLostImpressionShare',
'Ctr').From('CAMPAIGN_PERFORMANCE_REPORT').During(during).Build())
# realiza o download do report para cada conta
for id in accounts_ids:
client.SetClientCustomerId(id)
reports.download_report(
client, report_query,
'CampaignPerformance\\campaign_performance_report_' + str(id) +
'.csv')
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (
adwords.ReportQueryBuilder().Select(
'CampaignId', 'CampaignName', 'Impressions', 'Clicks',
'Cost').From('CAMPAIGN_PERFORMANCE_REPORT').Where('CampaignName').
Contains('tROAS').Where('CampaignStatus').EqualTo('ENABLED').Where(
'Impressions').GreaterThan('0').During('LAST_7_DAYS').Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
sys.stdout,
skip_report_header=False,
skip_column_header=False,
skip_report_summary=False,
include_zero_impressions=True)
def execute(self, context):
logging.info("Executing {}".format(self.__class__.__name__))
aws = AwsHook(aws_conn_id=self.aws_conn_id)
s3 = aws.get_client_type('s3')
s3_location = '{0}/{1}'.format(self.destination_prefix,
self.s3_filename)
local_file = '/tmp/temp_adwords_data.csv'
s3_location = '{0}/{1}'.format(self.destination_prefix,
self.s3_filename)
# initiliaze adwords client
client = adwords.AdWordsClient.LoadFromStorage(self.yaml_file_location)
report_downloader = client.GetReportDownloader(version='v201806')
# build awql report
report_query = adwords.ReportQueryBuilder().\
Select(*self.fields).\
From(self.report_name).\
During(start_date=self.query_start_date,
end_date=self.query_end_date)
for condition in self.conditions:
report_query = report_query.Where(condition['name'])\
.In(*condition['values'])
report_query = report_query.Build()
# Download report locally (temp)
filepath = self.temp_localfile
with open(filepath, 'wb') as handler:
report_downloader.DownloadReportWithAwql(
report_query,
'CSV',
output=handler,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=False)
#Upload to S3
s3.upload_file(filepath, self.destination_bucket, s3_location)
def main(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder()
.Select('CampaignName', 'AdGroupId', 'Id', 'Criteria',
'CriteriaType', 'FinalUrls', 'Impressions', 'Clicks',
'Cost')
.From('CRITERIA_PERFORMANCE_REPORT')
.Where('Status').In('ENABLED', 'PAUSED')
.During('LAST_7_DAYS')
.Build())
# You can provide a file object to write the output to. For this
# demonstration we use sys.stdout to write the report to the screen.
# writer = pd.ExcelWriter('Datanew.xlsx')
with open('auction_data_from_google_report.csv', 'w', encoding='utf-8') as fp:
report_downloader.DownloadReportWithAwql(
report_query, 'CSV', fp, skip_report_header=False,
skip_column_header=False, skip_report_summary=False,
include_zero_impressions=True)
def get_adgroups_report(client):
# Initialize appropriate service.
report_downloader = client.GetReportDownloader(version='v201809')
# Create report query.
report_query = (adwords.ReportQueryBuilder().Select(
'AdGroupId', 'CampaignId', 'AdGroupName', 'AdGroupStatus',
'Impressions', 'Clicks',
'Cost').From('ADGROUP_PERFORMANCE_REPORT').Where('AdGroupStatus').In(
'ENABLED', 'PAUSED').During('LAST_7_DAYS').Build())
# You can provide a file object to write the output to. For this
#
file_name = 'data.csv'
file = open(file_name, 'w+')
report_downloader.DownloadReportWithAwql(report_query,
'CSV',
file,
skip_report_header=True,
skip_column_header=False,
skip_report_summary=True,
include_zero_impressions=True)
return file_name
