Process for Mapping Leads Files
leads_schema_mapping = where the standard Lead file mappings live. Generally, DO NOT MODIFY this unless the column names are very generic and you are sure it will not affect other existing lead mappings!
- alias_fingerprint = the column name from the Leads file (all lowercase, no spaces or punctuation EXCEPT DO include #)
- target_field = the target field name as taken from leads_schema_mapping table target_field
-
IMPORTANT: You can have multiple columns (alias_fingerprint) mapped to the same target_field AS LONG AS only 1 of those columns exists in the file you are loading.
- The exception is array columns like email and phone_number where multiple columns can be ingested into the same target_field
- Example: If both accountname and dealername exist in a file being loaded, it will throw an error. To avoid this, you would want to map one of these fields (the one with no/bad data) in leads_schema_mapping_custom_headers to (blank/null) for the specific lead supplier in question
- Remember to commit changes!
leads_schema_mapping_custom_headers = where custom mapping for Leads files live. Here, you can custom map columns for each lead data supplier (vendor).
- preprocessing_key = the lead supplier name as appears in lead_data_supplier table preprocessing_key
- alias_fingerprint = the column name from the Leads file (all lowercase, no spaces or punctuation EXCEPT DO include #)
- target_field = the target field name as taken from leads_schema_mapping table target_field
-
IMPORTANT: You can have ONLY 1 column (alias_fingerprint) mapped to the same target_field for each lead supplier. This is different than the rules for leads_schema_mapping. If multiple non-array columns (alias_fingerprint) are mapped to the same target_field, files for this vendor will not process and will throw errors.
- The exception is array columns like email and phone_number where multiple columns can be ingested into the same target_field
- Remember to commit changes!
Mapping Multi Files
What are Multi Files?
A (multi) file is when a dealer or vendor includes data for multiple stores in 1 file. The file MUST contain a Dealership Name column with the following rules:
- The Dealership Name column must be populated on all data rows. It cannot be left blank or the file will get stuck due to an error.
- The Dealership Name column must have unique values for each dealership.
- The values must not be generic values such as 1, 2, 3. They must be unique values such as the store name, or an ID number consisting of 4 or more digits.
Mapping a (multi) file needs to be done for:
- Multi files loaded through Dropbox Insyncing Data Drop folders.
- This may include files received in the SFTP that get transferred to Dropbox Insyncing Data Drop by a DAG (example: Tekion Sales)
- Leads files pulled by Wall-E/the scraper. See Data Integration: Scraper Method for information on how to obtain the property aliases from each vendor system.
Process for Mapping Multi Files
- Insert rows into the claripedia -> tables -> property_aliases table
- property_alias = the name of the property in the Multi file *property column - ALL lowercase, no punctuations or spaces EXCEPT DO include #
- property_id = Clarivoy property ID
- Remember to commit changes!