Marketplace: update WC.com product prices

gcsecsey commented 1 year ago

Thread: pdh6GB-2Se-p2

UPDATE 21/04/2023: We've discussed on a call today that we should tackle this issue by:

creating a CLI tool that takes a product_slug and yearly/monthly prices and updates the plans using the existing process
creating a one time script that gathers the slugs and prices for all products that need to be updated and calls the CLI tool

gcsecsey commented 1 year ago

Based on the sheet linked on the P2 post, I've gathered the WC.com slugs of the products that we need to update the prices for.

Product slugs

To get the product slugs of these products, I've put together the Kibana query below, it:

filters for products that are currently in the Marketplace (have categories assigned)
returns the slug (WC.com slug) and software_slug (WP.com slug) for these products

GET jetpack-search-26/_search
{
  "from": 0,
  "size": 50,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "blog_id": {
              "value": 113771570
            }
          }
        },
        {
          "exists": {
            "field": "taxonomy.wpcom_marketplace_categories"
          }
        },
        {
          "terms": {
            "slug": [
              "abandoned-cart-recovery",
              "flat-rate-shipping-plugin-for-woocommerce",
              "advanced-notifications",
              "ajax-layered-navigation",
              "all-products-for-woocommerce-subscriptions",
              "australia-post-shipping-method",
              "automatewoo",
              "automatewoo-birthdays",
              "automatewoo-refer-a-friend",
              "back-in-stock-notifications",
              "bulk-category-editor-for-woocommerce",
              "bulk-discounts-for-woocommerce",
              "bulk-order-form-for-woocommerce",
              "bulk-table-editor-for-woocommerce",
              "buy-once-or-subscribe-for-woocommerce-subscriptions",
              "canada-post-shipping-method",
              "woocommerce-cart-reports",
              "catalog-visibility-options",
              "composite-products",
              "conditional-shipping-and-payments",
              "cost-reports-woocommerce",
              "currency-switcher-for-woocommerce",
              "custom-cancelation-rules-for-woocommerce-subscriptions",
              "custom-start-date-for-woocommerce-subscriptions",
              "customer-email-verification",
              "direct-checkout-options-pro-for-woocommerce",
              "dynamic-pricing-discounts",
              "e-path-gateway",
              "email-customizer-for-woocommerce",
              "sales-report-email-pro",
              "estimated-delivery-date-time-for-woocommerce",
              "eu-vat-number",
              "fedex-shipping-module",
              "flat-rate-box-shipping",
              "free-gift-coupons",
              "free-gifts-for-woocommerce",
              "gift-cards",
              "woocommerce-subscriptions-gifting",
              "gocardless",
              "gravity-forms-add-ons",
              "intuit-qbms",
              "local-pickup-plus",
              "lottery-for-woocommerce",
              "measurement-price-calculator",
              "minmax-quantities",
              "woocommerce-mix-and-match-products",
              "moneris-gateway",
              "name-your-price",
              "paywall-for-woocommerce",
              "per-product-shipping",
              "price-stock-import",
              "product-add-ons",
              "product-bundles",
              "product-csv-import-suite",
              "product-designer-for-woocommerce",
              "product-faqs",
              "product-likes",
              "product-price-before-after-text",
              "product-recommendations",
              "product-vendors",
              "warranty-requests",
              "role-based-payment-shipping-methods",
              "roles-rules-b2b",
              "royal-mail",
              "sales-report-by-country",
              "self-service-dashboard-for-woocommerce-subscriptions",
              "shipment-tracking",
              "shipping-multiple-addresses",
              "sms-for-woocommerce",
              "woocommerce-social-login",
              "table-rate-shipping",
              "ups-shipping-method",
              "usps-shipping-method",
              "woocommerce-wishlists",
              "woocommerce-additional-variation-images",
              "woocommerce-after-the-order",
              "woocommerce-api-manager",
              "bookings-availability",
              "brands",
              "woocommerce-deposits",
              "woocommerce-distance-rate-shipping",
              "multi-currency",
              "woocommerce-order-barcodes",
              "woocommerce-points-and-rewards",
              "woocommerce-pre-orders",
              "woocommerce-quick-view",
              "woocommerce-slack",
              "woocommerce-gateway-snapscan",
              "woocommerce-splash-popup",
              "woocommerce-shipping-stamps",
              "woocommerce-subscription-downloads",
              "woocommerce-subscriptions",
              "woocommerce-zapier",
              "xero",
              "sensei-pro",
              "woocommerce-paid-courses"
            ]
          }
        }
      ]
    }
  },
  "_source": false,
  "fields": [
    "slug",
    "plugin.software_slug"
  ]
}

This query has returned a result that had this schema:

{
  "took": 114,
  "timed_out": false,
  "_shards": {
    "total": 100,
    "successful": 100,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 33,
      "relation": "eq"
    },
    "max_score": 3,
    "hits": [
      {
        "_index": "jetpack-search-26",
        "_id": "113771570-p-680253",
        "_score": 3,
        "_routing": "113771570-6",
        "fields": {
          "plugin.software_slug": [
            "woocommerce-conditional-shipping-and-payments"
          ],
          "slug": ["conditional-shipping-and-payments"]
        }
      },
    ]
  }
}

Then I've used this jq query to get the list of product slugs from the result:

.hits.hits[].fields."plugin.software_slug"[]

Plan details

To get the USD prices for these slugs I've used this query on wpsh: 2fec8-pb

I've removed some product slugs from the list after the discussion here: pdh6GB-2Se-p2#comment-3561

gcsecsey commented 1 year ago

Based on the WC.com products in the G sheet, we have the billing_plans ids that we need to update. However, the price update CLI takes store_products ids, so we need a query to get those. Then we need to execute the steps in FG to update prices for each product. PCYsg-J5F-p2

gcsecsey commented 1 year ago

We've discussed on a call today that we should tackle this issue by:

creating a CLI tool that takes a product_slug and yearly/monthly prices and updates the plans using the existing process
creating a one time script that gathers the slugs and prices for all products that need to be updated and calls the CLI tool

gcsecsey commented 1 year ago

FG on changing the price for one product: PCYsg-J5F-p2

gcsecsey commented 1 year ago

I've raised a draft diff with the changes so far: D108935-code

I've run into an issue with using the existing add_plans_by_store_product to update the prices, as this function can't be run in a non-interactive mode currently. I'll investigate if we can update the prices without a lot of code duplication and post updates to the diff.

gcsecsey commented 1 year ago

I've raised another diff that takes the approach of calling the existing add_plans_by_store_product but also updates that function to be run in non-interactive mode. D108948-code

I'm still evaluating whether it's possible to update all the prices for a plan this way, I'll post updates on this to the diff.

gcsecsey commented 1 year ago

I've abandoned D108935-code and updated D108948-code, now the CLI tool can perform the updates as described.

I'll proceed with drafting a one-time script to run the tool for all updated products.

gcsecsey commented 1 year ago

I drafted a diff for the one-time script: D109320-code

gcsecsey commented 1 year ago

I've abandoned the diff for the one-time script in favor of adding CSV file support to the CLI tool directly, similarly to the add_plans_by_store_product function here: fbhepr%2Skers%2Sjcpbz%2Sova%2Sfgber%2Sovyyvat%2Sovyyvat%2Qcebqhpg%2Qpngnybt%2Qhgvy.cuc%3Se%3Qsr040q0n%23623%2Q627-og

gcsecsey commented 1 year ago

I tested the script by trying to update some prices in production, and realized that the script needs the billing product IDs to filter the products to sync. I've also found that syncing the USD prices to the prod DB and fetching them back doesn't work within the script unfortunately, it always fetches the previous price.

I've found these issues while working on updating the prices for woocommerce-conditional-shipping-and-payments and automatewoo. After finding the issues, I've spent a long time manually updating the prices.

I'll now work on reworking the script to be able to update all plan prices and only require a syn to production at the end.

gcsecsey commented 1 year ago

I've pushed the latest version of the script to review. D108948-code

I also updated the points on this issue to 1, considering the review and potential revisions needed, as well as carrying out the actual price updates with the help of the script.

gcsecsey commented 1 year ago

I've carried out the price updates with the help of the script: pdh6GB-2Se-p2#comment-3629

gcsecsey commented 1 year ago

Changes deployed with r266954-wpcom

Automattic / wp-calypso

Marketplace: update WC.com product prices #76044

Product slugs

Plan details