Skip to content

Latest commit

 

History

History
99 lines (68 loc) · 4.57 KB

README.md

File metadata and controls

99 lines (68 loc) · 4.57 KB
Raw

Salesforce API for CUMC AOU Project

The project is for data transfering between AOU database in CUMC and Salesforce Platform.
It's based on simple-salesforce API.

Motivation

Salesforce is a CRM platform which is used for participants recruiting and engagement tracking purposes.
Participants' data need to be extracted and uploaded into Salesforce platform on a daily basis.
The project is established to automate the data transferring and updating to minimize manual work.

Requirements

Salesforce Account and Salesforce security token (Production and Sandbox)
AOU SQL Database Settings (Driver, Server and Windows Authentication)

Please refer to the settings.py

Workflow

Data Flow Diagram

Steps for Data Extraction and Uploading

  1. Build up database using view_create_scripts.sql to track the relationship and maintain information consistency.
  2. Extract contacts and journeys from Salesforce and save them in local database.
  3. Using hash function in views to check if there's any changes in contacts and journeys.
  4. Update and insert records from contact_update view.
  5. Once the contacts are upserted, related journey records are created automatically in Salesforce.
  6. Pull out journey and PMI ID table and save the data into local database.
  7. Extract the journey data from database and compare with journey from Salesforce.
  8. Update any changes in journey_update view.

Tables in Local Database

  • sf_contact (Salesforce originated contacts)
Id LastName FirstName HP_PMI_ID__c
0030a00002WIXUXXX2 Holmes Sherlock P123456789
  • pardot_contact (Pardot originated contacts)
Id LastName FirstName Email Phone ...
0030a00002WIXUXYY3 Watson John [email protected] xxxxxxxxxx
  • sf_journey_export (Salesforce journeys)
Id Contact__c HP_Participant_Status__c ...
0060a00000k5ZgaBBC 0030a00002WIXUXXX2 Core Participant
  • contact_log (Log the update or insert action and the error message for contact records)
PMI_Id LastName FirstName Action Success Error Last_Modified_Time
P123456789 Holmes Sherlock Create 1 2019-07-23 11:56:30
P123456789 Holmes Sherlock Create 0 Error message:You're creating a duplicate record. 2019-07-23 11:56:31
  • journey_log (Log the update or insert action and the error message for contact records)
Journey_Id Action Success Error Last_Modified_Time
0060a00000k5ZgaBBC Create 1 2019-07-23 11:56:30
  • contact_journey_relation (map PMI_ID and journey_Id)
Contact_Id PMI_Id Journey_Id
0030a00002WIXUXXX2 P123456789 0060a00000k5ZgaBBC

Views in Local Database

  • sf_contact_view (contacts extracted from HealthPro and REDCap)
  • sf_contact_export_view (built upon sf_contact table and store all the existing contacts in Salesforce)
  • sf_contact_export_update (difference between contact_view and contact_export_view, need to be inserted and updated)
  • pardot_contact_export_update (Pardot contacts which match HealthPro and REDCap contacts)
  • sf_journey_view (journeys extracted from HealthPro and REDCap)
  • sf_journey_export_view (built upon sf_journey table and store all the existing journeys in Salesforce)
  • sf_journey_export_update (difference between journey_view and journey_export_view, need to be updated)

Instruction to Run the Scripts

  • Run the below script in your shell to install all the packages
$ pip install -r requirements.txt
  • Modify and run view_create_script.sql in your local database to build up 7 views
  • Update the _settings.py file and rename it to settings.py
  • Run the following:
$ python daily_update.py