Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
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.
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,