Understanding a Constituent's Overview Information in CRM360®

The Overview section of the CRM360 screen (highlighted below) displays quantitative and qualitative data that should be helpful to most Personify users when they first pull up a constituent record. Organizations that wish to remove a screen element from this screen can do so via the Application Designer. Please note that if an element is removed, you may also want to update the stored procedure to increase performance.

The information displayed in the Overview section can be refreshed to display the most up-to-date data for the constituent by clicking the Update button. This button runs the SQL job "ADD_CRM360_DATA" for all the stored procedures discussed below. For more information, please see Refreshing the Overview and Involvement Sections in CRM360.

Balance Due (Current / Total)

·            Current:
The first number indicates the total balance due (as described below) minus Type 9 deferred payments. This number does NOT include proforma orders (including renewals). The Current Balance Due is updated by the usp_ConstituentOverview_CurrentBalance stored procedure.

·            Total:
The second number indicates the sum of Type 1, 2, 3, 4, 5, and 6 transaction types (receipt, voucher, transfer, sales, write-off, and adjustment) on active, invoiced order lines where the customer is the bill-to customer, regardless of the payor. This number does NOT include proforma orders (including renewals). The Total Balance Due is updated by the usp_ConstituentOverview_TotalBalance stored procedure.

The Balance Due can be a negative amount (credit).

Spending (YTD(Jan) / Lifetime)

·            YTD (Jan):
Spending is the sum of Order_Detail.BASE_TOTAL_AMOUNT of active order lines for all subsystem orders belonging to organization unit of logged in user, except FND pledges. Spending for pledges calculated either on order or on payment based on the org unit parameter that defines when a hard credit is given for pledges. For individuals, it includes amount where customer is the bill-to or the order line ship-to. For companies, it includes amount where company is the bill-to or the order line ship-to plus spending amount of employees. The first number indicates the constituent's year-to-date (YTD) spending based on the CRM360_YTD__START_MONTH application parameter. This month is displayed in parentheses to the right of "YTD". If the constituent open in CRM360 is a company, the number also includes orders placed by corporate subsidiaries and employees based on the date the order was placed. The YTD spending is updated by the usp_ConstituentOverview_LifetimeEmployeeTans stored procedure.

·            Lifetime:
The second number indicates the constituent's lifetime spending, which is the sum of Order_Detail.BASE_TOTAL_AMOUNT for all active order lines for all subsystem orders belonging to the org unit of logged in user, except FND pledges. Spending for pledges is calculated either on order or on payment based on the organization unit parameter that defines whether hard credits are given to pledges on order creation or payment. The portion of multi-year pledges scheduled to be paid in the future will not be included in lifetime calculations if the organization gives hard credit for pledges on payment. For individuals, it includes the amount where customer is the bill-to or the order line ship-to. For companies, it includes the amount where company is the bill-to or the order line ship-to plus spending amount of employees. The Lifetime Spending is updated by the usp_ConstituentOverview_LifetimeTans stored procedure. The amount of a multi-year pledge is included in YTD calculations in the year the pledge amount will be paid.  For example, if a $10,000 pledge is to be paid in 24 installments, $5,000 will be included in the first year and $5,000 will be included in the second year.

This field includes related constituents’ orders so you can understand a company’s importance, even if orders are not directly tied to the company (but instead reside at the employee level).

Score (YTD(Jan) / Lifetime)

Organizations can modify variables but cannot modify the mapping to the database via the stored procedure because each stored procedure updates a distinct database field. The score displayed in CRM360 and stored for Universe reporting is the sum of those individual fields.

Personify does not come with every rule pre-populated. Rather, the metrics that will be used the most by organizations. To enable every rule, regardless of whether or not it applies, would significantly increase the organization’s load. If additional fields are desired (that exceed what is available as placeholders), then the database structure will need to change. See "Creating Additional Score Columns in CRM360" below.

Base calculations are provided as a starting point for clients. It is expected that this logic will be modified to meet your organization's needs. Any changes to the scoring rule will require database coding (modifying Stored Procedures). The stored procedure code includes commenting so that users with sufficient skills can easily customize it. See "Creating Additional Score Columns in CRM360" below on how to add customizations.

Creating Additional Score Columns in CRM360

The rationale for weighting different product types is based on level of engagement. The fundamental difference between meetings and other products, for instance, is the time and energy that the person spends to engage with the organization. Base logic should be reviewed with your Account Specialist to determine which metrics are meaningful to your organization, how many points should be assigned, and etc. For instance, 0.02 points can be assigned for each dollar spent on meetings.

The time and effort it takes to attend a meeting shows greater commitment to the organization that just paying – e.g., buying a book.
All products in the MTG subsystem will be considered – including extra badges, standalone sessions, webinars, and sponsorships – so sponsorships will be double counted (separate metric for all sponsorship).

·            Order need to be active but an event does not need to have started yet.

·            0.01 point for each dollar spent on inventory products (invoiced order lines).

·            Order need to be active or backordered but does not need to be shipped yet.

·            0.02 point for each increment of $50 contributed on an active gift order line that is not a pledge (i.e., where PRODUCT_TYPE_CODE not in (‘PLEDGE’,’MEMO’).

A higher value is assigned, as there is little cost to the organization in producing and the donor usually does not get a product in return, which corresponds to higher engagement. For example, 0.02 point for each increment of $50 contributed on a pledge.

·            Order Line Amount or the Payment Amount: For active, fundraising pledges, we need to first identify whether the spending and scores should be based on the order line amount or the payment amount. We call this “When to Count Pledge Amounts”:

·            Multi-Year Pledges: For multi-year pledges, for current year scores, and current year spending totals, we only want to include pledge amounts scheduled to be paid in the current year. For instance, you can set 0.02 points for each dollar spent on subscriptions and/or 0.02 points for dollar spent on exhibits.

Subscriptions are probably a higher value for most organizations as they show an enduring value and recurring revenue. Order need to be active but issues do not need to be fulfilled yet.

All XBT subsystem products will be considered – booths, registrations/badges, sponsorships – so sponsorships will be double counted (separate metric for all sponsorships).

Order need to be active but event date does not need to be reached yet. For instance, you can set 0.02 points for each dollar spent on advertising.

All ADV subsystem products will be considered – insertion orders, classified ads, web banner ads, and sponsorships – so sponsorships will be double-counted (separate metric for all sponsorships).

Order need to be active but does not need to be fulfilled yet.

·            0.01 points for each dollar spent on all other transactions (for subsystems not mentioned above) (PCK, MISC, CRT, TRN, and FAC) Calculate by subsystem – if clients wish to be more granular (e.g., by product type), then they can update the SP. For example, if they wish to give fewer points to meetings that do not require a physical presence (e.g., webinars).

·            0.02 point for every dollar on sponsorships.

·            Includes ADV, MTG, or XBT.

o           DV Product Type = SPONSORSHIP.

o           XBT Product Type = SPONSORSHIP.

o           MTG Product Type = F (Sponsorship/Other Fees).

·            This is in addition to the points for orders in these subsystems.

·            Order need to be active but does not need to be fulfilled yet.

·            1.00 point for every year served on a committee.

·            Based on a distinct list of CMT position held on each committee (e.g., if an individual has a current, active position of MEMBER on two different committees, the individual would get 2 points).

·            Includes past service.

·            Based on the begin-end date ranges (if end date is null, assume that the end date is current year). If end date year is greater than the current year, assume the current year is the end date.

·            Each year and part of a year is counted (if subsequent terms, count per committee/position).

·            For lifetime score, count all service. For YTD score, count just positions where the end date is null or in the future.

Clients that wish to assign different points based on committee or position can update the SP accordingly. For instance, they can potentially leverage one of the Option fields in System Types and Codes per COMM Position Type to assign a weight (e.g., Chairperson earns more points than Member). Note that this change would impact points assigned regardless of committee. For instance, they can potentially leverage the VIP Committee Priority field to assign a weight (e.g., the BOD should earn more than a task force).

·            1.00 point per year of membership (Note: customers only get points for years of membership, not for membership spending.)

·            Must be in the primary member group (note that the new member group flag has no impact, as it only applies to non-primary membership)

·            Lifetime Score: Select the earliest membership order in the database for the customer that belongs to the primary member group and calculate the number of years between the initial begin date and cycle end date. To that, add the number of years for each subsequent active membership order by calculating the number of years between cycle begin date and cycle end date (partial year membership counts gets points for a full year).

·            Detailed Logic: Select the minimum active membership order where Order_Detail.LINE_STATUS_CODE = ‘A’ and SUBSYSTEM = ‘MBR’ and Order_Detail.FULFILL_STATUS_CODE not in (‘D’,’S’) and Order_Detail.ORG_ID = Mbr_Group.ORG_ID and Order_Detail.ORG_UNIT_ID = Mbr_Group.ORG_UNIT_ID and Order_Detail.PARENT_PRODUCT = Mbr_Group.MEMBER_GROUP and Mbr_Group.PRIMARY_FLAG = ‘Y’.

Calculate the number of years between Order_Detail.INITIAL_BEGIN_DATE and Order_Detail.CYCLE_END_DATE.

PLUS

Select each active membership order after the minimum order line where Order_Detail.LINE_STATUS_CODE = ‘A’ and SUBSYSTEM = ‘MBR’ and Order_Detail.FULFILL_STATUS_CODE not in (‘D’,’S’) Order_Detail.ORG_ID = Mbr_Group.ORG_ID and Order_Detail.ORG_UNIT_ID = Mbr_Group.ORG_UNIT_ID and Order_Detail.PARENT_PRODUCT = Mbr_Group.MEMBER_GROUP and Mbr_Group.PRIMARY_FLAG = ‘Y’

Calculate the number of years between CYCLE_BEGIN_DATE and CYCLE_END_DATE for each selected membership order line. Future active membership order lines should also be included.

·            Current year score: If the customer is a current member in the period of time defined as “current year” based on the CRM360_YTD_START_MONTH parameter, the customer gets points for a full year only.

·            Detailed Logic: The member only gets credit for the “current year” as defined by the CRM360_START_MONTH parameter value.  Even if the membership has a begin date before the current year and/or even if the cycle end date is greater than the current year, the member should get points for 1 year (a partial year counts as one year).

For the order line with the membership order line, Order_Detail.LINE_STATUS_CODE = ‘A’ and SUBSYSTEM = ‘MBR’ and Order_Detail.FULFILL_STATUS_CODE not in (‘D’,’S’) and Order_Detail.ORG_ID = Mbr_Group.ORG_ID and Order_Detail.ORG_UNIT_ID = Mbr_Group.ORG_UNIT_ID and Order_Detail.PARENT_PRODUCT = Mbr_Group.MEMBER_GROUP and Mbr_Group.PRIMARY_FLAG = ‘Y’ and

If month(getdate()) < CRM360_YTD_Start_Month
and cycle_end_date >= CRM360_YTD_Start_Month + '1' + year(getdate()) minus 1 year
and cycle_begin_date <= CRM360_YTD_Start_Month + '1' + year(getdate()) minus 1 year
or
and cycle_begin_date >= CRM360_YTD_Start_Month + '1' + year(getdate()) minus 1 year
and cycle_end_date >= CRM360_YTD_Start_Month + '1' + year(getdate()) minus 1 year
If month(getdate()) > CRM360_YTD_Start_Month
and cycle_end_date >= CRM360_YTD_Start_Month + '1' + year(getdate())
and cycle_begin_date <= CRM360_YTD_Start_Month + '1' + year(getdate())
or
and cycle_begin_date >= CRM360_YTD_Start_Month + '1' + year(getdate())
and cycle_begin_date < CRM360_YTD_Start_Month + '1' + year(getdate()) + 1 year

·            Example:

o           Membership from 12/5/2009 – 12/4/2010 should only get points for 1 year membership

o           Membership from 1/1/2012 – 12/31/2012 should get points for 1 year membership

·            Count incrementally, i.e., from 1 through 365 days of membership (non leap year) earns the points for a full year, from 366-730 earns 2 years’ worth of points, and etc.

·            Even a partial year earns points (full points, not partial).

A distinct Initial Begin Date is captured for each product in base. Clients that manually override this date (e.g. when a Student moves on to Professional membership) may see an inflated number.

·            Membership orders must be active to be considered (order line status is considered only, and does not ‘expire’; fulfill status is not considered)

·            Same points are assessed regardless of type of membership (e.g. Student earns as much as Professional)

·            Lifetime score includes all memberships as defined above. YTD score only considers memberships that fit the above definition AND where today’s date falls between the Cycle Begin and End Dates (i.e., it’s an active, current order) and fulfill_status_code <> ‘D’ or ‘S’. Take the latest current order and count number of years between Initial Begin Date and Cycle End Date. This means that if a member has paid a multi-year membership, they will get credit for future years.

No single field has “all the answers” – e.g. order history may not be complete. Do not have an Original Join Date (would require new CUS field and trigger for 1st membership; would need to determine which MBR products qualify). Initial Begin Dates reset with interruptions and are specific to product.

Year-to-date (YTD) scoring uses the same criteria as above, but records must fall in the current year, based on theCRM360_YTD_START_MONTH application parameter.

Web Credentials

If web credentials have not been defined for the constituent, click the Create a web login link to open the Web Access Control screen. If web credentials have been defined for the constituent, the constituent's SSO login email address displays as a hyperlink. Click this link to open the Web Access Control screen to reset the constituent's password or disable their web account.

The Web Credentials is updated by the usp_ConstituentOverview_WebCredentials stored procedure.

Manages This Online Group

(Not pictured above) If the constituent manages one or more segments, the segments display here. Please note that a user may be a manager of one group, but part of other group(s). The manager information will trump the general group information, discussed in Belongs to This Online Group.

Belongs to This Online Group

(Individual only) If the constituent is not a manager of a segment, the segment(s) to which the constituent belongs display here.

Online Records Managed By

(Not pictured above) If the constituent is a segment, the segment manager displays.