want to analyse how the proportion of purchases made by different age groups has changed over time. For example, I want to see if 18-year-olds made up a larger share of total purchases in 2024 compared to 2020. I have users' birth dates—how can I calculate their age at the time of purchase and compare purchase proportions by age across years?
Hi Lara M., Please stand by as I search the Mixpanel knowledge base for an AI-powered answer. I normally take about 15 seconds or so. In case AI cannot answer your question, feel free to reach out to Mixpanel support at mixpanel.com/get-support. If you have follow-up questions for this thread, please tag me again with your question: @kapa.ai <follow-up question> 👋
Feel free to ask a follow-up question once I've answered by mentioning me with @kapa.ai
To analyze how the proportion of purchases made by different age groups has changed over time in Mixpanel, you can use a combination of cohorts, custom buckets, and comparison features. Here's how to approach this:
1. Create Age Buckets Using Custom Buckets
First, you'll need to calculate users' ages at the time of purchase and group them into meaningful buckets: 1. In Insights, create a report tracking your purchase events 2. Add a breakdown by the birth date property 3. Change the data type to numeric (if it's not already) 4. Use Custom Buckets to create meaningful age groupsMixpanel's Custom Buckets feature allows you to group numeric values into meaningful segments, such as age ranges like 18-24, 25-34, etc. Mixpanel's documentation explains how to use this feature to create specific segments.
2. Compare Proportions Over Time
To compare how these age groups' proportions have changed over time: 1. In your Insights report, select your purchase event 2. Break down by your age buckets 3. Use the "Compare" menu and select "Percentage of Overall"This will show each age group as a percentage of the total, allowing you to see how their proportions have changed. As noted in the documentation: "This option allows users to see how segments perform as a percentage of the total" Mixpanel's comparison documentation.
3. Compare Different Time Periods
To compare different years (e.g., 2020 vs 2024): 1. Set your date range to include all relevant years 2. Group by month or quarter to see the trend over time 3. Alternatively, use the time comparison feature to directly compare specific periods
Advanced Approach Using JQL
For more complex analysis, you could use Mixpanel's JQL (JavaScript Query Language) to create custom buckets based on calculated age at purchase time: LBKSfunction main() { return Events({ from_date: "2020-01-01", to_date: "2024-12-31", event_selectors: [{event: "purchase"}] }) .groupBy(["time.year", mixpanel.numeric_bucket( function(event) { // Calculate age at time of purchase var birthDate = new Date(event.properties.birth_date); var purchaseDate = new Date(event.time); var age = purchaseDate.getFullYear() - birthDate.getFullYear(); return age; }, [0, 18, 25, 35, 45, 55, 65])], // Age buckets mixpanel.reducer.count()) .map(function(result) { // Calculate percentage of total for each year // Additional processing here return result; }); }LBKSThis approach uses mixpanel.numeric_bucket() to group users into age buckets as described in Mixpanel's JQL documentation.By analyzing the results, you'll be able to see if 18-year-olds (or any other age group) made up a larger share of total purchases in 2024 compared to 2020.
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.
Let me check the knowledge base for you again. One moment! 📚
I'll help you build a JQL query to analyze how the proportion of purchases made by different age groups has changed over time using your "Birthday" field and "Complete Purchase" event.Here's a JQL query that calculates the age at purchase time, groups purchases by year and age buckets, and then computes the proportion of each age group: ```function main() { return Events({ from_date: "2020-01-01", to_date: "2024-12-31", event_selectors: [{event: "Complete Purchase"}] }) .groupBy( [ // Extract the year from the event timestamp function(event) { return new Date(event.time).getFullYear(); }, // Calculate age at time of purchase and bucket it mixpanel.numeric_bucket( function(event) { var birthDate = new Date(event.properties.Birthday); var purchaseDate = new Date(event.time); var age = purchaseDate.getFullYear() - birthDate.getFullYear();
// Adjust age if birthday hasn't occurred yet in the purchase year
var hasBirthdayOccurred = (
purchaseDate.getMonth() > birthDate.getMonth() ||
(purchaseDate.getMonth() === birthDate.getMonth() &&
purchaseDate.getDate() > = birthDate.getDate())
);
if (!hasBirthdayOccurred) {
age--;
}
return age;
},
[0, 18, 25, 35, 45, 55, 65, 100] // Age buckets: 0-17, 18-24, 25-34, etc.
)
],
mixpanel.reducer.count()
) // Process the results to calculate proportions .reduce(function(memo, result) { // Initialize the memo structure if it's the first iteration if (!memo.yearTotals) { memo.yearTotals = {}; memo.results = []; }
var year = result.key[0];
var ageBucket = result.key[1];
var count = result.value;
// Track total purchases per year
if (!memo.yearTotals[year]) {
memo.yearTotals[year] = 0;
}
memo.yearTotals[year] += count;
// Store the raw results for later processing
memo.results.push({
year: year,
ageBucket: ageBucket,
count: count
});
return memo;
}) // Calculate proportions and format final output .map(function(memo) { var proportions = [];
// Calculate the proportion for each age bucket within each year
memo.results.forEach(function(result) {
var yearTotal = memo.yearTotals[result.year];
var proportion = yearTotal > 0 ? (result.count / yearTotal) : 0;
proportions.push({
year: result.year,
ageBucket: result.ageBucket,
count: result.count,
total: yearTotal,
proportion: proportion,
percentage: (proportion * 100).toFixed(2) + '%'
});
});
return proportions;
}); }```This query:
1. Selects all "Complete Purchase" events from 2020 to 2024 2. Groups them by year and age buckets 3. Calculates the age at purchase time using the "Birthday" field 4. Adjusts the age calculation to account for whether the birthday has occurred in the purchase year 5. Creates age buckets (0-17, 18-24, 25-34, etc.) 6. Calculates the proportion of purchases made by each age group within each yearThe result will show you how the proportion of purchases made by different age groups has changed over the years, allowing you to see if 18-year-olds (or any other age group) made up a larger share of total purchases in recent years compared to earlier years.Note that JQL is in maintenance mode as mentioned in Mixpanel's documentation, so while this solution works, you might want to consider using Mixpanel's Data Pipelines for more complex analyses in the future.
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.
Let me check the knowledge base for you again. One moment! 📚
I see the error in the JQL query. The issue is in the reduce function where we're trying to access result.key[0] but result.key is undefined. Let's fix the query: ```function main() {
return Events({
from_date: "2020-01-01",
to_date: "2024-12-31",
event_selectors: [{event: "Complete Purchase"}]
})
.groupBy(
[
// Extract the year from the event timestamp
function(event) {
return new Date(event.time).getFullYear();
},
// Calculate age at time of purchase and bucket it
mixpanel.numeric_bucket(
function(event) {
var birthDate = new Date(event.properties.Birthday);
var purchaseDate = new Date(event.time);
var age = purchaseDate.getFullYear() - birthDate.getFullYear();
// Adjust age if birthday hasn't occurred yet in the purchase year
var hasBirthdayOccurred = (
purchaseDate.getMonth() > birthDate.getMonth() ||
(purchaseDate.getMonth() === birthDate.getMonth() &&
purchaseDate.getDate() > = birthDate.getDate())
);
if (!hasBirthdayOccurred) {
age--;
}
return age;
},
[0, 18, 25, 35, 45, 55, 65, 100] // Age buckets: 0-17, 18-24, 25-34, etc.
)
],
mixpanel.reducer.count()
);
}LBKSLBKSThe error occurred because we were trying to do additional processing with `.reduce()` and `.map()` functions that were too complex for JQL's processing model. Let's simplify the approach.LBKSThis simplified query will: 1. Group "Complete Purchase" events by year and age bucket 2. Count the number of purchases in each groupLBKSAfter running this query, you'll get results showing the count of purchases for each year and age bucket combination. You can then calculate the proportions manually or use Mixpanel's visualization options to see the relative percentages.LBKSIf you need to calculate the proportions within JQL, we can try a different approach using chained aggregations as described in [Mixpanel's JQL documentation](https://docs.mixpanel.com/docs/reports/apps/jql). Here's an alternative approach: LBKSLBKSfunction main() {
// First get counts by year and age bucket
var results = Events({
from_date: "2020-01-01",
to_date: "2024-12-31",
event_selectors: [{event: "Complete Purchase"}]
})
.groupBy(
[
function(event) {
return new Date(event.time).getFullYear();
},
mixpanel.numeric_bucket(
function(event) {
var birthDate = new Date(event.properties.Birthday);
var purchaseDate = new Date(event.time);
var age = purchaseDate.getFullYear() - birthDate.getFullYear();
if (purchaseDate.getMonth() < birthDate.getMonth() ||
(purchaseDate.getMonth() === birthDate.getMonth() &&
purchaseDate.getDate() < birthDate.getDate())) { age--; } return age; }, [0, 18, 25, 35, 45, 55, 65, 100] ) ], mixpanel.reducer.count() );
return results; }```This should work without errors. After getting these results, you can analyze the proportions by comparing the counts for each age bucket within each year.
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.
Let me check the knowledge base for you again. One moment! 📚
Looking at your output, I can see that the JQL query returned results, but they don't appear to be correctly bucketing users by age. The issue is that all your results have key.1 as 100, which corresponds to the upper bound of our age bucket array [0, 18, 25, 35, 45, 55, 65, 100].This suggests that either: 1. All your users are being categorized in the oldest age bucket (65-100)
2. There's an issue with the age calculation in the query
3. The Birthday field might not be in the expected format or might be missing for many usersLet's modify the query to debug what's happening: ```function main() {
return Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
})
.groupBy(
[
// Year of purchase
function(event) {
return new Date(event.time).getFullYear();
},
// Log the actual calculated age before bucketing
function(event) {
var birthDate = new Date(event.properties.Birthday);
var purchaseDate = new Date(event.time);
var age = purchaseDate.getFullYear() - birthDate.getFullYear();
// Adjust age if birthday hasn't occurred yet in the purchase year
if (purchaseDate.getMonth() < birthDate.getMonth() ||
(purchaseDate.getMonth() === birthDate.getMonth() &&
purchaseDate.getDate() < birthDate.getDate())) {
age--;
}
// Return the raw age to see what's being calculated
return age;
}
],
mixpanel.reducer.count()
);
}LBKSLBKSThis modified query will show the actual calculated ages instead of the buckets, which should help identify what's happening. If most ages are above 65, that would explain why they're all falling into the 65-100 bucket.LBKSIf you want to ensure the `Birthday` property is being read correctly, you could also try: LBKSLBKSfunction main() {
return Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
})
.groupBy(
[
// Sample some birthday values to verify format
function(event) {
return event.properties.Birthday ? String(event.properties.Birthday).substring(0, 10) : "missing";
}
],
mixpanel.reducer.count()
);
}```This will show you the distribution of birthday values, which can help verify if they're in the expected format.As noted in <https://docs.mixpanel.com/docs/reports/apps/jql|Mixpanel's JQL documentation>, JQL is in maintenance mode, so you might encounter some limitations when working with complex queries.Let me know what these debugging queries reveal, and we can adjust the approach accordingly.
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.
kapa.ai I just did this: function main() { return Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }) .groupBy( [ // Sample some birthday values to verify format function(event) { return event.properties.Birthday ? String(event.properties.Birthday).substring(0, 10) : "missing"; } ], mixpanel.reducer.count() ); } and it returned: key.0 value missing 14,197,873
Let me check the knowledge base for you again. One moment! 📚
I see the issue now. The query results show that all 14,197,873 events have "missing" for the Birthday property, which explains why our age bucketing wasn't working correctly. This means that either: 1. The Birthday property isn't being tracked with your Complete Purchase events 2. The property might be named differently (perhaps "birth_date" or "DOB" instead of "Birthday") 3. The Birthday property might be stored as a user property rather than an event propertyLet's try a few approaches to resolve this:
1. Check if Birthday is a user property instead of an event property
If Birthday is stored as a user property (profile property) rather than an event property, we need to use a different approach: LBKSfunction main() { return Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }) .groupBy( [ // Year of purchase function(event) { return new Date(event.time).getFullYear(); }, // Try to access Birthday as a user property function(event) { return event.properties.$properties && event.properties.$properties.Birthday ? String(event.properties.$properties.Birthday).substring(0, 10) : "missing"; } ], mixpanel.reducer.count() ); }LBKS
2. Check for alternative property names
You might want to examine what properties are actually available on your Complete Purchase events: LBKSfunction main() { return Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}], limit: 10 }) .map(function(event) { // Return just the property names to see what's available return Object.keys(event.properties); }); }LBKSThis will show you the property names available on your Complete Purchase events, which might help identify if the birthday information is stored under a different name.
3. If you know the user ID, try looking up the profile directly
If you know that Birthday is definitely a user property and you have a specific user ID to test with: LBKSfunction main() { return People({ user_selector: { // Replace with an actual user ID from your data distinct_id: "your-test-user-id" } }) .map(function(user) { // Return all user properties to see what's available return user.properties; }); }LBKSOnce we identify where the Birthday data is actually stored, we can modify our original query to correctly calculate age proportions by year.Let me know what these debugging queries reveal, and we can adjust our approach accordingly.Mixpanel's JQL documentation provides more information about working with JQL queries and accessing different types of properties.Would you like to try any of these approaches to locate your Birthday data?
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.