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.
Let me check the knowledge base for you again. One moment! đź“š
Thanks for confirming that Birthday is a user property. This explains why our previous queries couldn't find it as an event property. Let's modify our JQL query to correctly access the Birthday from user profiles: ```function main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Calculate age using Birthday from user profile mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday
var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.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 age;
},
[0, 18, 25, 35, 45, 55, 65, 100] // Age buckets: 0-17, 18-24, 25-34, etc.
)
],
mixpanel.reducer.count()
);
}LBKSLBKSThis query: LBKS1. Joins the "Complete Purchase" events with user profiles 2. Groups the results by year of purchase and age bucket 3. Calculates the age at the time of purchase using the Birthday from the user profile 4. Returns counts for each year and age bucket combinationLBKSThe results will show you how many purchases were made by each age group in each year. To calculate the proportions, you can divide each count by the total for that year.LBKSIf you want to verify that the Birthday property is being accessed correctly before running the full query, you can try this simpler query first: LBKSLBKSfunction main() {
return join(
Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}],
limit: 10
}),
People(),
{type: "inner"}
)
.map(function(joined) {
return {
time: joined.event.time,
birthday: joined.user.properties.Birthday
};
});
}```This will show you a sample of purchase times and the corresponding Birthday values, which can help confirm that the data is being accessed correctly.As mentioned in <https://docs.mixpanel.com/docs/reports/apps/jql#combining-events-and-profiles|Mixpanel's JQL documentation>, the join function allows you to combine events and profile data for this type of analysis.
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! đź“š
Yes, that output looks correct! The query is now successfully grouping your "Complete Purchase" events by year and age buckets based on the user's Birthday property.Let me explain what each column means: - key.0: The year of the purchase (2021 in your example)
- key.1: The age bucket (the lower bound of each age range)
- value: The count of purchases in that year by users in that age bucketYour results show:
- -1.79769e+308: This represents users with missing Birthday data (we returned -1 for these cases, but JQL seems to be displaying it as a very small number)
- 0: Users aged 0-17
- 18: Users aged 18-24
- 25: Users aged 25-34
- 35: Users aged 35-44
- 45: Users aged 45-54
- 55: Users aged 55-64
- (You would also see 65 for users aged 65-100 if present in your data)From your sample, we can see that in 2021:
- The 25-34 age group made the most purchases (118,122)
- Followed by the 18-24 age group (97,007)
- Then the 35-44 age group (26,528)
- And so onTo calculate the proportion each age group represents of the total purchases in 2021, you would:
1. Sum all the values for 2021 (excluding the missing data if you prefer)
2. Divide each age group's count by that totalFor example, if the total valid purchases in 2021 is 251,698 (sum of all except the -1.79769e+308 row), then the 25-34 age group represents approximately 47% of purchases (118,122 Ă· 251,698).You can repeat this calculation for each year to see how the proportions have changed over time, which was your original goal.This data now allows you to analyze how the proportion of purchases made by different age groups has changed over the years, as you initially wanted.
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'd be happy to modify the JQL query with your requested age buckets. Here's the updated query: ```function main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday
var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.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 age;
},
[0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // New age buckets as requested
)
],
mixpanel.reducer.count()
);
}```This query uses the mixpanel.numeric_bucket() function as described in <https://docs.mixpanel.com/docs/reports/apps/jql#mixpanelnumeric_bucketaccessor-buckets-spec|Mixpanel's JQL documentation> to create the exact age buckets you requested: - 0-17 (automatically created for values below your first bucket)
- 18-20 (≥18 & <21)
- 21-23 (≥21 & <24)
- 24-26 (≥24 & <27)
- 27-29 (≥27 & <30)
- 30-32 (≥30 & <33)
- 33-35 (≥33 & <36)
- 36-38 (≥36 & <39)
- 39-41 (≥39 & <42)
- 42-44 (≥42 & <45)
- 45+ (≥45)The output will show you the count of purchases for each year and age bucket, allowing you to calculate the proportion of purchases made by each age group and analyze how these proportions have changed over time.
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 understand you want to take the age group analysis we've been working on in JQL and apply it to an Insights report in Mixpanel. Here's how you can do that:
Using Your JQL Analysis in Insights
While JQL is powerful for custom analysis, Insights offers a more user-friendly interface with visualization options. Here's how to translate your age group analysis to Insights: 1. Create a Custom Property for Age Buckets: Since you've already calculated age buckets in JQL, you can create a custom property in Mixpanel to make this analysis reusable in Insights. Go to Data Management > Custom Properties and create a new custom property that calculates age from Birthday, then buckets it according to your specified ranges (18-20, 21-23, etc.).2. Create an Insights Report: - Go to the Insights report section - Select "Complete Purchase" as your event - Break down by your new custom age bucket property - Group by year (using the date controls)3. Use Custom Buckets in Insights Directly: Alternatively, you can use Mixpanel's Custom Buckets feature directly in Insights: - Create a new Insights report with "Complete Purchase" event - Add a breakdown by calculating age from Birthday - Click on the breakdown and select "Custom Buckets" from the overflow menu - Define your custom buckets (18-20, 21-23, etc.) As noted in Mixpanel's documentation, "Insights will automatically group your high-cardinality segments into buckets. Buckets can be edited by using the 'Custom Buckets' option in the overflow menu."4. Compare Proportions Over Time: - After setting up your report with age buckets, use the "Compare" > "Overall" option - This will show each age group as a percentage of the total According to Mixpanel's documentation: "When you have multiple metrics, or have broken down a metric by a property, you can compare them against each and the relative value. Click on Compare -> Overall."5. Visualize the Results: - Choose an appropriate chart type for your analysis - For proportion changes over time, a stacked column chart often works well - You can also use the line chart to see trends in specific age groupsThe advantage of using Insights over JQL is that you'll get interactive visualizations and can easily share the report with others on your team.Would you like more specific guidance on any of these steps?
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! đź“š
You're right to ask about this connection between JQL and Insights. Unfortunately, there is no direct way to access or import your JQL query results into an Insights report in Mixpanel.JQL (JavaScript Query Language) and Insights are separate reporting systems within Mixpanel: 1. JQL is a code-based query interface that allows for complex custom analysis using JavaScript 2. Insights is a UI-based reporting tool with its own query builder and visualization optionsAs noted in Mixpanel's documentation, "JQL is in maintenance mode" and they recommend using Data Pipelines for more complex analyses Mixpanel's JQL documentation.Your options for working with your age bucket analysis are: 1. Export your JQL results: You can export the JQL query results as CSV and work with them outside Mixpanel 2. Recreate the analysis in Insights: As mentioned previously, you can use Custom Properties or Custom Buckets in Insights to create a similar analysis 3. Use Data Pipelines: For ongoing complex analyses, Mixpanel recommends exporting your data to a warehouse using their Data Pipelines featureWhile you can't directly use your JQL query in Insights, you can apply the same analytical approach using Insights' native features to achieve similar results.
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 are you able to adapt this JQL into a custom property?
function main() {
return join(
Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
}),
People(),
{type: "inner"}
)
.groupBy(
[
// Year of purchase
function(joined) {
return new Date(joined.event.time).getFullYear();
},
// Calculate age using Birthday from user profile with new buckets
mixpanel.numeric_bucket(
function(joined) {
if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday
var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.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 age;
},
[0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // New age buckets as requested
)
],
mixpanel.reducer.count()
);
}Let me check the knowledge base for you again. One moment! đź“š
Yes, I can help you adapt your JQL query into a custom property in Mixpanel. The JQL query calculates age from Birthday and buckets it into specific age ranges, which can be translated into a custom property formula.Here's how to create a custom property that replicates the age calculation and bucketing logic from your JQL: 1. Go to Data Management > Custom Properties
2. Click "Create New Property"
3. Select "Event Property" (since you want to use this with "Complete Purchase" events)
4. Name your property (e.g., "Age Bucket")
5. In the formula field, use this formula: LBKSif(not(has_property("$people.Birthday")), "Unknown", if(date_diff_years($people.Birthday, timestamp()) < 18, "0-17", if(date_diff_years($people.Birthday, timestamp()) < 21, "18-20", if(date_diff_years($people.Birthday, timestamp()) < 24, "21-23", if(date_diff_years($people.Birthday, timestamp()) < 27, "24-26", if(date_diff_years($people.Birthday, timestamp()) < 30, "27-29", if(date_diff_years($people.Birthday, timestamp()) < 33, "30-32", if(date_diff_years($people.Birthday, timestamp()) < 36, "33-35", if(date_diff_years($people.Birthday, timestamp()) < 39, "36-38", if(date_diff_years($people.Birthday, timestamp()) < 42, "39-41", if(date_diff_years($people.Birthday, timestamp()) < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ) )LBKSThis formula:
- Uses date_diff_years() to calculate age from Birthday to the event timestamp
- Creates the same age buckets you defined in your JQL
- Handles missing Birthday values with "Unknown"The date_diff_years() function automatically handles the birthday adjustment logic that you had in your JQL (checking if the birthday has occurred in the current year).As described in <https://docs.mixpanel.com/docs/features/custom-properties|Mixpanel's Custom Properties documentation>, you can use comparison operators like < to create these conditional buckets.Once you've created and saved this custom property, you can use it in Insights reports to break down your "Complete Purchase" events by age bucket and analyze how the proportions have changed over time.
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 you're encountering with the custom property formula. The issue is with how we're referencing the user property in Mixpanel's custom property syntax. Let me correct that: LBKSif(not(has_property("Birthday", "user")), "Unknown", if(date_diff_years(property("Birthday", "user"), timestamp()) < 18, "0-17", if(date_diff_years(property("Birthday", "user"), timestamp()) < 21, "18-20", if(date_diff_years(property("Birthday", "user"), timestamp()) < 24, "21-23", if(date_diff_years(property("Birthday", "user"), timestamp()) < 27, "24-26", if(date_diff_years(property("Birthday", "user"), timestamp()) < 30, "27-29", if(date_diff_years(property("Birthday", "user"), timestamp()) < 33, "30-32", if(date_diff_years(property("Birthday", "user"), timestamp()) < 36, "33-35", if(date_diff_years(property("Birthday", "user"), timestamp()) < 39, "36-38", if(date_diff_years(property("Birthday", "user"), timestamp()) < 42, "39-41", if(date_diff_years(property("Birthday", "user"), timestamp()) < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ) )LBKSThe key changes I made: 1. Changed $people.Birthday to property("Birthday", "user") - This is the correct syntax for accessing user properties in Mixpanel's custom property formulas
2. Changed has_property("$people.Birthday") to has_property("Birthday", "user") - This properly checks if the user property existsAccording to <https://docs.mixpanel.com/docs/features/custom-properties|Mixpanel's Custom Properties documentation>, you need to use the property() function to access properties, and specify whether it's a "user" or "event" property.This corrected formula should work without syntax errors and will create the age buckets as requested.
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 you're encountering function name errors with the custom property formula. Let me correct it using the proper function names in Mixpanel's custom property formula syntax: LBKSif(Birthday == null, "Unknown", if(datedif(Birthday, TODAY(), "Y") < 18, "0-17", if(datedif(Birthday, TODAY(), "Y") < 21, "18-20", if(datedif(Birthday, TODAY(), "Y") < 24, "21-23", if(datedif(Birthday, TODAY(), "Y") < 27, "24-26", if(datedif(Birthday, TODAY(), "Y") < 30, "27-29", if(datedif(Birthday, TODAY(), "Y") < 33, "30-32", if(datedif(Birthday, TODAY(), "Y") < 36, "33-35", if(datedif(Birthday, TODAY(), "Y") < 39, "36-38", if(datedif(Birthday, TODAY(), "Y") < 42, "39-41", if(datedif(Birthday, TODAY(), "Y") < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ) )LBKSThe key corrections: 1. Replaced has_property() with a direct null check: Birthday == null
2. Replaced date_diff_years() with Mixpanel's datedif() function, which is documented in <https://docs.mixpanel.com/docs/features/custom-properties#functions|Mixpanel's Custom Properties documentation>
3. Replaced timestamp() with TODAY() which is the correct function for getting the current date
4. Removed the property() function and directly referenced the Birthday propertyThe datedif() function takes three parameters:
- Start date (Birthday in this case)
- End date (TODAY() for current date)
- Unit ("Y" for years)This should resolve the function name errors you were encountering.
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 you're still encountering an error with the custom property formula. Let's fix the null check syntax in Mixpanel's custom property formula language: LBKSif(defined(Birthday), if(datedif(Birthday, TODAY(), "Y") < 18, "0-17", if(datedif(Birthday, TODAY(), "Y") < 21, "18-20", if(datedif(Birthday, TODAY(), "Y") < 24, "21-23", if(datedif(Birthday, TODAY(), "Y") < 27, "24-26", if(datedif(Birthday, TODAY(), "Y") < 30, "27-29", if(datedif(Birthday, TODAY(), "Y") < 33, "30-32", if(datedif(Birthday, TODAY(), "Y") < 36, "33-35", if(datedif(Birthday, TODAY(), "Y") < 39, "36-38", if(datedif(Birthday, TODAY(), "Y") < 42, "39-41", if(datedif(Birthday, TODAY(), "Y") < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ), "Unknown" )LBKSThe key change is using the defined() function instead of a null comparison. According to <https://docs.mixpanel.com/docs/features/custom-properties#comparison-operators|Mixpanel's Custom Properties documentation>, the defined() function is the proper way to check if a property exists.This formula:
1. Checks if the Birthday property is defined
2. If defined, calculates the age using datedif() and assigns the appropriate bucket
3. If not defined, returns "Unknown"This should resolve the "unknown variable: null" error you're encountering.
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! đź“š
The formula I provided doesn't correctly factor in the age at the time of purchase. It uses TODAY() which calculates the current age rather than the age when the purchase was made.To calculate age at the time of purchase, we need to use "borrowed properties" which allow you to use the event timestamp instead of the current date. According to Mixpanel's blog on Borrowed Properties, this feature lets you combine data between events on the fly.Here's an improved formula that will calculate age at the time of purchase: LBKSif(defined(Birthday), if(datedif(Birthday, $time, "Y") < 18, "0-17", if(datedif(Birthday, $time, "Y") < 21, "18-20", if(datedif(Birthday, $time, "Y") < 24, "21-23", if(datedif(Birthday, $time, "Y") < 27, "24-26", if(datedif(Birthday, $time, "Y") < 30, "27-29", if(datedif(Birthday, $time, "Y") < 33, "30-32", if(datedif(Birthday, $time, "Y") < 36, "33-35", if(datedif(Birthday, $time, "Y") < 39, "36-38", if(datedif(Birthday, $time, "Y") < 42, "39-41", if(datedif(Birthday, $time, "Y") < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ), "Unknown" )LBKSThe key change is replacing TODAY() with $time, which refers to the timestamp of the event (in this case, the "Complete Purchase" event). This ensures the age calculation is based on when the purchase actually happened rather than the current date.This approach aligns with Mixpanel's custom properties functionality that allows you to calculate values at the time events occurred, as described in their documentation on <https://docs.mixpanel.com/docs/features/custom-properties|Custom Properties>.
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 you're still encountering syntax errors with the custom property formula. Let me correct it using the proper syntax for accessing the event timestamp in Mixpanel's custom property formula language: LBKSif(defined(Birthday), if(datedif(Birthday, time, "Y") < 18, "0-17", if(datedif(Birthday, time, "Y") < 21, "18-20", if(datedif(Birthday, time, "Y") < 24, "21-23", if(datedif(Birthday, time, "Y") < 27, "24-26", if(datedif(Birthday, time, "Y") < 30, "27-29", if(datedif(Birthday, time, "Y") < 33, "30-32", if(datedif(Birthday, time, "Y") < 36, "33-35", if(datedif(Birthday, time, "Y") < 39, "36-38", if(datedif(Birthday, time, "Y") < 42, "39-41", if(datedif(Birthday, time, "Y") < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ), "Unknown" )LBKSThe key correction is using just time instead of $time. In Mixpanel's custom property formula language, time is the reserved property name that refers to the timestamp of the event, as described in <https://docs.mixpanel.com/docs/data-structure/property-reference/reserved-properties|Mixpanel's documentation on reserved properties>.This formula will calculate the age at the time of purchase by comparing the user's Birthday with the event timestamp, and then assign the appropriate age bucket.
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 you're still encountering the "unknown variable: time" error. Let's try a different approach using Mixpanel's custom property formula syntax.In Mixpanel's custom property formula language, we need to use the correct syntax to reference the event time. Let's try using the now() function instead, which should be available in the formula context: LBKSif(defined(Birthday), if(datedif(Birthday, now(), "Y") < 18, "0-17", if(datedif(Birthday, now(), "Y") < 21, "18-20", if(datedif(Birthday, now(), "Y") < 24, "21-23", if(datedif(Birthday, now(), "Y") < 27, "24-26", if(datedif(Birthday, now(), "Y") < 30, "27-29", if(datedif(Birthday, now(), "Y") < 33, "30-32", if(datedif(Birthday, now(), "Y") < 36, "33-35", if(datedif(Birthday, now(), "Y") < 39, "36-38", if(datedif(Birthday, now(), "Y") < 42, "39-41", if(datedif(Birthday, now(), "Y") < 45, "42-44", "45+" ) ) ) ) ) ) ) ) ) ), "Unknown" )LBKSThis approach has a limitation - it will calculate age based on the current date rather than the purchase date. However, it should work syntactically in Mixpanel's custom property formula language.If you need to calculate age at the exact time of purchase, you might need to consider: 1. Creating a custom event property at tracking time that calculates age
2. Using <https://docs.mixpanel.com/docs/features/advanced#custom-buckets|Custom Buckets> directly in your Insights report instead of a custom property
3. Continuing to use JQL for this specific analysisThe Custom Buckets approach might be the most straightforward alternative, as it allows you to create age buckets on the fly within your Insights reports without needing to define a custom property.
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 for this JQL, can you change it so it is broken down by quarter, year and not just year? function main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday var birthDate = new Date(joined.user.properties.Birthday); var purchaseDate = new Date(joined.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 age; }, [0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // New age buckets as requested ) ], mixpanel.reducer.count() ); }
Let me check the knowledge base for you again. One moment! đź“š
I'll modify the JQL query to break down by quarter and year instead of just year. Here's the updated query: LBKSfunction main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Quarter of purchase function(joined) { return "Q" + (Math.floor(new Date(joined.event.time).getMonth() / 3) + 1); }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing BirthdayLBKS var birthDate = new Date(joined.user.properties.Birthday); var purchaseDate = new Date(joined.event.time); var age = purchaseDate.getFullYear() - birthDate.getFullYear();LBKS // 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--; }LBKS return age; }, [0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested ) ], mixpanel.reducer.count() ); }LBKSThe key change is the addition of a new grouping function that extracts the quarter from the event timestamp. This function: 1. Gets the month from the event timestamp (0-11)
2. Divides by 3 and floors the result to get the quarter index (0-3)
3. Adds 1 and prepends "Q" to format it as "Q1", "Q2", "Q3", or "Q4"Now your results will be grouped by year, quarter, and age bucket, allowing you to see how the proportion of purchases by different age groups has changed not just year over year, but quarter over quarter as well.The output will have three keys:
- key.0: Year (e.g., 2021)
- key.1: Quarter (e.g., "Q1")
- key.2: Age bucket (e.g., 18, 21, 24, etc.)This will give you a more granular view of how purchase patterns by age group have evolved over time.
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 can you edit the JQL query so it filters only for 'RA Region Name' = 'London' function main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday var birthDate = new Date(joined.user.properties.Birthday); var purchaseDate = new Date(joined.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 age; }, [0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // New age buckets as requested ) ], mixpanel.reducer.count() ); }
Let me check the knowledge base for you again. One moment! đź“š
I'll modify the JQL query to filter for purchases where 'RA Region Name' equals 'London'. Here's the updated query: LBKSfunction main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .filter(function(joined) { // Filter for events where RA Region Name is London return joined.event.properties["RA Region Name"] === "London"; }) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing BirthdayLBKS var birthDate = new Date(joined.user.properties.Birthday); var purchaseDate = new Date(joined.event.time); var age = purchaseDate.getFullYear() - birthDate.getFullYear();LBKS // 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--; }LBKS return age; }, [0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested ) ], mixpanel.reducer.count() ); }LBKSThe key change is the addition of a .filter() function after the join operation. This filter checks if the event property "RA Region Name" equals "London" and only includes events that match this condition. As described in <https://docs.mixpanel.com/docs/reports/apps/jql|Mixpanel's JQL documentation>, the filter transformation allows you to keep only the elements that match your specified criteria.This query will now analyze the age distribution of purchases made specifically in the London region, broken down by year and using your specified age buckets.
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 clarify the filter in the JQL query based on your explanation about the property types. Since you mentioned that "RA Region Name" is an event property and "RA Area" is a user property, I need to know which one you want to filter by for your analysis.Since your original request was to filter for "London", I'll provide both options so you can use the appropriate one:
Option 1: Filter by Event Property "RA Region Name"
return join(
Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
}),
People(),
{type: "inner"}
)
.filter(function(joined) {
// Filter for events where RA Region Name (event property) is London
return joined.event.properties["RA Region Name"] === "London";
})
.groupBy(
[
// Year of purchase
function(joined) {
return new Date(joined.event.time).getFullYear();
},
// Calculate age using Birthday from user profile with new buckets
mixpanel.numeric_bucket(
function(joined) {
if (!joined.user.properties.Birthday) return -1; // Handle missing BirthdayLBKS var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.event.time);
var age = purchaseDate.getFullYear() - birthDate.getFullYear();LBKS // 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--;
}LBKS return age;
},
[0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested
)
],
mixpanel.reducer.count()
);
}LBKS```
Option 2: Filter by User Property "RA Area"
return join(
Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
}),
People(),
{type: "inner"}
)
.filter(function(joined) {
// Filter for users where RA Area (user property) is London
return joined.user.properties["RA Area"] === "London";
})
.groupBy(
[
// Year of purchase
function(joined) {
return new Date(joined.event.time).getFullYear();
},
// Calculate age using Birthday from user profile with new buckets
mixpanel.numeric_bucket(
function(joined) {
if (!joined.user.properties.Birthday) return -1; // Handle missing BirthdayLBKS var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.event.time);
var age = purchaseDate.getFullYear() - birthDate.getFullYear();LBKS // 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--;
}LBKS return age;
},
[0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested
)
],
mixpanel.reducer.count()
);
}LBKS```LBKSLBKSThe key difference is in the filter function - the first option filters based on the event property "RA Region Name", while the second filters based on the user property "RA Area".LBKSPlease choose the appropriate option based on whether you want to analyze purchases that occurred in London (event property) or purchases made by users who are based in London (user property).
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 for this JQL, can you please add in a breakdown using the field 'is festival', I want to see how many purchases per age group over time that are festivals (so 'is festival' = true) vs aren't ('is festival' = false)
function main() {
return join(
Events({
from_date: "2020-01-01",
to_date: "2025-12-31",
event_selectors: [{event: "Complete Purchase"}]
}),
People(),
{type: "inner"}
)
.filter(function(joined) {
// Filter for users where RA Area (user property) is London
return joined.user.properties["RA Area"] === "London";
})
.groupBy(
[
// Year of purchase
function(joined) {
return new Date(joined.event.time).getFullYear();
},
// Calculate age using Birthday from user profile with new buckets
mixpanel.numeric_bucket(
function(joined) {
if (!joined.user.properties.Birthday) return -1; // Handle missing Birthday
var birthDate = new Date(joined.user.properties.Birthday);
var purchaseDate = new Date(joined.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 age;
},
[0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested
)
],
mixpanel.reducer.count()
);
}Let me check the knowledge base for you again. One moment! đź“š
I'll modify your JQL query to include a breakdown by the 'is festival' field. This will allow you to compare festival vs. non-festival purchases across different age groups over time.LBKSfunction main() { return join( Events({ from_date: "2020-01-01", to_date: "2025-12-31", event_selectors: [{event: "Complete Purchase"}] }), People(), {type: "inner"} ) .filter(function(joined) { // Filter for users where RA Area (user property) is London return joined.user.properties["RA Area"] === "London"; }) .groupBy( [ // Year of purchase function(joined) { return new Date(joined.event.time).getFullYear(); }, // Is Festival breakdown (true or false) function(joined) { // Check if the event property exists and is true, otherwise return false return joined.event.properties["is festival"] === true; }, // Calculate age using Birthday from user profile with new buckets mixpanel.numeric_bucket( function(joined) { if (!joined.user.properties.Birthday) return -1; // Handle missing BirthdayLBKS var birthDate = new Date(joined.user.properties.Birthday); var purchaseDate = new Date(joined.event.time); var age = purchaseDate.getFullYear() - birthDate.getFullYear();LBKS // 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--; }LBKS return age; }, [0, 18, 21, 24, 27, 30, 33, 36, 39, 42, 45] // Age buckets as requested ) ], mixpanel.reducer.count() ); }LBKSThe key change I've made is adding a new grouping function between the year and age bucket functions. This function: 1. Checks if the event property "is festival" is true
2. Returns true if it is, false otherwise (including if the property doesn't exist)With this modification, your results will now have three keys:
- key.0: Year (e.g., 2021)
- key.1: Is Festival (true or false)
- key.2: Age bucket (e.g., 18, 21, 24, etc.)This will allow you to analyze how the proportion of festival vs. non-festival purchases varies across different age groups over time, specifically for users in the London area.As described in <https://docs.mixpanel.com/docs/reports/apps/jql|Mixpanel's JQL documentation>, the groupBy transformation lets you segment your data by multiple dimensions simultaneously.
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.