About¶

This notebook explores the conversion dataset.

The conversions.tsv dataset has one row per search conversion.

The dataset tells you which photo has been downloaded for a search, the country of origin, and an anonymous identifier to indiciate the unique users.

Source

We will use this dataset to understand the type of queries, that users in the platform are searching.

Exploring the data¶

In [1]:

import pandas as pd

In [2]:

pd.set_option('display.max_rows', 100)

In [3]:

path = "../data/raw/conversions.tsv000"

In [4]:

df = pd.read_csv(path,sep="\t")

In [5]:

len(df)

Out[5]:

12166088

sample view of the data

In [6]:

df.head()

Out[6]:

	converted_at	conversion_type	keyword	photo_id	anonymous_user_id	conversion_country
0	2020-07-29 00:08:04.221	download	clouds	ABmygVJcYgY	dd01ebdd-7691-4518-ab19-b2105782ae8b	VE
1	2020-07-29 00:25:23.426	download	shark	fB2jl6Rb3l4	c48ba6e0-c6a7-4a92-b569-fe57808a8a2c	QA
2	2020-07-29 00:26:13.122	download	dogs	k1hbfag2na0	62c4f043-579c-438f-8815-eb8ba3c54d34	KR
3	2020-07-29 00:37:03.308	download	astronaut	-SyUjRlHauQ	7ad6dc18-a02e-4ba2-b93c-fd7ea2e551d8	JP
4	2020-07-29 00:54:28.942	download	red roses	A0iTJUhK4es	f03a5708-32e4-4fae-8210-3c5d2632cbfb	NZ

Get top queries

In [7]:

df_res = df.groupby(["keyword"], as_index=False)\
            .size()\
            .sort_values("size", ascending=False)\
            .rename(columns={'size':'num_searches'})

In [8]:

print (f"Number of unique queries: {len(df_res)} ")

Number of unique queries: 569996

In [9]:

df_res.head(30)

Out[9]:

	keyword	num_searches
334943	nature	381173
445718	sky	239848
193034	flowers	202391
333735	natural	196189
189492	flower	175126
431887	sea	165744
325200	mountain	161816
198609	forest	153677
350461	ocean	145435
45100	beach	136862
460237	space	120184
146484	dog	112637
328262	mountains	111005
533443	water	109987
320914	moon	106111
550361	winter	89541
90851	cat	87984
528686	wallpaper	87079
19880	animal	79378
507852	tree	78697
345303	night sky	77892
482869	sunset	75404
343674	night	74551
278629	landscape	72824
481555	sunrise	72290
161638	earth	70303
21578	animals	68001
518127	universe	66711
475829	summer	66019
387151	plant	64406

What can we say about the typical queries ?¶

Most of the queries seem to be under <3 keywords.
Users in the platform are interested in nature
no normalizations is done for the queries; animal vs animals ; vs mountain vs mountains

In [ ]:

Queries like above with "broad" intent are not that useful for comparing results

In [ ]:

Exploring Longer Queries¶

In [10]:

df_res["num_keywords"] = df_res["keyword"].apply(lambda x: len(x.split(" ")))

In [11]:

df_long_queries = df_res[(df_res["num_keywords"] > 1) ]

In [12]:

df_long_queries[df_long_queries.num_keywords > 4].head(50)

Out[12]:

	keyword	num_searches	num_keywords
327457	mountain star landscape night sky	779	5
287590	light at the end of the tunnel	308	7
499894	there is no planet b	242	5
276561	lago di braies, braies, italy	118	5
534678	water droplets on a leaf	106	5
224699	great sand dunes national park	94	5
258115	image of a man in a desert	82	7
274846	konkan beach resort, ratnagiri, india	73	5
335652	nature backgrounds water ripple reflection	67	5
459722	south georgia and the south sandwich islands	54	7
426421	samsung note 10 lite wallpaper	52	5
37977	background image for google doc	51	5
141140	desert sunset nature landscape sky	49	5
61505	black grapes with wood plates	44	5
454677	snow mountain clear blue sky	44	5
364313	palm trees at the beach	43	5
349030	nova scotia duck tolling retriever	43	5
499403	the surface of the moon	37	5
499623	the waves of the sea	37	5
263540	iphone 11 pro max wallpaper	36	5
27378	art of table potted flower	35	5
337991	nature photos light colours	35	5
504377	torres del paine national park	32	5
134706	dark side of the moon	31	5
67340	blue sky and white clouds	31	5
375214	person on top of mountain	31	5
277772	lake with lotus and lilies photos	29	6
50421	beauitful wallpaper nature 8k	29	5
287582	light at end of tunnel	28	5
26499	ariel view of the ocean	28	5
177964	farmhouse rustic yellow and pink	28	5
161094	eagle flying in the sky	27	5
569329	沙漠青蛙沙漠青蛙 desert frog	26	14
415209	ripley's aquarium of canada, toronto, canada	26	6
425941	salar de uyuni uyuni bolivia	25	5
143650	dew drops on a grass	25	5
123267	couple romdik love photo in tamil	25	6
304156	man on top of mountain	25	5
193247	flowers and plants and trees	24	5
497287	the butterfly atrium at hershey gardens	24	6
277773	lake with lotus and lily	24	5
479245	sun rise on a mountain	24	5
203350	free hd luminious backgrounds for photos	24	6
298593	lower antelope canyon, page, united states	24	6
393216	por do sol no mar	23	5
142678	desktop wallpapers 1920 x 1080	22	5
534769	water drops on the rose	22	5
437160	seven wonders of the world	20	5
66424	blue lake and green shore	19	5
295937	looking up to the sky	19	5

Interesting Queries¶

Detailed Intent

water droplets on a leaf
image of a man in a desert
person on top of mountain

Location:

ripley's aquarium of canada, toronto, canada
the butterfly atrium at hershey gardens

Non English Queries

salar de uyuni uyuni bolivia
沙漠青蛙沙漠青蛙 desert frog
por do sol no mar
conhece te a ti mesmo ( Greek for know thyself)

Metaphors / Slogan:

light at the end of the tunnel
there is no planet b

Multiple Candidates

seven wonders of the world

Long Query / Single Intent

nova scotia duck tolling retriever ( dog breed)

In [ ]:

Non frequently searched queries

In [13]:

df_long_queries[df_long_queries.num_keywords > 4].tail(50)

Out[13]:

	keyword	num_searches	num_keywords
313105	mid night star picture for youtube thumbnail	1	7
119583	cool gamer pics for free	1	5
313060	mid century gothic style rose painting	1	6
313079	mid century modern interior design	1	5
313077	mid century modern home interior	1	5
313076	mid century modern home decor	1	5
313160	middle aged women beauty	1	5
313148	middle age is an age of many colors.	1	8
313185	middle east night in the desert	1	6
313694	milky way at the sea	1	5
313709	milky way by the nasa	1	5
119302	cool adventurous places one can visit with a b...	1	16
119308	cool and colorful wallpapers	1	5
119310	cool and fun pictures of animals	1	6
313799	milky way moon 3000x3000	1	5
119239	cooking over a flame	1	5
313744	milky way galaxy and man	1	5
313765	milky way galaxy with people	1	5
119390	cool beach romance for familly	1	5
119374	cool backgrounds with cool wolves	1	5
313519	miles pond, vt chamber of commerce	1	6
315756	minimalist autumn wallpaper for mac	1	5
118529	constantia, cape town, south africa	1	5
118506	conserve energy hd images	1	5
315504	minimal windows 10 wallpaper plants	1	5
315497	minimal white pot with green leaves	1	6
316032	minimalist nature black and white	1	5
316000	minimalist lotus whitte background flower	1	5
316020	minimalist motivation work hard beach	1	5
316101	minimalist qoutes for travel wallpaper	1	5
118232	conhece te a ti mesmo	1	5
315931	minimalist gentle monochrome simple macro text...	1	7
315959	minimalist home decor with plants	1	5
315905	minimalist flower black and white	1	5
314828	minimal black and white background	1	5
118809	contaminated and counterfeited bottled water	1	5
314994	minimal flower on white background	1	6
314905	minimal colorful art on white background	1	6
314803	minimal background texture nature plants	1	5
314801	minimal background stacks of magazines	1	5
314765	minimal background dark double screen	1	5
118848	contemporary architecture made from wood	1	5
314790	minimal background nature soft brown	1	5
118850	contemporary art gallery at night	1	5
314729	minimal art black and white	1	5
315333	minimal scene with geometric forms.	1	5
118589	constellations in the night sky	1	5
315010	minimal food flat lay background	1	5
118714	construction worker at the beach	1	5
118716	construction worker in the winter	1	5

In [ ]: