SQL recipes - beyond the click
Jun 11, 2014
There are a lot of tools, including third party solutions, to help companies track customer behavior. Software like Google Analytics can help with aggregate metrics and funnel optimization, but nothing beats the granularity, control, and flexibility you get from collecting and leveraging your in-house data.
An added bonus to owning your marketing data is agility and speed to insights (avoiding the inherent delay in third party tracking) and an infrastructure that affordably scales with you.
Most third party tools will focus on measuring proximate performance -- how many near term conversions came from each click, what was the same day conversion rate, how much revenue can be directly attributed to each advertisement?
The full picture goes beyond the events immediately after the click and into a user's first week, month, or year. So why do companies talk about lifetime value but focus on shorter term metrics? Because setting up internal customer tracking can be hard. If you own your data and have the right tools it is easy.
Let's look at a basic CPC (cost per click) model -- you pay for traffic from various search, display, and social campaigns and want to know where to focus and allocate your marketing budget.
These two SQL tables are the main ingredients:
Users table: An entry for every user, often backfilled to include the user's sign up source (if not, we can calculate that separately).
Orders table: An entry for every transaction.
You want to relate these tables to each other to answer questions like "What was the ROAS / payback period for last month's 'Looker The Other Way' Facebook campaign?"
You probably want to look at revenue by cohort over time. The simple case:
The above SQL gives you a derived view you can use throughout your data model:
Note: You will have one row per purchaser in this table, so in this example 500,000 users have placed at least one order.
Now you can join
users to create an extension of the users table. In Looker, this would be:
This approach effectively widens your users table in Looker -- you can explore Users and facts about their orders, order items, clickstream, and more depending on what data you capture.
You can now answer the question "What was the 30 day payback for the Facebook campaign we ran between date x and date y (segmented by week)?" or any variant thereof once you join in cost data. For example:
Pick the dimensions to group and filter by (e.g. sign up week, campaign, # of customer's orders in a certain category, gender, geography) and the measures to aggregate by (30 day revenue, repeat purchase rate, referral rate, returns/cancellation rate) -- and you have an agile view of cohort behavior at your fingertips.
To learn more about how to do this in Looker on top of your own data, get in touch.