Identifying new customers

[Guest]

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not purchase an
in the previous 12 months, thus the customer number does not appear as a
person who bought previousely. I am unsure how to set that up. I would like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it. Trying
to understand my business better.

Thanks,
AL

 

[Arvin Meyer [MVP]]

It looks as though you have 3 different fields doing the work of 1 (Year,
Month, Quarter). A single date field will allow you to calculate the others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));

 

[SusanV]

What if they bought something 13 months ago? Would you still want them
listed as new? Perhaps a better approach would be to add a field for the
date they were added to the database - you can set your older customers
manually or via an update query - then use the aging of that date to
determine new...

 

[Guest]

As Arvin said a single SaleDate column of date/time data type would make life
easier (but not quite so easy as he stated I'm afraid). You can easily
calculate yearly, monthly or quarterly sales per customer from a single date.
With your current design, however, you can do it using the DateSerial
function to return a date/time value for the first of each (assuming the
Month column contains numbers in the range 1 – 12). Simply looking for
customers to whom a sale has been made more than one year ago isn't enough,
however, as they might also have been sold something within the last year, so
you have to look for the *latest* sale date per customer, which can be done
with the Max function in a subquery. The following would return all
customers who have purchased nothing in the last year (I've assumed the table
is called Sales):

SELECT DISTINCT [Customer Number], "New Customer" AS CustomerStatus
FROM Sales AS S1
WHERE
(SELECT MAX(DATESERIAL([Year], [Month], 1))
FROM Sales AS S2
WHERE S2.[Customer Number] = S1.[Customer Number])
< DATESERIAL(YEAR(DATE())-1,MONTH(DATE()),1);

One thing you'll notice from this is that it includes columns named Year and
Month and built in functions with the same names. This illustrates why such
'keywords' should not be used as object names. Putting the object names in
square brackets should avoid any confusion, but its far better to use
something like SaleYear, SaleMonth instead.

To show these new customers by means of a column in a list of all customers
you can join the above query to the sales table, using a left outer join so
it returns all rows from Sales regardless of a matching row in the above
query (which I'll call qryNewCustomers):

SELECT DISTINCT Sales.[Customer Number], qryNewCustomers.CustomerStatus
FROM Sales LEFT JOIN qryNewCustomers
ON Sales.[Customer Number] = qryNewCustomers.[Customer Number];

Incidentally, if you did have a SaleDate column of date/time data type the
equivalent of the qryNewCustomers query above would be as follows, using the
DateAdd function to return the date exactly one year ago:

SELECT DISTINCT [Customer Number], "New Customer" AS CustomerStatus
FROM Sales AS S1
WHERE
(SELECT MAX(SaleDate)
FROM Sales AS S2
WHERE S2.[Customer Number] = S1.[Customer Number])
< DATEADD("yyyy",-1,DATE());

Ken Sheridan
Stafford, England

 

[Guest]

Arvin:

Its not quite that simple. The fact that a customer has bought an item over
one year ago doesn't preclude their having bought one or more in the last
year. The query would need to be restricted on the MAX(DateField) value per
customer by means of a subquery.

Ken Sheridan
Stafford, England

It looks as though you have 3 different fields doing the work of 1 (Year,
Month, Quarter). A single date field will allow you to calculate the others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not purchase an
in the previous 12 months, thus the customer number does not appear as a
person who bought previousely. I am unsure how to set that up. I would
like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it. Trying
to understand my business better.

Thanks,
AL

 

[Arvin Meyer [MVP]]

So, you'll need to get a bit fancier. Run the same query Where [DateField] >
(Date()-365)); and you'll get all the customers who've bought in the last
year. Now run a third query joining the first 2 of them on [Customer Number]
where [Customer Number] is the second query is null. Now you have only the
customers who have purchased more than 1 year ago.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

Its not quite that simple. The fact that a customer has bought an item
over
one year ago doesn't preclude their having bought one or more in the last
year. The query would need to be restricted on the MAX(DateField) value
per
customer by means of a subquery.

Ken Sheridan
Stafford, England

It looks as though you have 3 different fields doing the work of 1 (Year,
Month, Quarter). A single date field will allow you to calculate the
others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not purchase
an
in the previous 12 months, thus the customer number does not appear as
a
person who bought previousely. I am unsure how to set that up. I
would
like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it.
Trying
to understand my business better.

Thanks,
AL

 

[SusanV]

However, if a customer bought something 13 or 14 months ago, but not since,
does that make him/her a NEW customer? Not at all sure I'd be going this way
at all...
--
SusanV

So, you'll need to get a bit fancier. Run the same query Where [DateField]

(Date()-365)); and you'll get all the customers who've bought in the

last year. Now run a third query joining the first 2 of them on [Customer
Number] where [Customer Number] is the second query is null. Now you have
only the customers who have purchased more than 1 year ago.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

Its not quite that simple. The fact that a customer has bought an item
over
one year ago doesn't preclude their having bought one or more in the last
year. The query would need to be restricted on the MAX(DateField) value
per
customer by means of a subquery.

Ken Sheridan
Stafford, England

It looks as though you have 3 different fields doing the work of 1
(Year,
Month, Quarter). A single date field will allow you to calculate the
others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not
purchase an
in the previous 12 months, thus the customer number does not appear as
a
person who bought previousely. I am unsure how to set that up. I
would
like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it.
Trying
to understand my business better.

Thanks,
AL

 

[Guest]

To all, thanks for your help. You help is greatly appreciated. I am a
little stumped in what you are all saying. Here is my issue.

I told you I have the following columns:
Customer Number
Year
Month
Sales
Quarter
Date

I would like to count a customer as a new user if they did not purchase an
in the previous 12 months, thus the customer number does not appear as
a person who bought previously. I also want to track New Uers added per
month. Defininition of a New User is a customer who has not purchased in the
previous Month/s if it is a new product or 12 months if it is an existing
product. I am unsure how to set that up. I would like the New coulumn to
read New User. The other issue would be if the person does not purchase in a
year timeframe or with in 60 months, I would like to code them as a potential
Lost User. I know this is very complex. I forgot to mention that I also had
a date column. It is written in this format (6/22/2005 0:00)


Thanks again for your help.
ABJ

However, if a customer bought something 13 or 14 months ago, but not since,
does that make him/her a NEW customer? Not at all sure I'd be going this way
at all...
--
SusanV

So, you'll need to get a bit fancier. Run the same query Where [DateField]

(Date()-365)); and you'll get all the customers who've bought in the

last year. Now run a third query joining the first 2 of them on [Customer
Number] where [Customer Number] is the second query is null. Now you have
only the customers who have purchased more than 1 year ago.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

Its not quite that simple. The fact that a customer has bought an item
over
one year ago doesn't preclude their having bought one or more in the last
year. The query would need to be restricted on the MAX(DateField) value
per
customer by means of a subquery.

Ken Sheridan
Stafford, England

:

It looks as though you have 3 different fields doing the work of 1
(Year,
Month, Quarter). A single date field will allow you to calculate the
others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not
purchase an
in the previous 12 months, thus the customer number does not appear as
a
person who bought previousely. I am unsure how to set that up. I
would
like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it.
Trying
to understand my business better.

Thanks,
AL

 

[Guest]

Firstly lets get the question of the date out of the way. Provided the
column is of date/time data type then the format is irrelevant. date/time
values are actually implemented in Access as a 64 bit floating point number
as an offset from 30 December 1899 00:00:00. You can view them in any format
you wish, but the underlying values remain the same. Having the date of sale
does make things easier however.

The query for which I posted the SQL would give you the customers who have
made a purchase more than one year ago but not since, which is your first
criterion of a 'New user'.

To identify a 'customer who has not purchased in the previous Month/s if it
is a new product or 12 months if it is an existing product' would be done in
a similar way, but requires a definition of 'new product'. Does this mean an
item the customer has not purchased before or an item which has recently
introduced into the product range? The former would be easy to do on the
basis of the sales data alone provided each sale record included a ProductID
or similar, but if the latter there would need to be some means of
identifying a recently introduced product.

As regards the situation where 'the person does not purchase in a year
timeframe or with in 60 months' I may be misunderstanding what's meant here,
but it seems to me that the latter would be included in the former in any
case. Also, how does the former differ from your first criterion, i.e. a
customer who 'did not purchase an in the previous 12 months'?

Whatever the answers to the above points are there is no reason why a query
can't give you what you want, provided that the relevant data on which to
restrict the query is there in the base tables. As well as clarifying the
points I've raised above it would probably help us if you could provide some
dummy sales data for which a customer would fall into your New User and Lost
User categories.

Ken Sheridan
Stafford, England

To all, thanks for your help. You help is greatly appreciated. I am a
little stumped in what you are all saying. Here is my issue.

I told you I have the following columns:
Customer Number
Year
Month
Sales
Quarter
Date

I would like to count a customer as a new user if they did not purchase an
in the previous 12 months, thus the customer number does not appear as
a person who bought previously. I also want to track New Uers added per
month. Defininition of a New User is a customer who has not purchased in the
previous Month/s if it is a new product or 12 months if it is an existing
product. I am unsure how to set that up. I would like the New coulumn to
read New User. The other issue would be if the person does not purchase in a
year timeframe or with in 60 months, I would like to code them as a potential
Lost User. I know this is very complex. I forgot to mention that I also had
a date column. It is written in this format (6/22/2005 0:00)


Thanks again for your help.
ABJ

However, if a customer bought something 13 or 14 months ago, but not since,
does that make him/her a NEW customer? Not at all sure I'd be going this way
at all...
--
SusanV

So, you'll need to get a bit fancier. Run the same query Where [DateField]
(Date()-365)); and you'll get all the customers who've bought in the
last year. Now run a third query joining the first 2 of them on [Customer
Number] where [Customer Number] is the second query is null. Now you have
only the customers who have purchased more than 1 year ago.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin:

Its not quite that simple. The fact that a customer has bought an item
over
one year ago doesn't preclude their having bought one or more in the last
year. The query would need to be restricted on the MAX(DateField) value
per
customer by means of a subquery.

Ken Sheridan
Stafford, England

:

It looks as though you have 3 different fields doing the work of 1
(Year,
Month, Quarter). A single date field will allow you to calculate the
others.
Having said that, a simply query will avoid any customers greater than 1
year old:

Select [Customer Number]
From TableName
Where [DateField] < (Date()-365));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an access database that contains the following fields:
Customer Number
Year
Month
Sales
Quarter


I would like to count a customer as a new user if they did not
purchase an
in the previous 12 months, thus the customer number does not appear as
a
person who bought previousely. I am unsure how to set that up. I
would
like
the New coulumn to read New User.

Can anyone help me to set this up? I would greatly appreciate it.
Trying
to understand my business better.

Thanks,
AL