Conquer Your Digital Marketing Data Fears: 5 Steps to Master Excel-to-SQL Conversion (Plus Code to Get You Started)

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.

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.

Don’t rush! Skipping the basics may lead to future roadblocks.

Learn the ropes and the data world will be your playground.

For instance, imagine you have a table named sales_data with columns like product_id, sale_date, and revenue. To find the revenue for product_id 101 on 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:

revenue
150

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 campaign_name, clicks, impressions, and 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_data table, 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;

Results

campaign_name	total_cost	total_clicks	total_impressions
Campaign A	500.00	300	2000
Campaign B	600.00	400	2500

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;

Results

campaign_name	avg_cpc	avg_cpi
Campaign A	1.67	0.25
Campaign B	1.50	0.24

That’s it!

TL;DR

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.