2.1.4 Consultas, consultas, consultas, análisis de pérdida
Objetivo
- Escribir consultas para análisis de datos
- Escribir consultas SQL que combinen datos en línea, de centro de llamadas y de fidelidad disponibles en Adobe Experience Platform.
- Obtenga información acerca de las funciones definidas por Adobe
Contexto
En estos ejercicios escribirá consultas para analizar vistas de productos, canales de productos, pérdidas, etc.
Todas las consultas enumeradas en este capítulo se ejecutarán en la interfaz de línea de comandos PSQL. Debe copiar (CTRL-c) los bloques de instrucciones indicados con SQL y pegarlos (CTRL-v) en la interfaz de línea de comandos de PSQL. Los bloques Resultado de la consulta muestran la instrucción SQL pegada y el resultado de la consulta asociado.
Escribir consultas básicas para el análisis de datos
Marca de tiempo
Los datos capturados en Adobe Experience Platform están marcados con la hora. El atributo timestamp le permite analizar datos a lo largo del tiempo.
¿Cuántas vistas de productos tenemos diariamente?
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 ('Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
tech-insiders:all=> select date_format( timestamp , 'yyyy-MM-dd') AS Day,
count(*) AS productViews
from demo_system_event_dataset_for_website_global_v1_1
where _experienceplatform.demoEnvironment.brandName IN ('Citi Signal')
and eventType = 'commerce.productViews'
group by Day
limit 10;
Day | productViews
------------+--------------
2024-12-04 | 2297
(1 row)
Principales 5 productos vistos
¿Cuáles son los 5 productos más visitados?
SQL
select productListItems.name, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Citi Signal')
and eventType = 'commerce.productViews'
group by productListItems.name
order by 2 desc
limit 5;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
tech-insiders:all=> select productListItems.name, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where _experienceplatform.demoEnvironment.brandName IN ('Citi Signal')
and eventType = 'commerce.productViews'
group by productListItems.name
order by 2 desc
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)
Canal de interacción de productos, desde la visualización hasta la compra
SQL
select eventType, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Citi Signal')
and eventType is not null
and eventType <> ''
group by eventType;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
tech-insiders:all=> select eventType, count(*)
from demo_system_event_dataset_for_website_global_v1_1
where _experienceplatform.demoEnvironment.brandName IN ('Citi Signal')
and eventType is not null
and eventType <> ''
group by eventType;
eventType | count(1)
--------------------------+----------
commerce.productListAdds | 494
commerce.purchases | 246
commerce.productViews | 2297
(3 rows)
Identificar visitantes con riesgo de pérdida (página de visita => Cancelar servicio)
SQL
select distinct --aepTenantId--.identification.core.ecid
from demo_system_event_dataset_for_website_global_v1_1
where --aepTenantId--.demoEnvironment.brandName IN ('Citi Signal')
and web.webPageDetails.name = 'Cancel Service'
group by --aepTenantId--.identification.core.ecid
limit 10;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
tech-insiders:all=> select distinct _experienceplatform.identification.core.ecid
from demo_system_event_dataset_for_website_global_v1_1
where _experienceplatform.demoEnvironment.brandName IN ('Citi Signal')
and web.webPageDetails.name = 'Cancel Service'
group by _experienceplatform.identification.core.ecid
limit 10;
ecid
----------------------------------
86069928882940477620713284798772
75691756152042231410852704832434
47381264398548915586824480724480
51294194577949645447313762862726
95873885060131472480685538836534
71192995127345419624952514250737
81469709164961922907426138040032
53545252726821876244061095202780
13294750130353985087337266864522
58843891994459565443501421307174
(10 rows)
En el siguiente conjunto de consultas ampliaremos la consulta anterior, con el fin de obtener una vista completa de los clientes y su comportamiento que han estado visitando la página "Cancelar servicio". Aprenderá a utilizar la función definida por Adobe para crear sesiones de información, identificar la secuencia y el tiempo de los eventos. También unirá conjuntos de datos para enriquecer y preparar aún más los datos para su análisis en Microsoft Power BI.
Consultas avanzadas
La mayor parte de la lógica empresarial requiere reunir los puntos de contacto de un cliente y pedirlos por tiempo. Spark SQL proporciona esta compatibilidad en forma de funciones de ventana. Las funciones de ventana forman parte de SQL estándar y son compatibles con muchos otros motores SQL.
Funciones definidas por Adobe
Adobe ha agregado un conjunto de funciones definidas por Adobe a la sintaxis SQL estándar que le permite comprender mejor sus datos de experiencia. En las próximas consultas aprenderá sobre estas funciones de ADF. Puede encontrar más información y la lista completa en la documentación.
¿Qué hacen las personas en el sitio antes de llegar a la página "Cancelar servicio" como la tercera página de una sesión?
Con esta consulta descubrirá las dos primeras funciones definidas por Adobe SESS_TIMEOUT y NEXT
El SESS_TIMEOUT() reproduce las agrupaciones de visitas que se encontraron con Adobe Analytics. Realiza una agrupación similar basada en el tiempo, pero parámetros personalizables.
NEXT() y PREVIOUS() le ayudan a comprender cómo navegan los clientes por su sitio.
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 ('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;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
webPage | webPage_2 | webPage_3 | webPage_4 | journeys
---------------------------------------+---------------------------------------+----------------+------------+----------
Telco Home | Citi Signal Sport | Cancel Service | Call Start | 2
Citi Signal Sport | Google Pixel XL 32GB Black Smartphone | Cancel Service | Call Start | 2
Broadband Deals | Samsung Galaxy S7 32GB Black | Cancel Service | | 2
TV & Broadband Deals | Samsung Galaxy S7 32GB Black | Cancel Service | | 2
SIM Only | Citi Signal Shop | Cancel Service | | 2
Google Pixel XL 32GB Black Smartphone | Broadband Deals | Cancel Service | | 2
SIM Only | Telco Home | Cancel Service | | 2
Citi Signal Shop | Samsung Galaxy S7 32GB Black | Cancel Service | Call Start | 1
Google Pixel XL 32GB Black Smartphone | Citi Signal Sport | Cancel Service | Call Start | 1
Google Pixel XL 32GB Black Smartphone | Citi Signal Shop | Cancel Service | Call Start | 1
(10 rows)
¿Cuánto tiempo tenemos antes de que un visitante llame al centro de llamadas después de visitar la página "Cancelar servicio"?
Para responder a este tipo de consulta, se usará la función definida por Adobe TIME_BETWEEN_NEXT_MATCH().
El tiempo entre las funciones de coincidencia anterior o siguiente proporciona una nueva dimensión, que mide el tiempo transcurrido desde un incidente en particular.
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 ('Citi Signal')
and web.webPageDetails.name in ('Cancel Service', 'Call Start')
) r
where r.webPage = 'Cancel Service'
limit 15;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
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)
¿Y cuál es el resultado de ese contacto?
En esta consulta, combinará conjuntos de datos. En este caso, se unirá al conjunto de datos demo_system_event_dataset_for_website_global_v1_1 con el conjunto de datos demo_system_event_dataset_for_call_center_global_v1_1. Esto se hace para comprender el resultado de la interacción del centro de llamadas.
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 ('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;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
ecid | webPage | contact_callcenter_after_seconds | callfeeling | calltopic | callcontractcancelled
----------------------------------+----------------+----------------------------------+-------------+-----------+-----------------------
00630470663554417679969244202779 | Cancel Service | -797 | negative | contract | f
00720875344152796154458668700428 | Cancel Service | -519 | positive | contract | f
00746064605049656090779523644276 | Cancel Service | -62 | positive | contract | t
00869613691740150556826953447162 | Cancel Service | -129 | negative | contract | t
00943638725078228957873279219207 | Cancel Service | -750 | positive | contract | f
01419076946514450291741574452702 | Cancel Service | -482 | neutral | contract | f
01738842540109643781526526573341 | Cancel Service | -562 | neutral | contract | f
02052460258994877317679083617975 | Cancel Service | -545 | neutral | contract | f
02156496759733199802585567179589 | Cancel Service | -83 | neutral | contract | t
02666934104296797891818818456669 | Cancel Service | -297 | positive | contract | t
03059764265715537001416957172652 | Cancel Service | -243 | negative | contract | t
03347899869945278660479273416679 | Cancel Service | -229 | positive | contract | t
04258863338643046907489131372300 | Cancel Service | -588 | positive | contract | f
04733864373954008966920919247566 | Cancel Service | -795 | neutral | contract | f
05199871096822598772351169572451 | Cancel Service | -236 | positive | contract | t
(15 rows)
¿Cuál es el perfil de fidelidad de estos clientes?
En esta consulta, unimos datos de CRM incorporados en Adobe Experience Platform. Esto permite enriquecer el análisis de pérdida con datos CRM.
SQL
select r.*,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callFeeling,
c.--aepTenantId--.interactionDetails.core.callCenterAgent.callTopic,
l.--aepTenantId--.loyaltyDetails.level,
l.--aepTenantId--.identification.core.crmId
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 ('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_crm_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;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
ecid | webPage | contact_callcenter_after_seconds | callfeeling | calltopic | level | crmid
----------------------------------+----------------+----------------------------------+-------------+-----------+--------+-----------
00630470663554417679969244202779 | Cancel Service | -797 | negative | contract | Bronze | 524483285
00720875344152796154458668700428 | Cancel Service | -519 | positive | contract | Silver | 860696333
00746064605049656090779523644276 | Cancel Service | -62 | positive | contract | Bronze | 072387270
00869613691740150556826953447162 | Cancel Service | -129 | negative | contract | Bronze | 789347684
00943638725078228957873279219207 | Cancel Service | -750 | positive | contract | Gold | 033926162
01419076946514450291741574452702 | Cancel Service | -482 | neutral | contract | Bronze | 105063634
01738842540109643781526526573341 | Cancel Service | -562 | neutral | contract | Gold | 791324509
02052460258994877317679083617975 | Cancel Service | -545 | neutral | contract | Gold | 443477555
02156496759733199802585567179589 | Cancel Service | -83 | neutral | contract | Silver | 305085589
02666934104296797891818818456669 | Cancel Service | -297 | positive | contract | Silver | 104266570
03059764265715537001416957172652 | Cancel Service | -243 | negative | contract | Silver | 814175245
03347899869945278660479273416679 | Cancel Service | -229 | positive | contract | Gold | 377699708
04258863338643046907489131372300 | Cancel Service | -588 | positive | contract | Silver | 298321657
04733864373954008966920919247566 | Cancel Service | -795 | neutral | contract | Gold | 655070958
05199871096822598772351169572451 | Cancel Service | -236 | positive | contract | Gold | 425688874
(15 rows)
¿De qué región nos visitan?
Incluyamos información geográfica, como longitud, actitud, ciudad, código de país, capturada por Adobe Experience Platform para obtener información geográfica sobre la pérdida de clientes.
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.crmId
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 ('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_crm_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;
Copie la instrucción anterior y ejecútela en la interfaz de línea de comandos PSQL.
Resultado de la consulta
ecid | city | countrycode | latitude | longitude | seconds_to_contact_callcenter | callfeeling | calltopic | callcontractcancelled | level | crmid
----------------------------------+------------+-------------+------------+------------+-------------------------------+-------------+-----------+-----------------------+--------+-----------
00630470663554417679969244202779 | Charlton | GB | 51.59119 | -1.407848 | -797 | negative | contract | f | Bronze | 524483285
00720875344152796154458668700428 | Ashley | GB | 51.4139633 | -2.2685462 | -519 | positive | contract | f | Silver | 860696333
00746064605049656090779523644276 | Liverpool | GB | 53.4913801 | -2.867264 | -62 | positive | contract | t | Bronze | 072387270
00869613691740150556826953447162 | Langley | GB | 51.888151 | -0.23924 | -129 | negative | contract | t | Bronze | 789347684
00943638725078228957873279219207 | Eaton | GB | 53.2945961 | -0.9335791 | -750 | positive | contract | f | Gold | 033926162
01419076946514450291741574452702 | Tullich | GB | 57.4694803 | -3.1269422 | -482 | neutral | contract | f | Bronze | 105063634
01738842540109643781526526573341 | Whitwell | GB | 54.3886617 | -1.555363 | -562 | neutral | contract | f | Gold | 791324509
02052460258994877317679083617975 | Edinburgh | GB | 55.9309486 | -3.1859102 | -545 | neutral | contract | f | Gold | 443477555
02156496759733199802585567179589 | West End | GB | 53.46464 | 0.04134 | -83 | neutral | contract | t | Silver | 305085589
02666934104296797891818818456669 | Newtown | GB | 51.3684218 | -1.3218754 | -297 | positive | contract | t | Silver | 104266570
03059764265715537001416957172652 | Edinburgh | GB | 55.9309486 | -3.1859102 | -243 | negative | contract | t | Silver | 814175245
03347899869945278660479273416679 | Liverpool | GB | 53.4913801 | -2.867264 | -229 | positive | contract | t | Gold | 377699708
04258863338643046907489131372300 | Norton | GB | 52.2679288 | -1.1202549 | -588 | positive | contract | f | Silver | 298321657
04733864373954008966920919247566 | Whitchurch | GB | 51.4057505 | -2.5573746 | -795 | neutral | contract | f | Gold | 655070958
05199871096822598772351169572451 | Stapleford | GB | 53.10672 | -0.687802 | -236 | positive | contract | t | Gold | 425688874
(15 rows)
Análisis de interacción del centro de llamadas
En las consultas anteriores solo observamos a los visitantes que terminaban contactando con el centro de llamadas en caso de cancelación del servicio. Queremos tener esto un poco más amplio y tener en cuenta toda la interacción del centro de llamadas, incluyendo (wifi, promoción, factura, queja y contrato).
Tendrá que editar una consulta, así que primero vamos a abrir el bloc de notas o los corchetes.
En Windows, haga clic en el icono "buscar" (1) en la barra de herramientas de Windows, escriba notepad en el campo "buscar" (2), haga clic en (3) el resultado "notepad":
En Mac
Copie la siguiente instrucción en bloc de notas/corchetes:
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.crmId 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_crm_global_v1_1 l
where e.--aepTenantId--.demoEnvironment.brandName IN ('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;
Y reemplace
enter your name
No quite /\* ni \*/. La instrucción modificada en el bloc de notas debería tener el siguiente aspecto:
Copie la instrucción modificada de notepad en la ventana de línea de comandos PSQL y presione Intro. Debería ver el siguiente resultado en la ventana de línea de comandos PSQL:
tech-insiders:all=> select /* vangeluw */
e._experienceplatform.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._experienceplatform.interactionDetails.core.callCenterAgent.callFeeling as callFeeling,
c._experienceplatform.interactionDetails.core.callCenterAgent.callTopic as callTopic,
c._experienceplatform.interactionDetails.core.callCenterAgent.callContractCancelled as contractCancelled,
l._experienceplatform.loyaltyDetails.level as loyaltystatus,
l._experienceplatform.loyaltyDetails.points as loyaltypoints,
l._experienceplatform.identification.core.crmId 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_crm_global_v1_1 l
where e._experienceplatform.demoEnvironment.brandName IN ('Citi Signal')
and e.web.webPageDetails.name in ('Cancel Service', 'Call Start')
and e._experienceplatform.identification.core.ecid = c._experienceplatform.identification.core.ecid
and l._experienceplatform.identification.core.ecid = e._experienceplatform.identification.core.ecid;
ecid | city | latitude | longitude | countrycode | callFeeling | callTopic | contractCancelled | loyaltystatus | loyaltypoints | crmid
----------------------------------+------------+------------+------------+-------------+-------------+-----------+-------------------+---------------+---------------+-----------
60082543727227001177187726544992 | Normanton | 52.643749 | -0.622129 | GB | neutral | contract | f | Bronze | 430.0 | 117969439
03250145103029549687906576330844 | Charlton | 51.59119 | -1.407848 | GB | none | none | f | Silver | 585.0 | 271570836
87322786414150971711720565798532 | Whitwell | 54.3886617 | -1.555363 | GB | none | none | f | Bronze | 872.0 | 570762160
46736059905281823751180777497223 | Edinburgh | 55.9309486 | -3.1859102 | GB | none | none | f | Gold | 482.0 | 980678773
81958524709959359235057647680790 | Linton | 54.0542238 | -2.0215836 | GB | none | none | f | Bronze | 666.0 | 341873673
24854602977644353049269284436324 | Tullich | 57.4694803 | -3.1269422 | GB | negative | contract | f | Bronze | 418.0 | 831581327
24854602977644353049269284436324 | Tullich | 57.4694803 | -3.1269422 | GB | negative | contract | f | Bronze | 418.0 | 831581327
En el siguiente paso, mantendrá su consulta (también conocida como crear tabla como seleccionar o CTAS) como un nuevo conjunto de datos que utilizará en Microsoft Power BI.
Pasos siguientes
Ir a 2.1.5 Generar un conjunto de datos a partir de una consulta
Volver a servicio de consultas
Volver a Todos los módulos