At Sisense, a significant marketing channel 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_signup 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!