Introduction

This project provides a set of SQL queries designed to analyze e-commerce data using Google BigQuery. The goal of this project is to help data analysts gain insights into customer behavior, sales performance, and key business metrics through SQL queries executed on the Google Analytics Sample dataset.

Objectives

• Optimize marketing channels for better decision-making: Analyze bounce rates, traffic revenue, and session spending to allocate budgets effectively and boost conversions.
• Enhance purchase journey & maximize order value: Improve conversion rates and use data-driven product recommendations to drive higher sales.

Dataset Used

• This project uses the Google Analytics Sample Dataset from BigQuery, which contains data from the Google Merchandise Store. namely "bigquery-public-data.google_analytics_sample.ga_sessions_2017*"
• Data tables:

Data Exploration

1. Total Visits, Pageviews, and Transactions (Jan, Feb, Mar 2017)

• Calculates the total number of visits, pageviews, and transactions for the first three months of 2017.
• Expected Output: Aggregated data for each month, ordered chronologically.

SELECT distinct format_date('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
  ,count (totals.visits)  as visits
  ,sum (totals.pageviews) as pageviews
  ,sum (totals.transactions) as transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`
group by month
order by month;

2. Bounce Rate by Traffic Source (July 2017)

• Determines the bounce rate per traffic source to understand the effectiveness of each marketing channel.
• Formula: Bounce Rate = total_bounces / total_visits

select distinct trafficSource.source as trafficSource
  ,count(totals.visits) as total_visits
  ,sum(totals.bounces) as total_no_of_bounces
  ,round(100*(sum(totals.bounces) / count(totals.visits)),3) as bounce_rate
from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`
where parse_date('%Y%m%d',date) between '2017-07-01' AND '2017-07-31'
group by trafficSource
order by total_visits desc;

3. Revenue by Traffic Source (Weekly and Monthly, June 2017)

• Analyzes revenue generated from different traffic sources on a weekly and monthly basis.
• Uses UNNEST(hits) and UNNEST(hits.product) to access productRevenue

select 'Month'as time_type
  ,FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as time
  ,trafficSource.source as source
  ,round((SUM(product.productRevenue) / 1000000),4) as revenue
from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST (hits) hits,
UNNEST (hits.product) product
WHERE FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) = '201706'
  and product.productRevenue is not null
GROUP BY time, source
union all
select 'Week'as time_type
  ,FORMAT_DATE('%Y%V', PARSE_DATE('%Y%m%d', date)) as time
  ,trafficSource.source as source
  ,round((SUM(product.productRevenue) / 1000000),4) as revenue
from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST (hits) hits,
UNNEST (hits.product) product
WHERE FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) = '201706'
  and product.productRevenue is not null
GROUP BY time, source
ORDER BY revenue desc;

4. Average Pageviews by Purchaser Type (June, July 2017)

• Compares the average number of pageviews between purchasers and non-purchasers.

with purchaser as (
  select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
    ,round((sum(totals.pageviews) / count(distinct fullVisitorId)),8) as avg_pageviews_purchase
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits,
  UNNEST (hits.product) product
  where (totals.transactions >=1)
    and (product.productRevenue is not null)
    and (_table_suffix between '0601' and '0731')
  group by month
  order by month
)
, non_purchaser as (
  select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
    ,round((sum(totals.pageviews) / count(distinct fullVisitorId)),8) as avg_pageviews_non_purchase
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits,
  UNNEST (hits.product) product
  where (totals.transactions is null)
    and (product.productRevenue is  null)
    and (_table_suffix between '0601' and '0731')
  group by month
  order by month
)
select p.month
  ,p.avg_pageviews_purchase
  ,np.avg_pageviews_non_purchase
from purchaser p
join non_purchaser np
on p.month = np.month
order by month;

5. Average Transactions per User (July 2017)

• Computes the average number of transactions per user who made a purchase in July 2017.

select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
  ,round((sum(totals.transactions) / count(distinct fullVisitorId)),9) as Avg_total_transactions_per_user
from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST (hits) hits,
UNNEST (hits.product) product
where (totals.transactions >=1)
  and (product.productRevenue is not null)
  and (_table_suffix between '0701' and '0731')
group by month;

6. Average Revenue per Session (July 2017)

• Calculates the average revenue generated per session, considering only purchasing users.

select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
  ,round((SUM((product.productRevenue) / 1000000) / count(totals.visits)),2) as avg_revenue_by_user_per_visit
 from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST (hits) hits,
UNNEST (hits.product) product
where (totals.transactions is not null)
  and (product.productRevenue is not null)
  and  (_table_suffix between '0701' and '0731')
group by month;

7. Other Products Purchased by Customers Who Bought "YouTube Men's Vintage Henley" (July 2017)

• Identifies other products bought by customers who purchased the "YouTube Men's Vintage Henley" product.

with users_buy_the_product as (
  select  fullVisitorId
  from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits,
  UNNEST (hits.product) product
  where product.v2ProductName = "YouTube Men's Vintage Henley"
    and (_table_suffix between '0701' and '0731')
    and product.productRevenue is not null
  )
select product.v2ProductName as other_purchased_products
  ,sum(product.productQuantity) as quantity
from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
UNNEST (hits) hits,
UNNEST (hits.product) product
where fullVisitorId in (select fullVisitorId from users_buy_the_product)
  and product.v2ProductName != "YouTube Men's Vintage Henley"
  and (_table_suffix between '0701' and '0731')
  and product.productRevenue is not null
group by other_purchased_products
order by quantity desc;

8. Cohort Analysis: Product View to Add to Cart to Purchase (Jan, Feb, Mar 2017)

• Computes the conversion rates from product views to cart additions and purchases at the product level.

with month_view_data as (
  select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
        ,count(hits.eCommerceAction.action_type) as num_product_view
  from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits
  where hits.eCommerceAction.action_type = "2"
  group by month
  order by month
)
  , addtocart_data as (
  select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
        ,count(hits.eCommerceAction.action_type) as num_addtocart
  from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits
  where hits.eCommerceAction.action_type = "3"
  group by month
  order by month
)
, purchase_data as (
  select FORMAT_DATE('%Y%m', PARSE_DATE('%Y%m%d', date)) as month
      ,count(hits.eCommerceAction.action_type) as num_purchase
  from `bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
  UNNEST (hits) hits,
  UNNEST (hits.product) product
  where hits.eCommerceAction.action_type = "6"
    and product.productRevenue is not null
  group by month
  order by month
)
select m. month
  ,m.num_product_view
  ,a.num_addtocart
  ,p.num_purchase
  ,round(100*(a.num_addtocart / m.num_product_view ),2) as add_to_cart_rate
  ,round(100*(p.num_purchase / m.num_product_view),2) as purchase_rate
from  month_view_data m
left join addtocart_data a
on m.month = a.month
left join purchase_data p
on m.month = p.month
order by month;

Insights and Recommendations

1. Optimize Traffic and Conversion Rates

Insights:

• Traffic volume fluctuates, but transactions do not always scale proportionally.
• High bounce rates from certain sources (e.g., YouTube) suggest engagement issues.

Recommendations:

• Invest in high-converting traffic sources while optimizing underperforming ones.
• Improve landing pages and reduce friction for high-bounce traffic to increase engagement.

2. Revenue Optimization by Traffic Source

Insights:

• Direct traffic generates the highest revenue, while other sources contribute less.
• Weekly revenue fluctuations indicate possible marketing inefficiencies.

Recommendations:

• Allocate budget to the highest revenue-generating traffic sources.
• Implement time-based marketing campaigns to smooth out revenue inconsistencies.

3. Enhancing the Purchase Journey

Insights:

• A significant drop-off occurs between product views, add-to-cart, and purchase.
• Purchasers browse fewer pages than non-purchasers, indicating decision-making hesitation.

Recommendations:

• Streamline checkout, offer incentives (e.g., discounts, free shipping) to encourage purchases.
• Use urgency tactics (limited stock, countdown timers) to increase add-to-cart conversions.

4. Maximizing Order Value and Repeat Purchases

Insights:

• Customers who buy "YouTube Men's Vintage Henley" often purchase complementary products.
• Purchasers have a higher average transaction per user, indicating strong retention potential.

Recommendations:

• Introduce product bundles and targeted upselling strategies.
• Launch loyalty programs to retain high-value customers and drive repeat purchases.

5. Data-Driven Decision Making for Long-Term Growth

Insights:

• Monthly and weekly trends reveal opportunities for proactive strategy adjustments.
• Revenue per session highlights the efficiency of the purchase funnel.

Recommendations:

• Monitor traffic source performance and adjust acquisition strategies accordingly.
• Continuously test and optimize pricing, UX, and promotional strategies to maximize revenue.

Introduction

Company overview

The train is the retail dataset of a superstore that offers a wide category of products such as furniture, technology, and office supplies to customers.

Objective

The project is conducted to analyze the Effectiveness of marketing campaigns from selected data and give suggestions. Building the store sale summary dashboard and product performance summary dashboard deliver outcomes by three terms: sale trend, customer preferences, and product sales by category.

Description of the data

Store revenue summary dashboard

• Fundamental statistic metrics: total revenue and total order values give overview outcomes of marketing and sale of the store in the period of 4 years.
• Specific statistics: the average revenue per order, the average order sold per customer, and the average item sold per order values are vital to analyzing the effectiveness of promotion campaigns, customer purchase behavior, and customer loyalty.
• Monthly revenue tables: the tables deliver customer purchasing trends for a short-term period. Likewise, it also shows the impact of marketing campaigns on sales during seasons of the last year
• Yearly revenue tables: they provide a long-term evaluation of marketing strategy performance. To be more specific, it involves marketing initiatives, product positioning, or shifts in customer behavior.
• Regional revenue distribution map: the map shows total revenue changes by broad geographical areas like east, west, south, and north. In addition, it can be useful to analyze the effectiveness of marketing strategies by wide regions.
• State-specific revenue distribution map: the map delivers more sophisticated total sale data by smaller areas. It is crucial to indicate where the sales strategy is weak and strong.
• Pie chart: it shows a quick visualization of revenue by customer segments in total. This is at great avail to highlight biggest and smallest customer range targets.
• Bar chart: the bar chart offers data on segment growth and decline year to year, which is essential for sales trend analysis. Besides that, it also indicates how well marketing targeting methods have been toward each customer group over the years.

Product performance summary dashboard

• Yearly product category sales bar chart and text table: they visualize annual sales trends by category, shifts in consumer preference, and market change. Both provide sales growth and decline of each category over 4 years, pointing to the bestseller and worst product.
• Total sales share by product category pie chart: the chart gives an overview visualization of sales distribution by product categories over four years. It underlines the top and bottom category by revenue.
• Yearly revenue share by product category bar chart: the bar chart breaks out the percentage of product revenues each year. It is useful to understand increasing or decreasing market needs for each category.

Dashboards:

View dynamic dashboard - Store Revenue Summary

View dynamic dashboard - Product Performance Summary

Executive Summary

Conclusion

Sale trend: The superstore generated approximately $2.26 million in revenue across 9,800 orders over a four-year span, averaging "$459.48 dollars per order." Year-to-year figures fluctuated considerably, beginning near $480,000 in 2015 and culminating around $722,000 by 2018. Monthly variations proved most dramatic, with "52.77 percent" growth between February-March dropping to "-49.36 percent" the following month.

Customer preferences: Consumer segments dominated revenue generation at $1.15 million versus corporate and home office segments. The western region led geographically with $710,220, though internal state-level variation was substantial. "California, Washington, and Colorado" generated $446,306 combined, while remaining western states contributed minimal amounts by comparison.

Product sales by category: Technology products led revenue at $827,456 (36.59%), followed by furniture at $728,659 (32.22%) and office supplies at $705,422 (32.2%). Furniture demonstrated the "most stable growth of revenue" throughout the analysis period.

Suggestions for Further Improvement and Business Outcomes

1. Revenue fluctuation improvement

• Suggestion: Conducting a detailed analysis of the biggest fluctuation periods like 2016-2017, March-April, and November-December to figure out the underlying reasons. Then use these key insights to develop marketing campaigns like targeting, promotion, advertising, distributing, pricing, or product programs.
• Business outcomes: eliminating the up-and-down on revenue yearly or monthly, maintaining stable cash flow for business development and reinvestment. It also helps reduce cost for inventory management.

1. Customer Segmentation and Personalization

• Suggestion: Creating marketing research and personalizing marketing of the consumer segment which shows the highest revenue share and the most stable growth. Customer behavior, personalized emails, targeted ads, and customized product recommendations should be thoroughly analyzed.
• Business outcome: customer retention and volume of targeted customers will be enhanced, contributing to increasing revenue, profits, and brand awareness.

1. Regional Market Analysis

• Suggestions: more localized marketing activities should be launched in regions with vast revenue disparity across states like west and central regions. Boost business funds into campaigns in regions with less revenue disparity across states like east and south regions.
• Business outcomes: The cost of marketing will be reduced once the localized marketing activities work well. The cost of operation and logistics will be optimized.

1. Product Category Focus

• Suggestion: product development, innovative marketing, market analysis, and risk management are considered as approaches toward categories with high revenue and high variability like technology and office supplies. In contrast, steady and reliable marketing strategies and enhancing customer engagement are suitable for categories with high state variability.
• Business outcomes: market share increase and stabilized sales would be fruitful results of product development and innovative marketing. Sustained growth, increased customer loyalty, and operational efficiency are outcomes of steady, reliable marketing strategies.

I. Introduction

1. Introduction to Dataset

• Dataset: Superstore database
• Dataset stores sales information worldwide for a company called Superstore.
• Data tables:

2. Data Detail

• Contents of tables:
• Orders: table storing transaction information
• People: table storing information of sellers in each region
• Returns: table recording returned transactions
• How data is recorded in each table:
• All tables follow the Snapshot data capture type. Each row records a product type in the order. For orders with n products, n rows will be recorded.
• Preliminary assessment of data quality: null, distinct, quality rule, min/max

3. Business Questions

• Which markets and regions should the company focus on for expansion to maximize revenue growth?
• What are the most profitable product categories, and how can the company optimize its product strategy to increase profitability?

II. Design Thinking Method

Here are the five steps of design thinking:

Stage 1: Empathize

Stage 2: Define POV

Step 3 - Ideate

Step 4 - Prototype

Step 5 - Review

III. Visualization

1. Data Schema

2. Overall Business Performance

3. Market Analysis

4. Product Analysis

IV. Insights and recommendations

1. Focus on High-Growth Regions

• Insight: The Market Analysis dashboard shows significant revenue growth in the APAC (28%) and EU (23%) regions.
• Recommendation: Prioritize expanding market presence in these high-growth regions to capitalize on existing momentum and increase market share.

1. Enhance Product Offerings in Technology

• Insight: Technology products lead in sales, contributing 38% of the total revenue as shown in the Product Analysis dashboard.
• Recommendation: Invest in developing and marketing new technology products to maintain and enhance the dominant market position in this category.

1. Improve Customer Retention Strategies

• Insight: The Overall Business Performance dashboard indicates a total of 3050 product returns with a return rate of 5.9%.
• Recommendation: Implement quality improvement initiatives and enhanced customer support to reduce return rates and improve customer satisfaction.

1. Diversify Product Range in Emerging Markets

• Insight: The Revenue by Market analysis highlights untapped potential in South America and Africa.
• Recommendation: Introduce a diversified range of products tailored to the unique preferences and demands of customers in these emerging markets.

1. Optimize Inventory Based on Seasonal Trends

• Insight: The Product Performance Over Time chart reveals seasonal spikes in sales, particularly in Q4.
• Recommendation: Align inventory management and marketing efforts with seasonal demand patterns to ensure product availability and maximize sales.