The data for this blog was taken from Maven Analytics data playground Free Practice & Free Sample Dataset Download – Toy Store E-Commerce Database – Maven Analytics | Build Data Skills, Faster toy store e-commerce database. Thanks to Maven Analytics for publishing datasets.

Four questions were presented:

• What is the trend in website sessions and order volume?
• What is the session-to-order conversion rate? How has it trended?
• Which marketing channels have been most successful?
• How has the revenue per order evolved? What about revenue per session?

I will answer those questions in this blog based on the sample data.

First, we need to get familiar with the tables.

The orders table includes the order id, website session, user id, primary product in case of bundle order, items purchased, total price of the order and cogs.

In order item table we have item level information about the order like price and cogs.

Refunds related to orders are stored in order item refund table. There are order item id and refund amount.

Product table includes four records of product id and product name.  

I have downloaded the data to Access.

In addition to this model, I created a date table.

In addition to this model, I created a date table.

I created the table in Excel. Excel was counting year, quarter, month, weekday and year quarter combination.

The datamodel looks like this.

Relations in the datamodel.

What is the trend in website sessions and order volume?

Number of websessions is increasing quarterly apart from the last reported quarter.

Order volume measured by money has increased.  Only 2015/Q1 was lower than previous quarter.

• What is the session-to-order conversion rate? How has it trended?

I understand this question so that how many sessions turn to order. If a customer is browsing the webpage, how often does the customer buy something.

This is the rate how many websessions are led to an order.

Sessions_to_order

=count(Orders[order_id])/count(Website_sessions[website_session_id])

How has the revenue per order evolved? What about revenue per session?

Number of websessions is increasing apart from the Q1 in 2015.

Session to order rate is steadily increasing apart from the last reported quarter, Q1/15.

• Which marketing channels have been most successful?

I understand market channel as campaigns in website_sessions table.

When calculating the success, I count the sales minus cogs minus refunds. Then we know the total success.

=SUM ( Orders[price_usd] )

– SUM ( Orders[cogs_usd] )

– SUM ( order_item_refunds[refund_amount_usd] )

Nonbrand has the highest sales, pilot the lowest.

The table above is calculated with DAX below:

=AVERAGEX (

Orders;

Orders[price_usd]

– Orders[cogs_usd]

– CALCULATE (

SUM ( order_item_refunds[refund_amount_usd] );

FILTER ( order_item_refunds; order_item_refunds[order_id] = Orders[order_id] )

)

)

We take the price of the order minus costs minus refunds and then we count the average.

Here we have divided the net sales with number of websessions.

The DAXes are as follows:

Sales

=SUM ( Orders[price_usd] )

– SUM ( Orders[cogs_usd] )

– SUM ( order_item_refunds[refund_amount_usd] )

CWeb

=count(Website_sessions[website_session_id])

S_CWeb

=DIVIDE([Sales];[CWeb])

The average sales per websession has steadily increased.

• What is the trend in website sessions and order volume?

Number of website sessions and order volume have increased steadily quarterly apart from the last reported quarter.

• What is the session-to-order conversion rate? How has it trended?

Session to order rate has increased steadily. Average is about 0,07, 7 % of all the sessions turn into an order.

• Which marketing channels have been most successful?

I understood market channel as campaigns. Nonbrand is the best campaign.

• How has the revenue per order evolved? What about revenue per session?

Both revenue per order and revenue per session have increased.