iMIS for Clubs: Working with historical member data

👍

Tip

The audience for this article's content is iMIS for Clubs users.

HistoricalClubProperties and HistoricalHouseholdDemographics are out-of-the-box panel sources that are used to store snapshots of member data on a monthly basis.

The Process Automation task Club historical reporting can be scheduled on a monthly basis to copy member data into these panel sources. It is recommended to schedule this task to run on the first of the month, so that the dates for all data rows are consistently on the first.

For more information on panel sources, see Panel Designer.

📘

Note

Data for all contacts is copied to HistoricalClubProperties by this task, not just members.

Review before importing

Keep in mind the following:

• Each row in these Workbook Converter tabs should represent a snapshot of a single member’s data for a specific month in time.
• For columns which have pre-defined reference lists of values, use the Code matching one of those pre-defined values to indicate a value, not the Description.
• Multiple values for a given cell should be separated with commas.

Importing historical data

To import historical data for months previous to the usage of iMIS (or previous to the Process Automation task being run regularly), the Workbook Converter tool (WBC) can be used to import the data into these panel sources.

Do the following to import historical data:

1. Create tabs in the Workbook Converter for HistoricalClubProperties and HistoricalHouseholdDemographics.
2. Import the data. For details, see Importing the workbook data.

The columns to use to import into HistoricalClubProperties are:

• Date - Use the 1st of the month, for example: 04/01/2018
• ID - The member’s ID
• PrimaryClubId - The ID of the club organization which is the member’s primary club
• PrimaryClubName - The Name of the club organization which is the member’s primary club
• MembershipStatus - Active, Future, or Expired
• AttendedThisTerm - TRUE or FALSE indicating if the member has attended a club as of the Date for this row during the current membership term
• Age
• FoodAllergies
• FoodAllergiesOther
• Grade
• SchoolOrgId - The ID of the member’s school organization
• SchoolName - The Name of the member’s school organization
• SchoolLunch
• DataPermission
• MedicalPermission
• TransportationPermission
• DataSharePermission
• PressPermission
• TechnologyPermission
• GeneralPermission
• HouseholdGroupName – The Name of the member’s household group
• HouseholdGroupImportID – The alphanumeric ID that is assigned to the member’s household group in the HistoricalHouseholdDemographics tab, if any

The columns to use to import into HistoricalHouseholdDemographics are:

• Date - Use the 1st of the month, e.g. 04/01/2018
• GroupImportID – Alphanumeric ID which is used to match this household row to a member’s row in the HistoricalClubProperties tab
• GroupName – The Name of the household
• HouseholdComposition
• HouseholdIncomeRange
• HouseholdIncomeValue
• HousingType
• AssistanceProgram
• OtherAssistanceProgram

Must do after importing

After using the Workbook Converter to import data into HistoricalClubProperties and HistoricalHouseholdDemographics, the Process Automation task Process imported club historical data should be run which updates the imported data to fill in any missing cell data or missing months. The result is that these panel sources will have one row per member or household per month with a snapshot of their data at that time, which can then be used in reporting.

🚧

Warning

This task can only be run one time, so make sure that historical data import is 100% complete before running the task.

Using blank cells when importing

Column cells may be left blank, which indicates that the value for that column has not changed and should match the most recent previously known value. If there is no previously known value then the value will be left blank.

👍

Example

Jorge Cervantes has a row for 04/01/2018 which indicates Dairy in the FoodAllergies column. In Jorge’s row for 10/01/2018, the FoodAllergies column can be left blank as his allergy has not changed.

If a given column for a member had a value in the past but should now be set to blank, then enter Blank in the column.

👍

Example

Jorge Cervantes has a row for 04/01/2018 which indicates Dairy in the FoodAllergies column. As of 02/01/2019 he had no FoodAllergies, so in Jorge’s row for 02/01/2019, the FoodAllergies column should be indicated as Blank.

Reporting

To include the historical data including this data in IQA queries or reports, use the HistoricalClubPropertyReporting business object. This business object will return one row per member per month, with properties or columns for both the member and the member’s household at that time.

For imported data, the HouseholdGroupImportID from HistoricalClubProperties are used to link the member to a household with a matching GroupImportID in HistoricalHouseholdDemographics to get the member’s household demographic property values.

ContactKey is used to identify the member in this business object. This value is also known as PartyId in some other business objects.

The Month and Year properties can be used to retrieve member data as of a certain month in time. A full Date property is also available if needed. The dates for this data should be the 1st of the month, but that is not guaranteed.