Collecting and transferring Microsoft Ads data to Google BigQuery

This tutorial explains how to collect data from Microsoft Ads and transfer the collected data to Google BigQuery.

Time requirement: 15 minutes

Collecting data from Microsoft Ads

Before you start collecting data from Microsoft Ads, perform all of the following actions:

  • You can authenticate the authorization to a Microsoft Ads account using Microsoft's work account authentication. If you choose this authentication method, your company's Azure Active Directory admin must first grant Adverity access. Invite the Azure Active Directory admin to the Microsoft Ads account to grant the relevant permissions.

    When granting permissions, Azure Active Directory admins can select the Grant permissions on behalf of your organization checkbox. If selected, all Microsoft Ads users within their domain can then grant access to Adverity without admin intervention.

    For more information on work accounts and Azure Active Directory admin permissions, see the Microsoft documentation.

  • Register your application to access the Microsoft Ads API with Adverity and set Redirect URI to https://{YOUR_INSTANCE}.datatap.adverity.com/.

    For more information and detailed instructions, see the Microsoft documentation.

To collect data from Microsoft Ads, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

  1. In the top right corner, click + Create datastream.

  2. Search for and click Microsoft Ads.

  1. Click Setup a new Authorization.

  2. Log in with your credentials.

  3. Click Keyword Report.

  4. Click Next.

  5. In the Destinations page, deselect Explore & Present if checked, and click Next.

  6. In the Initial Fetch page, click Yesterday.

  7. Click Run Fetch.

Adding Google BigQuery as destination

Before adding Google BigQuery as destination, perform all of the following actions:

  • Create a dataset in Google BigQuery that is dedicated to data transferred from Adverity.

  • Ensure that the Google account you use to connect to Google BigQuery includes the following permissions:

    • bigquery.jobs.get

    • bigquery.jobs.list

    • bigquery.jobs.create

    • bigquery.tables.get

    • bigquery.tables.getData

    • bigquery.tables.list

    • bigquery.datasets.get

    • bigquery.tables.create

    • bigquery.tables.update

    • bigquery.tables.updateData

    Alternatively, if your account does not include all of the permissions above, connect to Google BigQuery with a JSON service account key. For more information on creating a JSON service account key, see the Google documentation.

If you transfer large data sets to Google BigQuery (for example, larger than 50 GB), transfer the data as a batch operation. For more information on batch loading data to Google BigQuery, see the Google documentation. To use the batch load function, perform all of the following actions in addition to the prerequisites listed above:

  • Ensure that the Google account you use to connect to Google BigQuery includes the following permissions:

    • storage.objects.get

    • storage.objects.list

  • Ensure that the Google account you use to connect to Google BigQuery has permissions to write and delete files in your Google Cloud Storage.

  • Ensure that the account you use for Google BigQuery and Google Cloud Storage has access to both Google BigQuery and Google Cloud Storage. We recommend ensuring that Google BigQuery and Google Cloud Storage are in the same project. For more information on projects, see the Google documentation.

  • Set up an authorization to Google Cloud Storage in your Adverity workspace. For more information, see Setting up an authorization to Google Cloud Storage (Service Account).

  • Set up a storage in Adverity using the Google Cloud Storage authorization. For more information, see Setting up Storage for data extracts. You do not need to set up this storage to store the data extracts in your workspace.

To add Google BigQuery as a destination to a workspace, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Destinations.

  2. Click + Add Destination.

  3. Click Google BigQuery.

  1. Click Setup a new Authorization.

  2. Click Next.

  1. Select one of the following options:

    • To connect to Google BigQuery with your own account, click Google BigQuery (OAuth2), and log in with your user credentials.

    • To connect to Google BigQuery with a service account, click Google BigQuery (Service Account), upload the JSON service account key.

  2. (Optional) When connecting to Google BigQuery with a service account, select Retrieve projects from Google Cloud API to retrieve several projects that the service account can access.

  3. Click Authorize.

  1. In the Configuration page, fill in the following fields:

    Name

    (Optional) Rename the destination.

    Cloud Storage

    If you transfer data to Google BigQuery as a batch operation, select the storage you have created using the Google Cloud Storage authorization.

    Project

    Specify the name of the project where to transfer the data.

    Dataset

    Specify the name of the dataset where to transfer the data.

    For more information on advanced configuration settings, see Google BigQuery destination reference.

  1. Click Create.

Assigning Google BigQuery as destination

To assign the Google BigQuery destination to the Microsoft Ads datastream, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Datastreams.

  1. Select the Microsoft Ads datastream.

  1. In the Destinations section, click + Add Destination.

  2. Click Assign existing destinations.

  1. Select the Google BigQuery checkbox in the list.

  2. Click Save.

  1. In the All tasks tab, find the top element, and click Refetch on the right.

What’s next?

This tutorial outlined a basic workflow within Adverity. So far, you have learned how to collect data from a source and transfer it to a destination. Adverity offers additional features that may benefit your marketing goals and data integration. For more information, see Getting started with Adverity.