Additional operations
As you work with audiences in Composer Builder, bear in mind a few additional operations:
Save a draft audience
At some point during the process of creating your audience, you may decide that rather than activating the audience, you only want to save it as a draft.
To do this:
➢ Click More Actions and select the Save as Draft option.
The system responds with a message confirming a successful save, and it adds the new audience - in Draft status - to the bottom of the Audiences tab back on the Audience Composer main page.
Note that, at minimum, the audience name must be at least 3 characters long, otherwise, you cannot save it as a draft.
Estimate audience size
As you build audience attribute cards, you may want to see an estimate of the audience you are
building.
To do this:
➢ Click Refresh in the Estimated Audience Size box.
The system responds by displaying the numerical size of the audience in the box.
Any subsequent change made to the audience attribute cards causes the Estimated Audience Size box to reset and clear out the displayed audience size.
Note that the refresh is not automatic; the Refresh button must be manually clicked.
Convert audience definition to SQL editor
Audience creation from Composer Builder is facilitated by a UI that makes it easy to define an audience query. This query - written in SQL - extracts the audience you need from the Data Cloud.
You may find that you need to supplement your audience definition with attributes that are not available in the Builder UI. In this case, you can specify those additional attributes via the Audience Composer’s SQL query editor. But bear in mind that once converted, you cannot return Composer Builder and its canvas-based format.
It’s worth noting that if you want to supplement your audience segmentation logic with SQL but prefer not to move from the Builder UI to the SQL editor, you can utilize a custom card type called “SQL Query.” You can select that card type as part of the procedure for defining an audience, which is documented in Creating an Audience.
The section below details how to render an audience query in SQL; it is followed by another section that includes a library of common SQL query statements.
Rendering an audience query in SQL
Once your work using Composer Builder is complete, you can:
-
Click More Actions on the Builder page and then select the Convert to SQL Query Editor option. The system presents a confirmation dialog that explains that the audience will be converted into an editable SQL statement that cannot be undone.
-
Click Yes. The audience you defined in Builder is now represented in SQL in the SQL Query Editor. It can no longer be accessed from Builder.
Reviewing the Composer SQL query library
Before you begin defining an audience query with SQL, review this library of common SQL query statements. Each of these query templates reflects audience segmentation that is typical of the data in a SessionM Platform implementation.
General loyalty
This query extracts join date anniversary data:
select user_id
from {{CLIENT}}_mazu_std.v_ins_user_info iui
where joined_at is not null
and concat(cast(month(date(joined_at)) as
varchar),'-',cast(day(date(joined_at))as varchar)) =
concat(cast(month((current_date + interval '1' day)) as
varchar),'-',cast(day((current_date + interval '1' day))as varchar))"
Offers
This query extracts data that corresponds with customers that acquired a specific offer but have not redeemed it yet:
SELECT distinct uo.user_id
FROM {{CLIENT}}_mazu_std.user_offers uo
WHERE uo.root_offer_id = 'ROOT_OFFER_ID'
and uo.redeem_date is null
and uo.acquire_date >= current_date - interval '30' day"
This query extracts data associated with when an unused offer will expire in the next N days:
SELECT distinct uo.user_id
FROM {{CLIENT}}_mazu_std.user_offers uo
WHERE uo.root_offer_id = 'ROOT_OFFER_ID'
and uo.redeem_date is null
SessionM, A Mastercard Company Page 13
and date(uo.redemption_end_date) >= current_date - interval 'N' day"
This query extracts data for customers that have never redeemed an offer:
"SELECT distinct uo.user_id
FROM {{CLIENT}}_mazu_std.user_offers uo
WHERE uo.redeem_date is null"
Transactions
This query extracts data for customers who purchased with X payment type in the last Y days:
"SELECT eui.external_id
FROM {{CLIENT_ID}}_mazu_std.ext_unique_ids eui
JOIN {{CLIENT_ID}}_mazu_std.transaction_payments tp using (user_id)
WHERE tp.payment_type = 'GIFT CARD'
AND date(date_parse(substr(tp.payment_date, 1, 19), '%Y-%m-%d
%H:%i:%s')) >= current_date - interval '30' day;
This query extracts data for customers who have had a transaction that included a discount in the last 90 days:
select eui.external_id
from {{CLIENT}}_mazu_std.ext_unique_ids eui
join {{CLIENT}}_mazu_std.transaction_payments tp
on eui.user_id=tp.user_id
join {{CLIENT}}_mazu_std.transaction_discounts td
on td.transaction_id=tp.transaction_id
where date(td.created_at) > current_date - interval 'X' day -- insert
your # of days
This query extracts data for customers who made a purchase at store X:
SELECT eum.external_user_id
FROM {{CLIENT_ID}}_mazu_std.transaction_payments tp
JOIN {{CLIENT_ID}}_mazu_std.retailer_stores rs
using(retailer_store_id)
JOIN {{CLIENT_ID}}_mazu_std.external_user_mappings eum using
(user_id)
WHERE rs.name = 'Store Name' -- insert store name
AND eum.external_user_id_type = 'SalesForce' limit 10;
This query extracts data for customers who have bought items in X category:
Select eui.external_id from {{CLIENT}}_mazu_std.ext_unique_ids eui
join {{CLIENT}}_mazu_std.transaction_payments tp
on eui.user_id = tp.user_id
join {{CLIENT}}_mazu_std.transaction_items ti
on tp.transaction_id=ti.transaction_id
join {{CLIENT}}_mazu_std.master_items mi
on ti.pos_item_key=mi.sku
where mi.item_id IN
(Select mi.item_id from {{CLIENT}}_mazu_std.master_items mi
join master_item_parents mip
on mi.item_id=mip.item_id
join master_categories mc
on mip.category_id=mc.category_id
where mc.display_name LIKE '%DRESSES%')
and date(ti.transaction_datetime) = current_date - interval '1' day
This query extracts data for customers who never made a purchase:
select eum.external_user_id
from {{CLIENT}}_mazu_std.users u
join {{CLIENT}}_mazu_std.external_user_mappings eum on eum.user_id =
u.user_id
where u.user_id not in
(select tp.user_id from {{CLIENT}}_mazu_std.transaction_payments tp)
This query extracts data for customers who never made a purchase (SOR):
select eum.external_user_id
from {{CLIENT}}_mazu_std.external_user_mappings eum
where eum.external_user_id not in
(select tp.user_id from {{CLIENT}}_mazu_std.transaction_payments tp)
This query extracts data for customers who have bought X items in the last N time period:
SELECT distinct user_id
FROM {{CLIENT_ID}}_mazu_std.transaction_headers th
JOIN {{CLIENT_ID}}_mazu_std.transaction_items ti on
lower(th.transaction_id) = lower(ti.transaction_id)
WHERE th.is_closed = true annd th.is_voided = false
and datet(th.check_open_date) >= current_date - interval 'N' day
annd ti.pos_item_key in ('POS_ITEM_KEY')
Delete an audience
Should you find that you need to delete an existing audience, click More Actions and select the Delete option. The system displays a Delete Confirmation dialog, where you can click Delete to remove the audience from the Audiences tab on the Audience Composer main page.