In this tutorial, we will cover what Product-Led Growth (PLG) funnel analysis is, why it’s important, and a step-by-step example of how to build a PLG funnel in SQL. 

What is PLG Funnel Analysis?

Product Led Growth is one of the biggest buzzwords in software right now. Performed effectively, it can be a differentiated strategy for B2B SaaS companies. In its simplest form, PLG can be defined as leveraging the product as the primary driver of customer acquisition, conversion, and expansion. An example of a typical user journey for a PLG company is:

  • Awareness - User becomes aware of the product and the value they can derive
  • Education - User engages with product education via docs, videos, etc. 
  • Trial Starts - User decides to try the product for free and enters a free trial or free community version of the product 
  • Trial Aha! Moment - User leverages the product to experience the “aha! Moment” where the user fully understands the value of the PLG product offering
  • Trial Engaged (Product Qualified Lead) - User returns to the product as a repeat user, typically daily or weekly active and becomes a Product Qualified Lead for sales
  • Sales Opportunity Created - User engages with sales for information regarding pricing, legal, and InfoSec
  • Closed Won Customer - User converts to a customer via a product purchase 

PLG Funnel analysis tracks each of these steps on the user journey. 

Why PLG Funnel Analysis is important?

PLG has proven to enable organizations to grow faster, at a lower cost of customer acquisition, than their sales-led peers. By tracking each step of the user journey, businesses can ensure that they are acquiring enough prospects and that those prospects convert to leads and customers at a high rate of growth. Understanding how a user/customer progresses through their journey enables a company to forecast the impact of changes to various stages of the user journey. Finally, by tracking the conversion rate between each of these steps, a company can identify areas to focus on to improve the entire user journey and where they should invest resources.

Data needed for PLG Funnel Analysis

This user journey touches many different data sources, so piecing it all together for analysis is complex and painful. PLG Funnel Analysis requires three primary data sources:

  • Customer Relationship Management (CRM) data
    • Customer opportunities details
    • Source
      • Salesforce
      • Hubspot
  • Product telemetry
    • Information on
      • Number of users
      • User signups
      • Repeat users
    • Source
      • Heap
      • Amplitude
      • Segment
      • Snowplow
  • Web analytics
    • Information on customer interactions with the website
      • Documentation usage
    • Source
      • Google Analytics

How to create PLG Funnel Analysis in SQL?

In this section, we will show how to build the PLG funnel analysis using SQL. We will assume the data comes from Salesforce, Heap, and Google Analytics. We will need to clean each of these datasets, calculate the information weekly and join the data together. It’s worth noting, each organization will calculate their metrics slightly differently, so this is meant to act as a general overview. You should collaborate with your business leaders internally to ensure the metrics being built represent the specific business for your organization. 

Website Visitors

A common measure of awareness is the number of visits to our website. We can get website traffic from the Google Analytics Daily Traffic table, we are interested in the number of users and sessions each week. First, we can convert the date to a week using DATETRUNC


DATETRUNC(week, date) AS week

We can use this in a common table expression (CTE) to generate the week variable and then aggregate over the week to get the total number of users and sessions during a week.


WITH weekwebdata AS (
  SELECT DATETRUNC(week, date) AS week,
  users, sessions
  FROM google_analytics_daily_traffic)
SELECT week,
SUM(users) AS website_users,
SUM(sessions) AS website_sessions
FROM weekwebdata
GROUP BY week

Documentation Visitors

To measure education, we want to capture the number of users of our documentation, amongst other user engagements. This data is not available in the Google Analytics daily traffic data, so we will need to generate it from the individual page visits in Google Analytics page traffic data. We can calculate sessions by counting the number of entrances on the page data; this captures the first page a user visited during their time on site. We can identify those pages that are docs because they contain the string Doc in their page_title using the LIKE comparison operator and the ‘%’ wildcard.


WITH weekdocdata AS (
  SELECT DATETRUNC(week, date) AS week,
  users, entrances
  FROM google_analytics_daily_traffic
  WHERE page_title LIKE '%Doc%')
SELECT week,
SUM(users) AS docs_users,
SUM(entrances) AS docs_sessions
FROM weekdocdata
GROUP BY week

Produce Usage

The key metrics here are new free users and users who come back and use the product multiple times. In particular, we are interested in users who come back at least a week after they first joined (trial engaged customers or product qualified leads). We can generate these from the daily active users data in Heap.

To calculate the new users each week (and determine if they come back a week later), we first aggregate each user’s weekly activity. In addition, as we are looking at truly new users, not new users on an enterprise account, we will first exclude those users with and organization_type of ‘ENTERPRISE’.


WITH dailyplg AS (
  SELECT DATETRUNC(week, date) AS week,
  username,
  hoursactive
  FROM daily_active_users
  WHERE organization_type != ‘ENTERPRISE’),
weeklyuser AS (
  SELECT week, 
  username,
  SUM(hoursactive) AS hoursactive
  FROM dailyplg
  GROUP BY week, username),
weeklyrepeat AS (
  SELECT week,
  username,
  lag(week, -1) OVER(PARTITION BY username ORDER BY week) AS repeat_week
  FROM weeklyuser),
newuser AS (
  SELECT username
  MIN(week) AS week,
  MIN(repeat_week) AS repeat_week
  FROM weeklyrepeat
  GROUP BY username),
weeklyrepeatindicator AS (
  SELECT week,
  username,
  CASE WHEN repeat_weak IS NOT NULL THEN 1 ELSE 0 AS repeat_user
  FROM newuser),
SELECT week,
COUNT(username) AS new_product_users,
SUM(repeat_user) AS new_product_users_will_engage
FROM weeklyrepeatindicator
GROUP BY week

Similarly, if we only want to know how many users are repeat users each week, we can build off of the CTE weekly repeat by aggregating the number of users by repeat_week.


SELECT repeat_week AS week,
COUNT(username) AS weekly_returning_users
FROM weeklyrepeat
GROUP BY repeat_week

Opportunities and Customers

Both Sales opportunities, often sales qualified opportunities (SQO) and closed won (opportunities that purchased the product) can be identified from the Salesforce Opportunities table through the use of the relevant date and appropriate flags. As salesforce does not remove deleted opportunities from the underlying data and instead marks them as deleted and hides them, we need to exclude all opportunities that have is_deleted as ‘true’ (or keep those with the value ‘false’). Since we define a qualified opportunity as one that has entered the investigate stage, we can find the day an opportunity became an SQO as the investigate_date. 


WITH sqo_weekly AS (
  SELECT DATETRUNC(week, investigate_date) AS week,
  investigate_date
  FROM salesforce_opportunities
  WHERE is_deleted = ‘false’)
SELECT week,
COUNT(investigate_date) AS SQO
FROM sqo_weekly
GROUP BY week

Similarly for closed won, the date an opportunity closed is close_date. However, in this case an prospect that has decided not to purchase will also cause the opportunity to be closed (as closed lost). To identify closed won opportunities, we also need to check that the is_won flag is set to ‘true’.


WITH cw_weekly AS (
  SELECT DATETRUNC(week, close_date) AS week,
  close_date
  FROM salesforce_opportunities
  WHERE is_deleted = ‘false’
  AND is_won = ‘true’)
SELECT week,
COUNT(close_date) AS closed_won
FROM cw_weekly
GROUP BY week

Final Metrics

All of these blocks can be put together and joined on week to create the full PLG funnel metrics by week


WITH weekwebdata AS (
  SELECT DATETRUNC(week, date) AS week,
  users, sessions
  FROM google_analytics_daily_traffic),
weeklywebtraffic AS (
  SELECT week,
  SUM(users) AS website_users,
  SUM(sessions) AS website_sessions
  FROM weekwebdata
  GROUP BY week),
weekdocdata AS (
  SELECT DATETRUNC(week, date) AS week,
  users, entrances
  FROM google_analytics_daily_traffic
  WHERE page_title LIKE '%Doc%'),
weeklydoctraffic AS (
  SELECT week,
  SUM(users) AS docs_users,
  SUM(entrances) AS docs_sessions
  FROM weekdocdata
  GROUP BY week),
sqo_weekly AS (
  SELECT DATETRUNC(week, investigate_date) AS week,
  investigate_date
  FROM salesforce_opportunities
  WHERE is_deleted = ‘false’),
opportunities AS (
  SELECT week,
  COUNT(investigate_date) AS SQO
  FROM sqo_weekly
  GROUP BY week),
cw_weekly AS (
  SELECT DATETRUNC(week, close_date) AS week,
  close_date
  FROM salesforce_opportunities
  WHERE is_deleted = ‘false’
  AND is_won = ‘true’),
closewon AS (
  SELECT week,
  COUNT(close_date) AS closed_won
  FROM cw_weekly
  GROUP BY week),
dailyplg AS (
  SELECT DATETRUNC(week, date) AS week,
  username,
  hoursactive
  FROM daily_active_users
  WHERE organization_type != ‘ENTERPRISE’),
weeklyuser AS (
  SELECT week, 
  username,
  SUM(hoursactive) AS hoursactive
  FROM dailyplg
  GROUP BY week, username),
weeklyrepeat AS (
  SELECT week,
  username,
  lag(week, -1) OVER(PARTITION BY username ORDER BY week) AS repeat_week
  FROM weeklyuser),
newuser AS (
  SELECT username
  MIN(week) AS week,
  MIN(repeat_week) AS repeat_week
  FROM weeklyrepeat
  GROUP BY username),
weeklyrepeatindicator AS (
  SELECT week,
  username,
  CASE WHEN repeat_weak IS NOT NULL THEN 1 ELSE 0 AS repeat_user
  FROM newuser),
weeklyrepeatuser AS (
  SELECT repeat_week AS week,
  COUNT(username) AS weekly_returning_users
  FROM weeklyrepeat
  GROUP BY repeat_week).
weeklynewuser AS (
  SELECT week,
  COUNT(username) AS new_product_users,
  SUM(repeat_user) AS new_product_uses_will_engage
  FROM weeklyrepeatindicator
  GROUP BY week)
SELECT wt.week, 
COALESCE(wt.website_users,0), 
COALESCE(wt.website_sessions,0),
COALESCE(dt.docs_users,0), 
COALESCE(dt.docs_sessions,0),
COALESCE(opp.SQO,0),
COALESCE(cw.closed_won,0),
COALESCE(nu.new_product_users,0), 
COALESCE(nu.new_product_users_will_engage,0),
COALESCE(ru.weekly_returning_users,0)
FROM weeklywebtraffic AS wt
LEFT JOIN weeklydoctraffic AS dt
ON wt.week = dt.week
LEFT JOIN opportunities AS opp
ON wt.week = opp.week
LEFT JOIN closewon AS cw
ON wt.week = cw.week
LEFT JOIN weeklynewuser AS nu
ON wt.week = nu.week
LEFT JOIN weeklyrepeatuser AS ru
ON wt.week = ru.week

We have now created the key metrics for measuring the PLG Funnel. These can be visualized with any BI tool or analyzed to understand how the business is performing and to determine where to focus future efforts at improving the user journey.

Conclusion

Visibility into the PLG funnel is key to running a successful PLG business. Achieving this visibility requires combining data from multiple sources, but with the power of SQL, we can quickly generate this combined data. From this data, executives will have the ability to understand how the business is performing and make informed, data-driven decisions about what to do next.

No-code/low-code data prep and visualization