Help on customer loyalty percentage? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help on customer loyalty percentage?

Author

Topic

jrussell999
Starting Member

14 Posts

Posted - 2009-12-16 : 10:28:04

I need to show a particular metric, and I'm looking for the best way to provide it. I'm wondering if SQL can do this:

I need a "customer loyalty" percentage. That is, for each rep on each team, I want to show how many of the customers who ordered last year ordered again this year. So if Bob Smith of the East team had 100 unique customers last year, and 75 of them ordered again this year, the percentage would be 75%. I want to show the counts and the percentage. (100, 75, 75%)

So the results would hopefully look like this:

Team     Rep             Customers last year    Customers this year    Percentage
East     Bob Smith             100                   75                   75%
         Sue Jones             80                    40                   50%
         Frank Hghllhglgh      150                   100                  66%
West     Charisma Flhgdfjhgfj  200                   180                  90%

etc.

Does anyone know how to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-16 : 10:49:34

Could you post some sample data and the table structure?

Jim

Everyday I learn something that somebody else already knew

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-12-16 : 11:06:35

select a.myname, b.yearstat, c.yearstat, (c.yearstat/b.yearstat) * 100
from
stafftable a
left join results b on a.code = b.fkcode and b.date = thisyear
left join results c on a.code = c.fkcode and c.date = lastyear

should be close

you would need to incorporate a "NULLIF" somewhere to cater for people who sold nothing last year or nothing this year. you may also have to deal with division by 0.

jrussell999
Starting Member

14 Posts

Posted - 2009-12-16 : 11:29:04

The table named "Sales" looks like this:

Account	        Team	Sales Rep	        Year

Company 1	East	Bob Smith	        2008
Company 8	East	Bob Smith	        2009
Company 9	East	Bob Smith	        2009
Company 10	East	Bob Smith	        2009

Company 2	East	Sue Jones	        2008
Company 2	East	Sue Jones	        2009
Company 2	East	Sue Jones	        2009

Company 5	East	Frank Hghllhglgh	2008
Company 6	East	Frank Hghllhglgh	2008
Company 5	East	Frank Hghllhglgh	2009

Company 11	West	Charisma Flhgdfjhgfj	2008
Company 12	West	Charisma Flhgdfjhgfj	2008
Company 13	West	Charisma Flhgdfjhgfj	2008
Company 14	West	Charisma Flhgdfjhgfj	2008
Company 15	West	Charisma Flhgdfjhgfj	2008
Company 15	West	Charisma Flhgdfjhgfj	2008
Company 11	West	Charisma Flhgdfjhgfj	2009
Company 12	West	Charisma Flhgdfjhgfj	2009
Company 13	West	Charisma Flhgdfjhgfj	2009
Company 11	West	Charisma Flhgdfjhgfj	2009
Company 12	West	Charisma Flhgdfjhgfj	2009
Company 13	West	Charisma Flhgdfjhgfj	2009
Company 13	West	Charisma Flhgdfjhgfj	2009

So in this example,
Bob Smith had 1 unique customer in 2008, and that customer did not buy in 2009, so the result is 1,0,0%.
Sue Jones had 1 unique customer in 2008, and that customer did buy again in 2009, so the result is 1,1,100%.
Frank Hghllhglgh had 2 unique customers in 2008, of which 1 did buy in 2009, so the result is 2,1,50%.
Charisma Flhgdfjhgfj had 5 unique customers in 2008, of which 3 did buy in 2009, so the result is 5,3,60%.

jrussell999
Starting Member

14 Posts

Posted - 2009-12-16 : 11:35:38

Andrew, thanks - I'm going to have to study what you posted and see if I understand it. I'll post again when I reach a conclusion on that.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-16 : 12:07:02

DECLARE @Table TABLE (Account varchar(20),Team varchar(5),SalesRep varchar(100),[Year] int)

INSERT INTO @table
SELECT 'Company 1','East','Bob Smith', 2008 UNION ALL
SELECT 'Company 8','East','Bob Smith', 2009 UNION ALL
SELECT 'Company 9','East','Bob Smith', 2009 UNION ALL
SELECT 'Company 10','East','Bob Smith', 2009 UNION ALL

SELECT 'Company 2','East','Sue Jones', 2008 UNION ALL
SELECT 'Company 2','East','Sue Jones', 2009 UNION ALL
SELECT 'Company 2','East','Sue Jones', 2009 UNION ALL

SELECT 'Company 5','East','Frank Hghllhglgh', 2008 UNION ALL
SELECT 'Company 6','East','Frank Hghllhglgh', 2008 UNION ALL
SELECT 'Company 5','East','Frank Hghllhglgh', 2009 UNION ALL

SELECT 'Company 11','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 12','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 13','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 14','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 15','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 15','West','Charisma Flhgdfjhgfj', 2008 UNION ALL
SELECT 'Company 11','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 12','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 13','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 11','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 12','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 13','West','Charisma Flhgdfjhgfj', 2009 UNION ALL
SELECT 'Company 13','West','Charisma Flhgdfjhgfj', 2009

SELECT t1.Team,t1.SalesRep
,[CustomersLastYear] = CASE WHEN t1.[Year] = 2008 THEN t1.Customers END
,[CustomersThisYear] = CASE WHEN t2.[Year] = 2009 THEN t2.Customers END
,[Percentage] = str(t2.Customers*1.0 / t1.Customers *100)+'%'

FROM

(
select Team,SalesRep,[Year] ,[Customers] = count(*)
from @table
group by Team,SalesRep,[Year]
) t1

INNER JOIN
(
select Team,SalesRep,[Year] ,[Customers] = count(*)
from @table
group by Team,SalesRep,[Year]
) t2

ON
t1.Team = t2.team
and t1.SalesRep = t2.SalesRep
and t1.[Year] = t2.[Year] - 1

Jim

Everyday I learn something that somebody else already knew

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-16 : 14:08:58

Hmmm...I seem to have a slightly different interpretation of the requirement...

For the same sample data...

SELECT t1.Team,t1.SalesRep
,[CustomersLastYear] = count(t1.customers)
,[CustomersThisYear] = coalesce(count(t2.customers),0)
,[Percentage] = str(coalesce(count(t2.customers),0)*1.0 /count(t1.customers) *100)+'%'

FROM

(
select Account,Team,SalesRep,[customers] = count(*)
from @table
where [Year] = 2008
group by Account,Team,SalesRep
) t1

LEFT JOIN
(
select Account,Team,SalesRep,[customers] = count(*)
from @table
where [Year] = 2009
group by Account,Team,SalesRep
) t2

ON 
t1.Team = t2.Team
and t1.SalesRep = t2.SalesRep
and t1.Account = t2.Account
group by  t1.Team,t1.SalesRep

jrussell999
Starting Member

14 Posts

Posted - 2009-12-16 : 14:47:18

I'm using Microsoft Query to do this in Excel 2007, querying an Access 2007 db.

I started with what JimF wrote. I already have a table with this data in it (and much more), so I can skip the first part where you build the table with the INSERT INTO statement, right?

I decided to do a test, even though I'm not sure this is all right. So I put in the following query, making the minor change of putting the temporary table names before the perentheses, and also changing the table and field names to the real ones:

SELECT t1.Team,t1.`Sales Rep (invoice)`
,[CustomersLastYear] = CASE WHEN t1.[Year] = 2008 THEN t1.Customers END
,[CustomersThisYear] = CASE WHEN t2.[Year] = 2009 THEN t2.Customers END
,[Percentage] = str(t2.Customers*1.0 / t1.Customers *100)+'%'

FROM

t1 (
select Team,`Sales Rep (invoice)`,[Year] ,[Customers] = count(*)
from `J:\Russell\YTD.accdb`.`YTD (2)` `YTD (2)`
group by Team,`Sales Rep (invoice)`,[Year]
)

INNER JOIN
t2 (
select Team,`Sales Rep (invoice)`,[Year] ,[Customers] = count(*)
from `J:\Russell\YTD.accdb`.`YTD (2)` `YTD (2)`
group by Team,`Sales Rep (invoice)`,[Year]
)

ON
t1.Team = t2.team
and t1.`Sales Rep (invoice)` = t2.`Sales Rep (invoice)`
and t1.[Year] = t2.[Year] - 1

I got the error: "Could not add the table 't1'."
I made the change of moving the temporary table names because I originally got the error: "Could not add the table '('."

It seems like the first step is adding those tables. What am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-16 : 14:50:53

You're getting the current error because you moved the alias names. It sounds like derived tables aren't allowed in Microsoft query due to the original error. Could you instead use a view for those?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jrussell999
Starting Member

14 Posts

Posted - 2009-12-16 : 15:35:07

I'm still working on this and I will be back tomorrow to figure this out - Just wanted to thank everyone for their help!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-16 : 15:49:15

The @Table is a table variable in sql server. It goes away once you're done with it. I used it just to have some data to work with, so it isn't needed in your query. I'm not sure what the syntax considerations are for using Microsoft Query and Access, but it looks like you're on the right track. Just put the table aliases back like Tara said.

Jim

Everyday I learn something that somebody else already knew

jrussell999
Starting Member

14 Posts

Posted - 2009-12-17 : 15:42:33

I'm making progress on this. Using what you all wrote and after finding the Microsoft Access SQL Reference here: [url]http://msdn.microsoft.com/en-us/library/bb259125.aspx[/url], I was able to modify what Vijay wrote and get results by doing it in three separate actions:

CREATE VIEW t1
AS
select Team,SalesRepInvoice,AccountFixed,Rolling12
from `J:\Russell\YTD.accdb`.`YTD (2)` `YTD (2)`
where Rolling12 = 'Nov07toOct08'
group by Team,SalesRepInvoice,AccountFixed,Rolling12


CREATE VIEW t2
AS
select Team,SalesRepInvoice,AccountFixed,Rolling12
from `J:\Russell\YTD.accdb`.`YTD (2)` `YTD (2)`
where Rolling12 = 'Nov08toOct09'
group by Team,SalesRepInvoice,AccountFixed,Rolling12


SELECT t1.Team,t1.SalesRepInvoice,count(t1.AccountFixed) AS CustomersLastYear,count(t2.AccountFixed) AS CustomersThisYear
,(count(t2.AccountFixed)*1 /count(t1.AccountFixed)*100) AS Percentage
FROM t1 LEFT JOIN t2 ON 
t1.Team = t2.Team
and t1.SalesRepInvoice = t2.SalesRepInvoice
and t1.AccountFixed = t2.AccountFixed
group by  t1.Team,t1.SalesRepInvoice

Now there are just two sticking points left. First, how do I do it in one SQL statement that I can use as the data source of a pivot table?

And second, I'm having some kind of issue with decimal precision in the Percentage field. I'd like to have that field as a simple decimal (so 50% is .5). Originally it was:

(count(t2.AccountFixed)*1.0 /count(t1.AccountFixed)*100) AS Percentage

which resulted in (a few example rows):

East Bob 80 36 45
East Joe 1 1 100
East Sue 1 0 0
East Jose 14 11 78.57142857
West Charisma 55 1 1.818181818
West Alexi 122 80 65.57377049
West Mikhail 27 0 0
West Arune 1 0 0
West Lena 90 66 73.33333333

I changed it to:

(count(t2.AccountFixed)/count(t1.AccountFixed)) AS Percentage

which resulted in (same rows):

East Bob 80 36 0.45
East Joe 1 1 1
East Sue 1 0 0
East Jose 14 11 0.785714286
West Charisma 55 1 E-2
West Alexi 122 80 0.655737705
West Mikhail 27 0 0
West Arune 1 0 0
West Lena 90 66 0.733333333

Why is the result E-2 when it should be .018181818 ? There was another result of .037 something, so I am confused here.

jrussell999
Starting Member

14 Posts

Posted - 2009-12-17 : 16:51:14

I figured out how to do it one step:

SELECT t1.Team,t1.SalesRepInvoice,count(t1.AccountFixed) AS CustomersLastYear,count(t2.AccountFixed) AS CustomersThisYear
,(count(t2.AccountFixed)*1.0 /count(t1.AccountFixed)*100) AS Percentage
FROM (select Team,SalesRepInvoice,AccountFixed,Rolling12
from `J:\Josh Russell\YTD Margins\Master files\YTD Margins 4.accdb`.`YTD (2)` `YTD (2)`
where Rolling12 = 'Nov07toOct08'
group by Team,SalesRepInvoice,AccountFixed,Rolling12) AS t1
LEFT JOIN (select Team,SalesRepInvoice,AccountFixed,Rolling12
from `J:\Josh Russell\YTD Margins\Master files\YTD Margins 4.accdb`.`YTD (2)` `YTD (2)`
where Rolling12 = 'Nov08toOct09'
group by Team,SalesRepInvoice,AccountFixed,Rolling12) AS t2 ON
t1.Team = t2.Team
and t1.SalesRepInvoice = t2.SalesRepInvoice
and t1.AccountFixed = t2.AccountFixed
group by t1.Team,t1.SalesRepInvoice

Now I have a pivot table in Excel based on that query. I am also using a calculated field in the pivot table, which sidesteps the other problem I was having. I would still like to know the answer to it though. Now I just have to find out if I need to show exactly which Accounts did/did not reorder, but if not, then I think I am done with this. Again, thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources