---
title: "SQLクエリの作成"
slug: "writing-sql-queries"
abstract: "Sitecore CDPのデータレイクからエクスポートしたデータを、ゲスト、セッション、イベント、注文などのテーブルに対してSQLでクエリする方法。"
---
# SQLクエリの作成

SQLに詳しい方は、このトピックを使って「 [データへのアクセス](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/access-your-data.html "Access your data")」で説明しているように、組織のデータレイクからエクスポートしたデータに対してSQLクエリを書くのに役立ちます。データをクエリして、組織のサイトパフォーマンスに関する洞察を構築できます。

これらのクエリは6つの表から得られます:

-   [ゲスト](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/guests.html "Guests")
-   [セッション](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/sessions.html "Sessions")
-   [イベント](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/events.html "Events")
-   [命令](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/orders.html "Orders")
-   [注文品](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/order-items.html "Order items")
-   [経験の定義](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/data-entities-reference/experience-definitions.html "Experience definitions")

これらのテーブル間の関係は [エンティティ関係図](https://resources.doc.sitecore.com/cdp/en/resources/datalake-erd.png)で確認できます。データには事実(数値データ)と次元(非数値データ)が含まれます。

以下のSQLクエリは [、サマリーレポート](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html)でレンダリングされるのと同じデータを使用します。

## ゲストタイプのクエリ

組織のウェブサイトを訪れているゲストタイプ、特に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

このクエリは、タイプごとのユニークなゲストの数と割合を返します。

> !注サマリーレポートでは [、ゲストの種類](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html#UUID-c9ae60ab-96ce-b52e-2404-7e7fdddbd625_section-idm463710333440963285552209683) がドーナツチャートとして可視化されており、選択した日付範囲内の訪問者と顧客間の分布を簡単に確認できます。

## 新規訪問者や新規顧客へのクエリ

ウェブサイトへの新規トラフィックを監視するには、新規訪問者や新規顧客を初めて閲覧した日時から特定できます。まず、過去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

このクエリは以下の通りです:

-   [ゲストタイプのクエリ](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/writing-sql-queries.html#UUID-8857a739-5241-be2e-af84-1018930cc2a3_section-idm1903371375916372 "Querying guest types")に似た構造を使用しています。
-   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日間の [新規訪問者数と顧客](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html#UUID-c9ae60ab-96ce-b52e-2404-7e7fdddbd625_section-idm4611540153409632855522110441) 数の傾向として示されています。

## 注文項目のクエリ

このクエリを実行して、商品カテゴリごとの総額と注文量を把握できます。

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 を使用します。

> !注要約報告書の中で:
> 
> -   [注文品の価値](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html#UUID-5d892027-18da-ae5f-ddd4-fc43ac9f1b90_section-idm4611540098832032855558649723) は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

このクエリは、チャネルごとの総セッション数と割合を返します。

> !注サマリーレポートでは、[インバウンドチャネルとアウトバウンドチャネルが](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html#UUID-a06d26f9-20ee-55b5-0d95-554272112127) 別々のトレンドグラフとして表示されます。

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

ショッピングセッション中のユーザーの行動を理解するには、カートの種類を分析し、すべてのセッションにおける割合を測定できます。

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'

このクエリはカートタイプごとのセッション数と総セッションの割合を返します。

> !注サマリーレポートでは、[セッションの種類とカートの割合](https://doc.sitecore.com/cdp/en/users/sitecore-cdp/en/summary-report.html#UUID-f3a4906e-52b2-8d30-f634-51689deb7098_section-idm457370934334403381586124851) が別々のグラフとして表示されます。

## 日・時間ごとのコンバージョン検索

ユーザーがコンバージョンしやすい時間帯を理解するには、曜日や時間帯ごとのセッションレベルのコンバージョンを分析できます。このクエリは [カートタイプのクエリ](https://doc.sitecore.com/cdp/ja/developers/api/data-lake-export-service/writing-sql-queries.html#UUID-8857a739-5241-be2e-af84-1018930cc2a3_section-idm234567507911374 "Querying cart types and proportions")から取得したデータを基に構築されます。

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<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\_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パラメータを抽出し、異なるキャンペーンやトラフィックソースがユーザーセッションにどのように貢献するかの洞察を得ることができます。