Os dados da Adobe Target são transformados em schema XDM do Experience Evento e ingeridos no Adobe Experience Platform como conjuntos de dados para você. Há muitos casos de uso do Adobe Experience Platform Query Service com esses dados, e os seguintes query de amostra devem funcionar com seus conjuntos de dados Adobe Target.
No Experience Platform, o nome do conjunto de dados criado automaticamente é "Eventos do Adobe Target Experience". Ao usar esse conjunto de dados com query, você deve usar o nome adobe_target_experience_events
.
A lista a seguir mostra os campos de Público alvo que mapeiam para seus campos XDM correspondentes.
O uso de [ ]
no campo XDM denota uma matriz.
_experience.target.mboxname
_experience.target.activities.activityID
_experience.target.activities[].activityEvents[]._experience.target.activity.activityevent.context.experienceID
_experience.target.activities[].activityEvents[].segmentEvents[].segmentID._id
_experience.target.activities[].activityEvents[].eventScope
_experience.target.activities[].activityEvents[]._experience.target.activity.activityevent.context.stepID
commerce.order.priceTotal
Os query a seguir mostram exemplos de query usados com frequência no Adobe Target.
Nos exemplos a seguir, será necessário editar o SQL para preencher os parâmetros esperados para seus query com base no conjunto de dados, nas variáveis ou no período de tempo que você está interessado em avaliar. Forneça parâmetros onde quer que você veja { }
no SQL.
SELECT
Hour,
ActivityID,
COUNT(ActivityID) AS Instances
FROM
(
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd HH') AS Hour,
EXPLODE(_experience.target.activities.activityID) AS ActivityID
FROM adobe_target_experience_events
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
)
GROUP BY Hour, ActivityID
ORDER BY Hour DESC, Instances DESC
LIMIT 24
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd HH') AS Hour,
_experience.target.activities.activityID AS ActivityID,
COUNT(ActivityID) AS Instances
FROM adobe_target_experience_events
WHERE
array_contains( _experience.target.activities.activityID, {Activity ID} ) AND
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
GROUP BY Hour, ActivityID
ORDER BY Hour DESC
LIMIT 24
SELECT
Day,
Activities.activityID,
ExperienceID,
COUNT(ExperienceID) AS Instances
FROM
(
SELECT
Day,
Activities,
EXPLODE(Activities.activityEvents._experience.target.activity.activityevent.context.experienceID) AS ExperienceID
FROM
(
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd') AS Day,
EXPLODE(_experience.target.activities) AS Activities
FROM adobe_target_experience_events
WHERE
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
)
WHERE Activities.activityID = {activity_id}
)
GROUP BY Day, Activities.activityID, ExperienceID
ORDER BY Day DESC, Instances DESC
LIMIT 20
SELECT
Day,
Activities.activityID,
EventScope,
COUNT(EventScope) AS Instances
FROM
(
SELECT
Day,
Activities,
EXPLODE(Activities.activityEvents.eventScope) AS EventScope
FROM
(
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd') AS Day,
EXPLODE(_experience.target.activities) AS Activities
FROM adobe_target_experience_events
WHERE
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
)
)
GROUP BY Day, Activities.activityID, EventScope
ORDER BY Day DESC, Instances DESC
LIMIT 30
SELECT
Hour,
Activities.activityid,
SUM(CASE WHEN array_contains( Activities.activityEvents.eventScope, 'visitor' ) THEN 1 END) as Visitors,
SUM(CASE WHEN array_contains( Activities.activityEvents.eventScope, 'visit' ) THEN 1 END) as Visits,
SUM(CASE WHEN array_contains( Activities.activityEvents.eventScope, 'impression' ) THEN 1 END) as Impressions
FROM
(
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd HH') AS Hour,
EXPLODE(_experience.target.activities) AS Activities
FROM adobe_target_experience_events
WHERE
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
)
GROUP BY Hour, Activities.activityid
ORDER BY Hour DESC, Visitors DESC
LIMIT 30
SELECT
Day,
Activities.activityID,
ExperienceID,
SegmentID._id,
SUM(CASE WHEN ActivityEvent.eventScope = 'visitor' THEN 1 END) as Visitors,
SUM(CASE WHEN ActivityEvent.eventScope = 'visit' THEN 1 END) as Visits,
SUM(CASE WHEN ActivityEvent.eventScope = 'impression' THEN 1 END) as Impressions
FROM
(
SELECT
Day,
Activities,
ActivityEvent,
ActivityEvent._experience.target.activity.activityevent.context.experienceID AS ExperienceID,
EXPLODE(ActivityEvent.segmentEvents.segmentID) AS SegmentID
FROM
(
SELECT
Day,
Activities,
EXPLODE(Activities.activityEvents) AS ActivityEvent
FROM
(
SELECT
date_format(from_utc_timestamp(timestamp, 'America/New_York'), 'yyyy-MM-dd') AS Day,
EXPLODE(_experience.target.activities) AS Activities
FROM adobe_target_experience_events
WHERE
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}') AND
_experience.target.activities IS NOT NULL
LIMIT 1000000
)
LIMIT 1000000
)
LIMIT 1000000
)
GROUP BY Day, Activities.activityID, ExperienceID, SegmentID._id
ORDER BY Day DESC, Activities.activityID, ExperienceID ASC, SegmentID._id ASC, Visitors DESC
LIMIT 20
SELECT
_experience.target.mboxname,
COUNT(timestamp) AS records
FROM
adobe_target_experience_events
WHERE
TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY _experience.target.mboxname ORDER BY records DESC
LIMIT 100