Help with SQL code to Identify Lapsed Customers

Hello Experts,

I have a large table with customer id, product id, product ordered, product ordered date.  I want to identify customers who have not ordered any product in the last 30 days.  The data is in a oracle database.

Please help with a SQL like code.

Thanks,
LVL 1
fb1990Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT DISTINCT [customer id]
From (Select [customer id], [product ordered] From Tabel1
WHERE [product ordered date]> sysdate - 30) As d
WHERE [product ordered] IS NULL
-- or depending on the column [product ordered] data type
--WHERE [product ordered] = false

Open in new window

0
gplanaCommented:
I think eghtebas solution is not right as it will show customers that have ordered on last 30 days. I think you should do something like this:

SELECT *
FROM Customers
WHERE customer_id NOT IN (
   SELECT DISTINCT customer_id
   FROM your_large_table_or_orders_here
   WHERE ordered_date >= trunc(sysdate - 30)
);

Open in new window


Just put the proper table and column names and it should work.

Hope it helps. Regards!
0
fb1990Author Commented:
Hello Experts,

eghtebas suggested solution gave customers who have recently made purchases as already mentioned by gplana

gplana: Ran, but did not yield any data

Please help look at the request and the code further
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
re: gave customers who have recently made purchases as already mentioned by gplana

WHERE [product ordered] IS NULL  is the key to switch customer types. I do not know what kind of data you have in the [product ordered] column?

What is the purpose of this column?

All you have to do is play with this column. If you let me what field determines which customer had orders and which didn't and how (data sample) this problem could be solved easily.

Please read lines 4, 5, and 6 again and fix the as predicate required. Or tell me what data you have to indicate a customer has or hasn't orders.

Mike
0
fb1990Author Commented:
Thanks Mike.  The [product ordered] is the name of the merchandise ordered by the customers,  The field will always have a value in it.  The table contains all customers that ordered in the past.  I need to to pull a list of the customers who have not ordered in 30 or more days.
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> The field will always have a value in it

If we have only one table and [product ordered]  has always value in it, then how should we know a customer has nor orders.

It will be very helpful to provide tables involved with there fields (those important in this task). I know it should be more than one table.  But what are their names and column names.

Mike
0
fb1990Author Commented:
Sorry i was not clear.  All the customers have placed an order at some point or the other.  I need to find customers who have not ordered in the last 30 days. The product ordered should show the item they ordered in the past

Thanks,
0
Mike EghtebasDatabase and Application DeveloperCommented:
So, if [product ordered] is blank that means the customer has no orders? If so, then try:

WHERE [product ordered] = ''
0
Mark GeerlingsDatabase AdministratorCommented:
This kind of business request, basically: "Show me something that is *NOT* in the database", is always more-challenging to write in SQL, than a request like: "Show me all customers who ordered [whatever] in the last 30 days".

To find "lapsed customers" you have to start by finding all customers who ordered something at least 30 days ago, then subtract from that, those who have also ordered something in the past 30 days.

This should work for you:

select distinct (customer id)
from [your_table] t1
where ordered_date < sysdate -30
minus
select distinct (customer id)
from [your_table] t2
where ordered_date > sysdate -30;


If you only want to consider customers who actually ordered something within the past quarter, you could use this:
select distinct (customer id)
from [your_table] t1
where ordered_date between sysdate -90 and sysdate -30
minus
select distinct (customer id)
from [your_table] t2
where ordered_date > sysdate -30;

If you want to consider customers who ordered something in the past year, change "sysdate -90" to "sysdate -365".
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fb1990Author Commented:
Thanks to everyone who contributed a solution.

Mark's solution gave me exactly what I wanted. I am still trying to understand what the bonus code is doing.   Thank you Sir!
0
Mark GeerlingsDatabase AdministratorCommented:
Be aware that not only is this kind of SQL challenging to write, it is also usually *MUCH* more time-consuming for Oracle to execute than simple SQL queries like:

select customer_id, product_id, product_ordered, ordered_date
from [your_table]
where [some condition  is true];

I'm not sure what you mean by "bonus code".  Did you mean the second query I gave you after this comment:
"If you only want to consider customers who actually ordered something within the past quarter..."?

The first query I gave you simply considers all customers who have ever ordered something (no matter how long ago).  In case your system includes records for multiple years, I was thinking that someone who ordered something five years ago, but not in the recent past is already inactive or lapsed.  So, my second query is a way to look just at customers who have ordered something fairly recently, but not in the last 30 days.
0
fb1990Author Commented:
I got it thanks....
0
fb1990Author Commented:
Hello Mark,

I know this question is closed. I am wondering if you can help modify this code to customer who actually ordered something in the last 60 days?

I can submit another request if you like...

Thanks,
0
gplanaCommented:
Try this:

select distinct (customer id)
from [your_table] t2
where ordered_date > sysdate -60;
0
Mark GeerlingsDatabase AdministratorCommented:
I had given you this:

If you only want to consider customers who actually ordered something within the past quarter, you could use this:

 select distinct (customer id)
 from [your_table] t1
 where ordered_date between sysdate -90 and sysdate -30
 minus
 select distinct (customer id)
 from [your_table] t2
 where ordered_date > sysdate -30;


You only need to make a very small change to that to identify lapsed customers who did actually order something  in the last 60 days,  Just change the "90" to "60".  Or, if you want to find all customers who ordered anything in the past 60 days, (whether they are lapsed, or not) that is simpler, just use the simple query that gplana suggested.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.