# Youtube Bulk Reports Bulk Reports flow ingests all available daily bulk reports into staging tables. These data powers all Insights. More about Bulk Reports could be found in [Google Developer's Guide](https://developers.google.com/youtube/reporting/v1/reports?utm_source=devtools) More about flow design can be found here: [Tech Design for YouTube Bulk Reports ETL](https://docs.google.com/document/d/1jKJ57u1ywZu2Cjo1zh2Mbs0WSHnF-wyM_i11hxI88eQ/edit#heading=h.3cxfogq8xpsi) Caveats: - YT updates reports during one month after publishing them. So the flow configured to not only load the most recent date available, but also to reload days from a week ago ± a few days and a month ago ± a few days. This way we can have fresh data and minimize discrepancy between YT Studio and Insights. - Staging tables live in `PROD.YT_REPORTS_API` schema for historical reasons. ### Flow executor context examples: `date`, `report_name`, `licensor` are required parameters. #### Reload Pass `'reload': 'True'` in order to force reload of the report independant from status. #### Content owner To run the flow for all content owners: `{"report_name": "asset_traffic_source", "licensor": "theorchard", "reload": "True", "context_date": "2021-06-23"}` To run the flow for single content owner (`theorchard` licensor only): `{"selected_owner":"ent", "report_name": "asset_traffic_source", "licensor": "theorchard", "reload": "True", "context_date": "2021-06-23"}` #### Download only Some reports do not have staging_raw tables. These reports are only downloaded to s3. Parameter: `config.reports_download_only` ### Credentials Flow looks for Google API credentials in AWS secret `dev/swf-shared-youtube-access-token/YOUTUBE_ACCESS_TOKEN` Example: ```json { "access_token": "", "client_id": "", "client_secret": "", "refresh_token": "", "token_expiry": "2021-07-13T14:13:43Z", "token_uri": "https://oauth2.googleapis.com/token", "user_agent": "datalytics-etls", "revoke_uri": "https://accounts.google.com/o/oauth2/revoke", "id_token": null, "token_response": { "access_token": "", "expires_in": 3599, "scope": "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/yt-analytics.readonly https://www.googleapis.com/auth/yt-analytics-monetary.readonly", "token_type": "Bearer" }, "scopes": [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/yt-analytics.readonly", "https://www.googleapis.com/auth/yt-analytics-monetary.readonly" ], "token_info_uri": "https://www.googleapis.com/oauth2/v3/tokeninfo", "invalid": false, "_class": "OAuth2Credentials", "_module": "oauth2client.client" } ``` To user token generator you need to set these environment variables: GOOGLE_API_SECRETS_FILE_PATH= GOOGLE_API_TOKEN_FILE_PATH= GOOGLE_API_APPLICATION_NAME='datalytics-etls' To generate them use [generate_google_api_token.py](../../bin/generate_google_api_token.py) script ## Manual token generation 1. Go to ***API & Services*** -> [Credentilas](https://console.cloud.google.com/apis/credentials) 2. Copy **client Id** and **client Secret** from credential set named `datalytics-etls` 3. To create **accessToken** and **refreshToken** go to *[OAuth 2.0 Playground](https://developers.google.com/oauthplayground/)* 4. choose `Use your own OAuth credentials` in `OAuth 2.0 configuration` (gears in the top right corner) 5. paste **client Id** and **client Secret** to the config fields 6. select the scopes `https://www.googleapis.com/auth/spreadsheets`, `https://www.googleapis.com/auth/yt-analytics.readonly` and `https://www.googleapis.com/auth/yt-analytics-monetary.readonly` 7. click `Authorize API` button 8. click the `Exchange authorization code for tokens` button 9. copy JSON refresh and an access token which will appear on the right side 10. paste the credentials (`client_id`, `client_secret`, `access_token`, `refresh_token`) to the placeholders of the example from the previous section ## SME data For SME, we get the data through AWS Data Lake Formation. It can be queried by AWS Athena. So let's check available dates for sample bulk report. * Open Athena console https://console.aws.amazon.com/athena/home?region=us-east-1 * On the left sidebar select * Data source: AwsDataCatalog * Database: sme_youtube_api_db * write the query ```sql select report_date, count(*) from content_owner_ad_rates_a1 group by report_date order by report_date desc ``` * you should see results like: | # | report_date | count | |---|------------|----------| | 1 | 2021-05-04 | 24108516 | | 2 | 2021-05-03 | 23862265 | | 3 | 2021-05-02 | 23955665 | Note: The name of the table for particular report can be found in [reports_mapping](config.py) # adding new report In order to add new report job should be created https://developers.google.com/youtube/reporting/v1/reference/rest/v1/jobs List of already created jobs onBehalfOfContentOwner is Content owner ID ``` from feed_ingestion.util.youtube_util import get_authenticated_services SCOPES = ['https://www.googleapis.com/auth/yt-analytics-monetary.readonly', "https://www.googleapis.com/auth/yt-analytics.readonly"] API_SERVICE_NAME = 'youtubereporting' API_VERSION = 'v1' service = get_authenticated_services('youtube_access_token_youtube_bulk.json', API_SERVICE_NAME, API_VERSION) jobs = service.jobs().list( onBehalfOfContentOwner='J8vAyKuNSYBIN_9RIdxggQ').execute() print(jobs) ``` new job creation ``` from feed_ingestion.util.youtube_util import get_authenticated_services SCOPES = ['https://www.googleapis.com/auth/yt-analytics-monetary.readonly', "https://www.googleapis.com/auth/yt-analytics.readonly"] API_SERVICE_NAME = 'youtubereporting' API_VERSION = 'v1' service = get_authenticated_services('youtube_access_token_youtube_bulk.json', API_SERVICE_NAME, API_VERSION) service.jobs().create(body=dict( reportTypeId=report_type, name=name ), onBehalfOfContentOwner='J8vAyKuNSYBIN_9RIdxggQ').execute() ```