<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 PLEASE MAKE A COPY BEFORE CHANGING
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:  Install Dependencies
Step2:  Define function to enable charting library
Step11:  Authenticate against the ADH API
ADH documentation
Step12:  Frequency Analysis
<b>Purpose
Step13:  Step 2 - Create a function for the final calculations
From DT data Calculate metrics using pandas 
Pass through the pandas dataframe when you call this function
Step14:  Step 3 - Build the query
Step 3a - Query for reach & frequency
Set up the vairables
Step16:  Part 1 - Find all impressions from the impression table
Step18:  Part 2 - Find all clicks from the clicks table
Step20:  output example
Step22:  output example
Step23:  output example
Step24:  Create the query required for ADH 
* When working with ADH the standard BigQuery query needs to be adapted to run in ADH
* This can be done bia the API
Step26:  Step 3b - Query for demographics & interest
Step28:  output example
Step30:  output example
Step31:  output example
Step32:  Step 5 - Retrieve the table from BigQuery
Retrieve the results from BigQuery
Check to make sure the query has finished running and is saved in the new BigQuery Table 
When it is done we cane retrieve it
Step33:  We are using the pandas library to run the query. 
We pass in the query (q), the project id and set the SQL language to 'standard' (as opposed to legacy SQL)
Step34:  Save the output as a CSV
Step35:  Setup up the dataframe and preview to check the data.
Step36:  Step 6 - Set up the data and all the charts that will be plotted
6.1 Transform data
Use the calculation function created to calculate all the values based off your data
Step37:  Analysis 1
Step38:  Step 2
Step39:  Output
Step40:  Clicks and CPC Comparison on Each Frequency
What is your CPC ceiling
Understand what the frequency is at that level
Determine what impact changing your frequency will have on clicks
Step41:  CTR and CPC Comparison on Each Frequency
How does your CTR and CPC impact each other 
Make an informed decision regarding suitable goals
Step42:  Cumulative Clicks and CPC Comparison on Each Frequency
Understand what a suitable CPC goal might be
1. What is the change in cost for increased clicks
2. What is the incremental gains for an increased cost
Step43:  Cumulative Clicks and CTR Comparison on Each Frequency
At what frequency does your CTR drop below an acceptable value
Step44:  Analysis 2
Step45:  Output
Step46:  Cummulative Impressions and Cummulative Clicks on Each Frequency
To obtain my goals in terms of clicks, what frequency do I need, at what impression cost?
Step47:  Analysis 3
Step48:  Output
Step49:  Analysis 4
Step50:  Ouput
Step51:  check whether big bubble is in the bottom left corner, those are the affinities with lowest efficency
bubble in the upper left corner are the affinities with high potentials
you should keep your investment in the upper right hand corner bubble
shift budget from bottom right hand corner to other area
In market bubble chart
What in market segment is underreaching?
What in market segment should I switch the cost to?
Step52:  check whether big bubble is in the bottom left corner, those are the in market segment with lowest efficency
bubble in the upper left corner are the in market segment with high potentials
you should keep your investment in the upper right hand corner bubble
shift budget from bottom right hand corner to other area
age & gender bubble chart
which gender is underreaching?
Which age group should I switch the cost to?
<ASSISTANT_TASK:>
Python Code:
# The Developer Key is used to retrieve a discovery document containing the
# non-public Full Circle Query v2 API. This is used to build the service used
# in the samples to make API requests. Please see the README for instructions
# on how to configure your Google Cloud Project for access to the Full Circle
# Query v2 API.
DEVELOPER_KEY = 'xxxx' #'INSERT_DEVELOPER_KEY_HERE'
# The client secrets file can be downloaded from the Google Cloud Console.
CLIENT_SECRETS_FILE = 'adh-key.json' #'Make sure you have correctly renamed this file and you have uploaded it in this colab'
Explanation: PLEASE MAKE A COPY BEFORE CHANGING
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.
Important
This content are intended for educational and informational purposes only.
Configuration
ADH APIs Configuration Steps
Enable the ADH v1 API in the Google Cloud Storage account you use to access the API.
When searching for the API in your GCP Console API Library, use the search term “adsdatahub”. 
Go to the Google Developers Console and verify that you have access to the Full Circle Query project via the drop-down menu at the top of the page. If you don't see the Full Circle Query project, you should reach out to the Ads Data Hub support 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]((https://console.cloud.google.com/apis/credentials) 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
End of explanation
import json
import sys
import argparse
import pprint
import random
import datetime
import pandas as pd
import plotly.plotly as py
import plotly.graph_objs as go
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 plotly.offline import iplot
from plotly.graph_objs import Contours, Histogram2dContour, Marker, Scatter
from googleapiclient.errors import HttpError
from google.colab import auth
auth.authenticate_user()
print('Authenticated')
Explanation: Install Dependencies
End of explanation
# Allow plot images to be displayed
%matplotlib inline
# Functions
def enable_plotly_in_cell():
    import IPython
    from plotly.offline import init_notebook_mode
    display(IPython.core.display.HTML('''
          <script src="/static/components/requirejs/require.js"></script>
    '''))
    init_notebook_mode(connected=False)
Explanation: Define function to enable charting library
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.
Utilities used to step through OAuth 2.0 flow.
These are intended to be used for stepping through samples for the Full Circle
Query v2 API.
_APPLICATION_NAME = 'ADH Campaign Overlap'
_CREDENTIALS_FILE = 'fcq-credentials.json'      #just to rewrite a new credential file, not for users to provide
_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
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 adsdatahub Query '
         'v1 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 adsdatahub v1 discovery document.
  Downloads the adsdatahub v1 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 adsdatahub v1 API service.
  Returns:
    A googleapiclient.discovery.Resource instance configured for the adsdatahub v1 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
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: Authenticate against the ADH API
ADH documentation
End of explanation
#@title Define ADH configuration parameters
customer_id = 000000001 #@param
query_name = 'test1' #@param
big_query_project = 'adh-scratch' #@param Destination Project ID
big_query_dataset = 'test' #@param Destination Dataset
big_query_destination_table = 'freqanalysis_test' #@param Destination Table
big_query_destination_table_affinity = 'freqanalysis_test_affinity' #@param Affinity Destination Table
big_query_destination_table_inmarket = 'freqanalysis_test_inmarket' #@param In-market Destination Table
big_query_destination_table_age_gender = 'freqanalysis_test_age_gender' #@param Age/Gender Destination Table
start_date = '2019-12-01' #@param {type:"date", allow-input: true}
end_date = '2019-12-31' #@param {type:"date", allow-input: true}
max_freq = 100 #@param {type:"integer", allow-input: true}
cpm =  12#@param {type:"number", allow-input: true}
id_type = "campaign_id" #@param ["", "advertiser_id", "campaign_id", "placement_id", "ad_id"] {type: "string", allow-input: false}
IDs = "12345678" #@param {type: "string", allow-input: true}
Explanation: Frequency Analysis
<b>Purpose:</b> This tool should be used to guide you defining an optimal frequency cap considering the CTR curve. Due to that it is more useful in awareness use cases.
Key notes
For some campaings the user ID will be <b>zeroed</b> (e.g. Googel Data, ITP browsers and YouTube Data), therefore <b>excluded</b> from the analysis. For more information click <a href="https://support.google.com/dcm/answer/9006418" > here</a>;
It will be only included in the analysis campaigns which clicks and impressions were tracked.
Instructions
*   First of all: <b>MAKE A COPY</b> =);
*   Fulfill the query parameters in the Box 1;
*   In the menu above click in Runtime > Run All;
*   Authorize your credentials;
*   Go to the end of the colab and your figures will be ready;
*   After defining what should be the optimal frequency cap fill it in the Box 2 and press play.
Step 1 - Instructions - Defining parameters to find the optimal frequency
<b>max_freq:</b> Stands for the amount of frequency you want to plot the graphics (e.g. if you put 50, you will look for impressions that was shown up to 50 times for users);
<b>id_type:</b> How do you want to filter your data (if you don't want to filter leave it blank);
<b>IDs:</b> Accordingly to the id_type chosen before, fill in this field following this patterns: 'id-1111', 'id-2222', ...
End of explanation
def df_calc_fields(df):
    df['ctr'] = df.clicks / df.impressions
    df['cpc'] = df.cost / df.clicks
    df['cumulative_clicks'] = df.clicks.cumsum()
    df['cumulative_impressions'] = df.impressions.cumsum()
    df['cumulative_reach'] = df.reach.cumsum()
    df['cumulative_cost'] = df.cost.cumsum()
    df['coverage_clicks'] = df.cumulative_clicks / df.clicks.sum()
    df['coverage_impressions'] = df.cumulative_impressions / df.impressions.sum()
    df['coverage_reach'] = df.cumulative_reach / df.reach.sum()
    return df
Explanation: Step 2 - Create a function for the final calculations
From DT data Calculate metrics using pandas 
Pass through the pandas dataframe when you call this function
End of explanation
# Build the query
dc = {}
if (IDs == ""):
  dc['ID_filters'] = ""
else:
  dc['id_type'] = id_type
  dc['IDs'] = IDs
  dc['ID_filters'] = '''AND {id_type} IN ({IDs})'''.format(**dc)
#create global query list
global_query_name = []
Explanation: Step 3 - Build the query
Step 3a - Query for reach & frequency
Set up the vairables
End of explanation
q1 = 
    WITH
    imp_u_clicks AS (
    SELECT
        user_id,
        query_id.time_usec AS interaction_time,
        0 AS cost,
        'imp' AS interaction_type
    FROM
        adh.google_ads_impressions
    WHERE
        user_id != '0' {ID_filters}
Explanation: Part 1 - Find all impressions from the impression table: 
* Select all user IDs from the impression table
* Select the event_time
* Mark the interaction type as 'imp' for all of these rows
* Filter for the dates set in Step 1 using the partition files to reduce bigQuery costs by only searching in files within a 2 day interval of the set date range
* Filter out any user IDs that are 0
* If specific ID filters were applied in Step 1 filter the data for those IDs
End of explanation
q2 = 
    UNION ALL (
        SELECT
          user_id,
          click_id.time_usec AS interaction_time,
          advertiser_click_cost_usd AS cost,
          'click' AS interaction_type
        FROM
          adh.google_ads_clicks
        WHERE
           user_id != '0' AND impression_data.{id_type} IN ({IDs}) ) ),
Explanation: Part 2 - Find all clicks from the clicks table: 
Select all User IDs from the click table
Select the event_time
Mark the interaction type as 'click' for all of these rows
Filter for the dates set in Step 1 using the partition files to reduce BigQuery costs by only searching in files within a 2 day interval of the set date range
If specific ID filters were applied in Step 2 filter the data for those IDs 
Use a union to create a single table with both impressions and clicks
End of explanation
q3 =         
    user_level_data AS (
    SELECT
        user_id,
        SUM(IF(interaction_type = 'imp',
            1,
            0)) AS impressions,
        SUM(IF(interaction_type = 'click',
            1,
            0)) AS clicks,
        SUM(cost) AS cost
    FROM
        imp_u_clicks
    GROUP BY
        user_id)
Explanation: output example: 
<table>
  <tr>
    <th>USER_ID</th>
    <th>interaction_time</th> 
    <th>interaction_type</th>
  </tr>
  <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>impression</td>
  </tr>
  <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>impression</td>
  </tr>
    <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>click</td>
  </tr>
    <tr>
    <td>002</td>
    <td>timestamp</td> 
    <td>impression</td>
  </tr>
    </tr>
    <tr>
    <td>002</td>
    <td>timestamp</td> 
    <td>click</td>
  </tr>
    </tr>
    <tr>
    <td>003</td>
    <td>timestamp</td> 
    <td>impression</td>
  </tr>
    <tr>
    <td>001</td>
    <td>timestamp</td> 
    <td>impression</td>
  </tr>
</table>
Part 3 - Calculate impressions and clicks per user: 
For each user, calculate the number of impressions and clicks using the table created in Part 1 and 2
End of explanation
q4 = 
    SELECT
    impressions AS frequency,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    COUNT(*) AS reach,
    SUM(cost) AS cost
    FROM
    user_level_data
    GROUP BY
    1
    ORDER BY
    frequency ASC
Explanation: output example: 
<table>
  <tr>
    <th>USER_ID</th>
    <th>impressions</th> 
    <th>clicks</th>
  </tr>
  <tr>
    <td>001</td>
    <td>3</td> 
    <td>1</td>
  </tr>
    <tr>
    <td>002</td>
    <td>1</td> 
    <td>1</td>
  </tr>
      <tr>
    <td>003</td>
    <td>1</td> 
    <td>0</td>
  </tr>
</table>
Part 4 - Calculate metrics per frequency: 
Use the table created in Part 3 with metrics at user level to calculate metrics per each frequency
Frequency: The number of impressions served to each user
Clicks: The sum of clicks that occured at each frequency
Impressions: The sum of all impressions that occured at each frequency 
Reach: The total number of unique users (the count of all user ids) 
Group by Frequency
End of explanation
query_text = (q1 + q2 + q3 + q4).format(**dc)
print(query_text)  
Explanation: output example: 
<table>
  <tr>
    <th>frequency</th>
    <th>clicks</th> 
    <th>impression</th>
    <th>reach</th>
  </tr>
  <tr>
    <td>1</td>
    <td>1</td> 
    <td>2</td>
     <td>2</td>
  </tr>
 <tr>
    <td>2</td>
    <td>0</td> 
    <td>0</td>
     <td>0</td>
  </tr>
   <tr>
    <td>3</td>
    <td>1</td> 
    <td>3</td>
     <td>1</td>
  </tr>
</table>
Join the query and use pythons format method to pass in your parameters set in step 1
End of explanation
import datetime
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)
d = datetime.datetime.today()
query_create_body = {
        'name': query_name + '_' + d.strftime('%d-%m-%Y') + '_freq',
        'title': query_name + '_' + d.strftime('%d-%m-%Y') + '_freq',
        'queryText': query_text
}
try:
  # Execute the request.
  new_query = full_circle_query.customers().analysisQueries().create(body=query_create_body, parent='customers/' + str(customer_id)).execute()
  global_query_name.append(new_query["name"])
except HttpError as e:
  print e
  sys.exit(1)
print 'New query %s created for customer ID "%s":' % (query_name, customer_id)
print(json.dumps(new_query))
Explanation: Create the query required for ADH 
* When working with ADH the standard BigQuery query needs to be adapted to run in ADH
* This can be done bia the API
End of explanation
qb1 = 
  SELECT
    a.affinity_category,
    COUNT(imp.query_id.time_usec) AS impression,
    COUNT(clk.click_id.time_usec) AS clicks,
    COUNT(DISTINCT imp.user_id) AS reach,
    COUNT(imp.query_id.time_usec)/COUNT(DISTINCT imp.user_id) AS frequency,
    COUNT(clk.click_id.time_usec) /COUNT(imp.query_id.time_usec) AS ctr
  
  FROM
    adh.google_ads_impressions imp, UNNEST(affinity) aff
    LEFT JOIN adh.google_ads_clicks clk USING (query_id)
    JOIN adh.affinity a on aff = a.affinity_id
  WHERE a.affinity_category != '' {ID_filters}
  GROUP BY 1
  ORDER BY 3 DESC
Explanation: Step 3b - Query for demographics & interest
End of explanation
qb2 = 
  SELECT
    a.in_market_category,
    COUNT(imp.query_id.time_usec) AS impression,
    COUNT(clk.click_id.time_usec) AS clicks,
    COUNT(DISTINCT imp.user_id) AS reach,
    COUNT(imp.query_id.time_usec)/COUNT(DISTINCT imp.user_id) AS frequency,
    COUNT(clk.click_id.time_usec) /COUNT(imp.query_id.time_usec) AS ctr
  
  FROM
    adh.google_ads_impressions imp, UNNEST(in_market) aff
    LEFT JOIN adh.google_ads_clicks clk USING (query_id)
    JOIN adh.in_market a on aff = a.in_market_id
  WHERE a.in_market_category != '' {ID_filters}
  GROUP BY 1
  ORDER BY 3 DESC
Explanation: output example:
<table>
  <tr>
    <th>affinity</th>
    <th>impression</th> 
    <th>click</th>
    <th>reach</th>
    <th>frequency</th>
    <th>ctr</th>
  </tr>
  <tr>
    <td>Gamers</td>
    <td>10</td> 
    <td>2</td>
    <td>5</td>
    <td>2</td>
    <td>0.2</td>
  </tr>
 <tr>
    <td>Music Lovers</td>
    <td>20</td> 
    <td>10</td>
    <td>4</td>
    <td>5</td>
    <td>0.5</td>
  </tr>
   <tr>
    <td>Sports Fans</td>
    <td>40</td> 
    <td>10</td>
    <td>2</td>
    <td>20</td>
    <td>0.25</td>
  </tr>
</table>
End of explanation
qb3 = 
  SELECT
    gen.gender_name,
    age.age_group_name,
    COUNT(imp.query_id.time_usec) AS impression,
    COUNT(clk.click_id.time_usec) AS clicks,
    COUNT(DISTINCT imp.user_id) AS reach,
    COUNT(imp.query_id.time_usec)/COUNT(DISTINCT imp.user_id) AS frequency,
    COUNT(clk.click_id.time_usec) /COUNT(imp.query_id.time_usec) AS ctr
  
  FROM
    adh.google_ads_impressions imp
    LEFT JOIN adh.google_ads_clicks clk USING (query_id)
    LEFT JOIN adh.age_group age ON imp.demographics.age_group = age_group_id
    LEFT JOIN adh.gender gen ON imp.demographics.gender = gender_id
  WHERE {id_type} IN ({IDs})
  GROUP BY 1,2
  ORDER BY 1,2 DESC
import datetime
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)
# create request body method
d = datetime.datetime.today()
demoQuery = [qb1, qb2, qb3]
def createRequestBody(arg):
  data = {}
  data['name'] = query_name + '_' + d.strftime('%d-%m-%Y') + '_demo_' +str(arg)
  data['title'] = query_name + '_' + d.strftime('%d-%m-%Y') + '_demo_' + str(arg)
  data['queryText'] = demoQuery[arg].format(**dc)
  return data
#create multiple query
for i in range(len(demoQuery)):
  try:
    # Execute the request.
    queryBody = createRequestBody(i)
    new_query = full_circle_query.customers().analysisQueries().create(body=queryBody, parent='customers/' + str(customer_id)).execute()
    global_query_name.append(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)
Explanation: output example:
<table>
  <tr>
    <th>affinity</th>
    <th>impression</th> 
    <th>click</th>
    <th>reach</th>
    <th>frequency</th>
    <th>ctr</th>
  </tr>
  <tr>
    <td>SUVs</td>
    <td>10</td> 
    <td>2</td>
    <td>5</td>
    <td>2</td>
    <td>0.2</td>
  </tr>
 <tr>
    <td>Home & Garden</td>
    <td>20</td> 
    <td>10</td>
    <td>4</td>
    <td>5</td>
    <td>0.5</td>
  </tr>
   <tr>
    <td>Education</td>
    <td>40</td> 
    <td>10</td>
    <td>2</td>
    <td>20</td>
    <td>0.25</td>
  </tr>
</table>
End of explanation
destination_table_full_path = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table
destination_table_full_path_affinity = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table_affinity
destination_table_full_path_inmarket = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table_inmarket
destination_table_full_path_age_gender = big_query_project + '.' + big_query_dataset + '.' + big_query_destination_table_age_gender
CUSTOMER_ID = customer_id
QUERY_NAME = query_name
DEST_TABLES = [destination_table_full_path, destination_table_full_path_affinity, destination_table_full_path_inmarket, destination_table_full_path_age_gender]
#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': {
      'startDate': START_DATE,
      'endDate': END_DATE
        },
  'destTable': '',
  'customerId': CUSTOMER_ID
}
#run all queries
for i in range(len(global_query_name)):
  try:
    # Execute the request.
    query_start_body['destTable'] = DEST_TABLES[i]
    operation = full_circle_query.customers().analysisQueries().start(body=query_start_body, name=global_query_name[i].encode("ascii")).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: output example:
<table>
  <tr>
    <th>affinity</th>
    <th>impression</th> 
    <th>click</th>
    <th>reach</th>
    <th>frequency</th>
    <th>ctr</th>
  </tr>
  <tr>
    <td>SUVs</td>
    <td>10</td> 
    <td>2</td>
    <td>5</td>
    <td>2</td>
    <td>0.2</td>
  </tr>
 <tr>
    <td>Home & Garden</td>
    <td>20</td> 
    <td>10</td>
    <td>4</td>
    <td>5</td>
    <td>0.5</td>
  </tr>
   <tr>
    <td>Education</td>
    <td>40</td> 
    <td>10</td>
    <td>2</td>
    <td>20</td>
    <td>0.25</td>
  </tr>
</table>
Check your query exists
https://adsdatahub.google.com/#/queries
Find your query in the my queries tab
Check and ensure your query is valid (there will be a green tick in the top right corner)
If your query is not valid hover over the red exclamation mark to see issues that need to be resolved
Step 4 - Run the query
Start the query
Pass the query in to ADH using the full_circle_query method set at the start
Pass in the dates, the destination table name in BigQuery and the customer ID
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
  time.sleep(5)
print("Job completed... Getting results")
#run bigQuery query
dc = [big_query_dataset + '.' + big_query_destination_table,
      big_query_dataset + '.' + big_query_destination_table_affinity,
      big_query_dataset + '.' + big_query_destination_table_inmarket,
      big_query_dataset + '.' + big_query_destination_table_age_gender]
qs = ['select * from {}'.format(q) for q in dc]
Explanation: Step 5 - Retrieve the table from BigQuery
Retrieve the results from BigQuery
Check to make sure the query has finished running and is saved in the new BigQuery Table 
When it is done we cane retrieve it
End of explanation
# Run query as save as a table (also known as dataframe)
df = pd.io.gbq.read_gbq(q1, project_id=big_query_project, dialect='standard', reauth=True)
dfs = [pd.io.gbq.read_gbq(q, project_id=big_query_project, dialect='standard', reauth=True) for q in qs]
for i in range(4):
  print(dfs[i])
Explanation: We are using the pandas library to run the query. 
We pass in the query (q), the project id and set the SQL language to 'standard' (as opposed to legacy SQL)
End of explanation
# Save the original dataframe as a csv file in case you need to recover the original data
dfs[0].to_csv('data_reach_freq.csv', index=False)
dfs[1].to_csv('data_affinity.csv', index=False)
dfs[2].to_csv('data_inmarket.csv', index=False)
dfs[3].to_csv('data_age_gender.csv', index=False)
Explanation: Save the output as a CSV
End of explanation
#prepare reach & frequency data
df = pd.read_csv('data_reach_freq.csv')
print(df.head())
#prepare affinity data
df3 = pd.read_csv('data_affinity.csv')
print(df3.head())
#prepare in_market data
df4 = pd.read_csv('data_inmarket.csv')
print(df4.head())
#prepare age & gender data
df5 = pd.read_csv('data_age_gender.csv')
print(df5.head())
Explanation: Setup up the dataframe and preview to check the data.
End of explanation
df = df[:max_freq+1] # Reduces the dataframe to have the size you set as the maximum frequency (max_freq)
df = df_calc_fields(df)
df2 = df.copy() # Copy the dataframe you calculated the fields in case you need to recover it
graphs = [] # Variable to save all graphics
df.head()
Explanation: Step 6 - Set up the data and all the charts that will be plotted
6.1 Transform data
Use the calculation function created to calculate all the values based off your data
End of explanation
# Save all data into a list to plot the graphics
impressions = dict(type='bar', x=df.frequency, y=df.impressions,
                   name='impressions',
                   marker=dict(color='rgb(0, 29, 255)',
                   line=dict(width=1)))
ctr = dict(
    type='scatter',
    x=df.frequency,
    y=df.ctr,
    name='ctr',
    marker=dict(color='rgb(255, 148, 0)', line=dict(width=1)),
    xaxis='x1',
    yaxis='y2',
    )
layout = dict(
    title='Impressions and CTR Comparison on Each Frequency',
    autosize=True,
    legend=dict(x=1.15, y=1),
    hovermode='x',
    xaxis=dict(tickangle=-45, autorange=True, tickfont=dict(size=10),
               title='frequency', type='category'),
    yaxis=dict(showgrid=True, title='impressions'),
    yaxis2=dict(overlaying='y', anchor='x', side='right',
                showgrid=False, title='ctr'),
    )
fig = dict(data=[impressions, ctr], layout=layout)
graphs.append(fig)
			
clicks = dict(type='bar',
              x= df.frequency, 
              y= df.clicks, 
              name='Clicks',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
ctr = dict(type='scatter',
              x= df.frequency, 
              y= df.cpc, 
              name='cpc',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y2'
              
  )
layout = dict(autosize= True, 
              title='Clicks and CPC Comparison on Each Frequency',                   
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'clicks'
                        ), 
              yaxis2=dict( 
                          overlaying= 'y', 
                          anchor= 'x', 
                          side= 'right', 
                          showgrid= False, 
                          title= 'cpc'
                         )
             )
fig = dict(data=[clicks, ctr], layout=layout)
graphs.append(fig)
ctr = dict(type='scatter',
              x= df.frequency, 
              y= df.ctr, 
              name='ctr',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
cpc = dict(type='scatter',
              x= df.frequency, 
              y= df.cpc, 
              name='cpc',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y2'
              
  )
layout = dict(autosize= True, 
              title='CTR and CPC Comparison on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category',
                         showgrid =False
                        ), 
              yaxis=dict(
                         showgrid=False, 
                         title= 'ctr'
                        ), 
              yaxis2=dict( 
                          overlaying= 'y', 
                          anchor= 'x', 
                          side= 'right', 
                          showgrid= False, 
                          title= 'cpc'
                         )
             )
fig = dict(data=[ctr, cpc], layout=layout)
graphs.append(fig)
pareto = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_clicks, 
              name='Cumulative % Clicks',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
cpc = dict(type='scatter',
              x= df.frequency, 
              y= df.cpc, 
              name='cpc',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y2'
              
  )
layout = dict(autosize= True, 
              title='Cumulative Clicks and CPC Comparison on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'cum clicks'
                        ), 
              yaxis2=dict( 
                          overlaying= 'y', 
                          anchor= 'x', 
                          side= 'right', 
                          showgrid= False, 
                          title= 'cpc'
                         )
             )
fig = dict(data=[pareto, cpc], layout=layout)
graphs.append(fig)
pareto = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_clicks, 
              name='Cumulative % Clicks',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
cpc = dict(type='scatter',
              x= df.frequency, 
              y= df.ctr, 
              name='ctr',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y2'
              
  )
layout = dict(autosize= True, 
              title='Cumulative Clicks and CTR Comparison on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'cum clicks'
                        ), 
              yaxis2=dict( 
                          overlaying= 'y', 
                          anchor= 'x', 
                          side= 'right', 
                          showgrid= False, 
                          title= 'ctr'
                         )
             )
fig = dict(data=[pareto, cpc], layout=layout)
graphs.append(fig)
pareto = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_reach, 
              name='Cumulative % Reach',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
cpc = dict(type='scatter',
              x= df.frequency, 
              y= df.cost, 
              name='cost',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y2'
              
  )
layout = dict(autosize= True, 
              title='Cumulative Reach and Cost Comparison on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'cummulative reach'
                        ), 
              yaxis2=dict( 
                          overlaying= 'y', 
                          anchor= 'x', 
                          side= 'right', 
                          showgrid= False, 
                          title= 'cost'
                         )
             )
Explanation: Analysis 1: Frequency Analysis by user
Step 1: Set up graphs
End of explanation
# Show the first 5 rows of the dataframe (data matrix) with the final data
df.head()
# Export the whole dataframe to a csv file that can be used in an external environment
df.to_csv('freq_analysis.csv', index=False)
Explanation: Step 2: Export all the data (optional)
End of explanation
enable_plotly_in_cell()
iplot(graphs[0])
Explanation: Output: Visualise the data
Impression and CTR on each frequency
Clicks and CPC Comparison on Each Frequency
CTR and CPC Comparison on Each Frequency
Cumulative Clicks and CPC Comparison on Each Frequency
Cumulative Clicks and CTR Comparison on Each Frequency
Impression and CTR on each frequency
Consider your frequency range, ensure frequency management is in place. 
Where is your CTR floor? At what point does your CTR drop below a level that you care about. 
Determine what the wasted impressions is if you don't change your frequency.
End of explanation
enable_plotly_in_cell()
iplot(graphs[1])
Explanation: Clicks and CPC Comparison on Each Frequency
What is your CPC ceiling
Understand what the frequency is at that level
Determine what impact changing your frequency will have on clicks
End of explanation
enable_plotly_in_cell()
iplot(graphs[2])
Explanation: CTR and CPC Comparison on Each Frequency
How does your CTR and CPC impact each other 
Make an informed decision regarding suitable goals
End of explanation
enable_plotly_in_cell()
iplot(graphs[3])
Explanation: Cumulative Clicks and CPC Comparison on Each Frequency
Understand what a suitable CPC goal might be
1. What is the change in cost for increased clicks
2. What is the incremental gains for an increased cost
End of explanation
enable_plotly_in_cell()
iplot(graphs[4])
Explanation: Cumulative Clicks and CTR Comparison on Each Frequency
At what frequency does your CTR drop below an acceptable value
End of explanation
#Understand the logic behind calculation
graphs2 = []
pareto = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_reach, 
              name='Cummulative % Reach',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
ccm_imp = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_impressions, 
              name='Cummulative % Impressions',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y'
              
  )
layout = dict(autosize= True, 
              title='Cummulative Impressions and Cummulative Reach on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'cummulative %'
                        )
             )
fig = dict(data=[pareto, ccm_imp], layout=layout)
graphs2.append(fig)
pareto = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_clicks, 
              name='Cummulative % Clicks',            
              marker=dict(color= 'rgb(0, 29, 255)', line= dict(width= 1))
  )
 
ccm_imp = dict(type='scatter',
              x= df.frequency, 
              y= df.coverage_impressions, 
              name='Cummulative % Impressions',            
              marker=dict(color= 'rgb(255, 148, 0)', line= dict(width= 1)),
              xaxis='x1',
              yaxis='y'
              
  )
layout = dict(autosize= True, 
              title='Cumulative Impressions and Cummulative Clicks on Each Frequency',
              legend= dict(x= 1.15, 
                           y= 1
                          ),
              hovermode='x',
              xaxis=dict(tickangle= -45, 
                         autorange=True,
                         tickfont=dict(size= 10), 
                         title= 'frequency', 
                         type= 'category'
                        ), 
              yaxis=dict(
                         showgrid=True, 
                         title= 'cummulative %'
                        )
             )
fig = dict(data=[pareto, ccm_imp], layout=layout)
graphs2.append(fig)
Explanation: Analysis 2: Understanding optimal frequency
Step 1: Set up charts
End of explanation
enable_plotly_in_cell()
iplot(graphs2[0])
Explanation: Output: Visualise the results
Cummulative Impressions and Cummulative Reach on Each Frequency
How do you maximise your reach without drastically increasing your impressions?
To obtain my reach goals, what frequency do I need at what impression cost? 
With higher frequency caps you will need more impressions to maximise your reach
End of explanation
enable_plotly_in_cell()
iplot(graphs2[1])
Explanation: Cummulative Impressions and Cummulative Clicks on Each Frequency
To obtain my goals in terms of clicks, what frequency do I need, at what impression cost?
End of explanation
#@title 1.1 - Optimal Frequency
optimal_freq =  3#@param {type:"integer", allow-input: true}
slider_value = 1  #@param test
if optimal_freq > len(df2):
  raise Exception('Your optimal frequency is higher than the maxmium frequency in your campaign please make sure it is lower than {}'.format(len(df2)))
Explanation: Analysis 3: Determine impressions outside optimal frequency
Step 1: Define parameter to be the Optimal Frequency
This parameter below will guide the analysis of media loss talking about impressions. We will calculate the percentage of impressions that are out of the number you set as the optimal frequency.
End of explanation
from __future__ import division
df2 = df_calc_fields(df2)
df_opt, df_not_opt = df[:optimal_freq], df[optimal_freq:]
total_impressions = list(df2.cumulative_impressions)[-1]
total_imp_not_opt = list(df_not_opt.cumulative_impressions)[-1] - list(df_opt.cumulative_impressions)[-1]
imp_not_opt_ratio = total_imp_not_opt / total_impressions
total_clicks = list(df2.cumulative_clicks)[-1]
total_clicks_not_opt = list(df_not_opt.cumulative_clicks)[-1] - list(df_opt.cumulative_clicks)[-1]
clicks_within_opt_ratio = 1-(total_clicks_not_opt / total_clicks)
print("{:.1f}% of your total impressions are out of the optimal frequency.".format(imp_not_opt_ratio*100))
print("{:,} of your impressions are out of the optimal frequency".format(total_imp_not_opt))
print("At a CPM of {} - preventing these would result in a cost saving of {:,}".format(cpm, cpm*total_imp_not_opt))
print("")
print("If you limited frequency to {}, you would still achieve {:.1f}% of your clicks").format(optimal_freq, clicks_within_opt_ratio*100)
Explanation: Output: Calculate impression loss
End of explanation
# calculate the color scale
diff_affinity = df3.ctr.max(0) - df3.ctr.min(0) 
df3['colorscale'] = (df3.ctr - df3.ctr.min(0))/ diff_affinity *40 + 130
diff_in_market = df4.ctr.max(0) - df4.ctr.min(0) 
df4['colorscale'] = (df4.ctr - df4.ctr.min(0))/ diff_in_market *40 + 130
diff_in_market = df5.ctr.max(0) - df5.ctr.min(0) 
df5['colorscale'] = (df5.ctr - df5.ctr.min(0))/ diff_in_market *40 + 130
#prepare the graph, rerun if no graph shown
#affinity
graphs3 = []
size1=df3.reach
affinity = dict(type='scatter',
              x= df3.frequency, 
              y= df3.ctr, 
              text= df3.affinity_category,
              name='Cummulative % Reach',            
              marker=dict(color= df3.colorscale,
                          size=size1,
                          sizemode='area',
                          sizeref=2.*max(size1)/(40.**2),
                          sizemin=4,
                          showscale=True),
              mode='markers'
  )
layout = dict(autosize= True, 
              title='affinity bubble chart',
              xaxis=dict(autorange=True,
                         title= 'frequency'
                        ),
              yaxis=dict(autorange=True,
                         title= 'CTR'
                        )
             )
fig = dict(data=[affinity], layout=layout)
graphs3.append(fig)
#in market
size2=df5.reach
in_market = dict(type='scatter',
              x= df4.frequency, 
              y= df4.ctr, 
              text= df4.in_market_category,
              name='Cummulative % Reach',            
              marker=dict(color= df4.colorscale,
                          size=size2,
                          sizemode='area',
                          sizeref=2.*max(size2)/(40.**2),
                          sizemin=4,
                          showscale=True),
              mode='markers'
  )
layout = dict(autosize= True, 
              title='in_market bubble chart',
              xaxis=dict(autorange=True,
                         title= 'frequency'
                        ),
              yaxis=dict(autorange=True,
                         title= 'CTR'
                        )
             )
fig = dict(data=[in_market], layout=layout)
graphs3.append(fig)
#age & demo
male = dict(type='scatter',
              x= df5.frequency[df5.gender_name == 'male'], 
              y= df5.ctr[df5.gender_name == 'male'], 
              text= df5.age_group_name[df5.gender_name == 'male'],
              name='male',            
              marker=dict(size=df5.reach[df5.gender_name == 'male'],
                          sizemode='area',
                          sizeref=2.*max(df5.reach[df5.gender_name == 'male'])/(40.**2),
                          sizemin=4),
              mode='markers'
  )
female = dict(type='scatter',
              x= df5.frequency[df5.gender_name == 'female'], 
              y= df5.ctr[df5.gender_name == 'female'], 
              text= df5.age_group_name[df5.gender_name == 'female'],
              name='female',            
              marker=dict(size=df5.reach[df5.gender_name == 'female'],
                          sizemode='area',
                          sizeref=2.*max(df5.reach[df5.gender_name == 'female'])/(40.**2),
                          sizemin=4
                          ),
              mode='markers'
  )
unknown = dict(type='scatter',
              x= df5.frequency[df5.gender_name == 'unknown'], 
              y= df5.ctr[df5.gender_name == 'unknown'], 
              text= df5.age_group_name[df5.gender_name == 'unknown'],
              name='unknown',            
              marker=dict(size=df5.reach[df5.gender_name == 'unknown'],
                          sizemode='area',
                          sizeref=2.*max(df5.reach[df5.gender_name == 'unknown'])/(40.**2),
                          sizemin=4),
              mode='markers'
  )
layout = dict(autosize= True, 
              title='age & gender bubble chart',
              xaxis=dict(autorange=True,
                         title= 'frequency'
                        ),
              yaxis=dict(autorange=True,
                         title= 'CTR'
                        )
             )
fig = dict(data=[male,female,unknown], layout=layout)
graphs3.append(fig)
Explanation: Analysis 4: Determine which affinity work best
Step 1: Set up charts
End of explanation
enable_plotly_in_cell()
iplot(graphs3[0])
Explanation: Ouput: Visualise the data
Affinity bubble chart
What affinity is underreaching?
What affinity should I switch the cost to?
End of explanation
enable_plotly_in_cell()
iplot(graphs3[1])
Explanation: check whether big bubble is in the bottom left corner, those are the affinities with lowest efficency
bubble in the upper left corner are the affinities with high potentials
you should keep your investment in the upper right hand corner bubble
shift budget from bottom right hand corner to other area
In market bubble chart
What in market segment is underreaching?
What in market segment should I switch the cost to?
End of explanation
enable_plotly_in_cell()
iplot(graphs3[2])
Explanation: check whether big bubble is in the bottom left corner, those are the in market segment with lowest efficency
bubble in the upper left corner are the in market segment with high potentials
you should keep your investment in the upper right hand corner bubble
shift budget from bottom right hand corner to other area
age & gender bubble chart
which gender is underreaching?
Which age group should I switch the cost to?
End of explanation
<END_TASK>