Blog Data Analysis

The Challenge

Blog owner travel around the world and sell two types of products. One is an e-book and the second one is a course about best practices. On his blog he has thousands of readers every day. He uses a special logging software to monitor the performance of their blog. The business strategy is simple:

New readers visit the blog.
They read articles.
They subscribe to the newsletter.
They purchase the info products.

The marketing budget is not too high at the moment: He spends only ~$1000 a month combined:

for Adwords advertisement (~$500 a month for paid ads),
for SEO (~$250 a month for editing) and
for Reddit (~$250 a month for content creation)

Project Overview

Client wants to invest more (time, work and money). But he would like to do it in a smart way. First let’s start our server and set up the environment.

Used Technology:

Digital Ocean
Bash
Python
Canva

Data Preprocessing and Exploration

In this data set, you will find the raw user activity data from the blog between 1 January 2018 and 31 March 2018. It’s a log with ~600.000 rows. Here is a screenshot about the raw data:

During the data cleaning process we have to create common columns. As you can see there are different types of information recorded for different events. This is the code how we deal with it:

Copy to Clipboard

import pandas as pd
import csv
# Function to parse rows based on event type
def parse_row(row):
    event_type = row[1]
    if event_type == 'buy':
        return {
            'Date_Time': row[0],
            'Event_Type': row[1],
            'User_Id': row[2],
            'Price': row[3]
        }
    elif event_type == 'subscribe':
        return {
            'Date_Time': row[0],
            'Event_Type': row[1],
            'User_Id': row[2]
        }
    elif event_type == 'read':
        if len(row) == 6:
            return {
                 'Date_Time': row[0],
                 'Event_Type': row[1],
                'Country': row[2],
                'User_Id': row[3],
                'Source': row[4],
                'Topic': row[5]
            }
        elif len(row) == 5:
            return {
                'Date_Time': row[0],
                'Event_Type': row[1],
                'Country': row[2],
                'User_Id': row[3],
                'Topic': row[4]
            }
    return None

# List to hold each row's data dictionary
data_raw = []
# Path to the CSV file
file_path = '/data.csv'
# Read and parse the CSV file
with open(file_path, newline='', encoding='utf-8') as csvfile:
    reader = csv.reader(csvfile, delimiter=';')
    for row in reader:
        row_data = parse_row(row)
        if row_data:
            data_raw.append(row_data)
# Convert list of dictionaries to DataFrame
data = pd.DataFrame(data_raw)
# Display the DataFrame
print(data.head())

---------------------

Date_Time Event_Type    Country     User_Id   Source  \
0  2018-01-01 00:01:01       read  country_7  2458151261      SEO   
1  2018-01-01 00:03:20       read  country_7  2458151262      SEO   
2  2018-01-01 00:04:01       read  country_7  2458151263  AdWords   
3  2018-01-01 00:04:02       read  country_7  2458151264  AdWords   
4  2018-01-01 00:05:03       read  country_8  2458151265   Reddit

Topic Price  
0  North America   NaN  
1  South America   NaN  
2         Africa   NaN  
3         Europe   NaN  
4  North America   NaN

The result is much better than the raw data. Now we can continue with the data exploration part.

During the general exploration we are able to see the big picture, which is not always useful but it can help us later.

Country
country_5    25.042900
country_7    22.088402
country_2    21.755405
country_4    11.918007
country_6    10.662617
country_8     3.283648
country_3     1.526672
country_1     1.042144
Name: count, dtype: float64

Country Data Analysis

Copy to Clipboard

User Id Analysis

Copy to Clipboard

data['User_Id'].value_counts()

---------------------

User_Id
2458174315    61
2458203397    55
2458280511    55
2458284001    55
2458197594    55
              ..
2458234354     1
2458234356     1
2458234359     1
2458234360     1
2458256272     1
Name: count, Length: 210023, dtype: int64

data['Price'] = pd.to_numeric(data['Price'], errors='coerce')

# Filter out rows where 'Price' is NaN
filtered_df = data.dropna(subset=['Price'])

#Group by 'User_Id' and sum 'Price'
purchase_totals = filtered_df.groupby('User_Id')['Price'].sum()

#Sort the users by total purchases in descending order
top_users = purchase_totals.sort_values(ascending=False)

print(top_users)

---------------------

User_Id
2458151279    88.0
2458230675    88.0
2458230731    88.0
2458230793    88.0
2458230927    88.0
              ... 
2458244149     8.0
2458244126     8.0
2458244104     8.0
2458171021     8.0
2458346214     8.0
Name: Price, Length: 6648, dtype: float64

user_id_inspect = data[data['User_Id'] == '2458151279']
user_id_inspect

---------------------

Date_Time 	Event_Type 	Country 	User_Id 	Source 	Topic 	Price
20 	2018-01-01 00:13:39 	read 	country_4 	2458151279 	SEO 	North America 	NaN
211 	2018-01-01 02:53:39 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
427 	2018-01-01 05:36:39 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
3455 	2018-01-02 17:53:55 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
5918 	2018-01-03 21:15:01 	read 	country_4 	2458151279 	NaN 	North America 	NaN
6234 	2018-01-04 00:50:27 	buy 	NaN 	2458151279 	NaN 	NaN 	8.0
6346 	2018-01-04 01:53:26 	subscribe 	NaN 	2458151279 	NaN 	NaN 	NaN
6434 	2018-01-04 02:45:01 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
6465 	2018-01-04 03:15:01 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
6784 	2018-01-04 06:38:01 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
6885 	2018-01-04 07:48:01 	read 	country_4 	2458151279 	NaN 	Australia 	NaN
8627 	2018-01-05 01:25:32 	read 	country_4 	2458151279 	NaN 	Asia 	NaN
8894 	2018-01-05 04:08:32 	read 	country_4 	2458151279 	NaN 	Europe 	NaN
9536 	2018-01-05 10:23:05 	buy 	NaN 	2458151279 	NaN 	NaN 	80.0
11759 	2018-01-06 08:45:38 	read 	country_4 	2458151279 	NaN 	South America 	NaN

Source, Topic and Price Analysis

Copy to Clipboard

How many readers / country / source ?

Copy to Clipboard

Add source and country information to buy and subscribe event

Copy to Clipboard

# Step 1: Create a mapping from the first 'read' events
read_info = data[data['Event_Type'] == 'read'][['User_Id', 'Country', 'Source']]
user_country_source_mapping = read_info.drop_duplicates(subset='User_Id', keep='first').set_index('User_Id')

# Step 2: Merge this mapping back to the main DataFrame to fill missing values for 'buy' and 'subscribe' events
data.set_index('User_Id', inplace=True)
data.update(user_country_source_mapping, overwrite=False)
data.reset_index(inplace=True)

print(data.head())

---------------------
 User_Id            Date_Time Event_Type    Country   Source  \
0  2458151261  2018-01-01 00:01:01       read  country_7      SEO   
1  2458151262  2018-01-01 00:03:20       read  country_7      SEO   
2  2458151263  2018-01-01 00:04:01       read  country_7  AdWords   
3  2458151264  2018-01-01 00:04:02       read  country_7  AdWords   
4  2458151265  2018-01-01 00:05:03       read  country_8   Reddit

Topic  Price  
0  North America    NaN  
1  South America    NaN  
2         Africa    NaN  
3         Europe    NaN  
4  North America    NaN

Calculate Total Revenue, Conversion Rate and readers per country

Copy to Clipboard

# Convert 'Price' from string to float
data['Price'] = pd.to_numeric(data['Price'], errors='coerce')

# Step 1: Total Revenue per Country
total_revenue = data[data['Event_Type'] == 'buy'].groupby('Country')['Price'].sum()

total_revenue

---------------------

Country
country_1      920.0
country_2    39528.0
country_3     3616.0
country_4    33464.0
country_5    57920.0
country_6    16432.0
country_7    39760.0
country_8     2840.0
Name: Price, dtype: float64

# Step 2: Conversion Rate per Country (adjusted for all purchases)
# Total purchases per country
total_purchases_per_country = data[data['Event_Type'] == 'buy'].groupby('Country').size()
# Total unique users per country (remains the same)
unique_users_per_country = data.groupby('Country')['User_Id'].nunique()
# Adjusted conversion rate calculation
conversion_rate = (total_purchases_per_country / unique_users_per_country) *100
conversion_rate

---------------------

Country
country_1     0.669819
country_2     3.534287
country_3     5.324581
country_4    10.995216
country_5     6.188505
country_6     2.201823
country_7     3.357726
country_8     0.723374
dtype: float64

# Filter data for 'read' events and group by 'Country' to count unique 'User_Id'
unique_readers_per_country = data[data['Event_Type'] == 'read'].groupby('Country')['User_Id'].nunique()

unique_readers_per_country

---------------------

Country
country_1     5076
country_2    50675
country_3     2742
country_4    12751
country_5    40349
country_6    31156
country_7    51791
country_8    15483
Name: User_Id, dtype: int64

Calculate the ROI per sources

Copy to Clipboard

# Ensure 'Price' is a numeric value
data['Price'] = pd.to_numeric(data['Price'], errors='coerce')

# Step 1: Aggregate Total Revenue by Source
# Filter to 'buy' events and then group by 'Source' to sum up revenue
total_revenue_by_source = data[data['Event_Type'] == 'buy'].groupby('Source')['Price'].sum()

# Step 2: Total Ad Spend by Source
# Define ad spends for each source
ad_spends = {'AdWords': 1500, 'SEO': 750, 'Reddit': 750}

# Convert the ad_spends dictionary to a pandas Series for easier manipulation
ad_spends_series = pd.Series(ad_spends)

# Step 3: Calculate ROI by Source
# Compute ROI for each source
roi_by_source = ((total_revenue_by_source - ad_spends_series) / ad_spends_series) * 100

# Convert ROI results to a DataFrame for presentation
roi_calculations = pd.DataFrame({
    'Total Revenue': total_revenue_by_source,
    'Ad Spend': ad_spends_series,
    'ROI (%)': roi_by_source
}).fillna(0)  # Fill NaN where no revenue data is available

roi_calculations

---------------------

Total Revenue 	Ad Spend 	ROI (%)
AdWords 	27664.0 	1500 	1744.266667
Reddit 	89760.0 	750 	11868.000000
SEO 	77056.0 	750 	10174.133333

Conversion Rates Per Source

Copy to Clipboard

# Step 1: Count unique users exposed per source
users_exposed_per_source = data[data['Event_Type'].isin(['read', 'subscribe'])].groupby('Source')['User_Id'].nunique()

# Step 2: Count unique purchasers per source
# Filter for 'buy' events and count unique users
unique_purchasers_per_source = data[data['Event_Type'] == 'buy'].groupby('Source')['User_Id'].nunique()

# Step 3: Calculate Conversion Rates
# Divide the number of unique purchasers by the number of users exposed
conversion_rates_per_source = (unique_purchasers_per_source / users_exposed_per_source) * 100

# Create a DataFrame to present the results
conversion_rates_df = pd.DataFrame({
    'Users Exposed': users_exposed_per_source,
    'Unique Purchasers': unique_purchasers_per_source,
    'Conversion Rate (%)': conversion_rates_per_source
}).fillna(0)  # Fill NaN with 0 where no purchasers are recorded

# Print the results
print("Conversion Rates Per Source:")
print(conversion_rates_df)

---------------------

Users Exposed  Unique Purchasers  Conversion Rate (%)
Source                                                        
AdWords          63065               1029             1.631650
Reddit          105216               3094             2.940617
SEO              41742               2525             6.049063

Visitors per country

Copy to Clipboard

# Step 1: Aggregate unique users per country across all events
unique_visitors_per_country = data.groupby('Country')['User_Id'].nunique()

# Step 2: Calculate total unique visitors across all countries
total_unique_visitors = unique_visitors_per_country.sum()

# Step 3: Calculate percentage of total for each country
percentage_of_visitors = (unique_visitors_per_country / total_unique_visitors) * 100

# Combine the counts and percentages into a single DataFrame for better presentation
visitor_stats = pd.DataFrame({
    'Unique Visitors': unique_visitors_per_country,
    'Percentage of Total (%)': percentage_of_visitors
})

# Display the results for all countries
print(visitor_stats)

---------------------

Unique Visitors  Percentage of Total (%)
Country                                            
country_1             5076                 2.416878
country_2            50675                24.128310
country_3             2742                 1.305571
country_4            12751                 6.071240
country_5            40349                19.211705
country_6            31156                14.834566
country_7            51791                24.659680
country_8            15483                 7.372050

Revenue per country per source

Copy to Clipboard

Purchase Funnel

Copy to Clipboard

# Convert 'Date_Time' to datetime format if not already
data['Date_Time'] = pd.to_datetime(data['Date_Time'])

# Define new readers (first time a user is seen with a source)
first_visits = data[data['Source'].notnull()].groupby('User_Id')['Date_Time'].min().reset_index()
first_visits['Stage'] = 'New Reader'

# Define returning readers (subsequent visits where source is null)
data_returning = data[data['Source'].isnull()]
returning_readers = data_returning[data_returning['User_Id'].isin(first_visits['User_Id'])]
returning_readers = returning_readers.groupby('User_Id')['Date_Time'].min().reset_index()
returning_readers['Stage'] = 'Returning Reader'

# Identify subscribers
subscribers = data[data['Event_Type'] == 'subscribe'].groupby('User_Id')['Date_Time'].min().reset_index()
subscribers['Stage'] = 'Subscribe'

# Identify purchasers
purchasers = data[data['Event_Type'] == 'buy'].groupby('User_Id')['Date_Time'].min().reset_index()
purchasers['Stage'] = 'Purchase'

# Combine all data to see the funnel progress
funnel_data = pd.concat([first_visits, returning_readers, subscribers, purchasers], ignore_index=True)
funnel_data.sort_values(by=['User_Id', 'Date_Time'], inplace=True)

# Calculate progression
unique_users_at_each_stage = funnel_data.groupby('Stage')['User_Id'].nunique()

# Display the number of unique users at each stage of the funnel
print(unique_users_at_each_stage)

---------------------

Stage
New Reader    210023
Purchase        6648
Subscribe       7618
Name: User_Id, dtype: int64

Most reads topics

Copy to Clipboard

# Step 1: Filter data for 'read' events
read_events = data[data['Event_Type'] == 'read']

# Step 2: Count reads by topic
reads_by_topic = read_events['Topic'].value_counts()

# Step 3: Display most read topics
print("Most Read Topics:")
print(reads_by_topic)

---------------------

Most Read Topics:
Topic
Asia             194925
North America    130334
Europe            93697
South America     76801
Africa            43394
Australia         42726
Name: count, dtype: int64

# Step 1: Filter data for 'read' events
read_events = data[data['Event_Type'] == 'read']

# Step 2: Group by 'Country' and 'Topic' and count reads
reads_by_country_topic = read_events.groupby(['Country', 'Topic']).size().reset_index(name='Read Count')

# Step 3: Sort by read count within each country
reads_by_country_topic.sort_values(by=['Country', 'Read Count'], ascending=[True, False], inplace=True)

# Step 4: Get the top 3 topics for each country
top_3_topics_per_country = reads_by_country_topic.groupby('Country').head(3)

# Display the top 3 topics per country
print("Top 3 Read Topics Per Country:")
print(top_3_topics_per_country)

---------------------

Top 3 Read Topics Per Country:
      Country          Topic  Read Count
1   country_1           Asia        2052
4   country_1  North America        1302
3   country_1         Europe        1175
7   country_2           Asia       43887
10  country_2  North America       29088
9   country_2         Europe       20525
13  country_3           Asia        3023
16  country_3  North America        2091
15  country_3         Europe        1377
19  country_4           Asia       23945
22  country_4  North America       15676
21  country_4         Europe       11874
25  country_5           Asia       49913
28  country_5  North America       33971
27  country_5         Europe       24057
31  country_6           Asia       21479
34  country_6  North America       14173
33  country_6         Europe       10290
37  country_7           Asia       44142
40  country_7  North America       29707
39  country_7         Europe       20933
43  country_8           Asia        6484
46  country_8  North America        4326
45  country_8         Europe        3466

# Step 1: Filter data for 'read' events
read_events = data[data['Event_Type'] == 'read']

# Step 2: Group by 'Country' and 'Topic' and count reads
reads_by_country_topic = read_events.groupby(['Country', 'Topic']).size().reset_index(name='Read Count')

# Step 3: Sort by read count within each country
reads_by_country_topic.sort_values(by=['Country', 'Read Count'], ascending=[True, False], inplace=True)

# Step 4: Get the top 3 topics for each country
top_3_topics_per_country = reads_by_country_topic.groupby('Country').head(3)

# Display the top 3 topics per country
print("Top 3 Read Topics Per Country:")
print(top_3_topics_per_country)

---------------------

# Step 1: Filter data for 'read' events
read_events = data[data['Event_Type'] == 'read']

# Step 2: Group by 'Source' and 'Topic' and count reads
reads_by_source_topic = read_events.groupby(['Source', 'Topic']).size().reset_index(name='Read Count')

# Step 3: Sort by read count within each source
reads_by_source_topic.sort_values(by=['Source', 'Read Count'], ascending=[True, False], inplace=True)

# Step 4: Get the top topics for each source, or you can customize the number of topics you want to see
top_topics_per_source = reads_by_source_topic.groupby('Source').head(3)  # Adjust the number here to show more or fewer topics

# Display the top topics per source
print("Top Read Topics Per Source:")
print(top_topics_per_source)

---------------------

Top Read Topics Per Source:
     Source          Topic  Read Count
3   AdWords         Europe       41135
1   AdWords           Asia       37306
4   AdWords  North America       16372
7    Reddit           Asia      124128
10   Reddit  North America       40503
9    Reddit         Europe       31595
16      SEO  North America       73459
13      SEO           Asia       33491
17      SEO  South America       32630

Visualization

From previous section we already have the data. Let’s convert them into beautiful charts.

Let start with the first and most obvious question:

In which country should he prioritize his effort and why?

To answer this complex question we need a complex chart. Let’s put together the best KPI indicators and it will give us the answer.

Country 4 has the biggest potential due to high revenue and visitor conversion rate.

Country 5 also worse to consider due to high revenue and above average conversion rate and high number of readers

Reddit has the best ROI indicator

For the best ROI focus the resources to Reddit. The recommended source weighting can be found in the table.

Most of the visitors coming from Country 2 and Country 7.

Country 5 generates the most revenue.

Within Country 5 Reddit leads the revenue generated championship and it is also true for the other countries too.

Best Conversion Rate provided by SEO

The most read article is Asia.

Asia is clearly a favorite subject for readers. Interestingly, SEO visitors are mainly interested in North America, while paid visitors are interested in Europe.

The final conversion rate is 3%.

On the purchase funnel chart you can clearly see that how important is the conversion optimization. 7 out 10 visitors is not going to purchase, but there is a big potential in it.

Conclusion

This has turned out to be a good lengthy article. But as you can see, data analysis is not a 2-minute exercise. We need to dig deep into the data to find the obviousness correlations.
These correlations are usually shown on 3-4 dimension charts, but it is worth it because it can give you a real competitive advantage.

The Challenge

Project Overview

Data Preprocessing and Exploration

Country Data Analysis

User Id Analysis

Source, Topic and Price Analysis

How many readers / country / source ?

Add source and country information to buy and subscribe event

Calculate Total Revenue, Conversion Rate and readers per country

Calculate the ROI per sources

Conversion Rates Per Source

Visitors per country

Revenue per country per source

Purchase Funnel

Most reads topics

Visualization

In which country should he prioritize his effort and why?

Reddit has the best ROI indicator

Most of the visitors coming from Country 2 and Country 7.

Country 5 generates the most revenue.

Best Conversion Rate provided by SEO

The most read article is Asia.

The final conversion rate is 3%.

Conclusion

Do you want to know more about your customer and increase revenue with that knowledge?

Contact us!