ניתוח דפי נחיתה באתר על ידי חישוב “שיעור הנטישה” עוזר לנו לשפר את כמות המבקרים שעוברים בכל משפך השיווק שלנו עד לרכישה. הניתוח עוזר לנו לזהות נקודות כשל או צוואר בקבוק שפוגעים ביחס ההמרה של האתר.
הניתוח עוזר לנו גם לתכנן מבחני A/B על הטראפיק הנכנס, לבחור את דפי הנחיתה היעילים והאפקטיביים ביותר, והכל על מנת לשפר את יחס הנטישה ויחס ההמרה.
את החישוב נבצע על מסד נתונים חינמי מהרשת.
נציג את הטבלאות בהן נשתמש לחישוב יחס הנטישה:
טבלת website_sessions – המוכרת לנו מהפוסט הקודם בסדרה, מתעדת את מקורות התנועה של המבקרים באתר. מאיזה אתר מפנה, שם קמפיין השיווק, המקור השיווקי, סוג המכשיר בו השתמשו וכו’ (ניתן ללחוץ על התמונה להגדלה).
הטבלה השנייה – website_pageviews, שגם הוצגה בפוסט קודם, אוספת את המידע הבא: בכל session, מהם הדפים בהם ביקר משתמש הקצה באתר (ניתן ללחוץ על התמונה להגדלה).
מציאת תאריך ההשקה של דף הנחיתה
נניח שיש לנו דף בית, שהיה קיים מתחילת פעילות האתר, ודף נחיתה שעלה לאוויר בהמשך. ואנחנו רוצים להשוות שיעור נטישה ביניהם.
כפי שהוסבר בפוסטים קודמים השוואה בין דפים חייבת להתבצע בחלון הזמן הקטן ביותר בו היה אחד הדפים “באוויר”. אם דף נחיתה אחד היה באוויר חודש ודף נחיתה אחר היה באוויר שבועיים, אין טעם לבצע השוואה על פני חודש כי ההשוואה אינה הוגנת. יש לבצע את הבדיקה בחלון הזמן הקצר יותר, ולכן צריך לאתר את תאריך העליה לאוויר של דף הנחיתה (ניתן ללחוץ על התמונה להגדלה):
אוקיי, אז עכשיו אנחנו יודעים באיזה תאריך דף הנחיתה עלה לאוויר, נשתמש ב pageview_id = ‘23504’ כתאריך החתך ממנו נתחיל את הבדיקה.
ניתוח יחס נטישה – סדר פעולות
על מנת להשוות יחס נטישה ב SQL, יש לבצע את השלבים הבאים:
1) לזהות את דף האינטרנט pageview_url הראשון בכל ביקור session, ולהתמקד בדפי הנחיתה העומדים להשוואה.
2) לחשב את מספר הדפים באתר pageview שכל מבקר ראה במהלך ביקור session, ולהתמקד רק בביקורים בהם נצפה דף אינטרנט אחד בלבד. זה בעצם נותן לנו את מספר הביקורים בהם בוצעה “נטישה”.
3) לחשב לכל דף נחיתה את מספר הביקורים בהם בוצעה נטישה – כמות הנטישות לדף נחיתה.
4) לחלק את כמות הנטישות בסך הביקורים בדף – אז מקבלים את יחס הנטישה לדף.
מדובר בשאילתה מורכבת ולכן אני אעשה שימוש ב – CTE – Common Table Expression.
SET @FROM_DATE = '23504';
SET @TO_DATE = '2012-07-28' ;
SET @PAGE1 = '/home', @PAGE2 = '/lander-1';
WITH
cte_sessions
as
(select
website_pageviews.website_session_id
,MIN(website_pageviews.website_pageview_id) AS min_pageview_id
,pageview_url AS landing_page
FROM website_pageviews
INNER JOIN website_sessions
ON website_sessions.website_session_id = website_pageviews.website_session_id
AND website_sessions.created_at < @TO_DATE
AND website_pageviews.website_pageview_id > @FROM_DATE
AND website_pageviews.pageview_url IN(@PAGE1, @PAGE2)
GROUP BY website_pageviews.website_session_id
),
bounced_sessions
AS
(SELECT
sub_A.website_session_id
,sub_A.landing_page
from(
select
website_pageviews.website_session_id
,MIN(website_pageviews.website_pageview_id) AS min_pageview_id
,pageview_url AS landing_page
FROM website_pageviews
INNER JOIN website_sessions
ON website_sessions.website_session_id = website_pageviews.website_session_id
AND website_sessions.created_at < @TO_DATE
AND website_pageviews.website_pageview_id > @FROM_DATE
AND website_pageviews.pageview_url IN(@PAGE1, @PAGE2)
GROUP BY website_pageviews.website_session_id
) AS sub_A
JOIN website_pageviews
ON website_pageviews.website_session_id = sub_A.website_session_id
GROUP BY
sub_A.website_session_id,
sub_A.landing_page
HAVING
COUNT( website_pageviews.website_pageview_id) = 1)
SELECT
cte_sessions.landing_page
,COUNT(DISTINCT cte_sessions.website_session_id) AS sessions
,COUNT(DISTINCT bounced_sessions.website_session_id) AS bounced_sessions
,COUNT(DISTINCT bounced_sessions.website_session_id) / COUNT(DISTINCT cte_sessions.website_session_id) AS bounce_rats
FROM cte_sessions
LEFT JOIN bounced_sessions
ON cte_sessions.website_session_id = bounced_sessions.website_session_id
GROUP BY landing_page
;
בחלק הראשון – cte_sessions – פקודת MIN , תפקידה למיין מתוך עמודת website_pageview_id, ולהשאיר רק את מפתחות הדפים שעלו ראשונים בכל ביקור של גולש. העובדה שעמודת website_pageview_id ממספרת את תנועות הלקוחות באופן אוטומטי תעזור לנו להקל על השרת, שעובד יעיל ומהר יותר כאשר הוא מתבקש לחשב מספר שלם בעמודת website_session_id לעומת תאריך בעמודת created_at.
בנוסף אנחנו מתמקדים רק בשני דפים: “home” ו – “lander-1”.
חלון הזמן הוא תאריך החתך שמצאנו קודם ועד תאריך אקראי מאוחר יותר ‘2012-07-28’ .
הנה התוצאה של ה- CTE הראשונה (ניתן ללחוץ על התמונה להגדלה):
חלק שני – רק הביקורים – sessions – בהם עזבו לאחר דף אחד בלבד:
אנחנו נגדיר “נטישה” אם הגולש עזב לאחר שראה דף אחד בלבד באתר. ולכן ניקח את התוצאה שקיבלנו עד עכשיו ונמיין מתוכה רק את הביקורים שהסתיימו לאחר דף אינטרנט אחד. נעשה זאת על ידי ספירה של כמות הדפים לביקור ואז שימוש ב HAVING למיון פנימה רק של הביקורים שהסתיימו בדף האינטרנט הראשון . המשמעות היא שהגולש ראה את דף הנחיתה, לא השתכנע (או לפחות לא כרגע), ונטש את האתר.
החלק הזה – bounced_sessions – חושב באמצעות שילוב של CTE + Subquery.
חלק שלישי ואחרון – חישוב שיעור הנטישה לכל דף בנפרד:
השאילתה האחרונה מרכזת את שתי ה CTE’S. סופרת את מספר הביקורים בהם הייתה נטישה bounced_sessions. ואת סך הביקורים sessions באתרים הנבחנים. ומחלקת אותם אחד בשני לקבל את שיעור הביקורים בהם הייתה נטישה של גולשים – bounce_rats:
והנה התוצאה הסופית (ניתן ללחוץ על התמונה להגדלה):
ניתן לראות שדף הנחיתה מציג שיעור נטישה נמוך (53%) יחסית לדף הבית (58%), שזה אומר שדף הנחיתה כנראה עושה את העבודה.