Final Project, Part 2: A/B Test and SQL

The question

The TripleTen final sprint had four deliverables. The decomposition and the Olist project are in part 1. These are the other two, independent of each other: an A/B test for an online shop and a SQL analysis of a books database. Each one has its own block below, with data, method, findings and decision.

A/B test of the recommender system

The test's question

The shop changed the buying experience for part of its new users in the European Union and expected group B to convert at least 10% more than group A, within 14 days of sign-up, at three steps: product page, cart and purchase. The chart above shows what happened: B came in below A at all three.

The data, and what the test design was hiding

Four files: 14 marketing campaigns, 58,703 sign-ups, 423,761 events and 14,525 participant records. Before testing anything, I checked whether the test followed its specification. It did not:

• Another test at the same time. 887 participants (24.14%) were also in interface_eu_test, which the brief never mentioned. I removed only the 431 who received that test's change: the other 456 were in its control group and saw nothing different.
• People outside the EU. 194 participants came from other regions and were removed.
• Half the size and uneven groups. 3,050 participants remained, 50.83% of the 6,000 planned, with 74.72% in A and 25.28% in B. A chi-square against a 50/50 split gives p = 3.64e-164: the assignment was not even.
• Data that ends early. The last event is on 30 December, not 1 January, and 1,170 participants (38.36%) did not get the full 14 days, more in A (42.04%) than in B (27.50%).
• A funnel that is not sequential. 653 of the 953 buyers (68.52%) bought without going through the cart, so I measured each step over the group's total, not over the previous step.

Method

Each user's outcome at each step is binary and the groups are independent, so I used a two-proportion z-test, written as my own function, with two-sided hypotheses. Three comparisons on the same users call for Bonferroni: a result only counts below p = 0.0167. I ran the test twice, with every participant and only with those who had the full 14 days, and computed each step's power to detect a 10% lift with the groups' real sizes.

Findings

• Product page: 64.77% in A against 55.64% in B, a drop of 9.12 points (p = 0.000006). Significant, and the test had the power to see it (0.839).
• Cart: 30.10% against 27.76% (p = 0.217). Not significant.
• Purchase: 32.21% against 28.40% (p = 0.049). Below 5%, but not below the Bonferroni threshold, so not significant.
• With the full 14 days the decision is the same at all three steps.
• "Not significant" does not prove there is no effect. At the cart and purchase steps, the power to detect a 10% lift was 0.198 and 0.221, against the usual 0.8. But the difference points the other way: the data shows no sign of a gain.

The decision

Do not ship the change, because no result shows a gain and the only significant difference is a drop. Run the test again with the change clearly described, a 50/50 split checked while the test runs, no other test on the same users, a period outside the holidays, data collected until 14 days after the last sign-up, and a sample sized in advance to detect 10% at the cart and purchase steps.

SQL: the reading-app market

The analysis question

During the pandemic people started reading more, and several startups launched reading apps. I received the database of one of these services and answered five questions in SQL, as groundwork for the value proposition of a new product in that market.

The data

A PostgreSQL database with five tables: 1,000 books, 636 authors, 340 publishers, 6,456 ratings and 2,793 reviews, with books at the centre. Before the tasks, I checked the keys with LEFT JOIN … IS NULL: none without a match. And I found the trap: joining ratings and reviews directly by book yields 26,167 rows, because every rating pairs with every review of the same book. So in every task I aggregated each table in a CTE before the join.

Method

All the work (counting, averaging, filtering, sorting and joining) happens inside SQL; pandas only displays the result. One example, the publisher with the most books over 50 pages: the CTE aggregates per publisher, RANK() shows ties and SUM() OVER () computes the share across all publishers, before the LIMIT. The query is the one in the repository, with its Portuguese aliases.

WITH por_editora AS (
    SELECT
        publishers.publisher_id,
        publishers.publisher,
        COUNT(*) AS livros
    FROM books AS books
    JOIN publishers AS publishers ON publishers.publisher_id = books.publisher_id
    WHERE books.num_pages > 50
    GROUP BY publishers.publisher_id, publishers.publisher
)
SELECT
    RANK() OVER (ORDER BY livros DESC)             AS posicao,
    publisher,
    livros,
    ROUND(100.0 * livros / SUM(livros) OVER (), 2) AS pct_dos_livros
FROM por_editora
ORDER BY livros DESC
LIMIT 5;

Findings

• The catalogue is recent. 819 of the 1,000 books (81.9%) came out after 1 January 2000.
• Popularity is not quality. Twilight has the most reviews (7) and ratings (160), yet averages 3.66, below the catalogue average (3.9). The correlation between number of reviews and average rating is 0.02.
• A 5-star average from a few ratings misleads. 44 books average 5, and none has more than 4 ratings. So the author ranking only counts books with 50 ratings or more: just 19 books qualify, and J.K. Rowling/Mary GrandPré leads with 4.29 over 310 ratings.
• The catalogue is spread out. Penguin Books is the largest publisher with only 42 books (4.23%), followed by Vintage (31).
• Readers write. The 6 users who rated more than 50 books write 24.33 reviews on average, against 17.19 for the other 154. With only 6 people, it is a tendency, not a rule.

The decision

An app that wants to compete needs a current catalogue and deals with many publishers, since none holds more than 4.23% of the books. It should show the average rating next to the number of ratings and require a minimum in rankings, so a book with two 5-star ratings does not jump ahead of one with 80 ratings averaging 4.4. And it is worth investing in reviews and community, starting with the most active readers.

Both projects were approved by the TripleTen reviewer.

Links

Repository · Part 1: repurchase at Olist