Sign up for a SQL Lab account with the form below.
Watch our on-demand SQL 101 training, following along with the examples below.
The best way to learn is to practice! Run the intermediate and advanced queries below using your SQL Lab account.
SQL for Ecommerce Professionals
This is a hands-on training for ecommerce professionals to learn the basics of Structured Query Language (SQL).
Basic SQL
1. Basic select statement
Copied to clipboard!
select *
from campaign
limit 10
2. Choosing specific fields from a table
Copied to clipboard!
select date, campaign_name, spend
from campaign
limit 10
3. Using aliases
Copied to clipboard!
select a.date, a.campaign_name, a.spend
from campaign a
limit 10
4. Eliminating duplicates
Copied to clipboard!
select distinct date
from campaign
5. Sorting
Copied to clipboard!
select distinct date
from campaign
order by 1 desc
6. Aggregations (think pivots in Microsoft Excel)
Copied to clipboard!
select date, sum(spend)
from campaign
group by 1
order by 1 desc
7. Aggregations
Copied to clipboard!
select campaign_type , sum(spend)
from campaign
group by 1
order by 1 desc
8. Joining two tables (think vlookups in Microsoft Excel)
Copied to clipboard!
select a.keyword, a.match_type, b.campaign_name,
b.campaign_type, sum(a.spend) as spend,
sum(a.revenue) as ad_revenue
from keyword a
left join campaign b
ON
a.campaign_id = b.campaign_id
Group by 1,2,3,4
Intermediate SQL
1. Keyword brand vs. non-brand performance
Copied to clipboard!
select campaign_type,brand_type,
sum(k.spend) as spend,
sum(k.revenue) as revenue
from campaign c
left join keyword k
on c.campaign_name = k.campaign_name
where c.DATE > '05-01-2022'
and k.spend > 0
group by 1,2
order by 3 desc
2. Keyword matchtype performance
Copied to clipboard!
select campaign_type,brand_type,
sum(k.spend) as spend,
sum(k.revenue) as revenue
from campaign c
left join keyword k
on c.campaign_name = k.campaign_name
where c.DATE > '05-01-2022'
and k.spend > 0
group by 1,2
order by 3 desc
Advanced SQL
1. Advertising vs. overall sales
Copied to clipboard!
select s.asin, s.title, ad_revenue,
round((100 * ad_revenue)/(sum(ad_revenue) over()), 1)
ad_revenue_percentage,
overall_sales,
(100 * overall_sales)/(sum(overall_sales) over())
as product_sales_percentage
from (
select asin, title, sum(product_sales)
as overall_sales from sales s
where date > current_date-30
group by 1,2)s join
(select asin, title, sum(revenue) as ad_revenue
from product p
where date > current_date-30
group by 1,2)p on s.asin = p.asin
2. Top keywords in a campaign that is driving revenue
Copied to clipboard!
select keyword, campaign_name, revenue
from (
select keyword, campaign_name, revenue,
row_number() over (partition by campaign_name
order by revenue desc)
as revenue_order_count
from (
select keyword, campaign_name, sum(revenue) as revenue
from keyword k
where date > current_date-30
group by 1,2)k
)a
where revenue_order_count <= 3
order by campaign_name desc, revenue desc
3. Products generating 80% of overall sales
Copied to clipboard!
select asin, title,
100 * product_sales/(SUM(product_sales) OVER ())
as product_sales_percentage
from (
select asin, title, sum(product_sales) as product_sales
from sales s
where date > current_date-30
group by 1,2)s
4. Keywords generating 80% of advertising sales
Copied to clipboard!
select keyword, match_type, campaign_name,
100 * revenue/(SUM(revenue) OVER ())
as revenue_percentage
from (
select keyword, match_type, campaign_name,
sum(revenue) as revenue
from keyword k
where date > current_date-30
group by 1,2,3)k
Interested in learning more about Intentwise Analytics Cloud?
Complete this form, and a team member will be in touch to schedule a demo.
Sign Up for Intentwise Emails
Learn about product updates, webinars, and news for ecommerce professionals.
