Macros in data flows

Macros in Coupler.io allow you to interpolate date and date-time ranges in your data flow’s settings, automating repetitive tasks.

This guide will help you understand and use these macros effectively.

Check out the Coupler.io Macros Generator to optimize your flow.

Table of Contents

1. What are Macros?

2. Macro Syntax

3. Supported Date Macros

4. Date Macros Usage Examples

5. Supported Date-Time Macros

6. Date-Time Macros Usage Examples

7. Supported Macros Custom Formats

8. Macros Custom Format Examples

9. Where we can use Macros?

1. What are Macros?

Macros are automated input sequences that allow you to interpolate date and date-time strings in your Coupler.io data flow settings.

For example: you don’t have to manually specify yesterday's date-time value in a report's parameters or JSON URL query. You can instead type {{yesterday}} into this field, and the data flow will automatically interpolate it with yesterday’s date-time value.

Note: Our macros are converted to date/date-time using UTC+0 timezone so scheduling configuration and data timeframes must be planned accordingly.

Example:

2. Macro's syntax

The general syntax for macros is:

{{macro}}

3. Supported date macros

Note: Our macros are converted to date/date-time using UTC+0 timezone so scheduling configuration and data timeframes must be planned accordingly.

The default date format is YYYY-MM-DD which corresponds to the ISO 8601 date standard

• today

• yesterday

• tomorrow

• thisweekstart

  • this can be replaced with last or next

  • week can be replaced with month or quarter or year

  • start can be replaced with end

• weekago

  • week can be replaced with month or quarter or year

• 2weeksago

• 2weeksfromnow

  • 2 can be replaced with any numeral

  • weeks can be replaced with days or months or quarters or years

Tip:

To get data dynamically starting at the start/ end of some period, use the following custom macro format:

• {{2monthsago.endof(month).format(YYYY-MM-DD)}}

• 2 can be replaced with any numeral

• months can be replaced with days or weeks or quarters or years

• endof can be replaced with startof

• month can be replaced with day or week or quarter or year

More on custom macro formats in the dedicated section below.

4. Date macros usage examples

Note: In the below examples, assume that today’s date is August 24th, 2024.

Parameter + macro	Parameter + Output
start_date: {{today}}	start_date: 2024-08-24
updated_at: {{thisweekstart}}	updated_at: 2024-08-19
to: {{3monthsago}}	to: 2020-05-24
to: {{1yearfromnow}}	to: 2025-08-24

5. Supported Date-time Macros

Note: Our macros are converted to date/date-time using UTC+0 timezone so scheduling configuration and data timeframes must be planned accordingly)

The default date and time format is YYYY-MM-DD[T]hh:mm:ss[Z] which corresponds to the ISO 8601 date.

• now

• thishourstart

  • this can be replaced with last or next

  • start can be replaced with end

• hourago

• 2hoursago

  • 2 can be replaced with any numeral

• 1hourfromnow

  • 1 can be replaced with any numeral

6. Date-time macros usage examples

Note: In the below examples, assume that today’s date and time is August 24th, 2024-14:05 AM.

Parameter + macro	Output
created_at: {{thishourstart}}	created_at: 2024-08-24T14:00:00Z
updated_since: {{5hoursago}}	updated_since: 2024-08-24T09:05:00z
created_at: {{3hoursfromnow}}	created_at: 2024-08-24T17:05:00z

7. Supported macros custom formats

Syntax

You can format each macro as needed using the following syntax:

{{macro.format(output-format)}}

• macro - specify the macro you need

• output-format - specify the format you want the macro to output

  • dddd - day of the week (e.g. Monday, Tuesday, Wednesday, etc.)

  • DD - day of the month (1-31)

  • MM - month (1-12)

  • YYYY - year (e.g. 2024)

  • hh - hour (0-24)

  • mm - minute (0-59)

Examples

• {{2monthsago.format(YYYY-MM-DD)}}

  • 2 can be replaced with any numeral

  • months can be replaced with days or weeks or quarters or years

  • (YYYY-MM-SS) can be replaced with a combination of the output-format examples listed above like (MM-YYYY)

• {{2monthsago.endof(month).format(YYYY-MM-DD)}}

  • 2 can be replaced with any numeral

  • months can be replaced with days or weeks or quarters or years

  • endof can be replaced with startof

  • month can be replaced with day or week or quarter or year

Example of applying a custom macro in data flow settings

8. Macros custom format examples

Note: In the below examples, assume that today's date and time is August 24th, 2024 at 13:23

Parameter + Macros:	Parameter + Output:
created_at:{{today.format(dddd)}}	created_at:Saturday
created_at:{{5hoursago.format(hh:mm)}}	created_at:8:23
created_at:{{55hoursfromnow.format(YYYY-MM-DD)}}	created_at:2024-08-24
Created_at: 2monthsago.endof(month).format(YYYY-MM-DD)	Created_at: 2024-06-30

9. Where can you use Macros

1.Date & date-time settings of data flow (source settings)

For example:

• Facebook Ads "From", "To" fields

• Mailchimp "Created after date", "Created before date", "Sent since date", "Sent before date" fields

• Shopify "Created after date", "Created before date", "Changed after date", "Changed before date" fields

• WooCommerce "After date", "Before date" fields

• SalesForce "Changed after date", "Changed before date" fields

• Google Analytics 4 "Start date", "End date" fields

• Hubspot "Created after date", "Created before date", "Updated after date", and "Updated before date" fields

• Note: only date macros are available for Hubspot date fields. Date and time macros ( {{hourago}} etc) are unavailable.

• Xero reports "Report Period" field

2.Body/string/query settings of data flow that allow dynamic input (source settings)

For example:

• JSON / CSV URL, URL query string, and Body fields of the JSON or CSV Client data flow.

• Quickbooks "Where" field

• Note: QB requires the use of macro values in single quotes, i.e. '{{today}}'. Please find a more detailed guide for this here.

• Quickbooks reports the "Query parameters" field.

• Xero "Where" field

• Note: special formatting to be used with Xero data flow: check the details here.

• BigQuery "SQL query" field

• JIRA "JQL" field

• Slack "Search query" field

Examples of using macros in the URL Query Parameter and Request body for GET & POST Requests:

• "URL query parameter" field for GET requests

• 
• “Request body" field for POST requests

• The output you would see in Slack:

  • 
• To post a message to Slack:

• 

3.Sheet name setting (destination settings)

If you need the data flow to send data to a new destination sheet on each run, you may apply a macro in the name of the destination sheet.

Example:

• Setting applied:

• Result: each time the data flow runs, a new destination sheet corresponding to the macro's date will be created:

Please see more on this approach here.