🎉 Celebrate with 10% OFF on All Products! Use Code: GFEX10 Or Buy Any 2+ Items for 30% OFF with Code: GFEX30!

Gravity Forms BigQuery Premium Add-on Setup Guide

Updated 8 min read Gravity Forms Integrations A–Z
On this page
  1. Video walkthrough
  2. What you need
  3. Install and activate
  4. Connect your Google Cloud project
  5. How the key is kept
  6. Create a feed for a form
  7. How the columns are built
  8. Choosing the columns yourself
  9. When you change the form
  10. How sending works
  11. Send entries again
  12. Good to know
  13. Troubleshooting
  14. Frequently asked questions
  15. Do I need a Google library or Composer?
  16. Can I send to more than one table?
  17. Does it work with multisite or several Google projects?
  18. What happens if my license expires?

Video walkthrough

Gravity Forms BigQuery Add-On Setup Guide: Service Account Key and Feed (WordPress Side)
Video setup guide: Gravity Forms BigQuery Watch on YouTube

The Gravity Forms BigQuery Premium Add-on sends every entry of a form to a table in your own Google BigQuery project, as one row per entry. The columns are built from the fields of the form, so you do not write a schema by hand, and the table is created for you the first time an entry arrives. The add-on signs in to Google with the service account key that you create in your own Google Cloud project. No Google library is added to your site, and nothing is sent to GravityExtra.

Use it when the answers of your forms belong in a data warehouse next to your sales, ads and product data, for reports, dashboards or machine learning, without a separate automation service in between.

What you need

  • WordPress 5.8 or newer and PHP 7.4 or newer, with the OpenSSL extension (every normal host has it).
  • Gravity Forms 2.5 or newer. The add-on is tested with Gravity Forms 3.1.2 on PHP 8.3 and Gravity Forms 2.9.8 on PHP 7.4.
  • An active license key for this add-on.
  • A Google Cloud project with billing turned on. Google does not offer streaming inserts in its free tier, so a project without billing cannot receive rows.
  • A BigQuery dataset in that project, and a service account that may write to it.

Install and activate

  1. Install and activate the add-on like any plugin, after Gravity Forms.
  2. Open Forms → Settings → BigQuery and enter your license key.

Connect your Google Cloud project

You do this once. The steps are in the Google Cloud console:

  1. Create a dataset in BigQuery (for example web). Pick the data location you want; the add-on creates tables inside this dataset.
  2. Open IAM & Admin → Service Accounts and create a service account, for example forms-writer.
  3. Give it the role BigQuery Data Editor on the dataset (or on the project). The role lets it read the dataset, create tables, add columns and insert rows. If you create the tables yourself and turn table maintenance off, a role that can only insert rows is enough.
  4. Open the service account, choose Keys → Add key → Create new key → JSON. A file is downloaded. Keep it safe: it is a password.
  5. In WordPress, open Forms → Settings → BigQuery. Enter the Project ID and the Dataset, open the downloaded file in a text editor, copy everything and paste it into the Service account key (JSON) box. Save.
  6. Click Test the connection. The add-on signs in to Google and reads the dataset. It does not write anything.
The BigQuery settings screen in Gravity Forms: project, dataset, the service account key box, the stored key owner and the Test the connection button
The BigQuery settings.

How the key is kept

When you save, the add-on checks that the text is a service account key, then stores it encrypted with a key made from the secret salts of your site. The plain text is not kept anywhere: not in the settings, not in a log, not in an entry note. The screen shows only the email address of the service account, and the box is empty every time you open it. To replace the key, paste a new file. To remove it, tick Remove the stored key when saving. Because the salts of your site are part of the lock, the key cannot be read from a copy of the database alone. If you move the site to another server, keep the same salts in wp-config.php, or paste the key again.

Create a feed for a form

Open the form, go to Settings → BigQuery and add a feed. A form can have several feeds, for example one table for all entries and one for the entries that match a condition.

A BigQuery feed in the form settings: name, table, dataset, columns from the form or chosen by you, entry details and table maintenance
A BigQuery feed.
  • Name: a label for you.
  • Table: the table that gets one row per entry. Letters, numbers, underscores and dashes.
  • Dataset (optional): leave empty to use the dataset from the add-on settings.
  • Columns: Build the columns from the form fields (the default) or I choose the columns.
  • Name columns by: the field label (readable, for example customer_email) or the field ID (field_2, which never changes when you rename a label).
  • Entry details: extra columns for the entry ID, form ID, date created (UTC), source URL, user ID, payment status, payment amount, transaction ID and IP address. The IP address is personal data and is off by default.
  • Table maintenance: create the table when it is missing and add columns for new fields, and send a row ID so that a repeated send is dropped.
  • Condition: send only the entries that match.

How the columns are built

In automatic mode every field that holds an answer becomes a column. Sections, HTML blocks, page breaks, CAPTCHA and password fields are skipped. The BigQuery type follows the field:

  • Number, Quantity, Total, Price and Shipping: FLOAT. Currency signs and thousands separators are removed, and the decimal comma format of the field is respected.
  • Date: DATE, read with the date format of the field. A value that is not a real date is sent as empty.
  • Time: TIME, from 12-hour or 24-hour entries.
  • Consent: BOOLEAN.
  • Checkbox and Multi Select: a repeated STRING column, one value per choice that was ticked.
  • File Upload: a STRING with the file address, or a repeated STRING when the field accepts several files.
  • Name and Address: one STRING column per visible part, for example name_first and name_last.
  • The entry details: INTEGER for IDs, TIMESTAMP for the date, FLOAT for the payment amount.
  • Everything else (text, email, drop down, radio, list, hidden and so on): STRING. A List field is sent as JSON text.

Column names are made from the label: lower case, letters, numbers and underscores. Two fields with the same label get _2, _3 and so on. Empty answers are sent as NULL.

Choosing the columns yourself

In I choose the columns mode you write the column name and pick the source: a field, a part of a Name or Address field, or one of the entry details. Only the columns you list are created and sent. Use this when the table already exists with names that your reports depend on.

When you change the form

A new field adds a new column the next time an entry arrives. The add-on only ever adds columns. It never changes the type of an existing column, never renames and never removes one, so your reports keep working. If a column already exists as STRING, the add-on sends text for it. If a field is deleted from the form, its column stays in the table and new rows have NULL there. If you rename a label while columns are named by label, a new column appears; name columns by field ID to avoid that.

How sending works

When someone submits the form, the entry is saved first and the visitor sees the confirmation at once. The row is sent a moment later in the background, so a slow answer from Google never slows the form. If you prefer, tick Send while the visitor waits in the add-on settings.

  • Temporary faults (no answer, a server error, Google asking you to slow down) are tried again after 1 minute, 5 minutes, 30 minutes, 2 hours and 12 hours, six tries in all.
  • Final faults (no permission, a wrong project or dataset, a value the table cannot take, a table that is missing while maintenance is off) are not retried. The reason from Google is written as a note on the entry.
  • When a row is sent, the entry gets a note with the table name. Every entry also keeps its status for each feed (queued, retrying, ok or fail).
  • A column that is missing in the table is added and the row is sent again once. A table that was deleted is created again.

Send entries again

In Forms → Entries, tick the entries and choose Send to BigQuery in the bulk actions. Every active feed whose condition matches is queued for them, up to 500 entries at a time. Use it for entries that arrived before you added the feed, or after you fixed a permission. When the row ID setting is on, Google drops a row that it already received in the last minute or so; older rows are added again, so check before you resend a large batch.

The Gravity Forms entry list with the bulk action Send to BigQuery
Resend entries from the entry list.

Good to know

  • Streaming buffer. Rows can be read at once, but Google keeps new rows in a buffer that can take a while (up to about 90 minutes) before they can be changed or deleted with an UPDATE or DELETE statement.
  • Duplicates. The row ID gives best-effort de-duplication only. For exact-once delivery you would need Google’s Storage Write API, which needs a different protocol and is not used by this add-on. If an exact count matters, de-duplicate in a view on the entry ID column.
  • Changes are not mirrored. Editing, trashing or deleting an entry in WordPress does not change or delete the row in BigQuery. Remove a row there when a person asks to be erased.
  • Cost. Streaming inserts are billed by Google by the amount of data. A form entry is tiny, so the cost is very small, but check the current price list of your project.
  • Where the data goes. The add-on talks only to Google (the sign-in service and the BigQuery interface) over HTTPS with the certificate checked.

Troubleshooting

  • Sign-in failed (invalid_grant): the key was deleted or disabled in Google Cloud, or the clock of your server is more than a few minutes wrong. Create a new key and paste it.
  • Permission denied: the service account lacks a role on the dataset. Give it BigQuery Data Editor on the dataset.
  • Not found: dataset: the project ID or the dataset name is wrong, or the dataset is in another project.
  • Billing: streaming inserts need billing on the project.
  • Nothing arrives: check that the license is active, that the feed is switched on, and that your site runs WordPress scheduled tasks. On a site where visits are rare, add a real cron job that calls wp-cron.php every few minutes, or tick Send while the visitor waits.
  • The note on the entry says a value is invalid: the table has a column with a different type than the field gives. Change the field, or fix the column in BigQuery.

Frequently asked questions

Do I need a Google library or Composer?

No. The add-on signs the Google sign-in request itself with the OpenSSL extension of PHP and calls the BigQuery REST interface through WordPress. Nothing is bundled.

Can I send to more than one table?

Yes. Add several feeds to the form, each with its own table, columns and condition.

Does it work with multisite or several Google projects?

One key and project are set for the whole add-on. Several datasets are possible through the dataset field of each feed. A second project needs a second site.

What happens if my license expires?

The add-on stops sending until the license is active again. Your forms keep working.

See the BigQuery add-on

Still need help?

Our support team answers tickets every day, usually within a few hours.

0
0
Your Cart
Your cart is emptyReturn to Shop
🔥 FREQUENTLY BOUGHT TOGETHER
Complete Combo & Save 40%!
ITEM IN YOUR CART
Gravity Forms Klaviyo Add-on
$39.00/yr
+
🎁 RECOMMENDED ADD-ON SAVE 40% OFF
Original Price: $39.00/yr
Bundle Special Price: $23.40/yr