OpenField Data Export Pipeline

Overview

OpenField provides clients with access to a comprehensive set of interrelated tables representing the core objects in our data model that users actively input and engage with, allowing clients to perform analysis and generate the reports they need. We deliver daily flat-file exports of outreach data directly to your cloud storage bucket, with each sync cycle providing a complete dataset refresh.

To better understand the data available via the pipeline, please review:

• Data Dictionary: Provides table and column definitions
• Entity Relationship Diagram: Illustrates how primary and foreign key relationships connect these tables.
• Core Export Reporting Queries: Provides sample SQL queries for generating the core outreach datasets used for analysis and reporting.

If you are interested in receiving data via the OpenField Data Export Pipeline, please reach out to our data team <[e-mail]> with the Gardens you’d like included and your cloud storage configuration (outlined below)

Cloud Storage Configuration

To receive data via the OpenField Data Export Pipeline, please share the following securely, based on your storage provider:

🟦 AWS S3

• Bucket Name (e.g., my-openfield-data)
• Bucket Path (optional, e.g., exports/)
• Region (e.g., us-east-1)
• Access Method: IAM Role ARN or Access Key/Secret Key

Required Permissions:

• s3:GetObject
• s3:PutObject
• s3:ListBucket
• s3:DeleteObject

🟨 Google Cloud Storage (GCS)

• Bucket Name (e.g., openfield-exports)
• Bucket Path (optional, e.g., daily/)
• Service Account Key (JSON)

Required Permissions:

• storage.objects.get
• storage.objects.create
• storage.objects.list
• storage.objects.delete

Export Structure

Each data export is garden-specific and stored in a directory named with the export’s date and time (YYYYMMDD_HHMMSS). Each export contains one Parquet file and a corresponding manifest file for each table. The manifest provides metadata about the export, including:

• File URLs and locations
• Content length and record counts
• Schema definitions with data types and constraints
• Export summary statistics

Data Processing Methods

SQL Database Loading

• Redshift: data_export_redshift.py
• Postgres: data_export_postgres.py

The database loading method provides automated table creation and bulk data import capabilities. The process:

1. Identifies the most recent timestamped export directory.
2. Reads manifest files to understand data structure and schema
3. Maps OpenField data types to appropriate SQL types (INTEGER, TEXT, BOOLEAN, TIMESTAMP)
4. Creates database tables with proper column definitions including VARCHAR length constraints
5. Loads data using COPY for optimal performance

Release Notes

Data Export Pipeline Enhancements - March 2026

What's New

We have added OpenField's Turf object to the Data Export Pipeline. A turf is the fundamental unit of work assignment in OpenField’s door-knocking app. The system algorithmically generates walkable geographic polygons from census blocks based on a target list of addresses. Canvassers then check out an available turf from the map, visit the addresses within that polygon, and mark the turf complete when finished. This table stores each turf’s geographic boundary along with its assignment status and lifecycle timestamps as it progresses from available to checked out to complete.

These changes are reflected within the pipeline data dictionary and Entity Relationship Diagram.

Data Export Pipeline Enhancements - December 2025

What's New

We have added OpenField's Campaigns object to the Data Export Pipeline. Campaigns allow you to segment your Admin Panel for different organizations operating within the same Garden. You can read more about this feature in OpenField's Help Center.

Data Added to the Pipeline

1. New campaigns table: This table will list each campaign’s name along with its primary key (id)
2. New campaign_id column to the tables listed below. In each case, campaigns_id joins to id in the campaigns table:
   • conversation_code
   • scripts
   • questions
   • script_question
   • question_dropdown_options
   • question_responses, conversations
   • conversations_attempts_lookup
   • people_labels
   • customuser
   • label

Important Note About Primary Keys

If an object (script, question, conversation, etc.) is shared across multiple campaigns, it will now appear once per campaign in its table. In those cases, you will need to treat the combination of the object’s id plus its campaign_id as a composite primary key.

These changes are also reflected within the pipeline data dictionary and Entity Relationship Diagram.

Data Export Pipeline Enhancements - August 2025

What's New

When a canvasser or phone banker uses the OpenField app, they see all individuals linked to the same phone number or address. If they make a successful contact, they can select the person they spoke with, and the system records that person’s ID on the back-end. For unsuccessful attempts, like when no one answers, the system logs the attempt but doesn’t assign it to a specific person, since no one was identified.

To provide more complete data, OpenField is introducing a new object in the Data Export Pipeline, conversations_attempts_lookup. This table links each unsuccessful contact to its conversation_id, list_id,  along with all relevant person_ids tied to the address or phone number. Since multiple individuals can be associated with the same contact point, a single conversation_id may  correspond to more than one person_id.

New Export: conversations_attempts_lookup

• Links unsuccessful contact attempts to conversation_id, list_id, and all relevant person_ids
• JOIN with conversations export for complete contact details
• Sample query available for building conversations_attempts_lookupb using data from the Data Export Pipeline

Enhanced people Export

• Added phone2, phone3, phone4 fields
• Added all four national address ID types
• Now includes all people from the people_lables export table, not just those within the conversations export

New Export: people_labels

• Person-to-list lookup with id, people_id, and list_id
• Enables list membership analysis

Enhanced conversations Export

Added contacted_national_address_id_id field

Have more questions? Email us at [e-mail]. 

If you have feature requests or suggestions for how to improve OpenField, stop by our Product Suggestion & Feature Request page!