##About This Block
Nearly all businesses want customers to come back. It’s often much easier to increase your profitability by selling more to your existing customers than to acquire new customers. An interesting field to create for this is ORDERS Order Sequence Number.
ORDERS means its an attribute of an order. Order Sequence Number means that we are computing an index for this order relative to the other orders a user has placed. For a buyer, their first order will be numbered ‘1’, their second order will be numbered ‘2’, and so on.
Sequencing Transactions is an excellent foundation to build other analytical patterns on. Once you have ordered transaction, you gain insights into new facets of customer behavior, and answer questions such as (among others):
##Ideal Data Types
This Block works great a large portion of companies in the e-commerce, consumer goods, marketing, healthcare, finance, and gaming spaces, among other verticals. The logic can be applied to any usage or transactional data where actions can be mapped to users. Sample usage and transactional data includes orders, order items, sessions, visits, postings, donations, events, etc…
Here’s an example of insights you can gather once you’ve sequenced transactions. This table shows us how many of our orders are a customers’ first, second, third (and so on). We can use this information to form the basis of business decisions. Do we want to incentivize repeat orders? By digging into users who have ordered at least 3 times, what can we learn about how to encourage high numbers of orders?
Please note that this data is fictitious, and may not be representative of standard metric levels.
###Orders by Order Sequence Number, the last 10 days.
###Breakdown of Orders by Order Sequence Number (Up to 10 Orders)
As we see in this case, the majority of purchases are from first-time orders. Ideally we’d like to see higher values for higher numbers, which would indicate that customers are satisfied and coming back to buy from you again.
There are two samples of code at the bottom of this article. The first utilizes a correlated subquery to count the number of orders by this user, before the current order. In order for this to work you will need an index on orders.user_id
. This pattern is compatible MySQL and SQL Server. The second example utilizes a window function. Window functions are compatible with PL/pgSQL (PostgreSQL, Redshift, Greenplum), Microsoft SQL Server (2012+), Vertica, among others.
###Correlated Subquery
First, create a derived table with two columns:
order_id, squence_number
Then, for each order in the orders table, create a row in the order_user_sequence table. The index the table and join it into orders.
- connection: thelook ## ignore
- scoping: true ## ignore
- explore: orders
hidden: true ## ignore
- view: orders
fields:
- dimension: id
type: int
primary_key: true
sql: ${TABLE}.id
- dimension: user_order_sequence_number
type: number
sql: |
(
SELECT COUNT(*)
FROM orders o
WHERE o.id <= ${TABLE}.id
AND o.user_id = ${TABLE}.user_id
)
- measure: count
type: count
drill_fields: [id, created_time]
Window Functions are recommended for large tables.
- explore: orders
joins:
- join: order_user_sequence
foreign_key: id
- view: orders
fields:
- dimension: id
type: int
primary_key: true
sql: ${TABLE}.id
- measure: count
type: count
detail: [id, created_time]
# Compute the order's sequence over the users lifetime. Is this the first, second, third, etc.
- view: order_user_sequence
derived_table:
sql: |
SELECT
id AS order_id
, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at) as user_order_sequence_number
FROM orders
fields:
- dimension: order_id
primary_key: true
sql: ${TABLE}.order_id
- dimension: user_order_sequence_number
type: int
sql: ${TABLE}.user_order_sequence_number
Hi,
I received error message ‘PSQLException: ERROR: This type of correlated subquery pattern is not supported yet’.
Is there a workaround?
Thanks
@Vaite, if your SQL dialect doesn’t support coorelated sub queries, you can compute sequence nubmers with a window function instead. Here is a writeup on how to do it.
Thanks, I’ll do that!
Also, for larger tables it is not recommended to use correlated subqueries. Window functions (in Lloyd’s link) are preferred.