Tutorial

Survey Data to Google Sheets

Overview
Before you start
Step 1: Find the survey you want to report on
Step 2: List that survey's questions in order
Step 3: Let BigQuery write the column list for you
Step 4: Build the one-row-per-response report
Step 5: Put it in a Google Sheet
Handling every kind of answer
Variations
Troubleshooting
Related documentation

Overview

This tutorial turns survey responses in your data warehouse into the spreadsheet people actually want: one row per response, one column per question, columns in the order the survey asks them.

That takes a little SQL because of how the data is stored. Each submission is one row in uc_surveys, and the questions and answers live inside a repeated questions field on that row — a nested array, not columns. Query the table directly and you get one row per answer, or an unreadable array in a single cell. The queries below flatten that array and pivot it back into named columns.

The other thing to know up front: every survey has a different set of questions, so a report like this is always scoped to one survey. Building "all surveys" into one sheet would put the meeting-request form's questions and the post-checkout questionnaire's questions in the same columns.

By the end you will have:

A query that lists every survey in your account and how many responses each has
A query that lists one survey's questions in the order it asks them
A generator query that writes the report's column list for you
The finished report, connected to a Google Sheet that refreshes on a schedule

Before you start

You need Level 3 – Medium data warehouse access, and the ability to connect a sheet to BigQuery.

Level 3 – Medium (or Level 4 – High) access. Survey answers are PII, so readable answers exist only in the ultracart_dw_medium and ultracart_dw_high datasets. At Level 1 (ultracart_dw) and Level 2 (ultracart_dw_low), PII comes through in hashed form only: uc_surveys still lists the survey's questions and carries hashed identity columns like email_hash, but you cannot read what a shopper typed. Every query in this tutorial therefore uses ultracart_dw_medium. The account owner grants levels under Configuration → Account & Users → Users; see Providing Users Access to the Data Warehouse.
Your data warehouse project ID, from Configuration → Developer → Data Warehouse (BigQuery). It follows the pattern ultracart-dw-{merchantid}. Every query below uses that placeholder — replace {merchantid} with your merchant ID in lowercase.
A working Sheets connection. Data Warehouse (BigQuery) to Google Sheets covers connecting, refreshing, and extracting. This tutorial supplies the SQL that goes into it.
A survey that is collecting responses. Adding a Survey to StoreFronts covers building one.

warning

A finished survey report contains whatever your shoppers typed — names, email addresses, phone numbers, free-text comments. The sheet inherits none of your UltraCart permissions: anyone you share it with can read all of it. Share the sheet as carefully as you would share the underlying data, and prefer building reports that leave out the columns a given audience does not need.

Step 1: Find the survey you want to report on

Run this in the BigQuery console to see every survey that has responses, and pick the one you want:

SELECT
  widget_id,
  uri,
  ANY_VALUE(survey_name) AS survey_name,
  ANY_VALUE(survey_type) AS survey_type,
  COUNT(*) AS responses,
  MIN(survey_dts) AS first_response,
  MAX(survey_dts) AS last_response
FROM `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_surveys`
GROUP BY widget_id, uri
ORDER BY responses DESC

Each row is one survey element on one page. The columns that identify a survey:

Column	What it is
`widget_id`	The ID of the Survey element on the page, for example `surveyjs-4765322`. This is the most reliable key — it stays the same no matter which page the element appears on.
`uri`	The page path the response was submitted from. The same survey element placed on two pages produces two `uri` values.
`survey_name`	The Name field in the Survey element settings. Often empty, because the field is optional.
`survey_type`	`Marketing` or `PII`, from the element's Type setting.

note

survey_name is blank unless someone filled in the Name field in the Survey element settings, so most accounts identify surveys by widget_id. Filling in that Name makes every query in this tutorial easier to read — worth doing before you build the report.

Note the widget_id of the survey you want. Every remaining query filters on it.

Step 2: List that survey's questions in order

Two fields determine the order a survey asks its questions: page_number, then questionPosition within that page. This query lists the survey's questions in exactly that order, which is the order your spreadsheet columns should follow:

SELECT
  q.page_number,
  q.page_name,
  q.questionPosition AS position,
  q.question_name,
  q.question AS question_text,
  q.question_type,
  COUNT(*) AS times_answered
FROM `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_surveys` s
CROSS JOIN UNNEST(s.questions) q
WHERE s.widget_id = 'surveyjs-0000000'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY q.page_number, position

CROSS JOIN UNNEST(s.questions) is what flattens the nested array — it turns one response with nine questions into nine rows. You will use it in every query from here on.

The two name fields do different jobs, and the difference matters:

question_name is the SurveyJS name from the survey's JSON definition — firstName, additionalParticipants. It is stable, so queries key on it.
question is the title shown to the shopper — "First Name", "Anyone else joining?". It is what you want as a column heading, and it can be reworded at any time without breaking your report.

times_answered is worth reading closely. If a question shows a lower count than the others, one of two things is true: the question is optional and some people skipped it, or it was added to the survey partway through its life. Both are normal, and both are handled — the report keys on question_name, so a response that never answered a question simply gets an empty cell.

Step 3: Let BigQuery write the column list for you

The report needs one MAX(IF(...)) expression per question, in survey order. Rather than hand-typing them, have BigQuery generate them:

SELECT STRING_AGG(
  FORMAT("  MAX(IF(question_name = '%s', answer, NULL)) AS %s", question_name, column_name),
  ',\n' ORDER BY page_number, position
) AS generated_columns
FROM (
  SELECT
    q.question_name,
    LOWER(REGEXP_REPLACE(q.question_name, r'[^a-zA-Z0-9]+', '_')) AS column_name,
    MIN(q.page_number) AS page_number,
    MIN(q.questionPosition) AS position
  FROM `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_surveys` s
  CROSS JOIN UNNEST(s.questions) q
  WHERE s.widget_id = 'surveyjs-0000000'
  GROUP BY q.question_name, column_name
)

The single result cell contains the column list, already in page-and-position order:

  MAX(IF(question_name = 'firstName', answer, NULL)) AS firstname,
  MAX(IF(question_name = 'lastName', answer, NULL)) AS lastname,
  MAX(IF(question_name = 'email', answer, NULL)) AS email,
  MAX(IF(question_name = 'phone', answer, NULL)) AS phone,
  MAX(IF(question_name = 'extension', answer, NULL)) AS extension,
  MAX(IF(question_name = 'organization', answer, NULL)) AS organization,
  MAX(IF(question_name = 'jobTitle', answer, NULL)) AS jobtitle,
  MAX(IF(question_name = 'industry', answer, NULL)) AS industry,
  MAX(IF(question_name = 'additionalParticipants', answer, NULL)) AS additionalparticipants

Copy that block. The generator covers every question the survey has ever asked, so questions that were added or retired mid-life all get a column.

Rename the aliases to whatever you want the spreadsheet headings to say — AS first_name, AS anyone_else_joining. The alias is the column heading in the sheet; only the question_name inside the IF has to match the survey.

Step 4: Build the one-row-per-response report

Paste the generated columns into this query. The answers CTE flattens the response into one row per answer, and the outer SELECT pivots those rows back into one row per response:

WITH answers AS (
  SELECT
    s.survey_uuid,
    s.survey_dts,
    s.uri,
    s.email,
    q.question_name,
    COALESCE(
      q.single_answer.answer,
      (SELECT STRING_AGG(a.answer, ', ' ORDER BY a.answer) FROM UNNEST(q.multiple_choice_answers) a),
      (SELECT STRING_AGG(f.file_name, ', ') FROM UNNEST(q.file_answers) f)
    ) AS answer
  FROM `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_surveys` s
  CROSS JOIN UNNEST(s.questions) q
  WHERE s.widget_id = 'surveyjs-0000000'
    AND s.survey_dts >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 180 DAY)
)
SELECT
  DATETIME(TIMESTAMP(survey_dts), 'America/New_York') AS submitted_est,
  uri,
  -- Paste the generated column list here:
  MAX(IF(question_name = 'firstName', answer, NULL)) AS first_name,
  MAX(IF(question_name = 'lastName', answer, NULL)) AS last_name,
  MAX(IF(question_name = 'email', answer, NULL)) AS email,
  MAX(IF(question_name = 'phone', answer, NULL)) AS phone,
  MAX(IF(question_name = 'extension', answer, NULL)) AS extension,
  MAX(IF(question_name = 'organization', answer, NULL)) AS organization,
  MAX(IF(question_name = 'jobTitle', answer, NULL)) AS job_title,
  MAX(IF(question_name = 'industry', answer, NULL)) AS industry,
  MAX(IF(question_name = 'additionalParticipants', answer, NULL)) AS additional_participants
FROM answers
GROUP BY survey_uuid, survey_dts, uri
ORDER BY survey_dts DESC

What each piece is doing:

GROUP BY survey_uuid is what collapses the answers back into one row per response. survey_uuid is the unique ID of the submission, so grouping on it guarantees one spreadsheet row per person who filled out the form.
MAX(IF(question_name = '…', answer, NULL)) picks that question's answer out of the group. Every other answer in the group is NULL for that expression, and MAX ignores NULL, so what survives is the one answer. A skipped question stays empty.
The order of the SELECT list is the order of the columns in your sheet. That is the whole reason for Steps 2 and 3.
survey_dts is the submission timestamp, in UTC, like every timestamp in the warehouse. DATETIME(TIMESTAMP(survey_dts), 'America/New_York') shifts it to Eastern; change the zone to whatever your team reads.
The 180-day filter keeps the query cheap. Widen or drop it as needed — but every refresh of the sheet re-runs this query, so filter to the window you actually report on.

note

Filter dates on survey_dts, not partition_date. partition_date is an internal storage bucket that does not line up with the day a response was submitted, so using it as a date filter silently drops rows.

Run it, confirm the columns look right, then move it into Sheets.

Step 5: Put it in a Google Sheet

Follow Data Warehouse (BigQuery) to Google Sheets, pasting the Step 4 query as the custom query. In short:

In a new Google Sheet, choose Data → Data connectors → Connect to BigQuery.
Select your ultracart-dw-{merchantid} project.
Click Saved queries and query editor, paste the query, and insert the results.
Click Refresh options and set a schedule — daily is plenty for most surveys.
Click Extract to drop the results into a plain sheet you can chart, filter, and share.

The connected sheet previews 500 rows; pivot tables, charts, and formulas built on it use the whole result set.

Handling every kind of answer

The COALESCE in the Step 4 query already handles all three ways an answer can be stored, so most surveys need no changes. What it is choosing between:

Field	Holds	Example question
`single_answer.answer`	One answer	Text input, long-form comment, single-choice question
`multiple_choice_answers[]`	Several answers, one array entry each	"Select all that apply"
`file_answers[]`	Uploaded files: `file_name`, `mime_type`, `file_uuid`	File upload

COALESCE returns the first of the three that is populated, so a single-answer question yields its text, a multi-answer question yields Email, Phone, Text message in one cell, and a file upload yields the file names.

Multi-answer questions in their own columns

One cell containing Email, Phone is fine for a spreadsheet a human reads, but awkward to count. If you want to tally how often each choice was picked, add a flag column per choice instead:

  MAX(IF(question_name = 'preferredContact',
         IF('Email' IN UNNEST(SPLIT(answer, ', ')), 1, 0), NULL)) AS prefers_email,
  MAX(IF(question_name = 'preferredContact',
         IF('Phone' IN UNNEST(SPLIT(answer, ', ')), 1, 0), NULL)) AS prefers_phone

Summing those columns in the sheet gives you a clean count per choice.

File uploads

file_name is what the Step 4 query returns. If you need to identify the stored file, pull file_uuid as well:

  (SELECT STRING_AGG(f.file_uuid, ', ') FROM UNNEST(q.file_answers) f) AS file_uuids

Add it inside the answers CTE alongside answer, then surface it with the same MAX(IF(...)) pattern.

Quiz-style surveys

If your survey defines correct answers, each response carries correct_answer_count and incorrect_answer_count, and each question carries correct. Add the counts to the outer query as grouping columns:

GROUP BY survey_uuid, survey_dts, uri, correct_answer_count, incorrect_answer_count

carrying them through the answers CTE first.

What the shopper was offered

visible_choices[] records the choices actually displayed for a question, which is how you tell "picked nothing" apart from "was never shown the question" on surveys that use conditional logic.

Variations

One row per answer, for pivot tables

If you would rather pivot inside Sheets than in SQL — or you want one query that works across every survey at once — return the long form and let a pivot table do the shaping:

SELECT
  s.survey_uuid,
  DATETIME(TIMESTAMP(s.survey_dts), 'America/New_York') AS submitted_est,
  s.widget_id,
  s.uri,
  q.page_number,
  q.questionPosition AS position,
  q.question AS question_text,
  COALESCE(
    q.single_answer.answer,
    (SELECT STRING_AGG(a.answer, ', ' ORDER BY a.answer) FROM UNNEST(q.multiple_choice_answers) a),
    (SELECT STRING_AGG(f.file_name, ', ') FROM UNNEST(q.file_answers) f)
  ) AS answer
FROM `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_surveys` s
CROSS JOIN UNNEST(s.questions) q
WHERE s.survey_dts >= DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 180 DAY)
ORDER BY s.survey_dts DESC, q.page_number, q.questionPosition

This one is survey-agnostic: it never has to know which questions exist. The trade-off is that a response spans several rows, so it reads poorly until you pivot it.

The same survey on several pages

A Survey element placed on more than one page produces one uri per page and shares a widget_id. Keeping uri in the report — as Step 4 does — lets you see which page each response came from. To report on just one page, add AND s.uri = '/contact-us/' to the WHERE clause.

Identifying the respondent

Three columns tie a response to a person, and they are worth adding to any report you plan to act on:

email — populated only when the survey has a question named exactly email. The reserved-name behavior is documented in Adding a Survey to StoreFronts. It always matches the answer to that question, so use s.email and skip pivoting the email question.
ucacid — the anonymous customer ID cookie at submission time; joins against analytics and activity data.
email_hash — a SHA-256 hash of the email, for joining to other tables without moving the address around.

Tying responses to orders

Surveys placed on a receipt, or configured to add an item to the cart, carry order_id and order_item_id. Join to uc_orders to put revenue next to the answers:

LEFT JOIN `ultracart-dw-{merchantid}.ultracart_dw_medium.uc_orders` o
  ON o.order_id = s.order_id

Add whatever order fields you need to both the SELECT and the GROUP BY.

Troubleshooting

Symptom	Cause and fix
Questions appear but the answers are empty or hashed	You are querying `ultracart_dw` or `ultracart_dw_low`, where PII is available in hashed form only. Readable answers require `ultracart_dw_medium` or `ultracart_dw_high`.
Access denied on `ultracart_dw_medium`	Your data warehouse level is below Level 3 – Medium. The account owner raises it under Configuration → Account & Users → Users.
Columns from unrelated surveys in one report	The `WHERE s.widget_id = '…'` filter is missing or matches more than one survey. Re-run Step 1 to confirm the ID.
One response spread across many rows	The `GROUP BY survey_uuid` is missing, so you are looking at the flattened `answers` CTE rather than the pivoted result.
A column is entirely empty	The `question_name` in the `IF` does not match the survey's JSON. Re-run Step 2 — `question_name` is case-sensitive and is the `name`, not the `title`.
A recently added question has no column	The generator in Step 3 was run before the question existed. Re-run it and paste the new list.
Responses are missing from a date range	The filter is on `partition_date` rather than `survey_dts`. Only `survey_dts` is the submission time.
A new response is not in the sheet	The warehouse streams changes in within 1–2 minutes, but the sheet only shows what its last refresh returned. Refresh it manually to check.

Data Warehouse (BigQuery) to Google Sheets — connecting, scheduled refresh, and extracts
Adding a Survey to StoreFronts — building the survey and its reserved question names
Data Warehouse (BigQuery) — permission levels, datasets, pricing, and more sample queries
AI-Powered Report Builder — ask questions of the same data in plain English

Was this page helpful?

Table of Contents​

Overview​

Before you start​

Step 1: Find the survey you want to report on​

Step 2: List that survey's questions in order​

Step 3: Let BigQuery write the column list for you​

Step 4: Build the one-row-per-response report​

Step 5: Put it in a Google Sheet​

Handling every kind of answer​

Multi-answer questions in their own columns​

File uploads​

Quiz-style surveys​

What the shopper was offered​

Variations​

One row per answer, for pivot tables​

The same survey on several pages​

Identifying the respondent​

Tying responses to orders​

Troubleshooting​

Related documentation​

Table of Contents