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
Major League Soccer (MLS) provides teams with access to MLS Club Feed, their internal data warehouse. It contains details on customers, merchandise, ticketing, membership, demographics, and more. With this integration, MLS clubs can pull key data sets from the MLS Club Feed into KORE DWA.
During the initial setup, KORE pulls historical data via SFTP. Afterward, daily incremental updates (overnight) make use of the MLS Club Feed API. The MLS Club Feed itself is only refreshed once daily (evenings), and its timestamps are in EST (not UTC).
To enable this integration, contact your KORE Success Manager.
ERD and data dictionary
(This information is provided on a best-effort basis without guarantees. For clarity, some column names may be omitted from this ERD.)
Note: Tables with pink headers represent the Customer object. Tables with green headers represent the Demographic object.
Download data dictionary (Excel file)
Additional files provided by the league officer are available at the bottom of this page.
Table descriptions
Customer
This data set has been split into following tables: customer, customer_attr and customer_team_engagement.
These tables return the list of customer/fan attributes. Each record represents a single fan and their details. Key information includes fan’s personal details like the first name, last name, address, contact details, birth date, fans club ownership, fan’s location distance from club stadium, data sources flags, and marketing option flag (e.g. EA Sports, Gygia, Fantasy, MLS Store, MLS/SUM Ticketing, Ad-Hoc, MLS App, eCal, and Newsletter Subscription). All the records in these tables are identified by ‘iq_customer_key’ as the primary key which represents MLS Customer Golden Record ID.
Membership
This table returns the list of club’s customer membership records consisting of Fantasy, Gygia, EA Sport, ExactTarget (Salesforce Marketing Cloud), Club AdHoc Data Append, MLS Live, and ECal records. Each record represents a single fan record and its membership details. Key information includes customer’s email id, favorite team, source of subscriptions, source of campaign, subscription start date/end date. subscription id, amount, order number, subscription payment date, cancellation date, etc. All the records in the table are identified by ‘iq_customer_key’ which is referred from the Customer tables and represents MLS Customer Golden Record ID.
Demographic
This data set has been split into following tables: demographic_ah1_mb_coa, demographic_ah1_mb_dpv, demographic_ah1_mb_mailing_address, demographic_ah1_mb_zip4, demographic_address, demographic_financial, demographic_home, demographic_medical, demographic_models, demographic_person, demographic_preference_flag, demographic_ce_lhi_household, demographic_individual, demographic_marketing, demographic_selected_individual
These tables return the list of customer/fan demographic information. Each record represents a single customer record and its details. Key information includes customer’s demographic details like the individual, household, address, contact, medical, financial, marketing and other preferences, etc. All the records in these tables are identified by ‘iq_customer_key’ which is referred from the Customer tables and represents MLS Customer Golden Record ID.
Merchandise
This table returns the club’s Point of Sales data from the MLS Store WebsiteClub’s Point of Sales data from the MLS Store Website. Each record represents a single product in the order and its details. Key information includes customer’s personal details, customer id, billing address, shipping address, first order date, last order date, number of orders, order id, order details like sku's, product details, cost, tax, payment details, etc. All the records in these tables are identified by ‘iq_customer_key’ which is referred from the Customer tables and represents MLS Customer Golden Record ID.
Ticketing
This table returns the Club’s MLS and SUM ticketing data. Each record represents a seat number that is booked by the ticket and its details. Key information includes customer information, game events, seat information, stadium, ticket price values, and attendance. The "SUM_EventCode" tracks the SUM ticketing data[non MLS club event]. All the records in the table are identified by ‘iq_customer_key’ which is referred from the Customer tables and represents MLS Customer Golden Record ID.
Note: A single ticket may be duplicated in the dataset when it changes customer, if that occurs you must use the DTMODIFIED date to determine the latest record.
Example queries
List personal data, preferences, opt-ins, ticketing, and merchandising data for customers.
SELECT DISTINCT cd.iq_customer_key Customer_ID,
cd.full_name, cd.email_address, cd.birth_date, cd.phone_number,
COUNT (DISTINCT m.subscriber_id) Number_Of_Subscriptions,
cda.adhoc, cda.mlsstore_purchase, cda.clubticketpurchase,
cda.ea_fifa, cda.ecal, cda.adhoc_optinflag, cda.marketing_optin_flag,
COUNT (DISTINCT t.event_id) Number_of_Events,
COUNT (DISTINCT t.order_num) Ticket_Order_Count,
COUNT (DISTINCT t.seat_number) Seat_Count,
COUNT (DISTINCT m2.sku) Number_of_Products_Purchased
FROM mlsclubfeed_v2.customer cd
LEFT JOIN mlsclubfeed_v2.customer_attr cda
ON cd.iq_customer_key = cda.iq_customer_key
LEFT JOIN mlsclubfeed_v2.customer_team_engagement cdte
ON cd.iq_customer_key = cdte.iq_customer_key
LEFT JOIN mlsclubfeed_v2.membership m
ON cd.iq_customer_key = m.iq_customer_key
LEFT JOIN mlsclubfeed_v2.ticketing t
ON cd.iq_customer_key = t.iq_customer_key
LEFT JOIN mlsclubfeed_v2.merchandise m2
ON cd.iq_customer_key = m2.iq_customer_key
GROUP BY cd.iq_customer_key, cd.full_name, cd.email_address, cd.birth_date,
cd.phone_number, cda.adhoc, cda.mlsstore_purchase, cda.clubticketpurchase,
cda.ea_fifa, cda.ecal, cda.adhoc_optinflag, cda.marketing_optin_flag
ORDER BY 1,2,3;