Free Trial Performance Analysis in SQL
How much is a free trial worth? In this SQL notebook, We'll use SQL to analyze marketing data for a free trial of a product. We’ll summarize and aggregate data to calculate the sorts of metrics that are the bread and butter of any marketing analyst role.
Data Deals
We'll be using synthetic data that was created for this notebook. This data represents a product that is sold via a 1-month free trial. The Free Trials table records instances of customers beginning a free trial. 1 month after the free trial period starts, the customer may choose to pay, and if so we will have a Purchase record.
There are four tables:
Free Trials
A list of instances of free trials starts.
- Trial ID - An ID unique to the Free Trial.
- Free Trial Start Date - The date when the customer began their free trial.
- Region - The world region where the customer is located.
Purchases
A list of instances of customers paying, following their free trial.
- Trial ID - The ID of the free trial, from the Free Trials table. This ID is unique as each trial may have a maximum of 1 purchase asociated with it.
- Purchase Date - The date when the customer made their purchase, 1 month after they began their free trial.
- Purchase Value - The USD value of the Customer's purchase.
Dates
A list of dates, provided for convenience.
- Date - A sequential list of dates.
- Month - The first of the month for each date.
Prices
Optional - a list of prices of the product by region over time. This table will not be used in the live training, and is for optional follow-up activity. Prices are set on a Monthly basis, but the price for each customer is set at the beginning of their free trial, so subsequent price changes will not affect a customer.
- Free Trial Start Month - the month of free trials that the price applies to.
- Region - the customer's world region, as in the Free Trials table.
- The price that will be locked in at the beginning of the customer's Free Trial, based on their Free Trial Start Month & Region.
select * from trialsselect * from purchases1. Getting Familiar with the Data
We will query the Free Trials & Purchases tables, and produce graphs showing the volume of both of these over time.
a) Group trials by the month of free_trial_start_date (and order by the same).
Count the rows as num_free_trials.
select
date_trunc('month', free_trial_start_date) as month
, count(*) as num_free_trials
from trials
group by 1
order by 1b) Group purchases by the month of purchase_date (and order by the same).
Count the rows as num_purchases, and sum purchase_value as usd_value.
Call the output purchases_by_month.
select
date_trunc('month', purchase_date) as month
, count(*) as num_purchases
, sum(purchase_value) as usd_value
from purchases
group by 1
order by 1c) Create a line graph of num_purchases by month.
Purchases per Day
2. Data Aggregation 1 - Velocity Metrics by Month
We will pull metrics for Free Trial Starts, Purchases, and Gross Merchandise Value by month from the Free Trials & Purchases tables.
a) Now that we can aggregate the data by month, create both summaries as Common Table Expressions (CTEs), and left join our purchases per month against the free trials per month to get the results into a combined results table.
with free_trials_per_month as (
select
date_trunc('month', free_trial_start_date) as month
, count(*) as num_free_trials
from trials
group by 1
order by 1
)
, purchases_per_month as (
select
date_trunc('month', purchase_date) as month
, count(*) as num_purchases
, sum(purchase_value) as usd_value
from purchases
group by 1
order by 1
)
select
free_trials_per_month.month
, free_trials_per_month.num_free_trials
, purchases_per_month.num_purchases
, purchases_per_month.usd_value
from free_trials_per_month
left join purchases_per_month
on purchases_per_month.month = free_trials_per_month.monthDo you notice that there's some data missing? When we left join purchases_per_month, we only match against months that exist in free_trials_per_month. There are several ways to solve this.