How to use macros in Coupler.io importers?

Macros in Coupler.io allow you to interpolate date and date-time strings in your importers’ 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 importer 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 importer 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 importer 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 importers (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 importers that allow dynamic input (source settings)

For example:

    • JSON / CSV URL, URL query string, and Body fields of the JSON or CSV Client importer.
    • 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 Importer: 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 importer 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 importer runs, a new destination sheet corresponding to the macro's date will be created:

Please see more on this approach here.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.