def url_fetch(config, task) -> Iterator[dict]:
"""Fetch URL list and return both status code and/or contents.
Takes no parameters, it operates on recipe JSON directly. Core
function is to call urlopen on each passed in URL.
Returns:
Produces a dictionary generator with record matching URL_SCHEMA.
"""
for url, uri in get_rows(config, task['auth'], task['urls']):
if config.verbose:
print('URL/URI', url, uri)
record = {'URL': url, 'URI': None if uri is None else str(uri)}
url_request = request.Request(url, data=task.get('data'))
try:
url_response = request.urlopen(url_request)
if task.get('status', False):
record['Status'] = url_response.status
if task.get('read', False):
record['Read'] = url_response.read()
except InvalidURL as error:
if task.get('status', False):
record['Status'] = 400
except HTTPError as error:
if task.get('status', False):
record['Status'] = error.status
except Exception as error:
if task.get('status', False):
record['Status'] = 500
yield record
def report_filter(auth, body, filters):
""" Adds filters to a report body
Filters cannot be easily added to the reports without templateing, this allows
filters to be passed as lists.
Values are specified using get_rows(...) helper, see
starthinker/util/data/__init__.py.
To specify a filter, use the official filter name and a list of values.
For exmaple:
```
filters = {
"FILTER_PARTNER": {
"values":789
},
"FILTER_ADVERTISER": {
"values":[1234, 5678, 91011]
}
}
```
Args:
* auth: (string) Either user or service.
* body: (json) the report body ( with or without filters )
* filters: (json) a dictionary of filters to apply ( see above examples )
Returns:
* body: ( json ) modified report body
"""
new_body = body.copy()
for f, d in filters.items():
for v in get_rows(project.task['auth'], d):
new_body['params'].setdefault('filters', []).append({
'type': f,
'value': v
})
return new_body
def load_multiple():
for row in get_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'CM Profiles',
'header':False,
'range': 'A2:A'
}}
):
if row:
account_id = lookup_id(row[0])
is_superuser, profile_id = get_profile_for_api(config, task['auth_cm'], account_id)
kwargs = { 'profileId': profile_id, 'accountId': account_id } if is_superuser else { 'profileId': profile_id }
yield from API_DCM(
config,
task['auth_cm'],
iterate=True,
internal=is_superuser
).accounts().list(**kwargs).execute()
def campaign_load_multiple():
for row in get_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'Advertisers',
'header':False,
'range': 'A2:A'
}}
):
if row:
yield from API_DV360(
config,
task['auth_dv'],
iterate=True
).advertisers().campaigns().list(
advertiserId=lookup_id(row[0]),
filter='entityStatus="ENTITY_STATUS_PAUSED" OR entityStatus="ENTITY_STATUS_ACTIVE" OR entityStatus="ENTITY_STATUS_DRAFT"',
fields='campaigns.displayName,campaigns.campaignId,campaigns.advertiserId,nextPageToken'
).execute()
def google_api():
if project.verbose:
print('GOOGLE_API', project.task['api'], project.task['version'],
project.task['function'])
api_call = {
'auth': project.task['auth'],
'api': project.task['api'],
'version': project.task['version'],
'function': project.task['function'],
'iterate': project.task.get('iterate', False),
'key': project.key,
'headers': project.task.get('headers'),
}
results = google_api_build_results(project.task['auth'], api_call,
project.task.get('results', {}))
errors = google_api_build_errors(project.task['auth'], api_call,
project.task.get('errors', {}))
if 'kwargs' in project.task:
kwargs_list = project.task['kwargs'] if isinstance(
project.task['kwargs'],
(list, tuple)) else [project.task['kwargs']]
elif 'kwargs_remote' in project.task:
kwargs_list = get_rows(project.task['auth'],
project.task['kwargs_remote'],
as_object=True)
else:
kwargs_list = [{}]
for kwargs in kwargs_list:
api_call['kwargs'] = kwargs
google_api_initilaize(api_call, project.task.get('alias'))
google_api_execute(project.task['auth'], api_call, results, errors)
def partner_load():
# write partners to BQ
rows = API_DV360(project.task['auth_dv'], iterate=True).partners().list().execute()
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Partners',
'schema':
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('partners.list'),
'format':
'JSON'
}
}, rows)
# write partners to sheet
rows = get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"SELECT CONCAT(displayName, ' - ', partnerId), entityStatus FROM `%s.DV_Partners`"
% project.task['dataset'],
'legacy':
False
}
})
put_rows(project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'range': 'B2'
}
}, rows)
def vision_annotate(config, task):
body = {
"requests":[],
"parent": 'projects/' + config.project
}
uri_buffer = []
for request_index, request in enumerate(get_rows(config, task['auth'], task['requests'], as_object=True)):
# submit 16 requests at a time
if request_index > 0 and request_index % 16 == 0:
for response_index, response in enumerate(API_Vision(config, task['auth'], iterate=True).images().annotate(body=body).execute()):
response['imageUri'] = uri_buffer[response_index]
yield response
uri_buffer = []
body['requests'] = []
# buffer the URI, and add requests to the batch request
else:
uri = request['image'].get('source', {}).get('imageUri', 'image %s' % request_index)
uri_buffer.append(uri)
if config.verbose:
print('URI', uri)
if 'content' in request['image'] and 'source' in request['image']:
del request['image']['source']
body['requests'].append(request)
# clean up last requests
if body['requests']:
for response_index, response in enumerate(API_Vision(config, task['auth'], iterate=True).images().annotate(body=body).execute()):
response['imageUri'] = uri_buffer[response_index]
yield response
def conversion_upload():
"""Entry point for conversion_upload task, which uploads conversins to CM360.
Prints sucess or failure to STDOUT.
Currently only does batchInsert, not batchUpdate.
"""
rows = get_rows(
project.task['auth'],
project.task['from'],
as_object=False
)
if project.verbose:
print('CONVERSION UPLOAD')
statuses = conversions_upload(
project.task['auth'],
project.task['account_id'],
project.task['activity_id'],
project.task['conversion_type'], rows,
project.task['encryptionInfo']
)
has_rows = False
for status in statuses:
has_rows = True
if 'errors' in status:
if project.verbose:
print( 'ERROR:', status['conversion']['ordinal'], '\n'.join([e['message'] for e in status['errors']]))
else:
if project.verbose:
print('OK:', status['conversion']['ordinal'])
if not has_rows:
if project.verbose:
print('NO ROWS')
def sdf_download(config, auth, version, partner_id, file_types, filter_type,
filter_ids_obj):
#Read Filter Ids
filter_ids = list(get_rows(config, auth, filter_ids_obj))
body = {
'version': version,
'partnerId': partner_id,
'parentEntityFilter': {
'fileType': file_types,
'filterType': filter_type,
'filterIds': filter_ids
},
'idFilter': None
}
operation = API_DV360(config,
auth).sdfdownloadtasks().create(body=body).execute()
if operation and 'name' in operation:
request = API_DV360(
config,
auth).sdfdownloadtasks().operations().get(name=operation['name'])
# This is the eng recommended way of getting the operation
while True:
response = request.execute()
if 'done' in response and response['done']:
break
time.sleep(30)
else:
print('error')
if 'error' in response:
raise Exception(response['error']['message'])
return download_media(config, 'user', response['response']['resourceName'])
def pacing_load():
# write pacings to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT * FROM (
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
NULL,
I.pacing.pacingPeriod,
I.pacing.pacingPeriod,
I.pacing.pacingType,
I.pacing.pacingType,
CAST(I.pacing.dailyMaxMicros AS INT64) / 100000,
CAST(I.pacing.dailyMaxMicros AS INT64) / 100000,
I.pacing.dailyMaxImpressions,
I.pacing.dailyMaxImpressions
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
CONCAT(L.displayName, ' - ', L.lineItemId),
L.pacing.pacingPeriod,
L.pacing.pacingPeriod,
L.pacing.pacingType,
L.pacing.pacingType,
CAST(L.pacing.dailyMaxMicros AS INT64) / 100000,
CAST(L.pacing.dailyMaxMicros AS INT64) / 100000,
L.pacing.dailyMaxImpressions,
L.pacing.dailyMaxImpressions
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId )
ORDER BY IO_Display
""".format(**project.task),
"legacy":
False
}
})
put_rows(project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Pacing",
"range": "A2"
}
}, rows)
def pacing_patch(commit=False):
patches = []
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table":"PATCH_Pacing",
}},
as_object=True
)
for row in rows:
pacing = {}
if row['Period'] != row['Period_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["pacingPeriod"] = row['Period_Edit']
if row['Type'] != row['Type_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["pacingType"] = row['Type_Edit']
if row['Daily_Budget'] != row['Daily_Budget_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["dailyMaxMicros"] = int(float(row['Daily_Budget_Edit']) * 100000)
if row['Daily_Impressions'] != row['Daily_Impressions_Edit']:
pacing.setdefault("pacing", {})
pacing["pacing"]["dailyMaxImpressions"] = row['Daily_Impressions_Edit']
if pacing:
patch = {
"operation": "Pacing",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": pacing
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
else:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(row['Insertion_Order'])
patches.append(patch)
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
else:
patch_preview(patches)
def pacing_audit():
rows = get_rows(project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Pacing",
"range": "A2:Z"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "SHEET_Pacing",
"schema": [
{ "name": "Partner", "type": "STRING" },
{ "name": "Advertiser", "type": "STRING" },
{ "name": "Campaign", "type": "STRING" },
{ "name": "Insertion_Order", "type": "STRING" },
{ "name": "Line_Item", "type": "STRING" },
{ "name": "Period", "type": "STRING" },
{ "name": "Period_Edit", "type": "STRING" },
{ "name": "Type", "type": "STRING" },
{ "name": "Type_Edit", "type": "STRING" },
{ "name": "Daily_Budget", "type": "FLOAT" },
{ "name": "Daily_Budget_Edit", "type": "FLOAT" },
{ "name": "Daily_Impressions", "type": "INTEGER" },
{ "name": "Daily_Impressions_Edit", "type": "INTEGER" },
],
"format": "CSV"
}
}, rows)
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_Pacing",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Pacing' AS Operation,
CASE
WHEN Period_Edit IS NULL THEN 'Missing Period.'
WHEN Type_Edit IS NULL THEN 'Missing Type.'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_Pacing`
)
WHERE
Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
;
""".format(**project.task),
legacy=False)
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_Pacing",
"""SELECT *
FROM `{dataset}.SHEET_Pacing`
WHERE (
REGEXP_CONTAINS(Insertion_Order, r" - (\d+)$")
OR REGEXP_CONTAINS(Line_Item, r" - (\d+)$")
)
AND Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_Pacing` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_Pacing` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False
)
def advertiser_load():
# load multiple partners from user defined sheet
def load_multiple():
for row in get_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Partners',
'header': False,
'range': 'A2:A'
}
}):
if row:
yield from API_DV360(
project.task['auth_dv'], iterate=True).advertisers().list(
partnerId=lookup_id(row[0])).execute()
advertiser_clear()
# write advertisers to database
put_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'table':
'DV_Advertisers',
'schema':
Discovery_To_BigQuery('displayvideo',
'v1').method_schema('advertisers.list'),
'format':
'JSON'
}
}, load_multiple())
# write advertisers to sheet
put_rows(
project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Advertisers',
'range': 'B2'
}
},
get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
A.entityStatus
FROM `{dataset}.DV_Advertisers` AS A
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
'legacy':
False
}
}))
def cm_account_load(config, task):
# load multiple partners from user defined sheet
def load_multiple():
for row in get_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'CM Profiles',
'header':False,
'range': 'A2:A'
}}
):
if row:
account_id = lookup_id(row[0])
is_superuser, profile_id = get_profile_for_api(config, task['auth_cm'], account_id)
kwargs = { 'profileId': profile_id, 'accountId': account_id } if is_superuser else { 'profileId': profile_id }
yield from API_DCM(
config,
task['auth_cm'],
iterate=True,
internal=is_superuser
).accounts().list(**kwargs).execute()
cm_account_clear(config, task)
# write accounts to database
put_rows(
config,
task['auth_bigquery'],
{ 'bigquery': {
'dataset': task['dataset'],
'table': 'CM_Accounts',
'schema': Discovery_To_BigQuery(
'dfareporting',
'v3.4'
).method_schema(
'accounts.list',
iterate=True
),
'format':'JSON'
}},
load_multiple()
)
# write accounts to sheet
put_rows(
config,
task['auth_sheets'],
{ 'sheets': {
'sheet': task['sheet'],
'tab': 'CM Accounts',
'header':False,
'range': 'B2'
}},
get_rows(
config,
task['auth_bigquery'],
{ 'bigquery': {
'dataset': task['dataset'],
'query': "SELECT CONCAT(name, ' - ', id), active FROM `%s.CM_Accounts`" % task['dataset'],
'legacy': False
}}
)
)
def frequency_cap_load():
# write frequency_caps to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CAST(NULL AS STRING),
CAST(NULL AS STRING),
IFNULL(C.frequencyCap.unlimited, FALSE),
IFNULL(C.frequencyCap.unlimited, FALSE),
C.frequencyCap.timeUnit,
C.frequencyCap.timeUnit,
C.frequencyCap.timeUnitCount,
C.frequencyCap.timeUnitCount,
C.frequencyCap.maxImpressions,
C.frequencyCap.maxImpressions
FROM `{dataset}.DV_Campaigns` AS C
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON C.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CAST(NULL AS STRING),
IFNULL(I.frequencyCap.unlimited, FALSE),
IFNULL(I.frequencyCap.unlimited, FALSE),
I.frequencyCap.timeUnit,
I.frequencyCap.timeUnit,
I.frequencyCap.timeUnitCount,
I.frequencyCap.timeUnitCount,
I.frequencyCap.maxImpressions,
I.frequencyCap.maxImpressions
FROM `{dataset}.DV_InsertionOrders` AS I
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
IFNULL(L.frequencyCap.unlimited, FALSE),
IFNULL(L.frequencyCap.unlimited, FALSE),
L.frequencyCap.timeUnit,
L.frequencyCap.timeUnit,
L.frequencyCap.timeUnitCount,
L.frequencyCap.timeUnitCount,
L.frequencyCap.maxImpressions,
L.frequencyCap.maxImpressions
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
""".format(**project.task),
"legacy":
False
}
})
put_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Frequency Caps",
"range": "A2"
}
}, rows)
def partner_cost_load():
# write partner_costs to sheet
rows = get_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"query":
"""SELECT * FROM (
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
NULL,
PC.costType,
PC.costType,
PC.feeType,
PC.feeType,
PC.invoiceType,
PC.invoiceType,
PC.feeAmount / 100000,
PC.feeAmount / 100000,
PC.feePercentageMillis / 1000,
PC.feePercentageMillis / 1000
FROM `{dataset}.DV_InsertionOrders` AS I, UNNEST(partnerCosts) AS PC
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON I.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON I.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
UNION ALL
SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId) AS IO_Display,
CONCAT(L.displayName, ' - ', L.lineItemId),
PC.costType,
PC.costType,
PC.feeType,
PC.feeType,
PC.invoiceType,
PC.invoiceType,
PC.feeAmount / 100000,
PC.feeAmount / 100000,
PC.feePercentageMillis / 1000,
PC.feePercentageMillis / 1000
FROM `{dataset}.DV_LineItems` AS L, UNNEST(partnerCosts) AS PC
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId )
ORDER BY IO_Display
""".format(**project.task),
"legacy":
False
}
})
put_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Partner Costs",
"range": "A2"
}
}, rows)
def partner_cost_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Partner Costs",
"range": "A2:Z"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_PartnerCosts",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Cost_Type",
"type": "STRING"
},
{
"name": "Cost_Type_Edit",
"type": "STRING"
},
{
"name": "Fee_Type",
"type": "STRING"
},
{
"name": "Fee_Type_Edit",
"type": "STRING"
},
{
"name": "Invoice_Type",
"type": "STRING"
},
{
"name": "Invoice_Type_Edit",
"type": "STRING"
},
{
"name": "Fee_Amount",
"type": "FLOAT"
},
{
"name": "Fee_Amount_Edit",
"type": "FLOAT"
},
{
"name": "Fee_Percent",
"type": "FLOAT"
},
{
"name": "Fee_Percent_Edit",
"type": "FLOAT"
},
],
"format":
"CSV"
}
}, rows)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_PartnerCosts",
"""WITH
/* Check if sheet values are set */ INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Partner Costs' AS Operation,
CASE
WHEN Cost_Type_Edit IS NULL THEN 'Missing Cost Type.'
WHEN Fee_Type_Edit IS NULL THEN 'Missing Fee Type.'
WHEN Invoice_Type_Edit IS NULL THEN 'Missing Invoice Type.'
WHEN Fee_Amount_Edit IS NULL
AND Fee_Percent_Edit IS NULL THEN 'You must select a Fee Amount OR Fee Percent'
ELSE IF
(Fee_Amount_Edit IS NOT NULL
AND Fee_Percent_Edit IS NOT NULL,
'You must select a Fee Amount OR Fee Percent, not both',
NULL)
END
AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item,
Insertion_Order,
'BLANK') AS Id
FROM
`{dataset}.SHEET_PartnerCosts` )
WHERE
Error IS NOT NULL )
SELECT
*
FROM
INPUT_ERRORS ;
""".format(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_PartnerCosts",
"""SELECT *
FROM `{dataset}.SHEET_PartnerCosts`
WHERE (
REGEXP_CONTAINS(Insertion_Order, r" - (\d+)$")
OR REGEXP_CONTAINS(Line_Item, r" - (\d+)$")
)
AND Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_PartnerCosts` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_PartnerCosts` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False)
def bigquery():
if 'function' in project.task:
query = None
if project.task['function'] == 'pearson_significance_test':
query = pearson_significance_test()
if query:
run_query(project.task['auth'], project.id, query, False,
project.task['to']['dataset'])
elif 'run' in project.task and 'query' in project.task.get('run', {}):
if project.verbose:
print('QUERY', project.task['run']['query'])
run_query(
project.task['auth'],
project.id,
query_parameters(project.task['run']['query'],
project.task['run'].get('parameters')),
project.task['run'].get('legacy', True),
)
elif 'values' in project.task['from']:
rows = get_rows(project.task['auth'], project.task['from'])
rows_to_table(project.task['to'].get('auth', project.task['auth']),
project.id, project.task['to']['dataset'],
project.task['to']['table'], rows,
project.task.get('schema', []), 0)
elif 'query' in project.task['from']:
if 'table' in project.task['to']:
if project.verbose:
print('QUERY TO TABLE', project.task['to']['table'])
query_to_table(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['table'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
disposition=project.task['write_disposition']
if 'write_disposition' in project.task else 'WRITE_TRUNCATE',
legacy=project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql,
target_project_id=project.task['to'].get(
'project_id', project.id))
elif 'sheet' in project.task['to']:
if project.verbose:
print('QUERY TO SHEET', project.task['to']['sheet'])
rows = query_to_rows(
project.task['auth'],
project.id,
project.task['from']['dataset'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
legacy=project.task['from'].get('legacy', True))
# makes sure types are correct in sheet
rows = rows_to_type(rows)
sheets_clear(project.task['to'].get('auth', project.task['auth']),
project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'))
sheets_write(project.task['to'].get('auth', project.task['auth']),
project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'), rows)
elif 'sftp' in project.task['to']:
rows = query_to_rows(
project.task['auth'],
project.id,
project.task['from']['dataset'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
legacy=project.task['from'].get('use_legacy_sql', True))
if rows:
if project.verbose:
print('QUERY TO SFTP')
put_rows(project.task['auth'], project.task['to'], rows)
else:
if project.verbose:
print('QUERY TO VIEW', project.task['to']['view'])
query_to_view(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['view'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql
project.task['to'].get('replace', False))
else:
if project.verbose:
print('STORAGE TO TABLE', project.task['to']['table'])
storage_to_table(
project.task['auth'], project.id, project.task['to']['dataset'],
project.task['to']['table'], project.task['from']['bucket'] + ':' +
project.task['from']['path'], project.task.get('schema', []),
project.task.get('skip_rows', 1),
project.task.get('structure', 'CSV'),
project.task.get('disposition', 'WRITE_TRUNCATE'))
def bigquery():
if 'run' in project.task and 'query' in project.task.get('run', {}):
if project.verbose: print("QUERY", project.task['run']['query'])
run_query(
project.task['auth'],
project.id,
project.task['run']['query'],
project.task['run'].get('legacy', True),
#project.task['run'].get('billing_project_id', None)
)
elif 'values' in project.task['from']:
rows = get_rows(project.task['auth'], project.task['from'])
rows_to_table(project.task['to'].get('auth', project.task['auth']),
project.id, project.task['to']['dataset'],
project.task['to']['table'], rows,
project.task.get('schema', []), 0)
elif 'query' in project.task['from']:
if 'table' in project.task['to']:
if project.verbose:
print("QUERY TO TABLE", project.task['to']['table'])
if 'pre_process_query' in project.task['to']:
print('executing statement')
execute_statement(project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['pre_process_query'],
use_legacy_sql=project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)))
query_to_table(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['table'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
disposition=project.task['write_disposition']
if 'write_disposition' in project.task else 'WRITE_TRUNCATE',
legacy=project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql,
target_project_id=project.task['to'].get(
'project_id', project.id))
# NOT USED SO RIPPING IT OUT
# Mauriciod: Yes, it is used, look at project/mauriciod/target_winrate.json
elif 'storage' in project.task['to']:
if project.verbose:
print("QUERY TO STORAGE", project.task['to']['storage'])
local_file_name = '/tmp/%s' % str(uuid.uuid1())
rows = query_to_rows(project.task['auth'], project.id,
project.task['from']['dataset'],
project.task['from']['query'])
f = open(local_file_name, 'wb')
writer = csv.writer(f)
writer.writerows(rows)
f.close()
f = open(local_file_name, 'rb')
object_put(project.task['auth'], project.task['to']['storage'], f)
f.close()
os.remove(local_file_name)
elif 'sheet' in project.task['to']:
if project.verbose:
print("QUERY TO SHEET", project.task['to']['sheet'])
rows = query_to_rows(project.task['auth'],
project.id,
project.task['from']['dataset'],
project.task['from']['query'],
legacy=project.task['from'].get(
'legacy', True))
# makes sure types are correct in sheet
rows = rows_to_type(rows)
sheets_clear(project.task['auth'], project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'))
sheets_write(project.task['auth'], project.task['to']['sheet'],
project.task['to']['tab'],
project.task['to'].get('range', 'A2'), rows)
elif 'sftp' in project.task['to']:
rows = query_to_rows(project.task['auth'],
project.id,
project.task['from']['dataset'],
project.task['from']['query'],
legacy=project.task['from'].get(
'use_legacy_sql', True))
if rows:
if project.verbose: print("QUERY TO SFTP")
put_rows(project.task['auth'], project.task['to'], rows)
else:
if project.verbose:
print("QUERY TO VIEW", project.task['to']['view'])
query_to_view(
project.task['auth'],
project.id,
project.task['to']['dataset'],
project.task['to']['view'],
query_parameters(project.task['from']['query'],
project.task['from'].get('parameters')),
project.task['from'].get(
'legacy', project.task['from'].get(
'useLegacySql', True)), # DEPRECATED: useLegacySql
project.task['to'].get('replace', False))
else:
if project.verbose:
print("STORAGE TO TABLE", project.task['to']['table'])
storage_to_table(
project.task['auth'], project.id, project.task['to']['dataset'],
project.task['to']['table'], project.task['from']['bucket'] + ':' +
project.task['from']['path'], project.task.get('schema', []),
project.task.get('skip_rows', 1),
project.task.get('structure', 'CSV'),
project.task.get('disposition', 'WRITE_TRUNCATE'))
def dbm():
if project.verbose: print('DBM')
# legacy translations ( changed report title to name )
if 'title' in project.task['report']:
project.task['report']['name'] = project.task['report']['title']
# check if report is to be deleted
if project.task.get('delete', False):
if project.verbose: print('DBM DELETE', end='')
report_delete(project.task['auth'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None))
# check if report is to be created ( LEGACY, DO NOT USE, SEE body format below )
# REASON: this call tried to pass all parts of the json as parameters, this does not scale
# the new body call simply passes the report json in, leaving flexibility in the JSON recipe
if 'type' in project.task['report']:
if project.verbose: print('DBM CREATE', end='')
partners = get_rows(
project.task['auth'], project.task['report']
['partners']) if 'partners' in project.task['report'] else []
advertisers = get_rows(
project.task['auth'], project.task['report']['advertisers']
) if 'advertisers' in project.task['report'] else []
report_create(
project.task['auth'], project.task['report']['name'],
project.task['report']['type'], partners, advertisers,
project.task['report'].get('filters'),
project.task['report'].get('dimensions'),
project.task['report'].get('metrics'),
project.task['report'].get('data_range'),
project.task['report'].get('timezone', 'America/Los Angeles'),
project.id, project.task['report'].get('dataset_id', None))
# check if report is to be created
if 'body' in project.task['report']:
if project.verbose:
print('DBM BUILD',
project.task['report']['body']['metadata']['title'])
# filters can be passed using special get_rows handler, allows reading values from sheets etc...
if 'filters' in project.task['report']:
for f, d in project.task['report']['filters'].items():
for v in get_rows(project.task['auth'], d):
project.task['report']['body']['params'].setdefault(
'filters', []).append({
"type": f,
"value": v
})
# create the report
report = report_build(project.task['auth'],
project.task['report']['body'])
# moving a report
if 'out' in project.task:
filename, report = report_file(
project.task['auth'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None),
project.task['report'].get('timeout', 10), DBM_CHUNKSIZE)
# if a report exists
if report:
if project.verbose: print('DBM FILE', filename)
# clean up the report
rows = report_to_rows(report)
rows = report_clean(rows,
datastudio=project.task.get(
'datastudio', False),
nulls=True)
# write rows using standard out block in json ( allows customization across all scripts )
if rows: put_rows(project.task['auth'], project.task['out'], rows)
def barnacle():
if project.verbose: print('BARNACLE')
accounts = set(get_rows("user", project.task['accounts']))
# Accounts
rows = get_accounts(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_Accounts', ACCOUNTS_SCHEMA), rows)
# Profiles
rows = get_profiles(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_Profiles', PROFILES_SCHEMA), rows)
# Profiles Campaigns
if project.verbose: print('DCM Profile Campaigns')
put_rows(project.task['out']['auth'],
put_json('CM_Profile_Campaigns', PROFILE_CAMPAIGNS_SCHEMA),
PROFILE_CAMPAIGNS)
# Profiles Sites
if project.verbose: print('DCM Profile Sites')
put_rows(project.task['out']['auth'],
put_json('CM_Profile_Sites', PROFILE_SITES_SCHEMA), PROFILE_SITES)
# Profiles Roles
if project.verbose: print('DCM Profile Roles')
put_rows(project.task['out']['auth'],
put_json('CM_Profile_Roles', PROFILE_ROLES_SCHEMA), PROFILE_ROLES)
# Profiles Advertisers
if project.verbose: print('DCM Profile Advertisers')
put_rows(project.task['out']['auth'],
put_json('CM_Profile_Advertisers', PROFILE_ADVERTISERS_SCHEMA),
PROFILE_ADVERTISERS)
# Subaccounts
rows = get_subaccounts(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_SubAccounts', SUBACCOUNTS_SCHEMA), rows)
# Advertisers
rows = get_advertisers(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_Advertisers', ADVERTISERS_SCHEMA), rows)
# Campaigns
rows = get_campaigns(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_Campaigns', CAMPAIGNS_SCHEMA), rows)
# Sites
rows = get_sites(accounts)
put_rows(project.task['out']['auth'], put_json('CM_Sites', SITES_SCHEMA),
rows)
# Sites Contacts
if project.verbose: print('DCM Site Contacts')
put_rows(project.task['out']['auth'],
put_json('CM_Site_Contacts', SITE_CONTACTS_SCHEMA), SITE_CONTACTS)
# Roles
rows = get_roles(accounts)
put_rows(project.task['out']['auth'], put_json('CM_Roles', ROLES_SCHEMA),
rows)
if project.task.get('reports', False) == True:
# Reports
rows = get_reports(accounts)
put_rows(project.task['out']['auth'],
put_json('CM_Reports', REPORTS_SCHEMA), rows)
# Reports Deliveries
if project.verbose: print('DCM Deliveries')
put_rows(project.task['out']['auth'],
put_json('CM_Report_Deliveries', REPORT_DELIVERIES_SCHEMA),
REPORT_DELIVERIES)
def frequency_cap_audit():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Frequency Caps",
"range": "A2:M"
}
})
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"SHEET_FrequencyCaps",
"schema": [
{
"name": "Partner",
"type": "STRING"
},
{
"name": "Advertiser",
"type": "STRING"
},
{
"name": "Campaign",
"type": "STRING"
},
{
"name": "Insertion_Order",
"type": "STRING"
},
{
"name": "Line_Item",
"type": "STRING"
},
{
"name": "Unlimited",
"type": "BOOLEAN"
},
{
"name": "Unlimited_Edit",
"type": "BOOLEAN"
},
{
"name": "Time_Unit",
"type": "STRING"
},
{
"name": "Time_Unit_Edit",
"type": "STRING"
},
{
"name": "Time_Count",
"type": "INTEGER"
},
{
"name": "Time_Count_Edit",
"type": "INTEGER"
},
{
"name": "Max_impressions",
"type": "INTEGER"
},
{
"name": "Max_impressions_Edit",
"type": "INTEGER"
},
],
"format":
"CSV"
}
}, rows)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_FrequencyCaps",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Frequency Caps' AS Operation,
CASE WHEN Unlimited_Edit IS TRUE THEN
CASE WHEN Time_Unit_Edit IS NOT NULL OR Time_Count_Edit IS NOT NULL OR Max_Impressions_Edit IS NOT NULL THEN 'Time Unit and the Other Options are Mutually Exclusive.'
ELSE NULL
END
ELSE
IF(Time_Unit_Edit IS NULL OR Time_Count_Edit IS NULL OR Max_Impressions_Edit IS NULL, 'If Time Unit is FALSE, the other options are required.', NULL)
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, Insertion_Order, 'BLANK') AS Id
FROM
`{dataset}.SHEET_FrequencyCaps`
)
WHERE
Error IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
;
""".format(**project.task),
legacy=False)
query_to_view(project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_FrequencyCaps",
"""SELECT *
FROM `{dataset}.SHEET_FrequencyCaps`
WHERE Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
AND Insertion_Order NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
AND Campaign NOT IN (SELECT Id FROM `{dataset}.AUDIT_FrequencyCaps` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False)
def frequency_cap_patch(commit=False):
patches = []
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_FrequencyCaps",
}
},
as_object=True)
for row in rows:
frequency_cap = {}
if row['Unlimited'] != row['Unlimited_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["unlimited"] = row['Unlimited_Edit']
if row['Time_Unit'] != row['Time_Unit_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["timeUnit"] = row['Time_Unit_Edit']
if row['Time_Count'] != row['Time_Count_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["timeUnitCount"] = row[
'Time_Count_Edit']
if row['Max_impressions'] != row['Max_impressions_Edit']:
frequency_cap.setdefault("frequencyCap", {})
frequency_cap["frequencyCap"]["maxImpressions"] = row[
'Max_impressions_Edit']
if frequency_cap:
patch = {
"operation": "Frequency Caps",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": frequency_cap
}
}
if row['Line_Item']:
patch["line_item"] = row['Line_Item']
patch["parameters"]["lineItemId"] = lookup_id(row['Line_Item'])
elif row['Insertion_Order']:
patch["insertion_order"] = row['Insertion_Order']
patch["parameters"]["insertionOrderId"] = lookup_id(
row['Insertion_Order'])
else:
patch["campaign"] = row['Campaign']
patch["parameters"]["campaignId"] = lookup_id(row['Campaign'])
patches.append(patch)
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
campaign_commit(patches)
else:
patch_preview(patches)
def audit_load():
bid_strategy_audit()
integration_detail_audit()
frequency_cap_audit()
line_item_map_audit()
pacing_audit()
partner_cost_audit()
segment_audit()
insertion_order_audit()
line_item_audit()
# write audit to sheet
sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Audit',
'A2')
# write audits to sheet
rows = get_rows(
project.task['auth_bigquery'], {
'bigquery': {
'dataset':
project.task['dataset'],
'query':
"""SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_InsertionOrders`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_Segments`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_LineItems`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_LineItemMaps`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_Pacing`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_BidStrategy`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_FrequencyCaps`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_PartnerCosts`
UNION ALL
SELECT Operation, Severity, Id, Error
FROM `{dataset}.AUDIT_IntegrationDetails`
""".format(**project.task),
'legacy':
False
}
})
put_rows(project.task['auth_sheets'], {
'sheets': {
'sheet': project.task['sheet'],
'tab': 'Audit',
'range': 'A2'
}
}, rows)
def line_item_audit():
rows = get_rows(
project.task["auth_sheets"],
{ "sheets": {
"sheet": project.task["sheet"],
"tab": "Line Items",
"range": "A2:Z"
}}
)
put_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table": "SHEET_LineItems",
"schema": [
{ "name": "Partner", "type": "STRING" },
{ "name": "Advertiser", "type": "STRING" },
{ "name": "Campaign", "type": "STRING" },
{ "name": "Insertion_Order", "type": "STRING" },
{ "name": "Line_Item", "type": "STRING" },
{ "name": "Action", "type": "STRING" },
{ "name": "Status", "type": "STRING" },
{ "name": "Status_Edit", "type": "STRING" },
{ "name": "Warning", "type": "STRING" },
{ "name": "Line_Item_Type", "type": "STRING" },
{ "name": "Line_Item_Type_Edit", "type": "STRING" },
{ "name": "Flight_Data_Type", "type": "STRING" },
{ "name": "Flight_Data_Type_Edit", "type": "STRING" },
{ "name": "Flight_Start_Date", "type": "STRING" },
{ "name": "Flight_Start_Date_Edit", "type": "STRING" },
{ "name": "Flight_End_Date", "type": "STRING" },
{ "name": "Flight_End_Date_Edit", "type": "STRING" },
{ "name": "Flight_Trigger", "type": "STRING" },
{ "name": "Flight_Trigger_Edit", "type": "STRING" },
{ "name": "Budget_Allocation_Type", "type": "STRING" },
{ "name": "Budget_Allocation_Type_Edit", "type": "STRING" },
{ "name": "Budget_Unit", "type": "STRING" },
{ "name": "Budget_Unit_Edit", "type": "STRING" },
{ "name": "Budget_Max", "type": "FLOAT" },
{ "name": "Budget_Max_Edit", "type": "FLOAT" },
{ "name": "Partner_Revenue_Model", "type": "STRING" },
{ "name": "Partner_Revenue_Model_Edit", "type": "STRING" },
{ "name": "Partner_Markup", "type": "FLOAT" },
{ "name": "Partner_Markup_Edit", "type": "FLOAT" },
{ "name": "Conversion_Percent", "type": "FLOAT" },
{ "name": "Conversion_Percent_Edit", "type": "FLOAT" },
{ "name": "Targeting_Expansion_Level", "type": "STRING" },
{ "name": "Targeting_Expansion_Level_Edit", "type": "STRING" },
{ "name": "Exclude_1P", "type": "STRING" },
{ "name": "Exclude_1P_Edit", "type": "STRING" },
],
"format": "CSV"
}},
rows
)
# Create Insert View
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"INSERT_LineItems",
"""SELECT
REGEXP_EXTRACT(S_LI.Advertiser, r' - (\d+)$') AS advertiserId,
REGEXP_EXTRACT(S_LI.Campaign, r' - (\d+)$') AS campaignId,
REGEXP_EXTRACT(S_LI.Insertion_Order, r' - (\d+)$') AS insertionOrderId,
S_LI.Line_Item AS displayName,
S_LI.Line_Item_Type_Edit AS lineItemType,
S_LI.Status_Edit AS entityStatus,
STRUCT(
S_PC.Cost_Type_Edit As costType,
S_PC.Fee_Type_Edit As feeType,
S_PC.Invoice_Type_Edit AS invoiceType,
S_PC.Fee_Amount_Edit AS feeAmount,
S_PC.Fee_Percent_Edit * 1000 AS feePercentageMillis
) AS partnerCosts,
STRUCT(
S_LI.Flight_Data_Type_Edit AS flightDateType,
STRUCT (
STRUCT (
EXTRACT(YEAR FROM CAST(S_LI.Flight_Start_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_LI.Flight_Start_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_LI.Flight_Start_Date_Edit AS DATE)) AS day
) AS startDate,
STRUCT (
EXTRACT(YEAR FROM CAST(S_LI.Flight_End_Date_Edit AS Date)) AS year,
EXTRACT(MONTH FROM CAST(S_LI.Flight_End_Date_Edit AS DATE)) AS month,
EXTRACT(DAY FROM CAST(S_LI.Flight_End_Date_Edit AS DATE)) AS day
) AS endDate
) AS dateRange,
S_LI.Flight_Trigger_Edit AS triggerId
) AS flight,
STRUCT(
S_LI.Budget_Allocation_Type_Edit AS budgetAllocationType,
S_LI.Budget_Unit_Edit AS budgetUnit,
S_LI.Budget_Max_Edit * 100000 AS maxAmount
) AS budget,
STRUCT(
S_P.Period_Edit As pacingPeriod,
S_P.Type_Edit As pacingType,
S_P.Daily_Budget_Edit AS dailyMaxMicros,
S_P.Daily_Impressions_Edit AS dailyMaxImpressions
) AS pacing,
STRUCT(
S_FC.Unlimited_Edit AS unlimited,
S_FC.Time_Unit_Edit AS timeUnit,
S_FC.Time_Count_Edit AS timeUnitCount,
S_FC.Max_impressions_Edit AS maxImpressions
) AS frequencyCap,
STRUCT(
S_LI.Partner_Revenue_Model_Edit AS markupType,
S_LI.Partner_Markup_Edit * 100000 AS markupAmount
) AS partnerRevenueModel,
STRUCT(
S_LI. Conversion_Percent_Edit * 1000 AS postViewCountPercentageMillis,
[] AS floodlightActivityConfigs
) AS conversionCounting,
STRUCT(
IF(S_BS.Fixed_Bid_Edit IS NOT NULL,
STRUCT(
S_BS.Fixed_Bid_Edit * 100000 AS bidAmountMicros
),
NULL
) AS fixedBid,
IF(S_BS.Auto_Bid_Goal_Edit IS NOT NULL,
STRUCT(
S_BS.Auto_Bid_Goal_Edit AS performanceGoalType,
S_BS.Auto_Bid_Amount_Edit * 100000 AS maxAverageCpmBidAmountMicros,
S_BS.Auto_Bid_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS maximizeSpendAutoBid,
IF(S_BS.Performance_Goal_Type_Edit IS NOT NULL,
STRUCT(
S_BS.Performance_Goal_Type_Edit AS performanceGoalType,
S_BS.Performance_Goal_Amount_Edit * 100000 AS performanceGoalAmountMicros,
S_BS.Performance_Goal_Average_CPM_Bid_Edit * 100000 AS maxAverageCpmBidAmountMicros,
S_BS.Performance_Goal_Algorithm_Edit AS customBiddingAlgorithmId
),
NULL
) AS performanceGoalAutoBid
)
AS bidStrategy,
STRUCT(
S_ID.Integration_Code_Edit AS integrationCode,
S_ID.Details_Edit AS details
) AS integrationDetails,
STRUCT(
S_LI.Targeting_Expansion_Level_Edit AS targetingExpansionLevel,
S_LI.Exclude_1P_Edit AS excludeFirstPartyAudience
) AS targetingExpansion
FROM `{dataset}.SHEET_LineItems` AS S_LI
LEFT JOIN `{dataset}.SHEET_PartnerCosts` AS S_PC ON S_LI.Line_Item=S_PC.Line_Item
LEFT JOIN `{dataset}.SHEET_Pacing` AS S_P ON S_LI.Line_Item=S_P.Line_Item
LEFT JOIN `{dataset}.SHEET_FrequencyCaps` AS S_FC ON S_LI.Line_Item=S_FC.Line_Item
LEFT JOIN `{dataset}.SHEET_IntegrationDetails` AS S_ID ON S_LI.Line_Item=S_ID.Line_Item
LEFT JOIN `{dataset}.SHEET_BidStrategy` AS S_BS ON S_LI.Line_Item=S_BS.Line_Item
LEFT JOIN `{dataset}.DV_LineItems` AS DV_LI ON S_LI.Line_Item=DV_LI.displayName
WHERE S_LI.Action="INSERT"
AND DV_LI IS NULL
""".format(**project.task),
legacy=False
)
# Create Audit View
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"AUDIT_LineItems",
"""WITH
/* Check if sheet values are set */
INPUT_ERRORS AS (
SELECT
*
FROM (
SELECT
'Line Item' AS Operation,
CASE
WHEN Budget_Allocation_Type_Edit IS NULL THEN 'Missing Budget Allocation Type.'
WHEN Budget_Unit_Edit IS NULL THEN 'Missing Budget Unit.'
ELSE
NULL
END AS Error,
'ERROR' AS Severity,
COALESCE(Line_Item, 'BLANK') AS Id
FROM
`{dataset}.SHEET_LineItems`
)
WHERE
Error IS NOT NULL
),
/* Check duplicate inserts */
DUPLICATE_ERRORS AS (
SELECT
'Line Item' AS Operation,
'Duplicate Line Item name, insert will be ignored.' AS Error,
'WARNING' AS Severity,
COALESCE(S_LI.Line_Item, 'BLANK') AS Id
FROM `{dataset}.SHEET_LineItems` As S_LI
LEFT JOIN `{dataset}.DV_LineItems` AS DV_LI ON S_LI.Line_Item=DV_LI.displayName
WHERE S_LI.Action="INSERT"
AND DV_LI IS NOT NULL
)
SELECT * FROM INPUT_ERRORS
UNION ALL
SELECT * FROM DUPLICATE_ERRORS
""".format(**project.task),
legacy=False
)
query_to_view(
project.task["auth_bigquery"],
project.id,
project.task["dataset"],
"PATCH_LineItems",
"""SELECT *
FROM `{dataset}.SHEET_LineItems`
WHERE Line_Item NOT IN (SELECT Id FROM `{dataset}.AUDIT_LineItems` WHERE Severity='ERROR')
""".format(**project.task),
legacy=False
)
def barnacle():
if project.verbose: print 'BARNACLE'
accounts = set(get_rows("user", project.task['accounts']))
if 'accounts' in project.task['endpoints']:
# Accounts
rows = get_accounts(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Accounts', ACCOUNTS_SCHEMA),
"DCM_Accounts.csv",
rows
)
if 'profiles' in project.task['endpoints']:
# Profiles
rows = get_profiles(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Profiles', PROFILES_SCHEMA),
"DCM_Profiles.csv",
rows
)
# Profiles Campaigns
if project.verbose: print 'DCM Profile Campaigns'
put_rows(
project.task['out']['auth'],
put_json('CM_Profile_Campaigns', PROFILE_CAMPAIGNS_SCHEMA),
"DCM_Profile_Campaigns.csv",
PROFILE_CAMPAIGNS
)
# Profiles Sites
if project.verbose: print 'DCM Profile Sites'
put_rows(
project.task['out']['auth'],
put_json('CM_Profile_Sites', PROFILE_SITES_SCHEMA),
"DCM_Profile_Sites.csv",
PROFILE_SITES
)
# Profiles Roles
if project.verbose: print 'DCM Profile Roles'
put_rows(
project.task['out']['auth'],
put_json('CM_Profile_Roles', PROFILE_ROLES_SCHEMA),
"DCM_Profile_Roles.csv",
PROFILE_ROLES
)
# Profiles Advertisers
if project.verbose: print 'DCM Profile Advertisers'
put_rows(
project.task['out']['auth'],
put_json('CM_Profile_Advertisers', PROFILE_ADVERTISERS_SCHEMA),
"DCM_Profile_Advertisers.csv",
PROFILE_ADVERTISERS
)
if 'subaccounts' in project.task['endpoints']:
# Subaccounts
rows = get_subaccounts(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_SubAccounts', SUBACCOUNTS_SCHEMA),
"DCM_SubAccounts.csv",
rows
)
if 'advertisers' in project.task['endpoints']:
# Advertisers
rows = get_advertisers(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Advertisers', ADVERTISERS_SCHEMA),
"DCM_Advertisers.csv",
rows
)
#if 'changelogs' in project.task['endpoints']:
# # Changelogs
# rows = get_changelogs(accounts)
# put_rows(
# project.task['out']['auth'],
# put_json('CM_ChangeLogs', CHANGELOGS_SCHEMA),
# "DCM_ChangeLogs.csv",
# rows
# )
if 'campaigns' in project.task['endpoints']:
# Campaigns
rows = get_campaigns(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Campaigns', CAMPAIGNS_SCHEMA),
"DCM_Campaigns.csv",
rows
)
if 'sites' in project.task['endpoints']:
# Sites
rows = get_sites(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Sites', SITES_SCHEMA),
"DCM_Sites.csv",
rows
)
# Sites Contacts
if project.verbose: print 'DCM Site Contacts'
put_rows(
project.task['out']['auth'],
put_json('CM_Site_Contacts', SITE_CONTACTS_SCHEMA),
"DCM_Site_Contacts.csv",
SITE_CONTACTS
)
if 'roles' in project.task['endpoints']:
# Roles
rows = get_roles(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Roles', ROLES_SCHEMA),
"DCM_Roles.csv",
rows
)
if 'reports' in project.task['endpoints']:
# Reports
rows = get_reports(accounts)
put_rows(
project.task['out']['auth'],
put_json('CM_Reports', REPORTS_SCHEMA),
"DCM_Reports.csv",
rows
)
# Reports Deliveries
if project.verbose: print 'DCM Deliveries'
put_rows(
project.task['out']['auth'],
put_json('CM_Report_Deliveries', REPORT_DELIVERIES_SCHEMA),
"DCM_Report_Deliveriess.csv",
REPORT_DELIVERIES
)
def partner_cost_patch(commit=False):
patches = {}
changed = set()
rows = get_rows(project.task["auth_bigquery"], {
"bigquery": {
"dataset": project.task["dataset"],
"table": "PATCH_PartnerCosts",
}
},
as_object=True)
for row in rows:
lookup = row['Line_Item'] or row['Insertion_Order']
patches.setdefault(
lookup, {
"operation": "Partner Costs",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"body": {
"partnerCosts": []
}
}
})
if row['Line_Item']:
patches[lookup]["line_item"] = row['Line_Item']
patches[lookup]["parameters"]["lineItemId"] = lookup_id(
row['Line_Item'])
else:
patches[lookup]["insertion_order"] = row['Insertion_Order']
patches[lookup]["parameters"]["insertionOrderId"] = lookup_id(
row['Insertion_Order'])
patches[lookup]["parameters"]["body"]["partnerCosts"].append({
"costType":
row['Cost_Type_Edit'],
"feeType":
row['Fee_Type_Edit'],
"invoiceType":
row['Invoice_Type_Edit'],
"feeAmount":
int(float(row['Fee_Amount_Edit']) *
100000) if row['Fee_Amount_Edit'] else None,
"feePercentageMillis":
int(float(row['Fee_Percent_Edit']) *
1000) if row['Fee_Percent_Edit'] else None
})
if row['Cost_Type'] != row['Cost_Type_Edit'] \
or row['Fee_Type'] != row['Fee_Type_Edit'] \
or row['Invoice_Type'] != row['Invoice_Type_Edit'] \
or row['Fee_Amount'] != row['Fee_Amount_Edit'] \
or row['Fee_Percent'] != row['Fee_Percent_Edit']:
changed.add(lookup)
# Remove any patches where partner costs have not changed
for pc in list(patches.keys()):
if pc not in changed:
del patches[pc]
patches = list(patches.values())
patch_masks(patches)
if commit:
insertion_order_commit(patches)
line_item_commit(patches)
else:
patch_preview(patches)
def line_item_patch(commit=False):
def date_edited(value):
y, m, d = value.split("-")
return {"year": y, "month": m, "day": d}
patches = []
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"table":"PATCH_LineItems",
}},
as_object=True
)
for row in rows:
if row['Action'] == "DELETE":
patches.append({
"operation": "Line Items",
"action": "DELETE",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"line_item": row['Line_Item'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"lineItemId": lookup_id(row['Line_Item'])
}
})
elif row['Action'] == "PATCH":
line_item = {}
if row['Line_Item_Type'] != row['Line_Item_Type_Edit']:
line_item["lineItemType"] = row['Line_Item_Type_Edit']
if row['Status'] != row['Status_Edit']:
line_item['entityStatus'] = row['Status_Edit']
if row['Flight_Data_Type'] != row['Flight_Data_Type_Edit']:
line_item.setdefault("flight", {})
line_item["flight"]["flightDateType"] = row['Flight_Data_Type_Edit']
if row['Flight_Start_Date'] != row['Flight_Start_Date_Edit']:
line_item.setdefault("flight", {}).setdefault("dateRange", {})
line_item["flight"]["dateRange"]["startDate"] = date_edited(row['Flight_Start_Date_Edit'])
if row['Flight_End_Date'] != row['Flight_End_Date_Edit']:
line_item.setdefault("flight", {}).setdefault("endDate", {})
line_item["flight"]["dateRange"]["endDate"] = date_edited(row['Flight_End_Date_Edit'])
if row['Flight_Trigger'] != row['Flight_Trigger_Edit']:
line_item.setdefault("flight", {})
line_item["flight"]["triggerId"] = row['Flight_Trigger_Edit']
if row['Budget_Allocation_Type'] != row['Budget_Allocation_Type_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["budgetAllocationType"] = row['Budget_Allocation_Type_Edit']
if row['Budget_Unit'] != row['Budget_Unit_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["budgetUnit"] = row['Budget_Unit_Edit']
if row['Budget_Max'] != row['Budget_Max_Edit']:
line_item.setdefault("budget", {})
line_item["budget"]["maxAmount"] = int(
float(row['Budget_Max_Edit']) * 100000
)
if row['Partner_Revenue_Model'] != row['Partner_Revenue_Model_Edit']:
line_item.setdefault("partnerRevenueModel", {})
line_item["partnerRevenueModel"]["markupType"] = row['Partner_Revenue_Model_Edit']
if row['Partner_Markup'] != row['Partner_Markup_Edit']:
line_item.setdefault("partnerRevenueModel", {})
line_item["partnerRevenueModel"]["markupAmount"] = int(
float(row['Partner_Markup_Edit']) * 100000
)
if row['Conversion_Percent'] != row['Conversion_Percent_Edit']:
line_item.setdefault("conversionCounting", {})
line_item["conversionCounting"]["postViewCountPercentageMillis"] = int(
float(row['Conversion_Percent_Edit']) * 1000
)
if row['Targeting_Expansion_Level'] != row['Targeting_Expansion_Level_Edit']:
line_item.setdefault("targetingExpansion", {})
line_item["targetingExpansion"]["targetingExpansionLevel"] = row['Targeting_Expansion_Level_Edit']
if row['Exclude_1P'] != row['Exclude_1P_Edit']:
line_item.setdefault("targetingExpansion", {})
line_item["targetingExpansion"]["excludeFirstPartyAudience"] = row['Exclude_1P_Edit']
if line_item:
patches.append({
"operation": "Line Items",
"action": "PATCH",
"partner": row['Partner'],
"advertiser": row['Advertiser'],
"campaign": row['Campaign'],
"line_item": row['Line_Item'],
"parameters": {
"advertiserId": lookup_id(row['Advertiser']),
"lineItemId": lookup_id(row['Line_Item']),
"body": line_item
}
})
patch_masks(patches)
patch_preview(patches)
if commit:
line_item_commit(patches)
def dcm():
if project.verbose: print('DCM')
# stores existing report json
report = None
# check if report is to be deleted
if project.task.get('delete', False):
if project.verbose: print('DCM DELETE', project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None) or project.task['report'].get('report_id', None))
report_delete(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None),
)
# check if report is to be run
if project.task.get('report_run_only', False):
if project.verbose: print('DCM REPORT RUN', project.task['report'].get('name', None) or project.task['report'].get('report_id', None))
report_run(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None),
)
# check if report is to be created - DEPRECATED
if 'type' in project.task['report']:
if project.verbose: print('DCM CREATE')
report = report_create(
project.task['auth'],
project.task['report']['account'],
project.task['report']['name'],
project.task['report']
)
# check if report is to be created
if 'body' in project.task['report']:
if project.verbose: print('DCM BUILD', project.task['report']['body']['name'])
# filters can be passed using special get_rows handler, allows reading values from sheets etc...
if 'filters' in project.task['report']:
for f, d in project.task['report']['filters'].items():
for v in get_rows(project.task['auth'], d):
# accounts are specified in a unique part of the report json
if f in 'accountId':
project.task['report']['body']['accountId'] = v
# activities are specified in a unique part of the report json
elif f in 'dfa:activity':
project.task['report']['body']['reachCriteria']['activities'].setdefault('filters', []).append({
"kind":"dfareporting#dimensionValue",
"dimensionName": f,
"id": v
})
# all other filters go in the same place
else:
project.task['report']['body']['criteria'].setdefault('dimensionFilters', []).append({
"kind":"dfareporting#dimensionValue",
"dimensionName": f,
"id": v,
"matchType": "EXACT"
})
report = report_build(
project.task['auth'],
project.task['report']['body'].get('accountId') or project.task['report']['account'],
project.task['report']['body']
)
# moving a report
if 'out' in project.task:
filename, report = report_file(
project.task['auth'],
project.task['report']['account'],
project.task['report'].get('report_id', None),
project.task['report'].get('name', None) or project.task['report'].get('body', {}).get('name', None),
project.task['report'].get('timeout', 10),
)
if report:
if project.verbose: print('DCM FILE', filename)
# clean up the report
rows = report_to_rows(report)
rows = report_clean(rows)
# if bigquery, remove header and determine schema
schema = None
if 'bigquery' in project.task['out']:
schema = report_schema(next(rows))
project.task['out']['bigquery']['schema'] = schema
project.task['out']['bigquery']['skip_rows'] = 0
# write rows using standard out block in json ( allows customization across all scripts )
if rows: put_rows(project.task['auth'], project.task['out'], rows)
def line_item_load():
# load multiple partners from user defined sheet
def line_item_load_multiple():
rows = get_rows(
project.task["auth_sheets"], {
"sheets": {
"sheet": project.task["sheet"],
"tab": "Advertisers",
"range": "A2:A"
}
})
# String for filtering which entityStatus enums we want to see in the sheet
filter_string = 'entityStatus = "ENTITY_STATUS_ARCHIVED" OR entityStatus="ENTITY_STATUS_PAUSED" OR entityStatus="ENTITY_STATUS_ACTIVE" OR entityStatus="ENTITY_STATUS_DRAFT"'
for row in rows:
yield from API_DV360(
project.task["auth_dv"], iterate=True).advertisers().lineItems().list(
advertiserId=lookup_id(row[0]),
filter=filter_string).execute()
# write line_items to database
put_rows(
project.task["auth_bigquery"], {
"bigquery": {
"dataset":
project.task["dataset"],
"table":
"DV_LineItems",
"schema":
Discovery_To_BigQuery(
"displayvideo",
"v1").method_schema("advertisers.lineItems.list"),
"format":
"JSON"
}
}, line_item_load_multiple())
# write line_items to sheet
rows = get_rows(
project.task["auth_bigquery"],
{ "bigquery": {
"dataset": project.task["dataset"],
"query": """SELECT
CONCAT(P.displayName, ' - ', P.partnerId),
CONCAT(A.displayName, ' - ', A.advertiserId),
CONCAT(C.displayName, ' - ', C.campaignId),
CONCAT(I.displayName, ' - ', I.insertionOrderId),
CONCAT(L.displayName, ' - ', L.lineItemId),
'PATCH',
L.entityStatus,
L.entityStatus,
ARRAY_TO_STRING(L.warningMessages, '\\n'),
L.lineItemType,
L.lineItemType,
L.flight.flightDateType,
L.flight.flightDateType,
CONCAT(L.flight.dateRange.startDate.year, '-', L.flight.dateRange.startDate.month, '-', L.flight.dateRange.startDate.day),
CONCAT(L.flight.dateRange.startDate.year, '-', L.flight.dateRange.startDate.month, '-', L.flight.dateRange.startDate.day),
CONCAT(L.flight.dateRange.endDate.year, '-', L.flight.dateRange.endDate.month, '-', L.flight.dateRange.endDate.day),
CONCAT(L.flight.dateRange.endDate.year, '-', L.flight.dateRange.endDate.month, '-', L.flight.dateRange.endDate.day),
L.flight.triggerId,
L.flight.triggerId,
L.budget.budgetAllocationType,
L.budget.budgetAllocationType,
L.budget.budgetUnit,
L.budget.budgetUnit,
L.budget.maxAmount / 100000,
L.budget.maxAmount / 100000,
L.partnerRevenueModel.markupType,
L.partnerRevenueModel.markupType,
CAST(L.partnerRevenueModel.markupAmount AS FLOAT64) / 100000,
CAST(L.partnerRevenueModel.markupAmount AS FLOAT64) / 100000,
CAST(L.conversionCounting.postViewCountPercentageMillis AS Float64) / 1000,
CAST(L.conversionCounting.postViewCountPercentageMillis AS Float64) / 1000,
L.targetingExpansion.targetingExpansionLevel,
L.targetingExpansion.targetingExpansionLevel,
L.targetingExpansion.excludeFirstPartyAudience,
L.targetingExpansion.excludeFirstPartyAudience,
FROM `{dataset}.DV_LineItems` AS L
LEFT JOIN `{dataset}.DV_Advertisers` AS A
ON L.advertiserId=A.advertiserId
LEFT JOIN `{dataset}.DV_Campaigns` AS C
ON L.campaignId=C.campaignId
LEFT JOIN `{dataset}.DV_InsertionOrders` AS I
ON L.insertionOrderId=I.insertionOrderId
LEFT JOIN `{dataset}.DV_Partners` AS P
ON A.partnerId=P.partnerId
ORDER BY I.displayName, L.displayName
""".format(**project.task),
"legacy": False
}}
)
put_rows(
project.task["auth_sheets"],
{ "sheets": {
"sheet": project.task["sheet"],
"tab": "Line Items",
"range": "A2"
}},
rows
)
