<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Important
This content are intended for educational and informational purposes only.
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
Step1:  API Configuration
The Developer Key is used to retrieve a discovery
document
This is used to build the service used to make API 
Client secret is used to authenticate to the API,  it can be downloaded from the Google Cloud Console (make sure you have correctly renamed the json file to 'adh-key.json')
Step9:  API Authentication - OAuth2.0 Flow
Utility functions to execute the OAuth2.0 flow
Step10:  Actual Request to the Ads Data Hub Service API
Step11:  Analysis 1
Step12:  1.2 Analysis Parameters
Step13:  1.3 Funnel groups
Step14:  1.4 Friendly Names
Step15:  Step 2
Step16:  Create the Query
For each user id
Step17:  Part 2 - calculate metrics
Step18:  Put the query together
Join the 3 parts of the query and use the python format function to pass through the parameters set in step 1
Step19:  Set up group parameters
Step20:  Create the Query in ADH
Step21:  Check the query exists in ADH
Full Query
2.2 Execute the query
Step22:  2.3  Retrieve the result from BigQuery
Pass the query (q1) and billing project id in order to run the query
We are using a direct pandas integration with BigQuery in order to run this
Step23:  Step 3
Step24:  Step 4 - Display the output
Step25:  What is the overlap between your groups? 
Are your upper funnel users moving through the funnel? 
Analysis 2
Step26:  Step 2
Step27:  output example
Step28:  output example
Step29:  example output
Step30:  example output
Step31:  example output
Step32:  Set the parameter types
ADH allows you to create variables
We will pass this in to the request when we create the query so we need to define what the types are
Step33:  Create the query in ADH
Step34:  2.2
Step35:  2.3 Get the results from BigQuery
Step36:  Step 3
Step37:  3.2 Calculate the percentage cookie overlap and conversion rate and format the values
Calculate the percentage of cookie overlap and the percentage of conversions for each group in order to display on the chart
Step38:  Step 4
<ASSISTANT_TASK:>
Python Code:
# Install additional packages
!pip install -q matplotlib-venn
# Import all necessary libs
import json
import sys
import argparse
import pprint
import random
import datetime
import pandas as pd
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient import discovery
from oauthlib.oauth2.rfc6749.errors import InvalidGrantError
from google.auth.transport.requests import AuthorizedSession
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from googleapiclient.errors import HttpError
from matplotlib import pyplot as plt
from matplotlib_venn import venn3, venn3_circles
from IPython.display import display, HTML
from googleapiclient.errors import HttpError
from google.colab import auth
auth.authenticate_user()
print('Authenticated')
Explanation: Important
This content are intended for educational and informational purposes only.
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.
Target overlap analysis
Why?
In today’s digital ecosystem a consumer’s journey down the marketing funnel can be more complicated than ever. There are so many user touch points and ways to win (or lose) a customer along the way. Carefully moving consumers down the funnel is key to success. Therefore, we believe that brands that holistically think about the entire marketing funnel will generate the most demand and profitability.
By running targeting analysis you can intentionally increase the overlap of your branding and direct response media with coordinated targeting and creative.
What?
In this analysis we will be looking into the cookie size overlap across up to three different media strategies (DV360 Line Items or Insertion Oders), and (optionaly) how this overlap influenciates floodlight conversions/cookies rate.
Key notes / assumptions
* If User_id is 0  for a user we will not consider it (potentially due to user being in a Google Demographic or Affinity Segment, is direct traffic from Facebook or for publisher policy reasons).
ADH APIs Configuration Steps
Go to the Google Developers Console and verify that you have access to your Google Cloud project via the drop-down menu at the top of the page. If you don't see the right Google Cloud project, you should reach out to your Ads Data Hub team to get access.
From the project drop-down menu, select your Big Query project.
Click on the hamburger button on the top left corner of the page and click APIs & services > Credentials.
If you have not done so already, create an API key by clicking the Create credentials drop-down menu and select API key. This will create an API key that you will need for a later step.
If you have not done so already, create a new OAuth 2.0 client ID by clicking the Create credentials button and select OAuth client ID. For the Application type select Other and optionally enter a name to be associated with the client ID. Click Create to create the new Client ID and a dialog will appear to show you your client ID and secret. On the Credentials page for
   your project, find your new client ID listed under OAuth 2.0 client IDs, and click the corresponding download icon. The downloaded file will contain your credentials, which will be needed to step through the OAuth 2.0 installed application flow.
update the DEVELOPER_KEY field to match the
   API key you retrieved earlier.
Rename the credentials file you downloaded earlier to adh-key.json and upload the file in this colab (on the left menu click on the "Files" tab and then click on the "upload" button
Set Up - Install all dependencies and authorize bigQuery access
End of explanation
# TODO: Update the value of these variables with your own values
DEVELOPER_KEY = 'AIzaSyBa6P2oQ2m6T5M5LGUxuOKBsvzTDX1rnOw' #'INSERT_DEVELOPER_KEY_HERE'
CLIENT_SECRETS_FILE = 'adh-key.json' #'Make sure you have correctly renamed this file and you have uploaded it in this colab'
# Other configuration variables
_APPLICATION_NAME = 'ADH Campaign Overlap'
_CREDENTIALS_FILE = 'fcq-credentials.json'
_SCOPES = 'https://www.googleapis.com/auth/adsdatahub'
_DISCOVERY_URL_TEMPLATE = 'https://%s/$discovery/rest?version=%s&key=%s'
_FCQ_DISCOVERY_FILE = 'fcq-discovery.json'
_FCQ_SERVICE = 'adsdatahub.googleapis.com'
_FCQ_VERSION = 'v1'
_REDIRECT_URI = 'urn:ietf:wg:oauth:2.0:oob'
_SCOPE = ['https://www.googleapis.com/auth/adsdatahub']
_TOKEN_URI = 'https://accounts.google.com/o/oauth2/token'
MAX_PAGE_SIZE = 50
Explanation: API Configuration
The Developer Key is used to retrieve a discovery
document
This is used to build the service used to make API 
Client secret is used to authenticate to the API,  it can be downloaded from the Google Cloud Console (make sure you have correctly renamed the json file to 'adh-key.json')
End of explanation
#!/usr/bin/python
#
# Copyright 2017 Google Inc. All Rights Reserved.
#
# 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
#
#      http://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.
def _GetCredentialsFromInstalledApplicationFlow():
  Get new credentials using the installed application flow.
  flow = InstalledAppFlow.from_client_secrets_file(
      CLIENT_SECRETS_FILE, scopes=_SCOPE)
  flow.redirect_uri = _REDIRECT_URI  # Set the redirect URI used for the flow.
  auth_url, _ = flow.authorization_url(prompt='consent')
  print ('Log into the Google Account you use to access the Full Circle Query '
         'v2 API and go to the following URL:\n%s\n' % auth_url)
  print 'After approving the token, enter the verification code (if specified).'
  code = raw_input('Code: ')
  try:
    flow.fetch_token(code=code)
  except InvalidGrantError as ex:
    print 'Authentication has failed: %s' % ex
    sys.exit(1)
  credentials = flow.credentials
  _SaveCredentials(credentials)
  return credentials
def _LoadCredentials():
  Loads and instantiates Credentials from JSON credentials file.
  with open(_CREDENTIALS_FILE, 'rb') as handler:
    stored_creds = json.loads(handler.read())
  creds = Credentials(client_id=stored_creds['client_id'],
                      client_secret=stored_creds['client_secret'],
                      token=None,
                      refresh_token=stored_creds['refresh_token'],
                      token_uri=_TOKEN_URI)
  return creds
def _SaveCredentials(creds):
  Save credentials to JSON file.
  stored_creds = {
      'client_id': getattr(creds, '_client_id'),
      'client_secret': getattr(creds, '_client_secret'),
      'refresh_token': getattr(creds, '_refresh_token')
  }
  with open(_CREDENTIALS_FILE, 'wb') as handler:
    handler.write(json.dumps(stored_creds))
def GetCredentials():
  Get stored credentials if they exist, otherwise return new credentials.
  If no stored credentials are found, new credentials will be produced by
  stepping through the Installed Application OAuth 2.0 flow with the specified
  client secrets file. The credentials will then be saved for future use.
  Returns:
    A configured google.oauth2.credentials.Credentials instance.
  
  try:
    creds = _LoadCredentials()
    creds.refresh(Request())
  except IOError:
    creds = _GetCredentialsFromInstalledApplicationFlow()
  return creds
def GetDiscoveryDocument():
  Downloads the Full Circle Query v2 discovery document.
  Downloads the Full Circle Query v2 discovery document to fcq-discovery.json
  if it is accessible. If the file already exists, it will be overwritten.
  Raises:
    ValueError: raised if the discovery document is inaccessible for any reason.
  
  credentials = GetCredentials()
  discovery_url = _DISCOVERY_URL_TEMPLATE % (
      _FCQ_SERVICE, _FCQ_VERSION, DEVELOPER_KEY)
  auth_session = AuthorizedSession(credentials)
  discovery_response = auth_session.get(discovery_url)
  if discovery_response.status_code == 200:
    with open(_FCQ_DISCOVERY_FILE, 'wb') as handler:
      handler.write(discovery_response.text)
  else:
    raise ValueError('Unable to retrieve discovery document for api name "%s"'
                     'and version "%s" via discovery URL: %s'
                     % _FCQ_SERVICE, _FCQ_VERSION, discovery_url)
def GetService():
  Builds a configured Full Circle Query v2 API service.
  Returns:
    A googleapiclient.discovery.Resource instance configured for the Full Circle
      Query v2 service.
  
  credentials = GetCredentials()
  discovery_url = _DISCOVERY_URL_TEMPLATE % (
      _FCQ_SERVICE, _FCQ_VERSION, DEVELOPER_KEY)
  service = discovery.build(
      'adsdatahub', _FCQ_VERSION, credentials=credentials,
      discoveryServiceUrl=discovery_url)
  return service
def GetServiceFromDiscoveryDocument():
  Builds a configured Full Circle Query v2 API service via discovery file.
  Returns:
    A googleapiclient.discovery.Resource instance configured for the Full Circle
      Query API v2 service.
  
  credentials = GetCredentials()
  with open(_FCQ_DISCOVERY_FILE, 'rb') as handler:
    discovery_doc = handler.read()
  service = discovery.build_from_document(
      service=discovery_doc, credentials=credentials)
  return service
Explanation: API Authentication - OAuth2.0 Flow
Utility functions to execute the OAuth2.0 flow
End of explanation
try:
  full_circle_query = GetService()
except IOError as ex:
  print ('Unable to create ads data hub service - %s' % ex)
  print ('Did you specify the client secrets file in samples_util.py?')
  sys.exit(1)
try:
  # Execute the request.
  response = full_circle_query.customers().list().execute()
except HttpError as e:
  print (e)
  sys.exit(1)
if 'customers' in response:
  print ('ADH API Returned {} Ads Data Hub customers for the current user!'.format(len(response['customers'])))
  for customer in response['customers']:
    print(json.dumps(customer))
else:
  print ('No customers found for current user.')
Explanation: Actual Request to the Ads Data Hub Service API
End of explanation
#@title Define the data source in BigQuery:
customer_id = 000000001 #@param
dataset_id = 000000001 #@param
big_query_project = 'bq_project_id' #@param Destination Project ID {type:"string"}
big_query_dataset = 'dataset_name' #@param Destination Dataset {type:"string"}
big_query_destination_table = "table_name" #@param {type:"string"}
query_name = "query_name" #@param {type:"string"}
Explanation: Analysis 1: Impression/click campaign overlap
Step 1: Define analysis parameters
We will be looking at the overlap between users in the funnel for this example but this methodology could be used for any groups with potential overlap (e.g. flight sales and hotel sales) 
1.1 Data source:
First we will define which customer the data is coming from and where this data will be saved in BigQuery
We will also give the ADH query a name. This name must be unique and must not already exist in ADH. 
You must have a bigquery dataset and table set up in order to save results.
If you don't already have one you can create then in the BigQuery UI
End of explanation
#@title Define basic analysis parameters
group_type = 'dv360_insertion_order_id'#@param ["dv360_insertion_order_id", "dv360_line_item_id", "advertiser_id"]
overlap_type = 'impressions'#@param ["clicks", "impressions"]
start_date = '2019-08-01' #@param {type:"string"}
end_date = '2019-09-18' #@param {type:"string"}
Explanation: 1.2 Analysis Parameters:
Next we will set up the basic analysis parameters.
You can run the analysis at an IO or LI level based on either impressions or clicks
Select the dates for which you want to run the analysis
End of explanation
#@title Define IOs/LI ids (comma separated) that will be used to select each of the 3 groups. leave it as -1 to exclude a group.
group_1_ids = '10048874, 9939957'#@param {type:"string"}
group_2_ids = '10048146, 9956341'#@param {type:"string"}
group_3_ids = '10048875, 9939959'#@param {type:"string"}
Explanation: 1.3 Funnel groups:
For each of the groups list out the IO or LI ids you would like to include for the analysis
End of explanation
#@title Define friendly names (labels) for each group
group_1_lb = 'upper funnel'#@param {type:"string"}
group_2_lb = 'mid funnel'#@param {type:"string"}
group_3_lb = 'lower funnel'#@param {type:"string"}
if group_1_ids == '-1':
  group_1_lb = ''
if group_2_ids == '-1':
  group_2_lb = ''
if group_3_ids == '-1':
  group_3_lb = ''
Explanation: 1.4 Friendly Names:
Set the friendly name for each of the groups (e.g. upper funnel). These will be used in the visualisation
End of explanation
#assemble dynamic content dict
dc = {}
dc['group_type'] = group_type
dc['overlap_type'] = overlap_type
dc['start_date'] = start_date
dc['end_date'] = end_date
Explanation: Step 2: Assemble and run Query
2.1 Assemble Query
Set all the variables for the query
End of explanation
q1_1 = '''
### Step 1: Create a label for different targeted audiences and impressions
CREATE TABLE interactions_by_user_id_and_g AS (
  SELECT
    user_id,
    SUM(IF(event.{group_type} IN UNNEST(@group_1_ids),1,0)) AS imp_g_1,
    SUM(IF(event.{group_type} IN UNNEST(@group_2_ids),1,0)) AS imp_g_2,
    SUM(IF(event.{group_type} IN UNNEST(@group_3_ids),1,0)) AS imp_g_3
  FROM adh.cm_dt_{overlap_type} as imp
  WHERE 
  event.{group_type} IN UNNEST(ARRAY_CONCAT(@group_1_ids,@group_2_ids,@group_3_ids))
  AND 
  user_id != '0'
  GROUP BY 1
);'''
Explanation: Create the Query
For each user id:
Part 1 - find interactions:
* Identify if there has been an interaction (impression or click) for any of the listed IOs or LIs in each of the groups by comparing the IO or LI id (from the field defined in 'group_type' to the predefined list 
* filter for the IDs set- look at the IO or LI ID column and determine if it exists in the defined list
* filter out zero user IDs
End of explanation
q1_2 = '''  
#Part 2 - calculate metrics
SELECT  
  COUNT(interactions.user_id) AS Unique_Cookies,
  SUM(IF(interactions.imp_g_1 > 0 AND interactions.imp_g_2 + interactions.imp_g_3 = 0,1,0)) AS cookies_exclusive_g_1,
  SUM(IF(interactions.imp_g_2 > 0 AND interactions.imp_g_1 + interactions.imp_g_3 = 0,1,0)) AS cookies_exclusive_g_2,
  SUM(IF(interactions.imp_g_3 > 0 AND interactions.imp_g_1 + interactions.imp_g_2 = 0,1,0)) AS cookies_exclusive_g_3,
  SUM(IF(interactions.imp_g_1 > 0 AND interactions.imp_g_2 > 0 AND interactions.imp_g_3 = 0 ,1,0)) AS cookies_g_1_2,
  SUM(IF(interactions.imp_g_1 > 0 AND interactions.imp_g_3 > 0 AND interactions.imp_g_2 = 0,1,0)) AS cookies_g_1_3,
  SUM(IF(interactions.imp_g_3 > 0 AND interactions.imp_g_2 > 0 AND interactions.imp_g_1 = 0,1,0)) AS cookies_g_2_3,
  SUM(IF(interactions.imp_g_1 > 0 AND interactions.imp_g_2 > 0 AND interactions.imp_g_3 > 0 ,1,0)) AS cookies_g_1_2_3,
  #3 count total impressions
  SUM(interactions.imp_g_1 + interactions.imp_g_2 + interactions.imp_g_3) AS all_impressions,  
  
  #4 count total users
  SUM(1) AS total_cookies
FROM 
tmp.interactions_by_user_id_and_g AS interactions
'''
Explanation: Part 2 - calculate metrics: 
* For each of the groups and group combindations identify if any impressions have been logged 
* Identify the number of impressions from zero'd out users, total impressions, total users and the % of zero'd out users
End of explanation
query_text = (q1_1 + q1_2).format(**dc)
print('Final BigQuery SQL:')
print(query_text)
Explanation: Put the query together
Join the 3 parts of the query and use the python format function to pass through the parameters set in step 1
End of explanation
parameters_type = {
    "group_1_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    },
    "group_2_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    },
    "group_3_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    }
  }
Explanation: Set up group parameters
End of explanation
try:
  full_circle_query = GetService()
except IOError, ex:
  print 'Unable to create ads data hub service - %s' % ex
  print 'Did you specify the client secrets file?'
  sys.exit(1)
query_create_body = {
        'name': query_name,
        'title': query_name,
        'queryText': query_text,
        'parameterTypes': parameters_type
}
try:
  # Execute the request.
  new_query = full_circle_query.customers().analysisQueries().create(body=query_create_body, parent='customers/' + str(customer_id)).execute()
  new_query_name = new_query["name"]
except HttpError as e:
  print e
  sys.exit(1)
print 'New query %s created for customer ID "%s":' % (new_query_name, customer_id)
print(json.dumps(new_query))
Explanation: Create the Query in ADH
End of explanation
destination_table_full_path = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table
CUSTOMER_ID = customer_id
DATASET_ID = dataset_id
QUERY_NAME = query_name
DEST_TABLE = destination_table_full_path
#Dates
format_str = '%Y-%m-%d' # The format
start_date_obj = datetime.datetime.strptime(start_date, format_str)
end_date_obj = datetime.datetime.strptime(end_date, format_str)
START_DATE = {
  "year": start_date_obj.year,
  "month": start_date_obj.month,
  "day": start_date_obj.day
}
END_DATE = {
  "year": end_date_obj.year,
  "month": end_date_obj.month,
  "day": end_date_obj.day
}
try:
  full_circle_query = GetService()
except IOError, ex:
  print('Unable to create ads data hub service - %s' % ex)
  print('Did you specify the client secrets file?')
  sys.exit(1)
query_start_body = {
  'spec': {
      'adsDataCustomerId': DATASET_ID,
      'startDate': START_DATE,
      'endDate': END_DATE,
      'parameterValues': 
          {"group_1_ids": 
              {"value": group_1_ids},
          "group_2_ids": 
              {"value": group_2_ids},
          "group_3_ids": 
              {"value": group_3_ids},
          }
        },
  'destTable': DEST_TABLE,
  'customerId': CUSTOMER_ID
}
try:
  # Execute the request.
  operation = full_circle_query.customers().analysisQueries().start(body=query_start_body, name=new_query_name).execute()
except HttpError as e:
  print(e)
  sys.exit(1)
print('Running query with name "%s" via the following operation:' % query_name)
print(json.dumps(operation))
Explanation: Check the query exists in ADH
Full Query
2.2 Execute the query
End of explanation
import time
statusDone = False
while statusDone is False:
  print("waiting for the job to complete...")
  updatedOperation = full_circle_query.operations().get(name=operation['name']).execute()
  if updatedOperation.has_key('done') and updatedOperation['done'] == True:
    statusDone = True
  
  if(statusDone == False):
    time.sleep(5)
print("Job completed... Getting results")
#run bigQuery query
dc = {}
dc['table'] = big_query_dataset + '.' + big_query_destination_table
q1 = '''
select * from {table} 
  '''.format(**dc)
df1 = pd.io.gbq.read_gbq(q1, project_id=big_query_project, dialect='standard')
print('Total Cookies: ' +  str(df1.total_cookies[0]))
Explanation: 2.3  Retrieve the result from BigQuery
Pass the query (q1) and billing project id in order to run the query
We are using a direct pandas integration with BigQuery in order to run this
End of explanation
from __future__ import division
# define main variables
cookies = {}
#Total
cookies['all'] = df1.total_cookies[0]
cookies['g1'] = df1.cookies_exclusive_g_1[0]
cookies['g2'] = df1.cookies_exclusive_g_2[0]
cookies['g3'] = df1.cookies_exclusive_g_3[0]
cookies['g12'] = df1.cookies_g_1_2[0]
cookies['g13'] = df1.cookies_g_1_3[0]
cookies['g23'] = df1.cookies_g_2_3[0]
cookies['g123'] = df1.cookies_g_1_2_3[0]
#percentage 
cookies_p = {}
cookies_p['all'] = 1
cookies_p['g1'] = cookies['g1']/cookies['all']
cookies_p['g2'] = cookies['g2']/cookies['all']
cookies_p['g3'] = cookies['g3']/cookies['all']
cookies_p['g12'] = cookies['g12']/cookies['all']
cookies_p['g13'] = cookies['g13']/cookies['all']
cookies_p['g23'] = cookies['g23']/cookies['all']
cookies_p['g123'] = cookies['g123']/cookies['all']
# define table labels from variables at the start 
table_labels = {
  'g1':'1 - ' + group_1_lb,
  'g2':'2 - ' + group_2_lb,
  'g3':'3 - ' + group_3_lb,
  'g12':'4 - ' + group_1_lb + ' and ' + group_2_lb,
  'g13':'5 - ' + group_1_lb + ' and ' + group_3_lb,
  'g23':'6 - ' + group_2_lb + ' and ' + group_3_lb,
  'g123':'7 - ' + group_1_lb + ', '+ group_2_lb + ' and ' + group_3_lb,
  'all': 'total'
}
#display results in table
def create_df_series(data_dict,labels):
  retVal = {}
  for key in data_dict:
    data = data_dict[key]
    label = labels[key]
    retVal[label] = data
  return retVal
col_cookies_percent = pd.Series(create_df_series(cookies_p,table_labels))
col_cookies = pd.Series(create_df_series(cookies,table_labels))
df_1_summary = pd.DataFrame({'Cookies':col_cookies,'cookies (%)':col_cookies_percent})
df_1_summary
Explanation: Step 3: Calculate auxiliary metrics
For each group and combination of groups calculate the total number of cookies and the percentage of cookies
Label each group with a friendly name and display the output in a table
End of explanation
#create diagram image
plt.figure(figsize=(20,15))
plt.title("Cookie Overlap across %s, %s and %s"%(group_1_lb, group_2_lb, group_3_lb))
venn_data_subset = [
  cookies_p['g1'],cookies_p['g2'],cookies_p['g12'],
  cookies_p['g3'],cookies_p['g13'],cookies_p['g23'],cookies_p['g123']
  ]
v = venn3(
      subsets = venn_data_subset, 
      set_labels = (group_1_lb, group_2_lb, group_3_lb)
    )
#replace diagram labels
def replace_diagram_labels(v):
    for i, sl in enumerate(v.subset_labels):
        if sl is not None:
            sl.set_text(str(round(float(sl.get_text())*100,1))+'%\nof all cookies')
#plot diagram
replace_diagram_labels(v)
plt.show()
Explanation: Step 4 - Display the output
End of explanation
#@title Define floodlight ids (comma separated) that will be used as conversion
floodlight_activity_ids = '3716682,3714314,3716571,3714314,1399226'#@param
dc['activity_ids'] = floodlight_activity_ids
query_name_2 = 'query_name'#@param
big_query_destination_table_2 = 'table_name'#@param
Explanation: What is the overlap between your groups? 
Are your upper funnel users moving through the funnel? 
Analysis 2: Conversion Rate Impact on overlap
Step 1: Define additional analysis parameters
Set the list of floodlight activities to use for attributing conversions
All other parameters will be fetched from previous analysis
If overlap_type is set to click, query will perform a post-click attribution. If overlap_type is set to impression, query will perform a post-impression attribution
We will need to create a new unique query name and set a new BigQuery destination table
End of explanation
q2_1 = '''
#Part 1: Get interactions
with interactions AS (
SELECT 
  user_id,
  IF(event.{group_type} IN UNNEST(@group_1_ids),1,0) AS imp_g_1,
  IF(event.{group_type} IN UNNEST(@group_2_ids),1,0) AS imp_g_2,
  IF(event.{group_type} IN UNNEST(@group_3_ids),1,0) AS imp_g_3,
  event.event_time AS interaction_event_time
  FROM adh.cm_dt_{overlap_type}
  WHERE event.{group_type} IN UNNEST(ARRAY_CONCAT(@group_1_ids,@group_2_ids,@group_3_ids))
  AND user_id <> '0' #remove zeroed out ids
  ),'''
Explanation: Step 2: Assemble and Run the Query
2.1: Assemble Query
Part 1 - Get interactions: If the IO/LI is in the defined list of IOs/LIs for each of your groups - count as 1 from the click or impression data (overlap type) 
Part 2 - Filter date data
Part 3 - Get conversiions: 
Create a unique id for user-id + event time so that each event can be counted as a distinct event
Find all the conversion events in your defined list 
Part 4 - Find the interactions that lead to conversions: 
Join all the interactions (clicks or impressions) to all the conversions with a left join to see which interactions have a conversion using user id as the join key and only considering interactions that happened before the conversion event
Part 5 - For each user find the number of conversions and impressions
Count the distinct number of conversions per user and the number of impressions in each of the 3 funnel groups (IO / LIs)
Part 6 - For each combination of groups: 
For each group find the number of cookies and find the number of conversions
Part 1 - Get interactions
Looking at the impressions or clicks table 
Create a column for each of the groups
If the IO/LI id is in the defined list of IOs/LIs for each of your groups - count as 1 from the click or impression data (overlap type)
Data is grouped by user ID 
filter for the IDs set- look at the IO or LI ID column and determine if it exists in the defined list
filter out zero user IDs
End of explanation
q2_2 = '''
  conversions AS (
    SELECT
    user_id,
    event.event_time AS conversion_event_time
    FROM adh.cm_dt_activities
    WHERE CAST(event.activity_id AS INT64) IN UNNEST(@activity_ids)
    AND user_id <> '0'
    #GROUP BY 1
  ),
'''
Explanation: output example:
<table>
  <tr>
    <th>user_id</th>
    <th>imp_g_1</th> 
    <th>imp_g_2</th>
    <th>imp_g_3</th> 
    <th>interaction_event_time</th>
  </tr>
  <tr>
    <td>001</td>
    <td>0</td> 
    <td>1</td>
    <td>0</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>002</td>
    <td>0</td> 
    <td>0</td>
    <td>1</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>003</td>
    <td>1</td> 
    <td>0</td>
    <td>0</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>001</td>
    <td>0</td> 
    <td>1</td>
    <td>0</td>
    <td>timestamp</td>
  </tr>
  <tr>
    <td>001</td>
    <td>1</td> 
    <td>0</td>
    <td>0</td>
    <td>timestamp</td>
  </tr>
</table>
Part 2: Get conversions
Looking at the activity table 
Filter the data to find the conversion events in your defined list of floodlight activity ids
End of explanation
q2_3 = '''
#define which of these interactions led to a conversion
impressions_and_conversions AS(
  SELECT 
    t0.user_id AS user_id,
    t1.conversion_event_time,
    t0.imp_g_1 AS imp_g_1,
    t0.imp_g_2 AS imp_g_2,
    t0.imp_g_3 AS imp_g_3
  FROM interactions As t0
    LEFT JOIN conversions t1 ON t0.user_id = t1.user_id
  WHERE 
    t1.user_id IS NULL OR
    interaction_event_time < conversion_event_time
),
'''
Explanation: output example:
<table>
  <tr>
    <th>user_id</th>
    <th>conversion_event_time</th>
  </tr>
  <tr>
    <td>001</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>002</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>003</td>
    <td>timestamp</td>
  </tr>
    <tr>
    <td>004</td>
    <td>timestamp</td>
  </tr>
</table>
Part 3 - Find the interactions that lead to conversions:
Join all the interactions (clicks or impressions) to all the conversions with a left join to see which interactions have a conversion using user id as the join key and only considering interactions that happened before the conversion event
End of explanation
q2_4 = '''
#aggregate interactions per user
results_by_user_id AS (
SELECT 
  user_id,
  COUNT(DISTINCT conversion_event_time)  AS conversions,
  SUM(imp_g_1) AS imp_g_1,
  SUM(imp_g_2) AS imp_g_2,
  SUM(imp_g_3) AS imp_g_3
  FROM impressions_and_conversions
  GROUP BY 1
)
'''
Explanation: example output: 
<table>
  <tr>
    <th>user_id</th>
    <th>conversion_event_tim</th> 
    <th>imp_g_1</th>
    <th>imp_g_2</th>
    <th>imp_g_3</th>
  </tr>
  <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>0</td> 
    <td>1</td>
    <td>0</td>
  </tr>
    <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>0</td> 
    <td>1</td>
    <td>0</td>
  </tr>
    <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>1</td> 
    <td>0</td>
    <td>0</td>
  </tr>
    <tr>
    <td>002</td>
    <td>timestamp</td> 
    <td>0</td> 
    <td>0</td>
    <td>1</td>
  </tr>
    <tr>
    <td>003</td>
    <td>timestamp</td> 
    <td>1</td> 
    <td>0</td>
    <td>0</td>
  </tr>
</table>
Part 4 - For each user find the number of conversions and impressions
Count the distinct number of conversions per user and the number of impressions in each of the 3 funnel groups (IO / LIs)
End of explanation
q2_5 = '''
#calculate group overlaps per user
SELECT
  #cookie count
  SUM(IF(imp_g_1 > 0 AND imp_g_2 + imp_g_3 = 0,1,0)) AS cookies_exclusive_g_1,
  SUM(IF(imp_g_2 > 0 AND imp_g_1 + imp_g_3 = 0,1,0)) AS cookies_exclusive_g_2,
  SUM(IF(imp_g_3 > 0 AND imp_g_1 + imp_g_2 = 0,1,0)) AS cookies_exclusive_g_3,
  SUM(IF(imp_g_1 > 0 AND imp_g_2 > 0 AND imp_g_3 = 0 ,1,0)) AS cookies_g_1_2,
  SUM(IF(imp_g_1 > 0 AND imp_g_3 > 0 AND imp_g_2 = 0,1,0)) AS cookies_g_1_3,
  SUM(IF(imp_g_3 > 0 AND imp_g_2 > 0 AND imp_g_1 = 0,1,0)) AS cookies_g_2_3,
  SUM(IF(imp_g_1 > 0 AND imp_g_2 > 0 AND imp_g_3 > 0 ,1,0)) AS cookies_g_1_2_3,
  #conversion count
  SUM(IF(imp_g_1 > 0 AND imp_g_2 + imp_g_3 = 0,conversions,0)) AS conversions_exclusive_g_1,
  SUM(IF(imp_g_2 > 0 AND imp_g_1 + imp_g_3 = 0,conversions,0)) AS conversions_exclusive_g_2,
  SUM(IF(imp_g_3 > 0 AND imp_g_1 + imp_g_2 = 0,conversions,0)) AS conversions_exclusive_g_3,
  SUM(IF(imp_g_1 > 0 AND imp_g_2 > 0 AND imp_g_3 = 0 ,conversions,0)) AS conversions_g_1_2,
  SUM(IF(imp_g_1 > 0 AND imp_g_3 > 0 AND imp_g_2 = 0,conversions,0)) AS conversions_g_1_3,
  SUM(IF(imp_g_3 > 0 AND imp_g_2 > 0 AND imp_g_1 = 0,conversions,0)) AS conversions_g_2_3,
  SUM(IF(imp_g_1 > 0 AND imp_g_2 > 0 AND imp_g_3 > 0 ,conversions,0)) AS conversions_g_1_2_3,
  
  #total metrics count
  SUM(conversions) AS total_conversions,
  COUNT(1) AS total_cookies,
  SUM(conversions) / COUNT(1) As total_conversions_per_cookie
  
FROM results_by_user_id
'''
Explanation: example output: 
<table>
  <tr>
    <th>user_id</th>
    <th>conversions</th> 
    <th>imp_g_1</th>
    <th>imp_g_2</th>
    <th>imp_g_3</th>
  </tr>
  <tr>
    <td>001</td>
    <td>3</td>
    <td>1</td>
    <td>2</td>
    <td>0</td>
  </tr>
    <tr>
    <td>002</td>
    <td>1</td>
    <td>0</td>
    <td>0</td>
    <td>1</td>
  </tr>
    <tr>
    <td>003</td>
    <td>1</td>
    <td>1</td>
    <td>0</td>
    <td>0</td>
  </tr>
</table>
Part 5 - For each combination of groups:
For each group find the number of cookies and find the number of conversions
End of explanation
dc = {}
dc['group_type'] = group_type
dc['overlap_type'] = overlap_type
dc['activity_ids'] = floodlight_activity_ids
q2 = (q2_1 + q2_2 + q2_3 + q2_4 + q2_5).format(**dc)
print('Final BigQuery SQL:')
print(q2)
Explanation: example output: 
<table>
  <tr>
    <th>cookies_exclusive_g_1</th>
    <th>cookies_exclusive_g_2</th> 
    <th>cookies_exclusive_g_3</th>
    <th>cookies_g_1_2</th>
    <th>cookies_g_1_3</th>
    <th>cookies_g_2_3</th>
    <th>cookies_g_1_2_3</th>
    <th>conversions_exclusive_g_1</th>
    <th>conversions_exclusive_g_2</th> 
    <th>conversions_exclusive_g_3</th>
    <th>conversions_g_1_2</th>
    <th>conversions_g_1_3</th>
    <th>conversions_g_2_3</th>
    <th>conversions_g_1_2_3</th>
    <th>total_conversions</th>
    <th>total_cookies</th>
    <th>total_conversions_per_cookie</th>
  </tr>
  <tr>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  </table>
Assemble the Query and display the output
End of explanation
parameters_type = {
    "group_1_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    },
    "group_2_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    },
    "group_3_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    },
    "activity_ids": {
      "defaultValue": {
        "value": ""
      },
      "type": {
        "arrayType": {
          "type": "INT64"
        }
      }
    }
  }
Explanation: Set the parameter types
ADH allows you to create variables
We will pass this in to the request when we create the query so we need to define what the types are
End of explanation
try:
  full_circle_query = GetService()
except IOError, ex:
  print 'Unable to create ads data hub service - %s' % ex
  print 'Did you specify the client secrets file?'
  sys.exit(1)
query_create_body = {
        'name': query_name_2,
        'title': query_name_2,
        'queryText': q2,
        'parameterTypes': parameters_type
}
try:
  # Execute the request.
  new_query = full_circle_query.customers().analysisQueries().create(body=query_create_body, parent='customers/' + str(customer_id)).execute()
  new_query_name = new_query["name"]
except HttpError as e:
  print e
  sys.exit(1)
print 'New query created for customer ID "%s":' % customer_id
print(json.dumps(new_query))
Explanation: Create the query in ADH
End of explanation
destination_table_full_path = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table_2
CUSTOMER_ID = customer_id
DATASET_ID = dataset_id
QUERY_NAME = query_name
DEST_TABLE = destination_table_full_path
#Dates
format_str = '%Y-%m-%d' # The format
start_date_obj = datetime.datetime.strptime(start_date, format_str)
end_date_obj = datetime.datetime.strptime(end_date, format_str)
START_DATE = {
  "year": start_date_obj.year,
  "month": start_date_obj.month,
  "day": start_date_obj.day
}
END_DATE = {
  "year": end_date_obj.year,
  "month": end_date_obj.month,
  "day": end_date_obj.day
}
try:
  full_circle_query = GetService()
except IOError, ex:
  print('Unable to create ads data hub service - %s' % ex)
  print('Did you specify the client secrets file?')
  sys.exit(1)
query_start_body = {
  'spec': {
      'adsDataCustomerId': DATASET_ID,
      'startDate': START_DATE,
      'endDate': END_DATE,
      'parameterValues': 
          {"group_1_ids": 
              {"value": group_1_ids},
          "group_2_ids": 
              {"value": group_2_ids},
          "group_3_ids": 
              {"value": group_3_ids},
          "activity_ids": 
              {"value": floodlight_activity_ids}
          }
        },
        'destTable': DEST_TABLE,
        'customerId': CUSTOMER_ID
}
try:
  # Execute the request.
  operation = full_circle_query.customers().analysisQueries().start(body=query_start_body, name=new_query_name).execute()
except HttpError as e:
  print(e)
  sys.exit(1)
print('Running query with name "%s" via the following operation:' % query_name)
print(json.dumps(operation))
Explanation: 2.2: Run the query
End of explanation
statusDone = False
while statusDone is False:
  print("waiting for the job to complete...")
  updatedOperation = full_circle_query.operations().get(name=operation['name']).execute()
  if updatedOperation.has_key('done') and updatedOperation['done'] == True:
    statusDone = True
  if(statusDone == False):
    time.sleep(5)
print("Job completed... Getting results")
#run bigQuery query
dc = {}
dc['table'] = big_query_dataset + '.' + big_query_destination_table_2
q1 = '''
select * from {table} 
  '''.format(**dc)
df2 = pd.io.gbq.read_gbq(q1, project_id=big_query_project, dialect='standard')
print(dc['table'])
print('Total Cookies: ' +  str(df2.total_cookies[0]))
print('Total Conversions: ' +  str(df2.total_conversions[0]))
Explanation: 2.3 Get the results from BigQuery
End of explanation
# define main variables
cookies = {}
cookies['all'] = df2.total_cookies[0]
cookies['g1'] = df2.cookies_exclusive_g_1[0]
cookies['g2'] = df2.cookies_exclusive_g_2[0]
cookies['g3'] = df2.cookies_exclusive_g_3[0]
cookies['g12'] = df2.cookies_g_1_2[0]
cookies['g13'] = df2.cookies_g_1_3[0]
cookies['g23'] = df2.cookies_g_2_3[0]
cookies['g123'] = df2.cookies_g_1_2_3[0]
cookies_p = {}
cookies_p['all'] = 1
cookies_p['g1'] = cookies['g1']/cookies['all']
cookies_p['g2'] = cookies['g2']/cookies['all']
cookies_p['g3'] = cookies['g3']/cookies['all']
cookies_p['g12'] = cookies['g12']/cookies['all']
cookies_p['g13'] = cookies['g13']/cookies['all']
cookies_p['g23'] = cookies['g23']/cookies['all']
cookies_p['g123'] = cookies['g123']/cookies['all']
# define table labels
table_labels = {
  'g1':'1 - ' + group_1_lb,
  'g2':'2 - ' + group_2_lb,
  'g3':'3 - ' + group_3_lb,
  'g12':'4 - ' + group_1_lb + ' and ' + group_2_lb,
  'g13':'5 - ' + group_1_lb + ' and ' + group_3_lb,
  'g23':'6 - ' + group_2_lb + ' and ' + group_3_lb,
  'g123':'7 - ' + group_1_lb + ', '+ group_2_lb + ' and ' + group_3_lb,
  'all': 'total'
}
#display results in table
def create_df_series(data_dict,labels):
  retVal = {}
  for key in data_dict:
    data = data_dict[key]
    label = labels[key]
    retVal[label] = data
  return retVal
col_cookies_percent = pd.Series(create_df_series(cookies_p,table_labels))
col_cookies = pd.Series(create_df_series(cookies,table_labels))
df_2_summary = pd.DataFrame({'Cookies':col_cookies,'cookies (%)':col_cookies_percent})
df_2_summary
Explanation: Step 3: Set up the data to display
3.1 Define the variables for displaying in the chart
Use the response from the query to set all the variables for the chart
End of explanation
#calculate cookie overlap across groups
all_cookies = df2.total_cookies[0]
g1 = round((df2.cookies_exclusive_g_1[0]/all_cookies*100),2)
g2 = round((df2.cookies_exclusive_g_2[0]/all_cookies)*100, 2)
g3 = round((df2.cookies_exclusive_g_3[0]/all_cookies)*100, 2)
g12 = round((df2.cookies_g_1_2[0]/all_cookies)*100, 2)
g13 = round((df2.cookies_g_1_3[0]/all_cookies)*100, 2)
g23 = round((df2.cookies_g_2_3[0]/all_cookies)*100, 2)
g123 = round((df2.cookies_g_1_2_3[0]/all_cookies)*100, 2)
#calculate conversions per cookie metric
all_conversions = df2.total_conversions[0]
g1_conv_user = round((df2.conversions_exclusive_g_1[0]/df2.cookies_exclusive_g_1[0]*100),3)
g2_conv_user = round((df2.conversions_exclusive_g_2[0]/df2.cookies_exclusive_g_2[0]*100),3)
g3_conv_user = round((df2.conversions_exclusive_g_3[0]/df2.cookies_exclusive_g_3[0]*100),3)
g12_conv_user = round((df2.conversions_g_1_2[0]/df2.cookies_g_1_2[0]*100),3)
g13_conv_user = round((df2.conversions_g_1_3[0]/df2.cookies_g_1_3[0]*100),3)
g23_conv_user = round((df2.conversions_g_2_3[0]/df2.cookies_g_2_3[0]*100),3)
g123_conv_user = round((df2.conversions_g_1_2_3[0]/df2.cookies_g_1_2_3[0]*100),3)
Explanation: 3.2 Calculate the percentage cookie overlap and conversion rate and format the values
Calculate the percentage of cookie overlap and the percentage of conversions for each group in order to display on the chart
End of explanation
conv_per_cookie = [g1_conv_user, g2_conv_user, g12_conv_user, g3_conv_user,g13_conv_user,g23_conv_user,g123_conv_user]
subsets = ['g1', 'g2', 'g12', 'g3', 'g13', 'g23', 'g123']
plt.figure(figsize=(25,15))
plt.title("Cookie Overlap across %s, %s and %s"%(group_1_lb, group_2_lb, group_3_lb))
v = venn3(subsets = (g1, g2, g12, g3, g13, g23, g123), set_labels = (group_1_lb, group_2_lb, group_3_lb))
def replace_diagram_labels(v):
    for i, sl in enumerate(v.subset_labels):
        if sl is not None:
            print(table_labels[subsets[i]] +': '+ sl.get_text()+'% of all cookies. '+str(conv_per_cookie[i])+'% cvr')
            sl.set_text(sl.get_text()+'% of all cookies. \n'+str(conv_per_cookie[i])+'% cvr')
replace_diagram_labels(v)
plt.show()
Explanation: Step 4: Display the output
Plot the chart
End of explanation
<END_TASK>