# MySQL export format

The app currently imports a CSV, so prepare a MySQL **view or export query** that produces one flat row for each user connected to an organization account.

## Required row grain

```text
One membership organization + one connected user + one reporting period
```

For example, a member organization with five connected users produces five CSV rows. Organization-level values such as renewal outcome, need, owner, and follow-up date are repeated on all five rows.

Do not export raw event-level tables directly. First aggregate Green posts, webinar attendance, MST cases, program enrolments, and Knowledge Base activity by `organization_id` and `user_id`; then join those results to the organization-user relationship. This prevents a user with several events from appearing as several users and inflating totals.

## Required organization/account fields

| CSV column | Suggested MySQL source | Format and rule |
| --- | --- | --- |
| `organization_id` | membership/account primary key | Stable ID. Never use organization name as the join key. |
| `organization` | membership/account name | Display name. |
| `account_type` | account type | `Member organization`, `Household`, or `Individual`. Household/individual accounts are excluded from membership-renewal KPIs. |
| `membership_tier` | membership tier code | `A1`, `A2`, `B1`–`B9`, or `C`. Keep this organization-level value consistent across connected-user rows. |
| `membership_year` | membership/renewal period | Four-digit reporting year. Include historical rows with their corresponding year rather than overwriting the current year. |
| `member_status` | membership status | `Active`, `Terminated`, etc. |
| `organization_renewal_outcome` | closed renewal record | `1` renewed, `0` did not renew, blank if still unknown. Repeat for every connected user. |
| `membership_age_at_termination_years` | membership start/end dates | Decimal years for terminated memberships only; blank otherwise. |

## Connected-user and activity fields

| CSV column | Aggregate before joining | Format |
| --- | --- | --- |
| `individual` | user name | Display name. |
| `email` | user email | One stable email per connected user. |
| `green_participant` | Green access/activity | `1` or `0`. |
| `green_topics_posted` | Green topic posts | Count for the reporting period. |
| `programs_participated` | distinct program/service enrolments | Count for the user in the period. |
| `program_names` | distinct program/service names | Semicolon-separated names for the user in the period; use a controlled program-name list. |
| `mst_calls` | MST calls/cases | Count for the user in the period. |
| `webinars_attended` | attended webinar records | Count for the user in the period. |
| `kbLogins` | Knowledge Base sign-ins | Count for the user in the period. |
| `eventCount` | other live events attended | Count for the user in the period. |
| `greenActivity` | all Green activity | Count of posts/comments/likes in the period. |

## Relationship and decision fields

| CSV column | Format |
| --- | --- |
| `accredited`, `renewedOnTime` | `1` or `0`; repeated organization values. |
| `programCount`, `yearsMember`, `caseCount`, `financialTxCount` | Numeric counts; repeated only when they are organization-level. |
| `emailOpenRate` | Number from 0 to 100. |
| `financial_need`, `context_need`, `cccc_help_fit` | `1` or `0`; repeat organization values. |
| `data_confidence` | `High`, `Medium`, or `Low`. |
| `staff_owner`, `staff_note` | Staff review values. |
| `decision`, `reason_code`, `follow_up_date` | Workflow values; use `YYYY-MM-DD` for the date. |

## MySQL 8 query pattern

Replace the placeholder table and column names with your real schema. The reporting period below is the previous 12 months.

```sql
WITH org_users AS (
  SELECT
    a.id AS organization_id,
    a.name AS organization,
    a.account_type,
    m.membership_tier,
    m.membership_year,
    m.status AS member_status,
    m.organization_renewal_outcome,
    m.financial_need,
    m.context_need,
    m.cccc_help_fit,
    m.data_confidence,
    m.staff_owner,
    m.staff_note,
    m.decision,
    m.reason_code,
    m.follow_up_date,
    u.id AS user_id,
    CONCAT(u.first_name, ' ', u.last_name) AS individual,
    u.email
  FROM memberships m
  JOIN accounts a ON a.id = m.account_id
  JOIN account_users au ON au.account_id = a.id
  JOIN users u ON u.id = au.user_id
),
green AS (
  SELECT
    organization_id,
    user_id,
    MAX(is_green_participant) AS green_participant,
    SUM(topic_posts) AS green_topics_posted,
    SUM(topic_posts + comments + likes) AS greenActivity
  FROM green_activity
  WHERE occurred_at >= CURDATE() - INTERVAL 12 MONTH
  GROUP BY organization_id, user_id
),
programs AS (
  SELECT organization_id, user_id,
    COUNT(DISTINCT program_id) AS programs_participated,
    GROUP_CONCAT(DISTINCT program_name ORDER BY program_name SEPARATOR '; ') AS program_names
  FROM program_enrolments
  WHERE enrolled_at >= CURDATE() - INTERVAL 12 MONTH
  GROUP BY organization_id, user_id
),
webinars AS (
  SELECT organization_id, user_id, COUNT(*) AS webinars_attended
  FROM webinar_attendance
  WHERE attended_at >= CURDATE() - INTERVAL 12 MONTH
  GROUP BY organization_id, user_id
),
mst AS (
  SELECT organization_id, user_id, COUNT(*) AS mst_calls
  FROM mst_cases
  WHERE opened_at >= CURDATE() - INTERVAL 12 MONTH
  GROUP BY organization_id, user_id
)
SELECT
  ou.organization_id,
  ou.organization,
  ou.account_type,
  ou.membership_tier,
  ou.membership_year,
  ou.individual,
  ou.email,
  ou.member_status,
  COALESCE(g.green_participant, 0) AS green_participant,
  COALESCE(g.green_topics_posted, 0) AS green_topics_posted,
  COALESCE(g.greenActivity, 0) AS greenActivity,
  COALESCE(p.programs_participated, 0) AS programs_participated,
  COALESCE(p.program_names, '') AS program_names,
  COALESCE(w.webinars_attended, 0) AS webinars_attended,
  COALESCE(ms.mst_calls, 0) AS mst_calls,
  /* Add organization-level score, renewal, need, and review fields here. */
  ou.organization_renewal_outcome,
  ou.financial_need,
  ou.context_need,
  ou.cccc_help_fit,
  ou.data_confidence,
  ou.staff_owner,
  ou.staff_note,
  ou.decision,
  ou.reason_code,
  ou.follow_up_date
FROM org_users ou
LEFT JOIN green g ON g.organization_id = ou.organization_id AND g.user_id = ou.user_id
LEFT JOIN programs p ON p.organization_id = ou.organization_id AND p.user_id = ou.user_id
LEFT JOIN webinars w ON w.organization_id = ou.organization_id AND w.user_id = ou.user_id
LEFT JOIN mst ms ON ms.organization_id = ou.organization_id AND ms.user_id = ou.user_id;
```

## Export checks before uploading

1. One row per `organization_id` + `email` + `membership_year` pair for the reporting period.
2. No duplicate rows created by event joins.
3. One consistent renewal outcome, need score inputs, and staff-review values per `organization_id`.
4. All activity counts are zero, not blank, when there was no activity.
5. Export as UTF-8 CSV with a header row.

The full set of accepted CSV columns is in [MEMBER_CSV_FORMAT.md](MEMBER_CSV_FORMAT.md).