Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments |
|---|---|---|---|---|---|---|---|---|
| ORG_ID | int | 4 | null |
|
|
|||
| SITE_ID | int | 4 | √ | null |
|
|
||
| PACKAGE_ID | int | 4 | √ | null |
|
|
||
| PACKAGECATEGORY_ID | int | 4 | √ | null |
|
|
||
| MEMBERSHIP_ID | int | 4 | null |
|
|
|||
| Age | int | 4 | √ | null |
|
|
Customer age in years |
|
| Cust_Tran_Count | int | 4 | √ | null |
|
|
Pass-holder non-memberships transactions |
|
| Customer_Distance | numeric | 21,6 | √ | null |
|
|
Primary member customer distance from membership package site |
|
| Days_Since_Used | int | 4 | √ | null |
|
|
Days since membership last used |
|
| Membership_Age | int | 4 | √ | null |
|
|
Membership age in days |
|
| package_period | int | 4 | √ | null |
|
|
Package period in days |
|
| Sale_Discounted | int | 4 | √ | null |
|
|
Was memebership discounted at sale (1 = yes, 0 =no) |
|
| UNITFEE | money | 21,4 | √ | null |
|
|
Unit price of membership |
|
| Pass_Count | int | 4 | √ | null |
|
|
Number of passes assigned to membership |
|
| Renewal_Count | int | 4 | √ | null |
|
|
Number of renewals of membership |
|
| Usage_Count | int | 4 | √ | null |
|
|
Number of membership usages of membership |
|
| family_membership | int | 4 | √ | null |
|
|
Is the membership a family membership (1 = yes, 0 = no) |
|
| as_of | datetime | 16,3 | √ | null |
|
|
Date used to compute retention score |
|
| capture_dt | datetime | 16,3 | √ | null |
|
|
||
| retention_score | numeric | 21,6 | √ | null |
|
|
Predicted likelyhood of membership being renewed (0 = least likely, 100 = most likely) |
|
| class | varchar | 25 | √ | null |
|
|
Predicted classification of membership renewal independent of retention score (Risk, Retain) |
|
| regression_model | varchar | 50 | √ | null |
|
|
Model used to generate “retention score” |
|
| classification_model | varchar | 50 | √ | null |
|
|
Model used to predict “class” |
|
| membership_type | varchar | 25 | √ | null |
|
|
Type of membership for scoring (auto_renewal, retention_eligible) |
|
| LAST_SYNC_TIME | datetime | 16,3 | (getdate()) |
|
|
|||
| ACTIVE_HUB_PROCESS_ID | bigint | 8 | √ | null |
|
|
||
| row_version | binary | 8 | √ | null |
|
|
||
| AUTORENEWALTYPE | smallint | 2 | √ | null |
|
|
Type of auto-renewal for membership at time of scoring - Not auto renewed - By credit card - By EFT - By manual deduction - By payment plan |
|
| Days_Since_Tran | int | 4 | √ | null |
|
|
Days since a non-membership Transaction for all pass holders |
|
| created_dt | datetime | 16,3 | √ | null |
|
|
Date membership risk was first calculated |
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| Membership_Risk_Score_PK | Primary key | Asc/Asc | ORG_ID + MEMBERSHIP_ID |
| MEMBERSHIP_RISK_SCORE_MEMBERSHIP_ID_ORG_ID_IX | Performance | Asc/Asc | MEMBERSHIP_ID + ORG_ID |
| MEMBERSHIP_RISK_SCORE_ROW_VERSION_IX | Performance | Asc | row_version |
| ORG_ID_RETENTION_SCORE | Performance | Asc/Asc | ORG_ID + retention_score |