SQL ניתוח מקורות תנועה Traffic Analysis

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

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

אחד הכלים שעוזרים לנו להתחקות אחר נתיב התנועה של כל לקוח שהגיע אלינו הוא Urchin Tracking Module (UTM). זוהי שיטה טכנית לסימון ערוץ תנועה באופן יחודי שיאפשר לנו לבצע את האנליזה. לא אכנס כאן להסבר על UTM, תוכלו לקרא עליו לעומק בבלוג המצויין והמומלץ של רועי יוסף, לצורך הפוסט הזה חשוב רק שנבין ש UTM מאפשר לנו לדעת מהו ערוץ הטראפיק המקורי ממנו הגיע הלקוח או הליד (גוגל, פייסבוק וכו’), מאיזה קמפיין (בהנחה ויצאנו בכמה סוגי קמפיין) , מאיזה מדיום (למשל שיווק במייל, קליק על באנר וכו’) ועוד ועוד.

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

את הניתוח נעשה, כאמור, ב SQL בפלטפורמת כתיבת השאילתות “MYSQL”

לצורך הניתוח נשתמש בשתי טבלאות מתוך מסד הנתונים (נתונים מומצאים):

טבלת website_sessions , המכילה נתונים לגבי מקורות התנועה לאתר החברה:

ניתן לבצע ניתוח מהיר של הטבלה. ולראות את הסוגים השונים מכל קטגוריה:

הטבלה השנייה היא טבלת orders

המכילה נתונים על מכירות שבוצעו באתר החברה, כאשר כל מכירה מתקשרת לנתונים מטבלת website_sessions באמצעות עמודת website_session_id:

שאלה ראשונה: מהיכן מגיעה מרבית התנועה לאתר עד ליום 14/12/2012, פירוט לפי מקור התנועה, שם הקמפיין והדומיין המפנה לאתר שלנו ?

תשובה:

SELECT DISTINCT 
    utm_source,
    utm_campaign,
    http_referer,
    COUNT(DISTINCT website_sessions.website_session_id) AS Sessions
FROM website_sessions
WHERE website_sessions.created_at < '2012-04-12'
GROUP BY
    utm_source,
    utm_campaign,
    http_referer
ORDER BY 
    Sessions DESC
;

אנו למעשה מבקשים את העמודות הרלוונטיות (מקור תנועה, שם הקמפיין והאתר המפנה) ובונים עמודה נוספת שסופרת את נקודות המגע עם כל לקוח בכל אחד מהקטגוריות עד לתאריך 12/04/2012.

תוצאת השאילתה:

מה זה אומר? התוצאה מראה לנו שמירב התנועה מגיעה ממקור בשם ‘gsearch’, קמפיין בשם ‘nonbrand’, מאתר שהדומיין שלו  ‘https://www.gsearch.com’ (שורה ראשונה בתוצאה שקיבלנו).

שאלה שניה: כתוצאה מהתשובה שקיבלנו, מהו יחס ההמרה בהקשר של gsearch ,nonbrand , לתאריך 14/04/2012 ?

תשובה:

SELECT 
    utm_source,
     utm_campaign,
    COUNT(DISTINCT website_sessions.website_session_id) AS Sessions,
    COUNT(DISTINCT orders.order_id) AS Orders,
    COUNT(DISTINCT orders.order_id)/
    COUNT(DISTINCT website_sessions.website_session_id) AS CVR
FROM website_sessions
    LEFT JOIN orders
    ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-04-14'
    AND utm_source = 'gsearch'
    AND utm_campaign = 'nonbrand'
;

בשאילתה הזו עשינו שימוש בשתי הטבלאות.

כדי לקבל יחס המרה יש צורך בחישוב כמות המכירות לכל נקודת מגע. כאן עשינו שימוש בפקודה LEFT JOIN, כדי לקבל נתונים גם מטבלת orders. ספרנו את נקודות המגע בכל אחת מהטבלאות (עמודה שלוש וארבע), ובעמודה חמש חילקנו את נקודות המגע בהם בוצעה מכירה, בסך הנקודות המגע מטבלת website_sessions.

תוצאת השאילתה:

יחס ההמרה שקיבלנו הוא 2.88%.

שאלה שלישית: מהו יחס ההמרה בהקשר של gsearch, nonbrand by, בכל סוג מכשיר (דסקטופ, מובייל), עד לתאריך 11/05/2012 ?

תשובה:

SELECT 
    website_sessions.device_type,
    COUNT(DISTINCT website_sessions.website_session_id) AS Sessions,
    COUNT(DISTINCT orders.order_id) AS Orders,
    COUNT(DISTINCT orders.order_id)/
    COUNT(DISTINCT website_sessions.website_session_id) AS CVR
FROM website_sessions
    LEFT JOIN orders
    ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.created_at < '2012-05-11'
    AND utm_source = 'gsearch'
    AND utm_campaign = 'nonbrand'
GROUP BY 1
;

תוצאת השאילתה:

התשובה : בדסקטופ, יחס ההמרה, בקמפיין והמקור הספציפיים, הוא 3.73%, בעוד במובייל היחס הוא פחות מאחוז.

וזה מעלה כמובן סימן שאלה גדול האם ישנה בעיה טכנית במובייל שמקשה על הלקוח לבצע רכישה? המסקנה המיידית הנוספת היא שיש צורך לבצע אופטימיזציה BID OPTIMIZATION ולהגדיל את הבידים בדסקטופ כדי לשפר את המכירות במיידי.

שאלה רביעית: נניח שביצענו אופטימיזציה, כאמור, לדסקטופ. מהי המגמה השבועית החל מתאריך הבסיס 15/04/2012 ועד ליום 9/6/2012 , של נפח התנועה, בהקשר של  gsearch, nonbrand, לכל סוג מכשיר בנפרד?

תשובה:

SELECT 
    MIN(DATE(website_sessions.created_at)) AS First_day_of_week,
    COUNT(DISTINCT CASE WHEN  website_sessions.device_type = 'desktop' THEN website_sessions.website_session_id ELSE NULL END) AS Desktop,
    COUNT(DISTINCT CASE WHEN  website_sessions.device_type = 'mobile' THEN website_sessions.website_session_id ELSE NULL END) AS Mobile
FROM website_sessions
WHERE website_sessions.created_at < '2012-06-09'
    AND website_sessions.created_at > '2012-04-15'
    AND utm_source = 'gsearch'
    AND utm_campaign = 'nonbrand'
GROUP BY
    YEAR(website_sessions.created_at),
    WEEK(website_sessions.created_at)
;

השאילתה הזו דורשת הסבר:

עמודה ראשונה – מחשבת את היום הראשון בשבוע של כל שבוע בטווח הזמן שחישבנו.

עמודה שנייה ושלישית מבצעות PIVOT, המוכר מאקסל, שנותן לכל אחת מהאפשרויות ב”סוג מכשיר” עמודה משלו (דסקטופ, מובייל).

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

תוצאת השאילתה:

ניתן לראות שהחל מהשבוע שמתחיל ביום 20/5/2012, נפח התנועה בדסקטופ גדל על חשבון נפח התנועה מהמובייל שקטן.

כלומר האופטימיזציה שעשינו הצליחה.