page_referrerでの流入元判定の検討（GA4）

## 概要
GA4のイベントパラメータ（page_referrer）を用いた、分析の切り口のための流入元判定の検討メモです。
条件文が複雑になるとSQLでは可読性が低くなってしまうのでJSでUDFを記述しています。

流入元（page_referrer = document.referrer）の値は実際のGA4プロパティのデータを参考にしていますが、抜け漏れはあると思いますのでご留意ください。


## LINE判定

まずはLINEから見ていきましょう。
LINEと思われるリファラーは以下のようなURLが確認できました。

### 流入元
- android-app://jp.naver.line.android/
- http://line.me/
- http://line.me/R/msg/text/
- https://line.me/R/msg/text/
- https://place.line.me/


### UDF

というわけでこんな形のUDFで真偽値を返すようにします。

```sql
CREATE TEMP FUNCTION
  isLineAppReferral(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }
  var URIs = [
    'line.me/',
    'android-app://jp.naver.line.android'
  ];
  return URIs.some(emailURL => url.includes(emailURL));
""";
```

リファラーが入らないケースもあるようで、LINE Webviewに含まれるUA文字列（Line）での判定を組み合わせた方が良さそうです。

### UA String

> Mozilla/5.0 (iPhone; CPU iPhone OS 17_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 Safari **Line**/14.2.0



## Facebook判定

つづいてはFacebookです。

SSLや末尾のスラッシュの有無など揺れはありますがfacebook.comの一致条件にしておきます。

### 流入元
- http://m.facebook.com/
- http://m.facebook.com
- https://m.facebook.com/
- https://lm.facebook.com/
- https://l.facebook.com/
- https://l.facebook.com
- https://facebook.com/
- https://www.facebook.com/
- android-app://m.facebook.com/

### UDF
```sql
CREATE TEMP FUNCTION
  isFacebookReferral(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }
  var URIs = [
    'facebook.com'
  ];
  return URIs.some(emailURL => url.includes(emailURL));
""";
```

FacebookアプリもWebviewで独自のUA文字列が含まれるため併用すると良さそうです。

### UA String

> Mozilla/5.0 (iPhone; CPU iPhone OS 17_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/21D61 [**FBAN/FBIOS**;FBAV/452.0.0.39.110;FBBV/569146793;FBDV/iPhone15,4;FBMD/iPhone;FBSN/iOS;FBSV/17.3.1;FBSS/3;FBID/phone;FBLC/ja_JP;FBOP/5;FBRV/570654046]


## X（Twitter）判定

XではLINEやFacebook同様にアプリスキーマの文字列がリファラーに設定されることがあるようです。

### 流入元
- android-app://com.twitter.android
- https://t.co
- https://twitter.com/
- https://t.co/UWUG8579wI

### UDF
```sql
CREATE TEMP FUNCTION
  isXReferral(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }
  var URIs = [
    'https://t.co',
    'android-app://com.twitter.android',
    'https://twitter.com/'
  ];
  return URIs.some(emailURL => url.includes(emailURL));
""";
```

XはWebviewを使っていないので（iOSの場合Safari view controller）、独自のUA文字列を用いた判定はできません。

## Google Discover、Quick Search Box判定

Discover判定のために流入元を利用します。

### 流入元
- https://www.googleapis.com/auth/chrome-content-suggestions
- android-app://com.google.android.googlequicksearchbox/https/www.google.com
- android-app://com.google.android.googlequicksearchbox
- android-app://com.google.android.googlequicksearchbox/
- https://discover.google.com/

厳密な判定条件ではないものの、Google Search Console比で-10%〜-30%の誤差でDiscover判定をすることができました。


### UDF
```sql
CREATE TEMP FUNCTION
  isDiscoverOrQSB(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }
  var URIs = [
    'https://www.googleapis.com/auth/chrome-content-suggestions',
    'android-app://com.google.android.googlequicksearchbox'
  ];
  return URIs.some(emailURL => url.includes(emailURL));
""";
```

「Discover側のインプレッション数とクリック数」だけで考えるなら、SearchConsoleのBigQueryテーブルを使う選択肢もあります。

SearchConsoleのBigQueryエクスポートを有効にして以下のようなクエリを記述することで、検索タイプごとのインプレッション数、クリック数を抽出することができます。

```sql
SELECT
  data_date,
  search_type,
  SUM(impressions) AS imression,
  SUM(clicks) AS click
FROM
  `projectName.datasetName.searchdata_url_impression`
GROUP BY
  data_date,
  search_type
```

## email判定

Gmailアプリの流入、Webメールの流入、UTMパラメータの有無を組みあせて判定してみます。

実際のデータをざっとみた感じでは、以下の流入元が判定条件として利用できそうでした。

### 流入元
- https://mail.yahoo.co.jp/
- https://m.mail.yahoo.co.jp/
- https://mail.google.com/
- android-app://com.google.android.gm
- https://mail.nifty.com/
- https://mail.ezweb.ne.jp/
- https://mail.google.com/mail/mu/mp/533/
- https://eowebmail.eonet.jp/
- https://mail.ocn.jp/
- https://web.mail.goo.jp/

### UDF

UTMパラメータの存在も判定条件に利用します。

```sql
CREATE TEMP FUNCTION
  isEmailReferral(referrer STRING, pagePath STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """

  var URIs = [
    'mail.yahoo.co.jp',
    'mail.google.com',
    'mail.nifty.com',
    'mail.ocn.jp',
    'mail.goo.jp',
    'mail.ezweb.ne.jp',
    'eowebmail.eonet.jp',
    'webmail.auone-net.jp',
    'android-app://com.google.android.gm'
  ];
  
  var isUriMatch = URIs.some(emailURL => referrer.includes(emailURL));
  
  var hasUTMParameters = pagePath.includes('utm_source=mail') || pagePath.includes('utm_medium=mail');
  
  return isUriMatch || hasUTMParameters;
""";
```

なお、メール経由の流入のうち93%はリファラー無しでアクセスされていたため、リファラー単体を判定条件として利用するのは難しそうです。



## 画像検索判定

Googleレンズ等、画像ベースでの検索ニーズも増えていますので、流入元判定できるようにします。

### 流入元（Pinterest）

Pinterestは国ごとにドメイン・サブドメインが異なるようですので、正規表現が必要になりそうです。

- https://www.pinterest.com
- https://www.pinterest.com/
- https://pinterest.com/
- https://www.pinterest.co.kr/
- https://www.pinterest.jp
- https://www.pinterest.com/pin/{id}
- https://www.pinterest.ca/
- https://br.pinterest.com/
- https://www.pinterest.es/

アプリのWebviewには`Pinterest`の文字列が含まれていました。

> Mozilla/5.0 (iPhone; CPU iPhone OS 17_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 [**Pinterest**/iOS]

### 流入元（その他）
Gogole レンズやポータルの画像検索のリファラーが確認できましたので、これを利用します。

- https://lens.google.com/
- https://service.smt.docomo.ne.jp/portal/search/image/result.html
- https://imagesearch.excite.co.jp/
- https://www.bing.com/images/search
- https://www.bing.com/images/search?q={keyword}

### UDF

```sql
CREATE TEMP FUNCTION
  isImageSearchReferral(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }
  var URIs = [
    'lens.google.com',
    'service.smt.docomo.ne.jp/portal/search/image/result.html',
    'bing.com/images/search',
    'imagesearch.excite.co.jp'
  ];
  var pinterestRegex = /https:\/\/(www\.)?pinterest\.(com|co\.[a-z]{2}|[a-z]{2})(\/|\/pin\/\d+)?\/?/;
  return URIs.some(emailURL => url.includes(emailURL)) || pinterestRegex.test(url);
""";
```

## 生成AI関連判定

今後増えるであろう生成AI経由の流入も取得できるようにします。

### 流入元

> https://copilot.microsoft.com/
> https://chat.openai.com/

### UDF

```sql
CREATE TEMP FUNCTION
  isGenAIReferral(url STRING)
  RETURNS BOOLEAN
  LANGUAGE js AS """
  if (url === null || url === undefined) {
    return false;
  }

  var genAIRegex = /.*\.ai.*|.*\.openai.*|.*copilot\.microsoft\.com.*/;
  return genAIRegex.test(url);
""";
```

## 検索クエリ

docomoの検索ポータルやbingの一部ではリファラーに検索クエリ文字列も含まれるため取り出しておきましょう。

### 流入元
> https://service.smt.docomo.ne.jp/portal/search/web/result.html?q={keyword}
> https://www.bing.com/search?q={keyword}


### UDF

```sql
CREATE TEMP FUNCTION ExtractDecodeAndSplitQParam(url STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  try {
    // URLからクエリパラメータを取り出す正規表現
    var match = url.match(/[\?&]q=([^&]+)/);
    if (match) {
      // q=パラメータの値をデコード
      var decoded = decodeURIComponent(match[1]);
      // 空白またはプラス記号で分割して返す
      return decoded.split(/\\+|\\s/);
    } else {
      return []; // q=パラメータが見つからない場合は空の配列を返す
    }
  } catch(e) {
    return [url]; // 処理に失敗した場合はURLをそのまま配列に入れて返す
  }
""";
```

## というわけで

以下のような形で、各関数にイベントパラメータ（page_referrer）を渡す形になります。

### イベントパラメータの取得


```sql
CREATE TEMPORARY FUNCTION get_event_value(params ANY TYPE,name STRING) AS (
  ( 
    SELECT
      COALESCE( 
        value.string_value,
        cast(value.int_value AS string),
        cast(value.float_value AS string),
        cast(value.double_value AS string) 
      ) 
    FROM
      UNNEST(params) AS x 
    WHERE 
      x.key = name 
  )
);

WITH ReferrerValue AS (
  SELECT
    get_event_value(event_params, "page_referrer") AS page_referrer
  FROM
    `projectId.analytics_propertyId.events_*`
  WHERE
    event_name = "page_view"
)
```

### 各種フラグ

```sql
SELECT
  -- LINE
  isLineAppReferral(page_referrer) as isLineAppReferral,
  -- Facebook
  isFacebookReferral(page_referrer) as isFacebookReferral,
  -- X
  isXReferral(page_referrer) as isXReferral,
  -- Google Discover or Quick Search Box
  isDiscoverOrQSB(page_referrer) as isDiscoverOrQSB,
  -- email
  isEmailReferral(page_referrer) as isEmailReferral,
  -- 画像検索
  isImageSearchReferral(page_referrer) as isImageSearchReferral,
  -- 生成AI
  isGenAIReferral(page_referrer) as isGenAIReferral
FROM
  ReferrerValue
```

### 検索キーワードのカウント
```sql
prep_data AS (
  SELECT
    ExtractDecodeAndSplitQParam(ref) AS keywords
  FROM
    ReferrerValue
)
SELECT
  keyword,
  count(keyword) as count
FROM
  prep_data,
  unnest(keywords) as keyword
group by
  keyword
```


## おわりに
GA4はコンソールやAPI経由で抽出するとサンプリング・閾値・高基数の問題が発生するため、抽出精度の観点でBigQuery経由で抽出することをおすすめします。

そして再現性の維持や効率化のためにも、使用頻度の高い処理はUDFとして再利用可能な状態にしておきたいですね。