बिक्री फ़नल: एक्सेल का उपयोग कर डेटाबेस से स्वचालित रूप से अपडेट की गई रिपोर्ट बनाना

यदि आप एक ऑनलाइन सेवा बेच रहे हैं, तो आप शायद यह देखना चाहेंगे कि बिक्री फ़नल के प्रत्येक चरण में क्या होता है। फ़नल के विश्लेषण से, महत्वपूर्ण निष्कर्ष निकाले जा सकते हैं: आवेदन की स्थापना और प्रारंभिक सेटअप कितना स्पष्ट और सुविधाजनक है, कितने और कौन से ग्राहक सेवा के सक्रिय उपयोगकर्ता बन जाते हैं, मुफ्त संस्करण से भुगतान किए गए प्रतिशत तक कितना प्रतिशत जाता है। इसके अलावा, रूपांतरण दरों की गतिशीलता हमें यह निष्कर्ष निकालने की अनुमति देती है कि बिक्री बढ़ाने के लिए किए गए उपाय प्रभावी हैं।

कट के तहत आपको एक्सेल के साथ काम करने की कुछ तकनीकों का विवरण मिलेगा जो डेटा सरणियों के विश्लेषण में उपयोगी हो सकता है। हम यह बताएंगे कि हम एक्सेल पिवट टेबल का उपयोग करके jivosite.ru सेवा के लिए प्रबंधन आँकड़े कैसे बनाए रखते हैं और बिक्री कीप रिपोर्ट के उदाहरण का उपयोग करते हुए ODBC के माध्यम से MySQL से जुड़ते हैं। प्रस्तावित विधि काफी सरल और सार्वभौमिक है, इसकी मदद से आप मिनटों में सुंदर रिपोर्ट बना सकते हैं।

समस्या का बयान

Jivosite.ru सेवा के लिए बिक्री फ़नल रिपोर्ट बनाना आवश्यक है। यह ऑनलाइन स्टोर के लिए एक ऑनलाइन सलाहकार है, जिसे फ्रीमियम मॉडल के तहत बेचा जाता है। उपयोगकर्ता दो सप्ताह की डेमो अवधि के साथ जुड़ते हैं, जिसके दौरान एक विस्तारित संस्करण उपलब्ध होता है। 2 सप्ताह के बाद, एक नि: शुल्क संस्करण रहता है, जो समय सीमा के बिना काम करना जारी रखता है।

इस प्रकार, हमारे पास निम्नलिखित बिक्री फ़नल हैं:



फ़नल के प्रत्येक स्तर पर ग्राहकों की संख्या को दिखाने के लिए आवश्यक है, साथ ही प्रत्येक स्तर के लिए रूपांतरण दर की गणना करें, जो सप्ताह और महीने से अलग हो। स्रोत डेटा MySQL में है, रिपोर्ट को कुछ बटनों को दबाकर स्वचालित रूप से बनाया जाना चाहिए, और यदि आवश्यक हो, तो विभिन्न श्रेणियों में अनुभागों का निर्माण करने और अतिरिक्त प्रोग्रामिंग के बिना फ़िल्टर पेश करने की भी अनुमति दें।

हम डेटाबेस से स्रोत डेटा लोड करते हैं

Excel में डेटाबेस से डेटा लोड करने के लिए, हमें ODBC ड्राइवर की आवश्यकता होती है। हमारे मामले में, हम विंडोज के लिए ODBC-MySQL कनेक्टर का उपयोग करेंगे। कनेक्टर के साथ खसखस ​​पर कुछ काम नहीं किया, लेकिन शायद यह पहले से ही नए संस्करणों में तय किया गया है।

ड्राइवर स्थापित करने के बाद, एक खाली एक्सेल वर्कबुक बनाएं, टैब खोलें "डेटा" - "अन्य स्रोतों से" - "Microsoft क्वेरी से"



फिर "नया डेटा स्रोत" चुनें, कनेक्शन का नाम दर्ज करें, ड्राइवर "MySQL ODBC ड्राइवर" चुनें। फिर हम "संचार" बटन दबाते हैं, हमारे डेटाबेस से कनेक्ट करने के लिए पैरामीटर दर्ज करते हैं, "ओके" पर क्लिक करें। उसके बाद, यदि कनेक्शन सफलतापूर्वक स्थापित किया गया है, तो Microsoft क्वेरी क्वेरी बनाने के लिए चरण-दर-चरण विज़ार्ड की पेशकश करेगा। हम एक विफलता के साथ सभी पॉप-अप विंडो बंद करते हैं, और फिर "एसक्यूएल" पर क्लिक करते हैं और हमारी एसक्यूएल क्वेरी दर्ज करते हैं, जो मैन्युअल रूप से स्रोत तालिका वापस कर देगा। हमारा अनुरोध बस डेटाबेस स्लेव सर्वर से जुड़े क्लाइंट की तालिका से चयन करता है।

हमारी स्रोत तालिका में, हम निम्नलिखित कॉलम का उपयोग करेंगे:

• बनाया - ग्राहक पंजीकरण की तारीख
• नाम - साइट URL
• was_installed - 1 यदि क्लाइंट ने अपनी साइट पर विजेट स्थापित किया है, तो यदि यह कभी स्थापित नहीं होता है
• chats_count - हमारी सेवा का उपयोग करके आयोजित संवादों की संख्या
• is_paid - 0 यदि ग्राहक ने हमें कुछ नहीं दिया है, तो 1 - यदि भुगतान किया गया है

क्वेरी का परिणाम एक ऑर्डर की गई तालिका के रूप में एक्सेल वर्कशीट पर जाएगा।



इस आदेशित तालिका में कई उपयोगी गुण हैं जिनका उपयोग हम बाद में करेंगे।

एक धुरी तालिका बनाएँ

बड़े स्रोत डेटा सरणी को सुविधाजनक और सुंदर रिपोर्ट में बदलने के लिए, हम पिवट टेबल का उपयोग करेंगे। हम स्रोत डेटा (सेल A1) के साथ तालिका के शीर्ष-बाएँ कक्ष पर क्लिक करते हैं, फिर "इन्सर्ट" - "पिवट टेबल" - "ओके"। इस प्रकार, पिवट तालिका के लिए स्रोत परिणाम MySQL में संपूर्ण क्वेरी परिणाम होगा, जब नए कॉलम और पंक्तियाँ जोड़ते हैं, तो पिवट तालिका अपने आप अपडेट हो जाएगी।

एक खाली पिवट तालिका इस तरह दिखती है:

हम बिक्री के प्रत्येक चरण में ग्राहकों की संख्या की गणना करते हैं

इस तरह की रिपोर्ट बनाने से पहले, हमें उस सप्ताह की संख्या और वर्ष को जोड़ना होगा जिसमें ग्राहक स्रोत तालिका में प्रत्येक पंक्ति के लिए जुड़ा हुआ था। यह वर्ष और सप्ताह तक डेटा को समूहबद्ध करने के लिए आवश्यक है। ऐसा करने के लिए, स्रोत डेटा के साथ शीट खोलें, अंतिम कॉलम के दाईं ओर स्क्रॉल करें, अंतिम कॉलम के हेडर के दाईं ओर सेल पर क्लिक करें, और वहां हम "कनेक्शन सप्ताह" लिखते हैं। पंक्तियों में रिक्त मानों के साथ एक नया स्तंभ तालिका में जोड़ा गया है। अब नए कॉलम के शीर्षक के तहत सेल में, हम सूत्र "= WEEKLY (") लिखते हैं और इस पंक्ति में सेल पर क्लिक करते हैं, जिसमें हम क्लाइंट के कनेक्शन की तारीख दर्शाते हैं।



इस स्थिति में, सूत्र "= WEEKLY ([@ बनाया गया]; 21)" जैसा दिखेगा। यदि कक्ष सूत्र के समान पंक्ति पर है, तो स्मार्ट Excel स्तंभ के नाम से एक लिंक बनाता है, और यह स्वचालित रूप से तालिका के सभी पंक्तियों को इस सूत्र से भर देता है। जब आप स्रोत डेटा तालिका में पंक्तियों को जोड़ते हैं, तो नए परिकलित कक्ष स्वचालित रूप से जुड़ जाएंगे। सुविधाजनक, एक्सेल सम्मान :)। कृपया ध्यान दें कि सप्ताह संख्या की गणना के लिए अलग - अलग एल्गोरिदम हैं। अपने लिए, हमने स्कीम नंबर 21 को चुना।

इसी प्रकार, "= YEAR ([@ बनाया])" सूत्र के साथ "कनेक्शन का वर्ष" कॉलम जोड़ें। उसके बाद, हमारी पिवट टेबल के साथ शीट पर जाएं, पिवट टेबल पर राइट-क्लिक करें - "रिफ्रेश", ताकि टेबल सोर्स डेटा में नए कॉलम के बारे में पता लगा ले।

बेशक, इन स्तंभों को SQL का उपयोग करके स्रोत डेटा में जोड़ा जा सकता है, लेकिन एक्सेल में, यह किसी भी तरह से तेज और अधिक सुखद है। हालांकि, यह निश्चित रूप से स्वाद की बात है :)

अब हम फ़ील्ड की सूची से पंक्ति "पंक्ति के नाम" और "कनेक्शन के सप्ताह" कॉलम को "पंक्ति नाम", और फ़ील्ड "नाम" (हम इस क्षेत्र में साइट URL) को फ़ील्ड "मान" तक खींचते हैं।



हमें एक साफ-सुथरी तालिका मिलेगी जिसमें जुड़े हुए ग्राहकों की संख्या वर्ष के सप्ताह तक टूट जाती है। हमने "नाम" फ़ील्ड को मान क्षेत्र में घसीटा है ताकि एक्सेल इस कॉलम (यानी सभी तत्वों) में तत्वों की संख्या को गिनता है, जिसे सप्ताह और वर्ष के अनुसार समूहीकृत किया जाता है। यह पंजीकरण की संख्या (फ़नल का दूसरा चरण) होगी।

हम यह गणना करेंगे कि हर हफ्ते पंजीकृत कितने ग्राहकों ने साइट पर हमारी चैट स्थापित की है। ऐसा करने के लिए, मान फलक में "was_installed" फ़ील्ड को खींचें। इस क्षेत्र में, प्रारंभिक डेटा में "0" है यदि विजेट स्थापित नहीं है, और यदि स्थापित किया गया है तो "1"। फिर राइट-क्लिक करें - "मान फ़ील्ड के पैरामीटर" - ऑपरेशन "सम" चुनें। अब दूसरा स्तंभ धुरी तालिका में दिखाई दिया है, जिसमें हम देखते हैं कि किसी भी सप्ताह पंजीकृत कितने ग्राहकों ने साइट पर विजेट स्थापित किया है।



अब सक्रिय ग्राहकों की गिनती करें। हम उन लोगों पर सक्रिय विचार करेंगे, जिनके पास साइट के आगंतुकों के साथ 20 से अधिक संवाद हैं। ऐसा करने के लिए, हमें "is_active" कॉलम को सेल सूत्र "= IF ([@ [chats_count]]>> 20; 1; 0;") के साथ स्रोत डेटा टेबल पर जोड़ना होगा। Chats_count कॉलम में हमारे पास क्लाइंट चैट की संख्या है। नतीजतन, कॉलम "is_active" में हमारे पास "1" होगा यदि क्लाइंट के पास 20 से अधिक चैट हैं। अब is_active फ़ील्ड को मान श्रेणी में भी खींचा जा सकता है।

हिस्टोग्राम के रूप में थोड़ा फेंग शुई जोड़ना और स्तंभों का नाम बदलना, हमें यह प्लेट मिलती है:



यहां हमें पहले से ही अच्छे आंकड़े मिले हैं, जो डेटाबेस से स्वचालित रूप से अपडेट भी हो जाता है। डेटा को अपडेट करने के लिए, आपको पहले स्रोत डेटा के साथ शीट पर जाना होगा, वहां, टेबल पर राइट-क्लिक करें - "अपडेट"। और फिर पिवट टेबल पर राइट क्लिक करें - "अपडेट"।

हम रूपांतरण दर की गणना करते हैं

K0 की गणना करने के लिए, आपको Google विश्लेषिकी से अद्वितीय आगंतुकों के डेटा लेने की आवश्यकता है, और हम इसे इस मैनुअल के दायरे से बाहर छोड़ देंगे (वैसे, हम इस समस्या को Google विश्लेषिकी से कॉपी-पेस्ट करके हल कर रहे हैं)।

आइए k1 की गणना करके शुरू करें - उन लोगों की संख्या का अनुपात जिन्होंने अपनी साइट पर पंजीकृत ग्राहकों की कुल संख्या के लिए चैट स्थापित किया है।

एक नहीं, इतना सुंदर क्षण है कि हमें सीधे हल करने का तरीका नहीं मिला: स्रोत डेटा तालिका में, सूत्र "= 1" के साथ कॉलम "एक" जोड़ें - ताकि स्रोत तालिका के सभी कक्षों में एक इकाई इस स्तंभ में दिखाई दे।

अब आप ऐसे परिकलित फ़ील्ड को जोड़ सकते हैं:



नाम "k1" लिखा है, सूत्र में हम "= SUM (was_installed) / SUM (एक)" दर्शाते हैं।



यदि रिपोर्ट को पिवट टेबल में सप्ताह के अनुसार रखा जाता है, तो हम उन ग्राहकों की संख्या का अनुपात प्राप्त करते हैं जिन्होंने इस सप्ताह (SUM (एक)) पंजीकृत किए गए कुल ग्राहकों के लिए विजेट (SUM (wasinstall)) स्थापित किया है। यदि रिपोर्ट को महीने के अनुसार रखा जाता है, तो गुणांक को उसके अनुसार पुनर्गणना किया जाएगा। यह ध्यान रखना महत्वपूर्ण है कि रूपांतरण दिखाता है कि ग्राहक किस अनुपात में अपनी साइट पर चैट करते हैं जो एक विशेष सप्ताह में पंजीकृत हैं। यानी यदि ग्राहक चौथे सप्ताह में पंजीकृत है, और केवल 10 वें सप्ताह में साइट पर चैट सेट करता है, तो 4 वें सप्ताह के लिए रिपोर्ट में आंकड़ा बदल जाएगा।

अब हम सक्रिय ग्राहकों के लिए रूपांतरण से विचार करते हैं:
k2 = SUM (is_active) / SUM (was_installed)

उसी तरह, हम सक्रिय ग्राहकों से भुगतान वाले लोगों के लिए रूपांतरण के लिए एक क्षेत्र जोड़ते हैं:
k3 = SUM (is_paid) / SUM (is_active)

केवल k3 को स्क्रीनशॉट में नहीं दिखाया जा सकता, व्यापार रहस्य :)

अब हमारी धुरी तालिका में फ़ील्ड k1, k2, k3 हैं, जिन्हें मूल्य श्रेणी में खींचा जा सकता है। थोड़ा फेंग शुई जोड़कर, हम फ़नल द्वारा ऐसी तालिका प्राप्त करते हैं, जो सप्ताह से टूट जाती है:



कुछ निष्कर्ष पहले से ही निकाले जा सकते हैं, हालांकि, हम व्यापार विश्लेषण के सवालों को एक और पोस्ट पर छोड़ देंगे, अब हम तकनीकी मुद्दों में रुचि रखते हैं।

मासिक बिक्री कीप

साप्ताहिक रिपोर्ट से, मासिक रिपोर्ट बनाना बहुत सरल है। हम सूत्र डेटा के साथ "कनेक्शन महीना" कॉलम जोड़ते हैं "= मोंठ ([बनाया])" स्रोत डेटा के लिए, पिवट टेबल पर राइट-क्लिक करें - "अपडेट" और फ़ील्ड को "स्ट्रींग नेम्स" फ़ील्ड के क्षेत्र में "कनेक्शन का महीना" खींचें (फ़ील्ड के बाद) "कनेक्शन का वर्ष")। यह कुछ इस तरह दिखेगा:



और यहाँ एक सुंदर मासिक टैबलेट है:

अन्य रिपोर्टिंग विकल्प

यदि आप धुरी तालिकाओं से परिचित नहीं हैं, तो मेरा सुझाव है कि आप स्वयं उनके साथ खेलें। यह एक महान विश्लेषिकी उपकरण है जो दिलचस्प निर्भरताओं की पहचान करने की कोशिश करता है। उदाहरण के लिए, ग्राहकों के स्रोत (विज्ञापन अभियान) के संदर्भ में विभिन्न चरणों में रूपांतरण देखना दिलचस्प है। ऐसा करने के लिए, हम प्रत्येक ग्राहक को पंजीकृत करते समय डेटाबेस में UTM टैग संग्रहीत करते हैं, और निरपेक्ष (रूबल) और रिश्तेदार (रूपांतरण) इकाइयों में विभिन्न विज्ञापन अभियानों की प्रभावशीलता पर एक रिपोर्ट बनाते हैं।

वैसे, पिवट टेबल के प्रत्येक सेल पर डबल-क्लिक करने से स्रोत डेटा की लाइनों की एक सूची खुलती है जो इस आंकड़े की गणना करने के लिए उपयोग की गई थीं। यह पता लगाना बहुत सुविधाजनक है कि यह कहाँ से बढ़ रहा है।

सारांश तालिकाओं में कई और विशेषताएं और उपकरण हैं जो आपको दिलचस्प रिपोर्ट जल्दी से प्राप्त करने की अनुमति देते हैं। मैं दृढ़ता से अनुशंसा करता हूं कि सभी उद्यमी जो अपने व्यवसाय के मास्टर इन टूल में होने वाली प्रक्रियाओं के बीच रखना चाहते हैं।