Building a CRM Email Database with Postgres SQL.

  • Dream it.

    Initially, I created Python scripts to pull my Gmail for my ML-AI project (work in progress). However, I found it easier to catch up on important emails when sorting the table by grouping with domain & email addresses.

  • Build it.

    So I created a permanent storage solution for sorting and catching up on important emails. This new setup allows me to ensure data integrity scales easily and reduces complexity from using a coding language.

  • Grow it.

    Despite being a bootstrap solution, the framework can be a beneficial addition to a CRM system compared to plainly using the Gmail search bar.

The philosophy behind the ERD design.

As a person who has lots of different emails from multiple sources consistently, I want to simplify my inbox experience, so that I read the most important emails.

  1. I want to group by domain for larger services like (Uber, and LinkedIn, Economist)

  2. However, the email addresses need to be divided for Gmail due to different subtopics/people, similar to Linkedin.

  3. To prioritize services, I need the ability to add contexts to different tables of dim_domain & dim_mail_addresses as UberEats and Grubhub can have tags of food delivery.

SQL Process diagram

SQL queries to create the database:

SQL1: Creating email_facts

SQL2: Data cleaning & processing

SQL3: Create dim_domain table

SQL4: Create dim_mail_address

SQL5: data normalization/encoding

SQL queries to update the database:

SQL6: Create new_email_facts table

SQL7: Update new data to dim_domain and dim_mail_address

SQL8: Merging old & new data

What did I learn & next steps:

  • From this project, I learn the entire process and usage of an SQL database from creating & processing data to query values & updating the database. One interesting point I get from this is that keeping documentation of the creation of the database allows for quick and painless SQL queries to update new data. Also, from this entire process, I learned to love to use the transaction to the SQL database allowing myself to rollback on queries that may permanently damage the database.

  • As mentioned above, I want a DB that will allow me to scan through my email with the context column provided in both the dim_domain & dim_mail_address. I plan to add that column soon by using the function ALTER TABLE + ADD (create the column) & UPDATE table + SET col1 = value WHERE col2 = value (to create context like uber & GrubHub as food deliveries)