More than 3 years have passed since last update.

Ingestlyでマルチチャネルアトリビューションレポートを作るSQL

Last updated at 2021-03-21Posted at 2021-03-21

Ingestlyでアクセスログを集計している中で、コンバージョン関連のレポートを他のツールと同じような基準で作成したい時があります。
（しっかり分析する場合は適さないけれど、評価するサイトやコンバージョン地点が多く、組織内で汎用化させたいとき等を想定）

そんなときに、最小の加工で転用できるコンバージョン評価用クエリーを作ってみました。
基本的にがGoogle AnalyticsのMCFのコンセプトに似せています。

用途に応じてカスタマイズすべき箇所

各クエリーには以下のコメント箇所があります。
用途に応じて書き換えてください。

#	コメント箇所	意味	例
1	コンバージョン発生期間	いつからいつまでに発生したコンバージョンを評価するか	`'2020-12-01' AND '2020-12-31'`
2	コンバージョンページのホスト名	コンバージョンイベントの発生箇所・ホスト名部分	`example.com`
3	コンバージョンページのパス名	コンバージョンイベントの発生箇所・パス部分	`/form/thanks_page.html`
4	campaign_codeを任意の分析軸に置き換え	何を軸にして評価を行うか、カラムを選択する	`campaign_source`
5	評価対象クリックの発生期間	いつからいつまでに発生したクリックをコンバージョンと紐付けるか	`'2020-11-01' AND '2020-11-30'`
6	サイトのホスト名	クリック=流入を評価する範囲	`example.com`
7	クリック〜コンバージョンの最長紐付け時間	クリックとコンバージョンの紐付け時間の長さ	`720`

12月のコンバージョンについて分析する場合、1 の期間は12月1日〜12月31日にします。
コンバージョンと紐付けるクリックを「コンバージョンの前30日以内は貢献した」と見なす場合、
5 の期間指定は 1 の「コンバージョン発生期間」と、その中で最も古い12月1日から遡って30日前となる11月1日を起点にする必要があります。
そして、紐付け期間を縛るため 7 の最長紐付け期間を30日x24時間= 720 と指定します。

最後の間接クリック

最後から2番目のクリックのみがコンバージョンに貢献したと見なされる。
GAのMCFのデフォルト

WITH
-- （共通） コンバージョンした訪問者の `ingestly_id` と コンバージョンしたタイムスタンプの最大（最後のコンバージョン）を取り出す。
conversions AS (
    SELECT 
        ingestly_id,
        MAX(timestamp) AS date_time
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-03-01' AND '2021-03-31' -- コンバージョン発生期間
        AND action='view'
        AND category='page'
        AND url_hostname = 'your_domain' -- コンバージョンページのホスト名
        AND url_pathname = '/thanks.html'  -- コンバージョンページのパス名
    GROUP BY
        1
),
-- （共通） コンバージョンした訪問者の流入履歴を取り出す。
clicks AS (
    SELECT 
        timestamp AS date_time,
        campaign_code AS breakdown_col, -- campaign_codeを任意の分析軸に置き換え
        ingestly_id AS user
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-02-01' AND '2021-03-31' -- 評価対象クリックの発生期間
        AND action='view'
        AND category='page'
        AND ingestly_id IN (SELECT ingestly_id FROM conversions)
        AND url_hostname = 'your_domain' -- サイトのホスト名
        AND referrer_hostname != url_hostname
),
-- クリックとコンバージョンのタイムスタンプを比較可能にし、クリックの順序（新しい順）で採番する。
raw_result AS (
    SELECT
        clicks.date_time AS click_ts,
        conversions.date_time AS conversion_ts,
        clicks.user,
        ROW_NUMBER () OVER (PARTITION BY clicks.user
                         ORDER BY clicks.date_time DESC) AS sqe_num,
        clicks.breakdown_col
    FROM
        clicks
    LEFT JOIN
        conversions ON clicks.user = conversions.ingestly_id
    WHERE 
        clicks.date_time < conversions.date_time
        OR TIMESTAMP_DIFF(conversions.date_time, clicks.date_time, HOUR) <= 720 -- クリック〜コンバージョンの最長紐付け時間
)
-- 2番目に新しいクリックを足し上げる。
SELECT
    breakdown_col AS Variable,
    count(DISTINCT user) AS Conversions
FROM
    raw_result
WHERE
    sqe_num = 2 -- 最後から2番目
GROUP BY
    1
ORDER BY
    2 DESC;

終点（最後のクリック・Last Touch）

よくあるコンバージョン評価のロジック
コンバージョンまでに10回広告をクリックしても、最後の1回のみが貢献と見なされる

WITH
-- （共通） コンバージョンした訪問者の `ingestly_id` と コンバージョンしたタイムスタンプの最大（最後のコンバージョン）を取り出す。
conversions AS (
    SELECT 
        ingestly_id,
        MAX(timestamp) AS date_time
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-03-01' AND '2021-03-31' -- コンバージョン発生期間
        AND action='view'
        AND category='page'
        AND url_hostname = 'your_domain' -- コンバージョンページのホスト名
        AND url_pathname = '/thanks.html'  -- コンバージョンページのパス名
    GROUP BY
        1
),
-- （共通） コンバージョンした訪問者の流入履歴を取り出す。
clicks AS (
    SELECT 
        timestamp AS date_time,
        campaign_code AS breakdown_col, -- campaign_codeを任意の分析軸に置き換え
        ingestly_id AS user
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-02-01' AND '2021-03-31' -- 評価対象クリックの発生期間
        AND action='view'
        AND category='page'
        AND ingestly_id IN (SELECT ingestly_id FROM conversions)
        AND url_hostname = 'your_domain' -- サイトのホスト名
        AND referrer_hostname != url_hostname
),
-- クリックとコンバージョンのタイムスタンプを比較可能にし、クリックの順序（新しい順）で採番する。
raw_result AS (
    SELECT
        clicks.date_time AS click_ts,
        conversions.date_time AS conversion_ts,
        clicks.user,
        ROW_NUMBER () OVER (PARTITION BY clicks.user
                         ORDER BY clicks.date_time DESC) AS sqe_num, -- クリックが新しい順に採番
        clicks.breakdown_col
    FROM
        clicks
    LEFT JOIN
        conversions ON clicks.user = conversions.ingestly_id
    WHERE 
        clicks.date_time < conversions.date_time
        OR TIMESTAMP_DIFF(conversions.date_time, clicks.date_time, HOUR) <= 720 -- クリック〜コンバージョンの最長紐付け時間
)
-- 最も新しいクリックを足し上げる。
SELECT
    breakdown_col AS Variable,
    count(DISTINCT user) AS Conversions
FROM
    raw_result
WHERE
    sqe_num = 1
GROUP BY
    1
ORDER BY
    2 DESC;

起点（最初のクリック・First Touch）

所謂ファーストタッチチャネルと言われるもの。
10回のクリックを経てのコンバージョンであっても最初の1回のみが貢献と見なされる。

WITH
-- （共通） コンバージョンした訪問者の `ingestly_id` と コンバージョンしたタイムスタンプの最大（最後のコンバージョン）を取り出す。
conversions AS (
    SELECT 
        ingestly_id,
        MAX(timestamp) AS date_time
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-03-01' AND '2021-03-31' -- コンバージョン発生期間
        AND action='view'
        AND category='page'
        AND url_hostname = 'your_domain' -- コンバージョンページのホスト名
        AND url_pathname = '/thanks.html'  -- コンバージョンページのパス名
    GROUP BY
        1
),
-- （共通） コンバージョンした訪問者の流入履歴を取り出す。
clicks AS (
    SELECT 
        timestamp AS date_time,
        campaign_code AS breakdown_col, -- campaign_codeを任意の分析軸に置き換え
        ingestly_id AS user
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-02-01' AND '2021-03-31' -- 評価対象クリックの発生期間
        AND action='view'
        AND category='page'
        AND ingestly_id IN (SELECT ingestly_id FROM conversions)
        AND url_hostname = 'your_domain' -- サイトのホスト名
        AND referrer_hostname != url_hostname
),
-- クリックとコンバージョンのタイムスタンプを比較可能にし、クリックの順序（古い順）で採番する。
raw_result AS (
    SELECT
        clicks.date_time AS click_ts,
        conversions.date_time AS conversion_ts,
        clicks.user,
        ROW_NUMBER () OVER (PARTITION BY clicks.user
                         ORDER BY clicks.date_time ASC) AS sqe_num, -- クリックが古い順に採番
        clicks.breakdown_col
    FROM
        clicks
    LEFT JOIN
        conversions ON clicks.user = conversions.ingestly_id
    WHERE 
        clicks.date_time < conversions.date_time
        OR TIMESTAMP_DIFF(conversions.date_time, clicks.date_time, HOUR) <= 720 -- クリック〜コンバージョンの最長紐付け時間
)
-- 最も古いクリックを足し上げる。
SELECT
    breakdown_col AS Variable,
    count(DISTINCT user) AS Conversions
FROM
    raw_result
WHERE
    sqe_num = 1
GROUP BY
    1
ORDER BY
    2 DESC;

線形・均等配分

10回のクリックを経てコンバージョンすると、各クリックが10分の1ずつコンバージョンに貢献したと見なす。
均等配分なので最も古いクリックも直近のクリックも貢献度は同じとなる。

WITH
-- （共通） コンバージョンした訪問者の `ingestly_id` と コンバージョンしたタイムスタンプの最大（最後のコンバージョン）を取り出す。
conversions AS (
    SELECT 
        ingestly_id,
        MAX(timestamp) AS date_time
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-03-01' AND '2021-03-31' -- コンバージョン発生期間
        AND action='view'
        AND category='page'
        AND url_hostname = 'your_domain' -- コンバージョンページのホスト名
        AND url_pathname = '/thanks.html'  -- コンバージョンページのパス名
    GROUP BY
        1
),
-- （共通） コンバージョンした訪問者の流入履歴を取り出す。
clicks AS (
    SELECT 
        timestamp AS date_time,
        campaign_code AS breakdown_col, -- campaign_codeを任意の分析軸に置き換え
        ingestly_id AS user
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-02-01' AND '2021-03-31' -- 評価対象クリックの発生期間
        AND action='view'
        AND category='page'
        AND ingestly_id IN (SELECT ingestly_id FROM conversions)
        AND url_hostname = 'your_domain' -- サイトのホスト名
        AND referrer_hostname != url_hostname
),
-- クリックとコンバージョンのタイムスタンプを比較可能にしておく。
raw_result AS (
    SELECT
        clicks.date_time AS click_ts,
        conversions.date_time AS conversion_ts,
        clicks.user AS user,
        clicks.breakdown_col AS breakdown_col
    FROM
        clicks
    LEFT JOIN
        conversions ON clicks.user = conversions.ingestly_id
    WHERE 
        clicks.date_time < conversions.date_time
        OR TIMESTAMP_DIFF(conversions.date_time, clicks.date_time, HOUR) <= 720 -- クリック〜コンバージョンの最長紐付け時間
),
-- 訪問者ごとのクリック数を得る。
clicks_per_user AS (
    SELECT
        user,
        COUNT(*) AS clicks_per_user
    FROM
        raw_result
    GROUP BY
        user
),
-- 1コンバージョンをクリック数で割り、コンバージョン貢献を均等配分する。
participation AS (
    SELECT
        breakdown_col AS Variable,
        raw_result.user,
         (1 / clicks_per_user.clicks_per_user) AS weighted_click
    FROM
        raw_result
    LEFT JOIN
        clicks_per_user ON raw_result.user = clicks_per_user.user
)
-- コンバージョン貢献を足し上げる。
SELECT
    Variable,
    SUM(weighted_click) AS Contribution
FROM
    participation
GROUP BY
    1
ORDER BY
    2 DESC;

減衰・半減期1日

10回のクリックを経てコンバージョンすると、それぞれのクリックがコンバージョンに貢献したと見なす。
ただし貢献度は古いクリックほど低くなり、「1日経つ毎にで貢献度は半減する」ようになっている。
GAの減衰モデルと同様の考え方

WITH
-- （共通） コンバージョンした訪問者の `ingestly_id` と コンバージョンしたタイムスタンプの最大（最後のコンバージョン）を取り出す。
conversions AS (
    SELECT 
        ingestly_id,
        MAX(timestamp) AS date_time
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-03-01' AND '2021-03-31' -- コンバージョン発生期間
        AND action='view'
        AND category='page'
        AND url_hostname = 'your_domain' -- コンバージョンページのホスト名
        AND url_pathname = '/thanks.html'  -- コンバージョンページのパス名
    GROUP BY
        1
),
-- （共通） コンバージョンした訪問者の流入履歴を取り出す。
clicks AS (
    SELECT 
        timestamp AS date_time,
        campaign_code AS breakdown_col, -- campaign_codeを任意の分析軸に置き換え
        ingestly_id AS user
    FROM
        ingestly.logs
    WHERE 
        timestamp BETWEEN '2021-02-01' AND '2021-03-31' -- 評価対象クリックの発生期間
        AND action='view'
        AND category='page'
        AND ingestly_id IN (SELECT ingestly_id FROM conversions)
        AND url_hostname = 'your_domain' -- サイトのホスト名
        AND referrer_hostname != url_hostname
),
-- クリックとコンバージョンのタイムスタンプを比較可能にしておく。
raw_result AS (
    SELECT
        clicks.date_time AS click_ts,
        conversions.date_time AS conversion_ts,
        TIMESTAMP_DIFF(conversions.date_time, clicks.date_time, HOUR) AS ts_diff,
        clicks.user AS user,
        clicks.breakdown_col AS breakdown_col
    FROM
        clicks
    LEFT JOIN
        conversions ON clicks.user = conversions.ingestly_id
    WHERE 
        clicks.date_time < conversions.date_time
),
-- 半減期＝1日として、1日経過する毎にクリックのコンバージョン貢献を半減させる。
half_life AS (
    SELECT
        breakdown_col AS Variable,
        raw_result.user,
        POW(0.5, (CEIL((ts_diff+1)/25)-1)) AS weighted_click
    FROM
        raw_result
    WHERE
        raw_result.ts_diff <= 720 -- クリック〜コンバージョンの最長紐付け時間
)
-- コンバージョン貢献を足し上げる。
SELECT
    Variable,
    SUM(weighted_click) AS Contribution
FROM
    half_life
GROUP BY
    1
ORDER BY
    2 DESC;

実務でがっつり分析しようというシーンでは、広告コストによる重み付けをしたり、広告の効果（主観ではあるけれど）で重み付けをしたりもします。
認知拡大キャンペーンをしている場合は減衰を逆転させて認知貢献を重くしたり、減衰を半減期モデルではなく忘却曲線やリニアにしたりといった試行錯誤をしたりもします。
決まりはない中で、関係者の納得感であったり、実際に評価してみて貢献度が高いキャンペーンに投資したらそれだけ効果が再現するか、といったことを観察しながら決めていく必要があります。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up