LogoLogo
Back to studio
  • Formsort documentation
  • โฉQuickstart
    • ๐ŸŸขGet started with Formsort
    • ๐ŸŽCore concepts
    • โ„น๏ธQuestion and content reference
    • ๐Ÿ“•Key terms
    • ๐Ÿž๏ธCreate your first flow
    • ๐Ÿ“–Add content and collect answers
      • ๐ŸคณCapture demographic data
      • โ„น๏ธAdd informational content
      • ๐Ÿ” Review your variable schema
    • ๐ŸŽจCreate a theme
      • Set brand guidelines
    • ๐ŸคนPersonalize your flow
      • ๐ŸŒŸTemplate your variables
      • ๐Ÿง Add conditional logic
      • ๐Ÿ’ซUsing conditional logic with Calculated and API variables
      • ๐Ÿ”šEnd the flow
    • ๐Ÿ”€Set up integrations
    • ๐Ÿš€Go live
      • Auditing your flow for content, functionality, and design
    • ๐Ÿ’ผCommon use cases
      • ๐Ÿ’”Disqualify responders
      • ๐Ÿ—“๏ธAdd a scheduling option
      • ๐Ÿ“„Allow responders to read and accept your company policies
  • ๐Ÿ—๏ธBuilding flows
    • Flows and variants
      • Flow starts
    • Adding content
      • Groups
      • Steps
        • Settings
        • Logic
        • Style
      • Questions
        • General Settings
        • Style
        • Address
        • Comparison
        • Confirmation
        • Date
        • Date & Time
        • Email address
        • File upload
        • Grid choice
        • Iframe
        • Image upload
        • Number
        • Payment
        • Phone number
        • Postal code
        • Question group
        • Region
        • Select
          • Providing choices via API or calculation
        • Signature
        • SSN
        • Text questions
        • Yes/No
      • Content
        • General Settings
        • Statement
        • Image
        • Video
        • Next button
        • Divider
        • Map
      • Endings
      • Using markdown
      • Using variable templating
        • Template formatting functions
      • Copy-pasting form content
      • Content library
    • Conditions and logic
      • Logical operator reference
      • Advanced logic
    • Variables (answers)
      • Variables from questions
      • Externally provided variables
      • Calculated variables
      • API lookups
      • System Library variables
      • Orphaned variables
    • Schemas
      • JSON Schemas
      • Validating flow schemas
    • Redirects
    • Styling and themes
      • CSS Reference
      • Overriding theme styling
      • Custom CSS overrides
      • Content area
      • Animations and transitions
      • Form Layout
      • Typography
        • Adobe Fonts
        • Hosting custom fonts
      • Color variables
      • Dimension variables
      • Question containers
      • Assets
      • Form Buttons
        • Select buttons
      • Inputs and dropdowns
      • Checkmarks
      • Tables
      • Sliders
      • Divider lines
      • Progress bar
      • Comparison cards
    • Variant settings
      • Form behavior for returning responders
      • Group ranking API
    • Publishing and versions
      • Preview window
      • Deploying
      • History
  • ๐Ÿ’พHandling data
    • Philosophy and data retention policy
    • Viewing form answers
    • Responder UUIDs
    • Environments
      • Loading different environments
    • Passing data in
      • URL parameters
      • POST body
      • Embed query parameters
    • Custom validators
    • Form answers and events
      • Analytics events
      • Signed requests
      • Event payload shape
      • Submission frequencies
      • Runtime error reporting
      • Admin API
      • Flow content data format
    • Integration reference
      • Amplitude
        • Amplitude cross domain tracking
      • BigQuery
      • FullStory
      • Google Analytics
        • Updating from Universal Analytics to GA4
      • Google Cloud Storage
      • Google Sheets
      • Google Tag Manager (GTM)
        • JavaScript triggered by flow events
      • Hubspot
      • Jornaya
      • Optimizely
      • PostgreSQL
      • Redshift
      • Rudderstack
      • S3
      • Salesforce
      • Segment
        • Segment Setup
        • Segment cross domain tracking
      • Stripe
      • TrustedForm
      • Webhooks
        • Zapier
  • ๐Ÿ“บGoing live
    • Custom domains
    • Built-in analytics
    • Embedding
      • Web-Embed API
        • React-embed
      • Adding authentication
      • Embedding forms in iOS and Android
      • Setting up a dev environment
    • Split testing
    • Preflight checklist
  • ๐ŸขTeams
    • Accounts
      • Roles and permissions
    • Events subscriptions
    • Workspace domain detection
Powered by GitBook
On this page
  • Authentication
  • Frequency
  • Security
  • Table schema
  • Schema definition in JSON
  • Schema definition in python
  • Adding multiple BigQuery instances

Was this helpful?

  1. Handling data
  2. Integration reference

BigQuery

Stream form answers into Google BigQuery.

PreviousAmplitude cross domain trackingNextFullStory

Last updated 1 year ago

Was this helpful?

The integration uses the BigQuery to insert answers into a BigQuery table, as they are received.

BiqQuery billing

The BigQuery streaming API is not available in the Google Cloud's free tier. See .

Authentication

BigQuery is authenticated using a . Once a service account is created, export the credentials as a keyfile, and upload the keyfile to Formsort.

Note that the service account needs the minimum permissions necessary to have the ability to write data into the user-created table, such as the predefined role.

Frequency

The frequency of inserting a row to BigQuery can be configured as:

  • On Finalize: only at the end of the flow.

  • On Savepoint: after each step marked as save point, and at the end of the flow.

  • Every step: at the end of each step (when the responder advances using the Next button), and at the end of the flow.

  • Debounced: when the responder abandons the flow after a period of inactivity, and at the end of the flow. Formsort recommends using this setting to reduce the load.

Security

Formsort backend system will exclusively connect to BigQuery from the static IP 18.217.92.196.

Table schema

Since the schema of a form flow can change between different deployments, as new questions are added and removed, the schema for writing to a BigQuery table has a repeated answers field that stores the answer values received.

Field name

Type

Mode

Description

submitted_at

TIMESTAMP

REQUIRED

UTC timestamp of when the answers were received by Formsort.

responder_uuid

STRING

REQUIRED

flow_label

STRING

REQUIRED

The flow label.

variant_label

STRING

REQUIRED

The variant label.

variant_revision_uuid

STRING

REQUIRED

The variant revision UUID under which these answers were collected.

event_type

STRING

REQUIRED

This is currently either StepCompleted, or FlowFinalized.

answers

RECORD

REPEATED

Fields:

  • key

    • STRING, REQUIRED

    • The answer variable label

  • value

    • STRING, REQUIRED

    • The value of the answer, as a string

  • type

    • STRING, NUMERIC, or BOOLEAN

schema_version

NUMERIC

REQUIRED

The version of the BigQuery adapter in use.

Currently at 2

Schema definition in JSON

To create the table within the BigQuery console, we recommend you copy-paste the following JSON schema using the Edit as text toggle:

[
    {
        "name": "submitted_at",
        "type": "TIMESTAMP",
        "mode": "REQUIRED"
    },
    {
        "name": "responder_uuid",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "flow_label",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "variant_label",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "variant_revision_uuid",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "event_type",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "answers",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "key",
                "type": "STRING",
                "mode": "REQUIRED"
            },
            {
                "name": "value",
                "type": "STRING",
                "mode": "REQUIRED"
            },
            {
                "name": "type",
                "type": "STRING",
                "mode": "REQUIRED"
            }
        ]
    },
    {
        "name": "schema_version",
        "type": "NUMERIC",
        "mode": "REQUIRED"
    }
]

Schema definition in python

from google.cloud import bigquery

schema = [
    bigquery.SchemaField("submitted_at", "TIMESTAMP", mode="REQUIRED"),
    bigquery.SchemaField("responder_uuid", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("flow_label", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("variant_label", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("variant_revision_uuid", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("event_type", "STRING", mode="REQUIRED"),
    bigquery.SchemaField(
        "answers",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("key", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("value", "STRING", mode="REQUIRED"),
            bigquery.SchemaField("type", "STRING", mode="REQUIRED"),
        ],
    ),
    bigquery.SchemaField("schema_version", "NUMERIC", mode="REQUIRED"),
]

Adding multiple BigQuery instances

To enable multiple destinations, first complete setup for an initial destination (Destination 1). Then, click the "+ Add destination" button. Up to 3 instances can be added.

Once you've finished configuring all instances of your BigQuery integrations, be sure to Save your work with the button in the top right corner.

We take your security seriously, and require only the minimum permissions necessary to write to your BQ table (see ). Because of this, you must create the table to which Formsort can write - Formsort will not create the table automatically.

The .

Which this payload corresponds to.

There is an option to send answer payloads to multiple BigQuery destinations. This is useful if you'd like to share your data across multiple endpoints, or have payloads that are sent at different go to different destinations.

If you have deployed flows previous to integrating with or updating your BigQuery integration, it is advisable to re- those flows.

๐Ÿ’พ
Google BigQuery
streaming API
pricing
service account
bigquery.dataEditor
submission frequencies
deploy
Authentication
responder uuid
analytics event