Blog Archives

With our Lead Query created, next, we will create an Opportunity query. Create a SELECT statement for SFDC.STAGE.OPPORTUNITY with a table alias of “O” and include the following fields:

Id, CreatedDate, Revenue, Stage, AccountId, CloseWonDate

If you find that the first attempt does not work perfectly, use the errors to help guide you along the way. Here are some tips:

1. Did you include commas after the field name?
2. Did you add the table Alias correctly?
3. Did you declare you get single vs double quotes correct?
4. Did you remember to alias your table and fields?

Opportunity Query Result:

SELECT 
O.Id,
O.CreatedDate,
O.ExpectedRevenue,
O.StageName,
O.AccountId,
O.CloseDate,
O.Type
FROM SFDC.STAGE.OPPORTUNITY O;

Revenue Pipeline

Our next metric is called “Revenue Pipeline.” The distinction of pipeline indicates that we are measuring revenue at any Opportunity stage. This is a great example where having well-written definitions and a universal understanding of metrics and facts are very important. Because there are no implied constraints or calculations required, you can simply use the Opportunity Revenue field in your query. As it turns out we don’t need to change. As it turns out, the standard field as it stands within the opportunity is sufficient and already included in our query.

Result

O.ExpectedRevenue

New Customer Won Opportunities

The next metric is “New Customer Won Opportunities” where you get to put your conditional expression skills to use again. This time, you have two conditions. Give it a shot on your own.

1. Did you declare you get single vs double quotes correctly?
2. Did you utilize AND / OR to add two conditions?

Result

CASE
WHEN O.StageName = 'Closed Won' AND O.Type = 'New Customer' then 1
ELSE 0 END "New Customer Won Opportunities"

Customers

Our final metric is designed to measure the total number of distinct customers in Salesforce. A “Customer” in this case has a very specific definition that requires an Account to have at least 1 closed-won opportunity. Unlike the other metrics, I will share how we achieved this metric in Salesforce. I would need to build an Opportunity report that filters data for Closed-Won deals and then calculate Unique Account IDs.

Creating this metric in SQL is no different. In the spirit of breaking down a problem into its smallest parts, we need to:

1. Isolate AccountIds that have a Closed-Won opportunity.
2. Aggregate and COUNT the distinct AccountIds to determine  our “Customers” metric.

At this point, while writing our SQL for an Opportunity we can only perform 1 of the steps. Create a field that returns the AccountIDs for Opportunities that have reached a Closed-Won stage. 

Hints

1. You have technically built a nearly identical field.
2. Rather than a 0/1, you want to return the AccountId field.
3. If the condition is false (meaning that the opportunity was not closed won), you should just return a NULL value.

Result

CASE
WHEN O.StageName = 'Closed Won' THEN O.AccountId
ELSE NULL END "Closed Won AccountIDs",

Or you can use

IFF  (O.StageName = 'Closed Won' ,O.AccountId, NULL)
"Closed Won AccountIDs2"

As a recap for this field, we have not calculated the final metric, but we have created a calculated field that we need to accurately measure “Customers”. We will visit this aggregate metric at the same time that we work on the Qualified Lead to Won Opportunity Ratio.

Final Opportunity Query Result

SELECT
O.Id,
O.CreatedDate,
O.ExpectedRevenue,
O.StageName,
O.AccountId,
O.CloseDate,
O.Type,
CASE
WHEN O.StageName = 'Closed Won' AND O.Type = 'New Customer' then 1
ELSE 0 END "New Customer Won Opportunities",
CASE
WHEN O.StageName = 'Closed Won' THEN O.AccountId
ELSE NULL END "Closed Won AccountIDs",
FROM SFDC.STAGE.OPPORTUNITY O;

In any data analysis, data science, or data engineering project, clear and thorough documentation is as crucial as the technical execution itself. Proper documentation ensures that your SQL queries, data models, and analytical insights are accessible, understandable, and usable by others, including your future self. This section will cover the importance of documenting SQL queries and their intended outcomes, along with best practices for effective documentation and delivery of your work.

Knowledge Sharing: Documentation acts as a knowledge base, allowing team members to learn from existing work. It helps new team members get up to speed and supports cross-functional collaboration.

Reproducibility and Consistency: Documenting the context, assumptions, and parameters used in your queries ensures that results can be reproduced and verified, which is critical for maintaining data integrity and consistency across reports and analyses.

Maintenance and Scalability: As data projects evolve, queries and data models need to be updated or optimized. Comprehensive documentation makes maintenance easier and supports the scalability of your data infrastructure.

Best Practices for Documenting SQL Queries

Commenting Within Queries: Use comments liberally within your SQL scripts to explain complex logic, the purpose of specific clauses, and the rationale behind choosing certain approaches over others. Comments can be inline or block comments, depending on the complexity of the explanation.

Query Descriptions: At the beginning of each SQL script or stored procedure, include a descriptive header that outlines the script’s purpose, author, creation date, and any modifications along with their dates and reasons.

Data and Metrics Dictionary: Maintain a data dictionary or schema documentation that describes each table and column used in your queries, including data types, constraints, and any relationships between tables. This is especially important in complex databases with numerous tables and fields.

Assumptions and Context: Document any assumptions made during the query development process, such as data cleanliness, uniqueness of key fields, or time zone considerations. Include context about the business logic or analytical goals driving the query’s design.

Result Interpretation: Guide how to interpret the results of your query, especially for complex analyses that might not be immediately intuitive. Include any caveats or limitations of the analysis.

Version Control: Use version control systems like GIT to manage your SQL scripts. This allows for tracking changes over time, understanding the evolution of your queries, and facilitating collaboration among team members.

External Documentation: For complex projects, supplement in-code comments with external documentation, such as Wiki pages, README files, or data analysis reports. These documents can provide broader context, detailed methodology, and analytical insights derived from the queries.

The queries from this lab have been re-organized logically before we combine them:

LEADS QUERY

SELECT  
L.Id, 
L.CreatedDate, 
L.ConvertedOpportunityId, 
L.ConvertedAccountId, 
L.Status, 
L.ConvertedDate, 

--CALCULATED FIELDS

'1' as "Lead Count", 
CASE WHEN L.Status = 'Open - Not Contacted' then 0 ELSE 1 END "Qualified Leads" 
FROM 
  SFDC.STAGE.LEAD L;

OPPORTUNITY QUERY

SELECT  
O.Id, 
O.CreatedDate, 
O.ExpectedRevenue, 
O.StageName, 
O.AccountId, 
O.CloseDate, 
O.Type, 
CASE WHEN O.StageName = 'Closed Won' 
AND O.Type = 'New Customer' then 1 ELSE 0 END "New Customer Won Opportunities", 
CASE WHEN O.StageName = 'Closed Won' THEN O.AccountId ELSE NULL END "Closed Won AccountIDs" 
FROM 
  SFDC.STAGE.OPPORTUNITY O;

FULL OUTER JOIN ON LEAD AND OPPORTUNITY

SELECT 
  L.Id as "Lead Id", 
  L.ConvertedAccountId as "Converted Lead Id", 
  O.Id as "Opportunity Id", 
  O.AccountId "AccountId" 
FROM 
  SFDC.STAGE.LEAD L FULL 
  OUTER JOIN SFDC.STAGE.OPPORTUNITY AS O ON L.ConvertedAccountId = O.AccountId;

Step 1: Attempt to merge and append the Lead fields into the full outer Join query.

SELECT 
  L.Id as "Lead Id", 
  L.ConvertedAccountId as "Converted Lead Id", 
  L.CreatedDate, 
  L.ConvertedOpportunityId, 
  L.Status, 
  L.ConvertedDate, 
  O.Id as "Opportunity Id",   
  O.AccountId "AccountId",
  '1' as "Lead Count", 
  CASE WHEN L.Status = 'Open - Not Contacted' then 0 ELSE 1 END "Qualified Leads" 

FROM 
  SFDC.STAGE.LEAD L FULL 
  FULL OUTER JOIN SFDC.STAGE.OPPORTUNITY AS O ON L.ConvertedAccountId = O.AccountId;

Step 2: Attempt to merge the Opportunity fields into the full outer Join query.

Tips:

1. You only need to include the Opportunity fields and calculated fields. Do not repeat the SELECT statement.
2. You may have errors and that is Okay. We will troubleshoot and resolve them together!

SELECT 
  L.Id as "Lead Id", 
  L.ConvertedAccountId as "Converted Lead Id", 
  L.CreatedDate, 
  L.ConvertedOpportunityId, 
  L.Status, 
  L.ConvertedDate, 
  '1' as "Lead Count", 
  CASE WHEN L.Status = 'Open - Not Contacted' then 0 ELSE 1 END "Qualified Leads", 
  O.Id as "Opportunity Id", 
  O.AccountId "AccountId", 
  O.CreatedDate, 
  O.ExpectedRevenue, 
  O.StageName, 
  O.CloseDate, 
  O.Type, 
  CASE WHEN O.StageName = 'Closed Won' 
  AND O.Type = 'New Customer' then 1 ELSE 0 END "New Customer Won Opportunities", 
  CASE WHEN O.StageName = 'Closed Won' THEN O.AccountId ELSE NULL END "Closed Won AccountIDs" 
FROM 
  SFDC.STAGE.LEAD L FULL 
  OUTER JOIN SFDC.STAGE.OPPORTUNITY AS O ON L.ConvertedAccountId = O.AccountId;

Resolving SQL Runtime Errors

In this particular example, and in many joined Salesforce tables you will run into conflicting column names. As a result, you will want to get into the habit of aliasing field names that are common across objects.

To produce some of the metrics in our glossary we will use conditional expressions we learned about in chapter 6.

Lead Count

Let’s create our first metric which takes us to our very first lab. If you wanted to represent the number ‘1’ for every record and call the field “Lead Count”, how would you format your SQL?

Result: 

'1' as "Lead Count"

Qualified Leads

For our second metric, we want to count “Qualified Leads”, which are represented as leads where the LeadStatus is not “Open – Not Contacted”. We will use the work you performed in lesson 6 to create a conditional expression. There are several approaches to solving this problem. 

Result: 

CASE
WHEN L.Status = 'Open - Not Contacted' then 
ELSE 1 END "Qualified Leads"

You can also use:

IFF (L.Status = 'Open - Not Contacted', 0 ,1) "Qualified Leads2"

Qualified Lead to Won Opportunity Ratio

Our third metric is a ratio which requires a numerator and denominator to successfully create the metric. We have already created the denominator, Total Qualified Leads. To create the numerator, “Won Opportunities” we need data from the Opportunity Object which will come later.

Additionally, the ratio we are looking for is a rollup/ aggregated value, so that will require some additional SQL that we will create together. We will come back and revisit this Lead to Won opportunity ratio metric later in this lesson!

Final Lead Query Result

SELECT 
L.Id,
L.CreatedDate,
L.ConvertedOpportunityId,
L.ConvertedAccountId,
L.Status,
L.ConvertedDate,
'1' as "Lead Count",
CASE
WHEN L.Status = 'Open - Not Contacted' then 0
ELSE 1 END "Qualified Leads",
FROM SFDC.STAGE.LEAD L;

Other query considerations based on previous lessons:

Alias

As you may have noticed, we only have alias fields that originate directly from the Leads table and not newly created fields.

WHERE (filter Data)

For the lead object, we have not defined any constraints that would filter and remove lead records from our result. In other words, we do not need a WHERE clause.

GROUP BY (group and aggregate)

At this stage, the lead record does not need to be aggregated. In other rods, we do not need a GROUP BY clause.