Today, I’m sharing the steps you need to upgrade your Excel skills to SQL.
SQL unlocks a treasure trove of benefits. It helps you crunch data more quickly, blend different data sources together, and provide deeper insights to your clients or your boss. Plus, you’ll avoid all the headaches that come along with trying to keep Excel worksheets organized.
Unfortunately, most digital marketers figure that Excel is “good enough”. But that’s mostly because SQL is “too hard”.
Special thanks to this Bright Data for sponsoring this week’s newsletter.
|Only 17% of companies are utilizing Web Data for market research! |
That means 83% are missing out…
Are you part of the winning 17%?Access web data now
Reap the rewards of SQL while dodging the pitfalls
Ignoring the power of SQL will leave you stuck:
- Frustration: Struggling with slow, clunky Excel sheets stops your progress.
- Overwhelm: Juggling data in Excel can be confusing and tedious.
- Inaccuracy: If you miss a formula or transpose data incorrectly, you’re doomed.
- Stagnation: Thinking that “Excel is good enough” keeps you stuck doing the same thing forever.
Follow these 5 steps to turn Excel reports into SQL gold and level up your digital marketing analytics game.
Here’s how, step by step:
Step 1: Get Comfy with SQL
Knowing SQL basics will pave your way to data greatness.
Learn the ropes and the data world will be your playground.
For instance, imagine you have a table named
sales_data with columns like
revenue. To find the revenue for product_id
2023-03-01, you’d use the following SQL query:
SELECT revenue FROM sales_data WHERE product_id = 101 AND sale_date = '2023-03-01';
Assuming that product_id 101 had a revenue of $150 on 2023-03-01, the result of the query would be:
Step 2: Organize Your Excel Data
A tidy dataset makes for a smooth SQL transition.
But messy data leads to errors and confusion.
Fortunately, you can invest in the organization now and save time later.
Tips for keeping your data organized:
- Remove unnecessary columns
Keep only the relevant columns in your dataset.
This helps streamline the data import process and makes it easier to work with the data in SQL.
Protip: Start with the absolute most basic data you can. Once you’re comfortable add more columns.
- Ensure consistent data formats:
Make sure all data entries in a column have the same format, such as date formats, number formats, or text capitalization.
Consistent formats help avoid data import issues and simplify your SQL queries.
- Handle missing or incorrect data:
Check your dataset for missing or incorrect data entries and decide how to handle them.
You can fill in missing values with a default value or an average, or remove the rows with missing data altogether.
Step 3: Choose Your SQL Tool
Picking the right tool sets the stage for your SQL success.
Don’t get lost in options—select a tool suited to your needs.
The perfect tool will make your SQL journey a breeze.
For a database system, I recommend PostgreSQL.
- It’s free
- It’s very popular
- It’s easy to use
There are other options out there like MySQL, Oracle, and Microsoft SQL server.
No need to overthink this: if you’re not sure where to start, just pick PostgreSQL.
Protip: I’m a big fan of another free tool called DBeaver for working with databases.
Step 4: Import Excel Data to SQL
This step bridges the gap between Excel and SQL.
Here’s the exact 5-step process to follow for PostgreSQL, using a marketing campaign analysis as an example
1. Save Excel data as a CSV file
In Excel, click “File” > “Save As” and choose “CSV” as the file type. This format is compatible with PostgreSQL.
Save your digital marketing dataset, containing columns like
cost, as a CSV file.
2. Create a new table in PostgreSQL
Design a table schema that matches your Excel dataset.
CREATE TABLE digital_marketing_data ( campaign_name VARCHAR(255), clicks INT, impressions INT, cost DECIMAL(10, 2) );
3. Install a PostgreSQL client
Use a client like pgAdmin or DBeaver to connect to your PostgreSQL database.
(again, I recommend DBeaver)
4. Import CSV data into the new table
Use the PostgreSQL client’s import feature to load the CSV data into the digital_marketing_data table.
Sample code for pgAdmin:
- Right-click on the
digital_marketing_datatable, choose “Import/Export”
- Set “Import” as the direction
- Choose your CSV file
- Set the format as “csv”
- Click “OK”
5. Verify data import
Run a simple SQL query to ensure your data was imported correctly.
SELECT * FROM digital_marketing_data LIMIT 5;
Note: Watch out for data format mismatches while importing.
With your Excel data now in PostgreSQL, you can start analyzing and visualizing your digital marketing data using powerful SQL queries.
Step 5: Query, Analyze, and Visualize
Time to flex your SQL muscles and reap the rewards.
Don’t get too fancy – start with simple queries before leveling up.
Here are a few examples to get you started:
Example query 1
Calculate the total cost, clicks, and impressions for each campaign.
SELECT campaign_name, SUM(cost) as total_cost, SUM(clicks) as total_clicks, SUM(impressions) as total_impressions FROM digital_marketing_data GROUP BY campaign_name;
Example query 2
Find the average cost per click (CPC) and cost per impression (CPI) for each campaign.
SELECT campaign_name, (SUM(cost) / SUM(clicks)) as avg_cpc, (SUM(cost) / SUM(impressions)) as avg_cpi FROM digital_marketing_data GROUP BY campaign_name;
- Step 1: Get comfy with SQL by understanding basic commands and structure.
- Step 2: Organize your Excel data by cleaning and structuring your dataset.
- Step 3: Choose the right SQL tool for your needs (PostgreSQL and DBeaver).
- Step 4: Import your Excel data into your SQL tool by creating tables and inserting records.
- Step 5: Query, analyze, and visualize your data using powerful SQL queries.
Ready to dive even deeper? Boost your SQL skills by signing up for Solving with SQL, the cohort-based course and community! You’ll learn everything you need to solve real-world digital marketing problems using SQL in 30 days. (Sign ups close April 14th).
Don’t wait any longer – take the next step toward mastering SQL and increase your earning potential today.