Display Latest Customer Count in Mixpanel: Daily Chart Options | Mixpanel Community

Display Latest Customer Count in Mixpanel: Daily Chart Options

kapa.ai I'm tracking a get_customers event where I include a customer_count property (e.g., { customer_count: 42 }). I'd like to display the latest customer count per day on a chart — not the sum, average, or max, just the actual value from the most recent event of that day. Is there a way in Mixpanel to:

Show the latest value of a property per time interval (e.g., per day)?
Or simply plot the raw customer_count value from each event trigger without aggregation?

So far I only see aggregation options like Sum, Avg, Max, etc., and I haven’t found a way to show just the final value of the day or raw values per event.

14 comments

kapa.ai
APP
·
·
Please stand by Manish S. as I search the knowledge base. I normally take about 15 seconds or so. 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
kapa.ai
APP
·
·
Based on the knowledge sources provided, there isn't a direct built-in option in Mixpanel's standard reports to show only the latest value of a property per day without aggregation. The standard aggregation options you've encountered (Sum, Avg, Max, etc.) are indeed the primary methods available in the regular reporting interface.However, you have a few potential approaches to achieve what you're looking for:
Option 1: Use JQL (JavaScript Query Language)
You could use Mixpanel's JQL to create a custom query that extracts the latest value per day. JQL provides more flexibility for custom aggregations: LBKSfunction main() { return Events({ from_date: "2024-01-01", to_date: "2024-01-30", event_selectors: [{event: "get_customers"}] }) .groupBy([ mixpanel.numeric_bucket('time', mixpanel.daily_time_buckets)], function(events) { // Sort events by time (descending) events.sort(function(a, b) { return b.time - a.time; }); // Return the customer_count from the most recent event return events.length > 0 ? events[0].properties.customer_count : null; } ); }LBKSThis JQL query groups events by day using mixpanel.daily_time_buckets and returns only the customer_count from the most recent event of each day. JQL Builtins provides the time bucketing functionality needed for this approach.
Option 2: Export Raw Data and Process Externally
You could export the raw event data and process it externally: 1. Use the Raw Event Export API to download your event data 2. Process this data externally to extract the latest value per day
Option 3: Use Snowflake Integration (if available)
If you have Mixpanel's Snowflake integration set up, you could write a SQL query to get exactly what you need: LBKSWITH ranked_events AS ( SELECT TO_DATE(CONVERT_TIMEZONE('UTC','<PROJECT_TIMEZONE> ', time)) as event_date, properties:customer_count::int as customer_count, ROW_NUMBER() OVER (PARTITION BY TO_DATE(CONVERT_TIMEZONE('UTC','<PROJECT_TIMEZONE> ', time)) ORDER BY time DESC) as rn FROM <DB_NAME> .<SCHEMA_NAME> .MP_MASTER_EVENT WHERE event_name = 'get_customers' ) SELECT event_date, customer_count FROM ranked_events WHERE rn = 1 ORDER BY event_date;LBKSThis query would return only the latest customer_count value for each day, as described in the Snowflake integration documentation.Unfortunately, the standard Mixpanel reporting interface doesn't provide a built-in way to show just the raw values or latest value per day without some form of aggregation. You'll need to use one of these more advanced approaches to achieve your specific requirement.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Manish S.
·
·
kapa.ai Is there a way to plot raw property values from events in Mixpanel without any aggregation? For example, I’m tracking a get_customers event with a customer_count property. I want to show each customer_count value exactly as it was sent, per event, on a chart — not as a sum, average, or max. Is that possible within Insights or any other report type?
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
Based on the knowledge sources provided, there isn't a direct way in Mixpanel's standard reports to plot raw property values from events without any aggregation.In Mixpanel Insights, when visualizing event data with properties, you're typically required to select a measurement type (such as total, unique, average, etc.) to aggregate your data. The documentation shows that Insights is designed to visualize trends and compositions within your data using these aggregation methods.Looking at your specific use case of wanting to plot each individual customer_count value exactly as it was sent: 1. Standard Reports: The standard Insights report doesn't provide a way to plot raw property values without aggregation. As you've discovered, you're limited to aggregation options like Sum, Avg, and Max.2. JQL (JavaScript Query Language): You could use JQL to create a custom query that extracts the raw values, but even then, you'd need to export the results rather than visualizing them directly in Mixpanel's interface. JQL provides more flexibility but doesn't solve the visualization challenge within the platform itself Custom JQL Query.3. Data Export: You could export the raw event data using the Raw Event Export API and then visualize it in an external tool: LBKScurl --request GET \ --url '[https://data.mixpanel.com/api/2.0/export?from_date=2024-07-23&to_date=2024-07-24&event=get_customers](https://data.mixpanel.com/api/2.0/export?from_date=2024-07-23&to_date=2024-07-24&event=get_customers)' \ --header 'accept: text/plain' \ --header 'authorization: Basic YOUR_TOKEN'LBKSThis would give you the raw event data including all properties, which you could then visualize in a tool like Excel, Google Sheets, or a more advanced data visualization platform.Unfortunately, Mixpanel's reporting interface is primarily designed for aggregated analytics rather than plotting individual raw event property values. The platform is built to help identify trends and patterns across large datasets, which typically requires some form of aggregation.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Manish S.
·
·
kapa.ai what would you recommend to do to represent this in mixpanel, any other alternatives? i want to see the timeseries graph with number of customers over time when the event is fired
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
Based on your need to visualize the number of customers over time when your get_customers event is fired, I can suggest a few alternatives within Mixpanel:
1. Use Insights with "Last" Aggregation
If your Mixpanel project has the "Last" aggregation option available, you could use it to show the most recent value of the customer_count property for each time interval. This would give you a time series showing how the customer count changes over time.
2. Create a Custom Event for Each Count Value
Instead of sending a single get_customers event with a property, you could modify your implementation to send a specific event each time with the count in the event name (e.g., customer_count_42). Then you could use Insights to count these events over time. This is less ideal but would work within Mixpanel's standard reporting.
3. Use JQL for Custom Visualization
You could use JQL to extract exactly what you need and then export the results: LBKSfunction main() { return Events({ from_date: "2024-01-01", to_date: "2024-01-30", event_selectors: [{event: "get_customers"}] }) .groupBy([ mixpanel.numeric_bucket('time', mixpanel.daily_time_buckets) ], function(events) { // Sort events by time (descending) events.sort(function(a, b) { return b.time - a.time; }); // Return the customer_count from the most recent event return events.length > 0 ? events[0].properties.customer_count : null; }); }LBKSThis approach uses the JQL Builtins functionality to group by time buckets.
4. Export and Visualize Externally
Use the Raw Event Export API to export your event data and visualize it in a tool that better supports your specific needs: LBKScurl --request GET \ --url '[https://data.mixpanel.com/api/2.0/export?from_date=2024-01-01&to_date=2024-01-30&event=get_customers](https://data.mixpanel.com/api/2.0/export?from_date=2024-01-01&to_date=2024-01-30&event=get_customers)' \ --header 'accept: text/plain' \ --header 'authorization: Basic YOUR_TOKEN'LBKSThen use Excel, Google Sheets, Tableau, or another visualization tool to create exactly the time series you want.
5. Consider a Different Event Structure
Instead of tracking a get_customers event with a count property, you could track individual customer events. This would allow you to use Mixpanel's standard "Total" or "Unique" aggregations to show customer counts over time.The most straightforward approach would be option #3 or #4, as they give you the most control over exactly how your data is processed and displayed. Option #1 would be ideal if the "Last" aggregation is available in your Mixpanel plan.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Manish S.
·
·
kapa.ai I liked the 2nd approach, could you elaborate on how I would be able to club all these events into one as the event name would be different for each count?
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
I'd be happy to elaborate on how you could implement the second approach and then consolidate these different events for analysis in Mixpanel.
Creating Custom Events for Each Count Value
In this approach, you'd send a specific event each time with the count in the event name (e.g., customer_count_42). However, as you correctly pointed out, this creates a challenge because you'd have different event names for each count.
Consolidating These Events Using Lexicon
Mixpanel's Lexicon feature allows you to merge events that serve the same function but have different names. Here's how you could use it: 1. Track your events with the count in the name (e.g., customer_count_42, customer_count_43, etc.)2. Use Mixpanel's Lexicon to merge these events: - Go to Data Management > Lexicon - Select all your customer_count_** events - Click the "Merge" icon that appears - In the "Merge Events" window, specify which event Mixpanel should consider as the new unique event (e.g., customer_count) - Click MergeAs explained in the Lexicon documentation: > By merging "Purchase" and "purchase item" into a single event named "Purchase", you would no longer need to query each event separately since Mixpanel would recognize both "Purchase" and "purchase item" as a unique event.This same principle applies to your customer_count_** events. After merging, Mixpanel will treat all these events as a single event type, allowing you to analyze them together.
Alternative: Use Event Property Instead of Event Name
A more practical approach might be to: 1. Send a single event type like customer_count_update 2. Include the actual count as a property (e.g., count: 42) 3. Use the "Last" aggregation in Insights (if available) to see the most recent count valueThis would be more maintainable than creating and merging many different event types, especially as your customer count grows and changes over time.
Best Practice Recommendation
According to Mixpanel's best practices: > Instead of tracking multiple events Add Shirt to Cart, Add Hoodie to Cart, and Add Socks to Cart, track a Add to Cart event with a Item property set to "Shirt", or "Hoodie", or "Socks".Following this principle, tracking a single customer_count_update event with a count property would be more aligned with Mixpanel's recommended approach than creating different events for each count value.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.
Manish S.
·
·
kapa.ai Use the "Last" aggregation in Insights (if available) to see the most recent count value, I dont see this option how to unlock it?
kapa.ai
APP
·
·
Let me check the knowledge base for you again. One moment! 📚
kapa.ai
APP
·
·
Based on the knowledge sources provided, I don't see specific information about a "Last" aggregation option in Mixpanel Insights or how to unlock it. The documentation shows various metric types and aggregation options in Insights, but doesn't explicitly mention a "Last" aggregation feature.Looking at the available measurement options in Insights, the documentation mentions: - For Event metrics: total number of occurrences, unique users, or aggregates of a property - For Profile metrics: number of profiles or aggregates on properties - For other metric types: various default measurements like conversion rate for funnelsThe available aggregation types mentioned in the documentation include sum, average, and max, but there's no explicit mention of a "Last" aggregation option.If you're not seeing this option in your Mixpanel interface, it's possible that: 1. This feature might not be available in your current Mixpanel plan 2. It might be a newer feature not yet reflected in the documentation provided 3. It might be available under a different name or in a different report typeI would recommend reaching out to Mixpanel Support directly to inquire about this specific feature and whether it's available for your account. You could also check the Mixpanel Community Slack to see if other users have found solutions for tracking the most recent property values.In the meantime, you might want to consider the other approaches we discussed previously, such as using JQL for custom visualization or exporting the data to visualize externally.
kapa.ai is still learning and improving, please let me know how I did by reacting below.
ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

14 comments

Option 1: Use JQL (JavaScript Query Language)

Option 2: Export Raw Data and Process Externally

Option 3: Use Snowflake Integration (if available)

1. Use Insights with "Last" Aggregation

2. Create a Custom Event for Each Count Value

3. Use JQL for Custom Visualization

4. Export and Visualize Externally

5. Consider a Different Event Structure

Creating Custom Events for Each Count Value

Consolidating These Events Using Lexicon

Alternative: Use Event Property Instead of Event Name

Best Practice Recommendation