# Youtube Monthly Reports

This is intended as a quick reference to the flow. For more complete info, see [YouTube Monthly Reports Reference on Notion](https://www.notion.so/YouTube-Monthly-Reports-6162f1d7a0854695b644b8a8173f04a5).

## Report Data Sources and Configurations

The drop bucket for this flow located in `s3://prod-orcd-ftp/ftp/youtube/`.

The archive bucket for this flow depends on *report_name* and _date_ parameters:
`s3://cucumbers/YouTubeMonthly/reports/{report_name}/{date:%Y-%m-%d}/`

1. The [legacy version of the flow](https://github.com/theorchard/swf-feed-ingestion/blob/master/feed_ingestion/flows/youtube_monthly/config.py#L33-L48) is maintained under the report name 
**legacy_monthly**.  It follows the **stage  ⟶ staging_raw** 
sequence of steps and is composed of two reports (see samples below):
   - YouTube_dmgi_M_20220701_20220731_rawdata_v1-0.csv.zip
   - YouTube_theorchardmusic_M_20220701_20220731_rawdata_v1-0.csv.gz
2. The [newly added reports](https://github.com/theorchard/swf-feed-ingestion/blob/master/feed_ingestion/flows/youtube_monthly/config.py#L49-L212) follow the **temp_staging_raw ⟶ staging_raw** sequence. Some newly added reports contain multiple subreports, and a separate 
   staging raw table is dedicated to each subreport. You can learn more 
   about new reports on [YouTube Monthly Reports Reference on Notion](https://www.notion.so/YouTube-Monthly-Reports-6162f1d7a0854695b644b8a8173f04a5).

## Workflow Description & Execution
- The flow checks whether the file(s) for a particular report is(are)
 available on `s3://prod-orcd-ftp/ftp/youtube/`.
- If the source data is available, the flow will proceed to preprocessing 
  the files (unzipping the source file, splitting the subreports into separate 
  files) and moving them to the respective archive bucket. 
- If all the files (or subreports) are successfully moved  to the archive 
  bucket, the flow will proceed to populating the respective staging raw 
  table.


There are two possible approaches for executing the flow:
- triggering [swf-youtube-monthly](https://scheduler.theorchard.io/job/swf-youtube-monthly-exec/) with parameters:
  - **MONTHS_BACK**: the number of months to go back to from today,
  - **REPORT_NAME**: the name of the report we want to run the flow for.
- triggering [prod-swf-ingestion-exec](https://scheduler.theorchard.io/job/prod-swf-ingestion-exec/) with parameters:
     - **WORKFLOW**: `youtube_monthly`,
       - **CONTEXT**: `{"context_date": "2021-12-03", "report_name": 
         "red_summary"}` (using any date from a month will work).


## Add new report

If you need to add new report, you should follow the steps below:
* download sample report from the ftp bucket `s3://prod-orcd-ftp/ftp/youtube/`
* if it is multi-subreport report you need the whole file. Large files are usually single report so you can download only head of it:
```shell
aws s3 cp s3://prod-orcd-ftp/ftp/youtube/YouTube_theorchardmusic_M_20240301_claim_raw_v1-1.csv.gz - | gunzip -c | head -n 100 > YouTube_theorchardmusic_M_20240301_claim_raw_v1-1.csv.gz
```
* run `python gen.py YouTube_theorchardmusic_M_20240301_claim_raw_v1-1.csv`
* it generates DDL sql file which can be used for `created_temp_staging_raw_*.sql` and [DB PR](https://github.com/theorchard/database/blob/32a0c06a7ec2efb4e8b01c819a6e4b77b325c02d/snowflake%2FFACTS%2Fbuild%2Fchangelog%2Fddl%2FDS-5796_CREATE_TABLE_staging_raw_youtube_monthly_adj_claim_summary_v1_1_dmgi_GRANTS.sql)
* add report configuration to `config.py`

## Troubleshooting

If a staging raw table isn’t populated by the 
12th/13th/14th of each month (for the previous month), the following steps 
have to be undertaken:

1. We shall look into the execution statuses of **youtube_monthly** reports 
   for the respective period․ If the execution statuses aren't complete, we shall 
   rerun the **youtube_monthly** flow for the particular report_name and date.
2. If the execution status are complete, but the data isn’t available, we 
   shall look into the **[youtube ftp bucket on S3](s3://prod-orcd-ftp/ftp/youtube/)** for the respective source data file(s).
3. If the mentioned files are not available on the ftp, we shall check for 
   them on **YouTube Studio**. More about this can be found on [YouTube Monthly Reports Reference on Notion](https://www.notion.so/YouTube-Monthly-Reports-6162f1d7a0854695b644b8a8173f04a5).