Premium / Weekly Loyalty & Referral Report

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "name": "Premium / Weekly loyalty & referral report",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 9 * * 1"
            }
          ]
        }
      },
      "name": "Cron (Mon 09:00 MSK)",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.2,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "with new_qualified as (\n  select count(*)::int as cnt\n    from public.referrals r\n   where r.qualified_at >= now() - interval '7 days'\n),\nbalances as (\n  select coalesce(sum(bonus_rub),0)::int as total_balance\n    from public.loyalty_balances\n),\ntop_referrers as (\n  select c.full_name, count(r.*) filter (where r.status='qualified')::int as qualified,\n         count(r.*)::int as total\n    from public.bot_contacts c\n    join public.referrals r on r.referrer_contact_id = c.id\n   where r.created_at >= now() - interval '30 days'\n   group by c.id, c.full_name\n   order by qualified desc nulls last, total desc\n   limit 5\n),\nrepeat_orders as (\n  select count(*)::int as cnt\n    from public.bot_leads\n   where repeat_of is not null\n     and created_at >= now() - interval '7 days'\n)\nselect (select cnt from new_qualified) as qualified_this_week,\n       (select cnt from repeat_orders) as repeat_orders_this_week,\n       (select total_balance from balances) as total_loyalty_rub,\n       (select string_agg(format('\u2022 %s \u2014 %s/%s', full_name, qualified, total), E'\\n') from top_referrers) as top_text;"
      },
      "name": "Postgres (digest)",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.4,
      "position": [
        460,
        300
      ],
      "credentials": {
        "postgres": {
          "name": "<your credential>"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "report",
              "value": "\ud83d\udcca <b>\u041f\u043e\u0434\u0440\u044f\u0434 PRO \u2014 \u043e\u0442\u0447\u0451\u0442 \u0437\u0430 \u043d\u0435\u0434\u0435\u043b\u044e</b>\n\n\u041a\u0432\u0430\u043b\u0438\u0444\u0438\u0446\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0440\u0435\u0444\u0435\u0440\u0430\u043b\u043e\u0432: <b>{{$json.qualified_this_week}}</b>\n\u041f\u043e\u0432\u0442\u043e\u0440\u043d\u044b\u0445 \u0437\u0430\u043a\u0430\u0437\u043e\u0432: <b>{{$json.repeat_orders_this_week}}</b>\n\u041e\u0431\u0449\u0438\u0439 \u0431\u043e\u043d\u0443\u0441\u043d\u044b\u0439 \u0431\u0430\u043b\u0430\u043d\u0441 \u043a\u043b\u0438\u0435\u043d\u0442\u043e\u0432: <b>{{$json.total_loyalty_rub}} \u20bd</b>\n\n<b>\u0422\u043e\u043f \u0440\u0435\u0444\u0435\u0440\u0435\u0440\u043e\u0432 (\u0437\u0430 30 \u0434\u043d\u0435\u0439):</b>\n{{$json.top_text || '\u043f\u043e\u043a\u0430 \u043d\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0445'}}"
            }
          ]
        }
      },
      "name": "Set (report)",
      "type": "n8n-nodes-base.set",
      "typeVersion": 3,
      "position": [
        680,
        300
      ]
    },
    {
      "parameters": {
        "chatId": "={{$env.TELEGRAM_OWNER_CHAT_ID}}",
        "text": "={{$json.report}}",
        "additionalFields": {
          "parse_mode": "HTML",
          "disable_web_page_preview": true
        }
      },
      "name": "Telegram (owner digest)",
      "type": "n8n-nodes-base.telegram",
      "typeVersion": 1.1,
      "position": [
        900,
        300
      ],
      "credentials": {
        "telegramApi": {
          "name": "<your credential>"
        }
      }
    }
  ],
  "connections": {
    "Cron (Mon 09:00 MSK)": {
      "main": [
        [
          {
            "node": "Postgres (digest)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Postgres (digest)": {
      "main": [
        [
          {
            "node": "Set (report)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set (report)": {
      "main": [
        [
          {
            "node": "Telegram (owner digest)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "settings": {
    "executionOrder": "v1"
  },
  "active": false,
  "tags": [
    "premium",
    "loyalty",
    "report"
  ]
}

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• postgres
• telegramApi

About this workflow

Premium / Weekly loyalty & referral report. Uses postgres, telegram. Scheduled trigger; 4 nodes.

Source: https://github.com/alexdmitrievi/Podryad_PRO/blob/main/n8n/workflows/06-loyalty-weekly-report.json — original creator credit. Request a take-down →

More Slack & Telegram workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.