Skip to main content
Last updated: 3 Jun 2021

View or extract feedback data in BigQuery

You can do the following with the feedback data in BigQuery:

  • use Google Data Studio to present the data
  • extract the data from BigQuery into another file format, for example a CSV or a Google Sheets file

You do not need a specific permission to view or extract feedback data in BigQuery once you have access to the BigQuery data sets. To access the BigQuery data, contact the GDS Data Labs team on the #govuk-data-labs Slack channel.

BigQuery feedback data comes from SmartSurvey and Zendesk.

If you want to view or extract Zendesk feedback data, you should first reformat this data.

You do not need to reformat SmartSurvey data.

Reformat Zendesk data

Zendesk data in BigQuery is in a different format to the SmartSurvey data, and contains the following:

  • feedback data
  • Zendesk tickets raised by other Government departments

You should reformat the Zendesk feedback data and remove Zendesk tickets before you view or analyse that data.

You do this by running a SQL query in BigQuery to:

  • change the data format to the same format as SmartSurvey
  • rename some data fields
  • get the URL that the feedback was left on
  • exclude Zendesk tickets raised by other Government departments by excluding unwanted URLs

Once you have run this SQL query, the Zendesk feedback data will be ready to view or extract for analysis.

  1. Go to the GOV.UK BigQuery project.
  2. Select QUERY TABLE.
  3. Run the following SQL code in the code window:

    SELECT
            cast(created_at AS TIMESTAMP) AS created_at,
            'Ticket' AS source,
            REPLACE(concat('www.gov.uk',REPLACE(subject,'Named contact about ','')),'Named contact','') AS URL,
            '' AS Q1,
            '' AS Q2,
            description AS Q3,
            '' AS Q4,
            '' AS Q5,
            '' AS Q6,
            '' AS Q7,
            '' AS Q8
    FROM `govuk-bigquery-analytics.zendeskdata.zendesk_YYYYMMDD`
        WHERE LOWER(description) NOT LIKE '%govuk.zendesk.com%'
        AND LOWER(URL) NOT LIKE '%verify%'
        AND LOWER(URL) NOT LIKE '%gov.ukneeded%'
        AND LOWER(URL) NOT LIKE '%gov.uknotify%'
        AND LOWER(URL) NOT LIKE '%gov.ukreply%'
        AND LOWER(URL) NOT LIKE '%gov.ukurgent%'
        AND LOWER(URL) NOT LIKE '%gov.ukfeedback%'
        AND LOWER(URL) NOT LIKE '%vulnerable data transfer%'
        AND LOWER(URL) NOT LIKE 'www.gov.uk/templates/%'
        AND LOWER(URL) NOT LIKE '%content change request%'
        AND LOWER(URL) NOT LIKE '%gov.ukdaily%'
        AND LOWER(URL) NOT LIKE '%gov.ukgoogle%'
        AND LOWER(URL) NOT LIKE '%enquiries to GDS%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukwa%'
        AND LOWER(URL) NOT LIKE '%www.gov.uk[%'
        AND LOWER(URL) NOT LIKE '%ukre%'
        AND LOWER(URL) NOT LIKE '%www.gov.uk/dvlaforms%'
        AND LOWER(URL) NOT LIKE '%www.gov.uk/sign-in-universal-credit%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukQuery%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukUser%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukSupport%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukGOV.UK%'
        AND LOWER(URL) NOT LIKE '%www.gov.ukTechnical%'
        AND LOWER(description) NOT LIKE '%unsubscribe@digital.cabinet-office.gov.uk%'
        AND LOWER(description) NOT LIKE '%mailto%'
        AND LOWER(description) NOT LIKE '%digital.cabinet-office.gov.uk%'
        AND (CHAR_LENGTH(TRIM(description)) < 50000 OR description IS NULL)
    

    where YYYYMMDD is the date you want data for.

    You can use wildcards to specify time periods of more than one day. For example:

    • zendesk_202103* reformats all data from March 2021
    • zendesk_2021* reformats all data from 2021
  4. When the query has finished running, save the reformatted data in the appropriate format.

The Zendesk feedback data is ready to view or extract for analysis.

View or extract feedback data

In Google Data Studio, you use data connectors to connect to your data.

You can either use an existing data connector or create a new connector using the data connector wizard.

The following content assumes that you need to create a new connector.

  1. Go to Google Data Studio. If you do not have a Data Studio account, select Create to create an account.

  2. Select Connect to data to set up a new connector. If you want to use an existing connector, select My data sources.

  3. Select the BigQuery connector. Select Authorise if needed.

  4. In the BigQuery connector page, select either Recent projects or My projects, and then select the GOV-UK BigQuery analytics project.

    If you cannot see the GOV-UK BigQuery analytics project, contact the GDS Data Labs team on the #govuk-data-labs Slack channel.

  5. Select either the zendeskdata data set or the UISData data set.

  6. Select the table that ends in YYYYMMDD. This is the overall data set that contains all of the individual daily data sets for either Zendesk or SmartSurvey.

  7. Select Add and then Add to report to go to the data dashboard tool.

  8. Build your data dashboard using the data dashboard tool. Note that DataStudio automatically converts some string data fields into non-string data fields. This should not cause any errors.

  9. Select View to view the data dashboard that you’ve built.

  10. Hover your cursor over the data dashboard, and then select the dashboard’s hamburger menu.

  11. Select one of the following options to extract the data to the appropriate format:

    • Download CSV
    • Download CSV (Excel)
    • Export to Sheets

    You should always manually check your data for any personally identifiable information (PII) that did not get cleansed automatically.

Coronavirus and Brexit reporting

There are 2 automated daily reports on Coronavirus and Brexit. Every day at 8:30am, 2 automated SQL queries run on the SmartSurvey and Zendesk data in BigQuery. These queries:

  • reformat the Zendesk data in the same way as the manual reformatting process
  • combine the SmartSurvey and Zendesk data into a single data set
  • filter the data set based on a list of URLs agreed by the Coronavirus and Brexit teams
  • output the data set into 2 reports in Google Sheets

If there are any issues with one of these reports:

  • the product team that uses the report contacts the Insights team
  • the Insights team investigates and contacts the Data Labs team if required
  • a tech lead or data engineer in the Data Labs team investigates and resolves the issue

For more information, see the following locations: