MLB Wheelhouse – Two Circles Help Center

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

MLB Wheelhouse is a data warehouse operated by the league. It makes a wide variety of data available to MLB clubs, some of which can be imported into DWA using this integration:

Customer feed
Email marketing
League schedule and results
One Signal
Shop sales
StubHub data
Survey (Qualtrics)

To set up this integration, contact your KORE Success Manager. Also reach out to the MLB Office for help configuring a service user. Provide the service user credentials to your KORE success manager with our secure information transfer tool.

Schema name:mlb_wheelhouse_v2

Customer feed

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)

Download data dictionary (Excel file)

Table descriptions

Click to expand / collapse

all_interactions_vw	This table is a roll-up of all wheelhouse data sources by email address, interaction date and data source. This also includes location data for the customer as well as the spend information.
customer_source_agg	This table is a roll-up of all wheelhouse data sources by email address and data source. Includes the most recent location and IPID/GUID data for each customer as well as their lifetime spend.
customer_summary_agg	This table consists of data of a customer displaying aggregated tools for economic and engagement activity.
ltv_estimates	This table has estimates of how much a fan is going to spend with the team, over the next 12 months. These will be updated weekly.
sth_nonrenewal_probability	Each row in this table contains a predictions of the likelihood a season ticket holder will not renew at the end of the season, for a given club.
acxiom_demographics	This table contains one row per fan, with demographic data points sourced from Acxiom.
rfm_metrics_historical	In this table, each row contains a single customer's RFM Scores for a given club and point in time, as indicated by the as_of_date field.
team_avidity_scores_historical	The data in this table is a snapshot of a fan's team avidity score for a specific club at a specific point of time as indicated by the model_date field. This table will be updated at the start of each month with an updated snapshot. In this table, each row contains a single customer's Team Avidity Scores for a given club and point in time, as indicated by the model_date field, which is included as the 1st field with all other fields listed below in the same order after that field.
ballpark_checkins	Each record represents a user checking in with the Ballpark app. Ballpark Checkins data created starting on 3/26/2020 are coming in real-time via PubSub messages.
ballpark_views	Each record represents a user accessing a club-specific feature or page in the Ballpark app.
account_linking	A linking occurs when a user links his/her Ticketing Account to their MLB.com Account. The Account Linking feed will contain information about these events for links done through the Ballpark app. The feed will be incremental and contain only new events that have occurred since the previous send time.

Example queries

Find avid fans at moderate risk of not renewing their season tickets:

SELECT * FROM mlbwheelhouse_v2.sth_nonrenewal_probability snp 
	INNER JOIN mlbwheelhouse_v2.customer_source_agg csa 
	ON snp.email_addr = csa.email_address
WHERE snp.classification = 'AA - MODERATE'
	AND csa.avidity_classification = 'STRONG'
LIMIT 50;

Find ticket holders who have purchased tickets on StubHub:

SELECT * FROM mlbwheelhouse_v2.all_interactions_vw aiv
WHERE aiv.data_source = 'StubHub Order';

Email marketing

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)

Download data dictionary (Excel file)

Table descriptions

Click to expand / collapse

email_campaign_metadata	This table exists for MLB clubs that wish to analyze detailed email history, or integrate that history with their CRM database. This table provides information about the email campaigns as they are configured in Zeta (previously known as eDialog).
email_campaign_contact_detail	This table exists for MLB clubs that wish to analyze detailed email history, or integrate that history with their CRM database. This table contains details about each email sent. It has one row per email sent per customer. It will be sent incrementally, so the only data included in this will be the data received by MLBAM from Zeta since the last transmission of this file. The EMAIL_ID is a foreign key reference to the Customer data (which can be made available as a separate “Registered User Data Feed”, please ask the MLBAM Data Feeds team if interested in that as well).
email_campaign_response_detail	This table exists for MLB clubs that wish to analyze detailed email history, or integrate that history with their CRM database. This table contains details for each customer-initiated response to an email. So, if a customer opens an email, and clicks on two links in that email, there will be three rows of data in this table to represent that. The EMAIL_ID is a foreign key reference to the Customer data (which can be made available as a separate “Registered User Data Feed”, please ask the MLBAM Fanalytics team if interested in that as well). The EVENT_TYPE_ID specifies the kinds of response event that occurred. The possible values for EVENT_TYPE_ID are: “Open”: Opening the email (tracked if the user allows images to be displayed) “Click”: Clicking a link on the email “Unsub”: Unsubscribing from the email list “Spam”: Registering a spam complaint “Undel”: Undeliverable (meaning their email address is not valid or mailbox was full)
iterable_email_metadata	This table provides information about the email campaigns as they are configured in Zeta (previously known as eDialog).
iterable_email_contact	This table contains details about each email sent. It has one row per email sent per customer.
iterable_email_response	This table contains details for each customer-initiated response to an email. So, if a customer opens an email, and clicks on two links in that email, there will be three rows of data in this table to represent that. The EVENT_TYPE_ID specifies the kinds of response event that occurred. The possible values for EVENT_TYPE_ID are: “Open”: Opening the email (tracked if the user allows images to be displayed) “Click”: Clicking a link on the email “Unsub”: Unsubscribing from the email list “Spam”: Registering a spam complaint “Undel”: Undeliverable (meaning their email address is not valid or mailbox was full)
email_address_to_household_id	Each row in this table maps a customer's email address to a "household ID". A "household" can be thought of as a single user with multiple email addresses, or a group of users from the same family dwelling (e.g. a married couple) that can be treated similarly from a ticketing or sales point of view. The process that generates household IDs links email addresses together by comparing attributes like name, phone number, street address, etc. on individual interactions. For example, if a user with two email addresses, john.doe@club.example.com and jdoe31@example.net, purchases a ticket with account ID 1234567, those email addresses will be assigned to the same household ID.
optin_list_members_full	This table contains information for those fans currently opted in to receive emails on club's marketing lists (excluding sponsorship lists).
optin_list_members_incremental	This table will contain information for those fans currently opted-in to receive emails on club's marketing lists (excluding sponsorship lists). Unlike the existing “Full Opt-In List”, which simply provides a full export of all email addresses currently on the Opt-In List at the time it is run, this feed provides the changes to the list (new subscriptions, and new unsubscribes/bounces) on a daily basis.
email_campaign_agg	This table contains an aggregated view of email campaign metrics, across all three Email Service Providers (ESPs) in use at MLB: Zeta (PC7), Iterable, and ZetaHub. It contains one row per mailing/campaign, along with top-level campaign metrics such as opens and clicks. The data contained in this table comes directly from several more granular Wheelhouse Email Campaign data feeds, so it could be derived by rolling up those feeds. It is provided as a convenience to save the extra processing needed to perform these rollups and consolidate them across all three ESPs.
email_campaign_customer_list_yearly_agg	This table contains an aggregated view of email metrics for a given Customer (email address) and Query ID (MLB Email List), across 2 email service providers in use at MLB: Zeta (PC7) and Zetahub. (Iterable is excluded because most of the emails sent from it were service emails and there is no defined mlb email list id (Query ID).) It contains one row per Email Source, Email Year, Target Query ID (MLB Email List ID) and Email Address, along with top-level campaign metrics such as Total Email sent, Total Opens, Total Clicks, Total Unique Clicks etc. The data source for this table comes directly from more granular Wheelhouse Email Campaign Data (email_campaign_contact_detail, email_campaign_response_detail, email_campaign_metadata and zetahub_events). It is provided as a convenience to save the extra processing needed to perform these rollups and consolidation.
zetahub_events	This table provides event-level data from the ZetaHub email service provider platform. Unlike MLB’s other (Zeta and Iterable) email data feeds, both emails sent and emails opened/clicked are contained in a single feed file for ZetaHub. Data is present in this data set from June 15, 2020 and onwards. Reporting on emails sent prior to that date will need to be manually pulled by the Email Tech/Ops team of MLB Wheelhouse. The event types contained are: campaign_sent - Email was sent to user campaign_delivered - Email was delivered to user campaign_opened - Email was opened by user campaign_clicked - Email was clicked by user campaign_skipped- Email was not sent to user, because the user was suppressed campaign_bounced - Can be either 'hard' or 'soft' bounce indicating mail was undeliverable. Hard bounces cause the user to be opted out from all future emails. segment_unsubscribed - User clicked the 'unsubscribe' link and opted out of one particular list (query_id) campaign_unsubscribed- User clicked a global 'unsubscribe' link such as the one present in Gmail imh_reply - User replied with any number of filtered keywords (such as 'unsubscribe') - treated as a global opt-out campaign_complaint - Spam complaint by user - treated as a global opt-out.
registered_user_full	This table will contain information for those fans currently opted in to the club’s registered user (aka Newsletter / Insider) email marketing list. This table will have a very low fill rate on many fields. The data fill rate will vary by source. Those subscribers that are subscribed as a result of a ticket purchase, for example, have a higher fill rate on Name and Address information than do those fans that filled out a form where only email address is required.
registered_user_incremental	The table contains information for those fans opted in to the club’s registered user (aka Newsletter / Insider) email marketing list. Unlike the existing “Registered User Feed” which simply provides a full dump of all email addresses currently on the Registered User list at the time it is run, this feed provides the changes to the list (new subscriptions, and new unsubscribes/bounces) on a daily basis. NOTE: Instructions for Using the Incremental Registered User Feed There are two ways subscription events are captured for a user: INSERT – A user opts-in then a new record is created with SUB fields populated and UNSUB fields as NULL UPDATE – A user opts-out then UNSUB fields are updated for the existing record If the user opts-out, then CURRENT_STATE is set to “N”. If that same user later opts back into the Registered User list, then a new record will be created. In the one-time initial data dump, if the user opted-in then opted-out, it will be shown as one record rather than two records because the opt-out has occurred in the past.

Example queries

List contacts who have opened a campaign email within the last three months:

SELECT ecrd.email_id, ecrd.email_addr, ecm.email_campaign_name,
  ecm.udf2_business_unit, ecm.udf3_campaign, ecm.udf4_mlb_club,
  ecm.udf5_campaign_type, ecrd.event_type
FROM mlbwheelhouse_v2.email_campaign_metadata ecm
INNER JOIN mlbwheelhouse_v2.email_campaign_response_detail ecrd
  ON ecm.email_cell_id = ecrd.email_cell_id
  AND ecm.email_list_id = ecrd.email_list_id
WHERE ecrd.event_type = 'Open' 
  AND DATE(ecrd.event_date) >= dateadd(month,-3,CURRENT_DATE);

Get a list of all members and their information who are active and have opted in to receive emails on club's marketing lists:

SELECT DISTINCT * FROM mlbwheelhouse_v2.optin_list_members_full olmf
WHERE olmf.active_flag = 'Y';

League schedule and results

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)

Download data dictionary (Excel file)

Table descriptions

Baseball Game Master	This table contains data from every game in every season, preseason through postseason, including upcoming games.
Baseball Promotions	This table contains promotions data publicly listed on the club's website.
Baseball Team Standings Daily	This table contains daily team standings for each day during the regular season, including the previous seasons. Each record represents a team's standing on that day.

Example queries

Get the game and promotion details for games where the promotion was distributed to all fans in attendance:

SELECT DISTINCT bgm.attendance, bgm.game_description, bgm.game_status,
  bgm.venue_name, bgm.venue_city, bgm.venue_state, bgm.game_time, bp.game_pk,
  bp.distribution, bp.promotion_name, bp.offer_name 
FROM mlbwheelhouse_v2.baseball_game_master bgm
INNER JOIN mlbwheelhouse_v2.baseball_promotions bp 
  ON bgm.game_pk = bp.game_pk 
WHERE bp.distribution = 'All fans in attendance';

Get game standings for a particular game date and team:

SELECT DISTINCT btsd.team_name, btsd.game_date, btsd.win, btsd.loss,
  btsd.win_pct, btsd.last_ten, btsd.streak, btsd.home, btsd.away,
  btsd.runs, btsd.opponent_runs 
FROM  mlbwheelhouse_v2.baseball_team_standings_daily btsd 
WHERE btsd.team_name = 'Red Sox'            // edit as desired
  AND DATE(btsd.game_date) = '2021-04-08';  // edit as desired

One Signal

One Signal is a vendor which provides message delivery to customers.

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees.)

Data dictionary:

Click to expand / collapse

Table name	Column name	Data type	Notes
onesignal_ballpark_notification_events	notification_event_id	INTEGER	Primary Key - Unique identifier for this sent/clicked event
onesignal_ballpark_notification_events	event_name	STRING	A value of either 'sent' or 'clicked'
onesignal_ballpark_notification_events	email_addr	STRING	Email address tied to account of user
onesignal_ballpark_notification_events	event_timestamp	TIMESTAMP	Timestamp, in Eastern Time, of the send or click event
onesignal_ballpark_notification_events	clubtools_notification_id	INTEGER	Identifier for the notification this is associated with. Foreign Key to the Notifications Wheelhouse data set
onesignal_ballpark_notification_events	onesignal_notification_id	STRING	OneSignal's identifier for the notification this is associated with. One clubtools notification can be associated with several OneSignal notifications, since OneSignal has a limit of 2,000 targeted users per notification.
onesignal_ballpark_notification_events	batch_date	DATE
onesignal_ballpark_notification_events	team_nickname	STRING
onesignal_ballpark_notification_events	team_id	INTEGER
onesignal_ballpark_notification_events	okta_id	STRING	The MLB okta_id of the user associated with this event
onesignal_ballpark_notifications_full	clubtools_notification_id	INTEGER	PK - Unique ID for a notification created in Club Tools
onesignal_ballpark_notifications_full	created_at	TIMESTAMP	Timestamp the club tools notification was created, in Eastern time
onesignal_ballpark_notifications_full	name	STRING	Name given to the notification
onesignal_ballpark_notifications_full	title	STRING
onesignal_ballpark_notifications_full	message	STRING
onesignal_ballpark_notifications_full	url	STRING
onesignal_ballpark_notifications_full	destination_inbox	BOOLEAN	If the destination for this notification is the Ballpark inbox. Note: Messages sent for Ballpark inbox only are NOT included in this feed. A separate 'Ballpark Inbox' Wheelhouse data set will contain data on Ballpark Inbox data.
onesignal_ballpark_notifications_full	destination_push	BOOLEAN	If the destination for this notification is a Push notification
onesignal_ballpark_notifications_full	channel	STRING	The list targeted for this notification
onesignal_ballpark_notifications_full	unique_okta_targeted_count	INTEGER	Number of okta_ids targeted with this notification. This is the number of users targeted, and not the number of devices. This number will be lower than the sum of iOS & Android successful sends.
onesignal_ballpark_notifications_full	onesignal_ids	STRING	Unique OneSignal IDs of each notification sent. If there are more than 2,000 users targeted, there will be more than one value in this comma-separated list
onesignal_ballpark_notifications_full	ios_direct_clicks	INTEGER	Number of clicks from iOS
onesignal_ballpark_notifications_full	android_direct_clicks	INTEGER	Number of clicks from Android
onesignal_ballpark_notifications_full	ios_influenced_sessions	INTEGER	Number of iOS app opens that were indirectly influenced by this notification
onesignal_ballpark_notifications_full	android_influenced_sessions	INTEGER	Number of Android app opens that were indirectly influenced by this notification
onesignal_ballpark_notifications_full	ios_send_successful	INTEGER	Number sent successfully to iOS recipients
onesignal_ballpark_notifications_full	android_send_successful	INTEGER	Number sent successfully to Android recipients
onesignal_ballpark_notifications_full	ios_send_failed	INTEGER	Number sent unsuccessfully to iOS recipients
onesignal_ballpark_notifications_full	android_send_failed	INTEGER	Number sent unsuccessfully to Android recipients
onesignal_ballpark_notifications_full	completed_at	TIMESTAMP	Timestamp the push notification was completed being sent to recipients, as per OneSignal
onesignal_ballpark_notifications_full	team_nickname	STRING
onesignal_ballpark_notifications_full	team_id	INT
onesignal_ballpark_users_full	player_id	STRING	PK - OneSignal's unique identifier of a device for the purposes of receiving push notifications.
onesignal_ballpark_users_full	email_addr	STRING	Email address tied to this player_id
onesignal_ballpark_users_full	okta_id	STRING	Okta Identifier tied to this player_id
onesignal_ballpark_users_full	session_count	INTEGER	Number of times the user visited the app
onesignal_ballpark_users_full	device_os	STRING	Device Operating System Version. Example: 80 = Chrome 80, 9 = Android 9
onesignal_ballpark_users_full	device_type	STRING	Device Operating System Type. Current LOV from OneSignal: 0 = iOS 1 = Android 2 = Amazon 3 = WindowsPhone (MPNS) 4 = Chrome Apps / Extensions 5 = Chrome Web Push 6 = Windows (WNS) 7 = Safari 8 = Firefox 9 = MacOS 10 = Alexa 11 = Email
onesignal_ballpark_users_full	device_model	STRING	Device model - iOS reference can be found here
onesignal_ballpark_users_full	last_active	TIMESTAMP	Date and time the user last opened the mobile app or visited the site, in Eastern time.
onesignal_ballpark_users_full	playtime	INTEGER	Total amount of time in seconds the user had the mobile app open.
onesignal_ballpark_users_full	created_at	TIMESTAMP	Date and time the device record was created in OneSignal, in Eastern time. Mobile - first time they opened the app with OneSignal SDK. Web - first time the user subscribed to the site.
onesignal_ballpark_users_full	team_promotion_opt_in	STRING	"Y" if the user is opted in to the "Team Promotions" message channel. "N" if the user is not. "U" if it is unknown if the user is opted in - this is the case for most recent Android users. Eligibility for messaging for those users happens at message send time by OneSignal.
onesignal_ballpark_users_full	tickets_and_events_opt_in	STRING	"Y" if the user is opted in to the "Tickets and Events" message channel. "N" if the user is not. "U" if it is unknown if the user is opted in - this is the case for most recent Android users. Eligibility for messaging for those users happens at message send time by OneSignal.
onesignal_ballpark_users_full	news_and_announcements_opt_in	STRING	"Y" if the user is opted in to the "News and Announcements" message channel. "N" if the user is not. "U" if it is unknown if the user is opted in - this is the case for most recent Android users. Eligibility for messaging for those users happens at message send time by OneSignal.
onesignal_ballpark_users_full	team_nickname	STRING
onesignal_ballpark_users_full	team_id	INT

Table descriptions

onesignal_ballpark_notification_events	This table provides granular information about Notifications and events that occurred on them. Each record represents the “sent” or “clicked” event from OneSignal for Ballpark Push Notifications, along with the email address and okta_id of the user who was either sent that notification, or that clicked through that notification.
onesignal_ballpark_notifications_full	This table provides information about notifications. Each record represents a notification sent to club Ballpark users MLB's Push Notification vendor, OneSignal, via Club Tools.
onesignal_ballpark_users_full	This table provides information about users who are currently subscribed to receive club Ballpark push notifications. Each record represents the device subscribed to club Ballpark push notifications from MLB's Push Notification vendor, OneSignal.

Example query

Report on details of the selected event (e.g. 'sent') and affected users in the given date range:

SELECT
    obne.notification_event_id,
    obne.event_name,
    obne.email_addr,
    obne.event_timestamp,
    obne.clubtools_notification_id,
    obne.okta_id,
    obne.team_nickname,
    obnf.name,
    obnf.title,
    obnf.message,
    obnf.channel,
    obuf.player_id,
    obuf.device_model,
    obuf.device_type
FROM mlbwheelhouse_v2.onesignal_ballpark_notification_events obne
    LEFT JOIN mlbwheelhouse_v2.onesignal_ballpark_notifications_full obnf
        ON obne.clubtools_notification_id = obnf.clubtools_notification_id
    LEFT JOIN mlbwheelhouse_v2.onesignal_ballpark_users_full obuf
        ON obne.okta_id = obuf.okta_id
WHERE obne.event_name = 'sent'           ----event can be changed.
AND obne.event_timestamp
    BETWEEN GETDATE()-120 AND GETDATE()  ----change number of days as desired
;

Shop

Data dictionary

(All data is in the shop_sales table, so no ERD is necessary.)

Data dictionary:

Click to expand / collapse

Table name	Column name	Data type	Length	Description
shop_sales	DATA_SOURCE	string	30	Data Source name (GSI/Fanatics)
shop_sales	ORDER_DATE	Date	29
shop_sales	ORDER_NUMBER	Integer	10
shop_sales	PRODUCT_ID	Integer	10	Product is one level above SKU. For example T-Shirt (Product) will have multiple SKU for each size (S, M, L etc.)
shop_sales	SKU	Integer	10	Product SKU
shop_sales	PRODUCT_NAME	string	255	Detailed Product Name
shop_sales	PRODUCT_SHORT_DESCRIPTION	string	500	Detailed Product Description
shop_sales	PRODUCT_CATEGORY	string	30	e.g.:Collectibles, T-Shirts, Accessories, etc.
shop_sales	PRODUCT_CLASS	string	30	e.g.:Ladies, Mens, Youth, Toy, etc.
shop_sales	BRAND_NAME	string	100	e.g.: New Era, Majestic, Fanatics etc.
shop_sales	IMAGE_URL_50X50	string	500	Thumbnail Image URL of item
shop_sales	IMAGE_URL_500X500	string	500	Image URL of item
shop_sales	DESTINATION_URL	string	500	URL of item
shop_sales	DEMAND_QUANTITY	Integer	10	Number of items purchased for a SKU in an order
shop_sales	SELLING_UNIT_PRICE	Number	17	Price per unit of SKU
shop_sales	TEAM_ID	Integer	10
shop_sales	TEAM_NAME	string	100
shop_sales	EMAIL_ID	Integer	10
shop_sales	EMAIL	string	300
shop_sales	FIRST_NAME	string	100
shop_sales	LAST_NAME	string	100
shop_sales	DAYPHONE	string	100
shop_sales	EVENINGPHONE	string	100
shop_sales	MOBILEPHONE	string	100
shop_sales	ADDRESS_1	string	200
shop_sales	ADDRESS_2	string	200
shop_sales	ADDRESS_CITY	string	100
shop_sales	ADDRESS_STATE	string	20
shop_sales	ADDRESS_ZIP	string	20
shop_sales	ADDRESS_ZIP_PLUS4	string	20
shop_sales	ADDRESS_COUNTRY	string	30
shop_sales	DW_ADD_TSP	DATE	29
shop_sales	BATCH_DATE	DATE	29
shop_sales	TEAM_NICKNAME	string	20

Table description

The shop_sales table provides detailed transaction-level data for sales from MLBShop.com and Fanatics Shop. Each record represents MLB Shop demand sales transaction by SKU and contains information related to SKU and other product metadata with transaction and buyer details.

Example query

Display the total numbers of sale orders, items sold, and revenue for the past year:

SELECT
    TRUNC(ss.order_date) Order_Date,
    ss.data_source Shop,
    COUNT(DISTINCT ss.order_number) Sales_Orders,
    COUNT(DISTINCT ss.product_id) Distinct_Products_Sold,
    SUM(ss.demand_quantity) Total_ItemsSold,
    SUM(ss.selling_unit_price) total_price
FROM mlbwheelhouse_v2.shop_sales ss
WHERE ss.order_date BETWEEN GETDATE()-365 AND GETDATE()
GROUP BY TRUNC(ss.order_date), ss.data_source
ORDER BY 1,2,3;

StubHub

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees.)

Download data dictionary (Excel file)

Table descriptions

Stubhub_Listings	This table provides the complete set of listing data for the club. If the listing is removed over the course of the day (either because the listing turned into a sale, or because the seller otherwise removed or modified the listing), then the next day's snapshot will not contain that listing. This table contains a record for each day that contains a complete snapshot of all listings live at that point in time for that day. It should be thought of not as a full feed (in which case the data is simply replaced each day), but as a type of incremental feed where you get duplicate rows from day to day of the listing for as long it stays live and does not change. The primary keys for this table are a combination of the fields “Snapshot_date”, “Listing_ID”, and “Batch_date”
Stubhub_Details_Net	The table provides detailed StubHub transaction data. It provides more columns than the now-deprecated StubHub Club Data Feed, and thus it is considered the successor to that feed. This feed is a net, incremental data feed, containing one or more rows per StubHub transaction (TICKET_ID). The primary keys for this table are a combination of the fields “Ticket_ID” and “Batch_date”.
stubhub_details_net_pivot	The Stubhub table Stubhub_Listings come with 1 record per listing/transaction which will contain 1 or more seats per entry (e.g. 2,3,4). There is an option to pivot the data into new table stubhub_listings_pivot(pivot table per main table) so this table contains a record per seat per listing/transaction.
stubhub_listings_pivot	The Stubhub table Stubhub_Details_Net come with 1 record per listing/transaction which will contain 1 or more seats per entry (e.g. 2,3,4). There is an option to pivot the data into new table stubhub_details_net_pivot(pivot table per main table) so this table contains a record per seat per listing/transaction.

Example queries

See all the listings for a particular ticket along with seller information:

SELECT *
FROM mlbwheelhouse_v2.stubhub_listings sl
WHERE sl.listing_id = '1234567890';

Get the buyer information for a particular listing:

SELECT *
FROM mlbwheelhouse_v2.stubhub_details_net sdn
WHERE sdn.stubhub_listing_id = '1234567890';

Survey (Qualtrics)

Qualtrics is a vendor that provides extensive tools for surveying customers. This section applies only to Qualtrics data provided via MLB Wheelhouse—KORE also provides a stand-alone Qualtrics integration.

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)

Note: To keep table sizes managable, a new table is created annually for Voice of the Consumer post-attendance and post-purchase surveys. Replace YYYY in the table names with the relevant year (season) for your query.

Download data dictionary (Excel file)

Table description

`qualtrics_market_tracker_full`	This table provides all the Baseball Sentiment Tracker (MLB Audiences) data that has been collected.
`qualtrics_voc_post_attendance_YYYY_full`	This table provides all the post-attendance survey data that has been collected for the club as part of MLB's Voice of the Consumer program. Each record represents the post attendance survey information with customer experience comments, email id and qualtrics id.
`qualtrics_voc_post_purchase_YYYY_full`	This table provides all the post-purchase survey data that has been collected for the club as part of MLB's Voice of the Consumer program.Each record represents post purchase survey information and customer experience comments with qualtrics id and email id for year 2021.
`qualtrics_surveys_full`	This table provides responses and metadata for all surveys in the clubs' Qualtrics instance with the exception of VOC data. Each record represents the information about the survey created its status, name and question count.
`qualtrics_questions_full`	This table provides responses and metadata for all surveys in the clubs' Qualtrics instance. Each record represents the list of questions and its type in the survey.
`qualtrics_responses_full`	This table provides responses and metadata for all surveys in the clubs' Qualtrics instance with the exception of VOC data. Each record represents specific answer to a question from a specific survey. There will be one or more responses for a question if the question asks for multiple answers or it is an open text question which gets sentiment analysis calculated.

Example queries

Get the data on surveys posted and responses based on survey types:

SELECT
   qsf.survey_id,qsf.survey_name,
   qqf.question_type ,qqf.question_description ,
   qrf.element_name ,qrf.element_value 
     FROM mlbwheelhouse_v2.qualtrics_surveys_full qsf
        LEFT JOIN mlbwheelhouse_v2.qualtrics_questions_full qqf
        ON  qsf.survey_id = qqf.survey_id 
       LEFT JOIN mlbwheelhouse_v2.qualtrics_responses_full qrf
        ON  qsf.survey_id = qrf.survey_id ;

Fetch the survey data for post-purchase customer experience who purchased tickets in both 2021 and 2022:

SELECT mlbwheelhouse_v2.qualtrics_voc_post_purchase_2021_full.email,
       mlbwheelhouse_v2.qualtrics_voc_post_purchase_2021_full.preseason_ot
FROM
     mlbwheelhouse_v2.qualtrics_voc_post_purchase_2021_full
WHERE EXISTS (SELECT 1 FROM mlbwheelhouse_v2.qualtrics_voc_post_purchase_2022_full
              WHERE 
              mlbwheelhouse_v2.qualtrics_voc_post_purchase_2021_full.email = 
              mlbwheelhouse_v2.qualtrics_voc_post_purchase_2022_full.email);