This SQL script calculates Net Promoter Score (NPS)-related metrics for...

September 4, 2025 at 05:51 PM

WITH dates as
(
  SELECT trunc(dateadd(month, -24, date_trunc('MONTH',getdate()))) startAvgDate
  , trunc(dateadd(month, -1, max(date_trunc('MONTH',document_date))::date)) as endAvgDate
  , trunc(max(date_trunc('MONTH',document_date))::date) as latestDataDate
  FROM gss_ds.fct_all_bu_rltr_and_osat_sales
), client_nps_summary as 
(
  SELECT c.account__c, ci.account_business_unit  --, m.customer_number --for analysis only
  , '[' || LISTAGG(DISTINCT m.likely_to_recommend, ',') WITHIN GROUP (ORDER BY m.document_date) || ']' as likely_To_Recommend_Distinct_Raw --distinct list of values but some can repeat
  , avg(CASE WHEN (trunc(DATE_TRUNC('month', m.document_date)))::date BETWEEN d.startAvgDate AND d.endAvgDate THEN m.likely_to_recommend::numeric(5,2) ELSE Null END) as rnpsAccountTrendValue_Avg_Raw
  , avg(CASE WHEN (trunc(DATE_TRUNC('month', m.document_date)))::date >= d.endAvgDate THEN m.likely_to_recommend::numeric(5,2) ELSE Null END) as rnpsAccountAvg_PriorMonth_raw
  FROM gss_ds.fct_all_bu_rltr_and_osat_sales m
  INNER JOIN sfdc.client_control__c c on c.oracle_customer__c = m.customer_number and c.isdeleted = 'false'
  INNER JOIN reporting_stg.client_insights_attributes ci on ci.accountid = c.account__c and ci.account_business_unit like '%mas%', dates d
  WHERE DATE_TRUNC('month', m.document_date) >= d.startAvgDate
  AND m.likely_to_recommend is not null
  --and c.account__c in ('0010b00001t0rsxAAA') --('0010b00001rcFJIAA2','0010b00001rbAk9AAE','0010b00001t0rsxAAA','') --for analysis only
  GROUP BY 1, 2
), rnps_results as
(  --select count(*) --for analysis only
  SELECT m.account__c, m.account_business_unit  --, m.customer_number --for analysis only
  , likely_To_Recommend_Distinct_Raw, rnpsAccountTrendValue_Avg_Raw, rnpsAccountAvg_PriorMonth_raw, '' as blank
  , CASE WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 0.0 AND 6.9 THEN -100
         WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 7.0 AND 8.9 THEN 0
         WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 9.0 AND 10.0 THEN 100
    END rnpsAccountTrendValue_Avg
  , CASE WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 0.0 AND 6.9 THEN -100
         WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 7.0 AND 8.9 THEN 0
         WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 9.0 AND 10.0 THEN 100
    END rnpsAccountAvg
  , CASE WHEN rnpsAccountTrendValue_Avg < rnpsAccountAvg THEN 'Increasing'
         WHEN rnpsAccountTrendValue_Avg > rnpsAccountAvg THEN 'Decreasing'
         WHEN rnpsAccountTrendValue_Avg = rnpsAccountAvg THEN 'No change'
    END rnpsAccountTrentIndicator
  FROM client_nps_summary m
) --select * from rnps_results limit 10000
 --select count(*) from rnps_results --69794
-- FROM reporting_stg.client_insights_attributes c
-- INNER JOIN rnps_results r ON r.account__c = c.accountid
UPDATE reporting_stg.client_insights_attributes
SET account_average_rnps_current_month = r.rnpsAccountAvg
, account_average_rnps_trend_24_months = r.rnpsAccountTrendValue_Avg
, account_description_rnps_trend_indicator = r.rnpsAccountTrentIndicator
FROM reporting_stg.client_insights_attributes c
INNER JOIN rnps_results r ON r.account__c = c.accountid;


--All Non MAS BUs (joins on c.nan_id__c = m.client_iid) 
--count 42,614 on 2023/06/07 
WITH dates as
(
  SELECT trunc(dateadd(month, -24, date_trunc('MONTH',getdate()))) startAvgDate
  , trunc(dateadd(month, -1, max(date_trunc('MONTH',document_date))::date)) as endAvgDate
  , trunc(max(date_trunc('MONTH',document_date))::date) as latestDataDate
  FROM gss_ds.fct_all_bu_rltr_and_osat_sales
), client_nps_summary as 
(
  SELECT c.account__c, ci.account_business_unit  --, m.customer_number --for analysis only
  , '[' || LISTAGG(DISTINCT m.likely_to_recommend, ',') WITHIN GROUP (ORDER BY m.document_date) || ']' as likely_To_Recommend_Distinct_Raw --distinct list of values but some can repeat
  , avg(CASE WHEN (trunc(DATE_TRUNC('month', m.document_date)))::date BETWEEN d.startAvgDate AND d.endAvgDate THEN m.likely_to_recommend::numeric(5,2) ELSE Null END) as rnpsAccountTrendValue_Avg_Raw
  , avg(CASE WHEN (trunc(DATE_TRUNC('month', m.document_date)))::date >= d.endAvgDate THEN m.likely_to_recommend::numeric(5,2) ELSE Null END) as rnpsAccountAvg_PriorMonth_raw
  FROM gss_ds.fct_all_bu_rltr_and_osat_sales m
  INNER JOIN sfdc.client_control__c c on c.nan_id__c = m.client_iid and c.isdeleted = 'false'
  INNER JOIN reporting_stg.client_insights_attributes ci on ci.accountid = c.account__c and ci.account_business_unit not like '%mas%', dates d
  WHERE DATE_TRUNC('month', m.document_date) >= d.startAvgDate
  AND m.likely_to_recommend is not null
  --and c.account__c in ('0010b00001t0rsxAAA') --('0010b00001rcFJIAA2','0010b00001rbAk9AAE','0010b00001t0rsxAAA','') --for analysis only
  GROUP BY 1, 2
), rnps_results as
(  --select count(*) --for analysis only
  SELECT m.account__c, m.account_business_unit  --, m.customer_number --for analysis only
  , likely_To_Recommend_Distinct_Raw, rnpsAccountTrendValue_Avg_Raw, rnpsAccountAvg_PriorMonth_raw, '' as blank
  , CASE WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 0.0 AND 6.9 THEN -100
         WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 7.0 AND 8.9 THEN 0
         WHEN rnpsAccountTrendValue_Avg_Raw BETWEEN 9.0 AND 10.0 THEN 100
    END rnpsAccountTrendValue_Avg
  , CASE WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 0.0 AND 6.9 THEN -100
         WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 7.0 AND 8.9 THEN 0
         WHEN rnpsAccountAvg_PriorMonth_raw BETWEEN 9.0 AND 10.0 THEN 100
    END rnpsAccountAvg
  , CASE WHEN rnpsAccountTrendValue_Avg < rnpsAccountAvg THEN 'Increasing'
         WHEN rnpsAccountTrendValue_Avg > rnpsAccountAvg THEN 'Decreasing'
         WHEN rnpsAccountTrendValue_Avg = rnpsAccountAvg THEN 'No change'
    END rnpsAccountTrentIndicator
  FROM client_nps_summary m
) -- select * from rnps_results limit 10000
-- select count(*) from rnps_results --69794
-- FROM reporting_stg.client_insights_attributes c
-- INNER JOIN rnps_results r ON r.account__c = c.accountid
UPDATE reporting_stg.client_insights_attributes
SET account_average_rnps_current_month = r.rnpsAccountAvg
, account_average_rnps_trend_24_months = r.rnpsAccountTrendValue_Avg
, account_description_rnps_trend_indicator = r.rnpsAccountTrentIndicator
FROM reporting_stg.client_insights_attributes c
INNER JOIN rnps_results r ON r.account__c = c.accountid;

This SQL script calculates Net Promoter Score (NPS)-related metrics for client accounts and updates a reporting table with the results. Here's a breakdown of what it does:

First Section: MAS Business Units

Preparation (WITH dates):
- It identifies three key dates:
  - startAvgDate: 24 months ago based on the current date (getdate()), truncated to the start of the month.
  - endAvgDate: 1 month before the latest available month in the document_date column.
  - latestDataDate: The most recent month in the document_date column.
Calculating Client NPS Metrics (WITH client_nps_summary):
- For each account within MAS business units:
  - Aggregates likely_to_recommend values to generate a distinct list of them for reference (likely_To_Recommend_Distinct_Raw).
  - Calculates two averages:
    - rnpsAccountTrendValue_Avg_Raw: Average NPS value over the 24-month trend period from startAvgDate to endAvgDate.
    - rnpsAccountAvg_PriorMonth_raw: Average NPS value for the last month (endAvgDate onward).
Post-Processing Results (WITH rnps_results):
- Maps calculated averages to corresponding NPS categories:
  - 0.0 to 6.9 → -100 (Detractors)
  - 7.0 to 8.9 → 0 (Passives)
  - 9.0 to 10.0 → 100 (Promoters)
- Compares NPS trend values against the prior month's average to classify them into:
  - Increasing, Decreasing, or No change trends.
Updating Reporting Table:
- Updates columns in reporting_stg.client_insights_attributes with the computed metrics:
  - account_average_rnps_current_month: Prior month's average NPS value.
  - account_average_rnps_trend_24_months: 24-month trend NPS value.
  - account_description_rnps_trend_indicator: "Increasing", "Decreasing", or "No change."

Second Section: Non-MAS Business Units

The script repeats the same calculation process for Non-MAS business units but with a slight change in the join condition:

Instead of using c.oracle_customer__c, it joins on c.nan_id__c = m.client_iid.

Key Observations:

Purpose: The script is used to calculate and classify NPS performance metrics at the account level over two periods (a 24-month trend and the prior month) for both MAS and Non-MAS business units. It then updates the reporting table with these metrics.
Output: Key insights about how client accounts' NPS scores have changed over time, providing trends and summaries for decision-making.
Data Scope:
- Applies only to accounts with non-null likely_to_recommend values.
- Distinguishes between MAS and Non-MAS accounts based on a condition in the client_insights_attributes table.

Uses:

This type of script is typically used in customer success management, to track customer satisfaction trends, or in business reporting for forecasting and insights on customer loyalty (e.g., NPS).

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr