📤 👞 ⭐️ Google Analytics कस्टम मापदंडों के उदाहरण का उपयोग करके Google BigQuery से एक नेस्टेड संरचना के साथ डेटा अनलोड करने के लिए कैसे 🔶 ⤵️ 🐝

Google BigQuery दुनिया भर की कंपनियों द्वारा उपयोग किया जाने वाला एक लोकप्रिय क्लाउड डेटाबेस है। कच्चे Google Analytics डेटा के साथ काम करने के लिए यह विशेष रूप से सुविधाजनक है: GA 360 में, BigQuery के साथ एकीकरण कुछ ही क्लिक में कॉन्फ़िगर किया गया है, और निशुल्क संस्करण के लिए तृतीय-पक्ष स्क्रिप्ट और मॉड्यूल हैं।

Google Analytics के कच्चे डेटा में, प्रत्येक रिकॉर्ड (पंक्ति) एक सत्र से मेल खाती है। ऐसी प्रविष्टि के अंदर नेस्टेड फ़ील्ड होते हैं जो सत्र के हिट के अनुरूप होते हैं:

अक्सर ऐसी नेस्टेड डेटा संरचना उन उपयोगकर्ताओं के लिए भ्रमित होती है जो समझ नहीं पाते हैं कि इसके साथ कैसे काम करें और इस तरह के डेटा को अनलोड करें।

उदाहरण के रूप में Google Analytics कस्टम मापदंडों का उपयोग करते हुए, मैं "अंगुलियों पर" यह समझाने की कोशिश करूंगा कि Google BigQuery में एम्बेडेड डेटा कैसे संग्रहीत किया जाता है और इसे कैसे अपलोड किया जा सकता है।

दिए गए क्वेरी कोड काम कर रहे हैं, उनका उपयोग समस्याओं को हल करने में किया जा सकता है, उनकी तालिका के नाम और आवश्यक कस्टम आयाम सूचकांक संख्या को प्रतिस्थापित किया जा सकता है।

• अनलोडिंग पंक्तियाँ
• घोंसले के ढांचे को बनाए रखते हुए उतारना
• उपयोगकर्ता पैरामीटर मानों को बदलने का उदाहरण

मूल बातें

BigQuery SQL की 2 बोलियों का समर्थन करता है: विरासत और मानक। Google नए SQL मानक का उपयोग करने की अनुशंसा करता है, जिस पर हम अनलोडिंग के लिए क्वेरी लिखेंगे।
हर कोई जो SQL के साथ काम करता है, कम से कम मानक क्वेरी निर्माण को जानता है:

SELECT *  * FROM *  * WHERE * *

यह डिज़ाइन कार्य करता है यदि तालिका संरचना सरल है, अन्य क्षेत्रों में कोशिकाओं के बिना:

हम नेस्टेड फ़ील्ड के साथ तालिकाओं पर विचार करते हैं। ऐसी तालिका की संरचना (उदाहरण के लिए, Google Analytics उपयोगकर्ता पैरामीटर):

कस्टम जीए विकल्प बीक्यू में

Google BigQuery में, ऐसी तालिका में निम्नलिखित कॉलम नाम (विभाजक "होंगे।" नेस्टिंग संरचना दिखाता है):

तो हम नेस्टेड फ़ील्ड्स से डेटा अनलोड कैसे करते हैं?

पंक्तियाँ उतारना

BQ में GA कस्टम मापदंडों के उदाहरण के साथ तालिका पर वापस जाएं।

कॉलम customDimensions.index और customDimensions.value सत्र और उपयोगकर्ता-परिभाषित कंप्यूटर आयामों के सूचकांक और मूल्य हैं।

हिट.कस्टमडिमेंट्स.इंडेक्स और हिट । कस्टमडिमेंशन्स.वल्यू कॉलम हिट हिट आयामों के सूचकांक और मूल्य हैं।

Google BigQuery में उपयोगकर्ता पैरामीटर कार्रवाई का एक और स्तर है - उत्पाद। Google BigQuery में उत्पाद कस्टम आयामों के नाम और मूल्य हिट .product.customDimensions.index और hits.product.customDimensions.value कॉलम में हैं। वे हिट उपयोगकर्ता मापदंडों के साथ सादृश्य द्वारा उतारे जाते हैं, आपको केवल घोंसले के शिकार के दूसरे स्तर को ध्यान में रखना होगा।

सत्र और उपयोगकर्ता स्तर उपयोगकर्ता विकल्प

यदि हमें नेस्टेड संरचना (यानी लाइन द्वारा लाइन) को बचाने के बिना प्रत्येक तिथि के लिए सत्र (कस्टम) कस्टम आयामों को अनलोड करने की आवश्यकता है, तो हमें क्या करना चाहिए?

प्रश्न का उत्तर देने के लिए, आइए BQ में GA उपयोगकर्ता मापदंडों के साथ तालिका पर करीब से नज़र डालें।
यह दर्शाता है कि कस्टमडिमेंट्स कॉलम में कोशिकाओं के मान एक और तालिका हैं :

यह मुख्य क्वेरी में इस तालिका के लिए एक उपश्रेणी बनाने के लिए पर्याप्त है:

 SELECT --   date, --    value (SELECT value --   customDimensions,     t FROM t.customDimensions --       WHERE index = 1) AS customDimensions1 FROM --    t     `project.dataset.tablename` AS t

आउटपुट एक तालिका है:

यदि हमें किसी अन्य उपयोगकर्ता पैरामीटर के मान के साथ एक स्तंभ जोड़ने की आवश्यकता है, तो हम एक और उपश्रेणी बनाते हैं:

 SELECT date, (SELECT value FROM t.customDimensions WHERE index = 1) AS customDimensions1, --      customDimensions (SELECT value FROM t.customDimensions WHERE index = 2) AS customDimensions2 FROM `project.dataset.tablename` AS t

हम निम्नलिखित प्राप्त करते हैं:

हिट-स्तरीय कस्टम विकल्प

हिट उपयोगकर्ता पैरामीटर सत्र (उपयोगकर्ता) वाले के समान ही अनलोड किए जाते हैं, सिवाय इसके कि उप-वर्ग को नेस्टेड हिट टेबल पर करने की आवश्यकता है। दूसरे शब्दों में, Google Analytics के कच्चे डेटा तालिका के हिट कॉलम में कोशिकाओं के मूल्य एक नेस्टेड तालिका है जिसमें customDimensions तालिका नेस्टेड है:

लाइन-दर-लाइन हिट उपयोगकर्ता मापदंडों को डाउनलोड करने का अनुरोध इस प्रकार होगा:

 SELECT --   date, --    value (SELECT value --   customDimensions,     h FROM h.customDimensions --       WHERE index = 3) AS customDimensions3 FROM --    t     `project.dataset.tablename` AS t, --   t.hits  h     t.hits AS h

क्वेरी का परिणाम एक तालिका होगी:

आप कई हिट उपयोगकर्ता मापदंडों को अनलोड कर सकते हैं और हिटनंबर पैरामीटर (सत्र में हिट का क्रम संख्या) जोड़ सकते हैं:

 SELECT date, h.hitNumber AS hitNumber, (SELECT value FROM h.customDimensions WHERE index = 3) AS customDimensions3, --        h.customDimensions (SELECT value FROM h.customDimensions WHERE index = 4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h

तालिका प्राप्त करें:

सत्र (उपयोगकर्ता) + हिट उपयोगकर्ता पैरामीटर

यदि एक क्वेरी में हम सत्र को उतारना चाहते हैं और उपयोगकर्ता मापदंडों को हिट करना चाहते हैं, तो आपको केवल मुख्य और नेस्टेड प्रदर्शन के लिए आवश्यक उपश्रेणियाँ बनाने की आवश्यकता है:

 SELECT date, h.hitNumber AS hitNumber, --     (SELECT value FROM t.customDimensions WHERE index=1) AS customDimensions1, (SELECT value FROM t.customDimensions WHERE index=2) AS customDimensions2, --     (SELECT value FROM h.customDimensions WHERE index=3) AS customDimensions3, (SELECT value FROM h.customDimensions WHERE index=4) AS customDimensions4 FROM `project.dataset.tablename` AS t, t.hits AS h

क्वेरी के परिणामस्वरूप प्राप्त की जाने वाली तालिका:

घोंसले के ढांचे को बनाए रखते हुए उतारना

Google BigQuery में उपयोगकर्ता पैरामीटर के मूल्यों को प्रतिस्थापित करते समय इस तरह की उतराई आवश्यक हो सकती है।

उदाहरण
Google Analytics में, पूर्ण प्रारूप में देश का नाम सत्र उपयोगकर्ता पैरामीटर के साथ इंडेक्स 12 और रूस से उपयोगकर्ताओं के लिए इंडेक्स 25 के साथ हिट उपयोगकर्ता पैरामीटर में स्थानांतरित किया जाता है: RUSSIA। संक्षिप्त रूप में देश के प्रारूप को बदलना आवश्यक है: RUS।

ऐसा करने के लिए, आपको Google BigQuery में डेटा के पूरे इतिहास के लिए उपयोगकर्ता के देश के साथ उपयोगकर्ता पैरामीटर के आवश्यक मूल्यों को बदलने की आवश्यकता है।

समस्या को हल करने की प्रक्रिया:

नेस्टिंग संरचना को बनाए रखते हुए सभी डेटा अनलोड करें
उपयोगकर्ता पैरामीटर के मान को देश के साथ बदलें
तालिका को फिर से लिखें

नेस्टिंग संरचना को बनाए रखते हुए डेटा अपलोड करने के लिए, आपको ARRAY फ़ंक्शन और SELECT AS STRUCT निर्माण का उपयोग करना होगा। आइए जानें कि यह क्या है।

ARRAY फ़ंक्शन का सिंटैक्स निम्नानुसार है:

 ARRAY(**)

यह तत्वों की एक सरणी देता है।

पंक्ति-दर-पंक्ति रिकॉर्डिंग के साथ एक सरणी की तुलना करना:

बाईं ओर एक सरणी है, दाईं ओर एक पंक्ति रिकॉर्डिंग है

यदि हम एक नेस्टेड संरचना को सहेजना चाहते हैं और कई कॉलम के साथ एक सरणी को अनलोड करना चाहते हैं, तो हमें ARRAY (SELECT AS STRUCT) का उपयोग करना होगा:

घोंसला ऐरे

सत्र और उपयोगकर्ता स्तर उपयोगकर्ता विकल्प

सत्र की संरचना (कस्टम) कस्टम आयामों को बनाए रखते हुए उतारने के लिए, हम क्वेरी का उपयोग करते हैं:

 SELECT date, --  ARRAY(SELECT AS STRUCT...)    ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions) AS customDimensions FROM `project.dataset.tablename` AS t

इसके निष्पादन के परिणामस्वरूप, एक तालिका प्राप्त की जाती है, जिसमें Google Analytics डेटा के "कच्चे" घोंसले की संरचना को बचाया जाता है:

हिट-स्तरीय कस्टम विकल्प

नेस्टिंग संरचना को बनाए रखते हुए Google बिगविक से हिट उपयोगकर्ता मापदंडों के मूल्यों को उतारने के लिए, यह ध्यान रखना महत्वपूर्ण है कि कस्टमडिमेंशन टेबल हिट टेबल में नेस्टेड है। दूसरे शब्दों में, आपको 2 बार ARRAY सबक्वेरी (SELECT AS STRUCT ...) बनाने की आवश्यकता है: पहले नेस्टेड हिट टेबल पर, फिर उसमें कस्टमडिमेंशन टेबल नेस्टेड है:

 SELECT date, --    t.hits ARRAY(SELECT AS STRUCT hitNumber, --    h.customDimensions ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions) AS customDimensions FROM t.hits AS h ) AS hits FROM `project.dataset.tablename` AS t

इस क्वेरी का परिणाम एक तालिका होगी:

सत्र (उपयोगकर्ता) + हिट उपयोगकर्ता पैरामीटर

लाइन-बाय-लाइन अनलोडिंग के साथ, हमें वांछित नेस्टेड तालिकाओं के लिए उसी क्वेरी में ARRAY सबक्वेरीज़ (SELECT AS STRUCT ...) को संयोजित करने की आवश्यकता है:

 SELECT date, --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, value FROM t.customDimensions ) AS customDimensions, --  Custom Dimensions ARRAY(SELECT AS STRUCT hitNumber, ARRAY(SELECT AS STRUCT index, value FROM h.customDimensions ) AS customDimensions FROM t.hits AS h) AS hits FROM `project.dataset.tablename` AS t

परिणाम क्या है:

उपयोगकर्ता पैरामीटर मानों को बदलने का उदाहरण

आइए अपने उदाहरण पर वापस जाएं।
पिछले अनुभाग में, हमें नेस्टिंग संरचना को बनाए रखते हुए सत्र (उपयोगकर्ता) अपलोड करने और Google Analytics के उपयोगकर्ता मापदंडों को हिट करने का अनुरोध मिला।
हम इस प्रश्न को पूरक के साथ पूरक करते हैं * रिप्लेसमेंट के साथ अनलोडिंग के लिए निर्माण का निर्माण और आवश्यक उपयोगकर्ता मापदंडों के मूल्यों को अपडेट करने के लिए CASE:

 --      t.customDimensions  t.hits SELECT *REPLACE( --  () Custom Dimensions ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=12 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM t.customDimensions) AS customDimensions, --  Custom Dimensions --   t.hits      h.customDimensions ARRAY(SELECT AS STRUCT *REPLACE( ARRAY(SELECT AS STRUCT index, --      CASE WHEN index=25 AND value='RUSSIA' THEN 'RUS' ELSE value END AS value FROM h.customDimensions) AS customDimensions) FROM t.hits AS h) AS hits) FROM `project.dataset.tablename` AS t

इस अनुरोध के परिणामस्वरूप, हमें Google Analytics के कच्चे डेटा के साथ मूल तालिका मिलेगी। वह मूल नेस्टिंग संरचना को पूरी तरह से बनाए रखेगा, लेकिन आवश्यक उपयोगकर्ता मापदंडों के मूल्यों को नए लोगों में बदल दिया जाएगा।

Google BigQuery में नेस्टेड डेटा संरचना के साथ काम करने का विषय आसान नहीं है।

मुझे उम्मीद है कि मैं इस मुद्दे को स्पष्ट करने में कामयाब रहा। लेकिन, मुझे आपको याद दिलाना है, कुछ सीखने का सबसे अच्छा तरीका अधिक अभ्यास करना है।

Google Analytics कस्टम मापदंडों के उदाहरण का उपयोग करके Google BigQuery से एक नेस्टेड संरचना के साथ डेटा अनलोड करने के लिए कैसे

मूल बातें

पंक्तियाँ उतारना

सत्र और उपयोगकर्ता स्तर उपयोगकर्ता विकल्प

हिट-स्तरीय कस्टम विकल्प

सत्र (उपयोगकर्ता) + हिट उपयोगकर्ता पैरामीटर

घोंसले के ढांचे को बनाए रखते हुए उतारना

सत्र और उपयोगकर्ता स्तर उपयोगकर्ता विकल्प

हिट-स्तरीय कस्टम विकल्प

सत्र (उपयोगकर्ता) + हिट उपयोगकर्ता पैरामीटर

उपयोगकर्ता पैरामीटर मानों को बदलने का उदाहरण

More articles: