How to use BigQuery with Pollfish data

Pollfish’s massive audience means a potentially massive amount of data to be analyzed. For clients running higher-volume surveys, our Elite plan often works best, and now it includes the ability to integrate Pollfish data directly with Google’s BigQuery database for easier, faster data analysis. 

integrate-bigquery-pollfish

 

What is BigQuery?

Bigquery is Google’s answer to cloud-based data storage. It’s especially beneficial to those looking to segment and analyze large quantities of data rapidly. 

The “serverless” model saves on set-up costs associated with complete server systems as well as offers high-speed updates and more opportunities to integrate with existing BI tools. BigQuery also uses a columnar storage design that makes it easy to run SQL queries with their AI optimizing the datasets in nearly real-time.

There’s no question that this is a massive aid to researchers working with high volume datasets who can’t risk their information becoming outdated in the time that it takes to process it properly. 

Benefits of integrating BigQuery with Pollfish

BigQuery is where researchers can store, segment, and analyze their data from anywhere, but where they pull that information from is a different story. The tool makes this easy by offering the ability to integrate with data collection and analytics tools like Tableau, Looker, Data Studio, and most recently— Pollfish! 

The Pollfish <> BigQuery integration allows us to push all existing and future survey data from your Pollfish account to your Google BigQuery database in real-time, including the survey demographics, questionnaire content, respondent profiles, and actual responses. 

The implementation includes a ready-to-use query view that can be instantly applied to easily view and segment your Pollfish data. 

What this means is that our Elite clients who use BigQuery can connect their Pollfish data directly to their database. 

How to connect BigQuery and Pollfish

Please note that you must have a billing account with BigQuery and be on an Elite plan with Pollfish in order to integrate. Click here to visit BigQuery pricing.

      1. Begin by logging into your Google Cloud Platform account, or creating one if you do not have one yet.
      2. Go to “Console” and create or select the project that you want to use.bigquery-pollfish-project-id
      3. From the left navigation pane, select APIs & Services—> Credential
      4. Once the Credentials page is displayed, select the Create credentials tab and choose Service Account Key from the listed options.select-service-account
      5. Add a name for the service account. In our example, the account is “pollfish-bigdata-exporter.”
      6. Assign the role BigQuery—>BigQuery Data Owner to the account.
      7. Click “Create” and the service account file will be created and downloaded to your computer.bigquery-assign-data-owner
      8. For the last step, log into Pollfish in order to set up the integration.
          • Go to MyAccount-> Integrations
          • Upload the service account file (JSON) that you downloaded from your account in Google Cloud Platform in step 7.
          • Enter a Dataset Name (allowed characters are letters, numbers, and underscores.)
          • Select if you want to export all historical data from all your surveys or the data from new surveys only.

bigquery-add-json

Keep in mind that once you configure the integration you cannot edit it. However, you are able to delete it and create a new one from scratch if you need to make changes.

How to use Pollfish data in BigQuery

When your Pollfish surveys are exported, they will appear in seven BigQuery views: question, question_answer, respondent, result, result_scorepoint, scorepoint, and survey. Each view describes an element of the survey and the collected responses. 

Note that these are de-duplicated views of the original tables that hold the data. They are shown with the same table names but start with an underscore. Do not use the tables as the tables may contain duplicates. Only use the views for your queries.

In addition, the data is generated in a single view (survey_view) showing a flat representation of the complete dataset. You may see the query that creates this view using the BigQuery UI, which may help you create your own queries in the tool of your preference, such as Tableau, Metabase, or others. 

Below is a diagram showing the relationship between the entities of the exported database.

sql-table

Pollfish<>BigQuery Integration FAQs

Some of the most frequently asked questions about setting up, troubleshooting, and integrating BigQuery with your Pollfish account can be found here. If you have additional questions or need assistance, feel free to reach out to support for more guidance.

  • What will happen when I activate the BigQuery Sync?
Once the sync is activated, all surveys that start from the point onwards will be exported to BigQuery.
  • Can I get all my past surveys in BigQuery?
When you activate BigQuery, you’ll have the option to export all of your finished surveys up until that date. From that point forward, your future surveys will be exported to BigQuery.
  • Can I export a specific survey?
If you want to export a specific survey, support is happy to help. Reach out via chat to request assistance to export a specific survey.
  • What will happen if a survey is exported more than once?
If a survey is exported more than once, it will overwrite the previous survey data.
  • Can I see partial results of a survey currently in progress?
You can see partial results of a survey in progress if it began after you activated BigQuery on your account and the survey has not yet completed. If the survey was started before activation, partial results will not be visible.
  • I can’t see the results of my survey in BigQuery.
If you can’t see the results of your survey in BigQuery, verify that the survey started after the activation time. If you can’t see the results of an old survey, check that you have activated the option to receive all historical data.
  • How are surveys stored in BigQuery?
All surveys reside on the seven tables, explained above in the detailed documentation. The tables constitute a relation structure to avoid data duplication and share the same name as the views in BigQuery.
  • Where can I find all my surveys in BigQuery?
When you activate BigQuery, your service ID will be configured. All surveys are then stored under your service id.
  • What if I want to change my BigQuery service ID?
You are able to change your BigQuery service ID, although we don’t recommend this. If you want to change your BigQuery service ID you will need to reactivate the BigQuery sync in Pollfish. The data will be configured as part of the new activation/reconfiguration to your new service ID.
  • Do I need to enable billing in BigQuery?
Yes, you need to enable billing in BigQuery. You won’t be charged until you have reached a volume of data that is specified on the BigQuery configuration.

How do I get BigQuery on my Pollfish plan?

Pollfish only offers the BigQuery integration on Elite plan. Elite clients enjoy this feature at no additional cost, as well as a variety of other benefits tailored to high-volume client needs, such as additional seats for team members, lower CPIs, and dedicated account management. 

To learn more about Pollfish service plans, visit our pricing page to compare options and choose the right fit for you. To upgrade your account to an Elite plan, contact sales@pollfish.com.