Create audience where profiles having an active subscription

[BitunSenAEP]

Hello Team,

We are seeking help in creating segments/audiences to identify members who are having active subscription.

We have subscription data stored as event where it has subscription start date (data type - datetime) and subscription end date (data type - datetime). One profile will have only one subscription event.

Definition of active subscription: subscription start date should be before current timestamp and subscription end date is after current timestamp  

Sample Data:

Profile 1 -

• Subscription Start Date: 2024-01-01 00:00:00
• Subscription End Date: 2024-12-31 23:59:59

Profile 2 -

• Subscription Start Date: 2023-01-01 00:00:00
• Subscription End Date: 2025-12-31 23:59:59

Profile 3 -

• Subscription Start Date: 2023-01-01 00:00:00
• Subscription End Date: 2050-12-31 23:59:59

Profile 4 -

• Subscription Start Date: 2022-01-01 00:00:00
• Subscription End Date: 2999-12-31 23:59:59

With the current capability we have in audience builder, is there any way we can create an audience which fulfills the requirement: 

• subscription start date should be before current timestamp and subscription end date is after current timestamp

We are still exploring the various capabilities present in the Audience builder, but so far, we could not find any way to implement this. Any suggestions or pointers will be really helpful.

Thanks a lot for your help.

[DavidRoss91]

Hi @BitunSenAEP 
It may be easiest to use Data Distiller and SQL to build this type of audience in AEP. I have provided some documentation down below:
Build Audiences using SQL | Adobe Experience Platform

[BitunSenAEP]

Hi @DavidRoss91,

For last 3/4 years, I have been handling this type of requests/requirements

• using data distiller Query Service Only 

OR

• have an additional field "status" and requesting data source to derive and set the value or adding computed field mapping in the dataflow during ingestion (as suggested by @PratheepArunRaj )

All these options create an additional complexity. As users of AEP is mainly expected to be marketers, I think for this type of simple use cases, reaching out to IT team pushes the capability AEP in bad place - I have seen people raising eyebrows. 

There are so many features getting added in AEP every month. Can't we expect an enhancement in AEP Audience Builder UI to be able to do this using Audience Builder UI only? This will make it more powerful to position.

What do you think?

[DavidRoss91]

@BitunSenAEP I do agree with you on this. It becomes very complex this way. I would recommend adding an enhancement request in the "idea's" section of the community stating this request. I have seen success in doing this.

[BitunSenAEP]

@DavidRoss91 as recommended by you, I have submitted an idea about this. Below is the URL:

https://experienceleaguecommunities.adobe.com/t5/real-time-customer-data-platform/ability-to-create-...

[PratheepArunRaj]

Dear @BitunSenAEP,

If I understand correctly, you do have a field capturing the start date and the end date in the data type datetime.

If that is the case, can't we create a segment using Before and After using rolling dates? Or can we use compare operands to compare the start date and end date? Or am I completely missing your tricky question?

But the best option is to capture or add a variable to understand whether the subscription is active/inactive. Even if you are not capturing it currently, you can use the Query Service to update existing profiles while adjusting the implementation.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[DavidRoss91]

@PratheepArunRaj I thought this as well, but I don't think that you would be able to use before/after conditions in this instance since we can't capture a rolling "current timestamp" in the before or after condition within the UI? Please correct me if i'm wrong here.

[PratheepArunRaj]

Hello @DavidRoss91 ,

I didn't try creating one, so what you say is that rolling timestamp is not applicable for before/after conditions? It is such a basic condition to include, right? And Segment Builder didn't have this? Wondering buddy.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

PS: Let me try this out today and keep you posted if I have any solution.

[BitunSenAEP]

@PratheepArunRaj 

In the current Audience Builder UI, there is no rolling window capability at the right-hand side of the condition when we use BEFORE and AFTER. That is the main issue.

By the way, I am trying another option of creating this segment using PQL (create Segment API), where I could see an option. I have created the segment and waiting for the evaluation.

I have used the below expression:

"expression": {

"type": "PQL",

"format": "pql/text",

"value": "select _subscription from _XXX.subscription where (_subscription.subscriptionStartDate occurs before now) and (_subscription.subscriptionEndDate occurs after now)"

}

I hope this will work. Will keep you all posted on this as well.

[PratheepArunRaj]

Dear @BitunSenAEP @DavidRoss91 ,

It is a good exchange, thanks much.

Yes @BitunSenAEP , if you are successfully able to create the audience using API, kindly let us know.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[PratheepArunRaj]

Hello @BitunSenAEP @DavidRoss91 ,

Adobe Documentation is saying that we can select rolling days (days, weeks, months, or years ago) for before and after condition.

When I tried, it is available for BEFORE and not for AFTER. Weird.

Thank You, Pratheep Arun Raj B (Arun) | NextRow Digital | Terryn Winter Analytics

[BitunSenAEP]

Hi #PratheepArunRaj,

You are right. For the BEFORE condition, it has Custom Date with days, weeks, months and years. And it is not giving the feature of checking the condition I am talking about (subscriptionDate < current timestamp).

Anyways, a quick update on creating the segment using PQL which I was testing - it worked like a charm. So, I think, for now I would like to go ahead with this PQL approach only. 

Will definitely look forward for AEP Engineering team to add this feature in Audience Builder UI.

Thanks a lot for all your thoughts and time. Happy learning!!!

[PratheepArunRaj]

Thanks for posting the answer @BitunSenAEP . Will be super helpful for everyone.

[kautuk_sahni]

@BitunSenAEP Did you find the suggestions helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!

[BitunSenAEP · Accepted solution]

As Audience Builder UI is not supporting the feature I was looking for to implement the above mentioned requirement, I have decided to create the segment using PQL. 

I have used the below expression:

"expression": {

"type": "PQL",

"format": "pql/text",

"value": "select _subscription from _XXX.subscription where (_subscription.subscriptionStartDate occurs before now) and (_subscription.subscriptionEndDate occurs after now)"

}

This worked like a charm. I have validated the evaluation and verified the data of the qualified profiles. All good.

I will go ahead with this solution for now, until we see this capability in Audience Builder UI.

Thanks a lot Team for all your support and time. Happy learning!