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:
- Create/Modify the Airtable Base
- Authenticate your Airtable account
- 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:
Column Name (Default) | Required | Description |
---|---|---|
Name | no | Respondent full name |
no | Respondent email | |
Team ID | no | Respondent team ID |
Team Name | no | Respondent team name |
Timestamp | no | Survey Response submitted at as date timestamp |
Survey Name | no | Survey name, e.g Quality Test |
Survey Description | no | Survey description |
Survey Type | no | Survey type. It can be one of Faros’ standard values (ENPS, NPS, Satisfaction) or a custom one |
Survey Status | no | Current status of the survey. It can be one of Faros’ standard values (Completed, Canceled, Planned, InProgress) or a custom one |
Survey Started At | no | Survey start date as date timestamp |
Survey Ended At | no | Survey end date as date timestamp |
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 (optional)
Your question metadata table can include the following columns:
Column Name (Default) | Required | Description |
---|---|---|
Question | yes | Question text. It must be the exact same text as the one in the survey responses question column |
Category | yes | Question category. It can be one of Faros’ standard values (AlignmentAndGoals, DeveloperProductivity, ENPS, NPS, Quality, Satisfaction, SpeedAndAgility, CodingAssistants) or a custom one |
Response Type | no | It can be one of Faros’ standard values (Binary, LikertScale, MultipleChoice, NumericEntry, OpenEnded, RankOrder, Rating) or a custom one |
Survey Metadata (optional)
Optional table for including survey metadata. As explained above, either use this option or include survey metadata as Survey Responses columns.
Column Name (Default) | Required | Description |
---|---|---|
Survey Name | yes | Survey name, e.g Quality Test |
Survey Description | yes | Survey description |
Survey Type | yes | Survey type. It can be one of Faros’ standard values (ENPS, NPS, Satisfaction) or a custom one |
Survey Status | yes | Current status of the survey. It can be one of Faros’ standard values (Completed, Canceled, Planned, InProgress) or a custom one |
Survey Started At | yes | Survey start date as date timestamp |
Survey Ended At | yes | Survey end date as date timestamp |
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:
Argument | Default Value | Description |
---|---|---|
--dst.source_specific_configs.surveys.survey_responses_table_name | Survey Responses | The name of the table that contains survey responses. |
--dst.source_specific_configs.surveys.survey_metadata_table_name | Survey Metadata | The name of the table that contains survey metadata. |
--dst.source_specific_configs.surveys.question_metadata_table_name | Question Metadata | The name of the table that contains question metadata. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_name_column_name | Survey Name | The name of the column that contains the survey name. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_description_column_name | Survey Description | The name of the column that contains the survey description. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_type_column_name | Survey Type | The name of the column that contains the survey type. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_status_column_name | Survey Status | The name of the column that contains the survey status. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_started_at_column_name | Survey Started At | The name of the column that contains the survey started at timestamp. |
--dst.source_specific_configs.surveys.column_names_mapping.survey_ended_at_column_name | Survey Ended At | The name of the column that contains the survey ended at timestamp. |
--dst.source_specific_configs.surveys.column_names_mapping.respondent_name_column_name | Name | The name of the column that contains the respondent name. |
--dst.source_specific_configs.surveys.column_names_mapping.respondent_email_column_name | The name of the column that contains the respondent email. | |
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_id_column_name | Team ID | The name of the column that contains the unique identifier for the respondent's team. |
--dst.source_specific_configs.surveys.column_names_mapping.respondent_team_name_column_name | Team Name | The name of the column that contains the name of the respondent's team. |
--dst.source_specific_configs.surveys.column_names_mapping.question_column_name | Question | The name of the column that contains the question text. |
--dst.source_specific_configs.surveys.column_names_mapping.question_category_column_name | Category | The name of the column that contains the question category. |
--dst.source_specific_configs.surveys.column_names_mapping.response_type_column_name | Response Type | The name of the column that contains the question response type. |
--dst.source_specific_configs.surveys.question_category_mapping | {} | JSON map from source survey question categories to Faros question categories. |
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:
- Create a Google sheet
- Authenticate your Google account
- 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\" }"
Updated 4 months ago