דהשבורד מכירות SQL (קבצים מצורפים)

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

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

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

הדהשבורד הסופי (ניתן ללחוץ על התמונה להגדלה):

שאילתות SQL

פלטפורמת דאטה-וורלד מאפשרת לאנליסטים שלוש פעולות ETL רלוונטיות לנו:

1> העלאת מסד נתונים בקבצי CSV שעליהן ניתן לכתוב את השאילתות.

2> כתיבת שאילתות SQL ושמירתן כמסד נתונים נפרד (הפלטפורמה אינה מאפשרת יצירת טבלה – create table, אבל השאילתות מתפקדות כמסד נתונים עצמאי שניתן לשאוב לפלטפומות אחרות).

3> אינטגרציה ישירה עם פלטפורמות אחרות כגון PBI, Tableau  ועוד.

כמה טיפים חשובים ינתנו בהמשך.

הטבלאות עליהן נבצע ETL הן:

טבלת Sales – טבלת רישומי פעילות מכירה, הכוללת תאריך הפעולה, פרטים על המוצר, נתוני מכירה מספריים (כמות, מחיר ללקוח), נתונים על ססטוס הפעולה ( נשלח ללקוח, בוטל, בתהליך וכו’), נתונים על הלקוח כולל איזור מכירה, קוד איש המכירות האחראי ועוד.

ניתן ללחוץ על התמונה להגדלה

טבלת אנשי מכירות – טבלה שבניתי, המתרגמת את קוד איש המכירות (מפתח ראשי) לשם שלו.

כך נראה מסד הנתונים שהקמנו בדאטה-וורלד (ניתן ללחוץ על התמונה להגדלה)

שאילתה ראשונה – Sales Table

השאילתה הזו מבצעת את הפעולות הבאות:

1> מציגה רק את העמודות מתוך טבלת Sales, הדרושות למודל.

2> בונה את עמודת SalePrice שהיא מכפלה של הכמות הנמכרת במחיר המוצר (ההכפלה ב 10 נדרשה כדי להביא את המספר לסכומים נוחים לעבודה).

3> בונה את עמודת profit באופן דומה לעמודה הקודמת שבנינו.

4> מבצעת חיבור JOIN של עמודת “שם איש המכירות” מטבלת salesman לטבלת Sales על בסיס עמודות קוד איש המכירות salesman_key.

SELECT
    UPPER(salesman.salesman_name) AS salesman_name, 
    CAST(salestable.orderdate AS DATE) AS TDATE,
    salestable.quantityordered, salestable.priceeach, salestable.quantityordered * salestable.priceeach * 10 AS SalePrice,
    CAST((salestable.quantityordered * salestable.priceeach) AS INTEGER) AS profit,
    salestable.status,  salestable.productline ,salestable.productcode,salestable.customername, salestable.dealsize
FROM salestable
JOIN salesman on salestable.salesman_key = salesman.salesman_key
ORDER BY TDATE
;

שאילתה שניה – Customer First Record

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

SELECT DISTINCT 
salestable.customername, 
FIRST_VALUE(CAST(salestable.orderdate AS DATE)) OVER (PARTITION BY salestable.customername ORDER BY salestable.orderdate) AS firstday
FROM salestable
ORDER BY salestable.orderdate
;

שאילתה שלישית – Territory

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

SELECT 
     sales.customername, sales.city, sales.state, sales.territory, sales.country
FROM sales
GROUP BY sales.customername
;

שאיבת מסד הנתונים ל PBI

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

הדרך היותר יעילה לעשות זאת היא כך:

לכל שאילתה יש כפתור DOWNLOAD. לחיצה עליו מציגה מספר אפשרויות. יש ללחוץ על האפשרות  Copy  URLOr Embed code:

קופץ החלון הבא שבו יש לבצע שתי פעולות:

1> להפעיל את האפשרות “Allow changes to the original query to update”. זה יאפשר לשינויים שנעשה בשאילתה לאחר מכן, להתעדכן ב PBI.

2> להעתיק את הלינק (הכתובת הראשונה), וללחוץ DONE.

את הכתובת שהעתקנו יש להדביק ב PBI כמקור נתונים אינטרנטי: GET DATA => WEB, ולטעון.

את הפעולה יש לבצע לכל אחת משלושת השאילות שכתבנו עד כה.

מכאן והלאה מקבלים טבלת נתונים לכל דבר ב PBI.

בניית המודל ב PBI

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

והנה גם מיפוי הקשרים בין עמודות הטבלאות:

נבנה את הנוסחאות למודל:

השוואת מחזורי מכירה בין אנשי המכירות

נוסחה 1 – סכום המכירות מטבלת SALES TABLE:

sales = SUM('SALES TABLE'[SalePrice])

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

Sales Bookings = 

var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var salesCM = CALCULATE([sales],'Date'[Current Month] = "True")
var salesCQ = CALCULATE([sales],'Date'[Current Qurter] = "True")
var salesCY = CALCULATE([sales],'Date'[Current Year] = "True")

Return 

switch
    (
    true(),
    selectedperiod="Current Month", salesCM,
    selectedperiod="Quarter To Date", salesCQ,
    selectedperiod="Year to Date", salesCY
    )

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

Sales Bookings YTD = 
var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var salesCM = CALCULATE([sales],DATESMTD(dateadd('Date'[Date],-1,Year))) 
var salesCQ = CALCULATE([sales],DATESQTD(dateadd('Date'[Date],-1,Year))) 
var salesCY = CALCULATE([sales],DATESYTD(dateadd('Date'[Date],-1,Year))) 

Return 

switch
    (
    true(),
    selectedperiod="Current Month", salesCM,
    selectedperiod="Quarter To Date", salesCQ,
    selectedperiod="Year to Date", salesCY
    )

שיעור סגירת עסקאות

כדי לחשב את שיעור סגירת עסקאות יש לחשב את מספר העסקאות הסגורות ואת מספר העסקאות הכולל את הפתוחות (או הלא סגורות):

מספר העסקאות הסגורות – הנחתי שעסקה סגורה מופיעה בסטטוס “Shipped”:

Close transactions number = 

var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var Shipped = FILTER('SALES TABLE', 'SALES TABLE'[status] = "Shipped")
var CMF = FILTER('Date', 'Date'[Current Month] = "True") 
var CqF = FILTER('Date', 'Date'[Current Qurter] = "True")
var CyF = FILTER('Date', 'Date'[Current Year] = "True")


var CM = CALCULATE(COUNTROWS('SALES TABLE'),CMF,Shipped)
var CQ = CALCULATE(COUNTROWS('SALES TABLE'),CqF,Shipped)
var CY = CALCULATE(COUNTROWS('SALES TABLE'),CyF,Shipped)

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date", CQ,
    selectedperiod="Year to Date", CY
    )

מספר העסקאות כולל הפתוחות:

Close Leads number = 

var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var CMF = FILTER('Date', 'Date'[Current Month] = "True") 
var CqF = FILTER('Date', 'Date'[Current Qurter] = "True")
var CyF = FILTER('Date', 'Date'[Current Year] = "True")


var CM = CALCULATE(COUNTROWS('SALES TABLE'),CMF)
var CQ = CALCULATE(COUNTROWS('SALES TABLE'),CqF)
var CY = CALCULATE(COUNTROWS('SALES TABLE'),CyF)

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date", CQ,
    selectedperiod="Year to Date", CY
    )

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

Lead to Win Rate = 
[Close transactions number]/[Close Leads number]

עלות רכישת לקוח ועלות רכישת ליד

כדי לחשב עלות פר לקוח סגור או פר ליד, יש לחשב את מספר הלקוחות/לידים חדשים בכל תקופה. שימו לב שכאן נכנסת לפעולה טבלת CUSTOMER FIRST RECORD אותה בנינו כשאילתת SQL בהתחלת הפוסט.

מספר הלקוחות החדשים לתקופה:

new customrs number = 

var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var Shipped = FILTER('SALES TABLE', 'SALES TABLE'[status] = "Shipped")
var CMF = FILTER('Date', 'Date'[Current Month] = "True") 
var CqF = FILTER('Date', 'Date'[Current Qurter] = "True")
var CyF = FILTER('Date', 'Date'[Current Year] = "True")


var CM = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CMF,Shipped)
var CQ = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CqF,Shipped)
var CY = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CyF,Shipped)

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date", CQ,
    selectedperiod="Year to Date", CY
    )

מספר הלידים החדשים לתקופה:

new lead number = 

var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])

var CMF = FILTER('Date', 'Date'[Current Month] = "True") 
var CqF = FILTER('Date', 'Date'[Current Qurter] = "True")
var CyF = FILTER('Date', 'Date'[Current Year] = "True")


var CM = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CMF)
var CQ = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CqF)
var CY = CALCULATE(COUNTROWS('CUSTOMER  FIRST RECORD'),CyF)

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date", CQ,
    selectedperiod="Year to Date", CY
    )

עלות פר-ליד:

Cost per Lead = 
var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var CM = (2000000/12)/[new lead number]
var CQ = 500000/[new lead number]
var CY = 2000000/[new lead number]

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date", CQ,
    selectedperiod="Year to Date", CY
    )

עלות רכישת לקוח Customer Acquisition Cost (CAC):

CAC = 
var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var cost = 2000000
var CM = (cost/12)/[new customrs number]
var CQ = (cost/4)/[new customrs number]
var CY = cost/[new customrs number]

Return 

switch
    (
    true(),
    selectedperiod="Current Month", CM,
    selectedperiod="Quarter To Date",CQ,
    selectedperiod="Year to Date", CY
    )

שיעור רווח פר-לקוח – תרשים פיזור

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

Profit Ratio = 

var PRatio = sumx('SALES TABLE', 'SALES TABLE'[profit]/ 'SALES TABLE'[SalePrice])
var selectedperiod = SELECTEDVALUE(SelectPeriod[Select Period])
var salesCM = CALCULATE(PRatio,'Date'[Current Month] = "True")
var salesCQ = CALCULATE(PRatio,'Date'[Current Qurter] = "True")
var salesCY = CALCULATE(PRatio,'Date'[Current Year] = "True")

Return 

switch
    (
    true(),
    selectedperiod="Current Month", salesCM,
    selectedperiod="Quarter To Date", salesCQ,
    selectedperiod="Year to Date", salesCY
    )

קבצים מצורפים:

https://bit.ly/3H0EuVc