Automate order KPIs, customer analysis, and inventory risk assessment using SQL

SQL 100 Exercises Chapter 8 (No.071–No.080): Practical Data Analysis of SQL

[!NOTE] This material is a notebook previously used by Sukari Kobo (or personally by the representative, Kazuyama), and has been restructured, edited, and published with the company’s permission. All data listed is fictional and has no relation whatsoever to real companies, factories, or figures.

Introduction: Practical Challenges in Manufacturing Covered in This Article

In manufacturing, sales, sales, and inventory management, the following questions arise every month.

• “How are the number of orders and sales this month? Compared to the previous month and year-on-year?”
• “Is the average order amount (average order value per customer) per client reasonable?”
• “Which customers are you placing the most orders with?”
• “How many new clients did you do business with for the first time this year?”
• “What percentage of customers have placed orders more than twice (repeat purchase rate)?”
• “Which customer did you place the last order with a long time ago? (Dormant customer)”
• “I want to distinguish VIP customers from dormant customers through RFM analysis.”
• “I want to compare inventory with shipment performance over the past three months to check for shortages or surpluses.”
• “Want to detect products at risk of out-of-stock early”
• “I want to automatically detect products with abnormal fluctuations in orders.”

In this chapter, we will put these Most frequently used in practice SQL pattern into practice with manufacturing order and inventory data.

Table	Overview	number of cases
customers	Client Master (Manufacturing B2B Clients)	20 companies
products	Product Master	10 Items
orders	Order History (January–December 2024)	About 620 items
inventory	Inventory Table (as of the end of December 2024)	10 Items

Common situations on site

Imagine the tasks a sales manager in a mid-sized manufacturing company does at the end of each month.

1. Open multiple Excels and manually create copy & paste → Monthly Sales Report
2. Visually check whether this client placed an order last month as well→ Identifying repeat customers
3. Checking inventory shortage risks relies on the instincts of the person in charge→ Inventory alerts
4. Sudden sales spikes or decreases are often noticed afterward→ anomaly detection

All of these can be automated with SQL.
You can Reduce monthly report creation time from hours to minutes through regular batches + BI tool integration.

Why is this issue so difficult to judge?

The main reason why order and customer data analysis in manufacturing is difficult is “There are multiple axes of comparison.”.

$$\text{Order KPI} = f(\text{Time Series}, \text{Clients}, \text{Products}, \text{Inventory})$$

comparative axis	difficulty	Corresponding exercise
Time Series (Monthly Transition)	Influence of seasonality and external factors	No.071, 080
By customer (who is buying what)	Large customer base, making manual aggregation difficult	No.072, 073
Customer status changes (new/continuous/dormant)	Combining time axis and customer axis	No.074, 075, 076
Customer Overall Rating (RFM)	Simultaneous Implementation of 3-Dimensional Evaluation	No.077
Inventory and demand matching	Consideration of production planning and order lead times	No.078, 079

Additionally, multi-axis evaluations like RFM analysis are difficult to manage in Excel, and combining them with SQL + BI tools is standard in practice.

Overview of Exercise covered this time

No.	Theme	SQL Features	Applications in Manufacturing
071	Aggregate sales KPIs	GROUP BY + Aggregate Function	Automatic Generation of Monthly Order Summaries
072	Calculate the average order value	AVG, SUM / COUNT	Calculation of Average Order Amount by Customer
073	Aggregate purchase frequency by customer	GROUP BY + ORDER BY	Identifying VIP Customers and Heavy Users
074	Classify new and existing customers	CASE + DATE Comparison	Developing new customers vs. deepening existing ones
075	Calculating the repeat rate	Aggregation + Conditional Calculation	Setting Order Retention Rate as a KPI
076	Calculate the last purchase date	MAX(date) + date difference	Extracting dormant customers and taking sales actions
077	Creating data for RFM analysis	CTE + CASE WHEN	Automatic customer segment classification
078	Compare inventory and sales numbers	LEFT JOIN + Aggregate	Quantitative Tracking of Inventory Overage and Shortage
079	Extracting products at risk of out-of-stock	CTE + CASE WHEN	Automatic generation of replenishment order alerts
080	Extract products with abnormally high or low sales	CTE + LAG Window Function	Automatic detection of order anomalies

Preparing the Python environment

import subprocess, sys

res = subprocess.run(["sw_vers", "-productVersion"], capture_output=True, text=True)
print(f"macOS : {res.stdout.strip()}")
print(f"Python: {sys.version}")

import sqlite3
from datetime import date, timedelta
import numpy as np
import polars as pl
import matplotlib
import matplotlib.pyplot as plt

matplotlib.rcParams['font.family'] = 'Hiragino Maru Gothic Pro'
%config InlineBackend.figure_format = 'svg'
np.random.seed(42)


def q(conn, sql):
    '''SQL Run Polars DataFrame Display results'''
    print('── SQL ─────────────────────────────────────────')
    for line in sql.strip().split('\n'):
        print(f'  {line}')
    print('───────────────────────────────────────────────')
    cur = conn.execute(sql.strip())
    rows = cur.fetchall()
    cols = [d[0] for d in cur.description]
    data = {col: [row[i] for row in rows] for i, col in enumerate(cols)}
    df = pl.DataFrame(data)
    print(df)
    print(f'↳ {len(rows)} Acquisition of Banking')
    return df


print(f"sqlite3   : {sqlite3.sqlite_version}")
print(f"polars    : {pl.__version__}")
print(f"numpy     : {np.__version__}")
print(f"matplotlib: {matplotlib.__version__}")
print()
print("Library loading complete")

Creation of Fictional Data

conn = sqlite3.connect(':memory:')

# ── customers ─────────────────────────────────────────────
conn.execute('''
CREATE TABLE customers (
    customer_id         TEXT PRIMARY KEY,
    customer_name       TEXT,
    region              TEXT,
    first_contract_date TEXT
)
''')
CUST_DATA = [
    ('C001', 'Yamada Machinery Works',       'Kanto', '2022-03-15'),
    ('C002', 'Tanaka Steel Co., Ltd.',     'Kansai', '2021-07-20'),
    ('C003', 'Suzuki Precision Industry',         'East Sea', '2022-11-05'),
    ('C004', 'Takahashi Engineering', 'Kanto', '2023-04-18'),
    ('C005', 'Ito Kogyo Co., Ltd.',     'Kansai', '2021-09-30'),
    ('C006', 'Watanabe Machinery Industry',         'Kyushu', '2023-01-10'),
    ('C007', 'Nakamura Manufacturing',           'East Sea', '2022-06-22'),
    ('C008', 'Kobayashi Seiki',             'Kanto', '2024-02-15'),
    ('C009', 'Kato Heavy Industries',           'Kansai', '2021-12-01'),
    ('C010', 'Yoshida Mechatronics',   'Kanto', '2024-04-08'),
    ('C011', 'Yamamoto Industry',             'East Sea', '2022-08-14'),
    ('C012', 'Matsumoto Manufacturing',           'Kyushu', '2023-02-28'),
    ('C013', 'Sato Machinery and Electronics Industry',         'Kanto', '2024-07-03'),
    ('C014', 'Shimizu Seiko',             'Kansai', '2021-05-16'),
    ('C015', 'Ichikawa Kogyo Co., Ltd.',     'East Sea', '2022-10-19'),
    ('C016', 'Kimura Manufacturing',           'Kyushu', '2023-06-30'),
    ('C017', 'Hashimoto Machinery',             'Kanto', '2024-01-22'),
    ('C018', 'Ishikawa Industry',             'Kansai', '2021-11-08'),
    ('C019', 'Arakawa Seiki',             'East Sea', '2022-04-25'),
    ('C020', 'Fujita Manufacturing',           'Kyushu', '2023-09-12'),
]
conn.executemany('INSERT INTO customers VALUES (?,?,?,?)', CUST_DATA)

# ── products ──────────────────────────────────────────────
conn.execute('''
CREATE TABLE products (
    product_id     TEXT PRIMARY KEY,
    product_name   TEXT,
    category       TEXT,
    standard_price INTEGER,
    lead_time_days INTEGER
)
''')
PROD_DATA = [
    ('P001', 'Precision Bearings',     'Machine parts', 2800,  7),
    ('P002', 'hydraulic cylinder',     'Machine parts', 15000, 10),
    ('P003', 'motor coil',     'electrical components', 4500,   5),
    ('P004', 'Control board',           'electrical components', 28000, 14),
    ('P005', 'Industrial Rubber Packing', 'consumables',    350,   3),
    ('P006', 'Stainless steel flange', 'Machine parts',  6800,  7),
    ('P007', 'three-phase motor',       'electrical components', 35000, 14),
    ('P008', 'heat-resistant gasket',     'consumables',    850,   3),
    ('P009', 'rolling bearing',         'Machine parts',  3200,  5),
    ('P010', 'electromagnetic valve',         'electrical components', 12000, 10),
]
conn.executemany('INSERT INTO products VALUES (?,?,?,?,?)', PROD_DATA)
PROD_DICT = {p[0]: p[3] for p in PROD_DATA}

# ── orders ────────────────────────────────────────────────
conn.execute('''
CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    order_date TEXT,
    customer_id TEXT,
    product_id  TEXT,
    quantity    INTEGER,
    unit_price  INTEGER
)
''')

# Standard Order Quantity for Each Product
BASE_QTY = {'P001':80,'P002':8,'P003':40,'P004':3,'P005':400,
            'P006':15,'P007':2,'P008':150,'P009':60,'P010':8}

# Start Month of New Customers (within 2024)
NEW_START = {'C008':2, 'C010':4, 'C013':7, 'C017':1}

CUST_IDS = [f'C{i:03d}' for i in range(1, 21)]
PROD_IDS = [f'P{i:03d}' for i in range(1, 11)]

CUST_W = np.array([3.0,2.5,2.0,1.5,2.0,1.0,1.8,0.8,2.2,1.2,
                   1.5,0.7,0.5,1.8,1.2,0.8,1.0,2.0,1.4,0.9])
PROD_W = np.array([3.0,2.0,2.5,1.0,4.0,2.0,0.8,3.5,2.5,1.5])
MONTH_FAC = {1:0.85,2:0.80,3:1.10,4:0.95,5:0.90,6:1.05,
             7:0.85,8:0.75,9:1.00,10:1.05,11:1.10,12:1.20}

orders_rows = []
oid = 1

# Basic orders from all major clients (number of orders proportional to each company's weight)
for ci, cid in enumerate(CUST_IDS):
    sm = NEW_START.get(cid, 1)
    n = max(int(CUST_W[ci] * 8), 3)
    for _ in range(n):
        month = np.random.randint(sm, 13)
        day   = np.random.randint(1, 28)
        od    = date(2024, month, day)
        pi    = np.random.choice(10, p=PROD_W / PROD_W.sum())
        pid   = PROD_IDS[pi]
        qty   = max(int(BASE_QTY[pid] * np.random.uniform(0.5, 2.0)), 1)
        up    = int(PROD_DICT[pid] * np.random.uniform(0.92, 1.08))
        orders_rows.append((oid, str(od), cid, pid, qty, up))
        oid += 1

# Additional random orders (with monthly seasonal fluctuations)
for _ in range(400):
    ci  = np.random.choice(20, p=CUST_W / CUST_W.sum())
    cid = CUST_IDS[ci]
    sm  = NEW_START.get(cid, 1)
    month = np.random.randint(sm, 13)
    fac   = MONTH_FAC[month]
    if np.random.rand() > fac:
        continue  # High order probability during busy periods
    day = np.random.randint(1, 28)
    od  = date(2024, month, day)
    pi  = np.random.choice(10, p=PROD_W / PROD_W.sum())
    pid = PROD_IDS[pi]
    qty = max(int(BASE_QTY[pid] * np.random.uniform(0.5, 2.0)), 1)
    up  = int(PROD_DICT[pid] * np.random.uniform(0.92, 1.08))
    orders_rows.append((oid, str(od), cid, pid, qty, up))
    oid += 1

conn.executemany('INSERT INTO orders VALUES (?,?,?,?,?,?)', orders_rows)

# ── inventory ──────────────────────────────────────────────
conn.execute('''
CREATE TABLE inventory (
    product_id    TEXT PRIMARY KEY,
    stock_qty     INTEGER,
    safety_stock  INTEGER,
    last_updated  TEXT
)
''')
INV_DATA = [
    ('P001', 850,  300, '2024-12-31'),
    ('P002', 120,  150, '2024-12-31'),
    ('P003', 380,  200, '2024-12-31'),
    ('P004',  25,   40, '2024-12-31'),
    ('P005',5000, 1500, '2024-12-31'),
    ('P006', 280,  150, '2024-12-31'),
    ('P007',   8,   15, '2024-12-31'),
    ('P008',3200, 1000, '2024-12-31'),
    ('P009', 450,  300, '2024-12-31'),
    ('P010',  65,   80, '2024-12-31'),
]
conn.executemany('INSERT INTO inventory VALUES (?,?,?,?)', INV_DATA)
conn.commit()

print(f"customers: {len(CUST_DATA)} records")
print(f"products : {len(PROD_DATA)} records")
print(f"orders   : {len(orders_rows)} records")
print(f"inventory: {len(INV_DATA)} records")

for tbl in ['customers','products','orders','inventory']:
    n = conn.execute(f'SELECT COUNT(*) FROM {tbl}').fetchone()[0]
    print(f'{tbl:12s}: {n:5d} records')
print()
q(conn, 'SELECT * FROM products')

No.071: Aggregating Sales KPIs

Meaning in Practice

Monthly Order Summary (Number of Cases, Amount, Quantity) are the fundamental KPIs for sales management in manufacturing.
If monthly calculations can be automatically calculated, you can immediately support management meetings, budget management, and month-on-month analysis.

Approach to Analysis and Modeling

For monthly aggregation, extract the year and month by strftime('%Y-%m', order_date) and aggregate by GROUP BY.

$$\text{monthly sales} = \sum_{i} \text{quantity}_i \times \text{unit\_price}_i$$

Definition of Key KPIs:

KPI	SQL Representation	Meaning
Number of orders received	COUNT(order_id)	How many orders were placed each month
Sales amount	SUM(quantity * unit_price)	Total Order Amount for Month
Average Price per Customer	SUM(amount) / COUNT(DISTINCT customer_id)	Average monthly amount per company
Average Order Amount	SUM(amount) / COUNT(order_id)	Average amount per order

Check with Python

print("=== No.071 salesKPIAggregate ===\n")

# (1) Monthly Order KPI
print("① Monthly orders KPI summary")
df71 = q(conn, '''
SELECT
  strftime('%Y-%m', order_date)           AS ym,
  COUNT(order_id)                         AS order_count,
  COUNT(DISTINCT customer_id)             AS unique_customers,
  SUM(quantity * unit_price)              AS total_revenue,
  ROUND(SUM(quantity * unit_price) * 1.0
        / COUNT(order_id), 0)             AS avg_order_value
FROM orders
GROUP BY ym
ORDER BY ym
''')

# (2) Visualization: Monthly sales + number of orders (two-axis graph)
YMS     = df71['ym'].to_list()
REV     = [v / 1_000_000 for v in df71['total_revenue'].to_list()]  # million yen
ORDERS  = df71['order_count'].to_list()

fig, ax1 = plt.subplots(figsize=(10, 5))
ax2 = ax1.twinx()
ax1.bar(YMS, REV,    color='#3498db', alpha=0.7, label='Sales amount (million yen)')
ax2.plot(YMS, ORDERS, color='#e74c3c', marker='o', linewidth=2, label='Number of orders received')
ax1.set_title('monthly Order received KPI（No.071: Sales amount + Number of Orders)', fontsize=13)
ax1.set_xlabel('Year and month')
ax1.set_ylabel('Sales amount (million yen)')
ax2.set_ylabel('Number of orders received')
ax1.grid(axis='y', alpha=0.3)
lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc='upper left', fontsize=9)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

Reading the results

• December saw a tendency for monthly sales to be high (concentrated year-end demand and deliveries), while August was lower due to summer vacations.
• If the avg_order_value (average order amount) fluctuates significantly from month to month, check whether large orders are being accepted
• Months with few unique_customers (number of unique customers) may be highly dependent on specific large clients
  Can be used to understand → Sales concentration risk

No.072: Calculating Average Customer Value

Meaning in Practice

Average order amount per customer (average order amount by client) is an essential indicator for formulating sales strategies.
The sales strategies to address differ between “high-price, low-frequency” clients and those who “low-price, high-frequency” clients.

Approach to Analysis and Modeling

$$\text{Unit Price} = \frac{\text{Total Order Amount}}{\text{Number of Orders}}$$

By reviewing both the annual total order amount and the number of orders, you can understand the Trading Characteristics of your clients.

Features	High unit price, low frequency	Low Unit Price and High Frequency
Example	High-Value Equipment & Made-to-Order Products	Consumables & Regular Order Items
Sales Support	Assign a dedicated person for careful follow-up	Promoting Automated Ordering and EDI Integration
Risks	If transactions stop, it will have a significant impact on sales.	Prone to price wars

Check with Python

print("=== No.072 Calculate the average order value ===\n")

# (1) Annual Average Customer Value by Client (Top 10 Companies)
print("① Departing in triumph Annual Average Passenger Price (descending order)")
df72 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id)                              AS order_count,
  SUM(o.quantity * o.unit_price)                 AS total_revenue,
  ROUND(SUM(o.quantity * o.unit_price) * 1.0
        / COUNT(o.order_id), 0)                  AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY avg_order_value DESC
''')

# (2) Overall average customer value
print("\n② Overall Average Spend per Passenger")
q(conn, '''
SELECT ROUND(SUM(quantity * unit_price) * 1.0 / COUNT(order_id), 0) AS overall_avg
FROM orders
''')

Reading the results

• Customers with high average customer value tend to place more High-end products (control boards, three-phase motors) orders
• Even if the average transaction value is low, customers with a high number of orders become a stable sales base as Regular ordering location for consumables.
• If the top per-customer value rankings and the top order volume rankings do not match, it serves as a starting point for analyzing the low profits but high volume vs High Added Value’s transaction structure

No.073: Aggregating Purchase Frequency by Customer

Meaning in Practice

By understanding “how many times a year you place orders” by client,
Identifying Loyal Customers, Engaging with Low-Frequency Customers becomes possible.

Approach to Analysis and Modeling

Frequency is the “F” component in RFM analysis.

$$F_i = \text{COUNT}(\text{order\_id}) \text{ WHERE customer\_id} = i$$

By understanding the distribution of order frequency, you can see the structure of your customer base (Pareto’s Law: the top 20% of customers account for 80% of sales).

Check with Python

print("=== No.073 Aggregate purchase frequency by customer ===\n")

# (1) Number of orders by client (total)
print("① Departing in triumph Number of orders received")
df73 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY order_count DESC
''')

# (2) Visualization: Top 10 Order Counts (Bar Graph)
df73_top = df73.head(10)
names  = df73_top['customer_name'].to_list()
counts = df73_top['order_count'].to_list()
revs   = [v / 1_000_000 for v in df73_top['total_revenue'].to_list()]

fig, axes = plt.subplots(1, 2, figsize=(13, 5))

# Number of orders received
axes[0].barh(names[::-1], counts[::-1], color='#3498db', edgecolor='white')
axes[0].set_title('Departing in triumph Number of orders received TOP10', fontsize=12)
axes[0].set_xlabel('Number of orders received')
axes[0].set_ylabel('')
axes[0].grid(axis='x', alpha=0.4)

# Sales amount
axes[1].barh(names[::-1], revs[::-1], color='#2ecc71', edgecolor='white')
axes[1].set_title('Departing in triumph Sales amount TOP10(Million yen)', fontsize=12)
axes[1].set_xlabel('Sales amount (million yen)')
axes[1].set_ylabel('')
axes[1].grid(axis='x', alpha=0.4)

plt.tight_layout()
plt.show()

Reading the results

• There is a possibility that the top order number and top sales amount do not match Order frequency is low, but large orders are made. clients.
• Clients with extremely low order volumes (1–2) may be dormant candidate uncertain if the next order will come
• Verification of Pareto’s Law: Check whether the top few companies account for 80% of total sales and use it to review Sales Resource Allocation

No.074: Classifying New and Existing Customers

Meaning in Practice

By classifying “clients who have started trading with this year” and “clients who have been continuously trading for a long time,“
You can simultaneously grasp Effects of New Acquisition (results of business investment) and Deepening Existing Customers.

Approach to Analysis and Modeling

customers.first_contract_date (initial contract date) is used as the basis for classification using the CASE formula.

$$\text{customertype} = \begin{cases} \text{New customer} & \text{if } \text{first\_contract\_date} \geq \text{'2024-01-01'} \\ \text{Existing Customer} & \text{otherwise} \end{cases}$$

indicator	Meaning
Number of new customers	Number of new developments this year
Total Order Amount from New Customers	Contribution of New Acquisition Sales
Average customer value for new customers	Transaction Size in the First Year

Check with Python

print("=== No.074 Classify new and existing customers ===\n")

# (1) Summary of New vs. Existing Classification
print("① new / existing Client Summary (2024 (Based on year)")
df74a = q(conn, '''
SELECT
  CASE
    WHEN c.first_contract_date >= '2024-01-01' THEN 'New customers (2024New contracts every year)'
    ELSE 'Existing Customers (2024(Even before the year)'
  END AS customer_type,
  COUNT(DISTINCT c.customer_id)  AS customer_count,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS total_revenue,
  ROUND(SUM(o.quantity * o.unit_price) * 1.0
        / COUNT(o.order_id), 0)  AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY customer_type
ORDER BY customer_type DESC
''')

# (2) List of New Customers
print("\n② New customers (2024List of annual new contracts")
q(conn, '''
SELECT
  c.customer_id,
  c.customer_name,
  c.region,
  c.first_contract_date,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS annual_revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.first_contract_date >= '2024-01-01'
GROUP BY c.customer_id
ORDER BY c.first_contract_date
''')

Reading the results

• New customers (first contracts of 2024) are 4 companies: C008 Kobayashi Seiki (February), C010 Yoshida Mechatronics (April), C017 Hashimoto Machinery (January), C013 Sato Kiden Kogyo (July)
• New customers start transactions mid-year, so annual sales in the first year are often lower than those of existing customers
• By comparing the average spend of new customers with existing customers, you can leverage it for Accuracy Verification of New Target Development purposes.

No.075: Calculating Repeat Rate

Meaning in Practice

Repeat Rate = Percentage of clients who placed orders more than twice within the year.
In manufacturing B2B, a higher repeat rate means a stable order base.
If you notice a downward trend in repeat rates, it is a sign of declining customer satisfaction or a shift to competitors.

Approach to Analysis and Modeling

$$\text{repeat rate} = \frac{\text{number of orders} \geq 2 \text{ number of clients}} {\text{total number of customers}} \times 100$$

You can combine CASE expressions and aggregation functions to compute in a single SQL:

SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)

Check with Python

print("=== No.075 Calculating the repeat rate ===\n")

# (1) Overall Repeat Rate
print("① Overall repeat rate")
df75a = q(conn, '''
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT
  COUNT(*)                                       AS total_customers,
  SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
  SUM(CASE WHEN order_count  = 1 THEN 1 ELSE 0 END) AS one_time_customers,
  ROUND(SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1)                  AS repeat_rate_pct
FROM order_counts
''')

# (2) Distribution of Client Numbers by Number of Orders
print("\n② Distribution of Client Numbers by Number of Orders")
q(conn, '''
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT
  CASE
    WHEN order_count >= 30 THEN '30more than one'
    WHEN order_count >= 20 THEN '20〜29records'
    WHEN order_count >= 10 THEN '10〜19records'
    WHEN order_count >=  5 THEN '5〜9records'
    WHEN order_count >=  2 THEN '2〜4records'
    ELSE '1only case'
  END AS freq_band,
  COUNT(*) AS customer_count
FROM order_counts
GROUP BY freq_band
ORDER BY MIN(order_count) DESC
''')

Reading the results

• If the repeat rate is high (80–90% or more), relationships with existing customers can be considered good
• If you have many clients with “just one deal,” Connecting trial orders to ongoing orders follow-up is necessary.
• By tracking the annual repeat rate trends, it can be used as a Customer Loyalty Metrics for management decision-making.

No.076: Calculating the Last Purchase Date

Meaning in Practice

Number of days elapsed since the last order date (Recency） is the “R” component in RFM analysis.
Clients who have not placed orders for a long time are treated as dormant customer and become targets for sales follow-up.

Approach to Analysis and Modeling

In SQLite, the julianday() function converts the date to floating-point (Julian Day),
By taking the difference, you can calculate the number of days elapsed.

$$\text{elapsed days} = \text{julianday}(\text{'2024-12-31'}) - \text{julianday}(\text{MAX(order\_date)})$$

Number of days elapsed	Categories	Response
0–30 days	Active	Suggestions for the Next Order
31–90 days	Note	Follow-up Contact
91 days or more	dormant candidate	Need to rebuild relationships

Check with Python

print("=== No.076 Calculate the last purchase date ===\n")

# (1) Last Order Date and Number of Days Passed
print("① Departing in triumph Last Order Date (in order of elapsed days)")
df76 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  MAX(o.order_date)                                           AS last_order_date,
  ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS days_since_last,
  CASE
    WHEN julianday('2024-12-31') - julianday(MAX(o.order_date)) <= 30 THEN 'Active'
    WHEN julianday('2024-12-31') - julianday(MAX(o.order_date)) <= 90 THEN 'Note'
    ELSE 'dormant candidate'
  END AS recency_status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY days_since_last DESC
''')

# (2) Status Summary
print("\n② By Status of Number of Days Passed Since Last Order Proud and quick to count")
q(conn, '''
WITH recency AS (
  SELECT
    customer_id,
    ROUND(julianday('2024-12-31') - julianday(MAX(order_date)), 0) AS days_since_last
  FROM orders
  GROUP BY customer_id
)
SELECT
  CASE
    WHEN days_since_last <= 30 THEN 'Active (30within a few days)'
    WHEN days_since_last <= 90 THEN 'Note (31〜90Day)'
    ELSE 'Dormant candidate (91(days)'
  END AS recency_status,
  COUNT(*) AS customer_count
FROM recency
GROUP BY recency_status
ORDER BY MIN(days_since_last)
''')

Reading the results

• Clients with more than 90 days of service can be automatically added as dormant candidates to the sales priority list.
• Data functions as a Real-time sleep alerts by regularly recalculating (e.g., every Monday morning)
• Even dormant candidates with high annual sales are treated as High-Priority Sales Opportunities

No.077: Creating Data for RFM Analysis

Meaning in Practice

RFM Analysis is a customer segment analysis method that scores customers based on three indicators.

indicator	English	Definition	Meaning in manufacturing
R	Rency	Number of days elapsed since final order	Have you recently placed an order?
F	Frequency	Number of orders received	How many times have you placed an order?
M	Monetary (amount)	Total Order Amount	How much are you buying?

Customers with high RFM scores (RFM = 3-3-3) have Most Important Customers (VIP）, while customers with low scores (1-1-1) have Risk of Defection.

Approach to Analysis and Modeling

Convert each indicator into a score (1–3):

$$R_{score} = \begin{cases} 3 & R \leq 30 \\ 2 & 31 \leq R \leq 90 \\ 1 & R > 90 \end{cases} \quad F_{score} = \begin{cases} 3 & F \geq 25 \\ 2 & 10 \leq F < 25 \\ 1 & F < 10 \end{cases} \quad M_{score} = \begin{cases} 3 & M \geq 2{,}000{,}000 \\ 2 & 800{,}000 \leq M < 2{,}000{,}000 \\ 1 & M < 800{,}000 \end{cases}$$

Check with Python

print("=== No.077 RFMCreate data for analysis ===\n")

# (1) RFM Data Creation
print("① RFM Score Table")
df77 = q(conn, '''
WITH rfm_raw AS (
  SELECT
    o.customer_id,
    c.customer_name,
    ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS recency,
    COUNT(o.order_id)              AS frequency,
    SUM(o.quantity * o.unit_price) AS monetary
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY o.customer_id
)
SELECT
  customer_id,
  customer_name,
  recency,
  frequency,
  monetary,
  CASE WHEN recency  <=  30 THEN 3 WHEN recency  <=  90 THEN 2 ELSE 1 END AS r_score,
  CASE WHEN frequency >= 25 THEN 3 WHEN frequency >= 10  THEN 2 ELSE 1 END AS f_score,
  CASE WHEN monetary >= 2000000 THEN 3 WHEN monetary >= 800000 THEN 2 ELSE 1 END AS m_score
FROM rfm_raw
ORDER BY monetary DESC
''')

# (2) RFM Segment Aggregation
print("\n② RFM Segment aggregation")
df77s = q(conn, '''
WITH rfm_raw AS (
  SELECT
    o.customer_id,
    ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS recency,
    COUNT(o.order_id)              AS frequency,
    SUM(o.quantity * o.unit_price) AS monetary
  FROM orders o
  GROUP BY o.customer_id
),
scored AS (
  SELECT
    customer_id,
    CASE WHEN recency  <=  30 THEN 3 WHEN recency  <=  90 THEN 2 ELSE 1 END AS r,
    CASE WHEN frequency >= 25 THEN 3 WHEN frequency >= 10  THEN 2 ELSE 1 END AS f,
    CASE WHEN monetary >= 2000000 THEN 3 WHEN monetary >= 800000 THEN 2 ELSE 1 END AS m
  FROM rfm_raw
)
SELECT
  r || '-' || f || '-' || m AS rfm_segment,
  CASE
    WHEN r=3 AND f=3 AND m=3 THEN 'VIPcustomer'
    WHEN r=1 AND f<=2 AND m<=2 THEN 'Risk of Defection'
    WHEN r>=2 AND f>=2 THEN 'Excellent Customer'
    ELSE 'Other'
  END AS segment_label,
  COUNT(*) AS customer_count
FROM scored
GROUP BY rfm_segment
ORDER BY rfm_segment DESC
''')

# Visualization: F vs M scatter plot (color = R score)
R_COLORS = {1: '#e74c3c', 2: '#f39c12', 3: '#2ecc71'}
fig, ax = plt.subplots(figsize=(9, 6))
for r_val in [3, 2, 1]:
    sub = df77.filter(pl.col('r_score') == r_val)
    ax.scatter(
        sub['frequency'].to_list(),
        [v / 1_000_000 for v in sub['monetary'].to_list()],
        color=R_COLORS[r_val],
        label=f'R={r_val}（{"Most recent active" if r_val==3 else "Note" if r_val==2 else "dormant candidate"}）',
        s=80, alpha=0.8, edgecolors='white', linewidth=0.5
    )
ax.set_title('RFM Analysis: Order Frequency vs Sales amount (color) = Recency Score)', fontsize=13)
ax.set_xlabel('Number of orders (Frequency）')
ax.set_ylabel('Annual sales amount (million yen)')
ax.legend(fontsize=9)
ax.grid(alpha=0.3)
plt.tight_layout()
plt.show()

Reading the results

• If the dot in the upper right (high F / high M) and green (R=3) is VIP customer: frequent and large transactions until recently
• If the lower left (low F, low M) and red dot (R=1) are Defection risk customers: orders have stopped.
• RFM scores can be regularly recalculated to track Changes in the customer portfolio
  → Taking early sales actions for customers whose segments are deteriorating

No.078: Comparing inventory and sales volume

Meaning in Practice

By understanding “how many months’ worth of demand does the current inventory amount correspond to?“,
You can manage both Excess inventory (cash flow pressure) and Inventory shortage (missed opportunity) risks.

Approach to Analysis and Modeling

Calculate monthly average demand from shipment performance over the most recent three months (October–December) and calculate the inventory months:

$$\text{Months in stock} = \frac{\text{Current inventory quantity}}{\text{Average monthly demand}}$$

Inventory months	Reception	Response
≥ 6 months	excess inventory	Production Plan Review & Bargain Sale
2 to 5 months	Suitable for the warehouse,	maintain the status quo
1–2 months	Note	Consideration of Supplementary Orders
< 1 month	dangerous area	Emergency orders and alternative arrangements

Check with Python

print("=== No.078 Compare inventory and sales numbers ===\n")

# (1) Inventory vs. Shipment Performance in the Past 3 Months
print("① By product Inventory vs most recent3ヶMonthly demand (by number of months in stock)")
df78 = q(conn, '''
WITH recent_demand AS (
  SELECT
    product_id,
    SUM(quantity)                    AS qty_3m,
    ROUND(SUM(quantity) * 1.0 / 3, 0) AS monthly_avg_qty
  FROM orders
  WHERE order_date >= '2024-10-01'
  GROUP BY product_id
)
SELECT
  p.product_id,
  p.product_name,
  p.category,
  i.stock_qty,
  i.safety_stock,
  COALESCE(d.monthly_avg_qty, 0) AS monthly_avg_qty,
  CASE
    WHEN COALESCE(d.monthly_avg_qty, 0) = 0 THEN 999
    ELSE ROUND(i.stock_qty * 1.0 / d.monthly_avg_qty, 1)
  END AS stock_months
FROM products p
JOIN inventory i ON p.product_id = i.product_id
LEFT JOIN recent_demand d ON p.product_id = d.product_id
ORDER BY stock_months ASC
''')

# (2) Visualization: Inventory vs. Safety Stock (Bar Graph)
names   = df78['product_name'].to_list()
stocks  = df78['stock_qty'].to_list()
safetys = df78['safety_stock'].to_list()
months  = df78['stock_months'].to_list()
bar_colors = ['#e74c3c' if s < sf else '#f39c12' if s < sf * 2 else '#2ecc71'
              for s, sf in zip(stocks, safetys)]

x = range(len(names))
fig, ax = plt.subplots(figsize=(11, 5))
ax.bar(x, stocks,  color=bar_colors, label='Current Warehouse', edgecolor='white', alpha=0.9)
ax.plot(x, safetys, 's--', color='#e74c3c', markersize=8, label='Safety stock line', linewidth=1.5)
ax.set_title('By product Current Warehouse vs Safety stock (No.078）', fontsize=13)
ax.set_xlabel('Products')
ax.set_ylabel('Inventory quantity')
ax.set_xticks(list(x))
ax.set_xticklabels(names, rotation=30, ha='right')
ax.legend(fontsize=10)
ax.grid(axis='y', alpha=0.4)
plt.tight_layout()
plt.show()

Reading the results

• Red stick of the graph (Current Inventory < Safety Stock) are products at risk of being out of stock (detailed analysis in → No.079).
• The Orange bar in the graph (safety stock up to 2 times) indicates products that require consideration for replenishment.
• Products with high stock_months (such as consumables like P005 packing) have room to reduce Storage costs due to excess inventory

No.079: Extracting Products at Risk of Out-of-Stock

Meaning in Practice

Stockouts lead to Missed opportunities, production stoppages, and inconvenience to clients.
We calculate the Remaining Inventory Days (Runout Days） considering the order lead time,
It is important to automatically alert for products that require replenishment orders.

Approach to Analysis and Modeling

$$\text{Days remaining in stock} = \frac{\text{Current stock}}{\text{average daily demand}} = \frac{\text{stock\_qty}}{\text{monthly\_avg\_qty} / 30}$$

For products with a lead time of $L$ days:

$$\text{Condition to be supplemented} :\quad \text{Remaining stock days} < L \cdot 1.5 \quad (\text{Applies safety factor 1.5})$$

Check with Python

print("=== No.079 Extracting products at risk of out-of-stock ===\n")

# (1) Inventory Risk Assessment
print("① Inventory Risk Assessment (Remaining Inventory Days) + Status)")
df79 = q(conn, '''
WITH recent_demand AS (
  SELECT
    product_id,
    ROUND(SUM(quantity) * 1.0 / 3, 0) AS monthly_avg_qty
  FROM orders
  WHERE order_date >= '2024-10-01'
  GROUP BY product_id
),
stock_eval AS (
  SELECT
    i.product_id,
    i.stock_qty,
    i.safety_stock,
    p.product_name,
    p.lead_time_days,
    COALESCE(d.monthly_avg_qty, 0) AS monthly_avg_qty,
    CASE
      WHEN COALESCE(d.monthly_avg_qty, 0) = 0 THEN 9999
      ELSE ROUND(i.stock_qty * 30.0 / d.monthly_avg_qty, 0)
    END AS runout_days,
    CASE
      WHEN i.stock_qty < i.safety_stock THEN 'Stockout risk'
      WHEN i.stock_qty < i.safety_stock * 2 THEN 'To add,'
      ELSE 'Ample stock'
    END AS stock_status
  FROM inventory i
  JOIN products p ON i.product_id = p.product_id
  LEFT JOIN recent_demand d ON i.product_id = d.product_id
)
SELECT * FROM stock_eval ORDER BY runout_days ASC
''')

# (2) Display only risk items
print("\n② Stockout risk / To add, item")
risk = df79.filter(pl.col('stock_status').is_in(['Stockout risk', 'To add,']))
for row in risk.iter_rows(named=True):
    print(f"  {row['product_id']} {row['product_name']:18s} "
          f"Inventory:{row['stock_qty']:5d} Safety stock:{row['safety_stock']:5d} "
          f"remnant:{row['runout_days']:4.0f}days LT:{row['lead_time_days']}days [{row['stock_status']}]")

# Visualization: Days remaining inventory (Gantbar style)
STATUS_COLORS = {'Stockout risk': '#e74c3c', 'To add,': '#f39c12', 'Ample stock': '#2ecc71'}
names79   = df79['product_name'].to_list()
runouts   = [min(r, 180) for r in df79['runout_days'].to_list()]
statuses  = df79['stock_status'].to_list()
bar_cols  = [STATUS_COLORS[s] for s in statuses]
lead_days = df79['lead_time_days'].to_list()

fig, ax = plt.subplots(figsize=(10, 5))
ax.barh(names79, runouts, color=bar_cols, edgecolor='white')
for i, (ld, nm) in enumerate(zip(lead_days, names79)):
    ax.axvline(ld * 1.5, color='gray', linestyle=':', linewidth=0.8, alpha=0.5)
ax.axvline(30, color='#e74c3c', linestyle='--', linewidth=1.5, label='30Day Line (Alert)')
ax.set_title('By product Remaining Inventory Days (No.079: Out-of-stock risk assessment)', fontsize=12)
ax.set_xlabel('Remaining Inventory Days')
ax.set_ylabel('Products')
ax.legend(fontsize=9)
ax.grid(axis='x', alpha=0.4)
plt.tight_layout()
plt.show()

Reading the results

• P002 hydraulic cylinder, P004 Control board, P007 three-phase motor, and P010 electromagnetic valve have stocks below safety stock and require urgent replenishment orders.
• Since the lead_time_days for these products is 10 to 14 days, From Ordering to Arrival 2 Weeks required → should immediately place replenishment orders.
• You can build a system that runs this SQL every morning regularly and notifies procurement staff by email for stock_status = 'Stockout risk' products.

No.080: Extracting products with abnormally high or low sales

Meaning in Practice

Rapid increase and decline of monthly sales arises from various factors such as order concentration or loss, large orders from specific clients, and price fluctuations.
By automatically detecting this, Early Problem Identification and Seizing Opportunities becomes possible.

Approach to Analysis and Modeling

Calculate the month-on-month change rate and extract months or products whose absolute value exceeds the threshold (e.g., ±30%):

$$\text{Month-over-month change rate} = \frac{\text{Current month sales} - \text{Previous month sales}}{\text{Previous month sales}} \times 100$$

Using the window function LAG(), you can retrieve the previous month’s value for each product in chronological order:

LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY ym)

PARTITION BY product_id allows you to calculate an independent month-over-month comparison for each product.

Check with Python

print("=== No.080 Extract products with abnormally high or low sales ===\n")

# (1) Monthly Sales by Product + Month-on-Month
print("① Compared to the previous month ±30%Abnormal records above")
df80 = q(conn, '''
WITH monthly AS (
  SELECT
    o.product_id,
    p.product_name,
    strftime('%Y-%m', o.order_date) AS ym,
    SUM(o.quantity * o.unit_price)  AS monthly_revenue
  FROM orders o
  JOIN products p ON o.product_id = p.product_id
  GROUP BY o.product_id, ym
),
with_lag AS (
  SELECT
    *,
    LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY ym) AS prev_revenue
  FROM monthly
)
SELECT
  product_id,
  product_name,
  ym,
  monthly_revenue,
  prev_revenue,
  ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS mom_pct
FROM with_lag
WHERE prev_revenue IS NOT NULL
  AND ABS(ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1)) >= 30
ORDER BY ABS(ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1)) DESC
LIMIT 15
''')

# (2) Monthly Trend Data by Product (Total 12 Months)
df80_all = q(conn, '''
WITH monthly AS (
  SELECT
    o.product_id,
    p.product_name,
    strftime('%Y-%m', o.order_date) AS ym,
    SUM(o.quantity * o.unit_price)  AS monthly_revenue
  FROM orders o
  JOIN products p ON o.product_id = p.product_id
  GROUP BY o.product_id, ym
)
SELECT * FROM monthly
ORDER BY product_id, ym
''')

# Visualization: Monthly trends of the top 3 products (line lines)
TOP_PRODS = (df80['product_id'].unique().to_list())[:3]
COLORS_80 = ['#3498db', '#e74c3c', '#2ecc71']
ALL_YMS   = sorted(df80_all['ym'].unique().to_list())

fig, ax = plt.subplots(figsize=(11, 5))
for i, pid in enumerate(TOP_PRODS):
    sub  = df80_all.filter(pl.col('product_id') == pid).sort('ym')
    pname = sub['product_name'][0]
    revs  = [sub.filter(pl.col('ym') == ym)['monthly_revenue'][0] / 1_000_000
             if ym in sub['ym'].to_list() else 0 for ym in ALL_YMS]
    ax.plot(ALL_YMS, revs, marker='o', color=COLORS_80[i], linewidth=2,
            label=f'{pid} {pname}')
    # Highlighting anomalies
    anoms = df80.filter(pl.col('product_id') == pid)
    for row in anoms.iter_rows(named=True):
        ym_idx = ALL_YMS.index(row['ym']) if row['ym'] in ALL_YMS else -1
        if ym_idx >= 0:
            ax.scatter(row['ym'], row['monthly_revenue'] / 1_000_000,
                       color=COLORS_80[i], s=150, zorder=5,
                       marker='*', edgecolors='black', linewidth=0.5)

ax.set_title('By product Monthly Sales Trends (★ = Compared to the previous month ±30%The above abnormalities)No.080）', fontsize=12)
ax.set_xlabel('Year and month')
ax.set_ylabel('Monthly Sales (million yen)')
ax.legend(fontsize=9)
ax.grid(alpha=0.3)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

Reading the results

• ★ mark (outliers) indicate months with a month-on-month increase of ±30% or more.
• The sharp increase in months is likely due to factors such as “concentrated large orders,” “campaign effects,” and “switching away from competitors.”
• A sharp decline in months may be a sign of ‘order suspension from key clients,’ ‘price negotiations underway,’ or ‘order suppression due to excess inventory.’
• The threshold (±30%) can be adjusted according to industry and product life cycle and standardized as Internal anomaly detection rules

Practical Implications Seen Through Target Exercise

Manufacturing B2B “Order Management SQL Pattern Collection”

Challenges	SQL Patterns	Business Value
Automatic KPI Aggregation	GROUP BY + strftime	Reducing the time required for monthly report creation
Customer Rankings	ORDER BY + TOP-N	Priority Allocation of Sales Resources
Customer Classification	CASE + DATE Comparison	Visualizing New Acquisition vs. Deepening Existing Ones
Repeat Rate	Conditional aggregation + division	Customer loyalty KPI development
Detecting dormant customers	MAX(date) + date difference	Proactive measures to prevent defections
RFM Segment	CTE + CASE Scoring	Strategy design by customer segment
Inventory management	LEFT JOIN + Inventory Months Calculation	Quantitative Assessment of Excess Inventory / Stockout Risks
anomaly detection	LAG window function	Automatic alerts for sales anomalies

”What can be automated with SQL” and “What human judgment is required”

SQL automates Data aggregation, transformation, and filtering,
The Cause Analysis of “why this change happened” requires human knowledge and experience.

• SQL → Presenting “What, when, and how much changed”
• Humans → Judge “Why Things Have Changed and How to Respond”

By clarifying this division of roles, the effectiveness of utilizing SQL is maximized.

What is necessary for practical implementation

1. Ensuring Data Quality

• Regularly verify that there are no Missing, duplicate, and input mills in order data (to be addressed in → No.094–095).
• Standardize the format of order_date (YYYY-MM-DD)

2. Automate Regular Execution

Every morning at 8:00 → Run inventory risk SQL → Email notifications for items at risk of out-of-stock
On the 1st of every month, → RFM recalculation → notify the person in charge of segment changes.
Execute the quarter-end → KPI dashboard SQL → automatically insert into management reports

3. Connecting Data Sources

In this exercise, we used SQLite, but in practice, we connect to the following systems:

System	DB Example	Supported SQL
Sales Management System	MySQL / PostgreSQL	Almost identical (the date function has dialects)
ERP（SAP etc）	SQL Server / Oracle	Dialect support required
DWH	BigQuery / Snowflake	Runs with almost standard SQL

4. Security and Permission Management

• Order data and customer data may contain personal information
• Set role-based access control (RBAC) for DWHs and assign Minimum necessary permissions

Conclusion

In this chapter, we practiced 10 Practical Data Analysis SQL using manufacturing B2B order and inventory data.

No.	Acquired Skills	Practical Value
071	Monthly KPI Aggregation	Automatic Generation of Order Summaries
072	Unit Price Calculation	Understanding transaction characteristics by client
073	Number of purchases by customer	Identifying VIP and Low-Frequency Customers
074	New / Existing Customer Classification	Visualizing the impact of new development
075	Repeat Rate Calculation	Customer Loyalty KPIs
076	Last Purchase Date & Number of Days Passed	Automatic detection of dormant customers
077	RFM Analysis Data	Customer Segment Design
078	Inventory vs. Demand Comparison	Quantitative Tracking of Inventory Overage and Shortage
079	Stockout Risk Extraction	Automation of replenishment order alerts
080	Abnormal sales detection	Early Detection of Rapid Increases and Decreases

Chapter 2 (Part 1)9Chapter) covers Applied Analysis SQL (cohort analysis, funnel analysis, A/B test aggregation, etc.).
Let’s master more advanced analytical methods by combining the basic aggregation patterns learned in this chapter.

Consultations for Corporations

Building an order data analysis platform, Customer Analysis &RFM Implementation of Analytics,
Inventory risk management SQL automation, regarding SQL Training and in-house production support,
Suri Kobo accepts consultations for corporate clients.

As discussed in this exercise, we have a system for automating order KPIs, customer segments, and inventory alerts using SQL.
From implementation support to designing educational programs, please feel free to contact us.

---

📩 Contact Us: surikobo.co.jp/contact Please feel free to consult us first.