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	[email protected]	In Progress	0022f000e084Eh9AAB

Exercise

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

Log into the Savant tenant connected to your work email account.
Add your production Salesforce as a system using your SFDC user credentials (Use this Salesforce Connection Setup guide for reference)
Create a dataset from Salesforce Account, ensuring you have included the following fields:
1. Account ID
2. Account Name
3. Country
4. Website
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

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
You only want accounts in the United States, so add a filter to your account table for Accounts in the United States
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)
```
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
```
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)
```
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”
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:
Configure a Google Sheets destination to create a new tab and replace the data on the next run.
Create an arrange tool from the Join tool and format a table like this:
Finally, create another Google Sheet destination to house this data.
Run a test run of the application you built and preview the results.
Kick off an ad hoc run of the app and confirm it is writing to the Google Sheets as expected.
Turn your application into a bot by creating a weekly schedule for your app.

Match Leads to Accounts