Examples of Audience Builder Queries Using Paid Fields
- Bettina Lippisch
When creating a paid query you will need to open the Paid Subscription skittle to view your various paid fields. The paid subscription skittle can be found under Products > Magazines > Paid Subscription. Note, this may vary depending on where you have organized your paid products (i.e. paid newsletters may be under the Newsletter subfolder). If you don’t see the Paid Subscription skittle as an option in a folder, please open a JIRA ticket so the support team can configure it in for you.
When you click on the Paid Subscription skittle, it will expand to show the various paid fields you can query on from the Amount Paid to the Transaction Type. For a detailed explanation of each of these fields, check out our Paid Product Specific Fields article.
At the top you will notice a Subscription Type Filter with the default Current Subscriptions checked. Underneath is the All Subscriptions option, unchecked by default. The Subscription Type filter defaults to selecting current subscriptions so that you are only querying on current/live orders.
If you need to query all past paid orders with a specific date range or need to check how many orders are in pending payment status, then you would select the All Subscriptions option. The system will then look at all orders for a record instead of only the current active order.
NOTE: To have your selection of fields in the Paid Subscription skittle pull into your query, you will have to hit the search button at the bottom of the skittle, otherwise the selection will not appear.
Below are examples of several paid queries you can recreate in your database.
Autorenewals with Credit Cards that will Expire in 30 Days
This query is pulling all active paid records (qualified or non-qualified), who are opted into auto renew and have a Credit card on file that is expiring in the next 30 days.
Start by selecting the class = Active Qualified and Active Non-Qualified
Next, we isolate the paid records by selecting each of the following Payment Statuses:
– Paid with order
– Paid on Invoice
– Credit
Then we go to the Paid Subscription field > Renewal Code and select ‘On Expire – Auto Charge‘ This is how we isolate records who have opted into auto renew upon expire.
Lastly, go to the CC Expiration Field which allows you to query date ranges of subscribers with a credit card saved on file for autorenewals. You can enter a date range or month/year date (MMYY format) to query expiration dates. For this example we are selecting any credit cards that expire today through the next 30 days using this flex date formula: (today)-(today+30)
Active Autorenewals with Expired Credit Cards
This query is pulling those who are active paid records that have expired credit card but are also set to auto renew. This allows you to identify and then target these subscribers with messages that alert them their credit card on file has expired and they will need to provide updated information so that their subscription can process without interruption.
Start by selecting the class = Active Qualified and Active Non-Qualified
Next, isolate the paid records by selecting each of the following Payment Statuses:
– Paid with order
– Paid on Invoice
– Credit
Then, navigate to the Paid Subscription Field > Renewal Code and select ‘On Expire – Auto Charge‘. This will identify the records who have opted in to auto renew.
Next, go to the CC Expiration field which allows you to query date ranges of subs with a credit card saved on file for autorenewals. For this example, we’re looking at credit cards that have already expired, so use the flex formula: <(today) to look up any dates prior to today.
Lastly, go to the Rollup Expire Date which rolls up all orders (active & pending) to isolate the actual date the subscription will expire. Use the flex date formula: >(today) which will look for records who are expiring anytime in the future (note, the greater than symbol is used here, not the less than symbol used in the previous step). Feel free to modify the future expire date to narrow down records who have a subscription expiration in the next 30 days by changing the Rollup Expire Date formula to: >(today+30)
Paid Records with a Pending Paid Order
This query is selecting all Active Qualified paid records that also have a pending paid order. It allows you to see how many records have pre-emptively renewed their subscription (and thus have a pending order)
Start by selecting Class = Active Qualified
Next, you select the first of 2 paid fields, Payment Status, from the standard product skittles. Select all of the statuses:
– Paid with Order
– Paid on Invoice
– Credit
Next, you’ll select the second paid field under the Paid Subscription skittle > Payment Status. Make sure to set the subscription type to All Subscriptions so that it pulls in past (expired) and future (pending) orders. Select the following payment statuses:
– Paid with Order
– Paid on Invoice
– Credit
Then, set the Group Code to blank. This will pull in individual subs only
Lastly, select Status = Pending which will isolate your pending, paid orders
Active Group Records Receiving Grace Issues
This query example is pulling in all Active Qualified group records that are receiving grace issues.
Start by selecting Class = Active Qualified
Next, go to the Paid Subscriptions skittle > Graced Issues and select all numbers greater than 0. The dropdown will only show numbers that have subs with that number of graced issues received.
Next, you’ll need to create a new GROUP in your query. Hit the +Add Group button in the upper righthand corner (highlighted in green). Set the Group criteria to ‘AND‘ so the records in Group 2 are included in your query.
Now, (in Group 2) go to the Product > Paid Subscription > Group Admin and select Blank
Repeat (for Group 2) and add Product > Paid Subscription > Group Code = Blank
Lastly, EXCLUDE the Paid Subscription Blanks in Group 2. These blanks are those who are not part of a group, and not the focus on this query.
The remaining records will be those who are Active Qualified group records that are receiving graced issues.
If you have any questions or would like your queries reviewed, please reach out to your Client Success Manager. We can also assist with building campaigns centered around these audience segments for maximum paid record conversion.