Excel技术需求：统计每月成功联系的独立客户数量

Hey there! Let's break down how to calculate the number of unique successfully contacted customers per month—no counting the same customer multiple times even if they had multiple successful attempts in that month. Below are solutions for the most common tools you might be using:

This is straightforward with a pivot table, which handles the unique count automatically:

• First, add an auxiliary column to extract the year-month from your contact date (let's say your date is in column B). Use this formula:
  =TEXT(B2,"yyyy-mm") (works for both Excel and Google Sheets)
• Filter your data to only include rows where the success status is S (successful contacts).
• Insert a pivot table using the filtered data:
  • Drag the year-month column to the Rows area.
  • Drag the customer name column to the Values area.
  • For Excel: Right-click the value field → Value Field Settings → select Distinct Count.
  • For Google Sheets: In the pivot table editor, set the Summarize by option to Unique count.

Assuming your table is named contact_attempts with columns customer_name, contact_date, and success_status, here's a query that works across most databases (adjust the date formatting if needed for your specific DB):

SELECT
    -- Adjust this line based on your database:
    -- MySQL: DATE_FORMAT(contact_date, '%Y-%m')
    -- SQL Server: FORMAT(contact_date, 'yyyy-MM')
    -- PostgreSQL: DATE_TRUNC('month', contact_date)::date
    DATE_TRUNC('month', contact_date) AS contact_month,
    COUNT(DISTINCT customer_name) AS unique_successful_customers
FROM
    contact_attempts
WHERE
    success_status = 'S'
GROUP BY
    contact_month
ORDER BY
    contact_month;

The COUNT(DISTINCT ...) ensures we only count each customer once per month, even if they have multiple successful contacts.

If you're working with Python and Pandas, this script will process your data and output the monthly unique count table:

import pandas as pd

# Load your data (replace with your file path)
df = pd.read_csv('contact_attempts.csv')

# Convert contact date to datetime type (critical for date operations)
df['contact_date'] = pd.to_datetime(df['contact_date'])

# Extract year-month as a period (e.g., 2024-05)
df['contact_month'] = df['contact_date'].dt.to_period('M')

# Filter successful contacts, group by month, count unique customers
monthly_stats = df[df['success_status'] == 'S'] \
    .groupby('contact_month')['customer_name'] \
    .nunique() \
    .reset_index(name='unique_successful_customers')

# Print the result or export to Excel
print(monthly_stats)
monthly_stats.to_excel('monthly_successful_customers.xlsx', index=False)

The nunique() method does exactly what we need—it counts distinct values in the customer name column for each month group.

内容的提问来源于stack exchange，提问作者Dustin Burns