Marketing Analytics in SQL and PythonRiley PredumBlockedUnblockFollowFollowingFeb 24I work in marketing so I wanted to explore how to use programming tools to explore customer data, and calculate key marketing metrics like customer lifetime value (LTV) and cost per acquired customer (CAC).
These metrics are essential to understand and keep track of in relation to each marketing channel.
The metrics may vary by channel (i.
impressions and engagement for social, open rates and click-through rates for email, and so on) but they are all valuable for a business to keep track of.
By seeing these data points, you can sort and filter based on them to determine the performance of a given channel based on how many customers you are acquiring (CTR) and for how much per customer (CAC).
Powerful stuff!Let’s go ahead and dive in.
Check out the repo here and follow along with the SQL script and notebook in /02_code as you read if you like.
The data is in .
csv format in the /01_data folder.
Before anything else, you’ll need to create a database in your SQL client of choice (I use MySQL).
You can read about how to do that here if you’re not sure.
Once you have and are using the database (use [database_name]), you’ll need to import the .
csv files into the database.
I used MySQL’s import table wizard which is fine for this data set since it’s small (though it’s not efficient for large data tables).
To familiarize yourself with the database and its tables, it’s useful to look at the schema or Enhanced entity-relationship (EER) diagram:Table schema for the different tables in this data baseIn this case, it’s not as useful because there are no relationships between these tables.
But if you had a more complex database with many different relationships, you would find yourself returning to the schema rather often to sort things out.
Go ahead and try each segment of code here to see what calculations/columns are returned in your console.
In SQL, the end of a query is delimited by a ;.
Code for calculating each metricNext, fire up the Jupyter Notebook and get things set up with the first cell.
You shouldn’t need to change your directory if you ran the notebook from its place in the repo.
The code to set up for that is as follows:You can then visualize the total amount spent per month for each customer against how many purchases they made per month with the following code:Code for plot of purchase amount (in USD) against number of purchases per monthPurchase amount (in USD) against number of purchases per monthThere’s no strong correlation here, but it’s still interesting to see how the points fall and to determine whether or not there are associations.
Knowing there isn’t an association is just as valuable as finding that there is!This is incredibly powerful stuff because you can see what marketing channel the largest number of cheaper-to-acquire customers were sourced from.
I’ll show the output of that SQL code next.
Table of conversions, ordered by conversions in descending order.
In this case, the Twitter campaign, though it had lower impressions, resulted in more click-throughs and the most number of conversions.
It also had the lowest CAC at $15.
09 per customer.
And with an LTV of $34,448, that’s a very worthwhile investment!.Something’s going right with Twitter.
You can quickly see how much impact this can have and how it can be used to drive business and marketing decisions and strategy.
When in doubt, look to the data!Like what you read?.I have more articles.
Want to see more coding projects?.I have more of that too.
‘Til next time, happy coding!Riley.