Citrus Improves Sales by Analyzing the Competition

Goal

Citrus wanted to increase sales with the help of price optimization. To do that, the company needed to find out how the difference between Citrus’ prices and their competitors’ prices affects the conversion rates. At first, Citrus’ Category Managers spent a lot of time browsing their competitors’ websites. However, the results weren’t always accurate, due to the human factor. That’s why the company decided to automate the price monitoring process. The obtained information would help Citrus dynamically adjust prices on their website.

Solution

Citrus chose the solutions by OWOX BI and Competera, family of pricing and inventory products for retailers, and OWOX BI, to collect and bring together the data for dynamical price adjustment.

The company decided to take these steps:

1. Import data about user interactions with the website to Google BigQuery, via OWOX BI Pipeline.
2. Collect the data about competitors’ prices with the help of Competera, and then import the data to Google BigQuery via OWOX BI Pipeline.
3. Send the combined data from Google BigQuery to Google Sheets and Power BI, for creating reports.
4. Set up automatic notifications when the products in competitors’ stores become unavailable or their prices change.

Below is Citrus’ data consolidation flowchart:

Now, let’s take a closer look at each step.

Step 1. Collect user behavior data in Google BigQuery

Citrus uses Google Analytics to track product views, adding to the cart, orders, and other user interactions with the website. However, as the number of sessions on the company’s website is higher than 500,000, Google Analytics uses sampling when creating reports.

To obtain the raw unsampled data, Citrus’ analysts set up real-time collection of user behavior data from the website in Google BigQuery via OWOX BI Pipeline.

Step 2. Collect the data about competitors’ prices and send it to Google BigQuery

As mentioned above, Citrus’ managers used to monitor their competitors’ prices manually. They spent a lot of time doing that, and the collected data wasn’t sufficient for analysis. That’s why the company started using Competera to monitor prices. To get the most accurate data, Citrus’ analysts created the list of products for monitoring, and set the scanning frequency to «Once a day».

To identify how the deviation from the market affects conversions and sales volume, Citrus needed to combine the data about website user interactions and the data about competitors’ prices. For that purpose, OWOX BI analysts set up the automatic data import from Competera to Google BigQuery with OWOX BI Pipeline. The structure of the imported data can be found here.

Step 3. Build reports

With all the data combined in Google BigQuery, the analysts from OWOX BI and Citrus teams started creating the dashboard to visualize the reports. To do that, they wrote SQL queries, used Google Sheets and Power BI. Below are the descriptions of the 3 reports obtained.

1. The report demonstrating the effect of special offers on conversions and sales.

Thanks to this report, Citrus’ Category Managers can track their competitors’ pricing, and see how Citrus’ special offers affect the conversion rates and sales. They use the obtained information to adjust prices on citrus.ua.

The table above shows that the product number 445566 has the highest conversion rate. The possible reason is that there is a special offer for this product at Citrus, while the competitors don’t have any. However, it’s worth mentioning that user choices can be affected by purchase conditions, novelty, demand for product, seasonal price fluctuations and other factors.

2. The report presenting how the deviation from the market pricing affects the conversion and sales rates.

To create the report, Citrus' analysts send the data from Google BigQuery to the Power BI, using native integration. As a result, they got a dashboard with a bubble chart and a bar chart. With the bubble chart, Citrus’ Category Managers can estimate and set the optimal prices that help increase conversion rates.

The chart shown below demonstrates how Citrus’ conversion rates depend on the price deviation from the market.

As can be seen from the chart, there are two types of products with the highest level of conversion: the products with prices that are 15% higher than in the market, and the products that are 10% cheaper than in the market. The two types of products are presented with yellow and lettuce green circles. However, these products bring much less revenue than the products with the market prices. The latter are represented by the purple circle.

The next chart in the dashboard shows the number of users, sales and revenue, brought from different sources:

The bar chart above shows that channel grouping 10 brings the most loyal users. Such users buy products from Citrus at higher prices than on the market.

3. The report demonstrating changes in competitors’ prices

To calculate the deviation from the market price, OWOX BI analysts wrote SQL queries to the data in Google BigQuery:

    
    SELECT
      productSku,
      productPrice,
      usersPurchase,
      SUM(competitor1) AS competitor1,
      SUM(competitor2) AS competitor2,
      SUM(competitor3) AS competitor3,
    FROM (
      SELECT
        productSku,
        productPrice,
        usersPurchase,
        IF(competitorName CONTAINS 'competitorName1', priceDifference, 0) AS competitor1,
        IF(competitorName CONTAINS 'competitorName2', priceDifference, 0) AS competitor2,
        IF(competitorName CONTAINS 'competitorName3', priceDifference, 0) AS competitor3,
      FROM (
        SELECT
          productSku,
          productPrice,
          usersPurchase,
          competitorPrice,
          competitorName,
          competitorIsPromo,
          priceDifference,
          CASE
            WHEN ROUND(priceDifference, 4) < -0.25 THEN '-0.30'
            WHEN ROUND(priceDifference, 4)< -0.2
          AND ROUND(priceDifference, 4) >= -0.25 THEN '-0.25'
            WHEN ROUND(priceDifference, 4) < -0.15 AND ROUND(priceDifference, 4) >= -0.2 THEN '-0.20'
            WHEN ROUND(priceDifference, 4) < -0.1
          AND ROUND(priceDifference, 4) >= -0.15 THEN '-0.15'
            WHEN ROUND(priceDifference, 4) < -0.05 AND ROUND(priceDifference, 4) >= -0.1 THEN '-0.10'
            WHEN ROUND(priceDifference, 4) < 0
          AND ROUND(priceDifference, 4) >= -0.05 THEN '-0.05'
            WHEN ROUND(priceDifference, 4) = 0 THEN '0'
            WHEN ROUND(priceDifference, 4) > 0
          AND ROUND(priceDifference, 4) <= 0.05 THEN '0.05'
            WHEN ROUND(priceDifference, 4) > 0.05 AND ROUND(priceDifference, 4) <= 0.1 THEN '0.10'
            WHEN ROUND(priceDifference, 4) > 0.1
          AND ROUND(priceDifference, 4) <= 0.15 THEN '0.15'
            WHEN ROUND(priceDifference, 4) > 0.15 AND ROUND(priceDifference, 4) <= 0.20 THEN '0.20'
            WHEN ROUND(priceDifference, 4) > 0.2
          AND ROUND(priceDifference, 4) <= 0.25 THEN '0.25'
            WHEN ROUND(priceDifference, 4) > 0.25 THEN '0.30'
            ELSE 'other'
          END AS priceDifferenceType,
        FROM (
          SELECT
            productSku,
            IF(usersPurchase IS NULL, 0, usersPurchase) AS usersPurchase,
            FIRST(competitorPrice) AS competitorPrice,
            competitorName AS competitorName,
            FIRST(competitorIsPromo) AS competitorIsPromo,
            FIRST(productPrice) AS productPrice,
    ...
    

Next, the query results are sent to Google Sheets via the OWOX BI BigQuery Reports add-on.

The table above shows that at the moment of the report creation, the price of the product number 112233 at citrus.ua was 31.6% higher than the price in the store of Competitor 1.

To learn more about this and other types of reports on competitors’ prices, read the following blogpost.

Step 4. Set up automatic notifications

The OWOX BI analysts helped Citrus set up automatic email notifications to inform the company of any changes in competitors’ prices. Each time the prices change, or products become unavailable in the competitors’ stores, the Citrus’ Category Managers receive a report on the updates, by email.

The OWOX BI analysts used the Apps Script to set up the notifications.