How to Migrate Data From Mixpanel to Amazon Redshift?

Building an efficient ETL pipeline to move data from Mixpanel to Amazon Redshift can seem daunting, but trust me, it’s easier than you think! If you’re looking to analyze your Mixpanel event data alongside other business metrics, integrating it into Redshift is the perfect solution.

In this blog, I’ll walk you through the step-by-step process of building a seamless pipeline, so you can leverage Redshift’s powerful querying capabilities to unlock deeper insights from your Mixpanel data. Ready to take your analytics game to the next level? Let’s dive in!

Method 1: Using Hevo Data to Connect Mixpanel to Amazon Redshift

Hevo offers a fully managed, no-code pipeline that extracts Mixpanel event data, standardizes it, and loads it into Redshift reliably. You can load data from Mixpanel to Redshift within minutes:

Step 1: Connect Mixpanel as a source in Hevo

Give Hevo access to your Mixpanel project so it can read events, profiles, and metadata.

• Open your Hevo dashboard and click Add Source.
• Choose Mixpanel from the available connectors.

Enter the required Mixpanel credentials:

• Pipeline Name: Unique identifier for your data pipeline.
• API Secret: The Mixpanel project secret used for authentication.
• Events: Comma-separated Mixpanel event names to ingest.

Note: Select the exact data sets to sync (events, user profiles, cohorts).

What Hevo does:

• Hevo validates your credentials with the Mixpanel API. 
• It discovers available event types, properties, and user attributes. 
• It prepares a schema blueprint to map Mixpanel’s JSON-based structure into Redshift tables.

Step 2: Configure Amazon Redshift as the destination

• Provide connection details:
• Go to Add Destination and choose Amazon Redshift.

• Destination Name: Unique identifier for your Redshift target.
• Database Cluster Identifier: Redshift host DNS or IP address.
• Database Port: TCP port for Redshift connections (default 5439).
• Database User: Non-admin user with write permission
• Database Password: Secure credential for the Redshift user.
• Database Name: Name of the Redshift database to load into.
• Schema Name: Schema within the database where tables land.

• Now, select “SAVE DESTINATION.”
• Select the schema where Hevo should load the Mixpanel tables.
• Click on “TEST CONNECTION” to verify the configuration.

Enable the following options in these specific scenarios:

• Connect through SSH: When direct network access to PostgreSQL is restricted.
• Sanitize Table/Column Names: When names contain spaces or special characters.

What Hevo does:

• Hevo checks network connectivity and permissions to confirm it can create and update tables.
• It evaluates the Redshift schema and prepares to handle column creation and updates as Mixpanel data evolves.

Step 3: Create and configure the Mixpanel to Redshift pipeline

Leverage Hevo’s schema and data type automation for Mixpanel-to-Redshift pipelines.

• Create a new pipeline using Mixpanel as the Source and Redshift as the Destination.
• Review auto-generated table mappings (events, event properties, user profiles).
• Configure the sync mode:
  
  • Real-time streaming for immediate availability
  • Interval-based syncs for controlled frequency
    
• Enable the pipeline.

Pro tip: Use column-level mappings to optimize Redshift storage and query performance.

What Hevo does:

• Hevo begins incremental extraction by tracking event timestamps.
• Nested Mixpanel properties are flattened or structured to fit Redshift tables.
• Hevo queues and batches loads to optimize your Redshift performance.

Step 4: Activate pipeline and ensure continuous sync

Once your pipeline is active, you need to ensure it continues to run smoothly and delivers accurate data.

• Monitor pipeline health using Hevo’s dashboard for errors or failures.
• Check data freshness to ensure events are syncing in real-time or at scheduled intervals.
• Review schema changes in Mixpanel and update table mappings if new properties are added.
• Verify Redshift tables to confirm that all events and properties are correctly loaded.
• Set up alerts in Hevo for any pipeline disruptions or data inconsistencies.
• Schedule regular audits of event counts and profiles to maintain data accuracy.

What Hevo does:

Hevo handles incremental updates, enforces schema normalization, manages API rate limits, and ensures secure, consistent delivery of Mixpanel data into any data warehouse with minimal manual intervention.

Here’s a real-life example of how Hevo consolidated data from multiple sources into Redshift:

Company: Hornblower Group is a global passenger-transportation and leisure-experience company operating cruises, ferries, and hospitality services.

Problem: They needed a scalable way to consolidate data from multiple sources into Redshift while cutting infrastructure costs and removing manual ETL overhead.

Hevo’s solution: Hevo enabled Hornblower to build 75+ no-code pipelines that automatically handled schema mapping, table creation, and ingestion into Redshift. The team selectively ingested only business-critical datasets to reduce storage and compute costs.

Result: Hornblower achieved 50% data optimization by moving only essential data through Hevo, eliminating the need for 2–3 full-time data engineers.

Method 2: Using Custom ETL Scripts to Connect Mixpanel to Redshift

Loading Mixpanel data to Redshift using custom scripts gives full control over the ETL process but requires careful handling to ensure data accuracy, performance, and consistency. Below, we have used an example to explain the step-by-step process:

Step 1: Extract data from Mixpanel using the Export API

Start by hitting Mixpanel’s Export API to pull events and user profiles.

import requests
import json
from requests.auth import HTTPBasicAuth

API_SECRET = "YOUR_MIXPANEL_API_SECRET"
EXPORT_URL = "https://data.mixpanel.com/api/2.0/export/"

params = {

    "from_date": "2024-01-01",
    "to_date": "2024-01-31",
    "event": ["App Open", "Purchase"]
}

response = requests.get(

    EXPORT_URL,
    params=params,
    auth=HTTPBasicAuth(API_SECRET, "")
)

events = [json.loads(line) for line in response.text.split("\n") if line.strip()]

Workflow:

• The API returns events in JSON format.
• Each event contains insert_id (unique event identifier) and time (timestamp).
• Authentication is handled via HTTPBasicAuth(API_SECRET, “”).
• Your script parses each line with json.loads() to convert it into a structured Python dictionary.

Step 2: Prepare your Redshift schema

Before loading anything, define how you want the data stored inside Redshift.

CREATE TABLE mixpanel_events (

    event_name VARCHAR(255),
    event_time BIGINT,
    distinct_id VARCHAR(255),
    properties SUPER

);

Workflow:

• Define a table with properties SUPER to store raw JSON.
• Supports nested JSON keys, preserving structure for later queries.
• Eliminates the need for manual flattening of all event fields.

Step 3: Stage the extracted JSON data in S3

Push your extracted events into S3 because Redshift requires batch files.

import boto3
import json
import uuid

s3 = boto3.client("s3")
key = f"mixpanel/events_{uuid.uuid4()}.json"

s3.put_object(

    Bucket="your-s3-bucket",
    Key=key,
    Body=json.dumps(events)
)

Workflow:

• You generate a unique file name using uuid.uuid4().
• The script writes all extracted events into a single JSON batch file.
• s3.put_object() uploads the file into your S3 bucket for staging.
• This step ensures Redshift loads data in bulk.

Step 4: Load your staged data into Redshift

Use Redshift’s COPY command to ingest JSON files directly from S3.

COPY mixpanel_events
FROM 's3://your-s3-bucket/mixpanel/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS JSON 'auto';

Workflow:

• COPY pulls all JSON files from your S3 prefix.
• Redshift maps fields automatically with FORMAT AS JSON ‘auto’.
• The IAM role lets Redshift securely access your S3 bucket.
• COPY loads in parallel, giving you maximum throughput.

Step 5: Implement incremental updates

Load only new or updated data instead of reloading everything.

params = {
    "from_date": "2024-01-31",
    "to_date": "2024-01-31",
    "where": 'properties["updated_at"] >= 1706700000'
}

Workflow:

• Apply a filter based on time or updated_at to fetch only recent events.
• Each event’s insert_id ensures you can detect duplicates or missed events.
• This reduces API calls and keeps Redshift storage usage efficient.

Step 6: Schedule your ETL workflow

Automate your job to keep Redshift synced with Mixpanel.

0 */6 * * * /usr/bin/python3 /home/etl/mixpanel_etl.py

Workflow:

• Cron triggers mixpanel_etl.py script every six hours automatically.
• Script extracts Mixpanel data, stages JSON files, and uploads to S3.
• COPY command runs to ingest staged files into Redshift tables.

Step 7: Validate and monitor your ETL pipeline

After your ETL runs:

SELECT COUNT(*) FROM mixpanel_events;

Workflow:

• Check key fields like insert_id or updated_at for consistency.
• Query your Redshift table to verify row counts.
• Compare counts with events fetched from Mixpanel/S3.

Limitations of Using Custom ETL Scripts to Connect Mixpanel to Redshift

1. Maintenance overhead

Custom ETL requires writing and maintaining Python/SQL/CLI scripts, managing API rate limits, handling Mixpanel’s JSON structure, and updating pipelines whenever schemas or API contracts change.

2. Limited scalability for large events

Custom scripts can become slow or unstable as data volume grows, especially when dealing with JSON parsing, batching for S3 uploads, or COPY operations. Scaling requires additional engineering work, such as multi-threading or distributed processing.

3. Monitoring gaps

Most custom ETL pipelines lack built-in features like retry logic, pipeline alerts, and automatic failure recovery. This increases data latency and causes data loss if API calls fail or partial batches are not logged.

4. Complexity

Implementing proper incremental logic (updated_at, insert_id) requires additional scripting. Errors here can lead to duplicates or missing events, especially because Mixpanel event schemas contain nested JSON and dynamic properties.

5. Security and compliance

When using custom ETL, you must manage IAM roles, API key rotation, encryption, VPC configuration, and access control. Compliance with standards like SOC 2, GDPR, or HIPAA must be managed within the custom ETL setup.

Understanding Mixpanel to Amazon Redshift Integration

What is Mixpanel?

Mixpanel is a powerful product analytics tool designed to help businesses understand user behavior and drive data-driven decision-making. It enables organizations to track, analyze, and optimize user interactions across web and mobile platforms, offering insights into user engagement, retention, and conversion.

Key Features of Mixpanel

• Event Tracking: Captures detailed user interactions and events, allowing for granular analysis of user behavior.
• Segmentation: Provides advanced segmentation capabilities to analyze user groups based on various attributes and behaviors.
• Funnels: Tracks user progress through predefined steps to identify conversion rates and drop-off points.
• Cohort Analysis: Analyzes user groups over time to understand retention patterns and the impact of changes on user behavior.

If you are looking for a marketing analytics tool, check out our blog on 12 Best Marketing Analytics Tools to decide which suits you the best.

What is Amazon Redshift?

Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. It is designed to handle large-scale data processing and complex queries with high performance. Redshift enables businesses to run fast and powerful analytics on large datasets, supporting data-driven decision-making and business intelligence.

Key Features of Redshift

• Automated Backups: Offers computerized backups, snapshots, and data replication to ensure data durability and disaster recovery.
• Scalable Architecture: Provides scalable storage and compute resources, allowing users to start with a small cluster and scale up as needed.
• High Performance: Utilizes columnar storage, data compression, and parallel processing to deliver fast query performance and efficiently handle large volumes of data.
• SQL Interface: This interface supports standard SQL queries and integrates with popular BI tools, making it accessible to users familiar with SQL.

Why Connect Mixpanel to Redshift?

Here are some key reasons to connect the two:

• Custom Reporting: With Redshift, create custom dashboards and reports that blend Mixpanel data with other business data, making it easier to track KPIs and optimize decision-making.
• Centralized Data Warehouse: Integrate Mixpanel event data with other business data stored in Redshift for a unified view of user behavior and business performance.
• Advanced Analytics: Use Redshift’s advanced SQL capabilities to perform complex queries on your Mixpanel data, combining it with data from other sources for richer insights.
• Scalability: Redshift can scale with your growing data needs, ensuring that as your Mixpanel data increases, it can handle large amounts of information efficiently.

Streamline Your Migration Workflow With Hevo

Migrating Mixpanel data to Redshift is complex when you’re handling API extractions, JSON parsing, S3 staging, and COPY operations manually. Hevo simplifies this process by offering a no-code pipeline that does the heavy lifting for you.

Hevo automatically manages schema detection and mapping, so you don’t need to worry about new event properties or nested JSON structures. As Mixpanel’s data evolves, your Redshift tables stay aligned without manual intervention.

Hevo handles retries, rate limits, and error recovery in the background. You can monitor pipeline performance, data freshness, and load health directly from its dashboard.

Moreover, the platform adheres to industry-standard certifications, like SOC 2 Type II, GDPR, and HIPAA, ensuring secure data movement at every stage of the pipeline.

If you’re looking to simplify your Mixpanel to Redshift workflow, book a 1:1 consultation call with Hevo and witness true automation.

FAQs on Mixpanel to Redshift