Problem Description

You are provided with two pandas DataFrames: events and users.

events DataFrame Columns:

• user_id: Integer identifier for the user.
• ts: String representing an ISO-8601 timestamp with timezone information (e.g., '2025-08-31T23:58:43-07:00').
• event: String, one of {'signup', 'login', 'purchase'}.
• revenue: String that may include currency symbols (e.g., '$12.34', '€9,50') or be null.
• device_id: String identifier for the device.
• session_id: String identifier for the session (may contain duplicate values).
• source: String (may be null).

users DataFrame Columns:

• user_id: Integer identifier for the user.
• signup_ts: String representing a UTC timestamp.
• tz: String representing an IANA timezone (e.g., 'America/Los_Angeles').
• is_bot: Boolean, indicating if the user is a bot.
• country: String representing the user's country.
• plan: String, one of {'free', 'pro', 'enterprise'}.

Tasks:

1. Data Cleaning and Normalization:

   • Ensure all timestamps (ts in events, signup_ts in users) are timezone-aware datetime objects.
   • Deduplicate events: Identify events as duplicates if they have the same user_id, event, and session_id within a 5-minute window. For duplicates, keep only the earliest ts event.
   • Drop any events where the user_id does not exist in the users DataFrame.
   • Explain how to perform these cleaning steps without using chained assignment in pandas.

2. Daily Active Users (DAU) Calculation:

   • Define an "active day" for a user as any local-day (determined by the user's tz from the users DataFrame) during which they have at least one non-'signup' event.
   • Compute the DAU for each local date from 2025-08-25 through 2025-09-01, inclusive.
   • Exclude users where is_bot is True.
   • Exclude device_ids that appear on 20 or more distinct user_id values (these are considered "shared devices").
   • Return a DataFrame with columns: local_date, dau.

3. 7-Day Retention Analysis:

   • Calculate 7-day retention for cohorts defined by their signup_date (UTC).
   • Consider cohorts with signup_date (UTC) between 2025-08-24 and 2025-08-31, inclusive.
   • A user is considered "retained" if they have any non-'signup' event on the 7th day after their signup date, observed in their local timezone (tz).
   • Return a DataFrame with columns: signup_date, cohort_size, retained, retention_rate, and a Wilson 95% Confidence Interval for the retention rate.

4. Revenue Processing and ARPU Calculation:

   • Parse the revenue strings, extracting the numeric value and inferring the currency from symbols (e.g., '$' for USD, '€' for EUR, '¥' for JPY).
   • Convert all parsed revenues to USD using the provided exchange rate mapping: fx = {'USD':1.0, 'EUR':1.08, 'JPY':0.0068, ...}. Assume this mapping is complete for all encountered currencies.
   • Impute missing revenue values for 'purchase' events by using the median revenue over the last 30 days ending 2025-09-01, grouped by (country, plan).
   • Compute Average Revenue Per User (ARPU) for the last 7 days ending 2025-09-01, considering only non-bot users.

5. Performance and Memory Considerations:

   • Provide vectorized pandas code sketches for the above tasks, explicitly avoiding Python loops.
   • Discuss the expected memory footprint and computational complexity of your proposed solutions, assuming 100 million events.
   • Outline a chunked processing strategy to ensure peak RAM usage remains under 8 GB. This discussion should include techniques such as using category dtypes, specifying dtype maps when reading CSVs, leveraging sorted merges, and using groupby with observed=True.