SQL ניתוח שיעור הקלקה Click Through Rate

שיעור ההקלקה (Click Through Rate) הוא KPI חשוב לצורך עמידת האפקטיביות של קמפיין שיווק. שיעור ההקלקה חשוב גם לצורך ביצוע מבחני A/B לצורך בחינת אופטימיזציה של דפים באתר.

שיעור הקלקה מחושב כיחס בין מספר הגולשים אשר הקליקו על דף אינטרנט/מודעה לבין מספר הגולשים שנחשפו אל הדף האינטרנט.

הצעדים הנדרשים לחישוב “שיעור הקלקה” clickthrough rates בעזרת SQL :

1. לאתר את כל הדפים שנצפו pageviews במהלך ביקור session.

2. לזהות כל דף עם השלב המתאים במשפך השיווק.

3. נמפה עד להיכן הגיע כל מבקר במשפך השיווק.

4. נחשב את שיעור ההקלקה אל כל דף במשפך.

נציג את הטבלאות בהן נשתמש:

טבלת website_sessions – היא אותה טבלה מהפוסט הקודם בסדרה, מתעדת את מקורות התנועה של המבקרים באתר. מאיזה אתר מפנה, שם קמפיין השיווק, המקור השיווקי, סוג המכשיר בו השתמשו וכו’.

נציג את הטבלאות בהן נשתמש:

טבלת website_sessions – היא אותה טבלה מהפוסט הקודם בסדרה, מתעדת את מקורות התנועה של המבקרים באתר. מאיזה אתר מפנה, שם קמפיין השיווק, המקור השיווקי, סוג המכשיר בו השתמשו וכו’ (ניתן ללחוץ על התמונה להגדלה).

הטבלה השנייה – website_pageviews

אוספת את המידע הבא: בכל session, מהם הדפים בהם ביקר משתמש הקצה באתר.

לדוגמא, session 6 (מסומן באדום), ביקר בהתחלה בעמוד הבית, המשיך לעמוד המוצרים, משם לעמוד /the-original-mr-fuzzy וכו’ עד שלבסוף סיים בעמוד /billing (ניתן ללחוץ על התמונה להגדלה).

משפך השיווק

נסתכל על דיאגרמה של משפך השיווק במודל הזה: כל שלב במשפך מיוצג לשם פשטות על ידי דף אחד באתר. ולשם הפשטות נניח שעל מנת להגיע לדף מסויים במשפך חייבים לעבור בדף שקודם לו לפי הסדר הבא (הדף שקודם ל “products” הוא אחד מדפי הנחיתה “landers”) :

השאילתה:

WITH session_level_made_it_flage
AS
(SELECT 
website_session_id,
MAX(product_page) AS product_made_it,
MAX(fuzzy_page) AS fuzzy_made_it,
MAX(cart_page) AS cart_made_it,
MAX(shipping_page) AS shipping_made_it,
MAX(billing_page) AS billing_made_it,
MAX(thankyou_page) AS thankyou_made_it
FROM(
SELECT 
website_sessions.website_session_id,
website_pageviews.pageview_url,
website_pageviews.created_at AS pageview_created_at,
CASE WHEN website_pageviews.pageview_url = '/products' THEN 1 ELSE 0 END AS product_page,
CASE WHEN website_pageviews.pageview_url = '/the-original-mr-fuzzy' THEN 1 ELSE 0 END AS fuzzy_page,
CASE WHEN website_pageviews.pageview_url = '/cart' THEN 1 ELSE 0 END AS cart_page,
CASE WHEN website_pageviews.pageview_url = '/shipping' THEN 1 ELSE 0 END AS shipping_page,
CASE WHEN website_pageviews.pageview_url = '/billing' THEN 1 ELSE 0 END AS billing_page,
CASE WHEN website_pageviews.pageview_url = '/thank-you-for-your-order' THEN 1 ELSE 0 END AS thankyou_page
FROM website_sessions
LEFT JOIN website_pageviews
ON website_sessions.website_session_id = website_pageviews.website_session_id
WHERE website_sessions.created_at > '2012-08-05'
AND website_sessions.created_at < '2012-09-05'
ORDER BY 
website_sessions.website_session_id,
website_pageviews.created_at
) AS pageview_level
GROUP BY 
website_session_id
)

SELECT 
COUNT(DISTINCT website_session_id) AS sessions,
COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT website_session_id) AS landers_clickthrough_rate ,
COUNT(DISTINCT CASE WHEN fuzzy_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN product_made_it = 1 THEN website_session_id ELSE NULL END) AS product_clickthrough_rate,
COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN fuzzy_made_it = 1 THEN website_session_id ELSE NULL END) AS fuzzy_clickthrough_rate,
COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN cart_made_it = 1 THEN website_session_id ELSE NULL END) AS cartg_clickthrough_rate,
COUNT(DISTINCT CASE WHEN billing_made_it= 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN shipping_made_it = 1 THEN website_session_id ELSE NULL END) AS shipping_clickthrough_rate,
COUNT(DISTINCT CASE WHEN thankyou_made_it= 1 THEN website_session_id ELSE NULL END)/COUNT(DISTINCT CASE WHEN billing_made_it = 1 THEN website_session_id ELSE NULL END) AS billing_clickthrough_rate
FROM session_level_made_it_flage
;

הסבר:

במודל הזה אני עושה שימוש בCTE + SUBQUERY.

תת-השאילתה (SUBQUERY) שבנינו נותנת 1 או 0 ברמת הדפים – pageview_url ,כלומר אם הגולש הגיע לדף הזה או לא.

עקרון חשוב בהשוואת שיעור הקלקה בין דפי אינטרנט שונים הוא חלון הזמן בו מבצעים את ההשוואה. אם דף אינטרנט אחד היה באוויר חודשיים ודף אחר היה באוויר חודש, אין טעם לבצע השוואה על פני חודשיים כי ההשוואה אינה הוגנת. יש לבצע את הבדיקה בחלון הזמן הקצר יותר. ולכן גם פה נגדיר חלון זמן.

ה CTE בראש השאילתה  “session_level_made_it_flage”  תתן לנו 1 או 0 ברמת הביקור – website_session_id ,כלומר אם הגולש הגיע לדף הזה במהלך הביקור או לא.

השאילתה הסופית מרכזת את הנתונים, סופרת את כמות הביקורים בכל אתר ומחלקת אותם בכמות הביקורים באתר שקדם לו במשפך, כדי לקבל שיעור הקלקה.

מכיוון שאנחנו מניחים שביקור באתר אחד אומר בהכרח ביקור באתר שממוקם בשלב לפניו במשפך, אנחנו נתייחס ליחס שקיבלנו כשיעור ההקלקה בדף שקודם לו.

תוצאת השאילתה (ניתן ללחוץ על התמונה להגדלה):

ניתן לראות שהדפים שזוכים לשיעור הקלקה נמוך יחסית, ולכן זקוקים לאופטימיזציה, הם:

1. דפי הנחיתה

2. fuzzy

3. billing