Forum Discussion

CumberlandStree's avatar
CumberlandStree
Active Member II
2 years ago

New SQL Editor for Segment Extensions

Hi

Has anyone used this yet? I have tried but get rerouted to the performance page rather than the SQL editor

  • Max's avatar
    Max
    Specialist

    Yes, I use it and I love it! You can use the predefined templates or write your own queries. For example, do you want to see how many uninstalls you had in the last few days? Something that is really important to track, but not so easy to do in Braze (sure, you can create a segment and export it, etc.). But to really quickly see the numbers for each date is not that easy). You can simply use the following SQL query:

    SELECT DATE(SF_CREATED_AT) AS uninstall_date, COUNT(DISTINCT ID) AS uninstall_users_count
    FROM USERS_BEHAVIORS_UNINSTALL_SHARED
    WHERE SF_CREATED_AT IS NOT NULL
    GROUP BY DATE(SF_CREATED_AT)
    ORDER BY uninstall_date DESC;

    Or you're merging accounts together? With the following you can see how many accounts got merged by date:

    SELECT DATE(SF_CREATED_AT) AS merged_date, COUNT(DISTINCT ID) AS merged_users_count
    FROM USERS_USERORPHAN_SHARED
    WHERE SF_CREATED_AT IS NOT NULL
    GROUP BY DATE(SF_CREATED_AT)
    ORDER BY merged_date DESC;

    Or do you want to get some channel insights, like how many IAM impressions and clicks you had in the last few days? Easy:

    WITH impressions AS (
        SELECT DATE(SF_CREATED_AT) AS impression_date, COUNT(DISTINCT ID) AS impressions_event_count
        FROM USERS_MESSAGES_INAPPMESSAGE_IMPRESSION_SHARED
        WHERE SF_CREATED_AT IS NOT NULL
        GROUP BY DATE(SF_CREATED_AT)
    ), clicks AS (
        SELECT DATE(SF_CREATED_AT) AS click_date, COUNT(DISTINCT ID) AS click_event_count
        FROM USERS_MESSAGES_INAPPMESSAGE_CLICK_SHARED
        WHERE SF_CREATED_AT IS NOT NULL
        GROUP BY DATE(SF_CREATED_AT)
    )
    SELECT impressions.impression_date, impressions.impressions_event_count, clicks.click_event_count
    FROM impressions
    LEFT JOIN clicks ON impressions.impression_date = clicks.click_date
    ORDER BY impressions.impression_date DESC;

    And best of all, you can use the SQL queries to create segment extensions so that event information is stored longer than 30 days and you can use it for re-targeting. You have many possibilities. Definitely worth exploring.