如何将日期作为变量嵌入到 Google Ads 查询语言中？

发布于 2025-01-10 17:20:14 字数 1282 浏览 0 评论 0原文

我正在使用 python 执行查询并从 Google Ads 检索数据我正在尝试将开始日期和结束日期设置为变量并在查询中使用它们。

查询如下所示：

GAquery = """
SELECT
    segments.date,
    segments.device,
    campaign.name,
    metrics.clicks,
    metrics.conversions,
    metrics.conversions_value,
    metrics.cost_micros,
    metrics.impressions
FROM
   campaign WHERE segments.date >= '2021-12-01' AND segments.date <= '2022-02-27'
ORDER BY
    metrics.clicks DESC"""

它由 Google Function for Python 执行

response = ga_service.search_stream(customer_id=customer_id, query=GAquery)

该函数没有元素参数，我可以在查询中使用 %s 作为占位符，然后在 params 中调用它常规执行(sql,conn, params[Sdate,所以

我需要做的是以某种方式打破查询字符串，并在两者之间添加日期：

sdate = (dt.date.today() - dt.timedelta(days=60))
edate = dt.date.today()

GAquery = """
SELECT
    segments.date,
    segments.device,
    campaign.name,
    metrics.clicks,
    metrics.conversions,
    metrics.conversions_value,
    metrics.cost_micros,
    metrics.impressions
FROM
   campaign WHERE segments.date """ + """>= """+ str(sdate) +""" AND segments.date""" +  """<=""" +str(edate) + """
ORDER BY
    metrics.clicks DESC"""

所以基本上我试图通过将查询分开、插入并缝合来强制变量进入 GAquery。一起。

它失败了，因为我没有正确地破坏它，也没有正确地将它添加回来，

知道如何处理这个问题吗？

原文

I am using python to execute the query and retrieve the data from Google Ads
I am trying to set start and end date as variable and use these in my query.

The query looks like this:

GAquery = """
SELECT
    segments.date,
    segments.device,
    campaign.name,
    metrics.clicks,
    metrics.conversions,
    metrics.conversions_value,
    metrics.cost_micros,
    metrics.impressions
FROM
   campaign WHERE segments.date >= '2021-12-01' AND segments.date <= '2022-02-27'
ORDER BY
    metrics.clicks DESC"""

and it is executed by Google Function for Python

response = ga_service.search_stream(customer_id=customer_id, query=GAquery)

This function does not have element params where i could use %s as placeholder in the query and then call it in params regular execute(sql,conn, params[Sdate,Edate] function in python.

So what i need to do is somehow break the query string, and add the dates in between. Something like this:

sdate = (dt.date.today() - dt.timedelta(days=60))
edate = dt.date.today()

GAquery = """
SELECT
    segments.date,
    segments.device,
    campaign.name,
    metrics.clicks,
    metrics.conversions,
    metrics.conversions_value,
    metrics.cost_micros,
    metrics.impressions
FROM
   campaign WHERE segments.date """ + """>= """+ str(sdate) +""" AND segments.date""" +  """<=""" +str(edate) + """
ORDER BY
    metrics.clicks DESC"""

So basically i am trying to force variables into GAquery by breaking the query apart inserting it and stitching it together.

It is failing because i am not breaking it correctly and not adding it back together correctly.

Any idea how to handle this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

狂之美人 2025-01-17 17:20:14

对我来说，您似乎缺少 GAQL 中日期文字所需的单引号。
为了参数化查询，您可以使用格式化的字符串文字（假设您使用的是 Python 3.6 或更高版本）并执行类似的操作

GAquery = f"""
    SELECT
        ...
    FROM
        ...
    WHERE
        segments.date >= '{str(sdate)}' AND segments.date <= '{str(edate)}'
    ORDER BY
        ...
"""

To me it looks as if you're missing the single quotes that are required for a date literal in GAQL.
In order to parametrize the query, you could use a formatted string literal (assuming you are using Python 3.6 or newer) and do something like

GAquery = f"""
    SELECT
        ...
    FROM
        ...
    WHERE
        segments.date >= '{str(sdate)}' AND segments.date <= '{str(edate)}'
    ORDER BY
        ...
"""

回复收藏 0 原文

~没有更多了~