メディア
メディア名を日本語にしたり、広告チャネル別で分ける
GoogleとMicrosoftは、広告チャネルをメディア名に、それ以外は日本語名に変換したり、最新の媒体名称に変更することができます。API自動連携媒体が対象です。
CASE
WHEN channel = "google" THEN
CASE
WHEN LOWER(ad_campaign_objective) = "display" THEN "Google ディスプレイ"
WHEN LOWER(ad_campaign_objective) = "search" THEN "Google検索"
WHEN LOWER(ad_campaign_objective) = "performance_max" THEN "Google P-MAX"
WHEN LOWER(ad_campaign_objective) = "video" THEN "YouTube"
WHEN LOWER(ad_campaign_objective) = "discovery" THEN "デマンドジェネレーション"
WHEN LOWER(ad_campaign_objective) = "demand_gen" THEN "デマンドジェネレーション"
WHEN LOWER(ad_campaign_objective) = "shopping" THEN "Googleショッピング"
ELSE CONCAT("Google ", ad_campaign_objective)
END
WHEN channel = "microsoft" THEN
CASE
WHEN LOWER(ad_campaign_objective) = "audience" THEN "Microsoft ディスプレイ"
WHEN LOWER(ad_campaign_objective) = "search" THEN "Microsoft 検索"
WHEN LOWER(ad_campaign_objective) = "dynamicsearchads" THEN "Microsoft 検索"
WHEN LOWER(ad_campaign_objective) = "performancemax" THEN "Microsoft P-MAX"
ELSE CONCAT("Microsoft ", ad_campaign_objective)
END
WHEN channel = "facebook" THEN "Meta"
WHEN channel = "tiktok" THEN "TikTok"
WHEN channel = "line" THEN "LINE"
WHEN channel = "twitter" THEN "X"
WHEN channel = "yahoo_display_ads" THEN "Yahoo!ディスプレイ"
WHEN channel = "yahoo_search_ads" THEN "Yahoo!検索"
WHEN channel = "apple_search_ads" THEN "Apple Search Ads"
WHEN channel = "smartnews_amv2" THEN "SmartNews"
WHEN channel = "google_dv360" THEN "Google DV 360"
ELSE channel
END
※この分類に含まれない場合は、そのままの表記で表示されます
より詳細な説明は以下をご確認ください。
Looker Studio 計算フィールドでメディア名を変更する方法
都道府県
都道府県名を日本語名にする
都道府県名を日本語で表記できます。都道府県レポートの契約が必要です。
CASE
WHEN LOWER(prefecture) = "hokkaido" THEN "北海道"
WHEN LOWER(prefecture) = "aomori" THEN "青森県"
WHEN LOWER(prefecture) = "iwate" THEN "岩手県"
WHEN LOWER(prefecture) = "miyagi" THEN "宮城県"
WHEN LOWER(prefecture) = "akita" THEN "秋田県"
WHEN LOWER(prefecture) = "yamagata" THEN "山形県"
WHEN LOWER(prefecture) = "fukushima" THEN "福島県"
WHEN LOWER(prefecture) = "ibaraki" THEN "茨城県"
WHEN LOWER(prefecture) = "tochigi" THEN "栃木県"
WHEN LOWER(prefecture) = "gunma" THEN "群馬県"
WHEN LOWER(prefecture) = "saitama" THEN "埼玉県"
WHEN LOWER(prefecture) = "chiba" THEN "千葉県"
WHEN LOWER(prefecture) = "tokyo" THEN "東京都"
WHEN LOWER(prefecture) = "kanagawa" THEN "神奈川県"
WHEN LOWER(prefecture) = "niigata" THEN "新潟県"
WHEN LOWER(prefecture) = "toyama" THEN "富山県"
WHEN LOWER(prefecture) = "ishikawa" THEN "石川県"
WHEN LOWER(prefecture) = "fukui" THEN "福井県"
WHEN LOWER(prefecture) = "yamanashi" THEN "山梨県"
WHEN LOWER(prefecture) = "nagano" THEN "長野県"
WHEN LOWER(prefecture) = "gifu" THEN "岐阜県"
WHEN LOWER(prefecture) = "shizuoka" THEN "静岡県"
WHEN LOWER(prefecture) = "aichi" THEN "愛知県"
WHEN LOWER(prefecture) = "mie" THEN "三重県"
WHEN LOWER(prefecture) = "shiga" THEN "滋賀県"
WHEN LOWER(prefecture) = "kyoto" THEN "京都府"
WHEN LOWER(prefecture) = "osaka" THEN "大阪府"
WHEN LOWER(prefecture) = "hyogo" THEN "兵庫県"
WHEN LOWER(prefecture) = "nara" THEN "奈良県"
WHEN LOWER(prefecture) = "wakayama" THEN "和歌山県"
WHEN LOWER(prefecture) = "tottori" THEN "鳥取県"
WHEN LOWER(prefecture) = "shimane" THEN "島根県"
WHEN LOWER(prefecture) = "okayama" THEN "岡山県"
WHEN LOWER(prefecture) = "hiroshima" THEN "広島県"
WHEN LOWER(prefecture) = "yamaguchi" THEN "山口県"
WHEN LOWER(prefecture) = "tokushima" THEN "徳島県"
WHEN LOWER(prefecture) = "kagawa" THEN "香川県"
WHEN LOWER(prefecture) = "ehime" THEN "愛媛県"
WHEN LOWER(prefecture) = "kochi" THEN "高知県"
WHEN LOWER(prefecture) = "fukuoka" THEN "福岡県"
WHEN LOWER(prefecture) = "saga" THEN "佐賀県"
WHEN LOWER(prefecture) = "nagasaki" THEN "長崎県"
WHEN LOWER(prefecture) = "kumamoto" THEN "熊本県"
WHEN LOWER(prefecture) = "oita" THEN "大分県"
WHEN LOWER(prefecture) = "miyazaki" THEN "宮崎県"
WHEN LOWER(prefecture) = "kagoshima" THEN "鹿児島県"
WHEN LOWER(prefecture) = "okinawa" THEN "沖縄県"
WHEN prefecture IS NULL THEN "不明"
ELSE prefecture
END
※この分類に含まれない場合に、そのまま表示したい場合は、最後の部分をELSE prefectureに変更してください
エリアで分ける
9つの地域に分類した例です。都道府県レポートの契約が必要です。
CASE
WHEN LOWER(prefecture) IN ("hokkaido") THEN "北海道"
WHEN LOWER(prefecture) IN ("aomori", "iwate", "miyagi", "akita", "yamagata", "fukushima") THEN "東北"
WHEN LOWER(prefecture) IN ("ibaraki", "tochigi", "gunma") THEN "北関東"
WHEN LOWER(prefecture) IN ("tokyo", "kanagawa", "chiba", "saitama") THEN "関東"
WHEN LOWER(prefecture) IN ("niigata", "toyama", "ishikawa", "fukui") THEN "北陸"
WHEN LOWER(prefecture) IN ("nagano", "yamanashi", "aichi", "shizuoka", "gifu", "mie") THEN "東海"
WHEN LOWER(prefecture) IN ("osaka", "hyogo", "kyoto", "shiga", "nara", "wakayama") THEN "関西"
WHEN LOWER(prefecture) IN ("okayama", "hiroshima", "shimane", "tottori", "yamaguchi", "tokushima", "ehime", "kagawa", "kochi") THEN "中四国"
WHEN LOWER(prefecture) IN ("fukuoka", "kumamoto", "kagoshima", "saga", "nagasaki", "oita", "miyazaki", "okinawa") THEN "九州・沖縄"
ELSE "その他"
END
北から順番に並び替える用
Looker Studioの表示上の並び替えのみで使用することを推奨しています。都道府県レポートの契約が必要です。
CASE
WHEN LOWER(prefecture) = "hokkaido" THEN "01"
WHEN LOWER(prefecture) = "aomori" THEN "02"
WHEN LOWER(prefecture) = "iwate" THEN "03"
WHEN LOWER(prefecture) = "miyagi" THEN "04"
WHEN LOWER(prefecture) = "akita" THEN "05"
WHEN LOWER(prefecture) = "yamagata" THEN "06"
WHEN LOWER(prefecture) = "fukushima" THEN "07"
WHEN LOWER(prefecture) = "ibaraki" THEN "08"
WHEN LOWER(prefecture) = "tochigi" THEN "09"
WHEN LOWER(prefecture) = "gunma" THEN "10"
WHEN LOWER(prefecture) = "saitama" THEN "11"
WHEN LOWER(prefecture) = "chiba" THEN "12"
WHEN LOWER(prefecture) = "tokyo" THEN "13"
WHEN LOWER(prefecture) = "kanagawa" THEN "14"
WHEN LOWER(prefecture) = "niigata" THEN "15"
WHEN LOWER(prefecture) = "toyama" THEN "16"
WHEN LOWER(prefecture) = "ishikawa" THEN "17"
WHEN LOWER(prefecture) = "fukui" THEN "18"
WHEN LOWER(prefecture) = "yamanashi" THEN "19"
WHEN LOWER(prefecture) = "nagano" THEN "20"
WHEN LOWER(prefecture) = "gifu" THEN "21"
WHEN LOWER(prefecture) = "shizuoka" THEN "22"
WHEN LOWER(prefecture) = "aichi" THEN "23"
WHEN LOWER(prefecture) = "mie" THEN "24"
WHEN LOWER(prefecture) = "shiga" THEN "25"
WHEN LOWER(prefecture) = "kyoto" THEN "26"
WHEN LOWER(prefecture) = "osaka" THEN "27"
WHEN LOWER(prefecture) = "hyogo" THEN "28"
WHEN LOWER(prefecture) = "nara" THEN "29"
WHEN LOWER(prefecture) = "wakayama" THEN "30"
WHEN LOWER(prefecture) = "tottori" THEN "31"
WHEN LOWER(prefecture) = "shimane" THEN "32"
WHEN LOWER(prefecture) = "okayama" THEN "33"
WHEN LOWER(prefecture) = "hiroshima" THEN "34"
WHEN LOWER(prefecture) = "yamaguchi" THEN "35"
WHEN LOWER(prefecture) = "tokushima" THEN "36"
WHEN LOWER(prefecture) = "kagawa" THEN "37"
WHEN LOWER(prefecture) = "ehime" THEN "38"
WHEN LOWER(prefecture) = "kochi" THEN "39"
WHEN LOWER(prefecture) = "fukuoka" THEN "40"
WHEN LOWER(prefecture) = "saga" THEN "41"
WHEN LOWER(prefecture) = "nagasaki" THEN "42"
WHEN LOWER(prefecture) = "kumamoto" THEN "43"
WHEN LOWER(prefecture) = "oita" THEN "44"
WHEN LOWER(prefecture) = "miyazaki" THEN "45"
WHEN LOWER(prefecture) = "kagoshima" THEN "46"
WHEN LOWER(prefecture) = "okinawa" THEN "47"
WHEN prefecture IS NULL THEN NULL
ELSE "99"
END
性別
日本語名に変換した計算式です。性別レポートの契約が必要です。
CASE
WHEN LOWER(gender) = "male" THEN "男性"
WHEN LOWER(gender) = "female" THEN "女性"
WHEN LOWER(gender) = "unknown" THEN "不明"
WHEN LOWER(gender) = "other" THEN "その他"
WHEN gender IS NULL THEN "不明"
ELSE "不明"
END
年齢
10歳刻みにまとめる
各媒体で異なる表記のものを10歳毎に区分しています。年齢レポートの契約が必要です。
CASE
WHEN age IN (
"13-17", "13 to 17", "13〜17",
"〜17歳", "-14", "-17",
"15-19", "15歳〜19歳", "15〜19",
"under 18", "18歳未満"
) THEN "-17"
WHEN age IN (
"18-19", "18歳〜19歳", "18〜19",
"20-24", "20歳〜24歳", "20〜24",
"18-24", "18 to 24", "18〜24", "18歳〜24歳",
"21 and up"
) THEN "18-24"
WHEN age IN (
"25-29", "25歳〜29歳", "25〜29",
"30-34", "30歳〜34歳", "30〜34",
"25-34", "25 to 34", "25〜34", "25歳〜34歳"
) THEN "25-34"
WHEN age IN (
"35-39", "35歳〜39歳", "35〜39",
"40-44", "40歳〜44歳", "40〜44",
"35-44", "35 to 44", "35〜44", "35歳〜44歳"
) THEN "35-44"
WHEN age IN (
"45-49", "45歳〜49歳", "45〜49",
"50-54", "50歳〜54歳", "50〜54",
"45-54", "45 to 54", "45〜54", "45歳〜54歳"
) THEN "45-54"
WHEN age IN (
"55-59", "55歳〜59歳", "55〜59",
"60-64", "60歳〜64歳", "60〜64",
"55-64", "55 to 64", "55〜64", "55歳〜64歳"
) THEN "55-64"
WHEN age IN (
"65+", "65-", "65-69", "65歳〜69歳", "65〜69",
"65-74", "70歳〜", "70〜",
"65 or more", "over 65", "over 70",
"65歳以上", "75以上"
) THEN "65-"
WHEN age IN (
"その他", "不明", "未設定",
"Unknown", "unknown", "undetermined",
"age unknown", "no data"
) OR age IS NULL THEN "不明・その他"
ELSE age
END
※この分類に含まれない場合は、そのままの表記で表示されます
適用されている媒体と区分
上記の計算式は、Google、Facebook（Meta）、TikTok、Twitter（X）、Yahoo! ディスプレイ、LINEに対応しています。ただし媒体側の表記が変更された場合は、適用されませんのでご注意ください。その場合は、新しい表記を追加する必要があります。
また、Yahoo! とLINEは5歳区分ですが、10歳ごとに再区分しています。
キーワード
マッチタイプを日本語にする
CASE
WHEN LOWER(keyword_match_type) = "exact" THEN "完全一致"
WHEN LOWER(keyword_match_type) = "exact match" THEN "完全一致"
WHEN LOWER(keyword_match_type) = "exact (close variant)" THEN "完全一致(類似パターン)"
WHEN LOWER(keyword_match_type) = "exact match (similar term)" THEN "完全一致(類似パターン)"
WHEN LOWER(keyword_match_type) = "phrase" THEN "フレーズ一致"
WHEN LOWER(keyword_match_type) = "phrase match" THEN "フレーズ一致"
WHEN LOWER(keyword_match_type) = "phrase (close variant)" THEN "フレーズ一致(類似パターン)"
WHEN LOWER(keyword_match_type) = "phrase match (similar term)" THEN "フレーズ一致(類似パターン)"
WHEN LOWER(keyword_match_type) = "broad" THEN "部分一致"
WHEN LOWER(keyword_match_type) = "broad match" THEN "部分一致"
WHEN LOWER(keyword_match_type) = "broad (close variant)" THEN "部分一致(類似パターン)"
WHEN LOWER(keyword_match_type) = "broad match (similar term)" THEN "部分一致(類似パターン)"
ELSE keyword_match_type
END
※この分類に含まれない場合は、そのままの表記で表示されます
検索クエリ
マッチタイプを日本語にする
CASE
WHEN LOWER(search_query_match_type) = "exact" THEN "完全一致"
WHEN LOWER(search_query_match_type) = "exact match" THEN "完全一致"
WHEN LOWER(search_query_match_type) = "exact (close variant)" THEN "完全一致(類似パターン)"
WHEN LOWER(search_query_match_type) = "exact match (similar term)" THEN "完全一致(類似パターン)"
WHEN LOWER(search_query_match_type) = "phrase" THEN "フレーズ一致"
WHEN LOWER(search_query_match_type) = "phrase match" THEN "フレーズ一致"
WHEN LOWER(search_query_match_type) = "phrase (close variant)" THEN "フレーズ一致(類似パターン)"
WHEN LOWER(search_query_match_type) = "phrase match (similar term)" THEN "フレーズ一致(類似パターン)"
WHEN LOWER(search_query_match_type) = "broad" THEN "部分一致"
WHEN LOWER(search_query_match_type) = "broad match" THEN "部分一致"
WHEN LOWER(search_query_match_type) = "broad (close variant)" THEN "部分一致(類似パターン)"
WHEN LOWER(search_query_match_type) = "broad match (similar term)" THEN "部分一致(類似パターン)"
ELSE search_query_match_type
END