Friday, January 22, 2016

Calculating Customer's Latency

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.


To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:

--Create temp table
CREATE TABLE #Transact
(
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE
)

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:

--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
INTO #1
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
FROM ( SELECT a.MemberID,
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
  INNER JOIN #1 b
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!

Share:

2 comments:

  1. What you're saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I'm sure you'll reach so many people with what you've got to say.

    ReplyDelete
  2. Very great post. I simply stumbled upon your blog and wanted to say that I have really enjoyed browsing your weblog posts. After all I’ll be subscribing on your feed and I am hoping you write again very soon!

    ReplyDelete

About

Related Posts

Cisco VPN not Working on Windows 10

Error: Cisco VPN is not working (cannot login and connect to VPN connection). Solution: 1. Right-click on the VPN client, and Run as Ad...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Follow by Email