Data Validation
Fisheries Data Validation Guide
All data that flows through the fisheries data system is validated in Smartsheet. Data is sent for validation under three scenarios; 1) new names or ‘other’ information are manually added in Kobo Collect, 2) a numeric value falls beyond established thresholds or 3) names in the dataset do not have a corresponding match in a reference file. Once data is validated, both the datasets in the database and the reference tables are updated. Reference tables serve as inputs for dropdown lists in KoboToolbox forms and contain data that is added to the datasets through joins. See the decision tree below (Figure 1).
Data triggered for validation is sent to one of four tables: 1) Names and Gears, 2) Value Warnings, 3) Species or 4) Other. The instructions below detail the data validation process in Smartsheet Dynamic View for the four validation tables. Each validation table will require either selecting the correct field value from a dropdown list or entering correct values manually. The process will be appended for future data validation needs, as required.
The ‘Names and Gears’ validation table contains new data from all the Kobo surveys including the new names of fishers, buyers, data collectors, fishing grounds, landing sites and gears. ‘Value Warnings’ contains numeric information collected from the Landings Monitoring or Landings Profiling survey which flagged a price, weight or length value based on a pre-established threshold. The pre-established thresholds for weight and length warnings originate from the lw_ref; and price warnings originate from the min_max_ref. The ‘Species’ validation table contains photos and new local names from all the surveys in order to validate new species using a double verification process. Lastly, the ‘Other’ data validation table contains ‘other’ information collected from the Landings Profiling, Community Profiling or Household Survey and is mainly validated for translational purposes.
Daily e-mail messages are sent out with information and links on how to complete the data validation (see detailed instructions below). Additional notifications from Smartsheet may be sent via email to follow-up with incomplete data validation selections, if needed.
Step 1: Provide a point of contact, names and email addresses for those responsible for data validation.
The organization managing multiple partners may provide all points of contact information.
Individual partners may provide point of contact information.
The points of contact will receive data validation notifications through email.
Step 2: Check your email for a data validation request.
You will receive an email from “Smartsheet Automation” (Figure 2)
The email will contain a message with links to proceed with validation through either a “Dynamic View” or “Open Request”. Dynamic View is a table format and Open Request will direct you to a validation form.
To open with “Dynamic View” (Step 3, Option A), click the link that begins with “https://dynamicview.smarthseet.com….”
To open the update request form (Step 3, Option B), click the blue “Open request” button
A preview of the entries that need to be validated will appear below this message, but you will not be able to validate any data directly in the email
At the bottom of the email, there is a link for “Go to the sheet”; however, the underlying sheet has restricted access. Please review your data validation via Dynamic view, Open request, or your partner-specific report (coming soon).
Figure 1: Data validation decision tree.
Figure 2: Sample Smartsheet Validation Email Message
Step 3, Option A: Complete your data validation via Dynamic View (recommended)
For this option, you will need to create a free Smartsheet account. You will be directed from the email to set up an account.
Click on the Dynamic View link in the email request.
A new internet browser window will open with a table-like setup for all entries. Here, you can easily scroll through all the entries that need validation.
Click on a row to begin validation.
A “Details” panel will appear on the right.
Review the context fields and populate the validation fields. The validation fields contain the data that will be transferred to the final dataset (Table 1).
Leave a comment, ONLY IF there is something that needs our attention. Blue Ventures (BV) staff will review comments and respond appropriately.
Click the blue “Save” button to save your selection.
Once validated, that row will disappear from your dynamic view.
Click on the next row and repeat steps to complete validation for all entries.
You can exit and re-enter by clicking on the dynamic view link from your email or logging into your account at any time. You do not need to validate all entries in one sitting.
You can bookmark this link and return at any time.
Table 1: Primary fields or columns in the validation tables. Context fields provide information needed to validate data. Validation fields are populated with data that will be transferred to the master datasets. Only the validation fields can be edited. Some fields are only visible in the “Details” panel when a row is clicked in the Dynamic View table.
| Validation table field | Field description | Field type |
|---|---|---|
| All Data Validation Tables | ||
| partner | organization name collecting the data | context |
| admin3_landed | Village or sub-village where the catch was landed and recorded | context |
| data_collector | Name of person who collected the data in Kobo (enumerator) | context |
| Names and Gears | ||
| kobo_field | The field from the KoboCollect form that needs to be validated or name that does not have a match in the reference files. | context |
| new_data | The value that was entered for the corresponding kobo_field | context |
| ref_match1 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| ref_match2 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| ref_match3 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| validation_options | Select field with correct data (new_data, ref_match1, ref_match2, ref_match3), enter another value (other), or remove (remove row from analysis because data cannot be validated). Required. Only visible in the Details panel. Entry will disappear once data is validated. | validation |
| other | Enter another value if none of the validation options are correct. Required IF “other” selected from validation_options. Only visible in the Details panel. | validation |
| landings_submission_ids | List of submission ids this new data value appears in, from landings monitoring Kobo form | context |
| profiling_submission_ids | List of submission ids this new data value appears in, from landings profiling Kobo form | context |
| hhs_submission_ids | List of submission ids this new data value appears in, from the household survey Kobo form. | context |
| species_group | The species group associated with the new gear name, as collected in the Kobo form. This will only appear if the “kobo_field” is a gear category. | context |
| gear_type | Required IF validating new gear names. A dropdown list of gear types to be associated with the validated gear name. | validation |
| fisher_gender | The gender associated with the new fisher, as collected in the Kobo form. This will only appear if the “kobo_field” is a fisher name. | context |
| buyer_gender | The gender associated with the new buyer, as collected in the Kobo form. This will only appear if the “kobo_field” is a buyer name. | context |
| Value Warnings | ||
| fisher_name | Name of fisher who landed the catch | context |
| date_landed | Date catch was landed | context |
| local_name | Species local name | context |
| warning_calc | Unit value: weight/individual, price/weight, length/individual, width/individual | context |
| warning | Type of warning | context |
| numerator | Value of warning numerator: weight, price, length | context |
| numerator_unit | Unit of numerator: kg, g, lb, ons, USD, IDR, PHP, cm | context |
| denominator | Value of denominator: count, weight | context |
| denominator_unit | Unit of denominator: number, kg, g, lb, ons | context |
| correct | Y = values are correct, N=values are incorrect, Remove = remove from analysis because values cannot be validated. Required. | validation |
| correct_numerator | If correct = Y, values are auto-populated, If correct = N, enter correct value | validation |
| correct_denominator | If correct = Y, values are auto-populated, If correct = N, enter correct value | validation |
| correct_numerator_unit | If the numerator_units are incorrect, select the correct option from the dropdown | validation |
| data_origin | Dataset the data came from (Landings monitoring or profiling). | context |
| submission_id | Submission ID from the Kobo form. Use this to compare against raw data, if needed, for validation. | context |
| Species | ||
| data_origin | Origin or source of the data: ‘landings’, ‘profiling’, ‘community_profiling’ or ‘hhs’ | context |
| admin1 | Admin1 where the catch was landed and recorded. | context |
| admin2 | Admin2 where the catch was landed and recorded. | context |
| fisher_name | Name of fisher associated with this new species data. | context |
| new_species_photo | Link to photo that was submitted in Kobo Collect | context |
| new_data | The value that was entered as a new species local name in Kobo Collect OR a local name and scientific name ID (localname_scientificspecies) that no longer matches a name found in species_ref | context |
| ref_match1 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| ref_match2 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| ref_match3 | Closely matching name that already exists in the database. If blank, no existing names match the name in the “new_data” column | context |
| eng_common_name_new | English common name of new species. Please provide if known, else leave blank. (Not required) | validation |
| scientific_family_new | Scientific family name of new species. Please provide if known, else leave blank. (Not required) | validation |
| scientific_species_new | Scientific species name of new species. Please provide if known, else leave blank. Required: If species name is unknown, please put any level of taxonomic identification (e.g. Family name, English common name) | validation |
| submission_id | Submission ID from the Kobo form. Use this to compare against raw data, if needed, for validation. | context |
| validation_options | Select field with correct data (local_name_new, ref_match1, ref_match2, ref_match3), enter another value (other), or remove (remove row from analysis because data cannot be validated). Required. Only visible in the Details panel. Entry will disappear once data is validated. | validation |
| other | Enter another value if none of the validation options are correct. Required IF “other” selected from validation_options. Only visible in the Details panel. | validation |
| latest_comment | This column stores the last comment that was made for that row. To view the entire discussion of comments for that row, simply click anywhere on the row. The details panel will appear and click on the “Comments” tab to view all comments for that row. | auto |
| Other | ||
| kobo_field | The field from the KoboToolbox form that needs to be validated. This could be from a question in the Landings Profiling, Community Profiling or Household Survey. | context |
| new_data | The value that was entered for the corresponding kobo_field | context |
| data_origin | Dataset the data came from (Landings profiling, Community profiling or Household survey). | context |
| submission_id | Submission ID from the Kobo form. Use this to compare against raw data, if needed, for validation. | context |
| validation_options | Select field with correct data (new_data), enter another value (other), or remove (remove row from analysis because data cannot be validated). Required. Only visible in the Details panel. Entry will disappear once data is validated. | validation |
| other | Enter another value if none of the validation options are correct. Required IF “other” selected from validation_options. Only visible in the Details panel. | validation |
| validated_data_english | Required. Enter the English translation of the new_data or other field if ‘other’ was selected as the validation option. Only visible in the Details panel. | validation |
Figure 3: Example Dynamic View - Value Warnings
Figure 4: Example Dynamic View- Details Panel
Step 3, Option B: Complete your data validation via ‘Open Request’
For this option, you do not need to have a Smartsheet account login .
Click on the “Open request” button in the validation email message.
A new internet browser window will open with a form-like setup for each entry
Review the context fields and populate the validation fields. The validation fields contain the data that will be transferred to the final dataset (Table 1).
Click next at the bottom of the screen to move through each entry that needs to be validated.
To skip an entry (e.g. if you are not sure yet which answer to pick) click “Next” without making a selection in the ‘validation_options’ column. The next entry for validation will appear.
If you need to exit the page or stop before all entries have been validated, your choices should be saved the next time you reopen the request. Upon opening to continue, just click “Next” until you find an entry that needs validation.
When you get to the last entry, click “Done”. A pop-up message will appear asking if you are ‘Ready to submit your update?’:
Click “Go Back” if you need to review
Click “Submit Update” to submit validation
Check the “Send me a copy of my responses” box if you would like a copy of your responses sent to your email
If you click “Send me a copy of my responses” you will receive an email titled “Update Confirmation: Names and Gears”.
There will be a table summarizing which entries were updated. Entries in which no option was selected in ’validation_options” will not be included.
This email may contain a link to a sheet name “Data Validation - Names and Gears”; however, access to this sheet is locked.
To see any remaining validation needed, you will need to wait for the next update request email or open the Dynamic View.
Figure 5: Example of “Open Request” format
Frequently Asked Questions:
- Can multiple people work on data validation at the same time?
- Yes, however, once an entry (row) has been validated in Dynamic View it will no longer be visible.
- Do I need a Smartsheet account?
- Only to complete data validation via Dynamic View (i.e. the table view). You do not need a paid account, with a free trial or free version (once your trial is completed), you will be able to view and interact with all the Smartsheet files.
- What happens after data is validated?
- In some cases, data will undergo additional review by the Blue Ventures data team. Validated data will be updated in the database, and when appropriate, dropdown menus will be updated in the Kobo reference files and forms (Figure 6). Data that has not been validated will not appear in the dashboard visualizations or statistics, but will be available in the raw data download.
If at any time you encounter any errors, have questions, or need support for completing the data validation, please contact us at mary.mccabe@blueventures.org.
Figure 6: Data Validation to Reference File Workflow
