Replicating CM360 Reports Across Multiple Accounts

CM360 allows only one account per report. When running the same report for multiple accounts the user or script has to create the report multiple times and then download and combine those reports into one. Which is made even more difficult by the lack of an Account ID id field in CM360 reports.

To solve the problem, our team created a CM360 Report Replicate function which takes a master report and a list of additional accounts and/or advertisers. The solution then replicates the master report for each account, downloads the reports when they complete, and adds an AccountId to each downloaded report.

The Code

The example below is a python executable that will first create a CM360 report for account 101 then replicate the report for accounts 123, 456, and advertiser 1112 within account 789 ( all account numbers after the first one are assumed to be advertisers).

####################################################################
#
#  Copyright 2021 Google LLC
#
#  Licensed under the Apache License, Version 2.0 (the "License");
#  you may not use this file except in compliance with the License.
#  You may obtain a copy of the License at
#
#      https://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing,
#  software
#  distributed under the License is distributed on an "AS IS" BASIS,
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or 
#  implied.
#  See the License for the specific language governing permissions 
#  and limitations under the License.
#
####################################################################
#
#  This code generated (see scripts folder for possible source):
#    - Command: "python starthinker_ui/manage.py example"
#
####################################################################

import argparse
import textwrap

from starthinker.util.configuration import Configuration
from starthinker.task.dataset.run import dataset
from starthinker.task.dcm.run import dcm
from starthinker.task.cm_report_replicate.run import cm_report_replicate


def recipe_cm_replicate(config):
  dataset(config, {
    'description':'Create a dataset for bigquery tables.',
    'auth':'user',
    'dataset':'CM_Replicate'
  })

  dcm(config, {
    'description':'Create a template report.',
    'auth':'user',
    'report':{
      'name':'CM Replicate Template',
      'account':101,
      'body':{
        'kind':'dfareporting#report',
        'format':'CSV',
        'type':'STANDARD',
        'criteria':{
          'dateRange':{
            'kind':'dfareporting#dateRange',
            'relativeDateRange':'LAST_7_DAYS'
          },
          'dimensions':[
            { 'kind':'dfareporting#sortedDimension',
              'name':'dv360PartnerId' },
            { 'kind':'dfareporting#sortedDimension',
              'name':'dv360Partner' },
            { 'kind':'dfareporting#sortedDimension',
              'name':'advertiser' },
            { 'kind':'dfareporting#sortedDimension',
              'name':'advertiserId' },
            { 'kind':'dfareporting#sortedDimension',
              'name':'month' }
          ],
          'metricNames':[
            'impressions'
          ]
        },
        'schedule':{
          'active':True,
          'repeats':'WEEKLY',
          'every':1,
          'repeatsOnWeekDays':[
            'TUESDAY'
          ],
          'startDate':'2021-10-26',
          'expirationDate':'2022-01-24'
        },
        'delivery':{
          'emailOwner':False
        }
      }
    }
  })

  cm_report_replicate(config, {
    'description':'Replicate the report across accounts.',
    'auth':'user',
    'report':{
      'account':101,
      'name':'CM Replicate Template',
      'delete':False
    },
    'replicate':{
      'values':[ [ 123 ], [ 456 ], [ 789, 1112 ] ],
    'write':{
      'bigquery':{
        'dataset':'CM_Replicate',
        'is_incremental_load':False
      }
    }
  })


if __name__ == "__main__":
  parser = argparse.ArgumentParser(
    formatter_class=argparse.RawDescriptionHelpFormatter,
    description=textwrap.dedent("""
  """))

  parser.add_argument("-project", help="Cloud ID of Google Cloud Project.", default=None)
  parser.add_argument("-key", help="API Key of Google Cloud Project.", default=None)
  parser.add_argument("-client", help="Path to CLIENT credentials json file.", default=None)
  parser.add_argument("-user", help="Path to USER credentials json file.", default=None)
  parser.add_argument("-service", help="Path to SERVICE credentials json file.", default=None)
  parser.add_argument("-verbose", help="Print all the steps as they happen.", action="store_true")

  args = parser.parse_args()

  config = Configuration(
    project=args.project,
    user=args.user,
    service=args.service,
    client=args.client,
    key=args.key,
    verbose=args.verbose
  )

  recipe_cm_replicate(config)

The above code will generate the following BigQuery tables and schema. The Account_Id is added in transit by the replicate function.

BigQuery table and schema generated by the code above.

Deployment Instructions

Simply python -m pip install starthinker and provide the credentials necessary to access both CM360 and BigQuery. Additional deployment options are listed on the CM360 Report Replicate One-Sheeter. Deployments include Airflow, AppEngine, Colab, and Cloud Function.

Options To Modify The Code

The solution is flexible not only deployment but configuration as well:

1. Additional StarThinker utilities/tasks/scripts can be added.
2. Replace the write contents of the JSON to change destinations.
3. Change auth from user to service to run everything as Service Credentials.
4. Use a report id instead of a report name.
5. Change delete to True to remove all template copies.
6. Replace values with a Sheets or BigQuery source.
7. Include this entire workflow as a function in a larger program.
8. Skip the report create code, and use a manually created CM360 Report.
9. Replace the report create code with any report configuration.