<jupyter_start><jupyter_text>1. Install DependenciesFirst install the libraries needed to execute recipes, this only needs to be done once, then click play.<jupyter_code>!pip install git+https://github.com/google/starthinker<jupyter_output><empty_output><jupyter_text>2. Get Cloud Project IDTo 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.<jupyter_code>CLOUD_PROJECT = 'PASTE PROJECT ID HERE'

print("Cloud Project Set To: %s" % CLOUD_PROJECT)<jupyter_output><empty_output><jupyter_text>3. Get Client CredentialsTo 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.<jupyter_code>CLIENT_CREDENTIALS = 'PASTE CREDENTIALS HERE'

print("Client Credentials Set To: %s" % CLIENT_CREDENTIALS)<jupyter_output><empty_output><jupyter_text>4. Enter CM360 Oculi ParametersExport CM360 Creatives into BigQuery, process them with the Vision API, and generate a breakdown of each creative asset mapped back to its parent. Also generate a series of views to flatten the data. 1. Wait for BigQuery->->->Oculi_... to be created. 1. Then use the data for analysis. 1. Or give these intructions to the client.Modify the values below for your use case, can be done multiple times, then click play.<jupyter_code>FIELDS = {
  'auth_cm': 'user',  # CM360 read credentials.
  'auth_bigquery': 'service',  # BigQuery read/ write credentials.
  'account': '',  # CM360 Account Identifier
  'limit': 1000,  # Number of creatives to pull.
  'recipe_slug': '',  # name of dataset in BigQuery.
}

print("Parameters Set To: %s" % FIELDS)<jupyter_output><empty_output><jupyter_text>5. Execute CM360 OculiThis does NOT need to be modified unless you are changing the recipe, click play.<jupyter_code>from starthinker.util.configuration import Configuration
from starthinker.util.configuration import commandline_parser
from starthinker.util.configuration import execute
from starthinker.util.recipe import json_set_fields

USER_CREDENTIALS = '/content/user.json'

TASKS = [
  {
    'dataset': {
      'auth': 'user',
      'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
    }
  },
  {
    'google_api': {
      '__comment__': 'Download all creatives, limit set to 20K for 4 hour processing time, and up to maximum 80K to prevent triggering 500 Error in API.',
      'auth': 'user',
      'api': 'dfareporting',
      'version': 'v3.4',
      'function': 'creatives.list',
      'kwargs': {
        'accountId': {'field': {'name': 'account','kind': 'integer','order': 3,'default': '','description': 'CM360 Account Identifier'}},
        'sortField': 'ID',
        'sortOrder': 'DESCENDING'
      },
      'iterate': True,
      'limit': {'field': {'name': 'limit','kind': 'integer','order': 4,'default': 1000,'description': 'Number of creatives to pull.','choices': [1000,5000,10000,20000,40000,80000]}},
      'results': {
        'bigquery': {
          'auth': 'user',
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
          'table': 'CM_Creatives'
        }
      }
    }
  },
  {
    'url': {
      'auth': 'user',
      'status': True,
      'read': True,
      'urls': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
          'query': "WITH             URL_PARTS AS (               SELECT                 C.id,                 CAST(C.AdvertiserId AS STRING) AS AdvertiserId,                 CA.assetIdentifier.name AS Name               FROM `CM_Creatives` AS C, UNNEST(creativeAssets) AS CA               WHERE REPLACE(RIGHT(CA.assetIdentifier.name, 4), '.', '') IN ('jpg', 'png', 'gif', 'jpeg','html','htm')               AND CA.size.width >1 and CA.size.height > 1             )             SELECT FORMAT('https://s0.2mdn.net/%s/%s', AdvertiserId, REPLACE(Name, ' ', '%20')) AS URL, id AS URI FROM URL_PARTS             UNION ALL             SELECT  FORMAT('https://s0.2mdn.net/sadbundle/%s', REPLACE(Name, ' ', '%20')) AS URL, id AS URI FROM URL_PARTS             UNION ALL             SELECT FORMAT('https://s0.2mdn.net/simgad/%s', REPLACE(Name, ' ', '%20')) AS URL, id AS URI FROM URL_PARTS           ",
          'legacy': False
        }
      },
      'to': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
          'table': 'Creative_URLs'
        }
      }
    }
  },
  {
    'vision_api': {
      'auth': 'user',
      'requests': {
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
          'query': "             SELECT             STRUCT(               Read AS content,               STRUCT(                 URI AS imageUri               ) AS source             ) AS image,             [               STRUCT(                 'TEXT_DETECTION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'IMAGE_PROPERTIES' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'SAFE_SEARCH_DETECTION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'LABEL_DETECTION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'LOGO_DETECTION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'FACE_DETECTION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               ),               STRUCT(                 'OBJECT_LOCALIZATION' AS type,                 10 AS maxResults,                 'builtin/stable' AS model               )             ] AS features             FROM `Creative_URLs`             WHERE Status=200           ",
          'legacy': False
        }
      },
      'responses': {
        'bigquery': {
          'auth': 'user',
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
          'table': 'Vision_Creatives'
        }
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': 'SELECT           C.*,           VC.*           FROM `{dataset}.CM_Creatives` AS C           LEFT JOIN `{dataset}.Vision_Creatives` AS VC           ON C.Id=CAST(VC.imageUri AS INT64)         ',
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_Creatives'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': 'SELECT           CAST(imageUri AS INT64) AS creativeID,           description AS label,           score         FROM           `{dataset}.Vision_Creatives`, UNNEST( labelAnnotations)         ',
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_labelAnnotations'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': "WITH Creative_Sizes AS (           SELECT             Id AS creativeId,             MAX(size.width) AS width,             MAX(size.height) AS height,           FROM `{dataset}.CM_Creatives`           GROUP BY 1         )          SELECT           CAST(VC.imageUri AS INT64) AS creativeId,           LOWER(T.description) AS word,           SAFE_DIVIDE(MAX(V.x) - MIN(v.x), ANY_VALUE(width)) * SAFE_DIVIDE(MAX(V.y) - MIN(v.y), ANY_VALUE(height)) AS area_fraction           FROM             `{dataset}.Vision_Creatives` AS VC             JOIN UNNEST(textAnnotations) AS T             JOIN UNNEST(boundingPoly.vertices) AS V             JOIN Creative_Sizes AS CS             ON CAST(VC.imageUri AS INT64) = CS.creativeId           WHERE             /* Exclude small and common words */             LENGTH(description) > 2             AND LOWER(description) NOT IN ('for', 'the')           GROUP BY 1,2         ",
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_textAnnotations'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'function': 'RGB To HSV',
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': 'SELECT             CAST(VC.imageUri AS INT64) AS creativeId,             LOWER(LO.name) AS name,             (MAX(V.x) - MIN(v.x)) * (MAX(V.y) - MIN(v.y)) AS areaFraction           FROM `{dataset}.Vision_Creatives` AS VC             JOIN UNNEST(localizedObjectAnnotations) AS LO             JOIN UNNEST(boundingPoly.normalizedVertices) AS V           GROUP BY 1,2         ',
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_localizedObjectAnnotations'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'query': "WITH Vision_Colors AS (           SELECT             CAST(VC.imageUri AS INT64) AS creativeId,             STRUCT(               CAST(C.color.red AS INT64) AS r,               CAST(C.color.green AS INT64) AS g,               CAST(C.color.blue AS INT64) AS b             ) as rgb,             STRUCT(               FORMAT('%02X', CAST(C.color.red AS INT64)) as r,               FORMAT('%02X', CAST(C.color.green AS INT64)) as g,               FORMAT('%02X', CAST(C.color.blue AS INT64)) as b             ) as html,             {dataset}.rgb_to_hsv(C.color.red, C.color.green, C.color.blue) AS hsv,             (0.2126*C.color.red + 0.7152*C.color.green + 0.0722*C.color.blue) / 255.0 AS percievedBrightness,             C.score,             C.pixelFraction AS areaFraction           FROM             `{dataset}.Vision_Creatives` AS VC             JOIN UNNEST(imagePropertiesAnnotation.dominantColors.colors) AS C          )           SELECT            *,            CASE              WHEN hsv.h < 90 THEN (90 - hsv.h) / 90              WHEN hsv.h < 270 THEN 0              ELSE (hsv.h - 270) / 90            END AS warmness,            CASE              WHEN hsv.h < 90 THEN 0              WHEN hsv.h < 270 THEN ( 90 - ABS(180 - hsv.h)) / 90              ELSE 0            END AS coldness          FROM Vision_Colors;         ",
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_imagePropertiesAnnotation'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': '           WITH Creative_Sizes AS (             SELECT               Id AS creativeId,               MAX(size.width) AS width,               MAX(size.height) AS height,             FROM `{dataset}.CM_Creatives`             GROUP BY 1           )            SELECT             CAST(VC.imageUri AS INT64) AS creativeId,             F.angerLikelihood,             F.headwearLikelihood,             F.surpriseLikelihood,             F.sorrowLikelihood,             F.joyLikelihood,             F.blurredLikelihood,             F.panAngle,             F.rollAngle,             F.tiltAngle,             detectionConfidence AS score,             SAFE_DIVIDE(MAX(v.x) - MIN(v.x), ANY_VALUE(width)) * SAFE_DIVIDE(MAX(v.y) - MIN(v.y), ANY_VALUE(height)) AS area_fraction           FROM             `{dataset}.Vision_Creatives` AS VC             JOIN UNNEST(faceAnnotations ) AS F             JOIN UNNEST(boundingPoly.vertices) AS V             JOIN Creative_Sizes AS CS             ON CAST(VC.imageUri AS INT64) = CS.creativeId           GROUP BY 1,2,3,4,5,6,7,8,9,10,11         ',
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_faceAnnotations'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'query': "           WITH Creative_Sizes AS (             SELECT               Id AS creativeId,               MAX(size.width) AS width,               MAX(size.height) AS height,             FROM `{dataset}.CM_Creatives`             GROUP BY 1           ),           Creative_Faces AS (           SELECT             CAST(VC.imageUri AS INT64) AS creativeId,             description AS logo,             score,             SAFE_DIVIDE((MAX(v.x) + MIN(v.x)) / 2, ANY_VALUE(width)) AS x_fraction,             SAFE_DIVIDE((MAX(v.y) + MIN(v.y)) / 2, ANY_VALUE(height)) AS y_fraction,             SAFE_DIVIDE(MAX(v.x) - MIN(v.x), ANY_VALUE(width)) * SAFE_DIVIDE(MAX(v.y) - MIN(v.y), ANY_VALUE(height)) AS area_fraction           FROM             `{dataset}.Vision_Creatives` AS VC             JOIN UNNEST(logoAnnotations ) AS L             JOIN UNNEST(boundingPoly.vertices) AS V             JOIN Creative_Sizes AS CS             ON CAST(VC.imageUri AS INT64) = CS.creativeId           GROUP BY 1,2,3           )            SELECT           *,           score * area_fraction AS prominenceScore,           RANK() OVER (PARTITION BY creativeId ORDER BY score * area_fraction DESC) AS prominenceRank,           CASE             WHEN x_fraction < 0.33 AND y_fraction < 0.33 THEN 'TOP LEFT'             WHEN x_fraction > 0.66 AND y_fraction < 0.33 THEN 'TOP RIGHT'             WHEN x_fraction < 0.33 AND y_fraction > 0.66 THEN 'BOTTOM LEFT'             WHEN x_fraction > 0.66 AND y_fraction > 0.66 THEN 'BOTTOM RIGHT'             WHEN y_fraction < 0.33 THEN 'TOP CENTER'             WHEN y_fraction > 0.66 THEN 'BOTTOM CENTER'             WHEN X_fraction > 0.66 THEN 'RIGHT CENTER'             WHEN x_fraction < 0.33 THEN 'LEFT CENTER'             ELSE 'CENTER'           END AS position           FROM Creative_Faces;         ",
        'parameters': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}}
        },
        'legacy': False
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','order': 5,'default': '','description': 'name of dataset in BigQuery.'}},
        'view': 'Oculi_logoAnnotations'
      }
    }
  }
]

json_set_fields(TASKS, FIELDS)

execute(Configuration(project=CLOUD_PROJECT, client=CLIENT_CREDENTIALS, user=USER_CREDENTIALS, verbose=True), TASKS, force=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14421083802191295, 'fraction_numerical': 0.016345869114598757, 'mean_word_length': 2.0245387784345334, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 33, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '97971', 'license': 'Apache-2.0', 'n_tokens_mistral': 5262, 'n_tokens_neox': 4997, 'n_words': 1322, 'path': 'colabs/cm360_oculi.ipynb', 'repo_name': 'isabella232/starthinker', 'size': '16885'}