Summarizing NPS Data by Customer & Group

Transact-SQL
1

I have some raw customer survey NPS data coming in as per the table sample below. This has rows for each individual survey response, and whether that response in classified as a 'Promoter','Passive' or 'Detractor'.

+------------+--------+----------------------+--------------+------------+
|    Date    |  Acct  |       Customer       |    Group     | RatingType |
+------------+--------+----------------------+--------------+------------+
| 15/12/2024 | 116017 | Orange Homes (North) | Orange Homes | Promoter   |
| 20/12/2024 | 116017 | Orange Homes (North) | Orange Homes | Promoter   |
| 05/12/2024 | 116017 | Orange Homes (North) | Orange Homes | Promoter   |
| 12/12/2024 | 116017 | Orange Homes (North) | Orange Homes | Passive    |
| 14/12/2024 | 116017 | Orange Homes (North) | Orange Homes | Detractor  |
| 09/12/2024 | 116016 | Orange Homes (West)  | Orange Homes | Promoter   |
| 06/12/2024 | 116016 | Orange Homes (West)  | Orange Homes | Detractor  |
| 15/12/2024 | 116016 | Orange Homes (West)  | Orange Homes | Promoter   |
| 17/12/2024 | 115052 | Blue Homes           |              | Promoter   |
| 05/12/2024 | 115052 | Blue Homes           |              | Promoter   |
| 09/12/2024 | 115052 | Blue Homes           |              | Promoter   |
| 08/12/2024 | 115052 | Blue Homes           |              | Detractor  |
| 02/12/2024 | 115052 | Blue Homes           |              | Promoter   |
+------------+--------+----------------------+--------------+------------+

I need to summarize this both by Customer and Customer Group (if there is one), and provide the NPS score for the last complete month and the NPS score for the YTD. The NPS score should be calculated as % Promoters - % Detractors for the period & YTD, from the total number of responses (Promoters, Detractors & Passives). The result should look like the below, any NULL or blank NPS scores for either the customer or group like should be set to 0 in that instance.

+------------+-------+--------------+-----------+------+---------+
|    Date    | Level |   Customer   | Responses | NPS  | NPS_YTD |
+------------+-------+--------------+-----------+------+---------+
| 31/12/2024 | Acct  | 116016       |         3 | 33.3 | 33.3    |
| 31/12/2024 | Acct  | 116017       |         5 | 40   | 40      |
| 31/12/2024 | Group | Orange Homes |         8 | 37.5 | 37.5    |
| 31/12/2024 | Acct  | Blue Homes   |         5 | 60   | 60      |
+------------+-------+--------------+-----------+------+---------+

(the NPS_YTD figures here are the same as the current month as no previous data in the sample). The responses column should show the number of responses for the current month, but the NPS_YTD responses figures will obviously be different.

How can I achieve this from the NPS Data?

Here is the code for the table and sample data:

CREATE TABLE NPS_Data (
	[Date] [date] NULL,
	[Acct] [varchar](12) NULL,
	[Customer] [varchar](60) NULL,
	[Group] [varchar](40) NULL,
	[RatingType] [varchar](15) NULL
)

GO
;

INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-15' AS Date), N'116017', N'Orange Homes (North)', N'Orange Homes', N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-20' AS Date), N'116017', N'Orange Homes (North)', N'Orange Homes', N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-05' AS Date), N'116017', N'Orange Homes (North)', N'Orange Homes', N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-12' AS Date), N'116017', N'Orange Homes (North)', N'Orange Homes', N'Passive')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-14' AS Date), N'116017', N'Orange Homes (North)', N'Orange Homes', N'Detractor')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-09' AS Date), N'116016', N'Orange Homes (West)', N'Orange Homes', N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-06' AS Date), N'116016', N'Orange Homes (West)', N'Orange Homes', N'Detractor')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-15' AS Date), N'116016', N'Orange Homes (West)', N'Orange Homes', N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-17' AS Date), N'115052', N'Blue Homes', NULL, N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-05' AS Date), N'115052', N'Blue Homes', NULL, N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-09' AS Date), N'115052', N'Blue Homes', NULL, N'Promoter')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-08' AS Date), N'115052', N'Blue Homes', NULL, N'Detractor')
GO
INSERT INTO [NPS_Data] ([Date], [Acct], [Customer], [Group], [RatingType]) VALUES (CAST(N'2024-12-02' AS Date), N'115052', N'Blue Homes', NULL, N'Promoter')
GO

Many thanks for your help

Martyn

2

this might get you close. Why doesn't acct 115052 appear in your results?

select case when [level] is not null then 'Group' else 'Acct' end as [level]
	   ,coalesce([level], acct) as Customer
	   ,Responses
	   ,Promoter-Detractor as NPS
from (   
		select count(1) as Responses
			   ,Sum(case when RatingType = 'Promoter' then 1 else 0 end) as Promoter
			   ,Sum(case when RatingType = 'Passive' then 1 else 0 end) as Passive
			   ,Sum(case when RatingType = 'Detractor' then 1 else 0 end) as Detractor
			   ,acct
			   ,Coalesce([Group], Customer) as [level]
		from #NPS_Data 
		GROUP BY
			GROUPING SETS (
				(acct),
				(Coalesce([Group], Customer)),
				()
		)
	) v
where acct is not null or [level] is not null
1 Like
3

My bad in missing account.

But thank you as this has gotten me close enough to stage it and do the rest and YTD too.

Many thanks
Martyn

1 Like