The KORE Data Model supports this integration. It provides a more user-friendly data structure which is designed for better reporting and analytics uses.
Overview
The NFL's league office maintains a database containing event schedules, game-specific information, and team details. NFLs team may import this data into DWA with this integration.
To set up this integration, first reach out to the NFL's league office and request API credentials for the production environment:
- Client ID
- Client secret
Enter those details on KORE's Secure Information Transfer page and provide the link to your KORE Success Manager.
ERD and data dictionary
(This information is provided on a best-effort basis without guarantees. For clarity, most column names are omitted from this ERD.)
Download data dictionary (Excel file)
Table descriptions
The data in these tables are updated incrementally once a day with the full refresh of the current season. You can check the warehouse status report on Tableau to view the exact time the update happens each day.
| football_teams_venues | This table returns the list of team, season, and venue. Each record represents a mapping of team id, season, and venue id. Key information includes Venue name, venue id, team id, and season year. |
| football_venues | This table returns the list of venues and their details. Each record represents a single venue record and its address details. Key information includes venue id, address, city, country, postal code, and territory. |
| football_teams | This table returns the list of teams and their details. Each record represents team details for the particular season. Key information includes team id, season, team name, abbreviation, team type, conference abbreviation, conference name, current logo, division name, league, location, and nickname. |
| football_standings | This table returns a list of weekly standings of NFL teams. Each record represents team standing details for a particular week for the particular season and season type. Key information includes week number, season id, season type, team details, win details, losses details, ties details, rankings, points, overall_streak details. |
| football_games | This table returns a list of games and game details for the season and season type. Each record represents a unique game. Key information includes game id, category, date, time, venue, season, season type, status, week, home team details, away team details, broadcasting information, etc. |
| football_game_summaries | This table returns the list of game summaries. Each record represents a unique game and its summarized details. Key information includes game id, offset, clock, distance, start time, end time, attendance, weather, yardline, home team details, away team details, scores, timeouts, etc. |
| football_game_timeline_play_published | This table returns the list of offset points and all related information during the game for event type = ‘Play Published’. Key information includes game_id, offset, play_id, clock_time, play_deleted, play_description, play_end_time, play_is_end_of_quarter, quarter etc. |
Example queries
List the teams in the selected season and their details
SELECT DISTINCT (ft.id) AS Team_id,
ft.season, ft.abbreviation, ft.fullname, ft.nickname,
ftv.venue_id, fv.name, fv.city,fv.country
FROM nfl_shield.football_teams ft
LEFT JOIN nfl_shield.football_teams_venues ftv
ON ft.id = ftv.team_id
LEFT JOIN nfl_shield.football_venues fv
ON ftv.venue_id = fv.id
WHERE ft.season = 2020 AND ftv.season = 2020;
Get the list of team standings and last game details for a particular season at the end of a particular week:
SELECT fs.conference_rank, fs.division_rank, fs.team_id,
fs.seasontype, fs.team_fullname, fs.overall_games, fs.overall_wins,
fs.overall_losses, fg.id as game_id, fg.hometeam_fullname,
fg.awayteam_fullname, fg.venue_name, fgs.hometeam_score_total,
fgs.awayteam_score_total
FROM nfl_shield.football_standings fs
LEFT JOIN nfl_shield.football_games fg
ON fs.team_id = fg.awayteam_id OR fs.team_id = fg.hometeam_id
LEFT JOIN nfl_shield.football_game_summaries fgs
ON fg.id = fgs.gameid
WHERE fs.week = 17
AND fs.season = 2020
AND fg.week = 17
AND fg.season = 2020
ORDER BY conference_rank ASC;