Foundation
Box Office
Stripe + Airtable + Zapier — Configuration, field schema, and data flow for CIS pre-commitment validation
The Foundation Box Office is not a ticketing convenience. It is the data collection layer that makes the CII scoreable. Every CIS activation requires five data streams. Three of them, pre-commitment rate, resident ZIP share, and repeat participation, flow directly from purchase records. If those records are scattered across individual proposers' Venmo accounts and Eventbrite logins, there is no pipeline. This configuration puts the Foundation in control of the merchant relationship, the data format, and the refund authority that the 100% validation gate requires.
Three tools do three distinct jobs. Stripe handles money. Airtable holds records. Zapier connects them automatically so no one touches a spreadsheet between a purchase and a scorecard.
Payment Link Stripe-hosted page. Collects name, email, ZIP, program selection, ticket tier. Payment processed.
webhook On successful charge, Stripe sends a checkout.session.completed event with all purchaser data.
to Airtable Zapier catches the webhook, maps fields, and creates a new record in the Purchasers table.
scores live Rollup fields count commitments per program. Foundation sees real-time validation progress.
feeds CII Post-activation, the Purchasers table exports a clean CSV. The Matching Workbook scores F4. The Activation Report Generator imports F1 and F2.
Create the Stripe account under the Duncanville Arts Foundation's legal name and EIN. Use the Foundation's bank account for payouts. This is the step that establishes the Foundation as merchant of record. Do not create the account under a personal name or a proposer's name under any circumstances.
Create one Stripe Product per activation. Each product maps to a single CIS program in Airtable. Within each product, create one Price per ticket tier.
| Product Field | Value Convention | Example |
|---|---|---|
| Product Name | [Program Name] — [Activation Date] | Third Wednesday Jazz — June 18, 2026 |
| Product Description | CIS Activation ID + venue + brief description | ACT-2026-001 • Arts Junction • Monthly jazz series |
| Metadata: program_id | Airtable Program record ID | recXXXXXXXXXXXXXX |
| Metadata: activation_date | ISO 8601 | 2026-06-18 |
| Metadata: validation_deadline | ISO 8601, 30 days before activation | 2026-05-19 |
Create one Payment Link per activation. Configure each link with the following settings:
Zapier will read these fields from the Stripe checkout.session.completed object. Confirm they are present on every completed session before going live.
| Stripe Field | Path in API Response | Maps to Airtable Field |
|---|---|---|
| Customer email | customer_details.email | |
| Customer name | customer_details.name | Name (Stripe) |
| Display name (custom field) | custom_fields[0].text.value | Display Name |
| Postal code | customer_details.address.postal_code | ZIP Code |
| Amount paid (cents) | amount_total | Amount Paid (cents) |
| Payment Intent ID | payment_intent | Stripe Payment Intent ID |
| Product metadata: program_id | metadata.program_id | Program (linked record) |
| Product metadata: activation_date | metadata.activation_date | Activation Date |
The Airtable base has three tables. Programs holds one record per activation. Purchasers holds one record per ticket purchase. Activations holds the post-event scorecard inputs. The Purchasers table is the operational core. Everything the CII needs for F1, F2, and F4 lives here.
| Field Name | Type | Notes |
|---|---|---|
| Program Name | Single line text | Primary field. Matches Stripe Product name. |
| Activation ID | Single line text | Format: ACT-YYYY-NNN. Used in all CII reporting. |
| Proposer | Single line text | Organization or individual proposer name. |
| Activation Date | Date | ISO 8601. Used for F4 matching across activations. |
| Validation Deadline | Date | 30 days before activation date. |
| Venue | Single line text | Default: Arts Junction at 202 West Center. |
| Capacity | Number | Maximum seats. Set as quantity limit on Stripe Payment Link. |
| Cost to Activate | Currency | Total projected costs including licensing, venue, talent, and operations. |
| Ticket Price — General | Currency | Standard admission price. |
| Ticket Price — Supporter | Currency | Premium tier. Optional. |
| Tickets Required to Validate | Formula | CEILING({Cost to Activate} / {Ticket Price - General}). Minimum pre-commitments needed at 100%. |
| Purchasers | Linked record (Purchasers) | Auto-populated by Zapier as purchases arrive. |
| Total Committed | Rollup | COUNT of linked Purchasers. Live validation tracker. |
| Total Revenue Committed | Rollup | SUM of Amount Paid from linked Purchasers. |
| Validation Status | Single select | Options: Campaigning, Validated, Did Not Validate, Cancelled. |
| Stripe Product ID | Single line text | Used to match incoming Zapier records to the correct program. |
| Pipeline Stage | Single select | Intake, Develop, Validate, Activate, Graduate. Mirrors CIS v2.0 pipeline. |
One record per ticket. Zapier creates every record automatically from the Stripe webhook. No manual entry after initial setup.
| Field Name | Type | CII Factor |
|---|---|---|
| Name (Stripe) | Single line text | F4 matching |
| Display Name | Single line text | F4 matching (household name) |
| F4 matching (primary key) | ||
| ZIP Code | Single line text | F2 resident share |
| Is Duncanville Resident | Formula | F2 IF(OR({ZIP Code}="75116",{ZIP Code}="75137"),TRUE,FALSE) |
| Amount Paid (cents) | Number | F1 revenue |
| Amount Paid (dollars) | Formula | F1 {Amount Paid (cents)} / 100 |
| Ticket Tier | Single select | General, Supporter |
| Program | Linked record (Programs) | All factors |
| Activation Date | Date | F4 matching |
| Purchase Timestamp | Date/time | Validation tracking |
| Stripe Payment Intent ID | Single line text | Refund reference |
| Refund Status | Single select | Options: Active, Refunded. Updated manually on no-go. |
| Previous Activations | Count (linked from Activations) | F4 — Populated post-activation via email match lookup. |
| Is Repeat Participant | Formula | F4 IF({Previous Activations} > 0, TRUE, FALSE) |
One record per completed activation. Holds the raw inputs the Activation Report Generator reads. Populated by Foundation staff after each event night.
| Field Name | CII Factor | Source |
|---|---|---|
| Program | All | Linked record (Programs) |
| Actual Attendance | F1 | Door count on activation night |
| Pre-Committed Revenue | F1 | Rollup from Purchasers table |
| Total Activation Cost | F1 | Confirmed actual costs |
| Duncanville ZIP Purchasers | F2 | Rollup: count where Is Duncanville Resident = true |
| Total Purchasers | F2 | Rollup: count of all Purchasers linked to Program |
| Survey Responses — Would Have Left | F3 | Substitution survey count (collected separately) |
| Survey Responses — Total | F3 | Substitution survey total responses |
| Repeat Purchasers | F4 | Rollup: count where Is Repeat Participant = true |
| Activation Night Transactions | F5 | Tenant POS data (collected separately) |
| Baseline Average Transactions | F5 | Four-night baseline average from Baseline Data Entry Template |
One Zap handles all ticket purchases across all activations. It does not need to be duplicated per program. The program_id metadata on the Stripe product routes each purchase to the correct Airtable Program record automatically.
This fires once per completed purchase. Failed payments and abandoned checkouts do not trigger it.
| Airtable Field | Zapier Source |
|---|---|
| Name (Stripe) | Stripe → Customer Details → Name |
| Display Name | Stripe → Custom Fields → [0] → Text → Value |
| Stripe → Customer Details → Email | |
| ZIP Code | Stripe → Customer Details → Address → Postal Code |
| Amount Paid (cents) | Stripe → Amount Total |
| Program | Stripe → Metadata → program_id Airtable will link this string to the matching Programs record by record ID. |
| Activation Date | Stripe → Metadata → activation_date |
| Purchase Timestamp | Stripe → Created (Unix timestamp, convert to ISO) |
| Stripe Payment Intent ID | Stripe → Payment Intent |
| Refund Status | Set to: Active (static value) |
Use Stripe's test mode to run three purchases through the Zap before going live: one Duncanville ZIP, one out-of-market ZIP, and one at the Supporter price tier. Confirm all three create correct Airtable records and that the Programs table rollup fields update accurately.
Zapier documentation: Stripe + Airtable integration. Stripe documentation: Metadata on Payment Links, test mode.The 100% pre-commitment requirement is the governing financial discipline of the CIS. The Box Office is the mechanism that makes it enforceable. The Foundation checks Airtable on Day 15 and Day 30 of every validation campaign.
Pull the Programs record for the activation. Compare Total Committed against Tickets Required to Validate. A program tracking below 40% on Day 15 is unlikely to close. Issue a written mid-campaign assessment to the proposer within 48 hours. Continue the campaign; do not cancel early.
| Outcome | Airtable Action | Stripe Action | Proposer Communication |
|---|---|---|---|
| Total Committed ≥ Tickets Required | Set Validation Status to Validated. Set Pipeline Stage to Activate. | No action. Funds remain held until activation night. | Written confirmation. Activation scheduling begins. |
| Total Committed < Tickets Required | Set Validation Status to Did Not Validate. | Issue full refunds to all purchasers via Stripe dashboard. Use Payment Intent IDs from Purchasers table. Update Refund Status to Refunded in Airtable. | Written analysis and insights returned. Development support continues. Proposer may re-enter a new campaign. |
When a program does not validate, the Foundation executes all refunds directly from the Stripe dashboard. The process is:
The Foundation's refund policy for individual requests before the validation decision should be established before the first campaign opens and published on the ticket confirmation page. A reasonable default: full refund if requested more than 14 days before the activation date; no refund within 14 days of the activation date except in cases of program cancellation.
After each activation, export the Purchasers table filtered to the relevant program. This CSV is the input for the Matching Workbook and for the Activation Report Generator in the CIS Toolkit. The column order below is the canonical format all downstream tools are built against.
| Column | Type | CII Factor |
|---|---|---|
| activation_id | String | All |
| String | F4 primary key | |
| display_name | String | F4 secondary match |
| zip_code | String (5-digit) | F2 |
| is_duncanville_resident | Boolean | F2 |
| amount_paid_dollars | Decimal | F1 |
| ticket_tier | String | F1 breakdown |
| purchase_timestamp | ISO 8601 | Validation tracking |
| is_repeat_participant | Boolean | F4 |
| stripe_payment_intent_id | String | Refund reference |
| refund_status | String | Financial reconciliation |
| CII Factor | Derived From | Remaining Input |
|---|---|---|
| F1 — Pre-Commitment Rate | SUM(amount_paid_dollars) vs. total activation cost | Actual activation cost (from proposer) |
| F2 — Resident Share | COUNT(is_duncanville_resident = true) / COUNT(*) | None. Fully resolved by CSV. |
| F3 — Substitution Rate | Not in this CSV | Substitution survey (collected at door) |
| F4 — Repeat Participation | COUNT(is_repeat_participant = true) / COUNT(*) | None after second activation. First activation always scores zero. |
| F5 — Adjacent Business Lift | Not in this CSV | Baseline Data Entry Template (tenant POS data) |
| Platform | Cost | Notes |
|---|---|---|
| Stripe | 2.9% + $0.30 per transaction | No monthly fee. Fee applies to each ticket sale. On a $20 ticket, the Foundation nets $19.12. On a no-go refund, Stripe refunds the purchaser in full but retains the processing fee. Build a small processing buffer into the cost-to-activate calculation or absorb it as an infrastructure cost. |
| Airtable | Free tier: up to 1,000 records per base | Sufficient for Year One at expected activation volume. The free tier supports all field types required by this schema. Upgrade to Plus ($10/mo) if record count approaches the limit or if additional collaborator seats are needed. |
| Zapier | Starter plan: approximately $20/month | The free tier supports only 100 tasks per month, which is insufficient. The Starter plan supports 750 tasks and multi-step Zaps. A single Zap handles all activations; no additional Zaps are needed for this configuration. |
Each new activation requires three setup steps: create a Stripe Product with the correct metadata fields, create a Payment Link with the quantity cap set to capacity, and create an Airtable Programs record with the matching Stripe Product ID. Estimated time per activation: fifteen minutes. No code changes are required.