これは、トップトラフィックソースのダッシュボードレポートのSQLバージョンです。
このダッシュボードレポートは、指定された期間、およびオプションで特定のカテゴリまたはそのサブカテゴリ内のDiscourseサイトの投稿にリンクするトップの外部ソースの分析を提供します。
管理者は、どの外部ドメインがコミュニティにリンクしているかを特定することで、マーケティング活動の効果を評価し、より広範なオーディエンスを引き付けるためのコンテンツ戦略を洗練し、エンゲージメントの高いサイトとのコラボレーションの機会を探り、SEOを最適化し、不要なトラフィックソースから発生する可能性のある危機を管理できます。
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true
SELECT
ind.name AS domain,
COUNT(*) AS clicks,
COUNT(DISTINCT p.topic_id) AS topics
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN categories c ON c.id = t.category_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
OR t.category_id = :category_id
OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit
パラメータ
- 日付パラメータ:
- クエリは、レポートの日付範囲を定義する
:start_dateと:end_dateの2つの日付パラメータを受け入れます。両方の日付パラメータはYYYY-MM-DDの日付形式を受け入れます。
- クエリは、レポートの日付範囲を定義する
- カテゴリパラメータ:
:category_id: 特定のカテゴリのIDに設定して、そのカテゴリ内の投稿に分析を絞り込むことができる整数パラメータ。nullに設定されているか、提供されていない場合、すべてのカテゴリの投稿が考慮されます。:include_subcategories: 指定された:category_idのサブカテゴリの投稿を含めるかどうかを制御するブールパラメータ。trueに設定されている場合、レポートには指定されたカテゴリとそのサブカテゴリの両方の投稿へのリンクが含まれます。falseの場合、指定されたカテゴリのみが考慮されます。
- 結果の制限:
:limit: クリック数に基づいて上位N件の結果にクエリによって返される行数を制限する整数パラメータ。クエリで提供されるデフォルト値は10です。
SQLクエリの説明
- SELECT: レポートは、3つの情報の選択に構造化されています。
ind.name AS domain: この列には、フォーラムへの参照元となった各ドメインの名前が表示されます。COUNT(*) AS clicks: この列は、各ドメインからの着信リンククリックの総数を示します。COUNT(DISTINCT p.topic_id) AS topics: この列は、これらの着信リンクを通じてアクセスされたユニークなトピックの数を示します。
- FROM AND JOIN: クエリは複数のテーブルからデータを集計します。
incoming_links il: 着信リンクデータが格納されるプライマリテーブルです。posts p: フォーラムの投稿に関連するテーブルです。topics t: トピックデータを含むテーブルです。categories c: カテゴリに関する情報を含むテーブルです。incoming_referers ir: リファラーリンクに関する情報を格納するテーブルです。incoming_domains ind: フォーラムへのリンクの発信元ドメインをカタログ化するテーブルです。INNER JOINは、削除されていない投稿とトピックのみを考慮し、着信リンクデータを正しい投稿、トピック、および参照元ドメインにリンクすることを保証します。
- WHERE: このクエリの部分は、結果セットを絞り込むためのフィルターを適用します。
- レコードを「regular」アーキタイプのレコード（つまり、通常のフォーラムスレッド（プライベートメッセージやその他の特別なタイプとは対照的））に限定します。
- 指定された期間（
:start_dateから:end_date）内に作成されたリンクのみを含めるように日付をフィルターします。 - 提供された
:category_idパラメータに基づいてカテゴリフィルターを適用します。:category_idがnullの場合、すべてのカテゴリが考慮されます。:include_subcategoriesがtrueで:category_idがnullでない場合、指定されたカテゴリのサブカテゴリも含まれます。
- GROUP BY: クエリは、ドメイン名（
ind.name）でデータをグループ化し、カウントが各個別の参照元ドメインでパーティション化されることを保証します。 - ORDER BY and LIMIT: 最終的なデータセットは、クリック数に基づいて降順にソートされ、したがって最も高い参照トラフィックを持つドメインが優先されます。
LIMIT :limit句は、レポートを簡潔にし、トップドメインに焦点を当てるために、結果の数を制限します。
結果例
| domain | Clicks | Topics |
|---|---|---|
www.examplesite1.com |
1234 | 123 |
www.examplesite2.com |
56 | 67 |
www.examplesite3.com |
40 | 95 |
| … | … | … |