Core Reporting API - How to use multiple dimensionFilterClauses filters?

Question

I'm trying to use multiple dimensionFilterClauses into a Core Reporting API V4 query. If I use just a filter on the ga:adwordsCustomerID dimension everything goes fine, but when I add a second filter on the ga:adTargetingType dimension it throws a "Status 400: Bad request" error.

This is the query I wrote:

    return analytics.reports().batchGet(
        body={"reportRequests": [{
            "pageSize": 10000,
            "viewId": VIEW_ID,
            "dateRanges": [
                {"startDate": "31daysAgo", "endDate": "yesterday"}
            ],
            "dimensions": [
                {"name": "ga:adwordsCampaignID"},
                {"name": "ga:adwordsAdGroupID"},
                {"name": "ga:adwordsCriteriaID"}
            ],
            "metrics": [
                {"expression": "ga:adClicks"},
                {"expression": "ga:adCost"},
                {"expression": "ga:uniquePurchases"},
                {"expression": "ga:itemRevenue"},
                {"expression": "ga:CPC"},
                {"expression": "ga:ROAS"}
            ],
            "dimensionFilterClauses": [
                {"filters": [
                    {"dimensionName": "ga:adwordsCustomerID",
                     "operator": "EXACT",
                     "expressions": ["2096809090"]},
                    {"dimensionName": "ga:adTargetingType",
                     "operator": "EXACT",
                     "expressions": ["Keyword"]}
                ]}
            ],
            "metricFilterClauses": [
                {"filters": [
                    {"metricName": "ga:adCost",
                     "operator": "GREATER_THAN",
                     "comparisonValue": "0"}
                ]}
            ],
            "orderBys": [
                {"fieldName": "ga:adClicks",
                 "sortOrder": "DESCENDING"}
            ]}
        ]}
    ).execute()

Do you know what's wrong with the above query body?

Solution 1

Analytics Reporting API V4 Filtering

The ReportRequest takes an array of DimensionFilterClauses. These clauses are combined with the logical AND operator. Ie. If you had two DimensionFilterClause objects: A and B; the API will only return values that meet both conditions in A AND B.

Each DimensionFilterClause takes an array of DimensionFilters (called filters). These filters are combined with the logical OR operator. Ie. if you had two DimensionFilter objects C and D within a DimensionFilterClause; the API would return results that satisfied either C or D.

Example

Below is an example request with two DimensionFilterClauses; ga:adWordsCampaignID==8675309 AND ga:adwordsAdGroupID==12345 API Explorer example:

{
  "reportRequests": 
  [
    {
      "viewId": "XXXX",
      "dimensions": 
      [
        {"name": "ga:adwordsCampaignID"},
        {"name": "ga:adwordsAdGroupID"},
        {"name": "ga:adwordsCriteriaID"}
      ],
      "metrics": 
      [
        {"expression": "ga:adClicks"},
        {"expression": "ga:adCost"}
      ],
      "metricFilterClauses": 
      [
        {
          "filters": 
          [
            {
              "metricName": "ga:adCost",
              "operator": "GREATER_THAN",
              "comparisonValue": "0"
            }
          ]
        }
      ],
      "dimensionFilterClauses": 
      [
        {
          "filters": 
          [
            {
              "dimensionName": "ga:adwordsCampaignID",
              "operator": "EXACT",
              "expressions": ["8675309"]
            }
          ]
        },
        {
          "filters": 
          [
            {
              "dimensionName": "ga:adwordsAdGroupID",
              "operator": "EXACT",
              "expressions": 
              ["12345"],
            }
          ]
        }
      ]
    }
  ]
}

Conclusion

From the outset it does not appear that there is anything wrong with your API request body, if there was you would get an error message. But its more likely that you didn't intend to request for:

        "dimensionFilterClauses": [
            {"filters": [
                {"dimensionName": "ga:adwordsCustomerID",
                 "operator": "EXACT",
                 "expressions": ["2096809090"]},
                {"dimensionName": "ga:adTargetingType",
                 "operator": "EXACT",
                 "expressions": ["Keyword"]}
            ]}
        ],

But you intended to request for:

        "dimensionFilterClauses": [
            {"filters": [
                {"dimensionName": "ga:adwordsCustomerID",
                 "operator": "EXACT",
                 "expressions": ["2096809090"]},
            ]},{"filters": [
                {"dimensionName": "ga:adTargetingType",
                 "operator": "EXACT",
                 "expressions": ["Keyword"]}
            ]}
        ],

My recommendation when no data shows up is to first remove the filter, and then verify you have the exact string you are searching for. Also if you do not have the full string you can use a different filter.operator such as PARTIAL or BEGINS_WITH.

Error handling

It is also smart to use proper error handling especially when debugging:

try:
  response = analyticsreporting.reports().batchGet(
    body=requestBody
  ).execute()

except TypeError, error:
  # Handle errors in constructing a query.
  print 'There was an error in constructing your query : %s' % error

except HttpError, error:
  # Handle API errors.
  print ('There was an API error : %s : %s' %
         (error.resp.status, error.resp.reason))

Updated: ga:adTargetingType==Keyword Example

From the comment below it was requested to give an example of dimension filter with ga:adTargetingType==Keyword. Use the API Example here to prove to yourself that it works (just change VIEW_ID to your view view id and hit "Authorize and Execute"). JSON body below:

{
  "reportRequests": 
  [
    {
      "viewId": "VIEW_ID",
      "metrics": [{"expression": "ga:sessions"}],
      "dimensions": [{"name": "ga:adTargetingType"}],
      "dimensionFilterClauses": 
      [
        {
          "filters": 
          [
            {
              "dimensionName": "ga:adTargetingType",
              "operator": "EXACT",
              "expressions": ["Keyword"]
            }
          ]
        }
      ]
    }
  ]
}

I always like to start small and work up. By removing the other parameters and fields I can prove to myself what is working and what is not. This example is the bare minimum required request that filters for ga:keyword==Keyword.

Second Update:

The actual error message you are getting is as follows:

"Selected dimensions and metrics cannot be queried together."

The dimension ga:adTargetingType cannot be queried with the following metrics:

• ga:impressions
• ga:adClicks
• ga:adCost
• ga:CPM
• ga:CPC
• ga:CTR
• ga:costPerTransaction
• ga:costPerGoalConversion
• ga:costPerConversion
• ga:RPC
• ga:ROAS

Solution 2

A crucial piece of information that is missing in this page is the use of FilterLogicalOperator. This is the operator for combining multiple dimension filters. If unspecified, it is treated as an OR. So, the answer above that says that the default is AND is not correct.

In order to apply both filters, we need to add the FilterLogicalOperator in the code:

"dimensionFilterClauses": [
            {"operator": "AND",
             "filters": [
                {"dimensionName": "ga:adwordsCustomerID",
                 "operator": "EXACT",
                 "expressions": ["2096809090"]},
                {"dimensionName": "ga:adTargetingType",
                 "operator": "EXACT",
                 "expressions": ["Keyword"]}
            ]}
        ],

Comments

[Gianluca]

Thank you for all your help. I think there still is a problem with how I write the filter on the ga:adTargetingType dimension. The query structure you suggested works perfectly, since I've tested it with other filters. However if I use operators like BEGINS_WITH, EXACT, == or PARTIAL in combination with the expression ["Keyword"] it doesn't return any output. What is the equivalent of the filter ga:adTargetingType==Keyword in the Query Explorer? The latter returns exactly what I'm looking for.

[Matt]

@Gianluca I have updated the answer with an example filtering for ga:adTargetingType==Keyword. If you have the results from your request error.resp.message then it might help debug what's wrong.

[Matt]

@Gianluca updated again I tried your exact query and found that the "selected dimensions and metrics cannot be queried together". It is important to check the error messages. Also the Dimensions and Metric Explorer will show you what dimensions and metrics can be quirred together.

[Matt]

In what regard @ichimaru?

[Matt]

beats me @Gianluca is still active on SO, and only he can mark it as answering his question. But if you run into any issues with the API feel free to ask here or just ask a new question as this answer got a bit long winded. There are many Google Analytics experts on SO.

[Jonas Palačionis]

Hi, how would I exclude a particular ga:adwordsCustomerID using filters?

[Matt]

@JonasPalačionis you could try adding a not boolean to the dimensionFilter {"dimensionName": "ga:adwordsCustomerID", "not": true, "operator": "EXACT", "expressions": ["<ID_TO_EXCLUDE>"]}

[Jonas Palačionis]

Thanks @Matt, could you take a look at one of my questions regarding batchGet? I somehow cant retrieve more than 100,000 rows from google analytics API v4 even when passing the pageToken variable.

[Matt]

Any given response will never give you more than 100,000 rows. But if the data actually contains more than 100,000 supplying pageToken=1000001 should give you the next 100,000 rows.