Özel SQL Sorguları

"page": {
  "url": "Tam URL",
  "path": "URL yolu",
  "title": "Sayfa başlığı",
  "protocol": "Kullanılan protokol",
  "hostname": "Alan adı",
  "hash": "URL hash'i",
  "referrer": "Referans veren URL"
}

"screen": {
  "resolution": "Ekran çözünürlüğü",
  "color_depth": "Bit cinsinden renk derinliği",
  "pixel_depth": "Piksel derinliği",
  "viewport_size": "Görüntü alanı boyutları",
  "device_pixel_ratio": "Cihaz piksel oranı"
}

"browser": {
  "name": "Tarayıcı adı",
  "version": "Tarayıcı versiyonu",
  "language": "Tarayıcı dili",
  "platform": "İşletim platformu",
  "vendor": "Tarayıcı sağlayıcısı",
  "user_agent": "User agent dizisi"
}

SELECT 
    [columns]
FROM 
    default.analytics
WHERE 
    [conditions]
GROUP BY 
    [grouping]
ORDER BY 
    [ordering]
LIMIT 
    [limit]

-- Temel property çıkarma
JSONExtractString(properties, 'device_type')
JSONExtractString(properties, 'timezone')

-- İç içe property çıkarma
JSONExtractString(properties, 'page', 'url')
JSONExtractString(properties, 'browser', 'name')
JSONExtractString(properties, 'screen', 'resolution')

-- Float değerler
JSONExtractFloat(properties, 'screen', 'device_pixel_ratio')
JSONExtractFloat(properties, 'screen', 'color_depth')

-- Integer değerler
JSONExtractInt(properties, 'batch_size')

-- İlk olaydan event name çıkarma (dizi indeksi 1'den başlar)
JSONExtractString(JSONExtractString(properties, 'events')[1], 'event_name')

-- İlk olaydan source çıkarma
JSONExtractString(JSONExtractString(properties, 'events')[1], 'source')

-- İlk olaydan timestamp çıkarma
JSONExtractString(JSONExtractString(properties, 'events')[1], 'timestamp')

-- ARRAY JOIN kullanarak dizilerle çalışma
FROM default.analytics
ARRAY JOIN JSONExtractArrayRaw(properties, 'events') as event

-- İlk olaydan iç içe query parametrelerini çıkarma
JSONExtractString(
  JSONExtractString(
    JSONExtractString(properties, 'events')[1], 
    'query_params'
  ), 
  'tid'
)

-- Gerekli tarih aralığı formatı (? yerine gerçek tarihleri koyun)
WHERE created_at BETWEEN toDateTime(?) AND toDateTime(?)

-- Belirli tarihlerle örnek
WHERE created_at BETWEEN toDateTime('2025-02-10 00:00:00') AND toDateTime('2025-02-17 23:59:59')

-- Ek zaman filtreleri ekleyebilirsiniz
WHERE created_at BETWEEN toDateTime('2025-02-17 00:00:00') AND toDateTime('2025-02-18 00:00:00')
  AND JSONExtractString(properties, 'timestamp') LIKE '%18T%'

WHERE created_at BETWEEN toDateTime(date_sub(DAY, 7, now())) AND toDateTime(now())

SELECT
    id,
    event_name,
    JSONExtractString(properties, 'device_type') AS device,
    JSONExtractString(properties, 'page', 'url') AS page_url,
    JSONExtractString(properties, 'browser', 'name') AS browser_name,
    JSONExtractString(properties, 'screen', 'resolution') AS screen_resolution,
    JSONExtractFloat(properties, 'screen', 'device_pixel_ratio') AS pixel_ratio,
    created_at
FROM default.analytics
WHERE created_at BETWEEN toDateTime('2025-02-10 00:00:00') AND toDateTime('2025-02-17 23:59:59')
LIMIT 10;

SELECT
  toDate(created_at) as date,
  JSONExtractString(JSONExtractRaw(properties, 'page'), 'path') as page_path,
  COUNT(*) as view_count
FROM default.analytics
WHERE event_name = 'page_view'
  AND created_at BETWEEN toDateTime(date_sub(DAY, 7, now())) AND toDateTime(now())
GROUP BY
  date,
  page_path
ORDER BY date DESC, view_count DESC
FORMAT TabSeparatedWithNames

WITH CityPurchases AS (
    SELECT 
        JSONExtractString(properties, 'city') AS city,
        COUNT(DISTINCT JSONExtractString(properties, 'crmId')) AS unique_customers,
        COUNT(*) AS total_purchases,
        SUM(toFloat64OrNull(JSONExtractString(item, 'price'))) AS total_revenue,
        AVG(toFloat64OrNull(JSONExtractString(item, 'price'))) AS avg_basket_size,
        COUNT(*) / COUNT(DISTINCT JSONExtractString(properties, 'crmId')) AS purchases_per_customer
    FROM default.analytics
    ARRAY JOIN JSONExtractArrayRaw(properties, 'items') AS item
    WHERE created_at BETWEEN toDateTime(date_sub(DAY, 30, now())) AND toDateTime(now())
        AND event_name = 'purchase'
        AND JSONExtractString(properties, 'country') = 'TR'
        AND JSONExtractString(properties, 'city') != ''
    GROUP BY city
    ORDER BY total_purchases DESC
    LIMIT 20
)
SELECT 
    city AS "City",
    unique_customers AS "Unique Customers",
    total_purchases AS "Total Purchases",
    ROUND(total_revenue, 2) AS "Total Revenue",
    ROUND(avg_basket_size, 2) AS "Average Basket Size",
    ROUND(purchases_per_customer, 2) AS "Purchases per Customer"
FROM CityPurchases
ORDER BY "Total Purchases" DESC

SELECT
   toDate(created_at) AS PurchaseDate,
   CASE
       WHEN JSONExtractString(properties, 'user_agent') LIKE '%iPhone%' OR JSONExtractString(properties, 'user_agent') LIKE '%iPad%' THEN 'iOS'
       WHEN JSONExtractString(properties, 'user_agent') LIKE '%Android%' THEN 'Android'
       WHEN JSONExtractString(properties, 'user_agent') LIKE '%Windows%' OR JSONExtractString(properties, 'user_agent') LIKE '%Macintosh%' THEN 'Web'
       ELSE 'Unknown'
   END AS Platform,
   COUNT(*) AS TotalPurchases,
   SUM(toFloat64OrNull(JSONExtractString(item, 'price'))) AS TotalRevenue
FROM default.analytics
ARRAY JOIN JSONExtractArrayRaw(properties, 'items') AS item
WHERE
   created_at BETWEEN toDateTime(date_sub(DAY, 7, now())) AND toDateTime(now())
   AND event_name = 'purchase'
GROUP BY
   PurchaseDate,
   Platform
ORDER BY
   PurchaseDate DESC,
   TotalPurchases DESC

WITH 
base_data AS (
    SELECT
        created_at,
        event_name,
        JSONExtractString(properties, 'distinct', 'server_distinct_id') AS server_distinct_id,
        JSONExtractString(properties, 'page', 'url') AS page_url,
        JSONExtractString(properties, 'page', 'path') AS page_path,
        JSONExtractString(properties, 'page', 'referrer') AS page_referrer,
        JSONExtractString(properties, 'device_type') AS device_type
    FROM default.analytics
    WHERE created_at BETWEEN toDateTime(date_sub(DAY, 2, now())) AND toDateTime(now())
),

session_calcs AS (
    SELECT 
        *,
        MIN(created_at) OVER (PARTITION BY server_distinct_id) as first_seen,
        MAX(created_at) OVER (PARTITION BY server_distinct_id) as last_seen,
        if(dateDiff('minute',
            lagInFrame(created_at) OVER (PARTITION BY server_distinct_id ORDER BY created_at),
            created_at) > 30 OR
            lagInFrame(created_at) OVER (PARTITION BY server_distinct_id ORDER BY created_at) IS NULL,
            1, 0) AS is_new_session
    FROM base_data
)

SELECT 
    server_distinct_id,
    event_name,
    page_url,
    page_path,
    page_referrer,
    device_type,
    first_seen,
    last_seen,
    COUNT(*) as event_count
FROM session_calcs
GROUP BY 
    server_distinct_id,
    event_name,
    page_url,
    page_path,
    page_referrer,
    device_type,
    first_seen,
    last_seen
ORDER BY last_seen DESC
LIMIT 100

SELECT 
    toDate(created_at) as date,
    COUNT(*) as total_purchases,
    JSONExtractString(JSONExtractRaw(e, 'query_params'), 'ep.transaction_id') as transaction_id,
    SUM(CAST(JSONExtractString(JSONExtractRaw(e, 'query_params'), 'epn.value') AS Float64)) as total_value
FROM default.analytics
ARRAY JOIN JSONExtractArrayRaw(properties, 'events') as e
WHERE created_at >= toDateTime('2025-02-17 00:00:00')
    AND created_at < toDateTime('2025-02-19 00:00:00')
    AND event_name = 'network_batch'
    AND JSONExtractString(e, 'source') = 'ga4'
    AND JSONExtractString(e, 'event_name') = 'purchase'
GROUP BY 
    date,
    transaction_id
ORDER BY total_value DESC