def segment_clear(): sheets_clear( project.task["auth_sheets"], project.task["sheet"], "Segments", "A2:Z" )
def create_cm_site_segmentation(config, task): # Read sheet to bq table sheet_rows = sheets_read(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A:C', retries=10) if not sheet_rows: sheet_rows = [] schema = [ { "type": "STRING", "name": "Site_Dcm", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Impressions", "mode": "NULLABLE" }, { "type": "STRING", "name": "Site_Type", "mode": "NULLABLE" } ] rows_to_table( config, auth=task['auth_bq'], project_id=config.project, dataset_id=task['dataset'], table_id=CM_SITE_SEGMENTATION_SHEET_TABLE, rows=sheet_rows, schema=schema, skip_rows=1, disposition='WRITE_TRUNCATE' ) # Get Site_Type from the sheet run_query_from_file(config, task, Queries.cm_site_segmentation.replace('{{dataset}}', task['dataset']), CM_SITE_SEGMENTATION_TABLE) # Move Table back to sheets query = 'SELECT * from `' + config.project + '.' + task['dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`' rows = query_to_rows(config, task['auth_bq'], config.project, task['dataset'], query, legacy=False) # makes sure types are correct in sheet rows = rows_to_type(rows) sheets_clear(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A2:C') sheets_write(config, task['auth_sheets'], task['sheet'], 'CM_Site_Segments', 'A2:C', rows)
def partner_cost_clear(config, task): sheets_clear( config, task["auth_sheets"], task["sheet"], "Partner Costs", "A2:Z" )
def audit_clear(config, task): sheets_clear( config, task['auth_sheets'], task['sheet'], 'Audit', 'A2:Z' )
def dv_advertiser_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'DV_Advertisers', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.list')) sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV Advertisers', 'B2:D')
def clear(self): """Clears the store in the Bulkdozer feed. """ if self.trix_id: sheets_clear(self.auth, self.trix_id, 'Store', 'A1:Z1') self._store = {} self._id_map = {}
def line_item_clear(config, task): table_create( config, task["auth_bigquery"], config.project, task["dataset"], "DV_LineItems", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.lineItems.list")) sheets_clear(config, task["auth_sheets"], task["sheet"], "Line Items", "B2:H")
def advertiser_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Advertisers', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.list')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Advertisers', 'B2:D')
def line_item_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_LineItems", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.lineItems.list")) sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Line Items", "A2:AI")
def insertion_order_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_InsertionOrders", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.insertionOrders.list")) sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Insertion Orders", "A2:Z")
def inventory_source_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'DV_Inventory_Sources', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('inventorySources.list')) sheets_clear(config, task['auth_sheets'], task['sheet'], 'Inventory Sources', 'A2:Z')
def dv_algorithm_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'DV_Algorithms', Discovery_To_BigQuery( 'displayvideo', 'v1').method_schema('customBiddingAlgorithms.list')) sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV Algorithms', 'B2:D')
def cm_profile_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'CM_Profiles', Discovery_To_BigQuery('dfareporting', 'v3.4').method_schema('userProfiles.list', iterate=True)) sheets_clear(config, task['auth_sheets'], task['sheet'], 'CM Profiles', 'B2:E')
def partner_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Partners', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('partners.list'), ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Partners', 'B2:Z')
def campaign_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Campaigns', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.campaigns.list'), ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Campaigns', 'B2:Z')
def creative_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_Creatives", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.creatives.list"), ) sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Creatives", "B2:Z")
def create_cm_site_segmentation(project): # Read sheet to bq table sheet_rows = sheets_read('user', project.task['sheet'], 'CM_Site_Segments', 'A:C', retries=10) if not sheet_rows: sheet_rows = [] schema = [{ "type": "STRING", "name": "Site_Dcm", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Impressions", "mode": "NULLABLE" }, { "type": "STRING", "name": "Site_Type", "mode": "NULLABLE" }] rows_to_table(auth='service', project_id=project.id, dataset_id=project.task['dataset'], table_id=CM_SITE_SEGMENTATION_SHEET_TABLE, rows=sheet_rows, schema=schema, skip_rows=1, disposition='WRITE_TRUNCATE') # Get Site_Type from the sheet run_query_from_file( os.path.join(os.path.dirname(__file__), SQL_DIRECTORY + CM_SITE_SEGMENTATION_FILENAME), project.id, project.task['dataset'], CM_SITE_SEGMENTATION_TABLE) # Move Table back to sheets query = 'SELECT * from `' + project.id + '.' + project.task[ 'dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`' rows = query_to_rows('service', project.id, project.task['dataset'], query, legacy=False) # makes sure types are correct in sheet rows = rows_to_type(rows) sheets_clear('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C') sheets_write('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C', rows)
def create_dv360_segments(config, task): a1_notation = 'A:N' schema = [ { "type": "STRING", "name": "Advertiser", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Advertiser_Id", "mode": "NULLABLE" }, { "type": "STRING", "name": "Campaign", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Campaign_Id", "mode": "NULLABLE" }, { "type": "STRING", "name": "Insertion_Order", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Insertion_Order_Id", "mode": "NULLABLE" }, { "type": "STRING", "name": "Line_Item", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Line_Item_Id", "mode": "NULLABLE" }, { "type": "STRING", "name": "Line_Item_Type", "mode": "NULLABLE" }, { "type": "INTEGER", "name": "Impressions", "mode": "NULLABLE" }, { "type": "STRING", "name": "SegmentAutoGen", "mode": "NULLABLE" }, { "type": "STRING", "name": "Segment1", "mode": "NULLABLE" }, { "type": "STRING", "name": "Segment2", "mode": "NULLABLE" }, { "type": "STRING", "name": "Segment3", "mode": "NULLABLE" } ] sheet_rows = sheets_read(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation, retries=10) if not sheet_rows: sheet_rows = [] print('DV360 SEGMENT SHEET TABLE WRITE') rows_to_table( config, auth=task['auth_bq'], project_id=config.project, dataset_id=task['dataset'], table_id=DV360_CUSTOM_SEGMENTS_SHEET_TABLE, rows=sheet_rows, schema=schema, skip_rows=1, disposition='WRITE_TRUNCATE' ) # Run Query if config.verbose: print('RUN DV360 Custom Segments Query') run_query_from_file(config, task, Queries.dv360_custom_segments.replace('{{dataset}}', task['dataset']), DV360_CUSTOM_SEGMENTS_TABLE) # Move Table back to sheets query = 'SELECT * from `' + config.project + '.' + task['dataset'] + '.' + DV360_CUSTOM_SEGMENTS_TABLE + '`' rows = query_to_rows(config, task['auth_bq'], config.project, task['dataset'], query, legacy=False) # makes sure types are correct in sheet a1_notation = a1_notation[:1] + '2' + a1_notation[1:] rows = rows_to_type(rows) sheets_clear(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation) sheets_write(config, task['auth_sheets'], task['sheet'], 'DV3 Segments', a1_notation, rows)
def targeting_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Options', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('TargetingOption')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Targeting Options', 'A2:Z') table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Assigned', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('AssignedTargetingOption'))
def create_cm_site_segmentation(project): # Read sheet to bq table sheet_rows = sheets_read( 'user', project.task['sheet'], 'CM_Site_Segments', 'A:C', retries=10) if not sheet_rows: sheet_rows = [] schema = [{ 'type': 'STRING', 'name': 'Site_Dcm', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Impressions', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Site_Type', 'mode': 'NULLABLE' }] rows_to_table( auth='service', project_id=project.id, dataset_id=project.task['dataset'], table_id=CM_SITE_SEGMENTATION_SHEET_TABLE, rows=sheet_rows, schema=schema, skip_rows=1, disposition='WRITE_TRUNCATE') # Get Site_Type from the sheet run_query_from_file(Queries.cm_site_segmentation, CM_SITE_SEGMENTATION_TABLE) # Move Table back to sheets query = 'SELECT * from `' + project.id + '.' + project.task[ 'dataset'] + '.' + CM_SITE_SEGMENTATION_TABLE + '`' rows = query_to_rows( 'service', project.id, project.task['dataset'], query, legacy=False) # makes sure types are correct in sheet rows = rows_to_type(rows) sheets_clear('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C') sheets_write('user', project.task['sheet'], 'CM_Site_Segments', 'A2:C', rows)
def edit_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'EDIT_Preview', SCHEMA_PREVIEW ) table_create(project.task['auth_bigquery'], project.id, project.task['dataset'], 'EDIT_Log', SCHEMA_LOG ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Preview', 'A2:Z' ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Error', 'A2:Z' ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Success', 'A2:Z' )
def edit_clear(config, task): table_create(config, task['auth_bigquery'], config.project, task['dataset'], 'EDIT_Preview', SCHEMA_PREVIEW) table_create(config, task['auth_bigquery'], config.project, task['dataset'], 'EDIT_Log', SCHEMA_LOG) sheets_clear(config, task['auth_sheets'], task['sheet'], 'Preview', 'A2:Z') sheets_clear(config, task['auth_sheets'], task['sheet'], 'Warning', 'A2:Z') sheets_clear(config, task['auth_sheets'], task['sheet'], 'Error', 'A2:Z') sheets_clear(config, task['auth_sheets'], task['sheet'], 'Success', 'A2:Z')
def cm_campaign_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'CM_Campaigns', Discovery_To_BigQuery( 'dfareporting', 'v3.4' ).method_schema( 'campaigns.list', iterate=True ) ) sheets_clear( config, task['auth_sheets'], task['sheet'], 'CM Campaigns', 'B2:G' )
def cm_account_clear(config, task): table_create( config, task['auth_bigquery'], config.project, task['dataset'], 'CM_Accounts', Discovery_To_BigQuery( 'dfareporting', 'v3.4' ).method_schema( 'accounts.list', iterate=True ) ) sheets_clear( config, task['auth_sheets'], task['sheet'], 'CM Accounts', 'B2:D' )
def partner_cost_clear(): sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Partner Costs", "A2:Z")
def clear(self): """Clears the log tab in the Bulkdozer feed, useful when a new execution is starting.""" sheets_clear(self.auth, self.trix_id, 'Log', 'A1:B') self._row = 1
def frequency_cap_clear(): sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Frequency Caps", "A2:Z")
def pacing_clear(): sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Pacing", "A2:Z")
def create_dv360_segments(project): a1_notation = 'A:M' schema = [{ 'type': 'STRING', 'name': 'Advertiser', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Advertiser_Id', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Campaign', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Campaign_Id', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Insertion_Order', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Insertion_Order_Id', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Line_Item', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Line_Item_Id', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Line_Item_Type', 'mode': 'NULLABLE' }, { 'type': 'INTEGER', 'name': 'Impressions', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Segment1', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Segment2', 'mode': 'NULLABLE' }, { 'type': 'STRING', 'name': 'Segment3', 'mode': 'NULLABLE' }] sheet_rows = sheets_read( 'user', project.task['sheet'], 'DV3 Segments', a1_notation, retries=10) if not sheet_rows: sheet_rows = [] print('DV360 SEGMENT SHEET TABLE WRITE') rows_to_table( auth='service', project_id=project.id, dataset_id=project.task['dataset'], table_id=DV360_CUSTOM_SEGMENTS_SHEET_TABLE, rows=sheet_rows, schema=schema, skip_rows=1, disposition='WRITE_TRUNCATE') # Run Query if project.verbose: print('RUN DV360 Custom Segments Query') run_query_from_file(Queries.dv360_custom_segments, DV360_CUSTOM_SEGMENTS_TABLE) # Move Table back to sheets query = 'SELECT * from `' + project.id + '.' + project.task[ 'dataset'] + '.' + DV360_CUSTOM_SEGMENTS_TABLE + '`' rows = query_to_rows( 'service', project.id, project.task['dataset'], query, legacy=False) # makes sure types are correct in sheet a1_notation = a1_notation[:1] + '2' + a1_notation[1:] rows = rows_to_type(rows) sheets_clear('user', project.task['sheet'], 'DV3 Segments', a1_notation) sheets_write('user', project.task['sheet'], 'DV3 Segments', a1_notation, rows)
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)