クエリアドバイザー（α）

クエリアドバイザーとは？

クエリアドバイザーは、自然言語でクエリ作成をサポートする機能です。
SQLの知識がなくても、実行したい条件を日本語で入力するだけで、適切なクエリのアドバイスを取得できます。

こんな時に便利です

• SQLの構文がよく分からない
• どのテーブルやカラムを使えばいいのか迷う
• やりたいことは明確だが、クエリが思いつかない
• クエリの叩き台（たたき台）を手早く作りたい

活用の流れ

1. クエリ入力欄の左上にある虫眼鏡アイコンをクリック
2. 実行したい内容を日本語で入力（例：後述の「活用例」参照）
3. 「アドバイスを取得」をクリック
4. 提案されたクエリをそのまま実行、または編集して保存

具体的な活用例

以下のように自然な言葉で入力するだけで、目的に応じたクエリが自動で提案されます。

行動履歴

• 2024年3月1日から3月10日までのアクセスログ（日時、URL、ユーザーIDのみ）を抽出したい
  → 指定期間のログを絞り込むことで、特定キャンペーンや障害発生時の状況把握に活用できます。

    SELECT
      dt,
      url,
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.report_base`
    WHERE
      dt BETWEEN TIMESTAMP('2024-03-01') AND TIMESTAMP('2024-03-10')

• IPアドレス「172.16.0.10」で、パスが /product/detail で始まるアクセスを抽出
  → 特定のユーザーや社内端末からのアクセスを把握し、不正アクセスや内部利用状況の確認に役立ちます。

    SELECT
      dt,
      uid,
      ip,
      path
    FROM
      `connecty-cdp.＜サイト識別子＞_default.report_base`
    WHERE
      ip = '172.16.0.10'
      AND path LIKE '/product/detail%'

• 会員ID「MEMBER12345」のアクセス履歴をすべて表示して
  → 個別ユーザーの行動履歴を可視化し、問い合わせ対応やカスタマーサポートに活用できます。

    SELECT
      *
    FROM
      `connecty-cdp.＜サイト識別子＞_default.report_base`
    WHERE
      member_id = 'MEMBER12345'

• 直近1ヶ月のユニークユーザー数を日別に集計したい
  → サイト訪問者の推移を日単位で確認することで、日次トレンドや訪問者数の変化を把握できます。

    SELECT
      DATE(dt) AS log_date,
      COUNT(DISTINCT uid) AS unique_users
    FROM
      `connecty-cdp.＜サイト識別子＞_default.report_base`
    WHERE
      dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AND CURRENT_TIMESTAMP()
    GROUP BY
      log_date

• ここ数週間で特にセッション時間が長かったユーザー上位5名のIDと、その平均セッション時間。
  → 滞在時間が長いユーザーの特徴を把握し、優良顧客の傾向やエンゲージメントの高い行動を分析できます。

    SELECT
      report_base.uid,
      AVG(report_base.session_stay_seconds) AS average_session_stay_seconds
    FROM
      `connecty-cdp.＜サイト識別子＞_default.report_base` AS report_base
    WHERE
      report_base.dt BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7*3 DAY) AND CURRENT_TIMESTAMP()
    GROUP BY
      1
    ORDER BY
      average_session_stay_seconds DESC
    LIMIT 5

サーチコンソール

• 2023年の平均掲載順位が10位以内だった検索クエリだけ抽出して
  　→ 上位表示されていたキーワードを把握し、SEO強化の優先度決定に役立てます。

    SELECT
      query
    FROM
      `connecty-cdp.searchconsole_＜サイト識別子＞.searchdata_url_impression`
    WHERE
      data_date BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY
      1
    HAVING
      AVG(sum_position) <= 10

• 検索クエリが「BigQuery SQL 関数」のデータを 2024年2月分表示して
  → 特定のキーワードに関する検索状況を時期別に確認し、検索トレンドやニーズの変化を分析できます。

    SELECT
      data_date,
      site_url,
      url,
      query,
      impressions,
      clicks,
      sum_position
    FROM
      `connecty-cdp.searchconsole_＜サイト識別子＞.searchdata_url_impression`
    WHERE query = 'BigQuery SQL 関数'
      AND data_date BETWEEN '2024-02-01' AND '2024-02-29'

• 特定ページ（例：https://example.com/blog/sql-tips）への検索流入キーワードを出して
  　→ ページ別に流入元となっている検索ワードを確認し、ページ改善や拡張の方向性に活用可能です。

    SELECT
      searchdata_url_impression.query
    FROM
      `connecty-cdp.searchconsole_＜サイト識別子＞.searchdata_url_impression` AS searchdata_url_impression
    WHERE
      searchdata_url_impression.url = 'https://example.com/blog/sql-tips'

• デバイスが「MOBILE」のクリック数が多い順（上位10件）の検索クエリを今年分表示して
  → モバイルユーザーの検索傾向を掴むことで、デバイス別のSEO対策や表示コンテンツの最適化に役立ちます。

    SELECT
      query,
      SUM(clicks) AS total_clicks
    FROM
      `connecty-cdp.searchconsole_＜サイト識別子＞.searchdata_url_impression`
    WHERE
      device = 'MOBILE'
      AND data_date BETWEEN DATE_TRUNC(CURRENT_DATE(), YEAR) AND CURRENT_DATE()
    GROUP BY
      query
    ORDER BY
      total_clicks DESC
    LIMIT 10

• 検索クエリに「ユーザー分析」を含むデータを直近1ヶ月分見せて
  → 特定のテーマや施策に対する検索関心を把握し、コンテンツ強化やキャンペーン評価に活かせます。

    SELECT
      data_date,
      site_url,
      url,
      query,
      impressions,
      clicks,
      sum_position
    FROM
      `connecty-cdp.searchconsole_＜サイト識別子＞.searchdata_url_impression`
    WHERE query LIKE '%ユーザー分析%'
      AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) AND CURRENT_DATE()

GA

• GAの全期間で login イベントが発生したデータを抽出
  → サインイン動向を確認することで、アクティブユーザーやエンゲージメントの変化を把握できます。

    SELECT
      event_date,
      user_id,
      event_name,
      event_params
    FROM
      `connecty-cdp.analytics_＜GAプロパティID＞.events_*`
    WHERE event_name = 'login'

• user_properties.key が 'user_tier' で value が 'premium' のユーザーに関連するイベントを抽出
  → プレミアムユーザーの行動傾向を分析し、セグメント別の最適なコミュニケーション施策に活用できます。

    SELECT
      *
    FROM
      `connecty-cdp.analytics_＜GAプロパティID＞.events_*` AS T,
      UNNEST(user_properties) AS user_properties
    WHERE user_properties.key = 'user_tier'
      AND user_properties.value.string_value = 'premium'

• ちょうど半年前の同じ曜日に、特定のキャンペーンページ(utm_campaign=special)から流入したセッションの開始タイムスタンプ（マイクロ秒）
  　→ 同一曜日・同一キャンペーンの流入タイミングを比較分析することで、配信日の傾向把握や再施策時の参考情報として活用できます。

    SELECT
      event_timestamp
    FROM
      `connecty-cdp.analytics_＜GAプロパティID＞.events_*` AS T,
      UNNEST(T.event_params) AS params
    WHERE
      params.key = 'page_location'
      AND params.value.string_value LIKE '%utm_campaign=special%'
      AND EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_timestamp)) = EXTRACT(DAYOFWEEK FROM DATE_SUB(CURRENT_DATE(), INTERVAL 182 DAY))
      AND TIMESTAMP_MICROS(event_timestamp) BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 182 DAY), DAY) AND TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 182 DAY), INTERVAL 1 DAY), DAY)

• GAで event_name が login のイベントを先月分で抽出したい
  → イベントの発生タイミングと量を月次で比較・モニタリングすることで、ログイン促進施策の効果検証が行えます。

    SELECT
      *
    FROM
      `connecty-cdp.analytics_＜GAプロパティID＞.events_*` AS T
    WHERE T.event_name = 'login'
      AND FORMAT_TIMESTAMP('%Y%m', TIMESTAMP_MICROS(event_timestamp)) = '202503'

• GA ID xxxxxxxx のデータについて、昨日の午後3時から午後5時までのpage_viewイベントを抽出して。
  　→ 特定の時間帯におけるページ閲覧の動向を確認することで、施策直後の反応分析や障害発生時の影響把握、時間帯別のコンテンツ最適化などに活用できます。

    SELECT
      event_date,
      event_timestamp,
      user_pseudo_id,
      event_name
    FROM
      `connecty-cdp.analytics_＜GAプロパティID＞.events_*` AS T,
      UNNEST(event_params) AS params
    WHERE
      event_name = 'page_view'
      AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND CAST(event_timestamp as STRING) BETWEEN FORMAT_TIMESTAMP('%Y%m%d%H%M%S', TIMESTAMP_SUB(TIMESTAMP(CURRENT_DATE()), INTERVAL 15 HOUR)) AND FORMAT_TIMESTAMP('%Y%m%d%H%M%S', TIMESTAMP_SUB(TIMESTAMP(CURRENT_DATE()), INTERVAL 13 HOUR))

イベント

• 2024年2月中に「商品詳細閲覧」→「カート追加」→「購入完了」の順にイベントが発生したユーザーを抽出して 　→ コンバージョンファネルの通過率を見て、ボトルネックの特定に利用可能です

    WITH purchase_users AS (
      SELECT
        user_pseudo_id
      FROM
        `connecty-cdp.analytics_＜GAプロパティID＞.events_*`
      WHERE
        event_name = 'purchase'
        AND _TABLE_SUFFIX BETWEEN '20240201' AND '20240229'
    ), add_to_cart_users AS (
      SELECT
        user_pseudo_id
      FROM
        `connecty-cdp.analytics_＜GAプロパティID＞.events_*`
      WHERE
        event_name = 'add_to_cart'
        AND _TABLE_SUFFIX BETWEEN '20240201' AND '20240229'
    ), product_view_users AS (
      SELECT
        user_pseudo_id
      FROM
        `connecty-cdp.analytics_＜GAプロパティID＞.events_*`
      WHERE
        event_name = 'view_item'
        AND _TABLE_SUFFIX BETWEEN '20240201' AND '20240229'
    )
    SELECT DISTINCT
      p.user_pseudo_id
    FROM
      purchase_users AS p
    JOIN
      add_to_cart_users AS a ON p.user_pseudo_id = a.user_pseudo_id
    JOIN
      product_view_users AS v ON p.user_pseudo_id = v.user_pseudo_id

• イベントデータ（data列）に格納されている『評価点』（ratingキーと仮定）を集計して、平均評価点を出して
  → イベントに紐づく詳細情報からスコアや評価を抽出し、サービス満足度や利用評価の分析が可能です。

    SELECT
      AVG(SAFE_CAST(JSON_EXTRACT_SCALAR(data, '$.rating') AS FLOAT64)) AS average_rating
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      JSON_EXTRACT_SCALAR(data, '$.rating') IS NOT NULL

• 特定のイベントに紐づくユーザーIDごとの件数を抽出して
  → イベントの頻度や繰り返し発生の有無を確認し、リテンションや利用状況の把握に役立ちます。

    SELECT
      uid,
      count(name) AS event_count
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    GROUP BY
      1

• 直近7日間で発生したイベントを日付ごとに一覧で確認したい
  → 直近の動向や急な変化を把握するために、日次のモニタリングが可能になります。

    SELECT
      DATE(dt) AS event_date,
      name AS event_name,
      COUNT(*) AS event_count
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND CURRENT_TIMESTAMP()
    GROUP BY
      1, 2
    ORDER BY
      event_date,
      event_count DESC

• 特定イベント名（例：form_submit）の発生ユーザーとその発生回数を知りたい
  → 特定の行動をとったユーザーを特定し、リーチやアクション率の分析に活用できます。

    SELECT
      uid,
      COUNT(name) AS event_count
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'form_submit'
    GROUP BY
      uid

ポップアップ

• ポップアップID「popup-id」の表示数、クリック数、クリック率を知りたい
  → 各ポップアップの効果を数値で可視化することで、表示条件や内容の改善に役立てることができます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS popup_id,
      COUNTIF(name = 'popup-imp') AS popup_impressions,
      COUNTIF(name = 'popup-click') AS popup_clicks,
      SAFE_DIVIDE(
        COUNTIF(name = 'popup-click'),
        NULLIF(COUNTIF(name = 'popup-imp'), 0)
      ) AS click_rate
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name IN ('popup-imp', 'popup-click')
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'popup-id'
    GROUP BY
      popup_id

• 過去30日間で一度もクリックされていないポップアップを抽出したい
  → 効果の低い施策を早期に見直すための指標になります。

    SELECT DISTINCT JSON_EXTRACT_SCALAR(t1.data, '$.id') AS popup_id
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events` AS t1
    LEFT OUTER JOIN `connecty-cdp.＜サイト識別子＞_default.events` AS t2
      ON JSON_EXTRACT_SCALAR(t1.data, '$.id') = JSON_EXTRACT_SCALAR(t2.data, '$.id')
        AND t2.name = 'popup-click'
    WHERE t1.name = 'popup-imp'
      AND t1.dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND CURRENT_TIMESTAMP()
      AND t2.name IS NULL

• 「ポップアップ名」のポップアップをクリックしたユーザーが、クリック後に最初にアクセスしたページのURLを知りたい
  → クリック後の動線を把握することで、ユーザーの関心や導線設計の評価が可能になります。

    WITH PopupClicks AS (
      SELECT
        uid,
        dt,
        JSON_EXTRACT_SCALAR(data, '$.popup_name') AS popup_name,
        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt) AS click_rank
      FROM
        `connecty-cdp.＜サイト識別子＞_default.events`
      WHERE
        name = 'popup-click'
        AND JSON_EXTRACT_SCALAR(data, '$.popup_name') = '「ポップアップ名」'
    ),
    FirstPageViewsAfterClick AS (
      SELECT
        t1.uid,
        t1.url,
        ROW_NUMBER() OVER (PARTITION BY t1.uid ORDER BY t1.dt) AS pageview_rank
      FROM
        `connecty-cdp.＜サイト識別子＞_default.report_base` AS t1
      INNER JOIN
        PopupClicks AS t2 ON t1.uid = t2.uid
      WHERE
        t1.dt > t2.dt
    )
    SELECT
      t1.uid,
      t1.url
    FROM
      FirstPageViewsAfterClick AS t1
    WHERE
      t1.pageview_rank = 1

• 直近7日間のポップアップID「popup-id」の概要データを取得したい
  → 特定期間における表示回数や反応率を確認し、直近施策の効果測定に活用できます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS popup_id,
      COUNTIF(name = 'popup-imp') AS popup_impressions,
      COUNTIF(name = 'popup-close') AS popup_closes,
      COUNTIF(name = 'popup-click') AS popup_clicks,
      SAFE_DIVIDE(
        COUNTIF(name = 'popup-click'),
        NULLIF(COUNTIF(name = 'popup-imp'), 0)
      ) AS click_rate,
      MIN(dt) AS first_popup_dt,
      MAX(dt) AS last_popup_dt
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name IN ('popup-imp','popup-close','popup-click')
      AND dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
      AND CURRENT_TIMESTAMP()
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'popup-id'
    GROUP BY
      popup_id

• ポップアップが最も表示された時間帯を知りたい
  → 表示傾向から、配信タイミングの最適化に役立てられます。

    SELECT
      EXTRACT(HOUR FROM dt) AS hour,
      COUNT(*) AS popup_count
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'popup-imp'
    GROUP BY
      hour
    ORDER BY
      popup_count DESC
    LIMIT 1

コンテンツ出し分け

• コンテンツID「content_id」の表示回数と期間を教えて
  → 限定コンテンツやセグメント別施策の効果を把握することで、出し分け設計の最適化に役立ちます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS content_id,
      COUNT(*) AS display_count,
      MIN(dt) AS first_display,
      MAX(dt) AS last_display
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'content-imp'
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'content_id'
    GROUP BY
      content_id

• 2023年Q4（10月〜12月）のコンテンツID「campaign-banner-A」の表示概要を知りたい
  → 特定期間に実施したキャンペーン施策の閲覧状況を振り返ることで、過去施策の評価や比較に活用できます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS content_id,
      COUNT(*) AS display_count,
      MIN(dt) AS first_display,
      MAX(dt) AS last_display
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'content-imp'
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'campaign-banner-A'
      AND dt BETWEEN TIMESTAMP('2023-10-01') AND TIMESTAMP('2023-12-31')
    GROUP BY
      content_id

• コンテンツ出し分けの全体概要を教えて
  → 各コンテンツの表示状況を一覧で確認し、施策ごとのパフォーマンスを俯瞰できます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS content_id,
      COUNT(*) AS display_count,
      MIN(dt) AS first_display,
      MAX(dt) AS last_display
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'content-imp'
    GROUP BY
      content_id

• 直近1ヶ月のコンテンツ出し分けの状況（表示回数や期間）を知りたい
  → 最近の動きやトレンドを把握することで、今後の出し分け戦略を見直す参考になります。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS content_id,
      COUNT(*) AS display_count,
      MIN(dt) AS first_display,
      MAX(dt) AS last_display
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'content-imp'
      AND dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)) AND CURRENT_TIMESTAMP()
    GROUP BY
      content_id

• コンテンツID「content_id」の表示回数と、いつからいつまで表示されたかを教えて
  → 配信タイミングや期間の管理を通じて、表示ロジックや出し分け条件の見直しにつなげられます。

    SELECT
      JSON_EXTRACT_SCALAR(data, '$.id') AS content_id,
      COUNT(*) AS display_count,
      MIN(dt) AS first_display,
      MAX(dt) AS last_display
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'content-imp'
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'content_id'
    GROUP BY
      content_id

アンケート

• 『サービス改善』というタイトルのアンケートに回答したユーザーのuidを教えて
  → 特定テーマに関心を持ったユーザーを抽出することで、対象を絞ったフォローや分析に役立ちます。

    SELECT DISTINCT
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'anket-answer'
      AND JSON_EXTRACT_SCALAR(data, '$.answers[0].title') LIKE '%サービス改善%'

• アンケートの2番目の質問に対して『たまに』と回答したユーザーを教えて
  → 質問単位での集計・比較が可能になり、ユーザーの傾向をより詳細に把握できます。

    SELECT DISTINCT
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'anket-answer'
      AND JSON_EXTRACT_SCALAR(data, '$.answers[1].value') = 'たまに'

• 回答に『簡単』という言葉が含まれるアンケートに答えたユーザー一覧
  → 自由回答の傾向を分析し、ユーザーの本音や課題を探るヒントになります。

    SELECT DISTINCT
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'anket-answer'
      AND JSON_EXTRACT(data, '$') LIKE '%簡単%'

• 直近3ヶ月で、回答に『価格』が含まれるアンケートに答えたユーザーリスト
  → 最近の関心ワードやトレンドを把握することで、マーケティング施策に活かせます。

    SELECT DISTINCT
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'anket-answer'
      AND JSON_EXTRACT(data, '$') LIKE '%価格%'
      AND dt BETWEEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)) AND CURRENT_TIMESTAMP()

• アンケートID ‘anket_id’ に回答したが、『満足』とは答えなかったユーザーのリスト
  → 離反リスクのあるユーザーを特定し、個別対応や改善アクションにつなげることが可能です。

    SELECT DISTINCT
      uid
    FROM
      `connecty-cdp.＜サイト識別子＞_default.events`
    WHERE
      name = 'anket-answer'
      AND JSON_EXTRACT_SCALAR(data, '$.id') = 'anket_id'
      AND JSON_EXTRACT(data, '$') NOT LIKE '%満足%'

よくある質問

Q. クエリの内容はすべて正確ですか？

A. 自然言語の表現により差異が生じる場合があります。提案されたクエリは内容を確認の上、ご自身で編集することをおすすめします。

Q. 利用回数や制限はありますか？

A. アドバイスの取得回数には月ごとの上限があります。詳細はマニュアルページをご確認ください。

関連リンク

• クエリ機能のマニュアルを見る ↗

← 活用例一覧に戻る