Error while accessing AdSense data through API (BigQuery) with server2server authentication

[A. Burchert]

Hi everyone,

I tried to access the AdSense data of a project with the experimental approach via BigQuery which returns an error: "Unexpected. Please try again.". For testing I used the query example stated here:

SELECT date, SUM(earnings) FROM [google.com:adsense-reports:Reports.DailyReport] GROUP BY date

Executing this in the BigQuery browser tool produces the expected results. To ensure my authentication works and I have access to BigQuery I executed a query using the sample data which returned without errors:

SELECT word FROM [publicdata:samples.shakespeare] WHERE word="sleep"

Does anyone else tried this approach and can share experiences?

Further information: I'm using the php googe lib with Google_BigqueryService(), using a server2server authentication
Scopes: 'https://www.googleapis.com/auth/bigquery', 'https://www.googleapis.com/auth/bigquery.readonly', 'https://www.googleapis.com/auth/adsense'
Failing job: job_034df770bf77401d91613240a4cfb0f6

Thanks in advance!

[Louis Collard]

So to clarify - running the query in the BigQuery browser tool does work as expected? 

It's only when using the server2server authentication that you're having problems?

Are you authenticating as the same user in both situations?

[A. Burchert]

Exactly.
If I login to the account connected to AdSense, which is the owner of the google project, and run the command by hand in the browser tool I get the expected result.
Using a service account with server2server authentication I receive the error.

I tried to access the AdSense Management API with the service account too but received "User is not registered to AdSense" (or something like that, don't remember exactly).

[Louis Collard]

Right - you have to access the AdSense BigQuery tables with an account that you can log in to AdSense with, this is the same as with the Management API.

Can you use a different type of authentication?

[A. Burchert]

This is the setup of accounts and what I tried so far:

Google Account X: Owner of Project XX, Owner of AdSense
BigQuery
> s2s: does not work
> with a Client ID for web applications: no sufficient permission
AdSense Management API
> s2s: does not work
> with a Client ID for web applications: works

Google Account Y: Owner of Project YY, can access AdSense of X
BigQuery
> browser: worked
> s2s: does not work
AdSense Management API
> s2s: does not work

What makes me wonder is: why can I access BigQuery with s2s but then cannot access the adsense database?
And which other authentication can I try? I probably won't be able to access the database too I guess?

Warning: Very few Google APIs currently support Service Accounts. Service accounts are currently supported by the following Google developer services:

    Google Cloud Storage
    Google Prediction API
    Google URL Shortener
    Google OAuth 2.0 Authorization Server
    Google BigQuery

[Louis Collard]

The AdSense BigQuery tables have an extra layer of authentication compared to the normal BigQuery tables - this is so that everyone can read from the same tables without any setup.

Your service account works for the normal BigQuery authentication, allowing you to read the sample tables, but not for the AdSense authentication required to read the AdSense tables.

[A. Burchert]

Thanks for the explanation! So at this point it's not possible to get the data via BigQuery and a service account...