Push Eventsテーブル

Copy

• Copy for LLM

  Copy page as Markdown for LLMs
• View as Markdown

  Open this page as Markdown
• Open in ChatGPT

  Get insights from ChatGPT
• Open in Claude

  Get insights from Claude
• Cursorに接続

  CursorにMCPサーバーをインストール
• VS Codeに接続

  VS CodeにMCPサーバーをインストール

-- サンプルpush_eventsテーブル作成（ニーズに合わせてカスタマイズしてください）
CREATE TABLE IF NOT EXISTS mobile.push_events (
  time INTEGER,
  type STRING,
  td_td_campaign_id STRING,
  platform STRING,
  fcm_token STRING,
  user_id STRING,
  value STRING,
  device_model STRING,
  os_version STRING,
  app_version STRING
)

{
  "time": 1734134400,
  "type": "delivery",
  "td_campaign_id": "cmp_20251214_promo",
    "platform": "android",
  "user_id": "user_12345"
}

{
  "time": 1734134410,
  "type": "open",
  "td_campaign_id": "cmp_20251214_promo",
    "platform": "ios",
  "user_id": "user_12345"
}

{
  "time": 1734134420,
  "type": "dismiss",
  "td_campaign_id": "cmp_20251214_promo",
    "platform": "android",
  "user_id": "user_12345"
}

{
  "time": 1734134430,
  "type": "link_open",
  "td_campaign_id": "cmp_20251214_promo",
    "platform": "android",
  "user_id": "user_12345",
  "value": "https://example.com/promo?utm_campaign=promo"
}

{
  "time": 1734134440,
  "type": "deeplink_open",
  "td_campaign_id": "cmp_20251214_promo",
    "platform": "ios",
  "user_id": "user_12345",
  "value": "myapp://product/12345"
}

{
  "time": 1734134400,
  "type": "token_register",
  "fcm_token": "dQw4w9WgXcQ:APA91bF...",
  "platform": "android",
  "user_id": "user_12345",
  "app_version": "1.2.3",
  "os_version": "14",
  "device_model": "Pixel 7"
}

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": "user_12345",
  "platform": "android"
}

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": null,
  "platform": "android"
}

{
  "type": "token_register",
  "fcm_token": "ABC123...",
  "user_id": "user_12345",
  "platform": "android"
}

WITH latest_tokens AS (
  SELECT
    fcm_token,
    user_id,
    time,
    ROW_NUMBER() OVER (PARTITION BY fcm_token ORDER BY time DESC) AS rn
  FROM mobile.push_events
  WHERE type = 'token_register'
    AND user_id IS NOT NULL
)
SELECT
  fcm_token,
  user_id
FROM latest_tokens
WHERE rn = 1

SELECT
  td_campaign_id,
  COUNT(CASE WHEN type = 'delivery' THEN 1 END) AS deliveries,
  COUNT(CASE WHEN type = 'open' THEN 1 END) AS opens,
  COUNT(CASE WHEN type = 'link_open' OR type = 'deeplink_open' THEN 1 END) AS clicks,
  COUNT(CASE WHEN type = 'dismiss' THEN 1 END) AS dismissals,
  ROUND(100.0 * COUNT(CASE WHEN type = 'open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS open_rate_pct,
  ROUND(100.0 * COUNT(CASE WHEN type = 'link_open' OR type = 'deeplink_open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS click_rate_pct
FROM mobile.push_events
WHERE time >= CAST(strftime('%s', 'now', '-30 days') AS INTEGER)
GROUP BY td_campaign_id
ORDER BY deliveries DESC

SELECT
  platform,
  COUNT(CASE WHEN type = 'delivery' THEN 1 END) AS deliveries,
  COUNT(CASE WHEN type = 'open' THEN 1 END) AS opens,
  ROUND(100.0 * COUNT(CASE WHEN type = 'open' THEN 1 END) / NULLIF(COUNT(CASE WHEN type = 'delivery' THEN 1 END), 0), 2) AS open_rate_pct
FROM mobile.push_events
WHERE td_campaign_id = 'cmp_20251214_promo'
GROUP BY platform

WITH delivery_times AS (
  SELECT
    td_campaign_id,
    user_id,
    fcm_token,
    time AS delivery_time
  FROM mobile.push_events
  WHERE type = 'delivery'
),
open_times AS (
  SELECT
    td_campaign_id,
    user_id,
    fcm_token,
    time AS open_time
  FROM mobile.push_events
  WHERE type = 'open'
)
SELECT
  CASE
    WHEN (o.open_time - d.delivery_time) < 60 THEN '< 1 minute'
    WHEN (o.open_time - d.delivery_time) < 300 THEN '1-5 minutes'
    WHEN (o.open_time - d.delivery_time) < 3600 THEN '5-60 minutes'
    WHEN (o.open_time - d.delivery_time) < 86400 THEN '1-24 hours'
    ELSE '> 24 hours'
  END AS time_bucket,
  COUNT(*) AS count
FROM delivery_times d
JOIN open_times o
  ON d.td_campaign_id = o.td_campaign_id
  AND d.user_id = o.user_id
  AND d.fcm_token = o.fcm_token
GROUP BY time_bucket
ORDER BY MIN(o.open_time - d.delivery_time)

SELECT
  td_campaign_id,
  value AS clicked_url,
  COUNT(*) AS click_count,
  COUNT(DISTINCT user_id) AS unique_users
FROM mobile.push_events
WHERE type IN ('link_open', 'deeplink_open')
  AND value IS NOT NULL
GROUP BY td_campaign_id, value
ORDER BY click_count DESC
LIMIT 20

SELECT
  DATE(time, 'unixepoch') AS event_date,
  platform,
  COUNT(DISTINCT fcm_token) AS active_tokens
FROM mobile.push_events
WHERE time >= CAST(strftime('%s', 'now', '-30 days') AS INTEGER)
  AND type IN ('delivery', 'open', 'link_open', 'deeplink_open')
GROUP BY event_date, platform
ORDER BY event_date DESC, platform

WITH funnel_data AS (
  SELECT
    user_id,
    MAX(CASE WHEN type = 'delivery' THEN 1 ELSE 0 END) AS delivered,
    MAX(CASE WHEN type = 'open' THEN 1 ELSE 0 END) AS opened,
    MAX(CASE WHEN type IN ('link_open', 'deeplink_open') THEN 1 ELSE 0 END) AS clicked
  FROM mobile.push_events
  WHERE td_campaign_id = 'cmp_20251214_promo'
  GROUP BY user_id
)
SELECT
  SUM(delivered) AS total_delivered,
  SUM(opened) AS total_opened,
  SUM(clicked) AS total_clicked,
  ROUND(100.0 * SUM(opened) / NULLIF(SUM(delivered), 0), 2) AS open_rate_pct,
  ROUND(100.0 * SUM(clicked) / NULLIF(SUM(opened), 0), 2) AS click_through_rate_pct
FROM funnel_data

-- 月次パーティションテーブルを作成
CREATE TABLE mobile.push_events_202512 AS
SELECT * FROM mobile.push_events
WHERE time >= 1733011200 AND time < 1735689600

CREATE TABLE mobile.push_events_archive AS
SELECT * FROM mobile.push_events
WHERE time < CAST(strftime('%s', 'now', '-90 days') AS INTEGER)

DELETE FROM mobile.push_events
WHERE time < CAST(strftime('%s', 'now', '-90 days') AS INTEGER)