Scheduled queries

    Learn how to schedule queries in Stripe Sigma that run on a recurring basis.

    You can automate your Stripe Sigma queries by scheduling them to run on a daily, weekly, or monthly basis. Results for each scheduled query are sent via email to specified team members or as webhook events.

    Scheduling a query

    With a query loaded into the editor, click Schedule. We recommend you uniquely name all your scheduled queries to avoid confusion. If your query doesn’t already have a name (or you wish to modify it), you can update it during the scheduling process.

    Schedule

    Each scheduled query can be run on a daily, weekly, or monthly basis. Queries run as soon as the data for that period is available.

    Schedule Description
    Daily Queries run as soon as the data for each day (ending at 12:00 am UTC) is processed. Query results for the previous day are usually available by 12pm UTC.
    Weekly Queries run every week as soon as the data for the previous week (ending on Sunday at 12:00am UTC) is processed. Query results for the previous week are usually available by 12pm UTC the following Monday.
    Monthly Queries run every month as soon as the data for the previous month (ending at 12:00am UTC) is processed. Query results for the previous month are usually available by 12pm UTC on the 1st of the month.

    Subscribers

    Creators of scheduled queries are added as subscribers to email notifications by default. To notify other team members as well, enter their email addresses. Results sent via email include the name and date of the scheduled query, and a link to download the results in CSV format. To preview what the email looks like, click Preview email.

    You or your team members can stop receiving notifications at any time by clicking the Unsubscribe link in the email. You can also edit the scheduled query in the Dashboard and add or remove subscribers.

    Timeline

    Based upon your chosen schedule, the timeline displays the date your query will next run, and the processing date of the data it will use (additional time is required to make your account data available to query).

    Managing scheduled queries

    Upcoming scheduled queries are displayed under Scheduled within the Queries tab. Schedules are grouped based on whether they were created by you or other members of your team.

    To edit a scheduled query, select it and click Edit schedule. To delete it, click ••• and select Delete.

    Receiving results as webhooks

    If you make use of webhooks, you can receive notifications for scheduled queries as webhook events. Stripe sends the sigma.scheduled_query_run.created event each time a scheduled query is run. See below for a sample event.

    The data.object.file.url subfield of the webhook payload contains the URL from which you can access the results file using your live secret API key. For example, if your server received the webhook below, it could download the results using this curl command:

    curl https://files.stripe.com/v1/files/file_M7Gt6axV/contents -u sk_live_XXXXXXX:

    For more on how to integrate webhooks, see our webhook documentation and especially the section on testing.

    // Sample payload of a sigma.scheduled_query_run.created webhook { "object": "event", "pending_webhooks": 2, "created": 1504794194, "type": "scheduled_query_run.created", "livemode": true, "request": null, "data": { "object": { "id": "sqr_ikdlBkhr7I1vG6hKICxM", "object": "scheduled_query_run", "status": "completed", "scheduled_query": "scq_FkncmLgT0wfeJylccBcY", "data_load_time": 1504656000, "file": { "id": "file_M7Gt6axV", "object": "file", "url": "https://files.stripe.com/v1/files/file_M7Gt6axV/contents", "created": 1507841188, "purpose": "sigma_scheduled_query", "size": 53075, "type": "csv" }, "title": "Scheduled Query Example", "sql": "SELECT count(*) FROM charges WHERE created >= date('2017-01-01')", "created": 1504794194, "completed": 1504794133, "result_available_until": 1505398933, "error": null, "livemode": true } } }

    Referencing data freshness

    Additional time is required for data to be made available to query. You can use data_load_time as a value in your queries that represents when data was most recently processed on your account. This is useful for dynamically setting a date range in your scheduled queries

    For example, consider the following scheduled query that returns a list of balance transactions created one month before data_load_time.

    select id, amount, fee, currency from balance_transactions -- this table is the canonical record of changes to your Stripe balance where created < data_load_time and created >= data_load_time - interval '1' month order by created desc limit 10

    The following timeline illustrates how this works based on our current SLA for data availability.

    Date Timeline for results
    2020-02-22
    • data_load_time is interpreted as 2020-02-22 00:00:00 +0000')
    • The scheduled query will include transaction data through EOD 2020-02-21
    • Query results are available on 2020-02-22 by 12pm UTC

    Was this page helpful?

    Feedback about this page?

    Thank you for helping improve Stripe's documentation. If you need help or have any questions, please consider contacting support.

    On this page