SFDC Data Normalization Hack Setup

Using Coefficient

Read Time: 9 minutes

A HUGE thank you to our newsletter sponsors for moving this newsletter to free for our readers. PAID subscribers will continue to receive access to the templates from this newsletter. If you're reading this but haven't subscribed, join our community of over 1,264 crazy smart Revenue leaders. If you’d like to sponsor the newsletter reply to this email to learn more.

Today’s Paid Subscribers will receive access to several Appscript and Salesforce field formulas

In this newsletter I’m going to walk through how to build out a data normalization workflow using Google Sheets, Salesforce, and Coefficient. We’ll clean up the Account object in this example. If you follow me on LinkedIn I often talk about building out a Data Dictionary. What goes inside that dictionary is a data normalization strategy. It sounds tedious, but it can make a world of difference when it comes to executing plans with a clean data set.

Data normalization is the process of organizing data in a database so that it is consistent, accurate, and easily searchable. In Salesforce, there are several areas where data normalization is critical. Here are five areas to consider when devising a normalization strategy:

1. Account Information

2. Contact Information

3. Product Codes

4. Lead Sources

5. Case Categories

Let’s dive into each of these briefly then we’ll jump into our data-normalization-hack.

Account Information:

Accurate and consistent account data is essential for effective lead generation, sales forecasting, and customer relationship management. My helpful tips for setting up clean accounts:

• Account Name: use the name which the account is known by such as Nike

• Legal Account Name: Nike, Inc. Use the legal account name for your order forms and invoicing. This information will generally come from your data provider (i.e. ZoomInfo, Clearbit).

• State: I generally prefer using abbreviations. Here are a few ways you can do this:

  • Use Salesforce native functionality: From Setup, enter State and Country/Territory Picklists in the Quick Find box, then select State and Country/Territory Picklists. On the State and Country/Territory Picklists page, click Configure States, Countries, and Territories.

  • Use a VLookup in Google Sheets after pulling the data down

  • Create a custom function inside Google Sheets (use the code in the Paid Template): To use this formula in your Google Sheet, simply enter =STATEABBR(A2) into a cell, where A2 is the cell containing the full state name you want to convert to an abbreviation. The formula will return the corresponding abbreviation or "N/A" if the state name is not found in the list.

• Account Status: Working, No activity L30D, Active Opportunity, Customer Active Opportunity, Customer No Active Opportunity, No Longer In Operation, Competitor, Partner, Not a Fit

• Engage Status: This is a custom field I use to set up a similar structure to an Marketing Qualified Lead (MQL) but I call it a Marketing Qualified Account (MQA). If an Account not only fits our target account or ICP, but also demonstrates intent/engagement then the account is moved to become an MQA. This will trigger an alert to the sales team.

  • No Engagement

  • Intent

  • MQA

  • Engaged

  • If worked then the status moves to Prospecting and the Account Status field above moves to Working

  • Once worked, the concluding outcomes will be:

    • Open Opportunity (Account Status = Active Opportunity OR Customer Active Opportunity)

    • Disqualified (Account Status = Not a Fit)

• Type: Prospect, Partner, Customer, Supplier, Investor

• Industry: Let’s assume you use ZoomInfo and choose to use their parent level category.

• Sub Industry: Let’s assume you use ZoomInfo and choose to use their secondary level category.

• Website: I prefer to remove the following elements:

  • Http:

  • “/”

  • Sub-directories after the forward slashes

    ---

    ---

    Paid subscribers receive access to two methods of doing this: 1/ Appscript in Google Sheets to clean this up AND 2/ Salesforce formula which you can use a Salesforce Flow to cleanup

    ---

    ---

• Phone Number: For phone numbers I prefer stripping out non-numeric characters and then to format for international numbers. For international phone numbers add back in the “+” sign. I detail the Salesforce formula in the template below.

Contact Information:

Normalize contact information, such as addresses and phone numbers, to avoid data redundancy and ensure data accuracy. I won’t be going into detail on the how but here is what I would focus on:

1. Contact Status

2. Contact Record Type

3. Phone number

4. Mobile phone number

5. Custom field: Reference (will serve as a reference)

6. Custom field: Billing Contact (checkbox)

7. Custom field: Reports To (Lookup)

8. Title

9. Custom field: Function

10. Custom field: Level

11. Custom field: Persona (checkbox or dropdown)

Product Codes:

Standardize product codes to ensure consistency across the organization and to make it easier to search and analyze data.

1. Product Family

2. Product Name

3. Custom field: Product Certification Required (used for orgs where sales needs to pass an enablement certification in order to sell)

4. Unit Price

Opportunity Stages:

Define clear opportunity stages and ensure that they are consistently applied across the organization. This can help with forecasting and pipeline management.

Please see the Developing Sales Stages article. 👇

Developing Sales Stages

Lead Sources:

Define clear lead sources and ensure that they are consistently applied across the organization. This can help with tracking marketing effectiveness.

Please see the Marketing Attribution article. But here’s some guidance:

• Custom field Lead Source: Inbound vs Outbound

• Custom field: Lead Source Detail: use UTM Source

Also! I would highly advocate you settle on how you want to discuss your attribution models. Last Touch is fine, but I’d also argue it should be paired with a more nuanced multi-touch journey to avoid the Attribution Error Bias.

Marketing Operations Series: Attribution Hierarchy and why it matters

Cases

Next week I’ll go over a detailed guide on how to set up Salesforce Cases to manage internal revenue operations tickets. Some organizations use Asana, Jira, or other ticketing solutions but I always prefer to use something off-the-shelf when and where I can. Why buy more software when you don’t need to. Here is a look at a previous case setup I’ve used for the Type field:

1. Commissions question

2. Data integrity (used for Revenue data, employee count, industry)

3. Ownership dispute (used for record ownership)

4. Ad hoc reporting request

5. New tools

Alright let’s move on to the data normalization hack!

So here’s what we’re going to do.

1. Set up a scheduled data import from Salesforce into Google Sheets using Coefficient

2. Build out Appscript functions to normalize the data

3. Set up a scheduled data export from Google Sheets into Salesforce using Coefficient

4. Voilà! We’re done.

Scheduled Data Import

In Coefficient we’ll set up a scheduled data import that a SOQL query. In Google Sheets, select the Extensions tab and select Coefficient. Once there, select Import from Salesforce then select Custom SOQL query.

Use this SOQL query:

WHERE SELECT Id, Name, BillingState

FROM Account

WHERE NOT BillingState LIKE '__'

In the above query, Id, Name, and BillingState are the fields we want to retrieve from the Account object. The LEN() is critical in normal SQL but for some reason Salesforce SOQL does not have this basic function. Instead, we’re going to use a hack where we use the LIKE function to search for any records that are not two characters in length with two underscores.

You can modify the query to suit your specific needs, such as adding additional fields to retrieve or changing the length condition.

Use AppScript to normalize the data

The resulting AppScript will have a function called STATEABBR. If Cell A2 is “Maryland” then typing in =STATEABBR(A2) will result in “MD”.

We’ll copy this formula all the way down.

Scheduled Data Export

I’m a huge fan of Coefficient. Before they became a newsletter sponsor I used them religiously for a variety of use cases. Cleaning up the data in an offline process, away from leveraging a Process Flow in Salesforce, allows me to decrease the amount of CPU usage.

I’ve relabeled column D to “Upload to State” to distinguish between the BillingState field that’s downloaded and the new column that will be used for an upload.

First, reopen Coefficient and select Export. Also, I just noticed the new GTP Copilot feature. Don’t know what that is but I’ll be digging into it.

Next, select Salesforce. On the next screen I type in the following. I type in Row 2 as the header since that’s where the field labels are in my sheet.

The next few steps involve mapping your fields. Because the labels came down as the API Names, Coefficient will not automatically match. If you want to do that then you’ll need to replace the column names with the Field Labels instead. I opt for the manual selection because it gives me greater comfort knowing I’m selecting the right field. Remember, I created a brand new column for billing state to update. So you’ll see in the image below I’ve skipped over the billing state data that was downloaded and instead will upload the new column that has the Google Function.

And that’s it!

I hope this helped set up a semi-automated data normalization tool at a relatively great value. Combining Google Sheets Appscript, Coefficient, and Salesforce you should be able to update a great portion of your database relatively quickly.

Whenever you're ready, there are 2 ways I can help you:

1/ If you’re looking to further develop your Revenue Operations knowledge sign up for my courses in partnership with the RevOps Co-Op. 

→ Unleashing ROI course. A ten-week virtual, live instruction RevOps course designed to level up your RevOps Impact (R.O.I.). Lessons from my career scaling from $10M to $100M+. Join 50+ alumni. https://www.revopscoop.com/learn/unleashing-roi-course

→ Sales Ops Masterclass. A six-week virtual, live instruction SalesOps course designed to take your sales operations skills to the next level. https://www.revopscoop.com/learn/salesops-masterclass

2/ Promote your Revenue focused startup to a newsletter with over 900 tenacious revenue leaders. My eventual goal is to shift this to a completely free newsletter for my readers through sponsored ads. Reply to this email if you’re interested in receiving a media kit to learn more.

Template for Paid Subscribers

• Appscript + Salesforce custom formulas