SQL-Abfragen schreiben, die Online-, Call-Center- und Treuedaten in Adobe Experience Platform kombinieren
Informationen zu Adobe-definierten Funktionen
Kontext
In diesen Übungen schreiben Sie Abfragen zur Analyse von Ansichten, Produktflecken, Kehren usw.
Alle in diesem Kapitel aufgelisteten Abfragen werden in Ihrer PSQL-Befehlszeilenschnittstelle ausgeführt. Sie sollten die Anweisungsblöcke, die mit SQL angegeben sind, kopieren (STRG-V) und in die PSQL-Befehlszeilenschnittstelle einfügen. Die Blöcke Abfrage Result zeigen die eingefügte SQL-Anweisung und das zugehörige Abfragen-Ergebnis an.
7.3.1 Grundlegende Abfragen für die Analyse von Daten schreiben
Zeitstempel
In Adobe Experience Platform erfasste Daten werden mit einem Zeitstempel versehen. Mit dem Attribut timestamp können Sie Daten im Zeitverlauf analysieren.
Wie viele Ansichten haben wir täglich?
SQL
select date_format( timestamp , 'yyyy-MM-dd') AS Day,
count(*) AS productViews
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
aepenablementfy21:all=> select date_format( timestamp , 'yyyy-MM-dd') AS Day,
aepenablementfy21:all-> count(*) AS productViews
aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1
aepenablementfy21:all-> where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
aepenablementfy21:all-> and eventType = 'commerce.productViews'
aepenablementfy21:all-> group by Day
aepenablementfy21:all-> limit 10;
Day | productViews
------------+--------------
2020-07-31 | 2297
(1 row)
Die fünf beliebtesten Produkte
Welches sind die fünf am häufigsten angezeigten Produkte?
SQL
select productListItems.name, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and eventType = 'commerce.productViews'
group by productListItems.name
order by 2 desc
limit 5;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
aepenablementfy21:all=> select productListItems.name, count(*)
aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1
aepenablementfy21:all-> where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
aepenablementfy21:all-> and eventType = 'commerce.productViews'
aepenablementfy21:all-> group by productListItems.name
aepenablementfy21:all-> order by 2 desc
aepenablementfy21:all-> limit 5;
name | count(1)
---------------------------------------+----------
Google Pixel XL 32GB Black Smartphone | 938
SIM Only | 482
Samsung Galaxy S8 | 456
Samsung Galaxy S7 32GB Black | 421
(4 rows)
Produktinteraktionstrichter, vom Anzeigen bis zum Kauf
SQL
select eventType, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and eventType is not null
and eventType <> ''
group by eventType;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
aepenablementfy21:all=> select eventType, count(*)
aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1
aepenablementfy21:all-> where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
aepenablementfy21:all-> and eventType is not null
aepenablementfy21:all-> and eventType <> ''
aepenablementfy21:all-> group by eventType;
eventType | count(1)
------------------------------+----------
commerce.productViews | 2297
commerce.productListAdds | 494
commerce.purchases | 246
(3 rows)
Identifizieren Sie Besucher mit Churn-Risiko (Besuchsseite => Dienst abbrechen).
SQL
select distinct --aepTenantId--.identification.core.ecid
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and web.webPageDetails.name = 'Cancel Service'
group by --aepTenantId--.identification.core.ecid
limit 10;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
aepenablementfy21:all=> select distinct --aepTenantId--.identification.core.ecid
aepenablementfy21:all-> from demo_system_event_dataset_for_website_global_v1_1
aepenablementfy21:all-> where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
aepenablementfy21:all-> and web.webPageDetails.name = 'Cancel Service'
aepenablementfy21:all-> group by --aepTenantId--.identification.core.ecid
aepenablementfy21:all-> limit 10;
ecid
----------------------------------
67802232253493573025911610627278
27147331741697745713411940873426
19806347932758146991274525406147
06339676267512351981624626408225
23933440740775575701680766564499
11860828134020790182705892056898
04258863338643046907489131372300
90257333076958492787834714105751
66695181015407529430237951973742
19103852558440070949457567094096
(10 rows)
In den nächsten Abfragen erweitern wir die oben genannte Abfrage, um eine vollständige Ansicht über die Kunden und deren Verhalten zu erhalten, die die Seite "Dienst abbrechen" besucht haben. Sie erfahren, wie Sie mit der Funktion "Adobe definiert"Informationen segmentieren, die Reihenfolge und die Uhrzeit von Ereignissen identifizieren. Außerdem werden Sie die Datasets zusammenführen, um die Daten weiter zu bereichern und für die Analyse in Microsoft Power BI vorzubereiten.
7.3.2 Erweiterte Abfragen
Der Großteil der Geschäftslogik erfordert das Sammeln der Berührungspunkte für einen Kunden und deren rechtzeitige Bestellung. Dies wird von Spark SQL anhand von sogenannten Fenster- bzw. „Window“-Funktionen unterstützt. Window-Funktionen sind Teil von Standard-SQL und werden von einer Vielzahl anderer SQL-Engines unterstützt.
Adobe-definierte Funktionen
Adobe hat der SQL-Standardsyntax einen Satz von Adobe Definierte Funktionen hinzugefügt, mit denen Sie Ihre Erlebnisdaten besser verstehen können. In den nächsten Abfragen erfahren Sie mehr über diese ADF-Funktionen. Weitere Informationen und die vollständige Liste finden Sie in der Dokumentation.
Was machen Personen auf der Site, bevor sie die Seite "Dienst abbrechen"als dritte Seite einer Sitzung aufrufen?
Mit dieser Abfrage entdecken Sie die ersten beiden Adoben Definierte Funktionen SESS_TIMEOUT und NEXT
Die SESS_TIMEOUT()-Variable gibt die Besuchgruppierungen wieder, die mit Adobe Analytics gefunden wurden. Es führt eine ähnliche zeitbasierte Gruppierung durch, aber anpassbare Parameter.
Mit NEXT() und PREVIOUS() erfahren Sie, wie Kunden durch Ihre Site navigieren.
SQL
SELECT
webPage,
webPage_2,
webPage_3,
webPage_4,
count(*) journeys
FROM
(
SELECT
webPage,
NEXT(webPage, 1, true)
OVER(PARTITION BY ecid, session.num
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value
AS webPage_2,
NEXT(webPage, 2, true)
OVER(PARTITION BY ecid, session.num
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value
AS webPage_3,
NEXT(webPage, 3, true)
OVER(PARTITION BY ecid, session.num
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).value
AS webPage_4,
session.depth AS SessionPageDepth
FROM (
select a.--aepTenantId--.identification.core.ecid as ecid,
a.timestamp,
web.webPageDetails.name as webPage,
SESS_TIMEOUT(timestamp, 60 * 30)
OVER (PARTITION BY a.--aepTenantId--.identification.core.ecid
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS session
from demo_system_event_dataset_for_website_global_v1_1 a
where a.--aepTenantId--.identification.core.ecid in (
select b.--aepTenantId--.identification.core.ecid
from demo_system_event_dataset_for_website_global_v1_1 b
where b.--aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and b.web.webPageDetails.name = 'Cancel Service'
)
)
)
WHERE SessionPageDepth=1
and webpage_3 = 'Cancel Service'
GROUP BY webPage, webPage_2, webPage_3, webPage_4
ORDER BY journeys DESC
LIMIT 10;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
webPage | webPage_2 | webPage_3 | webPage_4 | journeys
---------------------------------------+---------------------------------------+----------------+------------+----------
Citi Signal Sport | Google Pixel XL 32GB Black Smartphone | Cancel Service | Call Start | 2
SIM Only | Citi Signal Shop | Cancel Service | | 2
SIM Only | Telco Home | Cancel Service | | 2
TV & Broadband Deals | Samsung Galaxy S7 32GB Black | Cancel Service | | 2
Telco Home | Citi Signal Sport | Cancel Service | Call Start | 2
Google Pixel XL 32GB Black Smartphone | Broadband Deals | Cancel Service | | 2
Broadband Deals | Samsung Galaxy S7 32GB Black | Cancel Service | | 2
Broadband Deals | Samsung Galaxy S8 | Cancel Service | | 1
Samsung Galaxy S8 | Google Pixel XL 32GB Black Smartphone | Cancel Service | | 1
SIM Only | Google Pixel XL 32GB Black Smartphone | Cancel Service | Call Start | 1
(10 rows)
Wie viel Zeit haben wir, bevor ein Besucher das Call Center aufruft, nachdem wir die Seite "Dienst abbrechen"besucht haben?
Um diese Abfrage zu beantworten, verwenden wir die Funktion TIME_BETWEEN_NEXT_MATCH() Adobe Definiert.
Die Funktion "Zeit zwischen vorherigen oder nächsten Übereinstimmungen"bietet eine neue Dimension, die die seit einem bestimmten Vorfall verstrichene Zeit misst.
SQL
select * from (
select --aepTenantId--.identification.core.ecid as ecid,
web.webPageDetails.name as webPage,
TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Call Start', 'seconds')
OVER(PARTITION BY --aepTenantId--.identification.core.ecid
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS contact_callcenter_after_seconds
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and web.webPageDetails.name in ('Cancel Service', 'Call Start')
) r
where r.webPage = 'Cancel Service'
limit 15;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Ergebnis der Abfrage
ecid | webPage | contact_callcenter_after_seconds
----------------------------------+----------------+----------------------------------
00331886620679939148047665693117 | Cancel Service |
00626561600197295782131349716866 | Cancel Service |
00630470663554417679969244202779 | Cancel Service | -797
00720875344152796154458668700428 | Cancel Service | -519
00746064605049656090779523644276 | Cancel Service | -62
00762093837616944422322357210965 | Cancel Service |
00767875779073091876070699689209 | Cancel Service |
00798691264980137616449378075855 | Cancel Service |
00869613691740150556826953447162 | Cancel Service | -129
00943638725078228957873279219207 | Cancel Service | -750
01167540466536077846425644389346 | Cancel Service |
01412448537869549016063764484810 | Cancel Service |
01419076946514450291741574452702 | Cancel Service | -482
01533124771963987423015507880755 | Cancel Service |
01710651086750904478559809475925 | Cancel Service |
(15 rows)
Und was ist das Ergebnis dieses Kontakts?
Erklären Sie, dass wir Datasets zusammenführen, in diesem Fall fügen wir unserem demo_system_event_dataset_for_website_global_v1_1 mit demo_system_event_dataset_for_call_center_global_v1_1 bei. Wir machen dies, um das Ergebnis der Interaktion zwischen Call-Center zu erfahren.
SQL
select distinct r.*,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callFeeling,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callContractCancelled
from (
select --aepTenantId--.identification.core.ecid ecid,
web.webPageDetails.name as webPage,
TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Call Start', 'seconds')
OVER(PARTITION BY --aepTenantId--.identification.core.ecid
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS contact_callcenter_after_seconds
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and web.webPageDetails.name in ('Cancel Service', 'Call Start')
) r
, demo_system_event_dataset_for_call_center_global_v1_1 c
where r.ecid = c.--aepTenantId--.identification.core.ecid
and r.webPage = 'Cancel Service'
and c.--aepTenantId--.interactionDetails.core.callCenterAgent.callContractCancelled IN (true,false)
and c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic IN ('contract', 'invoice','complaint','wifi')
limit 15;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
In dieser Abfrage führen wir Treuedaten zusammen, die wir in Adobe Experience Platform eingeführt haben. Dadurch können Sie die Analyse mit Kehrtdaten bereichern.
SQL
select r.*,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callFeeling,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic,
l.--aepTenantId--.loyaltyDetails.level,
l.--aepTenantId--.identification.core.loyaltyId
from (
select --aepTenantId--.identification.core.ecid ecid,
web.webPageDetails.name as webPage,
TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Call Start', 'seconds')
OVER(PARTITION BY --aepTenantId--.identification.core.ecid
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS contact_callcenter_after_seconds
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and web.webPageDetails.name in ('Cancel Service', 'Call Start')
) r
, demo_system_event_dataset_for_call_center_global_v1_1 c
, demo_system_profile_dataset_for_loyalty_global_v1_1 l
where r.ecid = c.--aepTenantId--.identification.core.ecid
and r.webPage = 'Cancel Service'
and l.--aepTenantId--.identification.core.ecid = r.ecid
and c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic IN ('contract', 'invoice','complaint','wifi','promo')
limit 15;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
Lassen Sie uns die von der Adobe Experience Platform erfassten geografischen Informationen, wie Längengrad, Einstellung, Stadt, Landschaftscode, einbeziehen, um einige geografische Einblicke zu gewinnen, die Kunden in die Kasse führen.
SQL
select distinct r.ecid,
r.city,
r.countrycode,
r.lat as latitude,
r.lon as longitude,
r.contact_callcenter_after_seconds as seconds_to_contact_callcenter,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callFeeling,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callContractCancelled,
l.--aepTenantId--.loyaltyDetails.level,
l.--aepTenantId--.identification.core.loyaltyId
from (
select --aepTenantId--.identification.core.ecid ecid,
placeContext.geo._schema.latitude lat,
placeContext.geo._schema.longitude lon,
placeContext.geo.city,
placeContext.geo.countryCode,
web.webPageDetails.name as webPage,
TIME_BETWEEN_NEXT_MATCH(timestamp, web.webPageDetails.name='Call Start', 'seconds')
OVER(PARTITION BY --aepTenantId--.identification.core.ecid
ORDER BY timestamp
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS contact_callcenter_after_seconds
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and web.webPageDetails.name in ('Cancel Service', 'Call Start')
) r
, demo_system_event_dataset_for_call_center_global_v1_1 c
, demo_system_profile_dataset_for_loyalty_global_v1_1 l
where r.ecid = c.--aepTenantId--.identification.core.ecid
and r.webPage = 'Cancel Service'
and l.--aepTenantId--.identification.core.ecid = r.ecid
and c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic IN ('contract', 'invoice','complaint','wifi','promo')
limit 15;
Kopieren Sie die oben stehende Anweisung und führen Sie sie in Ihrer PSQL-Befehlszeilenschnittstelle aus.
In den obigen Abfragen haben wir uns nur die Besucher angesehen, die im Falle einer Serviceabsage das Call Center kontaktiert haben. Wir möchten dies etwas weiter gehen und alle Interaktionen zwischen Call-Center berücksichtigen, einschließlich (WiFi, Promo, Rechnung, Beschwerde und Vertrag).
Sie müssen eine Abfrage bearbeiten. Öffnen Sie also zuerst das Notebook oder die Klammern.
Klicken Sie unter Windows auf "search"-icon (1) in der Windows-Symbolleiste, geben Sie notepad in das Feld "search"(2) ein und klicken Sie auf (3) das Ergebnis "notepad":
Unter macOS
Kopieren Sie die folgende Anweisung in die Klammern:
select /* enter your name */
e.--aepTenantId--.identification.core.ecid as ecid,
e.placeContext.geo.city as city,
e.placeContext.geo._schema.latitude latitude,
e.placeContext.geo._schema.longitude longitude,
e.placeContext.geo.countryCode as countrycode,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callFeeling as callFeeling,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic as callTopic,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callContractCancelled as contractCancelled,
l.--aepTenantId--.loyaltyDetails.level as loyaltystatus,
l.--aepTenantId--.loyaltyDetails.points as loyaltypoints,
l.--aepTenantId--.identification.core.loyaltyId as crmid
from demo_system_event_dataset_for_website_global_v1_1 e
,demo_system_event_dataset_for_call_center_global_v1_1 c
,demo_system_profile_dataset_for_loyalty_global_v1_1 l
where e.--aepTenantId--.demoEnvironment.brandName IN ('Luma Telco', 'Citi Signal')
and e.web.webPageDetails.name in ('Cancel Service', 'Call Start')
and e.--aepTenantId--.identification.core.ecid = c.--aepTenantId--.identification.core.ecid
and l.--aepTenantId--.identification.core.ecid = e.--aepTenantId--.identification.core.ecid;
und ersetzen
enter your name
Entfernen Sie nicht /\* und \*/. Die geänderte Anweisung in Notizblock sollte wie folgt aussehen:
Kopieren Sie die geänderte Anweisung von notepad in das Befehlszeilenfenster PSQL und drücken Sie die Eingabetaste. Das folgende Ergebnis sollte im Fenster der PSQL-Befehlszeile angezeigt werden:
Im nächsten Schritt werden Sie Ihre Abfrage (auch bekannt als Tabelle erstellen als select oder CTAS) als neuen Datensatz beibehalten, den Sie in Microsoft Power BI verwenden werden.
