SQLクエリの作成
このページの翻訳はAIによって自動的に行われました。可能な限り正確な翻訳を心掛けていますが、原文と異なる表現や解釈が含まれる場合があります。正確で公式な情報については、必ず英語の原文をご参照ください。
SQLに詳しい方は、このトピックを使って「 データへのアクセス」で説明しているように、組織のデータレイクからエクスポートしたデータに対してSQLクエリを書くのに役立ちます。データをクエリして、組織のサイトパフォーマンスに関する洞察を構築できます。
これらのクエリは6つの表から得られます:
これらのテーブル間の関係は エンティティ関係図で確認できます。データには事実(数値データ)と次元(非数値データ)が含まれます。
以下のSQLクエリは 、サマリーレポートでレンダリングされるのと同じデータを使用します。
ゲストタイプのクエリ
組織のウェブサイトを訪れているゲストタイプ、特にVISITOR人とCUSTOMER人の割合を特定するには、まず関連するゲストセッション記録を抽出することから始めてください。
select type as guest_type, s.core_channel, g.core_first_seen_at, g.meta_ref from guests g left join sessions s on s.meta_guest_ref = g.meta_ref where g.type in ('VISITOR','CUSTOMER') and s.meta_created_at_date >= current_date - interval '8' day and s.meta_created_at_date <= current_date - interval '1' day
このクエリは以下の通りです:
- 過去8日間(今日を除く)のすべてのゲストセッションの組み合わせを取得します。
- フィルターは VISITOR と CUSTOMER ゲストタイプのみを含みます。
- セッションレベルのデータ( core_channelなど)をゲストレコードと meta_ref 識別子で結合します。
- テーブルパーティショニングを活用するために meta_created_at_date でフィルタリングします。
データを抽出した後、ビジネスインテリジェンス(BI)ツール(Power BI、Tableau、Looker)を使って、guest_typeごとにグループ化し、異なるmeta_ref数を数えることで、タイプごとのユニークなゲスト数とその割合を計算できます。
または、このSQLクエリを使うこともできます:
select g.type as guest_type, count(distinct g.meta_ref) as guest_count, round( count(distinct g.meta_ref) * 100.0 / sum(count(distinct g.meta_ref)) over (), 2 ) as guest_percentage from guests g left join sessions s on s.meta_guest_ref = g.meta_ref where g.type in ('VISITOR','CUSTOMER') and s.meta_created_at_date >= current_date - interval '8' day and s.meta_created_at_date <= current_date - interval '1' day group by g.type
このクエリは、タイプごとのユニークなゲストの数と割合を返します。
!注サマリーレポートでは 、ゲストの種類 がドーナツチャートとして可視化されており、選択した日付範囲内の訪問者と顧客間の分布を簡単に確認できます。
新規訪問者や新規顧客へのクエリ
ウェブサイトへの新規トラフィックを監視するには、新規訪問者や新規顧客を初めて閲覧した日時から特定できます。まず、過去8日間以内にサイトに初めて表示されたVISITORおよびCUSTOMERの記録をすべて抽出します:
select g.type, s.core_channel, g.core_first_seen_at, g.meta_ref from guests g left join sessions s on s.meta_guest_ref = g.meta_ref where g.type in ('VISITOR','CUSTOMER') and g.core_first_seen_at >= current_date - interval '8' day and g.core_first_seen_at <= current_date - interval '1' day and s.meta_created_at_date >= current_date - interval '8' day and s.meta_created_at_date <= current_date - interval '1' day
このクエリは以下の通りです:
- ゲストタイプのクエリに似た構造を使用しています。
- core_first_seen_atでフィルターをかけて、指定された期間内に新しく来たゲストを特定します。
- s.meta_created_at_dateごとにフィルターをかけて、セッションが同じ期間内に収まるようにします。
- セッションデータを統合し、チャネルやタイムスタンプごとに分割できるようにします。
データを抽出した後、BIツールを使ってguest_typeごとにグループ化し、異なるゲスト数をカウントしてタイプごとのユニークな新規ゲスト数を計算meta_ref。
または、このSQLクエリを使うこともできます:
select g.type as guest_type, s.core_channel, g.core_first_seen_at, count(distinct g.meta_ref) as new_guest_count from guests g left join sessions s on s.meta_guest_ref = g.meta_ref where g.type in ('VISITOR', 'CUSTOMER') and g.core_first_seen_at >= current_date - interval '8' day and g.core_first_seen_at <= current_date - interval '1' day and s.meta_created_at_date >= current_date - interval '8' day and s.meta_created_at_date <= current_date - interval '1' day group by g.type, s.core_channel, g.core_first_seen_at
このクエリはゲストタイプ、チャネル、初回閲覧日でグループ化されます。また、複数のセッションで重複カウントを避けるため、異なるゲスト数もカウントします。
!注サマリーレポートでは、過去8日間の 新規訪問者数と顧客 数の傾向として示されています。
注文項目のクエリ
このクエリを実行して、商品カテゴリごとの総額と注文量を把握できます。
select distinct oi.meta_created_at, oi.type, oi.bxt'channel' as core_channel, oi.core_price, oi.core_quantity from order_items oi join orders o on o.meta_ref = oi.meta_order_ref where oi.meta_created_at >= current_date - interval '8' day and oi.meta_created_at <= current_date - interval '1' day and oi.core_status in ('PURCHASED','CONFIRMED')
このクエリは以下の通りです:
- 過去7日間に作成された注文商品を PURCHASED または CONFIRMEDのステータスで選択します。
- 必要に応じて、 orders テーブルと order_items テーブルを順序参照(meta_ref および meta_order_ref)で結合し、データセットを豊かにします。
- bxt JSON様フィールドからcore_channelを抽出します。
- 各品物の単価と数量を取得します。
- 重複する行を避けるために distinct を使用します。
!注要約報告書の中で:
注文品の価値 はcore_price×core_quantityを掛けて集計して計算されます。
注文品の数量は次のように計算されます SUM(core_quantity)
インバウンドおよびアウトバウンドチャネルのクエリ
トラフィックパターンを分析するには、最近のゲストセッションにおけるインバウンドチャネルとアウトバウンドチャネルを追跡できます:
select meta_created_at_date, core_channel, core_point_of_sale, meta_ref from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day and core_channel is not null
このクエリは以下の通りです:
- 効率的な実行のためにパーティション化された列 meta_created_at_date でセッションをフィルタリングします。
- 無効core_channelのセッションは除外します。これらは通常、欠損または不完全なデータによって引き起こされます。
- 日付、チャネル、販売時点情報、ゲスト識別子を取得し、下流分析を行います。
データを抽出した後、BIツールを使ってチャンネルごとのセッション数を、core_channelごとにグループ化し、数えてカウントmeta_ref。また、定義に基づいて各core_channelを インバウン ドかアウトバ ウン ドにラベル付けするカスタムフィールドを作成することもできます。
または、このSQLクエリを使うこともできます:
select core_channel, count(meta_ref) as session_count, round( count(meta_ref) * 100.0 / sum(count(meta_ref)) over (), 2 ) as channel_percentage from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day and core_channel is not null group by core_channel
このクエリは、チャネルごとの総セッション数と割合を返します。
!注サマリーレポートでは、インバウンドチャネルとアウトバウンドチャネルが 別々のトレンドグラフとして表示されます。
クエリカートの種類と比率
ショッピングセッション中のユーザーの行動を理解するには、カートの種類を分析し、すべてのセッションにおける割合を測定できます。
select meta_created_at_date, bxt'cartType' as cart_type, core_point_of_sale, meta_ref from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day
このクエリは以下の通りです:
- sessionsテーブルのbxtフィールドからcartTypeを取得します。
- 効率的なパフォーマンスのために、 meta_created_at_date、分割された列でフィルタリングします。
- 過去8日以内のすべてのセッションを返却します。
cart_typeは以下のいずれかです:
- BROWSED - カートとの相互作用なし
- ABANDONED - カートに追加されたものの購入されていない商品
- CONVERTED - 購入完了
データを抽出した後、BIツールを使ってcart_typeごとにグループ化し、各カートリッジタイプのセッション数と割合を計算meta_ref。
または、このSQLクエリを使うこともできます:
select bxt'cartType' as cart_type, count(meta_ref) as session_count, round( count(meta_ref) * 100.0 / sum(count(meta_ref)) over (), 2 ) as cart_type_percentage from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day group by bxt'cartType'
このクエリはカートタイプごとのセッション数と総セッションの割合を返します。
!注サマリーレポートでは、セッションの種類とカートの割合 が別々のグラフとして表示されます。
日・時間ごとのコンバージョン検索
ユーザーがコンバージョンしやすい時間帯を理解するには、曜日や時間帯ごとのセッションレベルのコンバージョンを分析できます。このクエリは カートタイプのクエリから取得したデータを基に構築されます。
select meta_created_at_date, day_of_week(meta_created_at_date) as day_of_week, core_channel, core_point_of_sale, extract(hour from meta_created_at) as hour_of_day, bxt'cartType' as cartType, meta_ref from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day and bxt'cartType' is not null
このクエリは以下の通りです:
- セッションのタイムスタンプから day_of_week と hour_of_day を抽出します。
- cartTypeがいるセッションのみを含むフィルターを設置し、閲覧、放棄、変換などの関与を示します。
- チャネルや販売時点情報などの追加メタデータを返します。
データを抽出した後、BIツールを使ってday_of_week、hour_of_day、cart_typeでグループ化し、カウントmeta_refをカウントしてカートタイプごとのセッションボリュームを計算します。
または、このSQLクエリを使うこともできます:
select day_of_week(meta_created_at_date) as day_of_week, extract(hour from meta_created_at) as hour_of_day, bxt'cartType' as cart_type, count(meta_ref) as session_count from sessions where meta_created_at_date >= current_date - interval '8' day and meta_created_at_date <= current_date - interval '1' day and bxt'cartType' is not null group by day_of_week, hour_of_day, bxt'cartType' order by day_of_week, hour_of_day
このクエリは、日・時間の組み合わせごとにカートタイプごとのセッション数を返します。BIツールで結果をピボット化して、以下のようにコンバージョン率を計算できます:
conversion rate = COUNT(CONVERTED) / COUNT(ALL_CART_TYPES)
クエリイベントの種類
ウェブサイトやプラットフォーム上のユーザーインタラクションを分析するには、コンテンツ閲覧、検索、カート追加、購入完了などのセッションレベルのイベントタイプを抽出できます。
select s.meta_ref ,s.meta_created_at_date ,e.type ,e.core_channel ,e.core_point_of_sale from sessions s join events e ON s.meta_ref=e.meta_session_ref where e.meta_created_at_date >= current_date - interval '8' day and e.meta_created_at_date <= current_date - interval '1' day and e.type IN ('VIEW','SEARCH','PAYMENT','CLEAR_CART','CONFIRM','CHECKOUT')
このクエリは以下の通りです:
- meta_refとmeta_session_refを使って、ユーザー操作のためのセッションコンテキストを提供するために、eventsとsessionsテーブルを接続します。
- 6つの一般的なイベントタイプに対するフィルター。
- meta_created_at_dateを使って過去8日間のデータを取得します。
データを抽出した後、BIツールを使って、event_typeごとにグループ化し、それぞれのイベントタイプがどれくらい頻繁に発生するかを測定するために、異なるmeta_session_refをカウントします。
または、このSQLクエリを使うこともできます:
select e.type as event_type, count(distinct e.meta_session_ref) as session_count from events e where e.meta_created_at_date >= current_date - interval '8' day and e.meta_created_at_date <= current_date - interval '1' day and e.type in ('VIEW', 'SEARCH', 'PAYMENT', 'CLEAR_CART', 'CONFIRM', 'CHECKOUT') group by e.type order by session_count desc
このクエリは、各イベントタイプに関連付けられたユニークなセッション数を返し、異なる段階でのユーザーエンゲージメントを定量化するのに役立ちます。
経験定義テーブルのクエリ
フロー構成やパフォーマンスを分析するには、experience_definitions次元テーブルとeventsテーブルを組み合わせることができます。これにより、どのバリアントユーザーが見たか、対照群かテスト群か、分割比率が明らかになります。
このクエリは、体験や実験で使われるTRACKINGイベントタイプを扱っていることを前提としています。
select core_friendly_id
,TRANSFORM(cast(json_parse(json_format(cast(core_variants as json))) as ARRAY
このクエリは以下の通りです:
- eventsテーブルからflow_ref、flow_variant_ref、meta_session_ refを取得します。
- experience_definitionsテーブルから、flow_refのfriendly_id、バリアント名、A/Bテストバリアントがコントロールかテストかなどの追加情報を取得します。
- eventsテーブルとexperience_definitionsテーブルをeventsテーブルのflow_execution_flow_refとexperience_definitionsテーブルのmeta_refで接続します。
- A/Bテスト中に制御グループとテストグループに分割されたトラフィック情報を core_traffic フィールドから取得し、 core_schedule フィールドを使ってテスト開始日と最後に実行された日を判定します。
- flow_execution_created_atタイムスタンプフィールドに基づいて結果をフィルタリングします。
BIツールやプログラミング言語によって、core_variant、core_schedule、core_traffic列からデータを抽出する方法は様々です。 core_variant列は配列であり、core_scheduleは、core_traffic列は構造化(または構造体)型です。
UTMパラメータのクエリ
もしあなたのウェブサイトがUTM(Urchin Tracking Module)パラメータを使ってキャンペーンを追跡しているなら、セッションデータからそれらを抽出してトラフィック源やキャンペーンのパフォーマンスを分析できます。
select bxt'utmCampaign' as utmCampaign from sessions where bxt'utmCampaign' is not null limit 100
このクエリは以下の通りです:
- bxtフィールドからutmCampaign値を取得します。
- フィルターは、 utmCampaign がnullでない行のみを含みます。
- 結果を直近の100件のUTMキャンペーンに限定します。
!注utmSource、utmMedium、utmCampaig、n、utmTerm、utmContentなどのUTMパラメータは通常、bxtフィールドにキーと値のペアとして格納されます。
同様のクエリを使って、ソース、中間、用語、コンテンツなどの特定のUTMパラメータを抽出し、異なるキャンペーンやトラフィックソースがユーザーセッションにどのように貢献するかの洞察を得ることができます。