Kirsten Alana's Instagram Data Challenge Submission- Jinal Doshi

#1

Hello Everyone,

Being an avid Instagram fan myself, this challenge was perfect for me with my passion for data analytics and Instagram. Starting as a social Spending a couple of hours every day scrolling on Instagram, it was never just a social media tool for me. It’s a platform to express, learn and get inspired by many strong men/women out there.

This is the Dashboard I created in Chartio-

https://chartio.com/ibm-22/dashboard/319890/link_sharing/1c6fa2c9b60511946bb2202fd9654eeafa4f5cb17f60735390a29f3536f88656/

So here are the analysis i worked on and I tried to keep it simple and effective-

  1. Question- Best time of day for posting?

Here, I thought that it would involve multiple factors on understanding the best time for posting. Turns out the best time is during lunch hours 12-2 pm where the post gets average likes and comments.

SQL I used:

SELECT to_char(created_time, ‘HH24’) as “time”, avg(likes_count) as “average likes”, avg(comments_count) as “Average comments”

FROM kirstenalana_instagram_media

GROUP BY time

  1. Top 10 Hashtags generating Max comments?

Hashtags have become a huge deal now. At the time I started Instagramming, I used to not know the importance of hashtags, but now people have realized the significance and can search posts through hashtags no matter what part of the world as long as the profile is kept open to the viewers.

Hashtags play a a key role in an influencer’s life and his/her Social media presence.

SQL I used:

SELECT

value as “hashtag”,

MAX(likes_count) as “Max likes”,

MAX(comments_count) as “Max comments”

FROM kirstenalana_instagram_media

JOIN kirstenalana_instagram_media_tags

ON kirstenalana_instagram_media.id = kirstenalana_instagram_media_tags.instagram_media_id

GROUP BY value

ORDER BY “Max likes” DESC

limit 10

  1. Top 10 Locations that have the most likes engagement by viewers?

I wanted to analyze this and understand if location was a factor in getting the viewers engagement by likes/comments.

Turns out, it does. I also wanted to understand from the latitude and longitude which city/country it is to give a better visual representation but couldn’t.

Gave it a try though.

SQL I used-

select latitude, longitude, likes_count, comments_count

FROM kirstenalana_instagram_media_location

join kirstenalana_instagram_media

on kirstenalana_instagram_media_location.id = kirstenalana_instagram_media.id

order by likes_count desc

limit 10

  1. Total Profile Likes-

Just to get a feel for the statistics here on:

SQL I used:

SELECT

sum(likes_count) as “Profile likes”

FROM kirstenalana_instagram_media

  1. Total Profile Comments-

Again to understand the statistics here.

SQL I used-

SELECT

sum(comments_count) as “Profile comments”

FROM kirstenalana_instagram_media

  1. Followers Count

Started with trying to get the top follower engagement for Kirsten Alana’s Instagram data. But couldn’t. All i could get was a basic followers count so here we go-

SQL:

SELECT

counts_followed_by as “Kristen’s total followers”

FROM kirstenalana_instagram_user

7.Profile maximum likes and comments by Month & Year

This was an interesting analysis to get the maximum likes and comments by Month & Year to understand how they increase/decrease

SQL:

SELECT to_char(created_time, ‘Mon YYYY’) AS “time”,

Max(likes_count) AS “Max likes”,

Max(comments_count) AS “Max comments”

FROM kirstenalana_instagram_media

GROUP BY TIME

order by time desc

LIMIT 12

  1. Top 10 videos gathering likes engagement by viewer

To understand viewer engagement for videos and how it rated as per images

SQL:

select kirstenalana_instagram_media_videos.instagram_media_id, likes_count as “likes”, comments_count as “comments”

from kirstenalana_instagram_media

join kirstenalana_instagram_media_videos

on kirstenalana_instagram_media.id = kirstenalana_instagram_media_videos.id

order by likes_count desc

limit 10

You can view the Dashboard on the link as mentioned above.

Here’s a snapshot:

This was a really great learning experience as Panoply is so versatile with the various compatiblity for tools be it Database, Visualization, Storage, etc.

I went out of my comfort zone this time (I have experience working with Business Objects, Tableau, IBM Cognos and Power BI) This time i worked on Chartio and I’m glad I did as they have some of the most versatile charts and ease of use. The interface worked so effortlessly and I could create visualizations in minutes.

Thanks Kirsten Alana for letting me use your instagram data for analysis & visualization

I would also like to thank Kate Strachnyi for this wonderful opportunity as I followed her on Linkedin and saw this challenge and it really gave me a great experience looking at insights and working with Kirsten Alana’s instagram data analyzing pictures, videos, comments was so much fun.

Some things I wanted to try but couldn’t due to time constraints-

  1. Sentiments on posts’s comments(positive/negative)

  2. Understand if carousel media worked better than normal media

  3. Display locations in the form of city/country that had most viewer engagement.

Thanks Panoply for this wonderful data challenge. Overall this challenge was great and I’m glad I challenged myself with it working on new tools and data.

Feel free to connect with me here:
https://www.linkedin.com/in/jinal-doshi/

Announcing the winners of our first Instagram Data Challenge + Vote for People's Choice!