Database for Digital Marketers — 1.0 (The basics)
So, you are a marketing genius! You have just rolled out an innovative digital marketing campaign and want to find out its impact on revenue. You can go two ways about it.
1. Send a series of questions to your Data Analyst, wait for her response and repeat.2. Look at the data up and close, understand and discover what your customer wants.
If you like the latter approach, this series of posts will help you get your feet wet. I hope you enjoy and learn something new. Junior level SQL developer supporting Marketing Teams will also find this series useful. If you have any questions, ask away. I will do my best to respond to your queries.
In my 15+ years of experience as a Database Developer /Architect /Administrator /Analyst (Yes, I have worn all these hats), I have noticed that people are so eager to write queries that they do not invest the time needed to understand how a database works. Even before one learns how to use inner/left/outer join, it is important to understand the concepts of set theory. You will only need to know the basic set theory from your high school days. Unions and Intersections. So, my first article of the series will be about basic set theory and how that relates to database joins. Each table in a database is considered a set of data and join in a database is a way to get data from two or more tables (or data sets) with a common key.
In the above diagram, let us make the following assumptions.
To represent a set of customers of Company X, we will writeCustomers C = {1,2,3,4,5,6,7,8,9,10,11,12,13}Products P={A,B,C,D}CaliforniaCustomers CC = {3,4,5,8,12}ProductACustomers PA ={3,6,7,12}
If you need to get the list of Customers who lives in California and bought Product A, the customers need to exist in both sets:CC and PA. From the Venn diagram above, you can tell that Customerid 3 and 12 fall in this bucket. In set theory, you can represent this {3,12} as an intersection(set symbol ∩) of CC and PA.CC ∩ PA = {3,12}In database terms, Intersection(∩ ) is known as an inner join. For CC ∩ PA, {3,12} are the two keys that belong to data set CC and data set PA.
If you need to get the list of Customers who live in California or buy Product A, the customers need to exist in either of the two sets: CC and PA. From the Venn diagram above, we know that CustomerID 3,4,5,6,7,8 and 12 fall in this bucket. This set {3,4,5,6,7,8,12} is represented as union(set symbol ∩) of CC and PA.CC U PA = {3,4,5,6,7,8,12}In database terms, Union (U) is known as full outer join. For CC U PA, {3,4,5,6,7,8,12} are the keys that belongs to either data set CC or data set PA.
Now, if you need to get the list of all Customers who live in California and may or may not have bought Product A, the customers must exist in sets CC but may or may not exist in PA. From the Venn diagram above, you can tell that CustomerID 3,4,5,8 and 12 fall in this bucket. This set {3,4,5, 8,12} is represented as a superset (set symbol ⟕) of CC and PA.CC ⟕ PA = {3,4,5,8,12}In database terms, this symbol(⟕) is known as the left outer join. For CC ⟕ PA, {3,4,5,8,12} are the keys that belong to data set CC and may or may not be present in data set PA.
It is very important that you understand this concept as this knowledge will make your database journey smooth down the road.
This covers the basic concepts necessary we need to delve into database queries. In my next article, I will show how to represent these data sets in the database and how to query data from a database.