This is a hands-on training for ecommerce professionals to learn the basics of Structured Query Language (SQL).
Sign up to get a training account.
A team member will reach out to you shortly with credentials.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Do You Have an Effective Data Strategy to Win on Amazon?
In this webinar, our CEO Sreenath Reddy shares best practices, frameworks, and pitfalls around managing your Amazon data effectively.
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?
Complete this form, and a team member will be in touch to schedule a demo.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Sign Up for Intentwise Emails
Learn about product updates, webinars, and news for ecommerce professionals.
