Skip to main content
Sign in

Project Recipe: List Loading for Dynamics

  • Updated

 

The following is a guideline to follow when using the recipe "List Load for Dynamics". Please read these guidelines before cloning the recipe.

Preparation

This project uses Openprise's App Factory List Load app. We recommend you review the following video on building a list loading app before cloning this recipe.

Overview

This recipe provides the following:

  • Jobs that process a list to load the records into Dynamics
  • An App Factory app to submit lists for loading
  • An App Factory app to review the list load results
  • A bot to run the above jobs using the App Factory application

The assumption is that your Dynamics objects are being imported on a regular basis by pre-existing bots. 

Limitation

This recipe does not support adding the list members to a campaign

Preparation

  1. Create a spreadsheet in Google drive for the template for the lists to be loaded. This spreadsheet will only be used to define the attributes for the list to be loaded by the list load app. Please use the exact column names shown below.

    Column name Openprise data type Input Values
    Added as New Lead text, single value Template
    Address text, single value Blank
    Status text, single value Blank
    City text, single value Blank
    Company text, single value Blank
    Country text, single value Blank
    Email Address text, single value Blank
    First Name text, single value Blank
    Job Title text, single value Blank
    Last Name text, single value Blank
    LinkedIn Profile URL text, single value Blank
    No. of Employees text, single value Blank
    Phone Number whole number Blank
    Postal Code text, single value Blank
    Rejected text, single value Blank
    State text, single value Blank
    Transaction ID text, single value Blank
    Uploaded By text, single value Blank
    Upload Time date Blank
    Website URL text, single value Blank
  2. Add one record to the template by adding a sample email (ie. junk@junkmail.com). You can leave all other values in the spreadsheet blank.
  3. Create a data source from the above spreadsheet. When creating the data source:
    1. The Data source name should be List Load Input Template - Dynamics.
    2. The data source should be put in a folder and be the only file in the folder. Note the folder name as it will be used when creating the data source.
    3. The source technology and data format for the data source should be Google Drive - Sheets.
    4. On the Map screen (during data source creation), change the data type for the attribute Upload Time to the Date data type.
    5. All other fields can be left as the default data type of Text, single value, except for Number of Employees which should be a whole number value.
    6. Note: Two additional columns will be added when the data source is created: OPFileNames and OPAutoDate. These attributes should remain in the data source.
  4. Import the above data source. You should end up with a data source that has one record in it with the junk email. This data source is only used to define the columns expected for processing by the list load app.
  5. Create data sources for Dynamics Account, Contacts, and Leads objects with the following attributes. Note: If these data sources are already created, please verify that the following attributes are defined in the data sources.
    Lead Contact Account
    LeadId ContactId AccountId
    Address1_City Address1_City  

    CompanyName

    		 Company Name
    Address1_Country Address1_Country Address1_Country
    EmailAddress1 EmailAddress1  
    FirstName FirstName  
    LastName LastName  
    NumberofEmployees   NumberofEmployees
    Telephone1 Telephone1  
    Address1_Postal Code Address1_Postal Code  
    Address1_StateorProvince Address1_StateorProvince Address1_StateorProvince
    Address1_line1 Address1_line1  
    JobTitle JobTitle  
    WebsiteURL WebsiteURL WebsiteURL
  6. Create new data targets: Create a new data target for Leads, and Contacts with the following attributes:
      1. Leads - all attributes listed in the table above
      2. Contacts - all attributes listed in the table above

Clone

To clone the project recipe, navigate to Projects Recipes and select the project recipe to clone.

1.png

listload_recipe.png

You will need to provide:

  • Project name
  • Prefix and/or suffix for all objects in the project
  • Data source to be used in the project
  • Data target to be used in the project

Examine the cloned project

We recommend you look through all the newly created project elements to familiarize yourself with what has been created.

Look for:

  1. Any job that is marked with errors. These errors must be manually corrected.
  2. Export tasks that write back to Dynamics. Make changes by selecting any values that want to write back to Dynamics. Note: Any Export task to Dynamics has been bypassed for your protection. We recommend that you run the jobs with the bypass enabled so you can review any data changes before writing data back to Dynamics.
  3. Review logic in the jobs to validate that it meets your requirements for cleaning the incoming list data and matching it to any existing lead, contact, and account.
  4. Review logic in the job that rejects a list record due to missing data. By default, these jobs will only reject records with a missing email address, or a duplicate record identified by a duplicate email address.  You may want to add tasks to identify other missing data values such as country, and company name.
  5. Please note that these jobs are built with the assumption that you have a separate, active process to handle duplicate records in Dynamics. If you have duplicate records in your instance, you'll need to change the job Dynamics_List02 - Match to existing records to properly handle the case when a list record matches multiple leads or contacts.
  6. Check that you have an automated bot that imports Leads and Contacts on a frequent basis so if a list is loaded twice in close succession, duplicate records are not created in Dynamics.
  7. Run the job Dynamics_List00 - Copy List Load Input into MDS. This job will create a MDS (manual data source) that will be the repository for lists that are dropped into the list loading app. This job only needs to be run once.

Test the App

When you're ready to test, change any export task that is bypassed to allow updates. Then, prepare a series of sample lists to test the list load app. It is suggested you test each use case separately, for ease of diagnosing errors, and only include a few records in each test. Results should be verified in both the App and in Dynamics directly.

For example, test the following use cases:

  1. A list of records missing an email address. (These should all be rejected)
  2. A list of records that match existing leads in Dynamics. Select emails for records that can be updated without affecting other processes (eg. test leads)
  3. A list of records that match test contacts in Dynamics
  4. A list of net new records

To run each test:

  1. Launch the App Portal and locate the List Load application.
  2. Run the application
  3. Load a test list into the UI, and optionally remap any columns needed. This is only needed if your list has different column names from the file spreadsheet used to create your initial data source
  4. Select the "Load with Mapping" button after the list has been dropped into the app
  5. Let the app process the list, and check the outputs using the List Load Review app
  6. Check the results using Dynamics directly by looking up the records that should have been updated and/or added
  7. Repeat for each test