My Reports pipeline for commodity dashboard

This prototype automates My Reports delivery to a MySQL database via The Trade Desk API, in order to visualize campaign performance in Google Data Studio.

Components

The Trade Desk API

Enables programmatic report delivery.

AWS lambda service

Provides event-driven, serverless computing resources.

Function a (My Reports to S3)

Calls the MyReports endpoint, pulls the relevant file, and writes the object to an AWS bucket. In this use case, it is triggered by a nightly CloudWatch Scheduled Event (similar to cronjob).

Function b (S3 to rds)

Downloads the object that was just placed in S3 and inserts the rows into a previously created table in the RDS MySQL instance. In this use case, it is triggered by the 'Create Object' actions in the AWS S3 bucket.

S3 (AWS simple storage service)

Offers cheap object storage. In this use case, the S3 service stores all My Reports files in chronological order, as determined by the file names.

Rds my SQL database

Stores all My Reports data. Users can manipulate data here in preparation for loading into a wide range of visualization dashboards/stacks.

AWS cloud watch

Provides a monitoring service for AWS cloud resources and services that also enables an event stream describing changes. In this use case, CloudWatch is used to:

Parameter store

Enables, as a fully managed service, the storage of configuration values as environment variables. Used in place of Lambda’s native environment variables to demonstrate a more scalable solution, as it avoids some of the persistence issues associated with containerization while allowing for the sharing of configurations across different functions and services.

Given a low volume of functions and a low rate of change, Lambda’s native environment variables can provide additional simplicity over Parameter Store at deployment. However, sequential uses of the same Lambda function may result in the function running on a different container environment. This can prevent you from reliably using environment variables across multiple uses, especially after deploying new values.

To retrieve the necessary values to implement environment variables in Lambdas:

  1. Ensure that the code is importing the "os" Python library.
  2. Remove the Parameter Store retrieval code block. In "myreports_to_s3.py", the relevant code is in lines 38-52.
  3. Create objects representing values, where the key = database and the value = unique_name:

db_name = os.environ['database']

The function can now reference "unique_name" by calling "db_name".

Google data studio

Enables, as a free data visualization service, fully customizable and shareable dashboards.

In this use case, Google Data Studio provides a specific instance of creating a commodity-dashboard solution. Upon connecting and mapping the data types from MySQL database to Google Data Studio, it is possible to set up charts and graphs by clicking and dragging shapes on the service's pages. You can use the right-hand formatting pane to define data sources, dimensions, metrics, and date ranges for each chart. With Google Data Studio, you can also create basic calculated metrics according to the available fields defined during the mapping phase.

Design scope

This prototype is intended to demonstrate an end-to-end data pipeline solution from the My Reports endpoint to a commodity relational database using tools and services from AWS. The prototype's design addresses the following requirements:

The prototype needs to be able to:

It also needs to provide:

This design does not address the following, which may be necessary to create a product fully ready for production:

Operational expenses

The operational expenses associated with maintaining this pipeline are primarily a function of report size (and, therefore, spend), with the average report size around 125 MB for a total of about 3.5 GiB per month. Given this transaction volume and a constant instance size of "db.r5.large", RDS monthly costs should total about $176.50.

Given the size of daily computing jobs, the use of Lambda will be free.

Assuming consistent, average My Reports file size, it would take about 6.5 months (200 days) to exhaust the storage capacity of a default 20 GiB RDS instance.

Db growth during prototype testing

Estimated db growth over time

Maintenance and limitations

We have recommended the use of services wherever possible in the design and implementation of this pipeline, to minimize the need for manual maintenance. Maintenance is largely defined by tasks associated with data storage.

Database

Depending on client data-analysis strategies and database storage size, you may need to either increase storage or delete data associated with past campaigns. By creating separate databases or tables per advertiser, it's possible to have separate cleaning and maintenance strategies for different clients.

Data load

When visualizing large data sets or creating charts with multiple dimensions, Google Data Studio can fail to load the data, resulting in error messages and blank charts. To resolve the issue, you can refresh the data or limit the data being loaded by filtering the results.

To reduce load times, eliminate errors, and minimize CPU credit consumption, we recommend utilizing RDS r4 instance types.

Transformation

Although Google Data Studio provides support for basic calculated metrics, you may need to connect directly to the database to apply advanced transformations, including the creation of certain calculated metrics.

API call timeouts

When calling the My Reports advertiser-level endpoint with multiple advertisers, the request can timeout if there is more than one page of results.

To avoid this and optimize toward a full expression of serverless architecture, we recommend setting up separate Lambda functions per advertiser.

Potential improvements

The prototype demonstrates a pipeline pattern that you can build on to improve scalability, performance, and reliability, as well as provide additional feature sets. You can also alter the pattern to enable data analysis or extract, transform, load (ETL) directly from S3 using other AWS services such as Redshift or Glue.

Other improvements include:

Deployment

The following instructions for common deployment and management patterns are intended for use with Python3, the AWS CLI, a *nix shell.

Step one - create a lambda deployment package

The deployment package is a .zip file consisting of code and any dependencies.

With Python3 "virtualenv", use the following commands:

  1. cd venv/lib/python3.6/site-packages
  2. zip -r9 deployment_package.zip *
  3. mv deployment_package.zip /project/root
  4. zip -g deployment_package.zip lambda_function.py

For more information, consult: https://docs.aws.amazon.com/lambda/latest/dg/lambda-python-how-to-create-deployment-package.html

Step two - create a deployment iam role in AWS with the following managed permissions

Coming soon.

Step three - create an ec2 staging instance

If you are only using the AWS UI to deploy this pipeline, this step is optional.

It's required if you're using AWS CLI to deploy and manage the pipeline, which is the process we recommend.

When using AWS CLI, it is possible to create an instance with the following command:

aws ec2 run-instances \
--image-id ami-xxxxxxxx \ 
--count 1 \
--instance-type t1.micro \
--key-name MyKeyPair \
--security-groups my-sg

Step four - create an rds my SQL instance

With the AWS CLI:

aws rds create-db-instance \
--db-instance-identifier MySQLForLambda \
--db-instance-class db.r4.large \
--engine MySQL \
--allocated-storage 5 \
--no-publicly-accessible \
--db-name MyReportsDB \
--master-username ttd_user \
--master-user-password Password123! \
--backup-retention-period 3
--region us-east-1

Step five - create lambda functions

With the AWS CLI ("#" marks inline reference comments):

aws lambda create-function \ 
--region us-east-1 \ 
--function-name PutCSVS3 \ 
--zip-file fileb://./myreports.zip \ 
--role arn:aws:iam::135411XXX087:role/service-role/myreports_role \ 
--handler myreports.lambda_handler \ # the filename.handler-method value in your function
--runtime python3.6 \ 
--timeout 50 \ 
--memory-size 1024

Step six - update lambda functions

With the AWS CLI:

aws lambda update-function-code \
--function-name PutCSVS3 \
--zip-file fileb://./myreports

Step seven - configure VPC, Subnet, permissions

Coming soon.

Step eight - reduce deployment permissions to the minimum required production permissions

Coming soon.

My Reports setup

Create a new report schedule based on a template with the necessary fields, metrics, and conversions. This template will need to generate:

It will also need to disable any unnecessary report schedules. Any changes made to the template will also need to be made to the Python script.

Create database tables

Step one - connect to the rds database instance with the below connection-parameter strings

Step two - update and execute the following SQL query, as needed

The table columns must be created in matching order with the report columns to ensure data is loaded correctly. We recommend creating multi-index table definitions that correspond with commonly used object IDs.

CREATE TABLE IF NOT EXISTS table_name (Date DATE
, AdFormat VARCHAR(225)
, AdGroup VARCHAR(225)
, Audience VARCHAR(225)
, Campaign VARCHAR(225)
, Country VARCHAR(225)
, Creative VARCHAR(225)
, DeviceType VARCHAR(225)
, RecencyGroup VARCHAR(225)
, Region VARCHAR(225)
, Site VARCHAR(225)
, SupplyVendor VARCHAR(225)
, Clicks INT
, Impressions INT
, MediaCostUSD FLOAT
, TotalClickAndViewConversions_01 INT
, TotalClickAndViewConversions_02 INT
, TotalClickAndViewConversions_03 INT
, TotalClickAndViewConversions_04 INT
, TotalClickAndViewConversions_05 INT
, INDEX `Campaign_FI_1` (`Campaign`));

Step three - check to confirm that the reports are uploading to the correct table

You can confirm this by checking:

*The SQL execution query string in the "s3_to_rds" Lambda function will need to be updated if the table name is changed.

Deploy advertiser-specific lambda functions

To create a function per advertiser:

Step One - Create an advertiser-specific key/value pair in the Parameter Store

Step Two - Update the relevant code in the "MyReports_to_S3" Lambda function:

Step Three - Copy the updated code files to the staging EC2 instance to prepare to zip into deployment packages.

scp -i staging.pem myreports_to_s3.py ec2-user@ec2-xx-xx-xx-xx.compute-1.amazonaws.com:.

Step Four - SSH into the staging instance and verify the updated files have been sent successfully.

ssh -i staging.pem ec2-user@ec2-xx-xx-xx-xx.compute-1.amazonaws.com

Step Five - Update the deployment packages with the code files. This step assumes that the packages have already been created and uploaded to the staging instance.

zip -g myreports_to_s3.zip myreports_to_s3.py

Step Six - Update the Lambda functions with the deployment packages.

aws lambda update-function-code \
--function-name MyReports_to_S3 \
--zip-file fileb://./myreports_to_s3.zip

References