# 1. Installation ETL

The installation process is very straightforward. You will need Python 3.4 installed.

```
$ git clone git@github.com:theorchard/ows-film-transparency.git 
$ cd ows-film-transparency/etls
```

#### Environment Variables
The .env.shadow is a shell script that exports environment variables used for credentials, locations, etc.

```sh
etls> cp .env.shadow .env
```

#### Installing Requirements
```sh
etls> pyvenv ./env
etls> pip install -r requirements.txt
etls> pip install -r requirements-dev.txt
```

#### Running The Script
```sh
$ source env/bin/activate
$ source .env
$ python application.py decider {flow_name}
$ python application.py worker {flow_name}
$ python application.py execute {flow_name}
```

*flow_name is the folder name under etls/flows/

Examples:
```sh
$ python application.py execute cable_calculation
$ python application.py execute cable_ingestion -s 2016-11-07 -e 2016-11-13
```

For more information, use the -h or --help flag in any of the commands.
```sh
$ python application.py -h
$ python application.py execute -h
```

#### Coding Style
All flows follow pep8 style.
```sh
$ flake8 flows/cable_calculation/
```

#### Unit Tests
The ETLs use `py.test` to for testing and code coverage reporting.
```sh
$ py.test -v --cov=flows --cov-report=term-missing tests/
```


# 2. Feed Status
All workflows update their respective log tables ending in `_etl_log`. 
Examples: `digital_revenue_etl_log`, `cable_revenue_ingestion_etl_log`, and `theatrical_revenue_etl_log` on the Aurora DB. 
General feed status reports for Data Squad is entered into their DynamoDB tables.  
[Monty](https://monty.theorchard.io/static/feed_ingestion_dashboard.html) dashboard for ETLs status.  
[Jenkins dashboard](http://jenkins.theorchard.com:8080/view/ows-film-transparency/) with ETL jobs.

# 3. Notification
Notifications are sent out to listeners on specific SNS topics. 
The SNS notification topic is configured in the environment variable `SNS_TOPIC_ARN`.

# 4. How It Works

The ETL codebase follows file structure conventions from the 
[swf-feed-ingestion](https://github.com/theorchard/swf-feed-ingestion#how-it-works) repo.

Below are summaries of each ETL. Documentation [here](https://docs.google.com/document/d/1wx7VAe00fd0tfXJYPBt7K74cEujVv0IRxAgRy59QiOc/edit#heading=h.hj33cw696qy4).

- **digital**
  - Gets digital revenue data from Snowflake's `fact_analytics` table for a set of UPCs. 
  The aggregated results (`upc`, `date`, `transaction_type`, `country`, `format`) are stored in Aurora's `digital_revenue` table.  
  - :x: **CURRENTLY NOT USED**
- **cable_ingestion**
  - Gets the cable revenue data from Rentrak's FTP server. The files are a pair of: `.tar.gz` & `.ctl`. 
  - The tar file has multiple csvs in it with sales data, and the ctl file has md5 checksums of the csv files.
  - The naming convention is: `rentrak_theorchard_subscriptions_<startdate>_<enddate>_<creation_timestamp>`.
  Example: `rentrak_theorchard_subscriptions_20161003_20161211_20161218095941.ctl`, `rentrak_theorchard_subscriptions_20161003_20161211_20161218095941.tar.gz`.
  - Only matching filename pairs are accepted as data.
  - Each row of every csv file is entered as is in the `cable_revenue_raw` table in Aurora DB.
  If the DB already has data within the time range of the csv files, it is overwritten.
  - :x: **CURRENTLY NOT USED** (contract with Rentrak expired + logic has changed)
- **cable_calculation**
  - Converts the `cable_revenue_raw` data to `cable_revenue` by applying the split percentage and other calculations.
  - The data we get from Rentrak has Operator/Providers info. 
  That is converted to our internal stores data, and use this to determine the split that The Orchard earns.
  - :x: **CURRENTLY NOT USED** (see item above)
- **theatrical**
  - Desktop software (known as "Hollywood Software") is used to download CSVs of theatrical data. The file is then manually uploaded to S3 at a set schedule.
  - Following the schedule the ETL reads the S3 file and populates `theatrical_revenue_raw` on Aurora. 
  - The ETL then transforms the data and inserts into the `theatrical_revenue` table.
  - :x: **CURRENTLY NOT USED** (contract with "Hollywood Software" expired)
- **theatrical_cuts**
  - :x: **NOT IMPLEMENTED**
- **projections**
  - This ETL is not executed from the command line or Jenkins but executed from an S3 event explained below.
  - Projections (future and original) CSVs are uploaded from an OA page and stored on a S3.
  - S3 is setup to automatically send a new object event messages to a lambda script.
  - The lambda script starts a SWF execution with contextual data.
- **distribution_fee**
  - Ingests base and territory splits from `art_relation` DB for film labels.
- **sales_data**
  - Gets accounting revenue data from Snowflake's `fact_sales` table for a set of UPCs. 
  The aggregated results (`upc`, `date`, `transaction_type`, `store`, `country`) are stored in Aurora's `accounting_revenue` table. 
  - ETLs runs daily but ingests data only once a month - after the accounting run, i.e. when new accounting period appears in `fact_sales`.
  - Data from `fact_sales` is first ingested into `accounting_revenue_raw` table, then gets aggregated and transformed into daily values (monthly total value divided by number of days in the month)
  - Note that unlike digital revenue, accounting revenue values are _already_ after distribution fee applied