SQL for Ecommerce Professionals

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.
A solutions expert will reach out to you shortly with next steps.
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.