How to use macros in Coupler.io 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.

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.

Example:

2.Macro's syntax

The general syntax for macros is:

{{macro}}

3.Supported date macros

• 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

4.Date macros usage examples

5.Supported Date-time Macros

• 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

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

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
  • To post a message to Slack:

    • 

  • The output you would see in 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.