Fan Factors Data Dictionary

1 ro.vw_Fan_Factors
2 ro.vw_Fan_Factors_Stage_Change_Capture

ro.vw_Fan_Factors

Description: This table is comprised of values that show what stage a Fan is in their journey, along with supporting fields that explain their touchpoints and behaviors for 5 data sources (ticketing, email, merchandise, CRM, and donations).

Update Frequency: Monthly

	Field Name	Field Position	Field Type	Description	SQL (Note: This is not exactly how it is calculated but an explanation for our advanced users)

	Field Name	Field Position	Field Type	Description	SQL (Note: This is not exactly how it is calculated but an explanation for our advanced users)
1	FAN_Primary_Key	1	uniqueidentifier	Primary Key
2	FAN_SSB_CRMSYSTEM_CONTACT_ID	2	nvarchar(50)
3	FAN_Email_Marketable	3	y/n	Record is active in email	`UPDATE fan SET fan.FAN_email_marketable = CASE WHEN d.EmailPrimaryIsCleanStatus =''valid'' THEN ''Y'' ELSE ''N'' END FROM dbo.DimCustomerSSBID ssbid with (nolock) JOIN dbo.DimCustomer d with (nolock) ON d.DimCustomerId = ssbid.DimCustomerId JOIN ods.fan_factors fan with (nolock) ON ssbid.SSB_CRMSYSTEM_CONTACT_ID = fan.FAN_SSB_CRMSYSTEM_CONTACT_ID WHERE d.IsDeleted =0 AND ssbid.SSB_CRMSYSTEM_PRIMARY_FLAG = 1`
4	FAN_Phone_Marketable	4	y/n	Clean phone number	`UPDATE fan SET fan.FAN_phone_marketable = CASE WHEN d.PhonePrimaryIsCleanStatus =''valid'' THEN ''Y'' ELSE ''N'' END FROM dbo.DimCustomerSSBID ssbid with (nolock) JOIN dbo.DimCustomer d with (nolock) ON d.DimCustomerId = ssbid.DimCustomerId JOIN ods.fan_factors fan with (nolock) ON ssbid.SSB_CRMSYSTEM_CONTACT_ID = fan.FAN_SSB_CRMSYSTEM_CONTACT_ID WHERE d.IsDeleted =0 AND d.PhonePrimary IS NOT NULL AND ssbid.SSB_CRMSYSTEM_PRIMARY_FLAG = 1`
5	FAN_postal_marketable	5	y/n	Record has valid postal address	`UPDATE fan SET fan.FAN_postal_marketable = CASE WHEN d.AddressPrimaryIsCleanStatus =''valid'' THEN ''Y'' ELSE ''N'' END FROM dbo.DimCustomerSSBID ssbid with (nolock) JOIN dbo.DimCustomer d with (nolock) ON d.DimCustomerId = ssbid.DimCustomerId JOIN ods.fan_factors fan with (nolock) ON ssbid.SSB_CRMSYSTEM_CONTACT_ID = fan.FAN_SSB_CRMSYSTEM_CONTACT_ID WHERE d.IsDeleted =0 AND d.AddressPrimaryIsCleanStatus IS NOT NULL AND ssbid.SSB_CRMSYSTEM_PRIMARY_FLAG = 1`
6	FAN_createddate	6	date	This is the MIN(Create Date) for the customer in DimCustomer - ie the first time we’ve every known to have seen them regardless of source
7	FAN_updateddate	7	date	This is the MAX(Update Date) for the customer in DimCustomer - ie the last time any record source was updated
8	TICKETING_current yearSTM	8	y/n	If the have a transaction marked as a seasonticket for current season(s)
9	TICKETING_prevseasonSTM	9	y/n	If the have a transaction marked as a seasonticket for last season(s)
10	TICKETING_STM_Tenure	10	# of season	How many seasons they have bought a season ticket
11	TICKETING_Games_Scanned	11	#	TM Distinct count of event codes based on ssb crmsystem contact id and season name from fact ticket seat where the record has isattended = 1 PAC Distinct count of event codes based on ssb crmsystem contact id and season name from fact ticket seat where the record has isattended = 1	TM PAC
12	TICKETING_Tickets_Scanned	12	%	Percentage of tickets scanned in - (Ticket Utilization)	PAC
13	TICKETING_Games_Forwarded	13	#	TM number of distinct dim event ids, and sum of qty seats by dim ticket customer where the tm activity = 'F' PAC v3 model is not currently capturing forwards/transfers	TM PAC
14	TICKETING_Mobile_Scanned_Games	14	#	TM dim ticket customer id records when is mobile = 1 PAC v3 model is not currently capturing mobile scans	TM PAC
15	TICKETING_Games_Sold_Secondary	15	#	TM count of distinct dim event ids and sum of qty seats based on dim ticket customer id where the activity code is ES meaning resale PAC count of distinct events where dimticketcustomer_resold_recipient is not null	TM PAC
16	TICKETING_Games_Purchased_Secondary	16	#	TM Same code as above but with dim ticket customer id for the recipient	TM PAC
17	TICKETING_First_Known_Ticket_Purchase	17	date	TM min sale date from fact ticket sales PAC min sale date from fact ticket sales	TM PAC
18	TICKETING_Last_Ticket_Purchase_Date	18	date	TM max sale date from fact ticket sales PAC max sale date from fact ticket sales	TM PAC
19	TICKETING_Ticket_Total_Spend	19	$$$	TM Sum of total revenue from fact ticket sales PAC Sum of total revenue from fact ticket sales	TM PAC
20	DONATION_Last_Donation	20	date	PAC 8 This is the last gift they have donated where payment amount does not = 0 Ticketmaster The last date they donated
21	DONATION_First_Donation	21	Date	PAC 8 First gift they have donated where payment amount does not = 0 Ticketmaster The last date they donated
22	DONATION_Max_Donation_Amount	22	$$$	PAC 8 Max donation amount where payment amount does not = 0 Ticketmaster MAX donation amount
23	DONATION_Total_Donation_Amount	23	$$$	PAC 8 Sum of donations where payment amount does not = 0 Ticketmaster Sum of all donations
24	DONATION_Current_Donor	24	y/n	PAC 8 If they have a donatoin in the current year, then YES Ticketmaster If they are a a donor this year, and if so, how much have they donated.
25	DONATION_Current_Donation_Amount	25	$$$	PAC 8 If the have a current donation in the current year, then sum all current year donations. Ticketmaster If the have a current donation in the current year, then sum all current year donations.
26	EMAIL_Valid_EmailAddress	26	y/n	Is the email address is cleaned (status like ‘% valid %’)
27	EMAIL_Last_email_sent	27	date	max email sent date
28	EMAIL_Last_email_opened	28	date	max opened email
29	EMAIL_Email_open_count	29	#	distinct count of emails opened
30	EMAIL_Is_Opted_In	30	y/n
31	MERCH_Date_of_last_purchase	31	Date	This code answers all the listed questions for merchandise, if the client is using FANATICS
32	MERCH_Quantity_30Days	32	#		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
33	MERCH_TotalSpent_30Days	33	$$$		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
34	MERCH_Quantity_90Days	34	#		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
35	MERCH_TotalSpent_90Days	35	$$$		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
36	MERCH_Quantity_365Days	36	#		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
37	MERCH_TotalSpent_365Days	37	$$$		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
38	MERCH_Quantity_Lifetime	38	#		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
39	MERCH_TotalSpent_Lifetime	39	$$$		ALL CODE IS IN ABOVE SNIPPER FOR FANATICS
40	CRM_Primary_CRM_ID	40	varchar	Discovery specific clients
41	CRM_Account_Owner	41	varchar
42	CRM_Last_Activity_Date	42	date	Discovery Specific clients, last and next activity dates by ssb crm system contact ids	CRM
43	CRM_Has_Open_Opportunity	43	y/n	Discovery specific clients. 'Y' indicator for ssb crmsystem contact IDs that have at least 1 open pipelineobject id
44	CRM_Opportunity_Last_Modified Date	44	date	Discovery clients specific. Shows last modified date regardless of pipeline status, can re comment in the ps.status = ‘open’ if we are looking for only open opportunities.
45	Total_Lifetime_Value	45	$$$
46	Recency_Score	46	#
47	Frequency_Score	47	#
48	Monetary_Score	48	#
49	RFM_Score		#
50	Fan_Journey_Stage	49	#	Detached - Do not currently identify with sports, focused on other options and interests, have little to no emotional connection with the community sports program, have encountered sports, but found it less than satisfying or exciting Stage criteria - Fan has no ticketing, donation, or merchandise transactions and no email opens, or if they do have an email open, its over 4 years old. Observer - Arguably the largest group of sports consumers, primary audience for media/digital consumption, not attending events, “superfan” without a propensity to spend or a cape Stage criteria - Fan has no ticketing or merchandise transactions, but has an email open in the last 4 years Affiliated - People who will spend $ to experience a premier sporting event live, most sought after fan, must enhance the development of the emotional connection to take the deepen their engagement Stage criteria - Fan has a ticketing, donation, or merchandise transaction Invested - Want tangible interactive experiences, discontented with being “part of the crowd” they want to interact with players/team, largely compiled of ticket purchasers, participation in camps, VIP player meet and greets, etc. Stage criteria - Fan has more than 1 ticketing transaction, but has never been a Season Ticket holder. Committed - Desire to be on the inside, higher level of commitment, donors, sponsors, premium seating holders, corporate entertainment Stage criteria - Fan more than 1 transaction for more than 1 season Devoted - Most emotionally evolved of all fans, core identity is built around the affiliation with the team, psychologically intertwined, live and die with every win or loss, player trade, or management decision Stage criteria - Fan has had 5 or more years of Season Ticket or Suite ticket transactions.
51	Fan_Parent_Group	50	#	Avid, Casual, DIsengaged
52	ETL_UpdatedDate	51	date

ro.vw_Fan_Factors_Stage_Change_Capture

Description: This view looks at the Fan and the movement of their Fan Journey Stage (forward or backwards) and allows you to see when they changed stages and RFM scores, which enables you to look into what caused them to move through their journey.

Update Frequency: Monthly

Field Name	Field Position	Field Type	Description

Field Name	Field Position	Field Type	Description
Fan_Factor_History_ID	1	uniqueidentifier	Primary Key
FAN_SSB_CRMSYSTEM_CONTACT_ID	2	nvarchar	CustomerID
Recency_Score	3	#
Frequency_Score	4	#
Monetary_Score	5	#
RFM_Score	6	#
Fan_Journey_Stage	7	nvarchar
Fan_Parent_Group	8	nvarchar
AS_of_Date	9	Datetime	Datetime of when they switch from that stage to another