• Fetch the latest 6 weeks aggregate data for FaceBook, Instagram, Twitter, Reddit and Stark model
• Send the fetched data as slack messages to a slack channel via slack web hook
• Update the latest two weeks fetched data to AWS aurora mysql database

• 1. Program Structure
• 2. Dependencies used
• 3. SQL Queries Used

• class : DataAggregatorReport
  • twitterAggregator()
  • facebookAggregator()
  • instragramAggregator()
  • redditAggregator()
  • starkAggregator()
• class : WeeklyDataCollectionReport
  • dateRangeGenerator()
  • sendSlackMessage()
  • dataAggregatorReport()
• main()

requires two parameters fromDateTime (starting date range) and toDateTime (ending date range) which are used in the SQL queries to fetch records from AWS Athena for that particular date range.

All the network functions defined are limited to return only the first 6 records of the weekly count data for the given date range.

All the queries for each particular network are defined as python dictionaries in their respective functions.

returns the below columns

returns the below columns

returns the below columns

returns the below columns

returns the below columns

• arguments : "query" - static sql query of type string
• returns : sql output as DataFrame

requires a datetime value for dateRangeGenerator() to work.

• uses class : DataAggregatorReport to fetch the data for a given date range.
• the dataframes fetched is formated/structured well to be sent to the slack channel

using the class variable currentDateTime sets the operable datetime to previous week's Wednesday generates a start and end datetime range using the created datetime of wednesday and returns the start/from date as previous 7th week's wednesday and end/to date as previous week's tuesday. Hence, @line-674 weekOffset = 7

Example 01, for any given date 2021-09-26 to 2021-10-02, lastWednesdayDate = "" it will return a python dictionary fromDateTime : "", toDateTime: ""

Example 02,for any given date 2021-10-03 to 2021-10-09, lastWednesdayDate = "" it will return a python dictionary fromDateTime : "", toDateTime: ""

arguments : 
 "message" = stringified table 
 "tableInfo" = heading for slack message 

sends the slack message as a post request for the specified slack web hook

datetime : Date Operations
pandas : SQL and Data Operations
pyathena : AWS Athena SQL query execution
requests : Slack HTTP Post request
tabulate : styling of tables for slack messages

write all the sub-queries used explain the overview of the columns, the database and column returned used,