Data Analysis | Use SeaTable to quickly analyze order data

SeaTable is a collaborative table and information management tool that supports multiple data types. It is not only convenient to record a large amount of structured data, but also provides a variety of data processing, analysis and visualization functions. This article will introduce how to comprehensively use SeaTable's SQL plug-in, data link function, and function calculation function to analyze the annual repurchase ratio and user retention ratio from a piece of online order data.

Through this article, we will see that compared to Excel, SeaTable has the following advantages in data analysis:

• Support SQL query, can complete relatively complex data query.
• Supports expansion table association of data. In some scenarios, data analysis can only be completed by expanding the table association, for example, the order table needs to be associated with the user table.
• It can handle records of 100,000 rows or even more than a million rows.

Compared with traditional databases, SeaTable is more intuitive and easy to use:

• The intermediate results can be directly exported as a new table through the interface, and then analyzed.
• You can directly create a formula column, calculate and process the data, and then analyze it further.
• You can clean up or modify some unreasonable data directly through the interface.

Data set introduction

As shown in the figure below, this is an online order data sheet for software products, which records the detailed information of all orders, such as ID, user name, payment amount, creation time (first payment time), and so on. The software is sold in the form of annual payment, and users need to renew once a year in order to continue to use the software. Price records the price in US dollars.

After we add the SQL plug-in and place it on the toolbar, we can quickly query the data.

First, we want to query the quantity of all orders, then the SQL statement is as follows:

select count(*) from Orders

count(*) represents the count of the number of all items, the result is 1855, which means that the total number of orders so far is 1855.

Next, we want to see how many different users these orders come from, then the statement is as follows:

select count(distinct User) from Orders limit 1000

Same as above, using the count function, distinct means deduplication, and the same value is only retained once. The result is 598, which means that there are 598 users who have purchased in total.

So, among so many users, we want to see who are the top ten users with the strongest paying ability in 2020, we can write the following statement:

select User, sum(Paid) from Orders where `Created At` > '2020-01-01' and `Created At` <= '2021-01-01' group by User order by sum(Paid) desc limit 10

We limit the payment time to 2020, sum the user's payment amount, and display ten records in descending order of the paid amount.

Use the ISOMonth function to analyze this month and last month's data

The date in SeaTable is generally saved in the ISO international standard format. It provides two functions, ISODate and ISOMonth, to help us perform statistical analysis on data according to date.

When we want to check the payment amount for this month, we can use the time function ISOMonth to filter the month.

To calculate the payment amount this month, the statement is as follows:

select sum(Paid) from Orders where ISOMonth(`Created At`) = ISOMonth(today())

To calculate the payment amount last month, the statement is as follows:

select sum(`Paid`)  from Orders where ISOMONTH(`Created At`) = ISOMONTH(dateAdd(today(), -1, "months"))

Analyze the renewal ratio of users who purchased for the first time in 2019

The data in the order table is recorded in units of orders. To analyze the renewal ratio of users, we first need to get a table in units of users.

We can use the following group by statement to get the first purchase time and last purchase time of each user. After querying the results, export them as a new sub-table—user purchase schedule.

select User, min(`Created At`), max(`Created At`) from Orders group by User limit 2000

Next, create two formula columns {First year} and {Last year} to find the year of first purchase and the year of last purchase.

In this way, we can make a query, and first obtain that the number of new purchasers in 2019 is 110.

select count(*) from Users where `First year`=2019

Then you can find 65 people whose first purchase time was in 2019 and the last purchase time was after 2019. From this we conclude that the renewal ratio of new users in 2019 is 59.0%.

select count(*) from Users where `First year`=2019 and `Last year`>2019

Analyze the proportion of repurchase amount in 2020

Let's make a more complicated analysis, that is, the proportion of repurchase amount in 2020. We use the "automatic association" of data processing to establish the association relationship between the order table and the user table obtained above. For each order in 2020, if the user's first purchase is earlier than 2020, then we consider this to be a repurchase.

Therefore, first of all, in the user table we have obtained, we use the link column and automatically add link function to establish an association between the Orders table and the user's purchase schedule, so as to facilitate the reference of the {First year} column to the Orders table for calculation.

After establishing the association, we will use the link formula to reference the {First year} column to the Orders table, and then create two new formula columns,

Use the date formula to calculate {Created year}-purchase year
Mark the users who repurchase: When {Created year} is greater than {First year}, then we consider it to be a repurchase situation and display as 1, otherwise display as 0

Next, we can query. First, query the total amount of orders in 2020, which is 106627.5.

Then we query the amount of repurchase orders in 2020, which is 89915.5. Therefore, the repurchase amount in 2020 will account for 84.3%.

Above, we use SeaTable easily and quickly complete the analysis of online order data, is it very convenient? SeaTable is not only rich in functions, but also very easy to use. You can flexibly build your own business applications according to specific scenarios.