Expand my Community achievements bar.

Announcement: Calling all learners and mentors! Applications are now open for the Adobe Analytics 2024 Mentorship Program! Come learn from the best to prepare for an official certification in Adobe Analytics.

Application complete time measure with clickstream query

Avatar

Level 1

I want to get the same result from clickstream query with workspace "Application Time To Complete"

Ran below query but found there's big gap between them. please review my code whether it's correctly written.

SELECT a.as_of_dt,

a.category,

a.product,

a.product1,

        sum(a.complete_time) AS complete_time,

count(*) AS complete_cnt

FROM

  (SELECT as_of_dt,

split_part(product_list, ';', 1) AS category,

CASE

WHEN split_part(product_list, ';', 1) IN ('ABC',

'DEF',

'XYZ') THEN split_part(product_list, ';', 1)

ELSE split_part(product_list, ';', 2)

END AS product,

split_part(product_list, ';', 2) AS product1,

CASE

WHEN substr(post_evar44, 1, 3) = 'iOS' THEN substr(post_evar57, 1, 6)

ELSE ltrim(split_part(split_part(user_agent, ';', 3), 'Build', 1))

END AS device,

post_evar57 AS mobile_os,

CASE

WHEN translate(post_prop15, 'abcdefghijklmnopqrstuvwxyz', '') = '' THEN 0

ELSE cast(translate(post_prop15, 'abcdefghijklmnopqrstuvwxyz', '') AS DOUBLE)

END AS complete_time

   FROM eap_sitecat_hit_data_mob

   WHERE as_of_dt LIKE '2019-09%'

     AND split_part(product_list, ';', 1) IN ('AB',

'CD',

'EF',

'GH',

'IJ',

'KL')

     AND concat(',',event_list,',') LIKE '%,206,%'

     AND exclude_hit = '0'

     AND hit_source NOT IN ('5',

'8',

'9')) AS a

GROUP BY a.as_of_dt,

a.category,

a.product,

a.product1,

ORDER BY count(*) DESC

0 Replies