ダッシュボードレポート - トップリファラー

データとレポート
, ,
1

これは、トップリファラーのダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間内に投稿への着信リンククリックを最も多く生成したトップユーザーを表示します。

このレポートは、各ユーザーが受け取ったクリックの総数だけでなく、それらのクリックが分散されている個別のトピックの数も表示します。これにより、どのユーザーが外部ソースからフォーラムへのトラフィックを促進し、複数のディスカッションにわたるオーディエンスをエンゲージしているかを理解するのに役立ちます。

--[params]
-- date :start_date
-- date :end_date

SELECT
    il.user_id,
    COUNT(*) Clicks,
    COUNT(DISTINCT p.topic_id) Topics
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
    AND il.user_id > 0
    GROUP BY il.user_id
ORDER BY 2 DESC

SQLクエリの説明

このレポートに使用されるSQLクエリは、次のアクションを実行します。

  • 指定された日付範囲（:start_dateから:end_dateまで）内に作成された着信リンクのみを含めるように、着信リンクレコード（incoming_links）をフィルタリングします。
  • 着信リンクが有効なユーザーに関連付けられていることを確認します（user_id < 0のシステムまたは匿名ユーザーを除外します）。
  • incoming_linksテーブルとpostsテーブルを結合して、各リンククリックを対応するトピックに関連付けます。
  • 結果をuser_idでグループ化して、ユーザーごとにデータを集計します。
  • 各ユーザーの着信リンククリックの総数（Clicks）と個別のトピック数（Topics）をカウントします。
  • 結果を総クリック数の降順で並べ替えて、トップ貢献者を特定します。

パラメータ

  • :start_date: レポートを生成する日付範囲の開始日。
  • :end_date: レポートを生成する日付範囲の終了日。

両方のdateパラメータは、YYYY-MM-DDの形式を受け入れます。

結果例

ユーザー クリック トピック
username_1 5241 97
username_2 1256 14
username_3 1242 12
… … …
「いいね！」 4