# Member CSV format

Upload one row for each **unique user connected to a member organization**. The app attributes that person's participation to their organization, then shows organization-level averages and totals.

Use the included [sample-member-participation-data.csv](sample-member-participation-data.csv) as a ready-to-import example with 500 organizations, 2–10 connected users per organization, varied geographic/program participation, and complete historical/decision fields.

## Core account columns

| Column | What to enter |
| --- | --- |
| `organization` | Account or organization name. Keep the spelling consistent for every connected user. |
| `organization_id` | Recommended stable membership/account ID. The app uses this to group users even if an organization name changes. |
| `account_type` | `Member organization` for a renewing membership; `Household` or `Individual` for a retained personal account. Household/individual accounts are excluded from organization-renewal KPIs. |
| `membership_tier` | Membership tier code, repeated for every connected user. Use the approved values below. |
| `membership_year` | Four-digit membership/reporting year, such as `2026`. Use this to load history and switch the Dashboard or Leadership View by year. |
| `province` | Province or region used for outreach segmentation, such as `BC` or `ON`. |
| `organization_type` | Organization type used for segmentation, such as `Church`, `Pregnancy centre`, or `Christian school`. |
| `focus_area` | Ministry or service focus used to support future outreach strategies. |
| `individual` | Connected user's full name. |
| `email` | Connected user's unique email address. |

## Participation columns

All participation figures should cover the same reporting period, normally the last 12 months. Enter `0` when there was no activity.

## Membership tier values

| Code | Tier |
| --- | --- |
| A1 | Accredited Member |
| A2 | Accredited Applicant |
| B1 | Web Member (Annual Revenue 0-30,000) |
| B2 | Affiliate (Annual Revenue 0-300,000) |
| B3 | Affiliate Member Plus (CQOC) Membership |
| B4 | Affiliate (Annual Revenue 300,001-675,000) |
| B5 | Affiliate (Annual Revenue 675,001-999,999) |
| B6 | Affiliate (Annual Revenue 1,000,000-4,999,999) |
| B7 | Affiliate Umbrella Organization (for group ins.) |
| B8 | Affiliate (Annual Revenue 5,000,000-9,999,999) |
| B9 | Affiliate (Annual Revenue 10,000,000 or more) |
| C | Professional Associate |

| Column | What it measures | Dashboard calculation |
| --- | --- | --- |
| `green_participant` | `1` when the user participates in The Green; otherwise `0`. | Green users per organization and Green adoption rate. |
| `programs_participated` | Number of programs or services this user is enrolled in or participating in. | Summed for each organization, then averaged across organizations. |
| `program_names` | Semicolon-separated program/service names, such as `Group Health; Pension; HR Consulting`. | Enables named-program filtering and optional specific-program weights. Keep names consistent across exports. |
| `mst_calls` | Number of MST calls for this user. | Summed for each organization, then averaged across organizations. |
| `webinars_attended` | Number of webinars attended by this user. | Summed for each organization, then averaged across organizations. |
| `green_topics_posted` | Number of Green topics posted by this user. | Total Green topics divided by Green participants. |
| `membership_age_at_termination_years` | Years from start of membership to termination, for a terminated organization only. Leave blank for active organizations. | One value per terminated membership organization is used in the portfolio average. |
| `member_status` | Optional descriptive status such as `Active` or `Terminated`. | Stored in the file but not currently calculated by the dashboard. |
| `organization_renewal_outcome` | `1` if the membership organization renewed, `0` if it did not renew, blank if unknown. Repeat the same organization outcome on each of its connected-user rows. | Renewal coverage, organization score adjustment, and renewal learning signals. |

## Existing relationship-score columns

These optional columns continue to drive the relationship score: `accredited`, `programCount`, `eventCount`, `yearsMember`, `greenActivity`, `kbLogins`, `renewedOnTime`, `caseCount`, `emailOpenRate`, and `financialTxCount`.

The app accepts `1`/`0`, `yes`/`no`, or `true`/`false` for binary fields. `emailOpenRate` is a percentage from 0 to 100. Existing aliases such as `org_name`, `person_name`, and `email_address` are also accepted.

## Need score and staff review columns

These optional fields support the pilot's separate 30-point **Member Need Score**. Repeat organization-level values on each connected-user row. A `1` enables the available points; a `0` leaves them at zero.

| Column | Points | What it means |
| --- | --- | --- |
| `financial_need` | 15 | Known financial or ministry hardship, budget strain, or short-term cash pressure. |
| `context_need` | 10 | Small, new, underserved, rural/remote, or limited-capacity context. |
| `cccc_help_fit` | 5 | CCCC has a clear, relevant path to help the organization. |
| `data_confidence` | — | `High`, `Medium`, or `Low`; use `Low` when human review is especially important. |
| `staff_owner` | — | Person responsible for the next conversation or review. |
| `staff_note` | — | Brief context not captured by the score. |
| `decision` | — | Recorded outcome such as `Review payment plan`, `Offer onboarding`, or `No discount`. |
| `reason_code` | — | Suggested values: `RETAIN-HIGH`, `NEED-HIGH`, `PROGRAM-DEEP`, `ONBOARDING`, `RECOVERY`, `STAFF-OVERRIDE`. |
| `follow_up_date` | — | Next review or follow-up date, preferably `YYYY-MM-DD`. |

The dashboard calculates **Combined decision guide = 70% relationship score + 30-point need score**. It is for staff review only and must not auto-approve or deny a discount.

## Named program weighting

Import `program_names` for every connected user. In Admin / Settings, each distinct program in the current import appears with an optional 0–5 weight. A weight of `0` leaves the base program-count signal unchanged; a weight from `1` to `5` increases the program-depth signal for organizations using that named program. Start with all weights at zero and only add weights after reviewing historical renewal outcomes.

## Important import rules

- Use one row per connected user per account. Do not add a second row for the same user unless the figures are split across reporting periods and have first been combined.
- For historical reporting, use one row per connected user **per membership year**. The same person can therefore appear once for 2024, once for 2025, and once for 2026.
- The unit of renewal is the membership organization—not an individual user. Repeat its `organization_renewal_outcome` on each connected-user row, or leave it blank when the renewal status is unknown.
- The unit of need and staff review is also the membership organization. Repeat the same need and review values on its connected-user rows.
- When an individual keeps a personal account after leaving an organization, set `account_type` to `Household` or `Individual` (for example, `Lukin Household`). That account remains visible in source data but does not count as a renewing member organization.
- A program or service is credited to an organization through its connected users. An admin or billing contact counts just as any other connected user.
- `programs_participated` is a participation count, not the number of programs created by the organization.
- If several users from the same organization attend one webinar or use MST, each person's activity is included. This intentionally measures organizational usage.
- If a user can be linked to multiple organizations, export a separate row for each valid affiliation and be aware their activity will be credited to each affiliated organization.