DAS Widgets Data
DAS stands for Data and Analytics Summary. The DAS widgets are used to display data in the Emarsys Contact profile. Project is owned by the Analytics team.
We have to store necessary data in BigQuery and provide queries to fetch that data for the widgets that we want to support. We have commited to the following widgets.
Widgets
Push Clicks Widget
Query
------------------------
-- PUSH CLICKS WIDGET --
------------------------
-- Inputs:
-- <CUSTOMER_ID> 210268110
-- <CONTACT_ID> 862947331
-- Query:
WITH contact_state AS (
SELECT
customer_id,
contact_id,
field_id,
field_value
FROM
`sap-contact-p.contact_state.state_210268110`
WHERE
contact_id = 862947331
AND field_id IN (1,2,3,4,10,11,12,13,14,15,31,37)
),
contact_information AS (
SELECT
*
FROM
contact_state
PIVOT (
ANY_VALUE(field_value)
FOR field_id
IN (
1 AS first_name,
2 AS last_name,
3 AS email,
4 AS birthday,
10 AS address,
11 AS city,
12 AS state,
13 AS zip_code,
14 AS country,
15 AS phone,
31 AS optIn,
37 AS mobile
)
)
),
clicks AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.push_clicks.clicks_*`
WHERE
_TABLE_SUFFIX = '210268110'
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = 862947331
),
last_click AS (
SELECT
*
FROM
clicks
WHERE
rn = 1
),
num_clicks AS (
SELECT
COUNT(1) AS clicked
FROM
clicks
),
clicks_by_device as (
SELECT
platform AS device,
COUNT(*) AS count
FROM
clicks
GROUP BY
platform
),
campaigns AS (
SELECT
*,
"push" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.push_database",
"""
SELECT
id AS internal_id,
multichannel_id AS id,
name
FROM
campaigns
WHERE
customer_id = 210268110
"""
)
)
SELECT
(SELECT AS STRUCT contact_information.*) as contactInformation,
STRUCT(
STRUCT(
num_clicks.clicked AS clicked
) AS behavior,
STRUCT (
STRUCT(
last_click.campaign_id AS id,
campaigns.internal_id AS internalId,
campaigns.name AS name,
campaigns.type AS type
) AS campaign,
last_click.platform AS device,
last_click.event_time AS eventTime
) AS lastEvent
) AS pushClicks,
(SELECT ARRAY_AGG(STRUCT(device, count)) from clicks_by_device) as pushClickDeviceDistribution
FROM
contact_information
LEFT JOIN last_click ON 1 = 1
LEFT JOIN num_clicks ON 1 = 1
LEFT JOIN campaigns ON campaigns.id = last_click.campaign_idResponse
[{
"contactInformation": {
"customer_id": "210268110",
"contact_id": "862947331",
"first_name": "SDK",
"last_name": "Testing",
"email": "test2@test.com",
"birthday": null,
"address": null,
"city": null,
"state": null,
"zip_code": null,
"country": null,
"phone": null,
"optIn": "1",
"mobile": null
},
"pushClicks": {
"behavior": {
"clicked": "232"
},
"lastEvent": {
"campaign": {
"id": "100375077",
"internalId": "123624",
"name": "testOfTests",
"type": "push"
},
"device": "android",
"eventTime": "2025-03-12 13:34:51.459000 UTC"
}
},
"pushClickDeviceDistribution": [{
"device": "ios",
"count": "57"
}, {
"device": "android",
"count": "175"
}]
}]In-app Clicks Widget
Query
--------------------------
-- IN-APP CLICKS WIDGET --
--------------------------
-- Inputs:
-- <CUSTOMER_ID> 210268110
-- <CONTACT_ID> 862947331
-- Query:
WITH contact_state AS (
SELECT
customer_id,
contact_id,
field_id,
field_value
FROM
`sap-contact-p.contact_state.state_210268110`
WHERE
contact_id = 862947331
AND field_id IN (1,2,3,4,10,11,12,13,14,15,31,37)
),
contact_information AS (
SELECT
*
FROM
contact_state
PIVOT (
ANY_VALUE(field_value)
FOR field_id
IN (
1 AS first_name,
2 AS last_name,
3 AS email,
4 AS birthday,
10 AS address,
11 AS city,
12 AS state,
13 AS zip_code,
14 AS country,
15 AS phone,
31 AS optIn,
37 AS mobile
)
)
),
clicks AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.inapp_clicks.inapp_clicks_*`
WHERE
_TABLE_SUFFIX = '210268110'
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = 862947331
),
last_click AS (
SELECT
*
FROM
clicks
WHERE
rn = 1
),
num_clicks AS (
SELECT
COUNT(1) AS clicked
FROM
clicks
),
clicks_by_device as (
SELECT
platform AS device,
COUNT(*) AS count
FROM
clicks
GROUP BY
platform
),
campaigns AS (
SELECT
*,
"inapp" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.inapp_database",
"""
SELECT
dbid AS internal_id,
id,
name
FROM
campaign
WHERE
customer_id = 210268110
"""
)
)
SELECT
(SELECT AS STRUCT contact_information.*) as contactInformation,
STRUCT(
STRUCT(
num_clicks.clicked AS clicked
) AS behavior,
STRUCT (
STRUCT(
last_click.campaign_id AS id,
campaigns.internal_id AS internalId,
campaigns.name AS name,
campaigns.type AS type
) AS campaign,
last_click.platform AS device,
last_click.event_time AS eventTime
) AS lastEvent
) AS inappClicks,
(SELECT ARRAY_AGG(STRUCT(device, count)) from clicks_by_device) as inappClickDeviceDistribution
FROM
contact_information
LEFT JOIN last_click ON 1 = 1
LEFT JOIN num_clicks ON 1 = 1
LEFT JOIN campaigns ON campaigns.id = last_click.campaign_idResponse
[{
"contactInformation": {
"customer_id": "210268110",
"contact_id": "862947331",
"first_name": "SDK",
"last_name": "Testing",
"email": "test2@test.com",
"birthday": null,
"address": null,
"city": null,
"state": null,
"zip_code": null,
"country": null,
"phone": null,
"optIn": "1",
"mobile": null
},
"inappClicks": {
"behavior": {
"clicked": "180"
},
"lastEvent": {
"campaign": {
"id": "100537125",
"internalId": "48866",
"name": "Coroutine testing",
"type": "inapp"
},
"device": "android",
"eventTime": "2025-03-18 10:02:43.802000 UTC"
}
},
"inappClickDeviceDistribution": [{
"device": "android",
"count": "120"
}, {
"device": "ios",
"count": "60"
}]
}]Push Opens Widget
Query
------------------------
-- PUSH OPENS WIDGET --
------------------------
-- Inputs:
-- <CUSTOMER_ID> 210268110
-- <CONTACT_ID> 862947331
-- Query:
WITH contact_state AS (
SELECT
customer_id,
contact_id,
field_id,
field_value
FROM
`sap-contact-p.contact_state.state_210268110`
WHERE
contact_id = 862947331
AND field_id IN (1,2,3,4,10,11,12,13,14,15,31,37)
),
contact_information AS (
SELECT
*
FROM
contact_state
PIVOT (
ANY_VALUE(field_value)
FOR field_id
IN (
1 AS first_name,
2 AS last_name,
3 AS email,
4 AS birthday,
10 AS address,
11 AS city,
12 AS state,
13 AS zip_code,
14 AS country,
15 AS phone,
31 AS optIn,
37 AS mobile
)
)
),
push_opens_raw AS (
SELECT
campaign_id,
hardware_id AS client_id,
application_code,
event_time
FROM
`ems-mobile-engage.mobile_push_opens_raw.push_opens_*`
WHERE
_TABLE_SUFFIX = '210268110'
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = 862947331
),
push_opens AS (
SELECT
po.campaign_id,
cs.platform,
po.event_time,
ROW_NUMBER() OVER (ORDER BY po.event_time DESC) AS rn
FROM
push_opens_raw AS po
INNER JOIN
`ems-mobile-engage.client_state.state_210268110` AS cs
ON
LOWER(po.client_id) = LOWER(cs.client_id)
AND po.application_code = cs.application_code
),
inbox_opens AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.inbox_tag_changes.tag_changes_*`
WHERE
_TABLE_SUFFIX = '210268110'
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND tag.operation = 'add'
AND tag.name = 'opened'
AND contact_id = 862947331
),
opens AS (
SELECT
*
FROM
push_opens
UNION ALL
SELECT
*
FROM
inbox_opens
),
last_open AS (
SELECT
*
FROM
opens
WHERE
rn = 1
),
num_opens AS (
SELECT
COUNT(1) AS opened
FROM
opens
),
opens_by_device as (
SELECT
platform AS device,
COUNT(*) AS count
FROM
opens
GROUP BY
platform
),
campaigns AS (
SELECT
*,
"push" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.push_database",
"""
SELECT
id AS internal_id,
multichannel_id AS id,
name
FROM
campaigns
WHERE
customer_id = 210268110
"""
)
)
SELECT
(SELECT AS STRUCT contact_information.*) as contactInformation,
STRUCT(
STRUCT(
num_opens.opened AS opened
) AS behavior,
STRUCT (
STRUCT(
last_open.campaign_id AS id,
campaigns.internal_id AS internalId,
campaigns.name AS name,
campaigns.type AS type
) AS campaign,
last_open.platform AS device,
last_open.event_time AS eventTime
) AS lastEvent
) AS pushOpens,
(SELECT ARRAY_AGG(STRUCT(device, count)) from opens_by_device) as pushOpenDeviceDistribution
FROM
contact_information
LEFT JOIN last_open ON 1 = 1
LEFT JOIN num_opens ON 1 = 1
LEFT JOIN campaigns ON campaigns.id = last_open.campaign_idResponse
[{
"contactInformation": {
"customer_id": "210268110",
"contact_id": "862947331",
"first_name": "SDK",
"last_name": "Testing",
"email": "test2@test.com",
"birthday": null,
"address": null,
"city": null,
"state": null,
"zip_code": null,
"country": null,
"phone": null,
"optIn": "1",
"mobile": null
},
"pushOpens": {
"behavior": {
"opened": "95"
},
"lastEvent": {
"campaign": {
"id": "100620082",
"internalId": "765765",
"name": "TestForiOSRelease120225",
"type": "push"
},
"device": "ios",
"eventTime": "2025-02-12 13:00:28.321000 UTC"
}
},
"pushOpenDeviceDistribution": [{
"device": "ios",
"count": "95"
}]
}]In-app Impressions Widget
Query
-------------------------------
-- IN-APP IMPRESSIONS WIDGET --
-------------------------------
-- Inputs:
-- <CUSTOMER_ID> 210268110
-- <CONTACT_ID> 862947331
-- Query:
WITH contact_state AS (
SELECT
customer_id,
contact_id,
field_id,
field_value
FROM
`sap-contact-p.contact_state.state_210268110`
WHERE
contact_id = 862947331
AND field_id IN (1,2,3,4,10,11,12,13,14,15,31,37)
),
contact_information AS (
SELECT
*
FROM
contact_state
PIVOT (
ANY_VALUE(field_value)
FOR field_id
IN (
1 AS first_name,
2 AS last_name,
3 AS email,
4 AS birthday,
10 AS address,
11 AS city,
12 AS state,
13 AS zip_code,
14 AS country,
15 AS phone,
31 AS optIn,
37 AS mobile
)
)
),
impressions AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.inapp_views.inapp_views_*`
WHERE
_TABLE_SUFFIX = '210268110'
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = 862947331
),
last_impression AS (
SELECT
*
FROM
impressions
WHERE
rn = 1
),
num_impressions AS (
SELECT
COUNT(1) AS viewed
FROM
impressions
),
impressions_by_device as (
SELECT
platform AS device,
COUNT(*) AS count
FROM
impressions
GROUP BY
platform
),
campaigns AS (
SELECT
*,
"inapp" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.inapp_database",
"""
SELECT
dbid AS internal_id,
id,
name
FROM
campaign
WHERE
customer_id = 210268110
"""
)
)
SELECT
(SELECT AS STRUCT contact_information.*) as contactInformation,
STRUCT(
STRUCT(
num_impressions.viewed AS viewed
) AS behavior,
STRUCT (
STRUCT(
last_impression.campaign_id AS id,
campaigns.internal_id AS internalId,
campaigns.name AS name,
campaigns.type AS type
) AS campaign,
last_impression.platform AS device,
last_impression.event_time AS eventTime
) AS lastEvent
) AS inappImpressions,
(SELECT ARRAY_AGG(STRUCT(device, count)) from impressions_by_device) as inappImpressionDeviceDistribution
FROM
contact_information
LEFT JOIN last_impression ON 1 = 1
LEFT JOIN num_impressions ON 1 = 1
LEFT JOIN campaigns ON campaigns.id = last_impression.campaign_idResponse
[{
"contactInformation": {
"customer_id": "210268110",
"contact_id": "862947331",
"first_name": "SDK",
"last_name": "Testing",
"email": "test2@test.com",
"birthday": null,
"address": null,
"city": null,
"state": null,
"zip_code": null,
"country": null,
"phone": null,
"optIn": "1",
"mobile": null
},
"inappImpressions": {
"behavior": {
"viewed": "656"
},
"lastEvent": {
"campaign": {
"id": "100566818",
"internalId": "68847",
"name": "0221",
"type": "inapp"
},
"device": "ios",
"eventTime": "2025-03-18 10:14:41.353000 UTC"
}
},
"inappImpressionDeviceDistribution": [{
"device": "android",
"count": "420"
}, {
"device": "ios",
"count": "236"
}]
}]Proof of Concept
As a proof of concept, we want to implement "Mobile clicks" widget (see Widget Info in the Resources section). This widget displays:
-
total number of messages (Push message buttons, In-app messages) a contact has clicked on
-
preferred device that contact uses
-
name of the last campaign that contact clicked on
Query
-- Inputs:
-- <CUSTOMER_ID>
-- <CONTACT_ID>
-- Query:
WITH contact_state AS (
SELECT
customer_id,
contact_id,
field_id,
field_value
FROM
`sap-contact-p.contact_state.state_<CUSTOMER_ID>`
WHERE
contact_id = <CONTACT_ID>
AND field_id IN (1,2,3,4,10,11,12,13,14,15,31,37)
),
contact_information AS (
SELECT
*
FROM
contact_state
PIVOT (
ANY_VALUE(field_value)
FOR field_id
IN (
1 AS first_name,
2 AS last_name,
3 AS email,
4 AS birthday,
10 AS address,
11 AS city,
12 AS state,
13 AS zip_code,
14 AS country,
15 AS phone,
31 AS optIn,
37 AS mobile
)
)
),
inapp_clicks AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.inapp_clicks.inapp_clicks_*`
WHERE
_TABLE_SUFFIX = <CUSTOMER_ID>
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = <CONTACT_ID>
),
push_clicks AS (
SELECT
campaign_id,
platform,
event_time,
ROW_NUMBER() OVER (ORDER BY event_time DESC) AS rn
FROM
`ems-mobile-engage.push_clicks.clicks_*`
WHERE
_TABLE_SUFFIX = <CUSTOMER_ID>
AND (
_PARTITIONTIME IS NULL
OR DATE(_PARTITIONTIME) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
AND contact_id = <CONTACT_ID>
),
clicks AS (
SELECT
*
FROM
inapp_clicks
UNION ALL
SELECT
*
FROM
push_clicks
),
last_click AS (
SELECT
*
FROM
clicks
WHERE
rn = 1
),
num_clicks AS (
SELECT
COUNT(1) AS clicked
FROM
clicks
),
clicks_by_device as (
SELECT
platform AS device,
COUNT(*) AS count
FROM
clicks
GROUP BY
platform
),
campaigns AS (
SELECT
*,
"inapp" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.inapp_database",
"""
SELECT
dbid AS internal_id,
id,
name
FROM
campaign
WHERE
customer_id = <CUSTOMER_ID>
"""
)
UNION ALL
SELECT
*,
"push" AS type
FROM
EXTERNAL_QUERY(
"ems-mobile-engage.eu.push_database",
"""
SELECT
id AS internal_id,
multichannel_id AS id,
name
FROM
campaigns
WHERE
customer_id = <CUSTOMER_ID>
"""
)
)
SELECT
(SELECT AS STRUCT contact_information.*) as contactInformation,
STRUCT(
num_clicks.clicked AS clicked,
STRUCT (
STRUCT(
last_click.campaign_id AS id,
campaigns.name AS name,
CASE campaigns.type
WHEN "inapp" THEN CONCAT("bootstrap.php?r=service&service=push-notification&service_product=mobile_engage#/reports/inapp/", campaigns.id)
WHEN "push" THEN CONCAT("bootstrap.php?r=service&service=push-notification&service_product=mobile_engage#/reports/push/" , campaigns.internal_id)
ELSE NULL
END AS link
) AS campaign,
last_click.platform AS device,
last_click.event_time AS eventTime
) AS lastEvent
) AS mobileClicks,
(SELECT ARRAY_AGG(STRUCT(device, count)) from clicks_by_device) as clickDeviceDistribution
FROM
contact_information
LEFT JOIN last_click ON 1 = 1
LEFT JOIN num_clicks ON 1 = 1
LEFT JOIN campaigns ON campaigns.id = last_click.campaign_idExample result
[{
"contactInformation": {
"customer_id": "123456789",
"contact_id": "100200300",
"first_name": "Pippo",
"last_name": "Inzaghi",
"email": "pippo.inzaghi@italy.com",
"birthday": "1973-08-09",
"address": "Via dei Campioni",
"city": "Milano",
"state": "Lombardia",
"zip_code": "20100",
"country": "Italy",
"phone": "+390123456789",
"optIn": "1",
"mobile": "+393331234567"
},
"mobileClicks": {
"clicked": "700",
"lastEvent": {
"campaign": {
"id": "2492418",
"name": "Last campaign I clicked on",
"link": "bootstrap.php?r=service&service=push-notification&service_product=mobile_engage#/reports/inapp/2492418"
},
"device": "ios",
"eventTime": "2025-01-27 14:19:36.844000 UTC"
}
},
"clickDeviceDistribution": [{
"device": "ios",
"count": "600"
}, {
"device": "android",
"count": "100"
}]
}]E-mail implementation
BigQuery query to fetch the data for the widgets: https://github.com/emartech/data-and-activity-summary-service/blob/983dea957e72a892445848678023546d801c6d56/terraform/sap-data-and-activity-sum-p/main.tf#L58-L180
Example JSON result for data fetched from previous BigQuery query: https://github.com/emartech/data-and-activity-summary-client/blob/main/data-examples/getContactProfile/sarah-snow.json