Importing Survey data

In order to get a complete view of your employees’ experience and its impact on your business, an excellent approach is to intersect qualitative data from employee surveys with quantitative data from your engineering operations. For this we need to capture surveys, questions and responses data.

There are multiple ways to get this data into Faros today:

• Importing from Airtable: you can import survey data from an Airtable base.
• Importing from Google Sheets: you can import survey data from a Google Sheets spreadsheet.
• Importing from AWS S3: same as Google Sheets spreadsheet but from a spreadsheet stored in an S3 bucket.
• Importing from a local spreadsheet: same as with Google Sheets, but from a local spreadsheet .xlsx file. This option is only available using the Airbyte Local CLI.

📘

It is important that each spreadsheet/base imported includes the data for a unique survey. To import several surveys, either add several base ids or sheet ids in the same source, or create one source for each survey.

Importing from Airtable

You can ingest your survey data into Faros from Airtable bases containing views with forms and survey results. The base should contain a table with the metadata related to questions, for example question categories. It should also include the metadata related to the survey, like survey name, description, and type. Here are the steps to follow:

1. Create/Modify the Airtable Base
2. Authenticate your Airtable account
3. Create the Faros Source

Create/Modify the Airtable Base

Your base must contain at a minimum two tables: Survey Responses and Question Metadata. There is an additional Survey Metadata table that can be added (recommended). This third table contains all survey specific data like survey type, start/end dates and status. If not included as a separate table, this data must be contained in the Survey Responses rows. You can use this example base for guidance.

📘

All column and table names can be changed to custom ones. The Faros Airtable Source includes configurations for table and column name mappings. That configuration is explained in the following section.

Survey Responses

Your survey responses table can include the following columns:

📘

Columns not listed in the table above will be interpreted as questions by the Faros Airtable Source. Survey data related columns (Survey Name, Survey Description, Survey Type, Survey Status, Survey Ended At and Survey Started At) are not required in the Survey Responses table if they are already included in the Survey Metadata table.

Question Metadata

Your question metadata table can include the following columns:

Survey Metadata

Optional table for including survey metadata. As explained above, either use this option or include survey metadata as Survey Responses columns.

Authenticate to Airtable

To connect to an Airtable Account you need to create a connection in Faros. The connection supports Personal Access Token and OAuth 2.0.

To generate an Airtable Personal Access token, follow this guide. In order for it to work, configure access to the base(s) where survey data is contained. Also make sure it has these three scopes:

Copy the personal access token generated. You'll need this to create a connection in Faros.

📘

We recommend creating a single token with access to all the bases containing survey data, so you need to create only one Faros Connection.

To use the OAuth2.0 method you can follow this other guide.

Add a Faros Connection

To add the connection simply go into the Connections page (switch to the Setup Pages view, and select Import Data) and add a new connection with a source type of Survey Import from Airtable and paste your personal access token. This can also be done when creating the source.

Create the Faros Source

Now that you have the Airtable base and have given Faros the correct permissions, you are ready to create a source to import this data into Faros.

In the Setup Pages view, select Import Data and Sources. Click on Add Source and search for “Survey Import” or click on Survey. Create a new Survey Import from Airtable source and use the connection you created above.

If you don’t specify any base ids, the source will read and import surveys from all bases that the Connection token has access to. If your token has access to multiple bases and you want the source to read from a single base, or a specific list of bases, configure the base ids to pull from.

📘

If you want to use a single source for all your survey bases we recommend to leave this field blank. This way, if you add new surveys in the future you only need to make sure that the token has access to the new base on the Airtable side, and you won't need to update the Faros Source.

Open the advanced settings section if you want to include question category mappings from your custom categories to Faros Categories, or for specifying custom table names or column name mappings.

📘

Keep in mind that if you use a single source for all your surveys, you have to create a common question category mapping for all your surveys question categories. Also the column and table names must be the same between all bases so that the mapping works for all of them.

If you want to configure column name mappings, activate the toggle and change the default mapping values to the desired ones:

After clicking connect source, data will start syncing at the interval specified in the configuration.

Survey Import from Airtable using the Airbyte Local CLI

Survey data can be also imported using the Airbyte Local CLI.

AIRTABLE_TOKEN should have these scopes:

• data.records:read
• schema.bases:read

We recommend setting up a token with access to the bases (each base corresponds to a survey) that need to be imported.

bash <(curl -s https://raw.githubusercontent.com/faros-ai/airbyte-local-cli/main/airbyte-local.sh) \
--src farosai/airbyte-airtable-source \
--src.credentials.auth_method 'api_key' \ 
--src.credentials.api_key ${AIRTABLE_TOKEN} \  
--src.stream_name 'surveys' \
--dst 'farosai/airbyte-faros-destination' \
--dst.edition_configs.api_key "${FAROS_API_KEY}" \
--connection-name "mysurveyssrc"

There's also an optional argument (--src.bases '["app0z7JKgJ19t13fw", "EXAMPLE_BASE_ID_2"]') to specify the bases to import. If not specified, the source will pull all bases that the token has access to.

There's a set of optional configuration fields explained below:

Here's the command with all the defaults included. Feel free to remove/edit as needed.

bash <(curl -s https://raw.githubusercontent.com/faros-ai/airbyte-local-cli/main/airbyte-local.sh) \
--src farosai/airbyte-airtable-source \
--src.credentials.auth_method 'api_key' \
--src.credentials.api_key ${AIRTABLE_TOKEN} \
--src.stream_name 'surveys' \
--dst 'farosai/airbyte-faros-destination' \
--dst.edition_configs.api_key "${FAROS_API_KEY}" \
--dst.source_specific_configs.surveys.survey_responses_table_name "Survey Responses" \
--dst.source_specific_configs.surveys.survey_metadata_table_name "Survey Metadata" \
--dst.source_specific_configs.surveys.question_metadata_table_name "Question Metadata" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_name_column_name "Survey Name" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_description_column_name "Survey Description" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_type_column_name "Survey Type" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_status_column_name "Survey Status" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_started_at_column_name "Survey Started At" \ 
--dst.source_specific_configs.surveys.column_names_mapping.survey_ended_at_column_name "Survey Ended At" \ 
--dst.source_specific_configs.surveys.column_names_mapping.respondent_name_column_name "Name" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_email_column_name "Email" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_id_column_name "Team ID" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_name_column_name "Team Name" \ 
--dst.source_specific_configs.surveys.column_names_mapping.question_column_name "Question" \
--dst.source_specific_configs.surveys.column_names_mapping.question_category_column_name "Category" \
--dst.source_specific_configs.surveys.column_names_mapping.response_type_column_name "Response Type" \
--dst.source_specific_configs.surveys.question_category_mapping "{}" \
--connection-name "mysurveyssrc"

Importing from Google Sheets

You can quickly ingest your survey data into Faros via a simple spreadsheet. The spreadsheet question responses can be populated for example from a Google Forms survey. It should also contain the metadata related to questions, for example question categories, and can include the metadata related to the survey, such as survey name, description, and type. To get data into Faros from this option you must:

1. Create a Google sheet
2. Authenticate your Google account
3. Create the Faros Source

Create a Google sheet

The Google sheet must contain two tabs: Survey Responses and Question Metadata. It also supports a Survey Metadata tab (optional). You can use this template as an example for creating it. Note that it contains the default column names, which can be modified as desired.

Authenticating to Google

To get credentials and authenticate against the Google API you can follow the steps described in this guide.

Add a Faros Connection

The connection needed for this source is very similar to the connection explained here. Follow those steps but selecting instead Survey Imports from Google Sheets type.

Create Faros Source

Now that you have the spreadsheet and have given Faros the correct permissions you're ready to create a source to import this data into Faros.

Create a new Survey Import source and use the connection you created above. You'll also need the sheet ID which can be found in the url. Remember to make sure this spreadsheet is shared with your service account's email.

In the Setup Pages view, select Import Data and Sources. Click on Add Source and search for Survey Import or click on Survey. Create a new Survey Import from Airtable source and use the connection you created above.

Open the advanced settings section if you want to include question category mappings from your custom categories to Faros Categories, or for specifying tabs or column name mappings.

After clicking connect source, data will start syncing at the interval specified in the configuration.

Importing from AWS S3

This solution is the same as Importing with Google Sheets (see previous section) but from a spreadsheet .xlsx file that resides in an Amazon S3 bucket.

Prepare your survey spreadsheet as described in the previous section.

Once you have a sheet, upload it into one of your S3 buckets.

Authenticating

You'll want to create IAM credentials so your Faros source can access the S3 bucket with your spreadsheet. You'll want to make sure your IAM user has the ListObjectsInBucket and ReadObjectsInBucket permissions enabled.

Note the AWS Key ID and AWS Secret Access Key values as well as the AWS region of your bucket. You will use these when creating your Connection in Faros.

Add a Faros Connection

Once you have set up your S3 credentials, you can set up your Faros source. First, find the correct source in the Faros Source Catalog, then create a new connection using the above keys.

Create Faros Source

Advanced settings are the same as the ones described in the previous section.

Once your source is set up, data will be pulled from your spreadsheet into Faros at a regular cadence.

Importing from a spreadsheet in your local filesystem

Same as with Importing with Google Sheets (see above) but from a local spreadsheet .xlsx file.

Prepare your survey spreadsheet the same as described in that section.

📘

Modify /path/to/my-survey.xlsx with the correct path to the file.

📘

All column_name_mapping, ..._table_name and question_category_mapping optional configs were added in the command with the default values. They can be changed or removed.

bash <(curl -s https://raw.githubusercontent.com/faros-ai/airbyte-local-cli/main/airbyte-local.sh) \
--src farosai/airbyte-sheets-source \
--src.spreadsheet_source.sheet_source 'LocalSpreadsheet' \
--src-docker-options '-v /path/to/my-survey.xlsx:/my-survey.xlsx' \
--src.spreadsheet_source.file '/my-survey.xlsx' \
--src.stream_name 'surveys' \
--dst 'farosai/airbyte-faros-destination' \
--dst.edition_configs.api_key "${FAROS_API_KEY}" \  
--dst.source_specific_configs.surveys.survey_responses_table_name "Survey Responses" \
--dst.source_specific_configs.surveys.survey_metadata_table_name "Survey Metadata" \
--dst.source_specific_configs.surveys.question_metadata_table_name "Question Metadata" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_name_column_name "Survey Name" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_description_column_name "Survey Description" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_type_column_name "Survey Type" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_status_column_name "Survey Status" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_started_at_column_name "Survey Started At" \
--dst.source_specific_configs.surveys.column_names_mapping.survey_ended_at_column_name "Survey Ended At" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_name_column_name "Name" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_email_column_name "Email" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_id_column_name "Team ID" \
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_name_column_name "Team Name" \
--dst.source_specific_configs.surveys.column_names_mapping.question_column_name "Question" \
--dst.source_specific_configs.surveys.column_names_mapping.question_category_column_name "Category" \
--dst.source_specific_configs.surveys.column_names_mapping.response_type_column_name "Response Type" \
--dst.source_specific_configs.surveys.question_category_mapping "{\"Outcomes\": \"AlignmentAndGoals\", \"Teamwork\": \"DeveloperProductivity\", \"Technical Practices\": \"SpeedAndAgility\" }"