بعد از اتصال آنالیتیکس 4 به بیگ کوئری، بعد از حداکثر 48 ساعت به داده‌های خام آنالیتیکس در بیگ کوئری دسترسی خواهید داشت. اما احتمالا سوالی که برای بسیاری از پرفورمنس مارکترها ایجاد می شود این است که چطور باید از این اطلاعات خام برای انجام محاسبات مختلف مانند ساخت audience استفاده کرد؟ در این مطلب قصد دارم چند نمونه از کوئری‌های لازم برای ساخت audience در بیگ کوئری از داده‌های آنالیتیکس را به شما عزیزان آموزش دهم. همراه من باشید.
در تمام کوئری‌هایی که در ادامه بررسی می شوند، هدف شمارش تعداد کاربران با یک ویژگی خاص است. اگر می‌خواهید به جای تعداد، به یوزر آی دی کاربران دسترسی داشته باشید، باید تابع COUNT را از کوئری‌های زیر حذف کنید.
اگر به یادگیری بیگ کوئری علاقه دارید، در دوره آموزشی من شرکت کنید 🙂
سگمنت افرادی که از شما خرید کرده اند
کاربرانی که در این گروه قرار می‌گیرند، افرادی هستند که یکی از ایونت‌های purchase یا in_app_purchase را انجام داده‌اند، درواقع افرادی هستند که یا از طریق سایت و یا از طریق اپلیکیشن خرید کرده اند.
کاربرانی که N روز گذشته، با سایت یا اپلیکیشن شما تعامل داشته‌اند
برای شمارش این کاربران، باید با تعریف اکتیو یوزر در آنالیتیکس 4 آشنایی داشته باشید. اکتیو یوزر به کاربری گفته میشود که حداقل یک ایونت در سایت شما انجام داده باشد و مدت زمان تعامل آن با سایت یا اپلیکیشن شما بیشتر از 0 ثانیه باشد. با توجه به این تعریف، تعداد اکتیو یوزرها در N روز گذشته برابر است با:
کاربرانی که N روز گذشته، با سایت یا اپلیکیشن شما تعامل نداشته‌اند
WITH MDaysUsers AS (
SELECT
_TABLE_SUFFIX,
user_pseudo_id
FROMbigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = ‘engagement_time_msec’
AND event_params.value.int_value > 0
AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(‘2021-01-31’, INTERVAL 7 DAY))
AND _TABLE_SUFFIX BETWEEN ‘20210101’ AND ‘20210131’
),
NDaysUsers AS (
SELECT
_TABLE_SUFFIX,
user_pseudo_id
FROMbigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* AS T
CROSS JOIN
T.event_params
WHERE
event_params.key = ‘engagement_time_msec’
AND event_params.value.int_value > 0
AND event_timestamp > UNIX_MICROS(TIMESTAMP_SUB(‘2021-01-31’, INTERVAL 2 DAY))
AND _TABLE_SUFFIX BETWEEN ‘20210101’ AND ‘20210131’
)
SELECT
COUNT(DISTINCT MDaysUsers.user_pseudo_id) AS n_day_inactive_users_count
FROM
MDaysUsers
LEFT JOIN
NDaysUsers
ON
MDaysUsers.user_pseudo_id = NDaysUsers.user_pseudo_id
WHERE
NDaysUsers.user_pseudo_id IS NULL;
