All Collections
Practice Exercises
Sales and Marketing
Salesforce Lead/Account Matching Exercise
Salesforce Lead/Account Matching Exercise

If you have access to Salesforce, you can test your Savant skills with this practical Lead to Account Matching exercise

Updated over a week ago

Business Context

Your business has asked you to produce a report showing how many Salesforce leads are from each existing account in your SFDC account table. You are also asked to map an Account ID, where possible, to leads.

You envision two outputs to this analysis:

A table that counts leads by account to be delivered to Google Sheets

SFDC Account ID

Account Name

Count of Leads

0035f0000084Eh9AAE

Acme Corp Inc. LLC

5

A table that maps Account ID to leads to be delivered to Google Sheets (for testing, then eventually updates SFDC Lead directly)

SFDC Lead ID

Lead Email

Lead Status

SFDC Account ID

0035f0000084Eh9AAE

In Progress

0022f000e084Eh9AAB

Exercise

Connect Savant to your Salesforce and build the datasets you’ll need

  1. Log into the Savant tenant connected to your work email account.

  2. Add your production Salesforce as a system using your SFDC user credentials (Use this Salesforce Connection Setup guide for reference)

  3. Create a dataset from Salesforce Account, ensuring you have included the following fields:

    1. Account ID

    2. Account Name

    3. Country

    4. Website

  4. Create a dataset from Salesforce Lead, ensuring you have included the following fields:

    1. Lead ID

    2. Email

    3. Status (If you use a custom field for Lead Status, use that)

    4. Created Date

Build your application

  1. You only want leads created in 2021/2022, so add a filter to your leads table for leads that were created AFTER 12/31/2020

  2. You only want accounts in the United States, so add a filter to your account table for Accounts in the United States

  3. It will be difficult to join website in your Account data to an email domain (website has “http://”, “www.”, etc) so add a transformation tool connected to your account filter to extract website domain:

    1. Add a field called “Website Domain Extracted” and enter the following logic:

      SPLIT_PART(
      REPLACE(REPLACE(REPLACE(`Website`,
      'https://', ''),
      'http://', ''),
      'www.', ''),
      '/',
      1)

  4. After you have extracted the domain, you need to ensure it is clean

    1. Add another transform tool connecting to the transform tool you created in step 3, add a field called “Website Domain Validated”, then enter the following logic:

      CASE WHEN
      STR_POS(`Website Domain Extracted`, '.') > -1
      AND LENGTH(`Website Domain Extracted`) > 4
      THEN `Website Domain Extracted`
      ELSE 'NULL1' END

  5. Now that you have cleaned the website field, move your attention to extracting domain from lead email.

    1. Add a transformation tool connected to your Lead filter, create a new column called “Email domain”, then enter the following logic:

      SPLIT_PART(
      `Email`,'@',2)

  6. Now that you have a clean email domain and a clean website domain, let’s join them together by adding a Join tool.

    1. Add a Join tool and connect the account cleaning transform to the top connection point, and connect the lead cleaning transform to the bottom connection point. You do this because you want to left join account to lead to retain all accounts.

    2. Left join on “Website Domain Validated” = “Email Domain”

  7. Now that you have joined the two tables together, add a Summarize tool from the join step to count leads by account, creating a table that looks like this:

  8. Configure a Google Sheets destination to create a new tab and replace the data on the next run.

  9. Create an arrange tool from the Join tool and format a table like this:

  10. Finally, create another Google Sheet destination to house this data.

  11. Run a test run of the application you built and preview the results.

  12. Kick off an ad hoc run of the app and confirm it is writing to the Google Sheets as expected.

  13. Turn your application into a bot by creating a weekly schedule for your app.

Did this answer your question?