Script to define customer as new or old

[pauledrich]

I have a problem I'm struggling to overcome, I have created a dashboard with around 10 tables all joined with no problem, the tables have customer information, transaction details, product descriptions and so on.

I want to give each customer a flag of "new"  when they buy the first one of a range of products and use that date as the 1st first purchase and potentially a Flag of "Old" for future purchases.

The customer table has a created date, UniqueID.

The Transaction table is for a specific range of products which a customer may or may not have had.

I would like to have this calculated in the load script and have in the past been successful with a simple if - and Im quite green with scripting so any help or a steer on where to try and find out the answer would be of great help.

P

[MK_QSL]

Could you please load your script to make it easy for everyone to understand?

[Anonymous]

Paul,

I'm assuming that the same customer is both "new" and "old", depending on time of purchase.  So, it makes sense to flag the purchase itself as "new" or "old" per customer per product.  The "Yes" value is assigned when a customer buys a product for the first time.  Example:
"Data" table includes CustomerId, ProductId, PurchaseId, and Date.
The script to could be:

tmp:
LOAD DISTINCT
CustomerId,
ProductId,
min(Date) as FirstPurchaseDate // this is the date of the 1st purchase of Product by Customer
RESIDENT Data
GROUP BY CustomerId, ProductId;

LEFT JOIN (Data) LOAD DISTINCT
CustomerId,
ProductId,
FirstPurchaseDate
RESIDENT tmp;
DROP TABLE tmp;

LEFT JOIN (Data) LOAD DISTINCT
PurchaseId,
CustomerId,
ProductId,
Date
if(FirstPurchaseDate=Date, 'Yes','No') as NewCustomer          // this is your flag
RESIDENT tmp;
DROP FIELD FirstPurchaseDate;

Regards,

Michael

[pauledrich]

Hi Michael

Thank you for the reply & sorry for the late reply.

Looks good and thanks for the help - I will be getting back to this piece of work later this week and will keep you updated.

Thanks again.

P

[pauledrich · Accepted solution]

Hi Michael

I have added the solution to the load script with amendments to correct field names and receive error messages in order :-

1. Table not found

tmp:

LOAD DISTINCT

CustomerID,

ProductID,

min(AgreementDate) as FirstPurchaseDate

RESIDENT Data

GROUP BY CustomerID, ProductID

2.

Table not found

LEFT JOIN (Data) LOAD DISTINCT

CustomerID,

ProductID,

FirstPurchaseDate

RESIDENT tmp

3.

Table not found

DROP TABLES statement

4.

Table not found
LEFT JOIN (Data) LOAD DISTINCT
AgreementID,
CustomerID,
ProductID,
AgreementDate,
if(FirstPurchaseDate=AgreementDate, 'Yes','No') as NewCustomer         

RESIDENT tmp

5.

Field not found

Did not find the field "FirstPurchaseDate" from the DROP FIELD statement

[Anonymous]

Hi Paul,

The first error message means that there is no table "Data".  You need to use the real name of the table.  Notice that in my example it is not only in LOAD RESIDENT, but also in JOIN (Data) LOAD.

All other errors are the results of the first one.

Regards,

Michael

[pauledrich]

Hi Michael

I have made the change and included the table name where there is "Data" and still receive the same error messages?

Any ideas?

P

[Anonymous]

Can you send the log file or the qvw itself?  Or the script as text?