データレイクエクスポートサービス

データレイクエクスポートサービス
SQL クエリの記述

SQLクエリの記述

日本語翻訳に関する免責事項

このページの翻訳はAIによって自動的に行われました。可能な限り正確な翻訳を心掛けていますが、原文と異なる表現や解釈が含まれる場合があります。正確で公式な情報については、必ず英語の原文をご参照ください。

SQLに精通している場合は、このトピックを使用して、「データへのアクセス」で説明されているように、組織のデータレイクからエクスポートされたデータに対してSQLクエリを記述します。データをクエリして、組織のサイトパフォーマンスに関する洞察を構築できます。

これらのクエリは、次の6つのテーブルから取得されます。

これらのテーブル間の関係は、エンティティ関係図で確認できます。データには、ファクト (数値データ) とディメンション (非数値データ) が含まれます。

次のSQLクエリでは、概要レポートでレンダリングされるのと同じデータが使用されます。

ゲストタイプのクエリ

このクエリは、組織のWebサイトを訪問しているゲストの種類 (訪問者または顧客) の割合を特定するための最初のステップとして実行できます。

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

このクエリでは、次の処理が行われます。

guestsテーブルをクエリし、一意のゲスト識別子であるmeta_refと、各meta_refのゲストタイプを抽出します。
guestsテーブルのmeta_refとsessionsテーブルのmeta_guest_refを使用してguestsテーブルをsessionsテーブルと結合し、channelやpoint of saleなどのセッションデータにアクセスします。
sessionsテーブルのmeta_created_at_dateを使用して、8日以内のデータを取得します。
最適化のためにwhere句を使用してmeta_created_at_date列でデータをフィルター処理します。これは、sessionsテーブルがこの列によってパーティション分割されているためです。
COUNT() 関数を使用してデータを集計し、各ゲストタイプのゲスト数を取得します。

データをクエリした後、任意のビジネスインテリジェンス (BI) ツールを使用して、ゲストタイプ情報を表示できます。サマリーレポートでは、ゲストタイプがドーナツグラフとして視覚化されるため、訪問者と顧客の間の分布を簡単に確認できます。

新規訪問者と新規顧客のクエリ

このクエリを実行すると、特定の期間における新規訪問者と新規顧客の傾向を表示できます。

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列にwhere句が適用されます。これにより、過去8日間に最初に表示されたゲストのみが含まれるようにデータがフィルタリングされ、新しい訪問者と新しい顧客に焦点を当てることができます。
COUNT() 関数を使用してデータを集計し、meta_refをカウントし、新規訪問者と新規顧客の数を決定します。

サマリーレポートには、新規訪問者数と新規顧客数が過去8日間のトレンドとして表示されます。

注文明細のクエリ

このクエリを実行して、製品カテゴリ別の注文の合計金額と注文量をキャプチャできます。

select distinct
             ,oi.meta_created_at
             ,oi.type
             ,oi.bxt['channel'] as core_channel
             ,oi.core_price
             ,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')

このクエリでは、次の処理が行われます。

order_itemsテーブルをクエリし、キーと値のペアを格納するbxtフィールドからcore_channelを抽出します。
ordersテーブルのmeta_refとorder_itemsテーブルのmeta_order_refを使用して、ordersテーブルをorder_itemsテーブルと結合します。ordersテーブルとorder_itemsテーブルはどちらも、どのカラムを使用してもパーティション化されていないことに注意してください。
core_statusをPURCHASEDまたはCONFIRMEDに設定して、価値を生成した注文のみに焦点を当てます。

概要レポートでは、注文品目の値を表示するために、core_priceにcore_quantityを乗算してから集計します。このレポートには、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を使用してパーティション化されたsessionsテーブルをクエリし、最適化のためにこのカラムにwhere句を適用します。
core_channelがnullである行 (通常はデータキャプチャの失敗によるもの) は除外されます。
COUNT() 関数を使用してデータを集計します。

データをクエリした後、core_channel列のインバウンドチャネルとアウトバウンドチャネルを区別できるほかのSQLクエリを使用するか、使用しているBIツールに応じてカスタムフィールドを使用します。サマリー・レポートには、インバウンド・チャネルとアウトバウンド・チャネルの傾向が別々のグラフで表示されます。

カートの種類と比率のクエリ

このクエリを実行して、カートの種類と、セッションの合計数に対するその比率をキャプチャできます。放棄されたセッション(アイテムがカートに追加されたが購入されなかった)、変換されたセッション(購入が行われた)、および参照されたセッション(カートインタラクションなし)の数が表示されます。

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カラムにwhere句を適用します。これは、sessionsテーブルがこのカラムによってパーティション化されているためです。
sessionsテーブルのmeta_created_at_dateを使用して、8日以内のデータを取得します。

カートの種類で結果をフィルタリングして、特定のデータに焦点を当てることができます。同じクエリを使用して、すべてのセッションのうちの各カートタイプの数または比率を確認できます。ビジネスニーズと必要な詳細レベルに応じて、カートタイプをトレンドまたは単一の値として表示できます。

サマリーレポートには、セッションタイプとカートの割合が別々のグラフで表示されます。

日と時間によるコンバージョンのクエリ

このクエリを実行して、日別および時間別のコンバージョン率の分布を計算できます。このクエリは、前のクエリから取得したデータに基づいて構築され、カートの種類とその比率を取得します。

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
             ,core_channel
             ,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

このクエリでは、次の処理が行われます。

meta_created_atタイムスタンプフィールドを使用して、時刻などの時間関連のメトリクスを抽出し、meta_created_at_dateフィールドを使用して日付を抽出します。BIツールによっては、曜日と時間を抽出する他の関数を使用できます。
COUNT() 関数を使用してデータを集計し、変換されたセッションの数を時間単位と曜日ごとに計算します。
cartTypeを分母として使用して、すべてのセッションタイプのうち変換されたセッションの割合を計算します。

イベント・タイプのクエリ

このクエリを実行して、閲覧、検索、カートへのアイテムの追加、購入の完了など、さまざまなユーザー操作イベントを抽出できます。

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')

このクエリでは、次の処理が行われます。

eventsテーブルに格納されているすべての基本的なイベントタイプ (VIEW、SEARCH、PAYMENT、CLEAR_CART、CONFIRM、CHECKOUTなど) を抽出します。
sessionsテーブルのmeta_refとeventsテーブルのmeta_session_refを使用して、eventsテーブルをsessionsテーブルにジョインします。meta_refは、各セッションの一意の識別子です。
meta_created_atフィールドとmeta_created_at_dateフィールドを使用して、イベントが発生した日時を識別します。

COUNT() 関数を使用して、クエリからのデータを集計し、各イベントタイプのセッション数を見つけることができます。また、1つのVIEWイベントのみを含むセッションの数をカウントして、バウンスされたセッションの数を計算するためにも使用できます。

エクスペリエンス定義テーブルのクエリ

このクエリは、Sitecore Personalizeのフロー設定情報を格納するディメンションテーブルであるexperience_definitionsテーブルに対して実行できます。また、eventsテーブルをクエリして、実験やエクスペリエンスに関するデータを取得します。

このクエリは、エクスペリエンスと実験で使用されるTRACKINGイベントタイプを使用していることを前提としています。

select core_friendly_id
             ,TRANSFORM(cast(json_parse(json_format(cast(core_variants as json))) as ARRAY<JSON>),x->JSON_EXTRACT_SCALAR(x,'$[1]')) as variant_name
             ,TRANSFORM(cast(json_parse(json_format(cast(core_traffic.splits as json))) as ARRAY<JSON>),x->JSON_EXTRACT_SCALAR(x,'$[3]')) as split
             ,e.flow_execution_flow_ref
             ,e.flow_execution_flow_variant_ref
             ,TRANSFORM(cast(json_parse(json_format(cast(core_variants as json))) as ARRAY<JSON>),x->JSON_EXTRACT_SCALAR(x,'$[2]')) as is_Control
			 ,e.meta_session_ref
	  from events e
	  join experience_definitions ed
	  ON e.flow_execution_flow_ref=ed.meta_ref
	  where meta_created_at_date >= current_date - interval '8' day
      and meta_created_at_date <= current_date - interval '1' day

このクエリでは、次の処理が行われます。

eventsテーブルをクエリして、flow_ref、flow_variant_ref、meta_session_ refをキャプチャします。
experience_definitionsテーブルから追加情報 (flow_reffriendly_id、バリアント名、A/B testバリアントがコントロールまたはテストとして設定されているかどうかなど) を取得します。
eventsテーブルのflow_execution_flow_refとexperience_definitionsテーブルのmeta_refを使用して、eventsテーブルをexperience_definitionsテーブルと結合します。
A/Bテスト中に制御グループとテストグループ間のトラフィック分割の詳細をcore_trafficフィールドから取得し、core_scheduleフィールドを使用してテストの開始日とテストが最後に実行された日時を判別します。
flow_execution_created_atタイムスタンプフィールドに基づいて結果をフィルタリングします。

BIツールやプログラミング言語によっては、core_variant列、core_schedule列、core_traffic列からさまざまな方法でデータを抽出できます。 core_variant列は配列であり、core_schedule列、およびcore_traffic列は構造化(または構造体)タイプであることに注意してください。

UTMパラメータのクエリ

訪問者セッションデータの一部としてUrchin Tracking Module(UTM)パラメータを使用している場合は、このクエリを実行してサイトトラフィックを抽出できます。

Select bxt['utmCampaign'] as utmCampaign from sessions 
where bxt['utmCampaign'] is not null 
limit 100

このクエリでは、次の処理が行われます。

sessionsテーブルをクエリし、bxtフィールドからutmCampaign文字列を抽出します。この文字列は、UTMソース、キャンペーン、メディア、用語、またはコンテンツとして格納されます。
結果をフィルタして、utmCampaignがnullでない行のみを含めます。
クエリを最新の100件のUTMキャンペーンを返すように制限します。

同様のクエリを使用して、ソース、メディア、ターム、コンテンツなどの特定のUTMパラメータを抽出し、さまざまなキャンペーンやトラフィックソースがユーザーセッションにどのように貢献しているかを分析できます。

この記事を改善するための提案がある場合は、お知らせください!