7.3 Requêtes, requêtes, requêtes… et analyse d'exécution
Objectif
Requêtes d’écriture pour les analyses de données
Création de requêtes SQL combinant des données en ligne, de centre d'appels et de fidélité disponibles dans Adobe Experience Platform
En savoir plus sur les fonctions définies par Adobe
Contexte
Dans ces exercices, vous écrirez des requêtes pour analyser les vues de produits, les entonnoirs de produits, le déclenchement, etc.
Toutes les requêtes répertoriées dans ce chapitre seront exécutées dans votre interface de ligne de commande PSQL. Vous devez copier (CTRL-c) les blocs d'instructions indiqués par SQL et les coller (CTRL-v) dans l'interface de ligne de commande PSQL. Les blocs Résultat de la Requête affichent l'instruction SQL collée et le résultat de la requête associé.
7.3.1 Rédiger des requêtes de base pour l’analyse des données
Horodatage
Les données capturées dans Adobe Experience Platform sont horodatées. L'attribut timestamp permet d'analyser les données au fil du temps.
Combien de vues de produits avons-nous quotidiennement ?
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Les 5 meilleurs produits consultés
Quels sont les 5 premiers produits affichés ?
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Entonnoir d’interaction de produit, de l’affichage à l’achat
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Identifiez les visiteurs présentant un risque pour Churn (page de visite => Annuler le service).
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Dans le prochain jeu de requêtes, nous allons étendre la requête ci-dessus, afin d'obtenir une vue complète sur les clients et leur comportement qui ont visité la page "Annuler le service". Vous apprendrez à utiliser la fonction définie par Adobe pour segmenter les informations, identifier la séquence et le moment des événements. Vous regrouperez également des jeux de données afin d’enrichir davantage et de préparer les données pour l’analyse dans Microsoft Power BI.
7.3.2 Requêtes avancées
La majeure partie de la logique commerciale requiert la collecte des points de contact pour un client et sa commande par temps. Spark SQL assure cette prise en charge sous forme de fonctions de fenêtre. Les fonctions de fenêtre font partie du langage SQL standard et sont prises en charge par de nombreux autres moteurs SQL.
Fonctions définies par Adobe
Adobe a ajouté un ensemble de fonctions définies par Adobe à la syntaxe SQL standard, ce qui vous permet de mieux comprendre vos données d'expérience. Dans les deux prochaines requêtes, vous apprendrez à propos de ces fonctions du FAD. Vous trouverez plus d'informations et la liste complète dans la documentation.
Que font les visiteurs sur le site avant d'accéder à la page "Annuler le service" en tant que troisième page d'une session ?
Avec cette requête, vous découvrirez les deux premières fonctions définies par Adobe SESS_TIMEOUT et NEXT.
La fonction SESS_TIMEOUT() reproduit les regroupements de visites trouvés avec Adobe Analytics. Il effectue un regroupement temporel similaire, mais des paramètres personnalisables.
NEXT() et PREVIOUS() vous aident à comprendre comment les clients naviguent sur votre site.
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Combien de temps disposons-nous avant qu'un visiteur appelle le centre d'appels après avoir consulté la page "Annuler le service" ?
Pour répondre à ce type de requête, utiliserons la fonction TIME_BETWEEN_NEXT_MATCH() définie par Adobe.
Les fonctions de correspondance précédente ou suivante offrent une nouvelle dimension qui mesure le temps écoulé depuis un incident particulier.
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Résultat de la requête
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)
Quel est le résultat de ce contact ?
Expliquez que nous joignons les ensembles de données, dans ce cas nous joignons notre demo_system_event_dataset_for_website_global_v1_1 à demo_system_event_dataset_for_call_center_global_v1_1. Nous faisons cela pour connaître le résultat de l'interaction du centre d'appels.
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Dans cette requête, nous joignons les données de fidélité que nous avons intégrées à Adobe Experience Platform. Cela permet d'enrichir l'analyse d'entreprise avec des données de fidélité.
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Incluons les informations géographiques, telles que la longitude, l'attitude, la ville, le code de la campagne, capturées par le Adobe Experience Platform afin d'obtenir des informations géographiques sur l'organisation de clients.
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;
Copiez l'instruction ci-dessus et exécutez-la dans votre interface de ligne de commande PSQL.
Dans les requêtes ci-dessus, nous avons seulement examiné les visiteurs qui ont fini par contacter le centre d'appel en cas d'annulation de service. Nous voulons prendre cela un peu plus en compte et prendre en compte toutes les interactions du centre d'appels y compris (wifi, promo, facture, plainte et contrat).
Vous devrez modifier une requête, alors commençons par ouvrir un bloc-notes ou des crochets.
Sous Windows, cliquez sur l'icône de recherche (1) dans la barre d'outils de Windows, tapez bloc-notes dans le champ de recherche (2), cliquez sur (3) le résultat de "bloc-notes" :
Sous Mac
Copiez l'instruction suivante dans le bloc-notes/les crochets :
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;
Et remplacer
enter your name
Ne supprimez pas /\* et \*/. Votre instruction modifiée dans le bloc-notes doit se présenter comme suit :
Copiez votre instruction modifiée de bloc-notes dans la fenêtre de ligne de commande PSQL et appuyez sur Entrée. Vous devriez voir le résultat suivant dans la fenêtre de ligne de commande PSQL :
Dans le prochain, vous conserverez votre requête (également appelée créer la table en sélectionnant ou CTAS) comme un nouveau jeu de données que vous utiliserez dans Microsoft Power BI.
