Derived Variable and Aggregates

Individual Aggregate and Derived Variables

The Ascent360 Customer Data Platform has the ability to store and create variables that aggregate data from other fields.  These aggregated fields are at the Individual (or Person) level which means that each individual in the database has about 55 fields that are "aggregates" about them.  

This is best explained through example:  Imagine if John Smith buys twice from your store and once from your eCommerce site.  The first purchase was for $50 on January 1st 2018, the second was for $100 on January 15th 2018 and the third purchase was for $200 on December 1, 2019.  In aggregate John has made 3 purchases.  His Total spend is $350 and the last time he made a purchase was on December 1 2018.  So John Smith would have the following aggregates:

AggregateValue
Total Lifetime Spend$350
Count of Purchases3
Last Purchase Date01/01/2018
First Purchase Date12/01/2018

Definition:

Customer Aggregate:  A customer aggregate is a derived field that is built at the customer level based upon other data related to that customer.  These aggregates are at the customer level so when we talk about a “first purchase date” for example, we define this for the customer rather than at some other level, such as the client or the household.

List of Standard Derived Variables and Aggregates

Below is a list of standard Derived Variables and Aggregates.  Most of these will be implemented "Out of the Box" with every customer implementation.  Some of these are industry specific such as "Count of Lift Scans This Season", which is exclusive to resorts that scan passes.

Database Column NameAlias Name (Friendly Name)Description and NotesData Type
Mag_Computed_Days_ActiveDays IndividualNumber of Days between Individual Create Date and Today.Integer or Number
Mag_Computed_Days_CustomerDays CustomerThis is the difference between the customer create date and today in days.Integer or Number
Mag_Computed_Days_Customer_LapseDays Customer LapseDays since the Last Implied Purchase Date.Integer or Number
Mag_Computed_Days_Since_Last_PurchaseDays Since Last PurchaseThe number of days since the Last Financial Purchase Date including self-reported dollars.Integer or Number
Mag_Computed_Spend_Avg_Order_ValueAverage Order ValueThis is the Average Order Value of transactions including self-reported dollars.numeric
Mag_Computed_Spend_Avg_Order_Value_QLAverage Order Value VerifiedThis is the Average Order Value of transactions of all verfied transactions.numeric
Mag_Custom_Count_LiftScan_This_SeasonCount of Lift Scans This SeasonThis is the total count of lift scans for a customer in the current season.Integer or Number
Mag_Custom_Count_Specific_ProductCount Custom Product PurchasedCount Custom Product Purchased.Integer or Number
Mag_Custom_Lapsed_CustomerLapsed CustomerThis is a flag set to yes if the days since last purchase > 730 (2 years).Text
Mag_Custom_Rating_Likely_RecommendLikelihood to RecommendThis is the zero to 10 value of likely to recommend.Integer or Number
Mag_Custom_Text_Store_Last_ShoppedLast StoreLast store that the customer shopped.Text
Mag_Custom_Text_Store_PreferredPreferred StoreStore that the customer prefers shopping at determined by the most number of purchases at a store.Text
Mag_CustomDate6Birthday This YearAllows query of birthdays occurring this year.Date or Date with Timestamp
Mag_customDecimal6RFM additionThe addition of recency-frequency-monetary valuesInteger or Number
Mag_Customer_Create_DateCustomer Create DateThe first date at which an individual became a Customer with a financial purchase.Date or Date with Timestamp
Mag_Customer_Source_TextFirst Customer SourceDescription of customer source.Text
Mag_Customer_StatusCustomer StatusStatus of either customer or prospect using financial transaction and implied purchases.Text
Mag_CustomText5Resort LifestageLifestage person has because of their age group. Defined as the following:
  • 17 or Younger: age 17 or younger
  • Younger years: age 18 to 34
  • Family years: age 35 to 50
  • Mature years: age 51 to 99
  • Unknown: No age given
Text
Mag_CustomText6Resort Customer TypeResort customer type based off season pass bought in 3 yearsnText
Mag_Email_First_Delivered_DateFirst Email Delivered DateThis is the date at which AscentCRM first sent email by client to the specific individual.Date or Date with Timestamp
Mag_Email_Last_Clicked_DateLast Email Click DateThis is the date at which an email was last clicked by the individual.Date or Date with Timestamp
Mag_Email_Last_Delivered_DateLast Email Delivered DateThis is the date at which an email was sent to the individual.Date or Date with Timestamp
Mag_Email_Last_Opened_DateLast Email Opened DateThis is the date at which an email was last opened by the individual.Date or Date with Timestamp
Mag_Email_Number_Clicked_365daysNumber of Emails Clicked - Past 12 MonthsThis is the total number of emails Clicked by an individual over the past 12 months.Integer or Number
Mag_Email_Number_Clicked_LifetimeNumber of Emails Clicked - LifetimeThe total number of emails clicked by an individual recorded by AscentCRM.Integer or Number
Mag_Email_Number_Opened_365daysNumber of Emails Opened - Past 12 MonthsThis is the total number of emails Opened on by an individual over the past 12 months.Integer or Number
Mag_Email_Number_Opened_LifetimeNumber of Emails Opened - LifetimeThe total number of emails opened on by an individual recorded by AscentCRM.Integer or Number
Mag_Email_Number_Sent_365daysNumber of Emails Sent - Past 12 MonthsThis is the total number of emails sent to an individual over the past 12 months.Integer or Number
Mag_Email_Number_Sent_LifetimeNumber of Emails Sent LifetimeThe total number of emails sent to an individual recorded by AscentCRM.Integer or Number
Mag_Email_Unsubscribed_DateUnsubscribe Date from EmailThis is the date at which the individual unsubscribed from email for all contact from any source.Date or Date with Timestamp
Mag_Individual_Create_DateIndividual Create DateThis is the first date at which an individual was created in the database.Date or Date with Timestamp
Mag_Individual_Source_TextFirst Source
Text
Mag_Last_Interaction_DateLast Interaction DateThe last date at which the individual has interacted with our client through any means.Date or Date with Timestamp
Mag_Last_Source_DateLast Source DateLast Source DateDate or Date with Timestamp
Mag_Last_Source_TextLast SourceDescription of last source.Text
Mag_Purchase_Last_Date_FinancialLast Financial Purchase DateThis is the date at which the customer last purchased something including self-reported purchases.Date or Date with Timestamp
Mag_Purchase_Last_Date_Financial_QLLast Purchase Date VerifiedThis is the number of days between the verified transactional Last Purchase Date and Today.Date or Date with Timestamp
Mag_Purchase_Last_Date_ImpliedLast Implied Purchase DateSimilar to the Last Financial Purchase Date, but includes implied purchases.Date or Date with Timestamp
Mag_Purchase_NumberTransactions_FinancialNumber of Financial PurchasesThis is a count of all purchases and self-reported purchases.Integer or Number
Mag_Purchase_NumberTransactions_Financial_QLNumber Transactions VerifiedThis is the number of transactions of all verfied transactions.Integer or Number
Mag_Purchase_NumberTransactions_ImpliedNumber of Implied PurchasesNumber of Implied  PurchasesInteger or Number
Mag_Score_FrequencyFrequency Score20% buckets of frequent customers.Integer or Number
Mag_Score_MonetaryMonetary Score20% bucket of top spend.Integer or Number
Mag_Score_RecencyRecency Score20% buckets of recent customers.Integer or Number
Mag_Score_RFMRFM ScoreRFM scores in one field.Integer or Number
Mag_Spend_30daysSpend in the Past 30 DaysTotal Spent by the customer over  the past 30 days.numeric
Mag_Spend_365daysSpend in Past 12 MonthsTotal Spent by the customer over the past 12 months.numeric
Mag_Spend_60daysSpend in the Past 60 DaysTotal Spent by the customer over the past 60 days.numeric
Mag_Spend_90daysSpend in the Past 90 DaysTotal Spent by the customer over the past 90 days.numeric
Mag_Spend_Current_Calendar_YearSpend in Current Calendar Year Total Spent by the customer in the current calendar year.numeric
Mag_Spend_LifetimeTotal Spent LifetimeThis is the sum of all known spend from all sources including self-reported purchases.numeric
Mag_Spend_Lifetime_QLTotal Spent Lifetime VerifiedThis is the sum of dollars spent of all verfied transactions.numeric
Mag_Spend_Prior_Calendar_YearSpend in Prior Calendar Year Total Spent by the customer previous calendar year.numeric