this final lab, we are going to bring together many of the skills you have learned to build a full-funnel representation of your Salesforce organization, incorporating leads, accounts, and opportunities. In this full-funnel, you get to build a model of your leads and opportunities that you can slice and dice. We will continue to learn new concepts so that you have real, usable code that you can put to work immediately!
Let’s quickly recap a few very important concepts….
Structuring your SQL
Together, we explored and learned the proper way to structure a SQL SELECT statement with the most important clauses, expressions, and keywords that will help you match your data needs with the right SQL code. With enough practice, you actually start to translate requirements into code.
Clause Name
Description
Status
WHERE
Filters the results to include only those rows that meet the specified condition.
CHECK
GROUP BY
Groups rows that have the same values in specified columns, often used with aggregate functions like SUM, AVG,MAX,MIN
CHECK
ORDER BY
Used to sort the result set in ascending or descending order
CHECK
HAVING
Filters grouped data (only when using GROUP BY
CHECK
AI is your Friend
As we covered early in this course, you should absolutely consider AI as a universal translator and learning aid. Knowing SQL fundamentals, Salesforce, and LLM basics will only accelerate your learning.
In our final lab, we will walk through a framework that I use to gather requirements and determine the path forward. Using the same principles you utilize to requirements for your Salesforce org, we can ensure we write SQL code that properly addresses a real-world problem.
Background
A fairly common use case for reporting and analytics is a full-funnel model that helps marketing, sales, and revenue operations stakeholders complete the complete lead to closed won lifecycle and lifetime value for accounts (assumes accounts represent businesses or individual customers). Using the standard Salesforce data model where leads are converted to Accounts (with or without opportunities), we will capture the key metrics that matter most. We will create an SQL query that will help us model and explore the Salesforce funnel.
1. Scope, Outcome, and Impact Definition
Business Challenge: Typically I clarify the business challenge we are solving for before a single line of code is written. It is too easy to get lost in data, data wrangling, and coding. Having a clearly stated business challenge gives you a nice way to take a step back and ask “what problem are we solving?”
In this particular case, our business challenge is the lack of visibility for conversion rates from lead to opportunity, lifetime value in revenue, and geographic segmentation. For this course I have purposely left the business challenge vague but hopefully relatable for you.
Outcome: When you code a solution and deliver the data and/or analysis, what is the desired outcome?
In this particular case, we will consider the visibility of lifetime value by lead creates:
Impact: The impact of improved data-driven decision-making and enhanced understanding of the sales funnel’s performance should hopefully tie back to a specific metric or KPI. If not, it’s very hard to articulate the value of the SQL work you are performing and who benefits.
Business Pain: Not every project you work on has a groundbreaking revenue-generating and saving impact. Sometimes you are implementing code that improves an individual or group of users’ jobs easier. Do not underestimate the value of using data and creating data assets with SQL that can facilitate new knowledge.
SQL mastery without the ability to articulate “WHY” is a waste of good skills, so hopefully through learning a practical application of SQL coding, you are armed for real success! Let’s move from our problem and put your new SQL skills to use!
2. Data Selection and Preparation
Object Selection – For our lab, to obtain all of the data points we need to report our full-funnel, we are going to use LEAD, ACCOUNT, and OPPORTUNITY.
Field Selection – The fields we select likely mirror reports present in reports that you already use. There are several tactics that Salesforce admins and developers use to produce funnel metrics inside of Salesforce core. We will keep it simple for our lab and select the fields to produce 3 metrics:
1- Leads (from the lead object). 2 – Qualified Leads (from the lead object). 3 – Qualified Lead to Opportunity Ratio (requires lead and opportunity data). 4- Revenue (from the opportunity object). 5- Won Opportunities (from the opportunity object). 6- Customers (from the account object).
Data Exploration: Typically, when I build reports and analytics based on Salesforce data, I start with existing reporting and dashboard assets that business stakeholders already use. The self-service reporting is quite good in Salesforce, so it is not uncommon for many of the metrics within a funnel to exist already in some form inside of Salesforce.
Snowflake’s SQL capabilities allow you to combine conditional expressions with date functions for more complex data manipulation. For example, you can determine the next renewal date based on the opportunity stage:
CASE
WHEN "StageName" = 'Closed Won' THEN DATEADD('years', 1, "CloseDate")
ELSE NULL
END AS "Next Renewal Date"
In this case, if the opportunity stage is “Closed Won”, the query calculates the renewal date as 12 months after the close date. If the stage is anything else, it leaves the renewal date as NULL, effectively using conditional logic to tailor your data analysis.
By mastering these date-handling functions in Snowflake, you can enhance your data analysis capabilities, making your reports more dynamic, accurate, and reflective of your operational realities. Whether you’re adjusting for time zones, calculating durations, or projecting future dates, Snowflake provides the tools necessary to navigate the complexities of date and time data efficiently.
Calculating the last day of a period, such as a month or a year, is a frequent requirement in data analysis, especially when dealing with financial, sales, or performance data that is often summarized on a monthly or annual basis. The LAST_DAY function simplifies this task by returning the date of the last day of the period that contains the specified date.
The basic syntax for the LAST_DAY function in Snowflake SQL is:
<date_or_time_expr> is the date or timestamp expression from which you want to find the last day of the period. [<optional_date_part>]: This optional argument allows you to specify the part of the date to consider for the calculation, such as MONTH or YEAR. If omitted, the default is typically MONTH.
For example, if we had a lead that was created mid-January, when we apply the LAST_DAY function for ‘month’ let’s explore the syntax and result:
LAST_DAY ("CreatedDate",'month',) “Month End Date”
RESULT EXAMPLE (assuming Created Date is ‘2024-01-25’)
2024-31-01
What if we wanted to calculate the last day of the year? If your SQL looks like the following, you are on the right track!
LAST_DAY ("CreatedDate",year,) “Year End Date”
RESULT EXAMPLE (assuming Created Date is ‘2024-01-25’)
Date Truncation is incredibly useful for aggregating, analyzing, and reporting data over specific time intervals, such as years, months, days, or even hours and minutes. By “truncating” a date, you essentially round it down to the nearest specified unit, which can simplify comparisons, aggregations, and trend analysis over periods of time.
The DATE_TRUNC function works by taking two arguments: the unit of time to which you want to truncate the date, and the date or timestamp value itself. The function then returns a new date value rounded down to the closest specified time unit, effectively “normalizing” your date data to that level of granularity.
For example, to find the current month for the lead created date you would write SQL like this:
DATE_TRUNC('months', "CreatedDate") “Lead Created Month”
RESULT EXAMPLE (assuming Created Date is ‘2024-02-25’)
2024-02-01
Snowflake simplifies the process of calculating the difference between dates and performing date arithmetic, such as adding or subtracting time intervals from specific dates.
To calculate the difference between two dates, you can utilize the DATEDIFF function. The format of the function is as follows:
In the realm of global data management, dealing with time zones is inevitable. Salesforce, for instance, stores all DateTime fields in UTC (Coordinated Universal Time) so it is time zone independent. This is incredibly useful to ensure data that is deployed to users reflects their local timezone when events occur. When you extract data into a data warehouse like Snowflake, you typically do not convert the timezone.
When you run analysis and aggregate data by a DateTime formatted field, you could experience small discrepancies from summary data in Salesforce reports. This is due to the difference between UTC and your local time zone. CONVERT_TIMEZONE function, allows you to adjust DateTime values to the appropriate local time zone, thus ensuring consistency and accuracy in your reports and analyses.
Example:
Suppose your operations are based in California, and you need to convert a UTC CreateDate to the local time in Los Angeles. You can achieve this with the following Snowflake SQL command:
SELECt *, CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', "CreateDate") AS "PST Created Date" FROM SFDC.STAGE.LEAD TZ Create Date"
This function not only caters to specific needs like the above but also supports a broad range of time zones listed in the IANA Time Zone Database, providing flexibility for global operations.
Most databases, Snowflake included, provide complete control over timezone data and how you store and query data. In our lesson, we focused solely on setting time zones while querying data. If you want to learn more about storing data in a time-zone-dependent format (https://docs.snowflake.com/en/sql-reference/data-types-datetime), Snowflake provides additional documentation. Unfortunately, this goes beyond the scope of our training.
Now that we are exploring the world of expressions, we know from experience that data by nature is imperfect. Your Salesforce org contains many fields missing data for a multitude of reasons. Without going deep into the details of how a database works, know that there are scenarios where you have to account for NULL values.
Salesforce approach
If you have ever built a formula field, Salesforce will give you the option to treat NULLs as 0s. This is a very useful function in particular when you are working with calculations and values. If your conditional function with CASE or IFF contains values with NULLs, and your output is a value, you will definitely want a condition that handles NULLs.
Snowflake
IFNULL(<expr1>, <expr2>): This function checks if expr1 is NULL. If it is, the function returns expr2; otherwise, it returns expr1. This is particularly useful in Salesforce formula fields where you might want to replace NULL values with a default value (like 0) to ensure calculations can proceed without errors.
SELECT
IFNULL("NUMBEROFLOCATIONS__C",0);
ZEROIFNULL(<expr>): This function does the opposite of IFNULL. It returns 0 if the expression (expr) is NULL. This aligns with the Salesforce approach of treating NULL values as 0s, which can simplify calculations by eliminating the need to check for NULL values explicitly. It’s particularly useful in numerical calculations where NULL values can otherwise disrupt summing, averaging, or other aggregate functions.
