Re-Activated / Re-Engaged Customers

[MTS95]

Hi there,

I was wondering if someone might be able to help me with a problem my coworkers and I are having. We are trying to find out our re-activation (or re-engagement) rate of license-purchasing customers, which is defined as:

An individual who purchased a license in the previous five fiscal years, and purchased in the current fiscal year after having not purchased a license the previous fiscal year.

So if a customer purchased a license sometime between 2017 and 2020, did not purchase a license in 2021, and then purchased a license in 2022, they would be considered reactivated.

I attempted to find this by creating the field reactive, like so:

  if(
        PurchaseYear = (Year(Today())) - 5
        or PurchaseYear = (Year(Today())) - 4
        or PurchaseYear = (Year(Today())) - 3
        or PurchaseYear = (Year(Today())) - 2
        and PurchaseYear <> (Year(Today())) - 1
        and PurchaseYear = (Year(Today()))
        ,count(CustomerId),0) as reactive,

but the output I get back is that the expression is invalid. What am I doing wrong?

For context, below is the script my coworkers and I have created. My part of the script is denoted by the lines with gray slashes ( ////////////////////////////// ), and the script runs without any issues if I remove my part:

[salesdbo]:
LOAD
    CustomerId,
    PurchaseYear;
SQL SELECT
    CustomerId,
    PurchaseYear
FROM CompassLTD.dbo."vw_SalesNoPII";

[sales a]:
NoConcatenate LOAD distinct
    CustomerId,
    PurchaseYear
resident [salesdbo]
Order by CustomerId desc, PurchaseYear desc;

DROP Table [salesdbo];

[sales b]:
Load
    CustomerId,
    PurchaseYear,
    Previous(PurchaseYear) as peekyear,
    Previous(CustomerId) as peekCID,
    if(                                                                                                              //////////////////////////////
        PurchaseYear = (Year(Today())) - 5                                         //////////////////////////////
        or PurchaseYear = (Year(Today())) - 4                                    //////////////////////////////
        or PurchaseYear = (Year(Today())) - 3                                    //////////////////////////////
        or PurchaseYear = (Year(Today())) - 2                                    //////////////////////////////
        and PurchaseYear <> (Year(Today())) - 1                              //////////////////////////////
        and PurchaseYear = (Year(Today()))                                       //////////////////////////////
        ,count(CustomerId),0) as reactive,                                         //////////////////////////////
    if((Previous(PurchaseYear)<>PurchaseYear+1 AND(Previous(CustomerId)=CustomerId))
        OR Previous(CustomerId)<>CustomerId,
        1,0) as nonrecurFlag
resident [sales a]
Order by CustomerId desc, PurchaseYear desc;

DROP Table [sales a];

[sales c]:
Load
    PurchaseYear,
    SUM(nonrecurFlag) as nonrecurCOUNT1X
resident [sales b]
Group by PurchaseYear;

[sales d]:
Load
    PurchaseYear,
    nonrecurCOUNT1X,
    Previous(nonrecurCOUNT1X) as nonrecurCOUNT
    resident [sales c]
Order by PurchaseYear asc;

DROP Table [sales c];

Any insight you may have is greatly appreciated. Also, if someone could explain why the script creates tables to then drop them a few lines later, that would be helpful as well. Please let me know if you need me to clarify anything.

[Vegar]

You are trying to aggregate the data using count(CustomerId). If you use Count() in a load then you need to define which dimensions you want to Group the aggregation by, usin GROUP BY ... in the end of your load.

 Consider  moving your section down to the C section of your script where you already are performing an group by.     

[MTS95]

Thank you for pointing that out. I actually had moved this script over from the Qlik Sheet page where I had been trying to create this formula as an expression in a table, and I had accidentally left in the Count() function.

I just removed the Count() function from my script, and the expression is no longer invalid (thank you Vegar!!). However, now when I run the script, cells in the reactive field display the CustomerId if the purchase year is 2017, 2018, or 2019. For example, customer # 257 (pictured below) is not a re-activated customer, yet the reactive field is filled in like so:

I'm hoping to create a formula that flags a customer if they purchased a license between 2017 and 2020, did not repurchase in 2021, and then purchased again in 2022. Here is my script as it stands right now:

  if(
        PurchaseYear = (Year(Today())) - 5
        or PurchaseYear = (Year(Today())) - 4
        or PurchaseYear = (Year(Today())) - 3
        or PurchaseYear = (Year(Today())) - 2
        and PurchaseYear <> (Year(Today())) - 1
        and PurchaseYear = (Year(Today()))
        ,CustomerId,0) as reactive,

Thank you again for your initial response. Please let me know if there is anything else I can do. In the meantime, I'll keep fiddling with the script in my data load editor, and I'll update this post with the answer if I make a breakthrough.

[MTS95 · Accepted solution]

I was able to make a field that is pretty close to what I'm looking for--a flag given to customers who did not consistently purchase a license each year, but show up again in some later year. The script for it is very similar to the nonrecurflag field in the section [sales b]. Here's how I generated the new field, called theyllbeback and placed in that same section [sales b]:

    if((Previous(PurchaseYear)<>PurchaseYear+1 AND(Previous(CustomerId)=CustomerId))
        OR Previous(CustomerId)<>CustomerId, AND(Previous(PurchaseYear)=PurchaseYear-2),
        1,0) as theyllbeback,

It shows up in a table in the Sheets area like this:

CustomerId	PurchaseYear	theyllbeback
000001	2012	0
000001	2013	0
000001	2014	0
000001	2015	0
000001	2016	0
000001	2017	0
000002	2012	1
000002	2014	0
000002	2015	0
000002	2016	0
000002	2017	1
000002	2020	0
000002	2021	0
000002	2022	0

Since Customer 000001 is consistently purchasing a license each year before they stop in 2017 and do not return, each of their purchases is given a value of 0 for the theyllbeback field. Customer 000002 has been sporadically buying licenses, and each year before their gaps in purchases is flagged with a 1 in the theyllbeback field.

Finally, I wanted to include a link to this other thread I found that covers a similar topic to this one (which I couldn't get to work in my data load for some reason, but only because I'm sure I was doing something wrong rather than anything wrong with their script), since consolidating  information and making it easy to find is important for posterity.

[MTS95]

Oops, I was slightly off---right at the end there, that 2 should be a 1. The theyllbeback field should look like this:

    if((Previous(PurchaseYear)<>PurchaseYear+1 AND(Previous(CustomerId)=CustomerId))
        OR Previous(CustomerId)<>CustomerId, AND(Previous(PurchaseYear)=PurchaseYear-1),
        1,0) as theyllbeback,