
*See my other [GitHub Repo](https://github.com/krozic/imdb-chrome-extension) for the chrome extension I built off this data.

# Summary

This application takes a chosen film as an input and creates a plot and table describing the rank of the film for the specific genres it is categorized in.

The intent of this project is to compare the average imdb ratings between genres and visualize the distribution of ratings for each genre in an effort to translate a film rating to a genre specific ranking. 

<div>
	![app](/images/imdb-rating/app.png)
</div>

# Data Sources

2 datasets, 2 APIs, and a web crawler comprise the backend data handling for this project:
- [IMDb open dataset](https://www.imdb.com/interfaces/)
- [Stefano Leone's IMDb extensive dataset](https://www.kaggle.com/stefanoleone992/imdb-extensive-dataset)
- [OMDb API](https://www.omdbapi.com/) (Third party API)
- [IMDb-API](https://imdb-api.com/) (Third party API)
- [IMDB website](https://www.imdb.com/) (BS4 web crawling)


# Methods

The tables 'title.basics.tsv', 'title.ratings.tsv' and 'IMDB movies.csv' were loaded into Microsoft SQL Server as TitleBasics, TitleRatings and TitleExtras respectively. These tables were used for exploratory data analysis and subsequent creation of the tables necessary for visualization (OMDb API was used to source the Rotten Tomatoes and Metacritic ratings). The database schema is shown below (created in [dbdiagram.io](https://dbdiagram.io/))

<div style={{'text-align': 'center'}}>
	![app](/images/imdb-rating/imdb_table_schema.png)
</div>

The python package `pyodbc` was then used to load the tables from this database into `pandas` for visualization in `matplotlib` and `plotly`. Dash was used for the frontend of the web application.



IMDB Ratings Visualization

YouTube Analytics on AWS


\* ETL made with [draw.io](https://drive.google.com/file/d/1GGBgKkbAsrv0-ypzIxPXeZlYJxb4aL-O/view?usp=sharing)

{/* ## Spotify ETL Using Python, Apache Airflow, and MS SQL Server */}
# Summary
The Spotify API documentation is very well written and can be used as a walkthrough for a wide variety of project applications. In this project I set up a daily extraction of my recently played tracks using Python for the data processing and Apache Airflow as the scheduler. The resulting data is appended to a database on a local Microsoft SQL Server.

Here is the resulting table:

<div style={{margin: '2rem 2.5rem'}}>
	<Image 
		src={ '/images/recent-spotify/table.png' }
		alt='table'
		layout='responsive'
		width={601}
		height={285}
	/>
</div>

## Data Extraction
```python
headers = {  
    'Accept' : 'application/json',  
    'Content-Type' : 'application/json',  
    'Authorization' : 'Bearer {token}'.format(token=token)  
}  
  
today = datetime.now() + timedelta(hours=4)  
yesterday = today - timedelta(days=1)  
yesterday_unix_timestamp = int(yesterday.timestamp()) * 1000  
r = requests.get('https://api.spotify.com/v1/me/player/recently-played?after={time}'.format(time=yesterday_unix_timestamp), headers=headers)  
  
data = r.json()
```

## Data Transformation and Validation
```python
for song in data['items']:  
    primary_key.append(song['played_at'])  
    song_names.append(song['track']['name'])  
    artist_names.append(song['track']['artists'][0]['name'])  
    timestamps.append(song['played_at'][0:10])  
  
song_dict = {  
    'primary_key' : primary_key,  
    'song_name' : song_names,  
    'artist_name' : artist_names,  
    'timestamp' : timestamps  
}  
  
song_df = pd.DataFrame(song_dict, columns = ['primary_key', 'song_name', 'artist_name', 'timestamp'])
```

```python
def check_if_valid_data(df) -> bool:  
    if df.empty:  
        print('No songs downloaded')  
        quit()  
  
    if pd.Series(df['primary_key']).is_unique:  
        pass  
    else:  
        raise Exception('Primary Key Check Violated')  
  
    if df.isnull().values.any():  
        raise Exception('Null value found')  
  
    yesterday = datetime.now() - timedelta(hours=20)  
    timestamps = df['primary_key']  
    for timestamp in timestamps:  
        played_time = datetime.strptime(timestamp[:19], '%Y-%m-%dT%H:%M:%S')  
        if played_time < yesterday:  
            raise Exception('At least one song is not from the past 24 hrs')  
  
    return True
```

## Loading Data into MS SQL Server
Initiating table:
```python
driver = '{ODBC DRIVER 17 for SQL Server}'  
server = 'localhost\server_name'  
database = 'db_name'  
  
connection_string = '''  
Driver={driver};  
Server={server};  
Database={database};  
Trusted_Connection=yes;  
'''.format(  
    driver=driver,  
    server=server,  
    database=database  
)  
  
conn = pyodbc.connect(connection_string)  
cursor = conn.cursor()  

create_table = '''  
IF (  
    NOT EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.TABLES  
		WHERE TABLE_NAME = 'my_played_tracks'        
		)    
)BEGIN  
    CREATE TABLE my_played_tracks (
		primary_key VARCHAR(200) NOT NULL PRIMARY KEY,
		song_name VARCHAR(200),
		artist_name VARCHAR(200),
		timestamp VARCHAR(200)
	)  
END;  
'''  
  
cursor.execute(create_table)  
conn.commit()
```

Loading Data:
```python
quoted = urllib.parse.quote_plus(connection_string)  
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))  
song_df.to_sql('my_played_tracks', engine, index=False, if_exists='append')

conn.close()
```

## Scheduling with Apache Airflow
Airflow was installed locally for this project by following the Apache [documentation](https://airflow.apache.org/docs/apache-airflow/stable/start/local.html).

```bash
airflow webserver -p 8080
```

```bash
airflow scheduler
```

Airflow Windows: https://towardsdatascience.com/run-apache-airflow-on-windows-10-without-docker-3c5754bb98b4

## OAuth 2.0 Authorization 
In order to extract the song information from Spotify's API we need an 'Access Token'. This can be acquired manually from [Spotify](https://developer.spotify.com/console/get-recently-played/). If you only require temporary access, you can skip this next part, however a code would have to be manually produced and copied into the program every time it runs.

In order to programatically request an 'Access Token', authorization must be granted through the [OAuth 2.0 framework](https://developer.spotify.com/documentation/general/guides/authorization/). This program follows the [Authorization Code Flow](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/). 3 pieces of information are required for the end product:
- client_id
- client_secret
- refresh_token

Getting these variables involves creating a Spotify Dev App and manually authorizing the app in a browser.

### Spotify Dev App
Spotify has an [App Guide](https://developer.spotify.com/documentation/general/guides/authorization/app-settings/) that describes how to create an App. The 'client_id' and 'client_secret' are both at the top of the app page. 

Remember to add a 'Redirect URI' to the app (I used https://localhost:8000/callback). It doesn't have to be a real website, it is just required to have a trusted destination for Spotify to send the 'authorization code' required for the next step.

### One-Time Manual User Authorization
The rest of this setup will follow the [Authorization Code Flow](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/) document. 

The goal is to acquire a 'Refresh Token' which will allow us to request and receive a new 'Access Token' programatically. In order to get this token a 'GET request' must be sent to the '/authorize' endpoint. This GET request can be as simple as a browser url:

```
https://accounts.spotify.com/authorize?client_id=<client_id_from_app>&scope=user-read-recently-played&response_type=code&redirect_uri=https%3A%2F%2Flocalhost%3A8888%2Fcallback
```

Parameters:
- client_id: ID from your newly created app
- response_type: code
- redirect_uri: The url added to the app settings, encoded [here](https://www.urlencoder.org/)
- scope: user-read-recently-played (or any [other scopes])
	- Use ''%20' as a space to separate multiple scopes

After building this URL, authorization is requested:



<div style={{margin: '0rem 7rem'}}>
	<Image 
	src={ '/images/recent-spotify/permission.png' }
	alt='permission'
	layout='responsive'
	width={623}
	height={744}
	/>
</div>

After the redirect, the 'authorization code' is appended to the url in the browser:


<div style={{margin: '0rem'}}>
	<Image 
	src={ '/images/recent-spotify/url.png' }
	alt='url'
	layout='responsive'
	width={778}
	height={35}
	/>
</div>

Run a `curl` command with the code to get an Access Token and a **Refresh Token**

```bash
curl -H "Authorization: Basic <base64 encoded client_id:secret_id>" \
	-H "Content-Type: application/x-www-form-urlencoded" \
	-d grant_type=authorization_code \
	-d code=AQBicXEN4K...qU_2-Pr0Q \
	-d redirect_uri=https%3A%2F%2Flocalhost%3A8888%2Fcallback \
	https://accounts.spotify.com/api/token
```

Parameters:
- Authorization: Basic \<base64 encoded client_id:secret_id>
	- Ex. paste your 'client_id:secret_id' string into [here](https://www.base64encode.org/)
- Content-Type: application/x-www-form-urlencoded
- grant_type=authorization_code
- code=AQBicXEN4Ks...WYAqU_2-Pr0Q
	- Given by Spotify in the redirect URL
- redirect_uri=https%3A%2F%2Flocalhost%3A8000%2Fcallback
	- Only used for verification

The response should look like this:
```json
{
   "access_token": "NgCXRK...MzYjw",
   "token_type": "Bearer",
   "scope": "user-read-private user-read-email",
   "expires_in": 3600,
   "refresh_token": "NgAagA...Um_SHo"
}
```

The 'access_token' can be used to authenticate connection the API, however it expires in 1 hour. The 'refresh_token', however, can be used along with the base64 encoded app credentials to get another 1 hour 'access_token'--giving unlimited access.

The code below is used to acquire a new 'access_token' at each run:

```python
def refresh(self):  
  
    query = 'https://accounts.spotify.com/api/token'  
  
    response = requests.post(query,  
                             data={'grant_type': 'refresh_token',  
                                   'refresh_token': refresh_token},  
                             headers={'Authorization': 'Basic ' + base_64})  
    return response.json()['access_token']
```

### Helpful Links
- [OAuth 2](https://developer.spotify.com/documentation/general/guides/authorization/)
- [App Auth](https://developer.spotify.com/documentation/general/guides/authorization/code-flow/)
- [App setup](https://developer.spotify.com/documentation/general/guides/authorization/app-settings/)
- [Using App Access](https://developer.spotify.com/documentation/general/guides/authorization/use-access-token/)

For setting the scope of the returned data:
- [Scopes guide](https://developer.spotify.com/documentation/general/guides/authorization/scopes/)
- [Recently Played scope](https://developer.spotify.com/documentation/web-api/reference/#/operations/get-the-users-currently-playing-track)

Others Implementations:
- [Implementation](https://github.com/kylepw/spotify-api-auth-examples)
- [YT Url hacking](https://www.youtube.com/watch?v=yAXoOolPvjU)

Spotify Recently Played Songs


# Summary

Extract-transform-loaded (ETL): GA data (1.4 million records) ➜ BigQuery ➜ Google Cloud Storage ➜ Tableau Server Connection.
	
BigQuery was used to calculate desirable metrics (Page views, time on page, bounces, sessions) for use as KPIs in Tableau. Created filters and parameters for segmentation and comparison analysis.

# What is this data?
The sample dataset contains obfuscated Google Analytics 360 data from the [Google Merchandise Store](https://www.googlemerchandisestore.com/shop.axd/Home?utm_source=Partners&utm_medium=affiliate&utm_campaign=Data%20Share%20Promo). It consists of 900k records (4.2 million unnested), 338 variables, and includes the following kinds of information:

-   Traffic source data: information about where website visitors originate. This includes data about organic traffic, paid search traffic, display traffic, etc.
-   Content data: information about the behavior of users on the site. This includes the URLs of pages that visitors look at, how they interact with content, etc.
-   Transactional data: information about the transactions that occur on the Google Merchandise Store website.

You can query the data in GCP BigQuerry by date in the following way:  
`FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20170101'`

The schema can be found [here](https://support.google.com/analytics/answer/3437719?hl=en)

# SQL Breakdown
Collect 8 descriptors and 5 Measures for import

Descriptors:
- hit_year
- hit_month
- pagePath
- source
- channel_grouping
- device_category
- country
- city 

Measures:
- pageviews
- unique_pageviews
- total_time_on_page_combined
- total_bounces
- total_sessions

## Constant Categories
The descriptor variables will be selected for all measure queries.
```SQL
SUBSTR(date, 1, 4) AS hit_year, 
SUBSTR(date, 5, 2) AS hit_month, 
hits.page.pagepath AS pagePath, 
trafficsource.source AS source, 
channelgrouping as channel_grouping, 
device.deviceCategory as device_category, 
geonetwork.country as country, 
geonetwork.city as city
```

# Tableau Calculations
Parameters:
- Comparison Selector: M/M (current month vs previous month), Y/Y (current month vs month from previous year), YTD (current YTD vs previous YTD)
- Date Selector: Select the Month/Year of interest

Categorical Descriptors:
- Geo Location
- Device Category
- Traffic Source
- Channel Grouping

Metrics:
- Sessions
- Pageviews
- Unique Pageviews
- Time on Page
- Bounces
- Average Unique Pageviews per Session
- Average Time on Page per Session
- Percentage of Sessions that Bounce

Sessions:
- Total Session Comparison: SUM(current session range) - SUM(previous session range) / SUM(previous session range)
- Session YTD (Year to Date): Return [Total Sessions] for selected Month and prior, within same Year.
- Session CM (Current Month): Return [Total Sessions] for selected Month and Year
- 

CM: Current Month
PM: Previous Month
PY: Previous Year
YTD: Year-to-Date
PYTD: Previous Year-to-Date
LTD: Life-to-Date (for grey charts)

For Percent of Total (Avg Unique Pageviews CM for Channel Grouping), the calculation must be modified from TOTAL() to WINDOW_SUM(). This is because the TOTAL() function does not work properly on measures that are already an aggregate function. [ref](https://kb.tableau.com/articles/issue/percent-of-total-adds-up-to-more-than-100)



Google Analytics Dashboard


# Description

Statistics and research data was sourced from [Our World in Data](https://ourworldindata.org/covid-deaths) to visualize how covid-19 has spread over time and between countries. The data was explored and cleaned with Microsoft SQL and visualized in Tableau.

# Finished Product

<div id='image'>
	![Dashboard Screenshot](/images/covid-data-vis/dashboard_screenshot.png)
</div>

The interactive dashboard is hosted on Tableau Public and can be found [here](https://public.tableau.com/app/profile/kyle.rozic/viz/CovidVisualization_16319417831480/Dashboard1?publish=yes)

Kyle Rozic

SQL

All Tags

IMDB Ratings Visualization

YouTube Analytics on AWS

Spotify Recently Played Songs

Google Analytics Dashboard

Covid-19 Data Dashboard