GA4 API无法提取Session Campaign的(not set)值问题求助

问题说明 #

使用Python脚本通过GA4 API提取数据时，指定维度为date、eventName、sessionCampaignName，指标为eventCount、totalUsers，但无法获取sessionCampaignName为(not set)的eventCount数据。相同维度、指标和时间范围下，Looker Studio可正常显示该数据，且在Universal Analytics时期能正常提取，迁移至GA4后出现此问题。

问题原因 #

GA4 API的run_report接口默认不包含维度值为空（即显示为(not set)）的行，而Looker Studio在查询时默认会包含这些空行，这是两者的默认行为差异导致的。

解决方案 #

在构建RunReportRequest时，添加include_empty_rows=True参数，强制API返回包含空维度值的记录。

修改后的关键代码片段 #

修改GA4Connector类中的fetch_data方法，在RunReportRequest中加入include_empty_rows=True：

def fetch_data(self, start_date, end_date):
    dimensions = [
        Dimension(name="date"),
        Dimension(name="eventName"),
        Dimension(name="sessionCampaignName"),
    ]
    metrics = [
        Metric(name="eventCount"),
        Metric(name="totalUsers"),
    ]
    date_range = DateRange(start_date=start_date, end_date=end_date)
    response = self.client.run_report(
        RunReportRequest(
            property=f"properties/{self.property_id}",
            dimensions=dimensions,
            metrics=metrics,
            date_ranges=[date_range],
            include_empty_rows=True  # 新增参数
        )
    )
    return response

完整修改后的代码 #

from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import (
    DateRange,
    Dimension,
    Metric,
    RunReportRequest,
)
import pyodbc
import pandas as pd
import os
from datetime import datetime, timedelta
from sqlalchemy import create_engine

# ----- Date ----------

data_hoje = datetime.now()
data_7_dias_atras = data_hoje - timedelta(days=8)
data_anterior = data_hoje - timedelta(days=1)
data_anterior_final = data_anterior.strftime('%Y-%m-%d')

# ------- GA4Connector ----------

class GA4Connector:
    def __init__(self, property_id):
        self.client = BetaAnalyticsDataClient()
        self.property_id = property_id
        
    def fetch_data(self, start_date, end_date):
        dimensions = [
            Dimension(name="date"),
            Dimension(name="eventName"),
            Dimension(name="sessionCampaignName"),
        ]
        metrics = [
            Metric(name="eventCount"),
            Metric(name="totalUsers"),
        ]
        date_range = DateRange(start_date=start_date, end_date=end_date)
        response = self.client.run_report(
            RunReportRequest(
                property=f"properties/{self.property_id}",
                dimensions=dimensions,
                metrics=metrics,
                date_ranges=[date_range],
                include_empty_rows=True  # 新增参数
            )
        )
        return response
            
# ---------- DB connection ------------

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = "G:\\Meu Drive\\DEV\\API\\archive-390417-079519729b16.json"

property_id = "XXXXXXXXX"
user_sqlserver = 'XX'
senha_sqlserver = 'XXXXXXXXXXXXXXXXXXXXXXX'
host_sqlserver = 'XXXXXXXXX'
bd_sqlserver = 'XXXXXXXX'
conn_str = (
    r'DRIVER={ODBC Driver 17 for SQL Server};'
    r'SERVER=' + host_sqlserver + ';'
    r'DATABASE=' + bd_sqlserver + ';'
    r'UID=' + user_sqlserver + ';'
    r'PWD=' + senha_sqlserver + ';'
)

engine = create_engine('mssql+pyodbc://' + user_sqlserver + ':' + senha_sqlserver + '@' + host_sqlserver + '/' + bd_sqlserver + '?driver=ODBC+Driver+17+for+SQL+Server')

# ------------------ Create dataframe ------------------


start_date = '2023-08-28'
end_date = '2023-09-04'

ga4_connector = GA4Connector(property_id)
response = ga4_connector.fetch_data(start_date, end_date)

columns = [dimension.name for dimension in response.dimension_headers] + [metric.name for metric in response.metric_headers]
data = [[value.value for value in row.dimension_values] + [value.value for value in row.metric_values] for row in response.rows]
df = pd.DataFrame(data, columns=columns)

# ------------------ Entering data ------------------

df.to_sql('ga4_hml', engine, if_exists='replace', index=False, chunksize=1000)

内容的提问来源于stack exchange，提问作者Matheus Pimenta