<jupyter_start><jupyter_text>Some notes about common web metrics:- **Rank**: Position that your ad appears in relation to other advertisements (refers to paid search ads).- **CTR (Click-Through-Rate)**: Rate at which users click on an ad after they see it.     - If 10 people see your advertisement and 1 person clicks on it, your CTR is 10%.- **CPC (Cost Per Click):** How much you spent each time a user clicked on your advertisement. With online advertising, you frequently only pay the site displaying your ad (or the ad network, like Google) when a user clicks on your advertisement. - **RPC (Revenue per Click):** The average revenue you make each time a user clicks on your advertisement.     - If you get 10 clicks and make one sale worth 10 dollars, then your RPC is 1 dollar. - **Conversion**: The completion of a desired action. This might be a sale, a signup or registration, participation in a survey, a video view, etc. - **Conversion Rate**: The rate at which users go from clicking to actually completing the desired action.    - If you get 10 clicks and 1 person completes the purchase, your conversion rate is 10%.- **COS (Cost of Sale)**: How much you spent to get 1 customer to convert. Don't confuse with CPA (cost per acquisition), which usually only refers to when you only pay your ad publisher when a customer makes a purchase)    - If you get 10 clicks, spend 50 cents per click, and 1 customer makes a purchase, your COS is 5 dollars- **Customer Lifetime Value**: The total revenue a customer has generated for you. Often this takes into account returns and user-support costs.- **RPM (Revenue Per Thousand Impressions**: Total revenue for every 1000 times the ad was displayed<jupyter_code>run('''
PRAGMA TABLE_INFO(transactions)
''')
run('''
SELECT  
    author,
    COUNT(DISTINCT(title)) as unique_titles,
    SUM(CASE WHEN gender = 'F' THEN price*purchases end) AS female_revenue,
    SUM(CASE WHEN gender = 'M' THEN price*purchases end) AS male_revenue
FROM
    books B
    JOIN transactions T ON B.id = T.bookid
    JOIN users U on U.id = T.userID
    JOIN authors A on A.id = B.AuthorID
GROUP BY author
ORDER BY female_revenue + male_revenue DESC
LIMIT 10
    ''')
run('''
SELECT 
    title, 
    author,
    SUM(clicks)/COUNT(*) as CTR,
    SUM(spend)/SUM(clicks) as CPC,
    SUM(price*purchases)/SUM(clicks) as RPC,
    SUM(purchases) as Conversions, 
    SUM(purchases)/SUM(clicks) as Conversion_Rate,
    SUM(spend)/SUM(purchases) as COS,
    SUM(price*purchases)/COUNT(*)*1000 RPM
FROM
    books B
    JOIN transactions T ON B.id = T.bookid
    JOIN users U on U.id = T.userID
    JOIN authors A on A.id = B.AuthorID
GROUP BY title
ORDER BY RPM DESC
LIMIT 10
    ''')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08293752283522104, 'fraction_numerical': 0.012422360248447204, 'mean_word_length': 3.81195079086116, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '425485', 'license': 'MIT', 'n_tokens_mistral': 857, 'n_tokens_neox': 777, 'n_words': 392, 'path': 'notebooks/SQL_Practice.ipynb', 'repo_name': 'spencerlyon2/SQLBootcamp', 'size': '2737'}