Mixpanel Community Icon

Extracting GCLID from URL in Segment: Custom Event Property Formula

·
·

I need help writing a formula for a custom event property, specifically to extract the GCLID from a url that is part of the page viewed event. We use Segment for tracking

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    Hi Bradley A., 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

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    I can help you create a custom property formula to extract the GCLID from a URL in Mixpanel when using Segment for tracking.Based on the knowledge sources, Mixpanel automatically tracks UTM parameters and advertising click IDs (including GCLID) when using the JavaScript SDK Track UTM Tags. However, if you need to extract it manually from a URL property, you can use Mixpanel's custom properties feature.Here's how to create a formula to extract the GCLID from a URL: 1. Click the plus button to the right of the search bar in your Mixpanel report 2. Select "Event Property" to open the property builder 3. Give your property a name like "GCLID from URL" 4. In the formula field, you can use the SPLIT function to extract the GCLID parameterIf your URL property is called "Current URL" or "Page URL", your formula might look like this: LBKSSPLIT(SPLIT(Current URL, "gclid=")[1], "&amp;")[0]LBKSThis formula: 1. Splits the URL at "gclid=" and takes everything after it (index 1) 2. Then splits that result at "&" and takes the first part (index 0)This works because URL parameters are formatted like ?param1=value1&amp;param2=value2, so this formula isolates just the GCLID value.When you're satisfied with the formula, you can click "Apply" to use it locally in your current report, or "Save" to make it available for other reports and team members.For more information on creating custom properties in Mixpanel, you can refer to the Custom Properties documentation.

    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.

  • Avatar of Bradley A.
    Bradley A.
    ·
    ·

    kapa.ai this formula does not work and returns the error: second argument to SPLIT (...) must be one character long (SPLIT(SPLIT(Current URL, "gclid=")[1], "&")[0])

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    Let me check the knowledge base for you again. One moment! 📚

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    I apologize for the error in the formula. You're right - the SPLIT function in Mixpanel has a specific requirement that the delimiter must be a single ASCII character, not a string like "gclid=".According to the Mixpanel documentation on custom property functions: > split: Splits a string into different parts based on a user-specified delimiter, and lets you select a particular split. Delimiter must be a single ASCII character. Custom Properties FunctionsLet me provide a corrected approach. Since we can't use multi-character delimiters, we need to use a different strategy: LBKSlet( url_with_gclid, regex_replace(Current URL, ".**gclid=([^&amp;]**).*", "$1"), ifs( url_with_gclid = Current URL, "", // If regex didn't match, return empty string url_with_gclid // Otherwise return the extracted gclid ) )LBKSThis formula uses the regex_replace function which is more appropriate for this task. It: 1. Looks for the pattern "gclid=" followed by any characters until an "&" 2. Captures just the value part (the GCLID) 3. Returns an empty string if no GCLID is foundThe let function helps make the formula more readable by creating a temporary variable.If you're still experiencing issues, you might want to reach out to Mixpanel's support team or join their community for more specific help with your implementation: - Mixpanel Support - Mixpanel Community Slack

    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.

  • Avatar of Bradley A.
    Bradley A.
    ·
    ·

    kapa.ai this returns an error: let( url_with_gclid, regex_replace(@"""{"label":"url","propertyDefaultType":"string","resourceType":"event","type":"string","value":"url"}"""@, ".*gclid=([^&]*).*", "$1"), ifs( url_with_gclid = @"""{"label":"url","propertyDefaultType":"string","resourceType":"event","type":"string","value":"url"}"""@, "", // If regex didn't match, return empty string url_with_gclid // Otherwise return the extracted gclid ) )

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    Let me check the knowledge base for you again. One moment! 📚

  • Avatar of kapa.ai
    kapa.ai
    APP
    ·
    ·

    I see the issue with your formula. It looks like there's a problem with how the property is being referenced in the custom property formula. The error shows that instead of the actual URL property value, you're getting a JSON representation of the property metadata.Let me provide a simpler approach using just the regex_extract function, which is specifically designed for extracting parts of strings using regular expressions: LBKSregex_extract(url, "gclid=([^&amp;]*)", 1)LBKSThis formula: 1. Takes your URL property (make sure to use the actual property name instead of "url") 2. Looks for "gclid=" followed by any characters that aren't "&" 3. Extracts just the value part (the GCLID) using capture group 1According to the Custom Properties documentation, regex_extract is defined as: > If haystack is a string and pattern matches at least one substring, extracts the result from the first pattern match in haystack. The result is a string equal to the entire regex match, or if capture group is specified, only that portion of the match.If your URL property has a different name than "url", make sure to replace it with the correct property name in the formula.If you're still experiencing issues, you might want to try a simpler test formula first to ensure the property is being accessed correctly, such as: LBKSdefined(url)LBKSThis will return true if the property exists and is accessible in your formula.Would you like me to suggest any other approaches to extract the GCLID?

    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.