<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <table align="left">
  <td>
    <a href="https
Step1:  Import packages
Step2:  Set up your GCP project
The following steps are required, regardless of your notebook environment.
Select or create a GCP project.. When you first create an account, you get a $300 free credit towards your compute/storage costs.
Make sure that billing is enabled for your project.
Enable the AI Platform APIs and Compute Engine APIs.
If you are running this notebook locally, you will need to install Google Cloud SDK.
Enter your project ID in the cell below. Then run the  cell to make sure the
Cloud SDK uses the right project for all the commands in this notebook.
Step3:  Authenticate your GCP account
If you are using AI Platform Notebooks, you are already authenticated so there is no need to run this step.
Step4:  Create a working dataset
This tutorial mostly uses BigQuery magic cells where the --params field does not support variables for datasets, tables and column names. 
This steps hardcode the dataset where all the steps of this tutorial happens.
Step5:  Load example tables
Step6:  Create clients
Step7:  [Optional] Match your dataset to template
If you use the example data, you can skip this step.
This tutorial assumes that you have a dump of your sales data already available in BigQuery located at [YOUR_PROJECT].[YOUR_DATASET].[YOUR_SOURCE_TABLE]
You are free to adapt the SQL query in the next cell to a SQL statement that transforms your data according to the template.
Step8:  Analyze dataset
Some charts might use a log scale.
Quantity
This sections shows how to use the BigQuery ML BUCKETIZE preprocessing function to create buckets of data for quantity and display a log scaled distribution of the qty field.
Step9:  Unit price
Step10:  Set parameters for LTV
Some parameters useful to run some of the queries in this tutorial
Step11:  Aggregate per day per customer
This query aggregates all orders per day per customer. This is useful if
Step12:  Check distributions
This tutorial does minimum data cleansing and focuses mostly on transforming a list of transactions into workable inputs for the model.
This section checks that data is generally usable.
Per date
Step13:  Orders are quite well distributed across the year despite a lower number in the early days of the dataset. You can keep this in mind when choosing a value for WINDOW_STEP_INITIAL.
Per customer
Step14:  The number of transactions per customer is distributed across a few discrete values with no clear outliers.
Per quantity
This section looks at the general distribution of the number of articles per orders and check if there are some outliers.
Step15:  A few customers seems to have quite large quantities in their orders but the distribution is generally healthy. 
They could be weird behavior for the same customers but the Standard Deviation filters should minimize the risk. 
Analyzing those few outliers can be part of additional data preparation work that can help improve your model.
Per value
This section shows that there are quite a few outliers 'outliers' that spend way more than others. We want to ignore them when creating the RFM values.
Step16:  The distribution shows a few outliers that you could investigate to improve the base model that you create in this tutorial.
Create inputs and targets for the ML model
The goal of this tutorial is to predict How much each customer is going to spend in the next N days knowing their transactions history.
Because all customers have different buying behavior over a specific period of time, this tutorial create multiple records per customer by
Step17:  Dataset
Step18:  Seems like for most values, there is a long tail of records. This is something that might required additional feature preparation even if AutoML already provides some automatic engineering. You can investigate this if you want to improve the base model.
Train the model
This tutorial uses an AutoML regressor to predict the continuous value of target_monetary.
With a non-AutoML model, you would generally need to
Step19:  This is an example of a model evaluation
Predict LTV
Predicts LTV for all customers. It uses the overall monetary value for each customer to predict a future one.
Step20:  The monetary distribution analysis shows small monetary amounts for the next month compare to the overall historical value. The difference is about 3 to 4 orders of magnitude.
One reason is that the model is trained to predict the value for the next month (LENGTH_FUTURE = 30). 
You can play around with that value to train and predict for the next quarter for example (LENGTH_FUTURE = 90)
Activation
This part shows how to activate on Google Ads using similar audience.
You can follow a similar process for Facebook for example
Extract top customers
This step extracts the top 20% customers with the highest future monetary value and join with a CRM table to get their email. 
The prediction used the overall monetary value but in this use case, we are looking at the most valuable in the future. You can modify the PERCENT_RANK to use another KPI.
Step22:  Setup Adwords client
Creates the configuration YAML file for the Google Ads client. You need to
Step25:  Create an AdWords user list
Using emails of the top LTV customers, you create an AdWords list. If more than 5000 of the users are matched with AdWords email, a similar audience list will be created.
Note that this guide uses fake emails so running these steps is not going to work but you can leverage this code with emails coming from your CRM.
<ASSISTANT_TASK:>
Python Code:
# Install libraries. 
# The magic cells insures that those libraries can be part of a custom container
# if moving the code somewhere else.
%pip install -q googleads
%pip install -q -U kfp matplotlib Faker --user
# Automatically restart kernel after installs
# import IPython
# app = IPython.Application.instance()
# app.kernel.do_shutdown(True)  
Explanation: <table align="left">
  <td>
    <a href="https://colab.research.google.com/github/GoogleCloudPlatform/ai-platform-samples/blob/master/notebooks/templates/ai_platform_notebooks_template_hybrid.ipynb"">
      <img src="https://cloud.google.com/ml-engine/images/colab-logo-32px.png" alt="Colab logo"> Run in Colab
    </a>
  </td>
  <td>
    <a href="https://github.com/GoogleCloudPlatform/ai-platform-samples/blob/master/notebooks/templates/ai_platform_notebooks_template_hybrid.ipynb">
      <img src="https://cloud.google.com/ml-engine/images/github-logo-32px.png" alt="GitHub logo">
      View on GitHub
    </a>
  </td>
</table>
Overview
Objective
Estimate how much an existing customer will spend in the future based on their historical orders to find similar new customers using lookalike features of advertising tools.
In this tutorial, you will:
- Define how far in the future you want to predict the monetary value of your customers (ex: 3 months)
- Use a moving session concept to aggregate multiple inputs and targets per customer (For more details, see the Create inputs and targets section of this tutorial).
- Use primarly inputs such as Recency, Frequency and Monetary which are common values to use in an LTV context, especially in statistical model due to their distribution patterns.
- Accelerate model development by using AutoML from within BigQuery ML.
- Predict the monetary value of all existing customers for a predefined period of time in the future.
- Use first-party data to extract the most valuable customers email in order to run lookalike campaigns using the Google Ads API. You can extend the concept to do the same using Facebook API.
Sales dataset
Sales data can be of different forms but generally look like a list of transactions where each record contains at a minimum the following:
- a customer reference
- a transaction date
- a transaction reference
- a monetary value
Each record usually represents one of the following:
- An entire order which contains aggregated values across products for that order. You can find the total order value in the record.
- A part of a transaction which contains a unique product, some of its characteristics including SKU and unit price and the quantity ordered. 
This tutorial uses the latter.
You can run this tutorial with your own dataset. The dataset that you provide must meet the following requirements:
1. Each row represents a transaction related to a product item and linked to a transaction, a date and a customer. A row can be either a transaction (quantity is > 0) or a return (quantity is < 0)
1. Columns must include the following:
| Field name | Type | Description |
| :-|:-|:-|
| customer_id | STRING | First party identifier of the customer.     |
| order_id | STRING | First party identitfier of the order. |
| order_date | DATE | Date of the order.  |
| product_sku | STRING | First party identitifer of the product. |
| qty | INTEGER | Quantity of the product either ordered or returned. |
| unit_price | FLOAT | Unit price of the product. |
Customer dataset
Customer data often resides in a Customer Relationship Management (CRM). 
This first party data is key for companies that want to provide a certain level of customer service.
This tutorial only uses two columns of the customer dataset:
- customer_id to join with the sales data
- email to create a marketing list.
The public dataset contains other fields that are not relevant for this tutorial and your data might have other fields. This tutorial focuses on an activation based on email addresses.
Costs
This tutorial uses billable components of Google Cloud Platform (GCP):
BigQuery
BigQuery ML
Cloud Storage
To learn more about pricing:
- Read BigQuery pricing
- Read BigQuery ML pricing
- Read Cloud Storage pricing
- Use the Pricing Calculator
to generate a cost estimate based on your projected usage.
Terminology
'Input' transactions: The set of transactions that the training task uses to create inputs values for the model.
'Target' transactions: The set of transactions that the training task uses to create the target value to predict. The target value is an aggregated monetary value per customer for a defined timeline.
Threshold date: Date that separates 'Input' transactions from 'Target' transactions per customer.
Setup
This step sets up packages, variables, authentication, APIs clients and resources for Google Cloud and Adwords.
Install packages and dependencies
Installs libraries, packages and dependencies to run this tutorial
End of explanation
# Import
from __future__ import absolute_import
from __future__ import division
from __future__ import print_function
import os, json, random
import hashlib, uuid
import time, calendar, math
import pandas as pd, numpy as np
import matplotlib.pyplot as plt, seaborn as sns
from datetime import datetime
from google.cloud import bigquery
from googleads import adwords
Explanation: Import packages
End of explanation
PROJECT_ID = "[YOUR-PROJECT]" #@param {type:"string"}
REGION = "US"
! gcloud config set project $PROJECT_ID
Explanation: Set up your GCP project
The following steps are required, regardless of your notebook environment.
Select or create a GCP project.. When you first create an account, you get a $300 free credit towards your compute/storage costs.
Make sure that billing is enabled for your project.
Enable the AI Platform APIs and Compute Engine APIs.
If you are running this notebook locally, you will need to install Google Cloud SDK.
Enter your project ID in the cell below. Then run the  cell to make sure the
Cloud SDK uses the right project for all the commands in this notebook.
End of explanation
import sys
if 'google.colab' in sys.modules:
  from google.colab import auth as google_auth
  google_auth.authenticate_user()
Explanation: Authenticate your GCP account
If you are using AI Platform Notebooks, you are already authenticated so there is no need to run this step.
End of explanation
! bq show $PROJECT_ID:ltv_ecommerce || bq mk $PROJECT_ID:ltv_ecommerce
Explanation: Create a working dataset
This tutorial mostly uses BigQuery magic cells where the --params field does not support variables for datasets, tables and column names. 
This steps hardcode the dataset where all the steps of this tutorial happens.
End of explanation
# Loads CRM data
!bq load \
  --project_id $PROJECT_ID \
  --skip_leading_rows 1 \
  --max_bad_records 100000 \
  --replace \
  --field_delimiter "," \
  --autodetect \
  ltv_ecommerce.00_crm \
  gs://solutions-public-assets/analytics-componentized-patterns/ltv/crm.csv
# Loads Sales data
!bq load \
  --project_id $PROJECT_ID \
  --skip_leading_rows 1 \
  --max_bad_records 100000 \
  --replace \
  --field_delimiter "," \
  --autodetect \
  ltv_ecommerce.10_orders \
  gs://solutions-public-assets/analytics-componentized-patterns/ltv/sales_*
Explanation: Load example tables
End of explanation
# BigQuery client
bq_client = bigquery.Client(project=PROJECT_ID)
Explanation: Create clients
End of explanation
%%bigquery --params $MATCH_FIELDS --project $PROJECT_ID
CREATE OR REPLACE TABLE `ltv_ecommerce.10_orders` AS (
SELECT
  CAST(customer_id AS STRING)  AS customer_id,
  order_id  AS order_id,
  transaction_date AS transaction_date,
  product_sku AS product_sku,
  qty AS qty,
  unit_price AS unit_price
FROM
  `[YOUR_PROJECT].[YOUR_DATASET].[YOUR_SOURCE_TABLE]`
);
Explanation: [Optional] Match your dataset to template
If you use the example data, you can skip this step.
This tutorial assumes that you have a dump of your sales data already available in BigQuery located at [YOUR_PROJECT].[YOUR_DATASET].[YOUR_SOURCE_TABLE]
You are free to adapt the SQL query in the next cell to a SQL statement that transforms your data according to the template.
End of explanation
%%bigquery df_histo_qty --project $PROJECT_ID
WITH
  min_max AS (
  SELECT
    MIN(qty) min_qty,
    MAX(qty) max_qty,
    CEIL((MAX(qty) - MIN(qty)) / 100) step
  FROM
    `ltv_ecommerce.10_orders` 
)
SELECT
  COUNT(1) c,
  bucket_same_size AS bucket
FROM (
  SELECT
    -- Creates (1000-100)/100 + 1 buckets of data.
    ML.BUCKETIZE(qty, GENERATE_ARRAY(min_qty, max_qty, step)) AS bucket_same_size,
    -- Creates custom ranges.
    ML.BUCKETIZE(qty, [-1, -1, -2, -3, -4, -5, 0, 1, 2, 3, 4, 5]) AS bucket_specific,
  FROM
    `ltv_ecommerce.10_orders`, min_max )
  # WHERE bucket != "bin_1" and bucket != "bin_2"
GROUP BY
  bucket
  -- Ohterwise, orders bin_10 before bin_2
ORDER BY CAST(SPLIT(bucket, "_")[OFFSET(1)] AS INT64)
# Uses a log scale for bucket_same_size.
# Can remove the log scale when using bucket_specific.
plt.figure(figsize=(12,5))
plt.title('Log scaled distribution for qty')
hqty = sns.barplot( x='bucket', y='c', data=df_histo_qty)
hqty.set_yscale("log")
Explanation: Analyze dataset
Some charts might use a log scale.
Quantity
This sections shows how to use the BigQuery ML BUCKETIZE preprocessing function to create buckets of data for quantity and display a log scaled distribution of the qty field.
End of explanation
%%bigquery df_histo_unit_price --project $PROJECT_ID
WITH
  min_max AS (
  SELECT
    MIN(unit_price) min_unit_price,
    MAX(unit_price) max_unit_price,
    CEIL((MAX(unit_price) - MIN(unit_price)) / 10) step
  FROM
    `ltv_ecommerce.10_orders` 
)
SELECT
  COUNT(1) c,
  bucket_same_size AS bucket
FROM (
  SELECT
    -- Creates (1000-100)/100 + 1 buckets of data.
    ML.BUCKETIZE(unit_price, GENERATE_ARRAY(min_unit_price, max_unit_price, step)) AS bucket_same_size,
    -- Creates custom ranges.
    ML.BUCKETIZE(unit_price, [10, 20, 30, 40, 50, 100, 200, 300, 400, 500, 1000]) AS bucket_specific,
  FROM
    `ltv_ecommerce.10_orders`, min_max )
  # WHERE bucket != "bin_1" and bucket != "bin_2"
GROUP BY
  bucket
  -- Ohterwise, orders bin_10 before bin_2
ORDER BY CAST(SPLIT(bucket, "_")[OFFSET(1)] AS INT64)
# Uses a log scale for bucket_same_size.
# Can remove the log scale when using bucket_specific.
plt.figure(figsize=(12,5))
q = sns.barplot( x='bucket', y='c', data=df_histo_unit_price)
q.set_yscale("log")
plt.title('Log scaled distribution for unit_price')
Explanation: Unit price
End of explanation
LTV_PARAMS = {
    'WINDOW_LENGTH': 0,
    'WINDOW_STEP': 30,
    'WINDOW_STEP_INITIAL': 90,
    'LENGTH_FUTURE': 30,
    'MAX_STDV_MONETARY': 500,
    'MAX_STDV_QTY': 100, 
    'TOP_LTV_RATIO': 0.2
}
LTV_PARAMS
Explanation: Set parameters for LTV
Some parameters useful to run some of the queries in this tutorial:
WINDOW_STEP: How many days between threshold dates.
WINDOW_STEP_INITIAL: How many days between the first order and the first threshold date. A threshold date is when BigQuery computes inputs and targets.
WINDOW_LENGTH: How many days back to use for input transactions. The default value is 0 which means that this tutorial takes all transactions before the threshold date.
LENGTH_FUTURE: How far in the future to predict the monetary value. At every threshold date, BigQuery calculate the target value for all orders that happen LENGTH_FUTURE after the threshold date.
MAX_STDV_MONETARY: Standard deviation of the monetary value per customer. Removes orders per customer that have order values with a greater standard deviation.
MAX_STDV_QTY: Standard deviation of the quantity of products per customer. Removes orders per customer that have product quantity with a greater standard deviation.
TOP_LTV_RATIO: Percentage of top customers that you want to keep for your lookalike activation.
You can change those parameters to see how they impact the model especially the parameters related to the window. There is no obvious rule to set values as they depend on how data looks.
For example:
- If your customers buy multiple times a week, you could try to predict monetary value on a weekly basis.
- If you have a lot of data, you can create more windows by decreasing their sizes and possibly the number of days between threshold dates.
After multiple trials, this tutorial chose values that provides a decent result for the example dataset.
End of explanation
%%bigquery --params $LTV_PARAMS --project $PROJECT_ID
DECLARE MAX_STDV_MONETARY INT64 DEFAULT @MAX_STDV_MONETARY;
DECLARE MAX_STDV_QTY INT64 DEFAULT @MAX_STDV_QTY;
CREATE OR REPLACE TABLE `ltv_ecommerce.20_aggred` AS
SELECT
  customer_id,
  order_day,
  ROUND(day_value_after_returns, 2) AS value,
  day_qty_after_returns as qty_articles,
  day_num_returns AS num_returns,
  CEIL(avg_time_to_return) AS time_to_return
FROM (
  SELECT
    customer_id,
    order_day,
    SUM(order_value_after_returns) AS day_value_after_returns,
    STDDEV(SUM(order_value_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_value_after_returns)) AS stdv_value,
    SUM(order_qty_after_returns) AS day_qty_after_returns,
    STDDEV(SUM(order_qty_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_qty_after_returns)) AS stdv_qty,
    CASE
      WHEN MIN(order_min_qty) < 0 THEN count(1)
      ELSE 0
    END AS day_num_returns,
    CASE
      WHEN MIN(order_min_qty) < 0 THEN AVG(time_to_return)
      ELSE NULL
    END AS avg_time_to_return
  FROM (
    SELECT 
      customer_id,
      order_id,
      -- Gives the order date vs return(s) dates.
      MIN(transaction_date) AS order_day,
      MAX(transaction_date) AS return_final_day,
      DATE_DIFF(MAX(transaction_date), MIN(transaction_date), DAY) AS time_to_return,
      -- Aggregates all products in the order 
      -- and all products returned later.
      SUM(qty * unit_price) AS order_value_after_returns,
      SUM(qty) AS order_qty_after_returns,
      -- If negative, order has qty return(s).
      MIN(qty) order_min_qty
    FROM 
      `ltv_ecommerce.10_orders`
    GROUP BY
      customer_id,
      order_id)
  GROUP BY
    customer_id,
    order_day)
WHERE
  -- [Optional] Remove dates with outliers per a customer.
  (stdv_value < MAX_STDV_MONETARY
    OR stdv_value IS NULL) AND
  (stdv_qty < MAX_STDV_QTY
    OR stdv_qty IS NULL);
SELECT * FROM `ltv_ecommerce.20_aggred` LIMIT 5;
Explanation: Aggregate per day per customer
This query aggregates all orders per day per customer. This is useful if:
- Your database has multiple records for one order, for example if each record represent a product in a transaction, which is the case in this tutorial.
- Customers bought multiple times during one day. This guide is based on LTV per day so no need to keep hourly records (unless you decided to use that data as a feature).
This query also
1. Creates inputs related to returns.
1. Removes orders with outliers per customer. This means that high spending customers remain in the dataset but orders that seems unusual for a unique customers are filtered out.
End of explanation
%%bigquery df_dist_dates --project $PROJECT_ID
SELECT count(1) c, SUBSTR(CAST(order_day AS STRING), 0, 7) as yyyy_mm
FROM `ltv_ecommerce.20_aggred`
WHERE qty_articles > 0
GROUP BY yyyy_mm 
ORDER BY yyyy_mm
plt.figure(figsize=(12,5))
sns.barplot( x='yyyy_mm', y='c', data=df_dist_dates)
Explanation: Check distributions
This tutorial does minimum data cleansing and focuses mostly on transforming a list of transactions into workable inputs for the model.
This section checks that data is generally usable.
Per date
End of explanation
%%bigquery df_dist_customers --params $LTV_PARAMS --project $PROJECT_ID
SELECT customer_id, count(1) c
FROM `ltv_ecommerce.20_aggred`
GROUP BY customer_id
plt.figure(figsize=(12,4))
sns.distplot(df_dist_customers['c'], hist_kws=dict(ec="k"), kde=False)
Explanation: Orders are quite well distributed across the year despite a lower number in the early days of the dataset. You can keep this in mind when choosing a value for WINDOW_STEP_INITIAL.
Per customer
End of explanation
%%bigquery df_dist_qty --params $LTV_PARAMS --project $PROJECT_ID
SELECT qty_articles, count(1) c
FROM `ltv_ecommerce.20_aggred`
GROUP BY qty_articles
plt.figure(figsize=(12,4))
sns.distplot(df_dist_qty['qty_articles'], hist_kws=dict(ec="k"), kde=False)
Explanation: The number of transactions per customer is distributed across a few discrete values with no clear outliers.
Per quantity
This section looks at the general distribution of the number of articles per orders and check if there are some outliers.
End of explanation
%%bigquery df_dist_values --params $LTV_PARAMS --project $PROJECT_ID
SELECT value
FROM `ltv_ecommerce.20_aggred`
axv = sns.violinplot(x=df_dist_values["value"])
axv.set_xlim(-200, 3500)
Explanation: A few customers seems to have quite large quantities in their orders but the distribution is generally healthy. 
They could be weird behavior for the same customers but the Standard Deviation filters should minimize the risk. 
Analyzing those few outliers can be part of additional data preparation work that can help improve your model.
Per value
This section shows that there are quite a few outliers 'outliers' that spend way more than others. We want to ignore them when creating the RFM values.
End of explanation
%%bigquery --params $LTV_PARAMS --project $PROJECT_ID
-- Lenght are number of days
--
-- Date of the first order in the dataset.
DECLARE MIN_DATE DATE;                                          
-- Date of the final order in the dataset.
DECLARE MAX_DATE DATE;                                          
-- Date that separates inputs orders from target transactions.
DECLARE THRESHOLD_DATE DATE;                                    
-- How many days back for inputs transactions. 0 means from the start.
DECLARE WINDOW_LENGTH INT64 DEFAULT @WINDOW_LENGTH;
-- Date at which an input transactions window starts.
DECLARE WINDOW_START DATE;
-- How many days between thresholds.
DECLARE WINDOW_STEP INT64 DEFAULT @WINDOW_STEP;                 
-- How many days for the first window.
DECLARE WINDOW_STEP_INITIAL INT64 DEFAULT @WINDOW_STEP_INITIAL; 
-- Index of the window being run.
DECLARE STEP INT64 DEFAULT 1;                                   
-- How many days to predict for.
DECLARE LENGTH_FUTURE INT64 DEFAULT @LENGTH_FUTURE;                            
SET (MIN_DATE, MAX_DATE) = (
  SELECT AS STRUCT 
    MIN(order_day) AS min_days,
    MAX(order_day) AS max_days
  FROM
    `ltv_ecommerce.20_aggred`
);
SET THRESHOLD_DATE = MIN_DATE;
-- For more information about the features of this table,
-- see https://github.com/CamDavidsonPilon/lifetimes/blob/master/lifetimes/utils.py#L246
-- and https://cloud.google.com/solutions/machine-learning/clv-prediction-with-offline-training-train#aggregating_data
CREATE OR REPLACE TABLE ltv_ecommerce.30_featured
(
  -- dataset STRING,
  customer_id STRING,
  monetary FLOAT64,
  frequency INT64,
  recency INT64,
  T INT64,
  time_between FLOAT64,
  avg_basket_value FLOAT64,
  avg_basket_size FLOAT64,
  has_returns STRING,
  avg_time_to_return FLOAT64,
  num_returns INT64,
  -- threshold DATE,
  -- step INT64,
  target_monetary FLOAT64,
);
LOOP
  -- Can choose a longer original window in case 
  -- there were not many orders in the early days.
  IF STEP = 1 THEN
    SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP_INITIAL DAY); 
  ELSE
    SET THRESHOLD_DATE = DATE_ADD(THRESHOLD_DATE, INTERVAL WINDOW_STEP DAY);
  END IF;
  SET STEP = STEP + 1;
  IF THRESHOLD_DATE >= DATE_SUB(MAX_DATE, INTERVAL (WINDOW_STEP) DAY) THEN
    LEAVE;
  END IF;
  -- Takes all transactions before the threshold date unless you decide
  -- to use a different window lenght to test model performance.
  IF WINDOW_LENGTH != 0 THEN
    SET WINDOW_START = DATE_SUB(THRESHOLD_DATE, INTERVAL WINDOW_LENGTH DAY);
  ELSE
    SET WINDOW_START = MIN_DATE;
  END IF;
  INSERT ltv_ecommerce.30_featured
  SELECT
    -- CASE
    --   WHEN THRESHOLD_DATE <= DATE_SUB(MAX_DATE, INTERVAL LENGTH_FUTURE DAY) THEN 'UNASSIGNED'
    --   ELSE 'TEST'
    -- END AS dataset,
    CAST(tf.customer_id AS STRING),
    ROUND(tf.monetary_orders, 2) AS monetary,
    tf.cnt_orders AS frequency,
    tf.recency,
    tf.T,
    ROUND(tf.recency/cnt_orders, 2) AS time_between,
    ROUND(tf.avg_basket_value, 2) AS avg_basket_value,
    ROUND(tf.avg_basket_size, 2) AS avg_basket_size,
    has_returns,
    CEIL(avg_time_to_return) AS avg_time_to_return,
    num_returns,
    -- THRESHOLD_DATE AS threshold,
    -- STEP - 1 AS step,
    ROUND(tt.target_monetary, 2) AS target_monetary,
  FROM (
      -- This SELECT uses only data before THRESHOLD_DATE to make features.
      SELECT
        customer_id,
        SUM(value) AS monetary_orders,
        DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,
        DATE_DIFF(THRESHOLD_DATE, MIN(order_day), DAY) AS T,
        COUNT(DISTINCT order_day) AS cnt_orders,
        AVG(qty_articles) avg_basket_size,
        AVG(value) avg_basket_value,
        CASE
          WHEN SUM(num_returns) > 0 THEN 'y'
          ELSE 'n'
        END AS has_returns,
        AVG(time_to_return) avg_time_to_return,
        THRESHOLD_DATE AS threshold,
        SUM(num_returns) num_returns,
      FROM
        `ltv_ecommerce.20_aggred`
      WHERE
        order_day <= THRESHOLD_DATE AND
        order_day >= WINDOW_START
      GROUP BY
        customer_id
    ) tf
  INNER JOIN (
    -- This SELECT uses all data after threshold as target.
    SELECT
      customer_id,
      SUM(value) target_monetary
    FROM
      `ltv_ecommerce.20_aggred`
    WHERE
      order_day <= DATE_ADD(THRESHOLD_DATE, INTERVAL LENGTH_FUTURE DAY)
      -- Overall value is similar to predicting only what's after threshold.
      -- and the prediction performs better. We can substract later.
      -- AND order_day > THRESHOLD_DATE
    GROUP BY
      customer_id) tt
  ON
    tf.customer_id = tt.customer_id;
END LOOP;
Explanation: The distribution shows a few outliers that you could investigate to improve the base model that you create in this tutorial.
Create inputs and targets for the ML model
The goal of this tutorial is to predict How much each customer is going to spend in the next N days knowing their transactions history.
Because all customers have different buying behavior over a specific period of time, this tutorial create multiple records per customer by:
Moving a threshold date by WINDOW_STEP over the dataset to create input and targets.
Aggregates input data per customer using transactions between WINDOW_START and THRESHOLD_DATE threshold date.
Aggregates the monetary value of the target transactions whose dates are between THRESHOLD_DATE and THRESHOLD_DATE + LENGTH_FUTURE.
Based on the size of your dataset and what you are trying to predict, you can update those values as needed in the Set parameters for LTV section.
This tutorial uses all historical data to predict the monetary value of its customers in the next 30 (LENGTH_FUTURE) days with a dataset that contains inputs/target value calculated every 30 days (WINDOW_STEP).
Query
End of explanation
%%bigquery --project $PROJECT_ID
-- Shows all data for a specific customer and some other random records.
SELECT * FROM `ltv_ecommerce.30_featured` WHERE customer_id = "10"
UNION ALL
(SELECT * FROM `ltv_ecommerce.30_featured` LIMIT 5)
ORDER BY customer_id, frequency, T
%%bigquery df_featured --project $PROJECT_ID
ltv_ecommerce.30_featured
df_featured.describe()
# Display distribution for all columns that are numerical (but will still ignore the categorical ones like day of the week)
valid_column_names = [key for key in dict(df_featured.dtypes) if dict(df_featured.dtypes)[key] in ['float64', 'int64']]
NUM_COLS = 5
NUM_ROWS = math.ceil(int(len(valid_column_names)) / NUM_COLS)
fig, axs = plt.subplots(nrows=NUM_ROWS, ncols=NUM_COLS, figsize=(25, 7))
for idx, cname in enumerate(valid_column_names):
  x = int(idx/NUM_COLS)
  y = idx % NUM_COLS
  sns.violinplot(df_featured[cname], ax=axs[x, y], label=cname)
Explanation: Dataset
End of explanation
# You can run this query using the magic cell but the cell would run for hours.
# Although stopping the cell would not stop the query, using the Python client
# also enables you to add a custom parameter for the model name.
suffix_now = datetime.now().strftime("%Y%m%d_%H%M%S")
train_model_jobid = f'train_model_{suffix_now}'
train_model_sql = f'''
CREATE OR REPLACE MODEL `ltv_ecommerce.model_tutorial_{suffix_now}`
       OPTIONS(MODEL_TYPE="AUTOML_REGRESSOR",
               INPUT_LABEL_COLS=["target_monetary"],
               OPTIMIZATION_OBJECTIVE="MINIMIZE_MAE")
AS SELECT 
  * EXCEPT(customer_id)
FROM 
  `ltv_ecommerce.30_featured`
'''
bq_client.query(train_model_sql, job_id=train_model_jobid)
Explanation: Seems like for most values, there is a long tail of records. This is something that might required additional feature preparation even if AutoML already provides some automatic engineering. You can investigate this if you want to improve the base model.
Train the model
This tutorial uses an AutoML regressor to predict the continuous value of target_monetary.
With a non-AutoML model, you would generally need to:
1. Apply common ML patterns such as normalization or clipping.
1. Split data in two to three datasets for training, evaluating and testing.
AutoML lets you split your data:
- Manually using a column with a name for each split.
- Manually using a column that defines a time
- Automatically
This tutorial uses the latter option where AutoML automatically assigns each row to a split.
End of explanation
%%bigquery --params $LTV_PARAMS --project $PROJECT_ID
-- TODO(developer): 
-- 1. Update the model name to the one you want to use.
-- 2. Update the table where to output predictions.
-- How many days back for inputs transactions. 0 means from the start.
DECLARE WINDOW_LENGTH INT64 DEFAULT @WINDOW_LENGTH;
-- Date at which an input transactions window starts.
DECLARE WINDOW_START DATE;
-- Date of the first transaction in the dataset.
DECLARE MIN_DATE DATE;                                          
-- Date of the final transaction in the dataset.
DECLARE MAX_DATE DATE; 
-- Date from which you want to predict.
DECLARE PREDICT_FROM_DATE DATE;
SET (MIN_DATE, MAX_DATE) = (
  SELECT AS STRUCT 
    MIN(order_day) AS min_days,
    MAX(order_day) AS max_days
  FROM
    `ltv_ecommerce.20_aggred`
);
-- You can set any date here. In production, it is generally today.
SET PREDICT_FROM_DATE = MAX_DATE;
IF WINDOW_LENGTH != 0 THEN
  SET WINDOW_START = DATE_SUB(PREDICT_FROM_DATE, INTERVAL WINDOW_LENGTH DAY);
ELSE
  SET WINDOW_START = MIN_DATE;
END IF;
CREATE OR REPLACE TABLE `ltv_ecommerce.predictions_tutorial`
AS (
SELECT
  customer_id,
  monetary AS monetary_so_far,
  ROUND(predicted_target_monetary, 2) AS monetary_predicted,
  ROUND(predicted_target_monetary - monetary, 2) AS monetary_future
FROM
  ML.PREDICT(
    -- /!\ Set your model name here.
    MODEL ltv_ecommerce.model_tutorial_YYYYMMDD,
    (
      SELECT
        customer_id,
        ROUND(monetary_orders, 2) AS monetary,
        cnt_orders AS frequency,
        recency,
        T,
        ROUND(recency/cnt_orders, 2) AS time_between,
        ROUND(avg_basket_value, 2) AS avg_basket_value,
        ROUND(avg_basket_size, 2) AS avg_basket_size,
        has_returns,
        CEIL(avg_time_to_return) AS avg_time_to_return,
        num_returns
      FROM (
        SELECT
          customer_id,
          SUM(value) AS monetary_orders,
          DATE_DIFF(MAX(order_day), MIN(order_day), DAY) AS recency,
          DATE_DIFF(PREDICT_FROM_DATE, MIN(order_day), DAY) AS T,
          COUNT(DISTINCT order_day) AS cnt_orders,
          AVG(qty_articles) avg_basket_size,
          AVG(value) avg_basket_value,
          CASE
            WHEN SUM(num_returns) > 0 THEN 'y'
            ELSE 'n'
          END AS has_returns,
          AVG(time_to_return) avg_time_to_return,
          SUM(num_returns) num_returns,
        FROM
          `ltv_ecommerce.20_aggred`
        WHERE
          order_day <= PREDICT_FROM_DATE AND
          order_day >= WINDOW_START
        GROUP BY
          customer_id
      )
    )
  )
)
%%bigquery df_predictions --project $PROJECT_ID
ltv_ecommerce.predictions_windowed
df_predictions.describe()
from matplotlib.gridspec import GridSpec
fig = plt.figure(constrained_layout=True, figsize=(15, 5))
gs = GridSpec(2, 2, figure=fig)
sns.set(font_scale = 1)
plt.tick_params(axis='x', labelsize=14)
ax0 = plt.subplot(gs.new_subplotspec((0, 0), colspan=1))
ax1 = plt.subplot(gs.new_subplotspec((0, 1), colspan=1))
ax2 = plt.subplot(gs.new_subplotspec((1, 0), colspan=2))
sns.violinplot(df_predictions['monetary_so_far'], ax=ax0, label='monetary_so_far')
sns.violinplot(df_predictions['monetary_predicted'], ax=ax1, label='monetary_predicted')
sns.violinplot(df_predictions['monetary_future'], ax=ax2, label='monetary_future')
Explanation: This is an example of a model evaluation
Predict LTV
Predicts LTV for all customers. It uses the overall monetary value for each customer to predict a future one.
End of explanation
%%bigquery df_top_ltv --params $LTV_PARAMS --project $PROJECT_ID
DECLARE TOP_LTV_RATIO FLOAT64 DEFAULT @TOP_LTV_RATIO;
SELECT
  p.customer_id,
  monetary_future,
  c.email AS email
FROM (
  SELECT
    customer_id,
    monetary_future,
    PERCENT_RANK() OVER (ORDER BY monetary_future DESC) AS percent_rank_monetary
  FROM
    `ltv_ecommerce.predictions_windowed` ) p
-- This creates fake emails. You need to join with your own CRM table.
INNER JOIN (
  SELECT
    customer_id,
    email
  FROM
    `ltv_ecommerce.00_crm` ) c
ON
  p.customer_id = CAST(c.customer_id AS STRING)
WHERE
  -- Decides the size of your list of emails. For similar-audience use cases 
  -- where you need to find a minimum of matching emails, 20% should provide
  -- enough potential emails.
  percent_rank_monetary <= TOP_LTV_RATIO
ORDER BY monetary_future DESC
df_top_ltv.head(5)
# Shows distribution of the predicted monetary value for the top LTV customers.
print(df_top_ltv.describe())
fig, axs = plt.subplots()
sns.set(font_scale = 1.2)
sns.distplot(df_top_ltv['monetary_future'])
Explanation: The monetary distribution analysis shows small monetary amounts for the next month compare to the overall historical value. The difference is about 3 to 4 orders of magnitude.
One reason is that the model is trained to predict the value for the next month (LENGTH_FUTURE = 30). 
You can play around with that value to train and predict for the next quarter for example (LENGTH_FUTURE = 90)
Activation
This part shows how to activate on Google Ads using similar audience.
You can follow a similar process for Facebook for example
Extract top customers
This step extracts the top 20% customers with the highest future monetary value and join with a CRM table to get their email. 
The prediction used the overall monetary value but in this use case, we are looking at the most valuable in the future. You can modify the PERCENT_RANK to use another KPI.
End of explanation
# Sets your variables.
if 'google.colab' in sys.modules:
  from google.colab import files
ADWORDS_FILE = "/tmp/adwords.yaml"
DEVELOPER_TOKEN = "[YOUR_DEVELOPER_TOKEN]"
OAUTH_2_CLIENT_ID = "[YOUR_OAUTH_2_CLIENT_ID]"
CLIENT_SECRET = "[YOUR_CLIENT_SECRET]"
REFRESH_TOKEN = "[YOUR_REFRESH_TOKEN]"
# Creates a local YAML file
adwords_content = f
# AdWordsClient configurations
adwords:
  #############################################################################
  # Required Fields                                                           #
  #############################################################################
  developer_token: {DEVELOPER_TOKEN}
  #############################################################################
  # Optional Fields                                                           #
  #############################################################################
  # client_customer_id: INSERT_CLIENT_CUSTOMER_ID_HERE
  # user_agent: INSERT_USER_AGENT_HERE
  # partial_failure: True
  # validate_only: True
  #############################################################################
  # OAuth2 Configuration                                                      #
  # Below you may provide credentials for either the installed application or #
  # service account flows. Remove or comment the lines for the flow you're    #
  # not using.                                                                #
  #############################################################################
  # The following values configure the client for the installed application
  # flow.
  client_id: {OAUTH_2_CLIENT_ID}
  client_secret: {CLIENT_SECRET}
  refresh_token: {REFRESH_TOKEN}
  # The following values configure the client for the service account flow.
  # path_to_private_key_file: INSERT_PATH_TO_JSON_KEY_FILE_HERE
  # delegated_account: INSERT_DOMAIN_WIDE_DELEGATION_ACCOUNT
  #############################################################################
  # ReportDownloader Headers                                                  #
  # Below you may specify boolean values for optional headers that will be    #
  # applied to all requests made by the ReportDownloader utility by default.  #
  #############################################################################
  # report_downloader_headers:
    # skip_report_header: False
    # skip_column_header: False
    # skip_report_summary: False
    # use_raw_enum_values: False
# AdManagerClient configurations
ad_manager:
  #############################################################################
  # Required Fields                                                           #
  #############################################################################
  application_name: INSERT_APPLICATION_NAME_HERE
  #############################################################################
  # Optional Fields                                                           #
  #############################################################################
  # The network_code is required for all services except NetworkService:
  # network_code: INSERT_NETWORK_CODE_HERE
  # delegated_account: INSERT_DOMAIN_WIDE_DELEGATION_ACCOUNT
  #############################################################################
  # OAuth2 Configuration                                                      #
  # Below you may provide credentials for either the installed application or #
  # service account (recommended) flows. Remove or comment the lines for the  #
  # flow you're not using.                                                    #
  #############################################################################
  # The following values configure the client for the service account flow.
  path_to_private_key_file: INSERT_PATH_TO_JSON_KEY_FILE_HERE
  # delegated_account: INSERT_DOMAIN_WIDE_DELEGATION_ACCOUNT
  # The following values configure the client for the installed application
  # flow.
  # client_id: INSERT_OAUTH_2_CLIENT_ID_HERE
  # client_secret: INSERT_CLIENT_SECRET_HERE
  # refresh_token: INSERT_REFRESH_TOKEN_HERE
# Common configurations:
###############################################################################
# Compression (optional)                                                      #
# Below you may specify whether to accept and automatically decompress gzip   #
# encoded SOAP requests. By default, gzip compression is not enabled.         #
###############################################################################
# enable_compression: False
###############################################################################
# Logging configuration (optional)                                            #
# Below you may specify the logging configuration. This will be provided as   #
# an input to logging.config.dictConfig.                                      #
###############################################################################
# logging:
  # version: 1
  # disable_existing_loggers: False
  # formatters:
    # default_fmt:
      # format: ext://googleads.util.LOGGER_FORMAT
  # handlers:
    # default_handler:
      # class: logging.StreamHandler
      # formatter: default_fmt
      # level: INFO
  # loggers:
    # Configure root logger
    # "":
      # handlers: [default_handler]
      # level: INFO
###############################################################################
# Proxy configurations (optional)                                             #
# Below you may specify an HTTP or HTTPS Proxy to be used when making API     #
# requests. Note: You must specify the scheme used for the proxy endpoint.    #
#                                                                             #
# For additional information on configuring these values, see:                #
# http://docs.python-requests.org/en/master/user/advanced/#proxies            #
###############################################################################
# proxy_config:
  # http: INSERT_HTTP_PROXY_URI_HERE
  # https: INSERT_HTTPS_PROXY_URI_HERE
  # If specified, the given cafile will only be used if certificate validation
  # is not disabled.
  # cafile: INSERT_PATH_HERE
  # disable_certificate_validation: False
################################################################################
# Utilities Included (optional)                                                #
# Below you may specify whether the library will include utilities used in the #
# user agent. By default, the library will include utilities used in the user  #
# agent.                                                                       #
################################################################################
# include_utilities_in_user_agent: True
################################################################################
# Custom HTTP headers (optional)                                               #
# Specify one or more custom headers to pass along with all requests to        #
# the API.                                                                     #
################################################################################
# custom_http_headers:
#   X-My-Header: 'content'
with open(ADWORDS_FILE, "w") as adwords_file:
    print(adwords_content, file=adwords_file)
# Google Ads client
# adwords_client = adwords.AdWordsClient.LoadFromStorage(ADWORDS_FILE)
Explanation: Setup Adwords client
Creates the configuration YAML file for the Google Ads client. You need to:
1. Create Client ID and Secret using the Cloud Console
2. Follow these steps
End of explanation
ltv_emails = list(set(df_top_ltv['email']))
# https://developers.google.com/adwords/api/docs/samples/python/remarketing#create-and-populate-a-user-list
# https://github.com/googleads/googleads-python-lib/blob/7c41584c65759b6860572a13bde65d7395c5b2d8/examples/adwords/v201809/remarketing/add_crm_based_user_list.py
# Adds a user list and populates it with hashed email addresses.
# Note: It may take several hours for the list to be populated with members. Email
# addresses must be associated with a Google account. For privacy purposes, the
# user list size will show as zero until the list has at least 1000 members. After
# that, the size will be rounded to the two most significant digits.
# 
# def normalize_and_SHA256(s):
#   Normalizes (lowercase, remove whitespace) and hashes a string with SHA-256.
#   Args:
#     s: The string to perform this operation on.
#   Returns:
#     A normalized and SHA-256 hashed string.
#   
#   return hashlib.sha256(s.strip().lower()).hexdigest()
  
# def create_user_list(client):
#   # Initialize appropriate services.
#   user_list_service = client.GetService('AdwordsUserListService', 'v201809')
#   user_list = {
#       'xsi_type': 'CrmBasedUserList',
#       'name': f'Customer relationship management list #{uuid.uuid4()}',
#       'description': 'A list of customers that originated from email addresses',
#       # CRM-based user lists can use a membershipLifeSpan of 10000 to indicate
#       # unlimited; otherwise normal values apply.
#       'membershipLifeSpan': 30,
#       'uploadKeyType': 'CONTACT_INFO'
#   }
#   # Create an operation to add the user list.
#   operations = [{
#       'operator': 'ADD',
#       'operand': user_list
#   }]
#   result = user_list_service.mutate(operations)
#   user_list_id = result['value'][0]['id']
#   emails = ltv_emails
#   members = [{'hashedEmail': normalize_and_SHA256(email)} for email in emails]
#   mutate_members_operation = {
#       'operand': {
#           'userListId': user_list_id,
#           'membersList': members
#       },
#       'operator': 'ADD'
#   }
#   response = user_list_service.mutateMembers([mutate_members_operation])
#   if 'userLists' in response:
#     for user_list in response['userLists']:
#       print('User list with name "%s" and ID "%d" was added.'
#             % (user_list['name'], user_list['id']))
# create_user_list(adwords_client)
Explanation: Create an AdWords user list
Using emails of the top LTV customers, you create an AdWords list. If more than 5000 of the users are matched with AdWords email, a similar audience list will be created.
Note that this guide uses fake emails so running these steps is not going to work but you can leverage this code with emails coming from your CRM.
End of explanation
<END_TASK>