Analyze SQL Assignment - Marketing Attribution - Cool T-Shirts Feedback

Hey :slight_smile:

This is the link to my assignment on google slides. Would appreciate any feedback.

Thanks!

Marcy

4 Likes

Hi there

I have reviewed your project as per rubric guide provided by codeacademy (all data is out of 4):

Report : 4
Query Accuracy : 4
Query Formating : 4
Understanding concepts : 3

Overall rating is very good

I have one feedback : presentation skills can be improved . Visualization of data can be worked upon with excel and powerpoint.

Hope this will help

Hello Can you review my project also . MARKETING ATTRIBUTES OF COOL TSHIRTS.pdf (533.1 KB)

Hello, I’d like feedback on the last assignment:

CoolTShirts can re-invest in 5 campaigns. Which should they pick and why?

it seems I found different results from others here, and wish also a feedback on the query (possible to simplify).

My rationale was to find the ratio between last touches for purchases and all last touches.

Sorry guys for not preparing slides, Im just focusing on coding now. tks!


WITH last_touch AS (
    SELECT user_id,
        page_name,
        MAX(timestamp) as last_touch_at
    FROM page_visits
    GROUP BY user_id),
    all_touch as (
    SELECT 
            pv.utm_campaign,
            pv.utm_source,
            count(*) as nLastTouch
    FROM last_touch as ft
    JOIN page_visits as pv
        ON ft.user_id = pv.user_id
        AND ft.last_touch_at = pv.timestamp
    group by 1, 2
    order by 3 desc),
    purchase_touch as (
    SELECT 
            -- ft.page_name,
            pv.utm_campaign,
            pv.utm_source,
            count(*) as nLastTouch
    FROM last_touch as ft
    JOIN page_visits as pv
        ON ft.user_id = pv.user_id
        AND ft.last_touch_at = pv.timestamp
    where ft.page_name = '4 - purchase'
    group by 1, 2
    order by 3 desc)
    
    select a.nLastTouch, a.utm_campaign, p.nLastTouch, 1.0 *  p.nLastTouch / a.nLastTouch as retention
    from purchase_touch as p
    join all_touch as a
    on a.utm_campaign = p.utm_campaign
    order by 4 desc;

I don’t think that’s a great idea since you shouldn’t only consider last_touch since a first_touch is really importanta as well.
You could also find very discrepant data, imagine if there was a campaign responsible for only 10 last_touches but ended up getting people to purchase 8/10 times. It would be a great ratio but a terrible campaign if we considered that all of them cost the same.