Customer concerns
Customer think there still have issue after P1 VOD click loss Firewall issue being resolved by upstream tracking team on May 3rd.
select
sent_event_type_desc,
SENT_DT,
COUNT(
DISTINCT CASE
WHEN actn_type = 'SEND' THEN CHNL_TRCKNG_ID
ELSE NULL
END
) AS SENDS,
COUNT(
CASE
WHEN actn_type = 'CLICK' THEN CHNL_TRCKNG_ID
ELSE NULL
END
) CLICKS,
round(clicks / sends * 100, 2) click_rate
from
ACCESS_VIEWS.mh_event a
where
A.ACTN_DT >= '2023-04-15'
and a.level_type in ('CC')
and a.sent_dt >= '2023-04-15'
and upper(a.actn_type) in ('SEND', 'CLICK')
and a.chnl_nm in ('SITE_EMAIL')
and sent_event_type_desc in (11401, 11400, 11412)
GROUP BY 1, 2
Why we are involved in?
- User are querying our table and think data is abnormal.
- Even though customer directly found Tracking team, Tracking team insisted they already fixed VOD issue and suspect it is a reporting issue.
What do we need to do?
Prove it is not a reporting issue.
How can we achive this?
We have 2 directions:
- Since customr are asking upstream tracking team, we only need to prove our data is consistant with upstream.
- Explain the “drops” are reasonable and not an issue.
Obviously the second choice is more abstract and it is supposed to be done by tracking team, so we’d better only to do option #1.
But I want do full deepdive since we are summarizing experiences.
Grouping by action date
The click rate trend grouping by action date is as expected. 27th Apr to 3th May, click has 20% unrecoverable loss due to VOD issue.
select
sent_event_type_desc,
actn_dt,
COUNT(
CASE
WHEN actn_type = 'SEND' THEN MH_EVENT_ID
ELSE NULL
END
) AS SENDS,
COUNT(
CASE
WHEN actn_type = 'CLICK' THEN MH_EVENT_ID
ELSE NULL
END
) CLICKS,
round(clicks/sends * 100, 2) click_rate
from
ACCESS_VIEWS.mh_event a
where
A.ACTN_DT >= '2023-04-15' ------ pls use ACTN_DT instead of sent_dt
and a.level_type in ('CC') -- and a.sent_dt >= '2023-04-15'
and upper(a.actn_type) in ('SEND', 'CLICK')
and a.chnl_nm in ('SITE_EMAIL')
and sent_event_type_desc in (11401, 11400, 11412)
GROUP BY 1, 2
Grouping by sent date
We guess the click rate grouping by sent date drops in recently days is because click and send has max 90 days window, but we just sent them for a few days.
But we need to prove it by exact truth.
From following code and chart whose max sent date is 9th May, we can select sent date and action date diffs, taking 11401 for example:
by chosing 0, the click rate is stable and avg is 8%, approximate to click rate of 9th May
by chosing 1, the click rate is stable and avg is 14%, approximate to click rate of 8th May
…
So the analyst saw drops is reasonable.
select
clicks.sent_event_type_desc,
clicks.actn_dt,
clicks.sent_dt,
datediff(clicks.actn_dt, sends.sent_dt) diff,
clicks.clicks,
sum(CLICKS) over (
partition by clicks.sent_event_type_desc,
clicks.sent_dt
order by clicks.actn_dt
) sum_clicks, sends.sends, round(sum_clicks / sends.sends * 100, 2) click_rate
from
(
select
sent_event_type_desc,
actn_dt,
sent_dt,
count(1) CLICKS
from
ACCESS_VIEWS.mh_event a
where
A.ACTN_DT >= '2023-04-15'
and A.sent_dt >= '2023-04-15'
and a.level_type in ('CC')
and upper(a.actn_type) in ('CLICK')
and a.chnl_nm in ('SITE_EMAIL')
and sent_event_type_desc in (11401, 11400, 11412)
GROUP BY 1, 2, 3
) clicks
join(
select
sent_event_type_desc,
sent_dt,
COUNT(1) AS SENDS
from
ACCESS_VIEWS.mh_event a
where
A.ACTN_DT >= '2023-04-15'
and A.sent_dt >= '2023-04-15'
and a.level_type in ('CC')
and upper(a.actn_type) in ('SEND')
and a.chnl_nm in ('SITE_EMAIL')
and sent_event_type_desc in (11401, 11400, 11412)
GROUP BY 1, 2
) sends on clicks.sent_dt = sends.sent_dt
and clicks.sent_event_type_desc = sends.sent_event_type_desc
Compare with upstream table
I also tried to compare clicks count with upstream table both grouping by action date, the trend matches.
But count diff rate dropped between 2nd May and 7th May then recovered on 9th May, it is weird but abviously not our reporting issue since it back to nornal automatically and I did not spent much time on this.
Keep monitoring is also a good way to saving effort.
select
coalesce(
payload ['typeid'],
case
when sojlib.is_bigint(
sojlib.soj_str_between_endlist(
replace(
coalesce(payload ['emsid'], payload ['sid']),
'null',
''
),
'e',
'.mle'
)
) = 1 then sojlib.soj_str_between_endlist(
replace(
coalesce(payload ['emsid'], payload ['sid']),
'null',
''
),
'e',
'.mle'
)
end
) email_type_id,
case
when action_type = 'CLICK' then payload ['session_start_dt']
else event_dt
end event_dt,
sum(
CASE
WHEN ACTION_TYPE = 'SEND' THEN 1
ELSE 0
END
) AS SENDS,
sum(
CASE
WHEN ACTION_TYPE = 'CLICK' THEN 1
ELSE 0
END
) CLICKS,
round(clicks / sends * 100, 2) click_rate
from
CHOCO_DATA_V.UTP_DAILY a
where
A.EVENT_DT >= '2023-04-15'
and a.payload ['session_start_dt'] >= '2023-04-15'
and upper(a.ACTION_TYPE) in ('SEND', 'CLICK')
and a.CHANNEL_TYPE in ('SITE_EMAIL')
and coalesce(
payload ['typeid'],
case
when sojlib.is_bigint(
sojlib.soj_str_between_endlist(
replace(
coalesce(payload ['emsid'], payload ['sid']),
'null',
''
),
'e',
'.mle'
)
) = 1 then sojlib.soj_str_between_endlist(
replace(
coalesce(payload ['emsid'], payload ['sid']),
'null',
''
),
'e',
'.mle'
)
end
) in (11401, 11400, 11412)
and coalesce(payload ['dup_flag'],0) <> 1
GROUP BY 1, 2
RE_ Still have issue after P1 VOD click loss Firewall issue resolved on May 3rd.eml
View Order Details (VOD) Click Capture Loss Due to Firewall Issue.eml