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:

  1. 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.

  2. 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.