Spreadsheet
1. Segment users into paid vs organic registrations.
In this example, we will assume that registrations that come from Social Media came through an ad campaign, meaning the company had to pay for an ad to register that user. Assuming all other Acquisition Source values are not paid campaigns, we can classify the rest as organic. Here's our approach.
2. Identify users that registered through domestic paid campaigns.
To segment our data based on multiple conditions, we will need to use a combination of functions in Google Sheets. To classify a user as a domestic paid user, we need to two conditions to be true:
- Country = "United States"
- Acquisition Source = "Social Media"
After trying it on your own, see our approach here - we first check to see if both conditions are met, and if so then we label the user as "Domestic Paid".
=IF(AND(D3 = "United States", E3 = "Social Media") = TRUE, "Domestic Paid", "Other")
3. Create a flag for low value transactions (payment amount < 100 or returned payment).
Just like question 2, we will need to use a combination of functions in Google Sheets to solve this problem. Instead of using the AND function, we will use an OR combined with an IF .
After trying it on your own, see our approach here - we first check to see if either condition is met, and if so then we label the transactions as "Low Value".
=IF(OR(E3 < 100, F3 = TRUE) = TRUE, "Low Value", "Other")
4. Segment transactions by whether they were before, during or after the holiday season of Nov and Dec 2018.
Since we are going to be comparing more than two conditions, we will need to use the IFS function as opposed to the simple IF . Using the AND function within the IFS allows us to set the conditions for the three different date ranges.
After trying it on your own, see our approach here - we set the values for transaction type based on the different conditions that are met.
=IFS(C3 < DATE(2018,11,1), "Pre Holiday Season",AND(C3 > DATE(2018,10,31), C3 < DATE(2019,1,1)), "Holiday Season", C3 > DATE(2018,12,31), "Post Holiday Season")
5. Coming soon
Database and SQL
-- 1. Segment users into paid vs organic registrations
-- 2. Identify users that registered through domestic paid campaigns
-- 3. Create a flag for low value transactions (payment amount < 100 or returned payment)
-- 4. Segment transactions by whether they were before, during or after the holiday season of Nov and Dec 2018
-- 5. Coming soon
-- 1. Segment users into paid vs organic registrations
SELECT
user_id
, signup_timestamp
, country
, acquisition_source
, user_deleted
, CASE WHEN acquisition_source = 'Social Media' THEN 'Paid'
ELSE 'Organic'
END AS acquisition_type
FROM getting_started.users
;
-- 2. Identify users that registered through domestic paid campaigns
SELECT
user_id
, signup_timestamp
, country
, acquisition_source
, user_deleted
, CASE WHEN country = 'United States' AND acquisition_source = 'Social Media' THEN 'Domestic Paid'
ELSE 'Other'
END AS international_acquisition_type
FROM getting_started.users
;
-- 3. Create a flag for low value transactions (payment amount < 100 or returned payment)
SELECT
user_id
, payment_timestamp
, payment_id
, payment_amount
, payment_returned
, CASE WHEN payment_amount < 100 OR payment_returned = TRUE THEN 'Low Value'
ELSE 'Other'
END AS transaction_type
FROM getting_started.payments
;
-- 4. Segment transactions by whether they were before, during or after the holiday season of Nov and Dec 2018
SELECT
user_id
, payment_timestamp
, payment_id
, payment_amount
, payment_returned
, CASE WHEN payment_timestamp BETWEEN '2018-01-01' AND '2018-10-31' THEN 'Pre Holiday Season'
WHEN payment_timestamp BETWEEN '2018-11-01' AND '2018-12-31' THEN 'Holiday Season'
WHEN payment_timestamp > '2018-12-31' THEN 'Post Holiday Season'
END AS payment_time_period
FROM getting_started.payments
;
-- 5. Coming soon
BI Software
1. Segment users into paid vs organic registrations.
After trying it on your own, here's our approach.
2. Identify users that registered through domestic paid campaigns.
After trying it on your own, here's our approach.
3. Create a flag for low value transactions (payment amount < 100 or returned payment).
After trying it on your own, here's our approach.
4. Segment transactions by whether they were before, during or after the holiday season of Nov and Dec 2018.
Because we are going to compare more than two conditions, we will need to enter development mode and add code to the LookML, as this use case isn't supported by table calculations.
After trying it on your own, here's our approach - we write a CASE WHEN statement with multiple conditions to create a new dimension in the LookML.
dimension: transaction_type {
sql: CASE WHEN ${TABLE}.payment_timestamp < '2018-11-01' THEN 'Pre Holiday Season'
WHEN ${TABLE}.payment_timestamp BETWEEN '2018-11-01' AND '2018-12-31' THEN 'Holiday Season'
WHEN ${TABLE}.payment_timestamp > '2018-12-31' THEN 'Post Holiday Season'
END;;
5. Coming soon