Measure commercial performance
Track website sessions, orders, revenue, gross profit, average order value, and conversion rate over time.
Project Overview
This project analyzes the end-to-end performance of Maven Fuzzy Factory, an online retailer selling teddy bear products. Raw e-commerce data was loaded into PostgreSQL, validated, and transformed into analytics views. The resulting data was exported for an interactive Power BI dashboard.
The dashboard is organised into four reporting areas: executive performance, marketing performance, product and refund performance, and landing-page performance. Together, these views connect website traffic and acquisition activity to orders, revenue, gross profit, refunds, and conversion outcomes.
Business Objective
The objective is to give Maven Fuzzy Factory a clear, evidence-based view of how visitors move from acquisition channels and landing pages to orders and revenue, while identifying opportunities to improve marketing efficiency, conversion performance, product profitability, and refund outcomes.
Evaluate marketing effectiveness
Compare traffic sources, campaigns, devices, and new versus repeat visitors by volume and conversion outcomes.
Improve product decisions
Identify the products that drive units sold, revenue, gross profit, and refund risk.
Optimise the customer journey
Compare landing pages by traffic, bounce rate, conversion rate, order volume, and revenue.
Data Source
Maven Analytics — Toy Store E-Commerce Database
The project uses the Maven Fuzzy Factory sample dataset provided by Maven Analytics. It is a multi-table e-commerce dataset containing website session and pageview activity, orders, products, order items, and refunds.
Data Description
This is a relational e-commerce dataset that follows the customer journey from website acquisition to post-purchase refunds. It contains 1,735,070 data records across six business tables (excluding CSV headers). The data can be used to connect marketing activity and on-site behaviour to orders, product sales, margin, and refund outcomes.
A visitor starts in website_sessions, may view one or more pages in website_pageviews, and may place an order recorded in orders. That order can contain one or more records in order_items; each item is linked to a product. If an item is refunded, the event is captured in order_item_refunds. This structure enables session-to-order conversion, product profitability, marketing-channel, and landing-page analysis.
| Table | Records | Grain | What the table is about |
|---|---|---|---|
| website_sessions | 472,871 | One row per website visit | The traffic-acquisition table. It records when a visitor started a session, whether the person had visited before, the marketing source/campaign/content, device type, and referring URL. website_session_id links each session to pageviews and any resulting order. |
| website_pageviews | 1,188,124 | One row per page viewed | The on-site browsing table. Each record represents one URL viewed during a session. Ordering pageviews by timestamp identifies the landing page; counting pageviews per session supports bounce-rate analysis, where a one-page session is treated as a bounce. |
| orders | 32,313 | One row per order | The order-level transaction table. It records the buying session and user, primary product, number of items purchased, total order revenue (price_usd), and cost of goods sold (cogs_usd). It is used to calculate revenue, gross profit, average order value, and session-to-order conversion rate. |
| order_items | 40,025 | One row per item in an order | The line-item sales table. It breaks each order into individual products, including the product identifier, primary-item flag, selling price, and cost. It enables product-level units sold, revenue, and gross-profit analysis. |
| products | 4 | One row per product | The product reference table. It provides the product name and product launch timestamp used to make order-item analysis understandable and to compare performance across the four toy products. |
| order_item_refunds | 1,731 | One row per refunded order item | The post-purchase refund table. It records refunded item and order identifiers, the refund date, and the refund amount. Joining it to order items enables refund count, refund value, and product-level refund-rate analysis. |
SQL Engineering & Analytics Workflow
PostgreSQL was used as the project’s data-engineering and analytical layer. Instead of analysing disconnected CSV files, the source data was structured into a reusable database, quality-checked, and transformed into reporting-ready views. This made the Power BI layer simpler and ensured that the dashboard measures were based on defined, repeatable SQL logic.
Database structure and type design
A dedicated maven_fuzzy_factory database was created with separate raw and analytics schemas. The raw schema contains the six imported source tables; the analytics schema contains reusable summary views for dashboard consumption.
- Defined primary keys for all source entities, including sessions, pageviews, orders, order items, products, and refunds.
- Assigned appropriate PostgreSQL types: INTEGER for identifiers and flags, TIMESTAMP for event times, NUMERIC(10,2) for monetary values, and TEXT for descriptive attributes.
- Separated raw ingestion data from analytical outputs to preserve source fidelity while supporting clean reporting logic.
Data quality validation
Before building metrics, SQL validation queries confirmed successful ingestion and checked that the relational model was usable for analysis. Validation included row counts, date boundaries, key-field null checks, and relationship checks using LEFT JOIN anti-joins.
- Confirmed the expected record volume for each imported table.
- Checked minimum and maximum created_at timestamps to verify chronological coverage.
- Tested essential session and order fields for missing identifiers, dates, users, revenue, and cost values.
- Verified that orders map to sessions, pageviews map to sessions, order items map to orders and products, and refunds map to order items. The completed checks returned no unmatched records.
Validation evidence and results
Query outputs were saved in sql/sql_validation_step_outputs to document the checks performed and preserve evidence of the final data-quality status before analytical views were created.
| Validation check | Saved SQL output | Result |
|---|---|---|
| Raw-table row counts | 3_1_row_counts.csv | Imported 472,871 website sessions, 1,188,124 pageviews, 32,313 orders, 40,025 order items, 1,731 refunds, and 4 products. |
| Event-date range | 3_2_date_range_check.csv | Sessions and pageviews run from 19 March 2012 to 19 March 2015; orders and order items use the same transaction range. Refund events extend to 1 April 2015, which is consistent with refunds occurring after purchase. |
| Key-field completeness | 3_3_missing_check_1.csv 3_3_missing_check_2.csv |
0 missing values across the tested website-session fields (session ID, event timestamp, user ID) and order fields (order ID, timestamp, session ID, price, and cost of goods sold). |
| Session relationship integrity | 4_table_relationship_check_1.csv 4_table_relationship_check_2.csv |
0 orders without a matching website session and 0 pageviews without a matching website session. |
| Transaction relationship integrity | 4_table_relationship_check_3.csv 4_table_relationship_check_4.csv 4_table_relationship_check_5.csv |
0 order items without matching orders, 0 order items without matching products, and 0 refunds without matching order items. |
Analytics Views Created for Reporting
Four PostgreSQL views were created and exported as CSV datasets for Power BI. Each view applies the correct join logic, granularity, and business definitions before visualisation.
| Analytics view | Reporting grain | SQL logic and business output |
|---|---|---|
| monthly_performance | One row per calendar month | Uses DATE_TRUNC('month') to aggregate session and order activity into monthly sessions, orders, revenue, cost of goods sold, gross profit, conversion rate, average order value, and revenue per session. |
| marketing_performance | One row per source, campaign, content, device, and repeat-session combination | Analyses channel performance through UTM attributes, device type, and repeat status. Uses COALESCE to make missing attribution fields reportable as direct or none, and calculates sessions, orders, revenue, gross profit, conversion rate, and revenue per session. |
| product_performance | One row per product | Joins product, order-item, and refund data to calculate units sold, revenue, cost, gross profit, refunded items, refund amount, and refund rate for each product. |
| landing_page_performance | One row per landing-page URL | Uses CTEs and the ROW_NUMBER() window function to identify the first page in each session. A second CTE counts pageviews per session, allowing one-page sessions to be calculated as bounces. The view returns sessions, orders, bounced sessions, bounce rate, conversion rate, and revenue. |
How SQL Views Were Prepared for Power BI
The Power BI report uses four exported CSV files generated from the PostgreSQL analytics views. Each view was designed at the same level of detail required by its dashboard page. This avoids duplicating complex joins in Power BI and ensures the visualisations use consistent metric definitions.
- Join the raw tables: session, order, item, product, pageview, and refund tables were joined only where needed for the business question.
- Aggregate at the visual level: SQL groups data by month, marketing attributes, product, or landing page before it is exported.
- Calculate metrics once: revenue, gross profit, conversion rate, revenue per session, refund rate, and bounce rate are defined in the view logic.
- Export reporting datasets: the four resulting views were exported to powerbi/exported_views_from_sql and loaded as standalone reporting tables in Power BI.
| Exported SQL view | Power BI dashboard use | Detailed output produced |
|---|---|---|
| monthly_performance.csv | Executive Overview | 37 monthly records, covering March 2012 through March 2015. Each row contains sessions, orders, revenue, cogs, gross_profit, conversion_rate, average_order_value, and revenue_per_session. Across the full period, the output totals 472,871 sessions, 32,313 orders, $1,938,509.75 revenue, and $1,216,139.50 gross profit; overall conversion is 6.83%. This output is used for KPI cards and monthly trend charts. |
| marketing_performance.csv | Marketing Performance | 19 acquisition cohorts, grouped by UTM source, campaign, content, device type, and repeat-session status. The output supplies sessions, orders, revenue, cogs, gross_profit, conversion_rate, and revenue_per_session for channel-level visuals. The largest cohort is gsearch / nonbrand / g_ad_1 / desktop, with 195,155 sessions, 16,037 orders, and $956,016.90 revenue. The highest conversion rate is 11.03% for repeat desktop visitors from gsearch / brand / g_ad_2. The extract also retains an unattributed NULL segment for sessions without UTM values. |
| product_performance.csv | Product Performance | 4 product records, with units_sold, revenue, cogs, gross_profit, refunded_items, refund_amount, and refund_rate. The output reports 40,025 units sold, 1,731 refunded items, and $85,338.69 in refunds. The Original Mr. Fuzzy is the largest product, producing $1,211,057.74 revenue and $738,893.00 gross profit. The Birthday Sugar Panda has the highest product refund rate at 6.04%. |
| landing_page_performance.csv | Landing Page Performance | 6 landing-page records, each containing sessions, orders, bounced_sessions, bounce_rate, conversion_rate, and revenue. The view identifies 211,640 bounced sessions, equivalent to a 44.76% overall bounce rate. /lander-5 is the strongest conversion page at 10.17% with the lowest bounce rate of 36.87%; /lander-3 requires attention with a 50.29% bounce rate and 3.39% conversion rate. This output supports bar, column, table, and bounce-versus-conversion bubble visuals. |
Power BI Dashboard: Visual Analysis & Interpretation
The Power BI dashboard contains four pages built from the exported SQL view outputs. KPI cards provide the headline result; charts then explain how performance varies over time, by marketing channel, by product, and by landing page. The interpretations below are based on the values in the exported view datasets and the dashboard screenshots.
1. Executive Overview
The overview page reports $1.94M total revenue, $1.22M gross profit, 32.3K orders, 472.9K sessions, a 6.83% conversion rate, and a $59.99 average order value. Gross profit represents approximately 62.7% of revenue before refund adjustments.
- Growth trajectory: monthly revenue rose from $3.0K in March 2012 to a peak of $144.8K in December 2014, alongside the highest monthly traffic volume of 29,722 sessions and 2,314 orders.
- Conversion improvement: the monthly conversion rate increased from 3.19% at the beginning of the dataset to a high of 8.69% in February 2015. This indicates that business growth was supported by improved purchase efficiency, not traffic growth alone.
- Latest-period caution: March 2015 contains data only through 19 March, so its lower totals should not be interpreted as a full-month decline.
2. Marketing Performance
The page evaluates the same full-period total of $1.94M revenue, 472.9K sessions, and 32.3K orders, then divides those results into 19 marketing cohorts defined by source, campaign, content, device type, and repeat-session status.
- Primary revenue engine: gsearch generated $1.28M revenue from 316,035 sessions and 21,333 orders—approximately 66% of all revenue. Its non-brand desktop campaign alone contributed $956.0K revenue and 16,037 orders.
- Campaign priority: non-brand campaigns drive the largest order volume, led by the gsearch / nonbrand / g_ad_1 / desktop cohort. This is the strongest acquisition segment by scale.
- Device gap: when calculated as weighted rates, desktop visitors converted at about 8.50% (27,805 orders / 327,027 sessions), versus 3.09% for mobile visitors (4,508 / 145,844). This is a material mobile conversion opportunity.
- High-intent visitors: repeat desktop visitors from gsearch / brand / g_ad_2 achieved the strongest cohort conversion rate at 11.03%.
3. Product Performance
The product page summarises 40,025 units sold, $1.94M revenue, $1.22M gross profit, and $85.3K refunded value across four products. It makes clear that the product portfolio is concentrated in one flagship product.
- Flagship concentration: The Original Mr. Fuzzy sold 24,226 units and generated $1.21M revenue and $738.9K gross profit—about 63% of total revenue. It is the business’s largest commercial dependency.
- Best secondary product: The Forever Love Bear generated $347.7K revenue and $217.4K gross profit with a relatively low 2.23% refund rate.
- Refund-risk priority: The Birthday Sugar Panda has the highest refund rate at 6.04% and $13.8K in refunds. Product quality, product-page expectations, or fulfilment experience should be investigated before scaling demand.
- Lowest refund risk: The Hudson River Mini bear has the lowest refund rate at 1.28%, although it also delivers the smallest revenue contribution at $150.5K.
The screenshot contains two refund-rate charts with the same measure. One can be treated as a repeated view of the same risk result; it does not provide an additional independent product metric.
4. Landing Page Performance
The landing-page page compares six entry pages. The bubble chart is particularly useful because it combines bounce rate on the horizontal axis, conversion rate on the vertical axis, and session volume as bubble size. Strong pages appear toward the upper-left: low bounce rate and high conversion rate.
- Best page to learn from: /lander-5 has the highest conversion rate at 10.17%, the lowest bounce rate at 36.87%, and generated $438.5K revenue from 68,166 sessions. Its design and offer should be evaluated as a potential template.
- Largest revenue contributors: /lander-2 produced the most revenue at $594.8K from 131,170 sessions, narrowly ahead of /home at $585.3K from 137,576 sessions.
- Primary optimisation target: /lander-3 received a substantial 79,000 sessions but converted only 3.39% with a 50.29% bounce rate. Because of its large traffic volume, improving this page offers the largest conversion upside.
- High bounce warning: /lander-1 has the highest bounce rate at 53.24% and converts at only 4.53%. Its message match, page speed, user experience, and call-to-action should be reviewed.