# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="af06b4ff-001" colab_type="text"
# #1. Install Dependencies
# First install the libraries needed to execute recipes, this only needs to be done once, then click play.
#

# + id="af06b4ff-002" colab_type="code"
# !pip install git+https://github.com/google/starthinker


# + [markdown] id="af06b4ff-003" colab_type="text"
# #2. Get Cloud Project ID
# To run this recipe [requires a Google Cloud Project](https://github.com/google/starthinker/blob/master/tutorials/cloud_project.md), this only needs to be done once, then click play.
#

# + id="af06b4ff-004" colab_type="code"
CLOUD_PROJECT = 'PASTE PROJECT ID HERE'

print("Cloud Project Set To: %s" % CLOUD_PROJECT)


# + [markdown] id="af06b4ff-005" colab_type="text"
# #3. Get Client Credentials
# To read and write to various endpoints requires [downloading client credentials](https://github.com/google/starthinker/blob/master/tutorials/cloud_client_installed.md), this only needs to be done once, then click play.
#

# + id="af06b4ff-006" colab_type="code"
CLIENT_CREDENTIALS = 'PASTE CREDENTIALS HERE'

print("Client Credentials Set To: %s" % CLIENT_CREDENTIALS)


# + [markdown] id="af06b4ff-007" colab_type="text"
# #4. Enter DV360 ITP Audit Parameters
# Dashboard that shows performance metrics across browser to see the impact of ITP.
#  1. Follow the instructions from <a href="https://docs.google.com/document/d/1HaRCMaBBEo0tSKwnofWNtaPjlW0ORcVHVwIRabct4fY/edit?usp=sharing" target="_blank">this document</a>
# Modify the values below for your use case, can be done multiple times, then click play.
#

# + id="af06b4ff-008" colab_type="code"
FIELDS = {
  'recipe_timezone': 'America/Los_Angeles',  # Timezone for report dates.
  'recipe_name': '',  # Name of document to deploy to.
  'auth_write': 'service',  # Credentials used for writing data.
  'recipe_slug': 'ITP_Audit_Dashboard',  # BigQuery dataset for store dashboard tables.
  'auth_read': 'user',  # Credentials used for reading data.
  'cm_account_id': '',  # Campaign Manager Account Id.
  'date_range': 'LAST_365_DAYS',  # Timeframe to run the ITP report for.
  'cm_advertiser_ids': '',  # Optional: Comma delimited list of CM advertiser ids.
  'floodlight_configuration_id': '',  # Floodlight Configuration Id for the Campaign Manager floodlight report.
  'dv360_partner_ids': [],  # Comma delimited list of DV360 Partner ids.
  'dv360_advertiser_ids': [],  # Optional: Comma delimited list of DV360 Advertiser ids.
}

print("Parameters Set To: %s" % FIELDS)


# + [markdown] id="af06b4ff-009" colab_type="text"
# #5. Execute DV360 ITP Audit
# This does NOT need to be modified unless you are changing the recipe, click play.
#

# + id="af06b4ff-010" colab_type="code"
from starthinker.util.project import project
from starthinker.script.parse import json_set_fields

USER_CREDENTIALS = '/content/user.json'

TASKS = [
  {
    'drive': {
      'auth': 'user',
      'hour': [
      ],
      'copy': {
        'source': 'https://docs.google.com/spreadsheets/d/1rH_PGXOYW2mVdmAYnKbv6kcaB6lQihAyMsGtFfinnqg/',
        'destination': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}}
      }
    }
  },
  {
    'dataset': {
      'auth': 'user',
      'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}}
    }
  },
  {
    'dbm': {
      'auth': 'user',
      'report': {
        'timeout': 90,
        'filters': {
          'FILTER_ADVERTISER': {
            'values': {'field': {'name': 'dv360_advertiser_ids','kind': 'integer_list','order': 6,'default': [],'description': 'Optional: Comma delimited list of DV360 Advertiser ids.'}}
          },
          'FILTER_PARTNER': {
            'values': {'field': {'name': 'dv360_partner_ids','kind': 'integer_list','order': 5,'default': [],'description': 'Comma delimited list of DV360 Partner ids.'}}
          }
        },
        'body': {
          'timezoneCode': {'field': {'name': 'recipe_timezone','kind': 'timezone','description': 'Timezone for report dates.','default': 'America/Los_Angeles'}},
          'metadata': {
            'title': {'field': {'name': 'recipe_name','kind': 'string','prefix': 'ITP_Audit_Browser_','order': 1,'description': 'Name of report in DV360, should be unique.'}},
            'dataRange': {'field': {'name': 'date_range','kind': 'choice','order': 3,'default': 'LAST_365_DAYS','choices': ['LAST_7_DAYS','LAST_14_DAYS','LAST_30_DAYS','LAST_365_DAYS','LAST_60_DAYS','LAST_7_DAYS','LAST_90_DAYS','MONTH_TO_DATE','PREVIOUS_MONTH','PREVIOUS_QUARTER','PREVIOUS_WEEK','PREVIOUS_YEAR','QUARTER_TO_DATE','WEEK_TO_DATE','YEAR_TO_DATE'],'description': 'Timeframe to run the ITP report for.'}},
            'format': 'CSV'
          },
          'params': {
            'type': 'TYPE_GENERAL',
            'groupBys': [
              'FILTER_PARTNER',
              'FILTER_PARTNER_NAME',
              'FILTER_ADVERTISER',
              'FILTER_ADVERTISER_NAME',
              'FILTER_ADVERTISER_CURRENCY',
              'FILTER_MEDIA_PLAN',
              'FILTER_MEDIA_PLAN_NAME',
              'FILTER_INSERTION_ORDER',
              'FILTER_INSERTION_ORDER_NAME',
              'FILTER_LINE_ITEM',
              'FILTER_LINE_ITEM_NAME',
              'FILTER_PAGE_LAYOUT',
              'FILTER_WEEK',
              'FILTER_MONTH',
              'FILTER_YEAR',
              'FILTER_LINE_ITEM_TYPE',
              'FILTER_DEVICE_TYPE',
              'FILTER_BROWSER'
            ],
            'metrics': [
              'METRIC_MEDIA_COST_ADVERTISER',
              'METRIC_IMPRESSIONS',
              'METRIC_CLICKS',
              'METRIC_TOTAL_CONVERSIONS',
              'METRIC_LAST_CLICKS',
              'METRIC_LAST_IMPRESSIONS',
              'METRIC_CM_POST_CLICK_REVENUE',
              'METRIC_CM_POST_VIEW_REVENUE',
              'METRIC_REVENUE_ADVERTISER'
            ]
          }
        }
      },
      'delete': False
    }
  },
  {
    'dcm': {
      'auth': 'user',
      'report': {
        'timeout': 90,
        'account': {'field': {'name': 'cm_account_id','kind': 'integer','order': 2,'default': '','description': 'Campaign Manager Account Id.'}},
        'body': {
          'kind': 'dfareporting#report',
          'name': {'field': {'name': 'recipe_name','kind': 'string','prefix': 'ITP_Audit_Floodlight_','order': 1,'description': 'Name of report in DV360, should be unique.'}},
          'format': 'CSV',
          'type': 'FLOODLIGHT',
          'floodlightCriteria': {
            'dateRange': {
              'kind': 'dfareporting#dateRange',
              'relativeDateRange': 'LAST_30_DAYS'
            },
            'floodlightConfigId': {
              'kind': 'dfareporting#dimensionValue',
              'dimensionName': 'dfa:floodlightConfigId',
              'value': {'field': {'name': 'floodlight_configuration_id','kind': 'integer','order': 4,'default': '','description': 'Floodlight Configuration Id for the Campaign Manager floodlight report.'}},
              'matchType': 'EXACT'
            },
            'reportProperties': {
              'includeUnattributedIPConversions': False,
              'includeUnattributedCookieConversions': True
            },
            'dimensions': [
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:site'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:floodlightAttributionType'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:interactionType'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:pathType'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:browserPlatform'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:platformType'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:week'
              }
            ],
            'metricNames': [
              'dfa:activityClickThroughConversions',
              'dfa:activityViewThroughConversions',
              'dfa:totalConversions',
              'dfa:totalConversionsRevenue'
            ]
          },
          'schedule': {
            'active': True,
            'repeats': 'WEEKLY',
            'every': 1,
            'repeatsOnWeekDays': [
              'Sunday'
            ]
          },
          'delivery': {
            'emailOwner': False
          }
        }
      },
      'out': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Floodlight_CM_Report',
          'is_incremental_load': False
        }
      },
      'delete': False
    }
  },
  {
    'dcm': {
      'auth': 'user',
      'report': {
        'timeout': 90,
        'account': {'field': {'name': 'cm_account_id','kind': 'integer','order': 2,'default': '','description': 'Campaign Manager Account Id.'}},
        'filters': {
          'dfa:advertiser': {
            'values': {'field': {'name': 'cm_advertiser_ids','kind': 'integer_list','order': 3,'default': '','description': 'Optional: Comma delimited list of CM advertiser ids.'}}
          }
        },
        'body': {
          'kind': 'dfareporting#report',
          'name': {'field': {'name': 'recipe_name','kind': 'string','order': 1,'prefix': 'ITP_Audit_Browser_','default': 'ITP_Audit_Dashboard_Browser','description': 'Name of the Campaign Manager browser report.'}},
          'format': 'CSV',
          'type': 'STANDARD',
          'criteria': {
            'dateRange': {
              'kind': 'dfareporting#dateRange',
              'relativeDateRange': {'field': {'name': 'date_range','kind': 'choice','order': 3,'default': 'LAST_365_DAYS','choices': ['LAST_7_DAYS','LAST_14_DAYS','LAST_30_DAYS','LAST_365_DAYS','LAST_60_DAYS','LAST_7_DAYS','LAST_90_DAYS','MONTH_TO_DATE','PREVIOUS_MONTH','PREVIOUS_QUARTER','PREVIOUS_WEEK','PREVIOUS_YEAR','QUARTER_TO_DATE','WEEK_TO_DATE','YEAR_TO_DATE'],'description': 'Timeframe to run the ITP report for.'}}
            },
            'dimensions': [
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:campaign'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:campaignId'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:site'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:advertiser'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:advertiserId'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:browserPlatform'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:platformType'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:month'
              },
              {
                'kind': 'dfareporting#sortedDimension',
                'name': 'dfa:week'
              }
            ],
            'metricNames': [
              'dfa:impressions',
              'dfa:clicks',
              'dfa:totalConversions',
              'dfa:activityViewThroughConversions',
              'dfa:activityClickThroughConversions'
            ],
            'dimensionFilters': [
            ]
          },
          'schedule': {
            'active': True,
            'repeats': 'WEEKLY',
            'every': 1,
            'repeatsOnWeekDays': [
              'Sunday'
            ]
          },
          'delivery': {
            'emailOwner': False
          }
        }
      },
      'out': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_CM_Browser_Report_Dirty',
          'is_incremental_load': False
        }
      },
      'delete': False
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'tab': 'Enviroment',
      'range': 'A:B',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Environment'
        }
      }
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'tab': 'Browser',
      'range': 'A:C',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Browser'
        }
      }
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'tab': 'CM_Browser_lookup',
      'range': 'A:C',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_CM_Browser_lookup'
        }
      }
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'tab': 'Device_Type',
      'range': 'A:B',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Device_Type'
        }
      }
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'tab': 'Floodlight_Attribution',
      'range': 'A:B',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Floodlight_Attribution'
        }
      }
    }
  },
  {
    'dbm': {
      'auth': 'user',
      'report': {
        'name': {'field': {'name': 'recipe_name','kind': 'string','prefix': 'ITP_Audit_Browser_','order': 1,'description': 'Name of report in DV360, should be unique.'}}
      },
      'out': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
          'table': 'z_Dv360_Browser_Report_Dirty',
          'header': True,
          'schema': [
            {
              'name': 'Partner_Id',
              'type': 'INTEGER'
            },
            {
              'name': 'Partner',
              'type': 'STRING'
            },
            {
              'name': 'Advertiser_Id',
              'type': 'INTEGER'
            },
            {
              'name': 'Advertiser',
              'type': 'STRING'
            },
            {
              'name': 'Advertiser_Currency',
              'type': 'STRING'
            },
            {
              'name': 'Campaign_Id',
              'type': 'INTEGER'
            },
            {
              'name': 'Campaign',
              'type': 'STRING'
            },
            {
              'name': 'Insertion_Order_Id',
              'type': 'INTEGER'
            },
            {
              'name': 'Insertion_Order',
              'type': 'STRING'
            },
            {
              'name': 'Line_Item_Id',
              'type': 'INTEGER'
            },
            {
              'name': 'Line_Item',
              'type': 'STRING'
            },
            {
              'name': 'Environment',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Week',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Month',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Year',
              'type': 'INTEGER',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Line_Item_Type',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Device_Type',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Browser',
              'type': 'STRING',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Media_Cost_Advertiser_Currency',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Impressions',
              'type': 'INTEGER',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Clicks',
              'type': 'INTEGER',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Total_Conversions',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Post_Click_Conversions',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Post_View_Conversions',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Cm_Post_Click_Revenue',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Cm_Post_View_Revenue',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            },
            {
              'name': 'Revenue_Adv_Currency',
              'type': 'FLOAT',
              'mode': 'NULLABLE'
            }
          ]
        }
      }
    }
  },
  {
    'itp_audit': {
      'auth': 'user',
      'account': {'field': {'name': 'cm_account_id','kind': 'integer','order': 2,'default': '','description': 'Campaign Manager Account Id.'}},
      'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 1,'default': 'ITP_Audit_Dashboard','description': 'BigQuery dataset for store dashboard tables.'}},
      'sheet': {'field': {'name': 'recipe_name','prefix': 'ITP Audit ','kind': 'string','order': 1,'description': 'Name of document to deploy to.','default': ''}},
      'timeout': 60
    }
  }
]

json_set_fields(TASKS, FIELDS)

project.initialize(_recipe={ 'tasks':TASKS }, _project=CLOUD_PROJECT, _user=USER_CREDENTIALS, _client=CLIENT_CREDENTIALS, _verbose=True, _force=True)
project.execute(_force=True)


StarcoderdataJupyterScriptsDedupFiltered