Instructions

Objective

Automate the onboarding process for new e-commerce customers by:

1. Identifying customers who completed their first order within the past 7 days.
2. Syncing their core information (name, email, order details) from WooCommerce to a BigQuery CRM table.
3. Sending a personalized welcome email to each identified customer.

Prerequisites & Setup

• WooCommerce Connection: Ensure access to the WooCommerce store's API (

  woocommerce-woo_orders_list

  ).
• BigQuery Dataset: Confirm the target BigQuery dataset (e.g.,

  woocommerce_crm

  ) and table (e.g.,

  new_customers

  ) exist. The table should have at least these columns:

  customer_name

  (STRING),

  email

  (STRING),

  order_id

  (INT64),

  order_date

  (TIMESTAMP),

  order_total

  (FLOAT64).
• Email Template: Locate or create the

  welcome_email_template.md

  file. The skill expects to find it at

  /workspace/dumps/workspace/

  by default.

Execution Steps

1. Fetch and Analyze Recent Orders

• Use

  woocommerce-woo_orders_list

  to retrieve completed orders. Start with parameters:

  {"status": ["completed"], "perPage": 100, "order": "desc", "orderby": "date"}

  .
• Critical Logic: Analyze the order metadata. In the provided trajectory, orders from first-time customers within the past 7 days were tagged with

  "period": "recent_7_days"

  in their

  meta_data

  . You must implement a similar filtering logic based on your store's data structure.
  • First-Time Customer Check: Ensure a customer's email appears only once in the filtered set (or check against a historical customer list in BigQuery if available).
  • Date Filtering: Use the

    date_completed

    field and calculate if it falls within the last 7 days from the current date.

2. Locate and Read the Email Template

• Use

  filesystem-search_files

  to find

  welcome_email_template.md

  .
• Use

  filesystem-read_file

  to load its content. The template should contain placeholders like

  {{customer_name}}

  ,

  {{order_id}}

  ,

  {{order_total}}

  , and

  {{order_date}}

  .

3. Prepare BigQuery Environment

• Use

  google-cloud-bigquery_list_datasets

  and

  google-cloud-bigquery_get_dataset_info

  to verify the target dataset exists.
• Use

  google-cloud-bigquery_run_query

  to inspect the

  INFORMATION_SCHEMA

  and confirm the schema of the target table (e.g.,

  new_customers

  ) matches the data you will insert.

4. Transform and Sync Customer Data

• For each identified first-time customer, extract:

  • customer_name

    : Combine

    billing.first_name

    and

    billing.last_name

    .

  • email

    : From

    billing.email

    .

  • order_id

    : The order's

    id

    .

  • order_date

    : Convert

    date_completed

    to a TIMESTAMP.

  • order_total

    : The order's

    total

    (as a float).
• Construct a single

  INSERT

  query for BigQuery with all customer values. Use parameterized values or explicit casting (e.g.,

  TIMESTAMP('...')

  ) to ensure data type compatibility.
• Execute the insert query using

  google-cloud-bigquery_run_query

  .
• Verification: Run a

  SELECT

  query to confirm all records were inserted correctly.

5. Generate and Send Welcome Emails

• For each customer, personalize the email template by replacing the placeholders with their specific data.
• Use

  emails-send_email

  to send the email.
  • Subject: Follow the template's subject line.
  • Body: Use the fully personalized email body.
  • To: The customer's email address.
• Send emails sequentially or in small batches to avoid rate limits.

Key Decisions & Error Handling

• Pagination: If you have more than 100 recent orders, implement pagination using the

  page

  parameter in

  woocommerce-woo_orders_list

  .
• Duplicate Prevention: Before inserting into BigQuery, consider checking if the

  email

  or

  order_id

  already exists in the target table to prevent duplicates if the skill runs multiple times.
• Template Flexibility: The skill is designed to use an external template file. If the file is not found, you may need to create a default template or ask the user for its location.
• Email Send Errors: Log or note if any email fails to send, but proceed with the remaining customers.

Completion

After all emails are sent, provide a summary report including:

• The number of first-time customers identified.
• Confirmation of data sync to BigQuery.
• Confirmation of emails sent.
• Use

  local-claim_done

  to mark the task as complete.