Periscope Data
The world’s fastest visualization platform for data analysts.

The High-Performance SQL Blog

Managing a Six-Figure Ad Budget with SQL

Managing a Six-Figure Ad Budget with SQL
March 3, 2016

At Periscope Data, one of our main marketing channels is advertising. We have to be very careful with advertising, because costs can quickly add up to outstrip the revenue we receive from our ad campaigns.

So how do we keep our unit economics around advertising healthy? With daily processes and some fancy SQL queries, which we’ll get into in part one of this two-part series.

Reconciling Our Ad Network Data

We advertise across several different ad networks and each network has slightly different ways in how they label and organize data. Facebook and Google both offer two levels of campaign organization, while Twitter only offers one, for example.

To make querying easier, we’ll convert the data into consistent patterns and put it all into one table named paid_spend_daily:

select 
  date(reporting_starts) as day
  , 'Facebook Ads' as source
  , campaign as campaign
  , ad_set_name as adgroup
  , amount_spent_usd as spend
from facebook_csv_upload
union all
select 
  date(time) as day
  , 'Twitter' as source 
  , campaign as campaign
  , null as adgroup
  , spend as spend
from twitter_csv_upload
union all
select 
  date(day) as day
  , 'AdWords' as source,
  , campaign as campaign
  , ad_group as adgroup
  , regexp_replace(cost, '(\\$|,)', '')::numeric(8,2) as spend
from google_csv_upload

Parsing URL Parameters

We use URL parameters to track our ad data once someone from a campaign visits our site. We need to parse these parameters to determine which ad network, campaign, and ad group drove a particular signup.

Regex is our friend here where we define our paid_pings table:

select *
from (
      select
        pings.*,
        case when url ilike '%source=adwords%' then 'AdWords'
             when url ilike '%source=twitter%' then 'Twitter'
             when url ilike '%ad-set=%' then 'Facebook Ads'
             else null end as source,
        case when url ilike '%campaign=%'
             then REPLACE(REGEXP_SUBSTR(url
             , 'campaign=([a-z0-9_\-]+)'), 'campaign=', '')
             else null end as campaign,
        case when url ilike '%adgroup=%' 
             then REPLACE(REGEXP_SUBSTR(url
              , 'adgroup=([a-z0-9_\-]+)'), 'adgroup=', '')
             else null end as adgroup
      from pings 
) t
where source is not null

Connecting Our Paid Data to our Signup Data

The ad networks we use only know when a signup has resulted from a campaign, not who that signup is. Knowing this is vital to attaching down-funnel data to a specific campaign.

Here we reference our paid_pings table to define a paid_signups table:

select    
  source,
  campaign,
  adgroup,
  signups.*,
  paid_pings.created_at as ping_created_at
from paid_pings
join signups on 
  paid_pings.created_at <= signups.created_at
  and paid_pings.cookie = signups.cookie

Organizing Our Internal Data

A key part of optimizing our ad campaigns involves determining which ones are profitable by looking at our data down the funnel from signup. In our case, we try to get each signup to do a demo with a salesperson, then start a trial, and, finally, become a customer.

This data resides in different tables, so we combine it into one table with paid_funnel_daily:

select   
  [created_at:pst:day] as day,
  source,
  campaign,
  adgroup,
  count(1) as signups,
  count(distinct case when crm_conversation then 
    paid_signups.id else null end) as demos,
  count(distinct sites.id) as trials,
  count(distinct plans.site_id) as customers,
  sum(first_plans.monthly_amount)*12 as arr
from
  paid_signups
  left join sites on paid_signups.id = sites.signup_id
  left join plans on sites.id = plans.site_id
group by 1,2,3,4

That’s it for part one. In part two, we’ll show you how our paid_spend_daily view fits into the process and the final query we use to create our ad tables. Stay tuned!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?