Returning Customers Percent

returning_customers_percent

Overview

Returning Customers Percent represents the percentage of orders placed by returning customers within a specific period relative to the total number of orders.

📘

Returning Customers Percent = (Returning Customer Orders / Total Orders)

The calculation is based on data from the Orders table.

Detailed Breakdown

The formula above is derived from the following components:

Returning Customer Orders = sum(if(o.is_new_customer, 0, 1)) --> Orders table
Total Orders = SUM(orders_quantity) --> Orders table

Insights and Actions

The Percentage of Returning Customers is a key metric for understanding customer loyalty and the effectiveness of retention strategies. Efficiently leveraging this data can guide strategic business decisions:

• Enhance Customer Retention Programs: Use insights from the returning customer rate to refine or introduce loyalty programs, personalized offers, and engagement strategies to increase customer loyalty.
• Identify Successful Retention Channels: Analyze which marketing channels and tactics have the highest correlation with customer returns to focus your efforts on the most effective methods.
• Improve Product and Service Offerings: Gather feedback from returning customers to understand what keeps them coming back and where there's room for improvement, then use this information to enhance your offerings.
• Segment and Personalize Communications: Segment your customer base by purchase history and preferences to tailor communications and promotions, encouraging more frequent returns.

Example Use

Prompt

Show me the % of returning customers for the last quarter

Response

Query

SQL

WITH
  customers_cte AS (
    SELECT
      COUNT(
        DISTINCT CASE
          WHEN ot.is_new_customer = 0 THEN ot.customer_id
        END
      ) AS returning_customers,
      COUNT(DISTINCT ot.customer_id) AS total_customers
    FROM
      orders_table AS ot
    WHERE
      ot.event_date BETWEEN '2024-07-01' AND '2024-09-30'
  )
SELECT
  COALESCE(
    customers_cte.returning_customers / NULLIF(customers_cte.total_customers, 0),
    0
  ) AS returning_customers_percentage
FROM
  customers_cte;