撈名單

Wednesday, Jul 2, 2025 | 5 minute read | Updated at Wednesday, Jul 2, 2025

@ Joanna Lei

從資料庫做資料轉換

學長處理需求

需要一個腳本，可以把站點身上有掛任一權限組：
-「業務顧問」「業務訓練官」: xuemi -「業務」「業務訓練官」: sixdigital 的人，他在「名單撥打」內的名單在「已完成」tab的，且超過 2 個月沒被撥打的，依照app改掛到以下帳號去

email@xuemi.co https://www.xuemi.co/admin/members/uuid/profile

email@ooschool.cc https://www.ooschool.cc/admin/members/uuid

學長作法

第一步整理

將學米/無限符合名單的member 篩選出來，匯出成google sheet先給 zz檢查

with app_variables as (
  select 'sixdigital' as app_id
),
specific_permission_group as (
  select p.id, p.name
  from permission_group p
  where p.name in ('業務顧問', '業務', '業務訓練官')
  and p.app_id = (select app_id from app_variables)
),
manager as (
  select m.id, m.name as manager_name, specific_permission_group.name
  from member m
  join member_permission_group mpg on mpg.member_id = m.id
  join specific_permission_group on specific_permission_group.id = mpg.permission_group_id
  where m.app_id = (select app_id from app_variables)
),
target_manager as (
  select m.id as target_manager_id
  from member m
  where m.id = 'uuid'
  and m.app_id = (select app_id from app_variables)
)

select m.id
from member m
join manager on m.manager_id = manager.id
where m.completed_at is not null
and m.app_id = (select app_id from app_variables)
and (
  greatest(m.last_member_note_called, m.last_member_note_answered) <= (current_date - interval '2 months')
)

第二步確認

zz 確認完後會通知需要將多少筆建立會員

第三步更新

先將篩出的名單更新成 manager lead+done (下方sql 已將select改成update)

with app_variables as (
  select 'sixdigital' as app_id
),
specific_permission_group as (
  select p.id, p.name
  from permission_group p
  where p.name in ('業務顧問', '業務', '業務訓練官')
  and p.app_id = (select app_id from app_variables)
),
manager as (
  select m.id, m.name as manager_name, specific_permission_group.name
  from member m
  join member_permission_group mpg on mpg.member_id = m.id
  join specific_permission_group on specific_permission_group.id = mpg.permission_group_id
  where m.app_id = (select app_id from app_variables)
),
target_manager as (
  select m.id as target_manager_id
  from member m
  where m.id = 'uuid'
  and m.app_id = (select app_id from app_variables)
)
update member
set manager_id = (select target_manager_id from target_manager)
where member.id in (
  select m.id
  from member m
  join manager on m.manager_id = manager.id
  where m.completed_at is not null
  and m.app_id = (select app_id from app_variables)
  and (
    greatest(m.last_member_note_called, m.last_member_note_answered) <= (current_date - interval '2 months')
  )
);

或是可以使用整理好的memberId 使用以下sql更新

WITH target AS (
    SELECT id
    FROM member m
    WHERE m.id = 'uuid'
      AND m.app_id = 'xuemi'
      AND m.email = 'email'
)
UPDATE member m
SET manager_id = (SELECT id FROM target)
WHERE m.id in ('uuid1','uuid2')
AND m.app_id = 'xuemi';

第四步隨機挑選

從上述第一點整理好的名單，隨機選出zz決定的筆數將它整理成學米及無限匯入會員的格式（下方sql可以產格式）

select
    max(case when p.name = '填單日期' then mp.value end) as "properties.填單日期",
    string_agg(distinct c.name, ',') as categories,
    max(case when p.name = '名單分級' then mp.value end) as "properties.名單分級",
    max(case when p.name = '廣告素材' then mp.value end) as "properties.廣告素材",
    max(case when p.name = '行銷活動' then mp.value end) as "properties.行銷活動",
    max(case when p.name = '觸及平台' then mp.value end) as "properties.觸及平台",
    max(case when p.name = '廣告組合' then mp.value end) as "properties.廣告組合",
    m.email,
    m.name,
    string_agg(distinct mp2.phone, ',') as phones
from member m
left join member_property mp on m.id = mp.member_id
left join property p on p.id = mp.property_id
left join member_phone mp2 on mp2.member_id = m.id
left join member_category mc on mc.member_id  = m.id
left join category c on c.id = mc.category_id
where m.id in (['uuid1','uuid2' ])
group by m.email, m.name

第五步匯入

請zz檢查並且請zz匯入 kkschool會員

第六步更新狀態lead+done

將已匯入的會員更新成lead+done → 透過email來找

‌WITH target AS (
    SELECT id
    FROM member m
    WHERE m.id = 'uuid'
      AND m.app_id = 'kkschool'
      AND m.email = 'email'
)
UPDATE member m
SET manager_id = (SELECT id FROM target)
WHERE m.email IN ('email1','email2')
  AND m.app_id = 'kkschool';

第七步更新狀態lead+expired

將舊的會員通過以下的sql更新成lead+expired

WITH target AS (
    SELECT id
    FROM member m
    WHERE m.id = 'uuid'
      AND m.app_id = 'xuemi'
      AND m.email = 'email'
)
UPDATE member m
SET manager_id = (SELECT id FROM target)
WHERE m.id IN ('uuid1','uuid2')
  AND m.app_id = 'xuemi';

我的卡

1.需要一個腳本，可以把學米跟無限身上有掛任一權限組： -「業務顧問」「業務訓練官」: xuemi -「業務」「業務訓練官」: sixdigital -「業務」: kkschool -「業務」: nschool 他在「名單撥打」內的名單在「已完成」tab的，且超過 2 個月沒被撥打的，依照app改掛到以下帳號去

email@xuemi.co https://www.xuemi.co/admin/members/uuid/profile

email@ooschool.cc https://www.ooschool.cc/admin/members/uuid

email@kkschool.com https://kkschool.kolable.app/admin/members/uuid

https://nschool.tw/admin/members/uuid

2.注意！移動到已完成時，需要把註記「已完成」清掉，名單等級都洗成C

3.幫忙把上面那幾個已完成帳號底下的已完成註記都移除，因為需要當作結案單重新派給業務洗一輪

我的做法

第一步 MetaBase

從MetaBase抓資料。

不用再將資料貼到Excel給對方確認
一次可以抓全部的名單

-- 一次查 4 個 app
with app_variables as (
    select 'xuemi' as app_id  -- xuemi
    union all
    select 'sixdigital'  -- sixdigital
    union all
    select 'kkschool'  -- kkschool
    union all
    select 'nschool'  -- nschool
),

-- 找出這些 app 裡有特定權限的 group
specific_permission_group as (
    select
        p.id,
        p.name,
        p.app_id
    from
        permission_group p
        join app_variables av on p.app_id = av.app_id
    where
        (p.app_id = 'xuemi' and p.name in ('業務顧問', '業務訓練官'))  -- xuemi
        or (p.app_id = 'sixdigital' and p.name in ('業務', '業務訓練官'))  -- sixdigital
        or (p.app_id = 'kkschool' and p.name = '業務')  -- kkschool
        or (p.app_id = 'nschool' and p.name = '業務')  -- nschool
),

-- 找出符合權限組的業務
manager as (
    select distinct
        m.id,
        m.app_id
    from
        member m
        join member_permission_group mpg on mpg.member_id = m.id
        join specific_permission_group spg on spg.id = mpg.permission_group_id
    where
        m.app_id = spg.app_id
)

-- 主查詢：找出符合條件的 member，並標記 app、天數
select *
from (
    select distinct on (m.id)
        m.id,
        m.email,
        m.name,
        app.id as app_id,
        DATE_PART('day', current_date - greatest(m.last_member_note_called, m.last_member_note_answered)) as days_since_last_contact,
        m.last_member_note_called,
        m.last_member_note_answered,
        m.completed_at
    from
        member m
        join manager on m.manager_id = manager.id
        join app on app.id = m.app_id::text
    where
        m.completed_at is not null
        and m.app_id::text in (
            'xuemi',
            'sixdigital',
            'kkschool',
            'nschool'
        )
        and greatest(
            m.last_member_note_called,
            m.last_member_note_answered
        ) <= (current_date - interval '2 months')
    order by m.id, m.completed_at desc nulls last
) as base
order by base.app_id;

可以先抓一個app_id，確認沒問提在加入其他的app_id做查詢

第二步 Hasura update

因為MetaBase只能read，所以需要到Hasura或table plus等可以操作資料的地方進行資料操作。

依照不同的app_id以及表單分開操作mutation

範例：修改業務及完成

mutation UpdateXuemiMembers {
  update_member(
    where: {
      id: { _in: [
        "uuid-1", "uuid-2", "uuid-3"
      ] }
    },
    _set: {
      manager_id: "{{ new_manager_id }}",
      completed_at: null,
    }
  ) {
    affected_rows
  }
}

範例：修改名單等級

mutation UpdateMemberPropertyXuemi {
  update_member_property(
    where: {
      member_id: { _in: [
        "uuid-1", "uuid-2"
      ] },
      property_id: { _eq: "{{ 要找到是哪一個property }}" } 
    },
    _set: {
      value: "C"
    }
  ) {
    affected_rows
  }
}

這樣就完成囉！！

Next page UrFit Docker Deploy

學長處理需求

學長作法

第一步 整理

第二步 確認

第三步 更新

第四步 隨機挑選

第五步 匯入

第六步 更新狀態lead+done

第七步 更新狀態lead+expired

我的卡