To Do List

• [x] Activate Google console APIs
• [x] research supermetrics and which data is possible to pull and how they will be displayed in gsheet see other task
• [x] Decide on a language to work with - either python or gscript
• [x] Implement the code in gcript that adds the new daily data into the database. Priority: Instagram data. Daily data extracted here. Place to store the data in this link. Please, do not modify any of these files without consulting first. @Ana Chacón @Winnie Shi
• [ ] Implement the dashboard using google Data Studio.

Current Status

KPI defined. Data from our social media currently being extracted every day. Currently working on the dashboard.

Overview

Problem statement

Creating a dashboard in data studio that will adjust every day according to the new data inflow. it has to update the list of locations (country city and postalcode/neighborhood) according to users location, it has to create a new column after the header of the rows to allow space for the new data so that the old data will shift to the right and leave the new data close to the headers on the left. The data is pulled from Google Analytics using Supermetrics, and it's pasted into a google sheet called rawData, which will be updated automatically every day. This data needs to be stored as well, using a separate google sheet that will contain all the historical data.

Success criteria

The criteria that must be met in order to consider this project a success.

• Integrate as many metrics as possible for actionable insights, and create the most automated system as possible and the most flexible as possible.

Current Solution

• Use Supermetrics to routinely dump the dataload from out social media (Twitter, Facebook, LinkedIn and Instagram) on an sheet called "rawdata" in the dashboard . The data is rewritten every day, and it can't be stored in Data Studio due to storage limitations, so needs to be copied onto a new google sheet that will keep all the historical data.
• All historical data is kept in a separate spreadsheet that copies the data from rawdata daily, using google script code.
• The dashboard will be created using Google Data Studio. It will be automatically updated every day and will show the KPI values described currently in the goggle sheet template. Link here.
• Data from GA (Google App + web) that will be extracted with Big queries will be stored in a gsheet to keep the history and to make sure we don't excide the query and storage limits of the free tier

Dashboard information: