PPC Analysis#

Web Analytics#

Trafic sources to our website
- Direct
- Search Engine
- Campaign
- QR Code
Time spent on site - Analyze for refining content & layout
- Time on site
- Time on page
- Time on part of the page
Bounce Rate / Abandonment rate
- (<< 40%-55%)

What are we doing ?

Most effective way to advertize through PPC :-

Which campaign is most effective?
Which platform is most effective?
How are we doing with our advertizing money ?

Usual steps : Integrate-> Clean -> Analyze -> Forecast

Imports#

import pandas as pd
import numpy as np
import scipy as sp
import matplotlib.pyplot as plt
import seaborn as sns

sns.set()

Read the dataset#

df = pd.read_excel("Marketing+Analytics+Case+Study.xlsm", sheet_name='PPC Data', skiprows=4).dropna(how='all', axis=1); df.head()

	Date	AdWords Campaign ID	AdWords Impressions	AdWords Clicks	AdWords Cost	AdWords CTR	AdWords Conversions	AdWords CVR	Facebook Campaign ID	Facebook Impressions	Facebook Clicks	Facebook Cost	Facebook CTR	Facebook Conversions	Facebook CVR
0	2017-04-01	AW_Apr17	4313	64	98.56	0.0149	5	0.0711	FB_Apr17	1416	27	65.07	0.0194	3	0.1038
1	2017-04-02	AW_Apr17	6668	83	134.46	0.0125	6	0.0670	FB_Apr17	2747	37	86.21	0.0135	4	0.1082
2	2017-04-03	AW_Apr17	4878	49	80.85	0.0101	3	0.0666	FB_Apr17	2343	37	68.45	0.0157	4	0.1038
3	2017-04-04	AW_Apr17	5291	57	99.75	0.0107	4	0.0654	FB_Apr17	1169	17	40.80	0.0144	2	0.1097
4	2017-04-05	AW_Apr17	5708	75	143.25	0.0131	5	0.0645	FB_Apr17	2169	32	66.88	0.0147	4	0.1186

df.describe(include=np.number).T

	count	mean	std	min	25%	50%	75%	max
AdWords Impressions	91.0	5639.142857	978.737867	4011.0000	4814.00000	5626.0000	6625.00000	6993.0000
AdWords Clicks	91.0	65.406593	15.719753	34.0000	54.00000	63.0000	77.50000	101.0000
AdWords Cost	91.0	106.379121	33.850598	41.1400	80.38500	99.8400	127.57000	217.1500
AdWords CTR	91.0	0.011660	0.002195	0.0076	0.01015	0.0116	0.01365	0.0150
AdWords Conversions	91.0	4.428571	1.203170	2.0000	4.00000	4.0000	5.00000	7.0000
AdWords CVR	91.0	0.067576	0.004451	0.0600	0.06420	0.0671	0.07115	0.0750
Facebook Impressions	91.0	2030.956044	602.242548	1006.0000	1458.50000	2087.0000	2550.00000	2961.0000
Facebook Clicks	91.0	38.131868	13.333507	14.0000	27.00000	37.0000	49.50000	66.0000
Facebook Cost	91.0	78.038901	30.119247	30.0000	55.98500	69.3000	102.49000	157.3000
Facebook CTR	91.0	0.018805	0.003537	0.0125	0.01565	0.0191	0.02180	0.0247
Facebook Conversions	91.0	4.098901	1.570951	1.0000	3.00000	4.0000	5.00000	7.0000
Facebook CVR	91.0	0.106926	0.010287	0.0904	0.09755	0.1065	0.11500	0.1242

df.describe(include='object')

	AdWords Campaign ID	Facebook Campaign ID
count	91	91
unique	3	3
top	AW_May17	FB_May17
freq	31	31

Summary Review#

df['Overall Cost'] = df['AdWords Cost'] + df['Facebook Cost']
df['Overall Impressions'] = df['AdWords Impressions'] + df['Facebook Impressions']
df['Overall Clicks'] = df['AdWords Clicks'] + df['Facebook Clicks']
df['Overall Conversions'] = df['AdWords Conversions'] + df['Facebook Conversions']

df_analysis=df.sum(numeric_only=True).to_frame()
df_analysis.index= df_analysis.index.str.split(" ", 1, expand=True)
df_analysis = df_analysis.unstack().T.reset_index(drop=True, level=0)
df_analysis.loc['CTR',:] = df_analysis.loc['Clicks',:]/ df_analysis.loc['Impressions',:]
df_analysis.loc['CPC', :] = df_analysis.loc['Cost',:]/df_analysis.loc['Clicks',:]
df_analysis.loc['CVR', :] = df_analysis.loc['Conversions',:]/df_analysis.loc['Clicks',:]
df_analysis.T

	CTR	CVR	Clicks	Conversions	Cost	Impressions	CPC
AdWords	0.011599	0.067708	5952.0	403.0	9680.50	513162.0	1.626428
Facebook	0.018775	0.107493	3470.0	373.0	7101.54	184817.0	2.046553
Overall	0.013499	0.082360	9422.0	776.0	16782.04	697979.0	1.781155

Data Review

Question	Answer
Which PPC source has the highest click-through-rate?	Facebook
Which PPC source has the lowest cost-per-click?	Adwords
Which PPC source has the highest conversions of clicks to leads?	Facebook
Why might Facebook have a higher conversion rate?	May be people trust referral more coming directly from the friends
Based on this information, is one PPC ad type better than another?	Further Analysis required : Facebook has better conversion but CPC is also high. We also don’t know how much revenue is generated from each click
If so, which?	Might be facebook until we can substantiate more
Are there any campaigns skewing our data, if so which campaign?	Further analysis required

Campaigns Review#

def get_campaign_summary(source):
    df_campaign = df.filter(like=source).groupby(f'{source} Campaign ID').sum().T
    df_campaign.loc[f'{source} CTR',:] = df_campaign.loc[f'{source} Clicks',:]/ df_campaign.loc[f'{source} Impressions',:]
    df_campaign.loc[f'{source} CPC', :] = df_campaign.loc[f'{source} Cost',:]/df_campaign.loc[f'{source} Clicks',:]
    df_campaign.loc[f'{source} CVR', :] = df_campaign.loc[f'{source} Conversions',:]/df_campaign.loc[f'{source} Clicks',:]
    return df_campaign.T

get_campaign_summary(source="AdWords")

	AdWords Impressions	AdWords Clicks	AdWords Cost	AdWords CTR	AdWords Conversions	AdWords CVR	AdWords CPC
AdWords Campaign ID
AW_Apr17	168019.0	2042.0	3365.79	0.012153	137.0	0.067091	1.648281
AW_Jun17	169661.0	1918.0	3077.71	0.011305	130.0	0.067779	1.604645
AW_May17	175482.0	1992.0	3237.00	0.011352	136.0	0.068273	1.625000

get_campaign_summary(source="Facebook")

	Facebook Impressions	Facebook Clicks	Facebook Cost	Facebook CTR	Facebook Conversions	Facebook CVR	Facebook CPC
Facebook Campaign ID
FB_Apr17	62768.0	1169.0	2434.87	0.018624	127.0	0.108640	2.082866
FB_Jun17	58502.0	1103.0	2243.74	0.018854	118.0	0.106981	2.034216
FB_May17	63547.0	1198.0	2422.93	0.018852	128.0	0.106845	2.022479

Data Review

None of the campaigns standout either for Adwords or Facebook

df_analysis

	AdWords	Facebook	Overall
CTR	0.011599	0.018775	0.013499
CVR	0.067708	0.107493	0.082360
Clicks	5952.000000	3470.000000	9422.000000
Conversions	403.000000	373.000000	776.000000
Cost	9680.500000	7101.540000	16782.040000
Impressions	513162.000000	184817.000000	697979.000000
CPC	1.626428	2.046553	1.781155

Cost Effectiveness#

df_cost = df_analysis.T[['CPC', 'CTR', 'CVR']].T
df_cost.loc['Budget',:] = 1000
df_cost.loc['#Clicks',:] = df_cost.loc['Budget',:]/df_cost.loc['CPC',:]
df_cost.loc['#Conversions',:] = df_cost.loc['#Clicks',:]*df_cost.loc['CVR',:]
df_cost.loc['Cost/lead', :] = df_cost.loc['Budget',:]/df_cost.loc['#Conversions',:]
df_cost

	AdWords	Facebook	Overall
CPC	1.626428	2.046553	1.781155
CTR	0.011599	0.018775	0.013499
CVR	0.067708	0.107493	0.082360
Budget	1000.000000	1000.000000	1000.000000
#Clicks	614.844275	488.626411	561.433533
#Conversions	41.630081	52.523819	46.239909
Cost/lead	24.021092	19.038981	21.626340

Data Review

For every lead we spend approximately $5 less on Facebook.
We still don’t know what is the conversion of lead to paying customer.
Also Facebook and Adwords might be addressing different demographics

AIBook

PPC Analysis

Contents

PPC Analysis#

Web Analytics#

Imports#

Read the dataset#

Summary Review#

Campaigns Review#

Cost Effectiveness#