Untitled
unknown
plain_text
2 years ago
4.0 kB
4
Indexable
WITH impression_data AS (SELECT DISTINCT visitor_id, TRY_CAST(home_data.field0 AS varchar) AS position, TRY_CAST(json_extract(home_data.field1, '$.component_id') AS varchar) AS component_id, CASE WHEN TRY_CAST(json_extract(home_data.field1, '$.component_id') AS int) IN (7359,7360,7361,7362,7363,7364,7365,7366,7378,7382,7383,7381,7384,7385,7386,7387,7388,7389,7390,7391,7392,7393,7394,7395,7396,7397,7398,7399,7400,7401,8198) THEN 'personalised' ELSE 'non_personalised' END AS personalisation, TRY_CAST(json_extract(home_data.field1, '$.ga_data.info.unique_token') AS varchar) AS unique_token, COALESCE(TRY_CAST(json_extract(home_data.field1, '$.ga_data.variant') AS VARCHAR), TRY_CAST(json_extract(home_data.field1, '$.variant') AS VARCHAR)) AS algo FROM ( select visitor_id,ZIP(SEQUENCE(1, CARDINALITY(json_arr)), json_arr) as widgets_with_position FROM ( SELECT visitor_id, user_journey, array_agg(offset order by (timestamp)) as offset_arr, flatten(array_agg(json_arr order by (timestamp))) as json_arr FROM ( SELECT *, sum( case when offset = 0 then 1 else 0 end) over (partition by visitor_id order by timestamp) user_journey FROM ( SELECT visitor_id, timestamp, CASE WHEN cast(json_extract(uri_args, '$.offset') AS int) IS NULL THEN 0 ELSE cast(json_extract(uri_args, '$.offset') AS int) END as offset, TRY_CAST(json_extract(response_body, '$.data.widgets') AS array(json)) as json_arr FROM nt_landing.raw_logs where year = {eval_year} and month = {eval_month:02d} and day = {eval_day:02d} and uri_categorization='home_page' AND lower(CAST(request_headers as varchar)) LIKE '%"x-platform":"android%' and request_uri IN ('/v9/home_page/initialize' , '/v8/home_page/initialize' , '/v7/home_page/initialize') ) ) group by visitor_id, user_journey order by user_journey ) ) CROSS JOIN UNNEST(widgets_with_position) AS t(home_data) ), click_data as (SELECT clk.visitorid, position, clk.component_id, widget_button,clk.algo FROM (SELECT DISTINCT visitorid, CASE WHEN lower(event) = 'click on widget' THEN 'widget' WHEN lower(event) = 'banner click' THEN 'banner' WHEN lower(event) IN ('medicines', 'lab tests', 'consult doctors', 'health products', 'covid support', 'articles', 'health records', 'pill reminder') THEN 'vertical_entry' END AS widget_button, TRY_CAST(json_extract(properties__label, '$.ga_data.info.component_id') as varchar) AS component_id, TRY_CAST(json_extract(properties__label, '$.ga_data.info.unique_token') as varchar) AS unique_token, COALESCE(TRIM(lower(cast(json_extract(properties__label, '$.variant') as varchar))), TRIM(lower(cast(json_extract(properties__label, '$.ga_data.variant') as varchar)))) as algo FROM nt_stg.pr_rudder_andrd_prod WHERE year = {eval_year} AND month = {eval_month:02d} AND day = {eval_day:02d} AND event_category = 'Homepage' AND lower(event) IN ('click on widget', 'banner click', 'medicines', 'lab tests', 'consult doctors','health products', 'covid support', 'articles', 'health records', 'pill reminder')) clk INNER JOIN impression_data ON clk.component_id = impression_data.component_id AND clk.unique_token = impression_data.unique_token) SELECT DISTINCT 'app' platform,imps.algo, imps.component_id,imps.personalisation, imps.position, imps.impressions, clks.clicks, (100.0*CAST(clks.clicks as double))/CAST(imps.impressions as double) AS positional_ctr FROM (SELECT component_id, position,algo,personalisation, COUNT(DISTINCT visitor_id) impressions FROM impression_data group by 1,2,3,4) imps LEFT JOIN (SELECT component_id, position,algo, COUNT(DISTINCT visitorid) clicks FROM click_data group by 1,2,3) clks ON imps.component_id = clks.component_id AND imps.position = clks.position AND imps.algo = clks.algo
Editor is loading...