Access All Live + All On-Demand Trainings for 1 Year! SAVE $500 NOW

Stage 1 Prep

  • To upload a list, it must complete the following.
    • Tag team processing
    • Received upload approval.
  • Move files from

C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\5 – Ready to Upload

to

(C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\6 – Staging\1. PENDING DEDUP\_FILES_TO_DEDUPE)

File Formatting (If not using python)

  • Make sure we have the right headers.
  • Check file for ghost data.
    • To see the limits of the cell that are hold data – Use:
    • Start of data= Ctrl+Home / End of Data= Ctrl+End
    • Delete any undesired Rows &Columns.
    • Highlight all cells and set formatting to TEXT. This is necessary to prevent Access from erroneously dropping data when executing the import.

If python is installed please make use of the following script that combines all the files

C:\Dropbox (Coding Leader)\Coding Leader\Data\Queries_and_Scripts\Python\ Stage 01_files combine.py

      Access Dedup

  • Open Dedupe.accdb in the following path C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\6 – Staging\1. PENDING DEDUP\Stage1_Dedupe_Process\Dedupe.accdb
  • once open – look under the Macro Tab.
  • Make sure you refresh the combomaster link first. This can be done by right clicking on “_Combo_Master” under tables and selecting refresh link. Make sure to use your login password for sql while refreshing the linked combo master.
  • Click “0010_Refresh_MasterList” – This will populate the “_Combo_Master”.
  • Click “0005_Clear_NewList” – This clears the table for New lists
  • If python/ excel is used to combine the files please use the saved imports option under external data and change the path to import the file.
  • Click “0015_ImportAllXLSX” – This upload’s and combines the files.
  • This takes a while, depending on the size of the list(s)
  • This is where you might run into some problems – errors.
    • Empty cells >Ghost Data
    • Incorrect or blank headers > Header Checker & Ghost Data
  • Click “0020_Cleanup_NL” – This cleans up the file uploaded in the initial import whether through python or by running the macro
  • This takes a while, depending on the size of the list(s)


Click and run the following queries.

    • PreDedup_Counts – displays the number of records by List source. We use this to update upload tracking sheet under the action item as ‘Approved for Processing.’(Please use the original file name while updating the upload tracking sheet)
    • Unique_Names_From_NL_CountByList – Displays the number of records by List Source we use this to update upload tracking sheet under the action item as ‘Post DD.’ (Please use the original file name while updating the upload tracking sheet)
  • Unique_Names_From_NL_Export will give us the unique emails that needs to be processed for stage 01.
  • Please paste the results of the following query “Unique_Names_From_NL_Export” in the file CL_AddToStage##_2021mmdd in Add2DMS tab under PENDING DEDUP folder. If export exceed 50,000 records – export as “.txt” file, then import data into excel template. Copy all data excluding header and past results online A2 on “Add2DMS” Sheet.

 

Hygiene

  • Open the SQL script in the following path

C:\Dropbox(CodingLeader)\CodingLeader\Data\Queries_and_Scripts\SQL\ESP_Data\DMS_Stage01_HygieneCheck.sql

  • Complete the Hygiene Check for Stage 01. The documentation for this can be found in the following path.


C:\Dropbox (Coding Leader)\Coding Leader\Business\Training\Processes Training\Scrubbing and Staging process\ Hygiene Check for Scrubbing, Stage 01 and Stage 02.docx

  • Please follow the instructions in the above script
  • Copy paste the #suppress results as Hygiene failures in stage 01 prep document.
  • Copy paste the results from stage1emails table in the ADD2DMS tab
  • Paste the counts from the following block in the upload tracking sheet

C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\6 – Staging\Upload_Tracking_Sheet.xlsx

—————- Populate upload tracking sheet —————-

  • Make sure to get the updated contact provider from the SQL Hygiene Query and please check the Hygiene.dbo.Domain_Provider_Relationship / [Hygiene].dbo.[Hygiene_Results] tables in SQL.

Checks:

  • Correct Specialties and Company Type where Check_SPEC and Check_CompType is false.

Please cross check the list of tags currently being used from the following document and update the in statement in sql script(if you are using) to reflect the same.

C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\MasterListIdentfiers_5-7-21.xlsx

  • Correct email format where CheckEmailStructure is false.
  • Fill in Attachment 3 (List Guid) by referring to the following sheet or by using the sql script block.

C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\6 – Staging\1. PENDING DEDUP\ VK_List_Source_Check.xlsx

Note: if the guid is not populated from the script, please add in the list source in dynamics and refresh the above sheet. Please use this to fill in the empty ones after you export the stage1emails.

  • Please refresh the sheet (this sheet is a connection from Dynamics List source table)
  • Please save the sheet using save as excel. Once saved open the file and save sheet “Add2DMS” as “CL_AddToStage01_yyyymmdd.csv”
  • Delete all columns from domain and make sure the date format for Date1 is in DMS format

yyyy/mm/dd hh:mm:ss

 

Uploading records to stage 1

  • Open DMS stage 1.
  • Members :> Add Members :> Import Members from CSV File
  • Import CSV file into stage 01.
  • Copy Import Results
  • Save the import results as text file CL_AddToStage01_yyyymmdd_ImportLog.txt
  • Update upload Tracking sheet.
  • On “ImportLog” sheet paste Import log results.
  • On “Pivots” sheet update pivot connections.
  • Added-stage01 counts from Add2DMS tab. (Please use the created file name while updating the upload tracking sheet)
  • Failed-Stage01 counts from ImportLog tab. (Please use the created file name while updating the upload tracking sheet)

Please move all the files once completed to the following path C:\Dropbox (Coding Leader)\Coding Leader\List_Mgmt\List Processing\6 – Staging\3. SENT FOR STAGE 1 CLEANING