DV30 User Audit Example - google-marketing-solutions/bqflow GitHub Wiki

{
  "script":{
    "license":"Licensed under the Apache License, Version 2.0",
    "copyright":"Copyright 2020 Google LLC",
    "dashboard":"https://datastudio.google.com/c/u/0/reporting/9f6b9e62-43ec-4027-849a-287e9c1911bd"
  },
  "tasks": [
   { "dataset":{
      "description":"Create DV360_User_Audit dataset.",
      "auth":"service",
      "dataset":"DV360_User_Audit"
    }},
    { "google_api": {
      "description":"Pull DV360 API users.",
      "auth":"service",
      "api": "doubleclickbidmanager",
      "version": "v2",
      "function": "queries.list",
      "alias":"list",
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":"DV360_User_Audit",
          "table": "DV_Reports"
        }
      }
    }},
    { "google_api": {
      "description":"Pull DV360 API partners.",
      "auth":"service",
      "api": "displayvideo",
      "version": "v3",
      "function": "partners.list",
      "kwargs": {"fields":"partners.displayName,partners.partnerId,nextPageToken"},
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":"DV360_User_Audit",
          "table": "DV_Partners"
        }
      }
    }},
    { "google_api": {
      "description":"Pull DV360 API advertisers.",
      "auth":"service",
      "api": "displayvideo",
      "version": "v3",
      "function": "advertisers.list",
      "kwargs_remote": {
        "query":"SELECT partnerId AS partnerId, 'advertisers.displayName,advertisers.advertiserId,nextPageToken' AS fields FROM `DV360_User_Audit.DV_Partners`"
      },
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":"DV360_User_Audit",
          "table": "DV_Advertisers"
        }
      }
    }},
    { "google_api": {
      "description":"Pull DV360 API users",
      "auth":"service",
      "api": "displayvideo",
      "version": "v3",
      "function": "users.list",
      "kwargs": {},
      "results": {
        "bigquery": {
          "auth":"service",
          "dataset":"DV360_User_Audit",
          "table": "DV_Users"
        }
      }
    }},
    { "bigquery":{
      "description":"Create the user roles view.",
      "auth":"service",
      "from":{
        "query":"SELECT
          U.userId,
          U.name,
          U.email,
          U.displayName,
          REGEXP_EXTRACT(U.email, r'@(.+)') AS Domain,
          IF (ENDS_WITH(U.email, '.gserviceaccount.com'), 'Service', 'User') AS Authentication,
          IF((Select COUNT(advertiserId) from UNNEST(U.assignedUserRoles)) = 0, 'Partner', 'Advertiser') AS Scope,
          STRUCT(
            AUR.partnerId,
            P.displayName AS partnerName,
            AUR.userRole,
            AUR.advertiserId,
            A.displayName AS advertiserName,
            AUR.assignedUserRoleId
          ) AS assignedUserRoles,
          FROM `DV360_User_Audit.DV_Users` AS U,
          UNNEST(assignedUserRoles) AS AUR
          LEFT JOIN `DV360_User_Audit.DV_Partners` AS P
          ON AUR.partnerId=P.partnerId
          LEFT JOIN `DV360_User_Audit.DV_Advertisers` AS A
          ON AUR.advertiserId=A.advertiserId
        "
      },
      "to":{
        "dataset":"DV360_User_Audit",
        "view":"User_Roles"
      }
    }},
    { "bigquery":{
      "description":"Create the dashboard table.",
      "auth":"service",
      "from":{
        "query":"SELECT
          R.*,
          P.displayName AS partnerName,
          A.displayName AS advertiserName,
          FROM (
          SELECT
            queryId,
            (SELECT CAST(value AS INT64) FROM UNNEST(R.params.filters) WHERE type = 'FILTER_PARTNER' LIMIT 1) AS partnerId,
            (SELECT CAST(value AS INT64) FROM UNNEST(R.params.filters) WHERE type = 'FILTER_ADVERTISER' LIMIT 1) AS advertiserId,
            R.schedule.frequency,
            R.params.metrics,
            R.params.type,
            R.metadata.dataRange,
            R.metadata.sendNotification,
            DATE(TIMESTAMP_MILLIS(R.metadata.latestReportRunTimeMS)) AS latestReportRunTime,
          FROM `DV360_User_Audit.DV_Reports` AS R) AS R
          LEFT JOIN `DV360_User_Audit.DV_Partners` AS P
          ON R.partnerId=P.partnerId
          LEFT JOIN `DV360_User_Audit.DV_Advertisers` AS A
          ON R.advertiserId=A.advertiserId
        "
      },
      "to":{
        "dataset":"DV360_User_Audit",
        "table":"DASHBOARD"
      }
    }}
  ]
}