PlayStation¶
1. Links¶
App Scripts: <Insert Link>
App Scripts – PAA & CD: <Insert Link>
BigQuery: <Insert Link>
Main Dashboard: <Insert Link>
Backup Dashboard: <Insert Link>
2. Extract Layer (App Scripts)¶
Script Name |
Dataset |
Table Name |
Frequency |
Notes |
|---|---|---|---|---|
Daily Positions |
positions |
playstation_positions_upload |
Daily |
— |
Search Volume |
search_volumes |
raw_search_volumes |
Monthly |
— |
Competitor 1 |
positions |
competitor_main_positions_upload |
Daily |
— |
Competitor 2 |
positions |
competitor_main2_positions_upload |
Daily |
— |
Do not use competitor_positions_upload (contains mixed competitor data from another account).
3. Transform Layer (BigQuery – Scheduled Queries)¶
Query Name |
Dataset |
Table Name |
Frequency |
Notes |
|---|---|---|---|---|
PlayStation Positions Upload (7am) |
positions |
Playstation_Positions |
Daily |
— |
Competitor Position Push |
positions |
Playstation_Positions_Master |
Daily |
— |
New Competitor Position Push |
positions |
Playstation_Positions_Master |
Daily |
— |
High-Storage Positions Push |
positions |
Playstation_Positions_Master |
Daily |
— |
Competitor Customer Number* |
master |
Customer_Numbers_Check |
Daily |
/2 mobile/desktop |
PlayStation Customer Numbers* |
master |
Customer_Numbers_Check |
Daily |
/2 mobile/desktop |
PlayStation Customer Growth* |
master |
Potential_Customer_Growth |
Daily |
— |
SV_SOV_Calc (Keep) |
master |
kw_SV_SOV_table |
Daily |
— |
Share of Voice Calculation |
master |
Share_Of_Voice_Values |
Daily |
— |
CD_Positions_push |
positions |
cd_Positions_Master |
Daily |
— |
CD_DirectNet_positions_push |
positions |
cd_Positions_Master |
Daily |
— |
CX_PAA_SV |
master |
CX_PAA_comp |
Weekly |
— |
Search Volume Monthly |
master |
Search_Volumes |
Monthly |
— |
Trending Volume |
search_volumes |
New_Trending_volumes |
Monthly |
— |
Monthly Classic Query¶
“Calculating Max Number of Possible Customers per Term”
Updates: master.maximum_possible_customers
Run monthly after Search Volume updates.
Storage Notes¶
Playstation_Positions archived to: Playstation_Positions_old_data_till_10Jan25
Current table holds data from 10 Jan 2025 onwards.
Review processing cost after a few months.
4. Disabled Queries¶
Query Name |
Dataset |
Table Name |
|---|---|---|
Positions Data Validation |
test |
Positions Data Validation |
Customers_received_by_URL |
master |
Customers_received_by_URL |
Keyword Share of Voice |
master |
test_kw_SOV_table |
New KW SOV |
master |
new_kw_SOV_table |
New SOV KW (LookerStudio) |
master |
New_Share_Of_Voice_Values |
LS_SV_SOV_KW |
master |
LS_SV_Share_Of_Voice_Values |
Notes:
New AOV KW and New KW SOV queries were improved due to missing SearchTerm columns in previous versions.
Positions Data Validation added for competitor data QA.
5. IGDB Queries¶
Query Name |
Dataset |
Table Name |
|---|---|---|
IGDB_positions_push |
positions |
igdb_Positions_Master |
IGDB_SOV_calculation |
master |
igdb_kw_SOV_table |
IGDB_unreleased_table |
master |
igdb_unreleased_data |
IGDB_metric_normalisation |
master |
igdb_unreleased_data_normalised |
IGDB_metric_segmentation |
master |
igdb_unreleased_data_normalised_metricSegmented |
6. Dashboard Tables¶
Table Name |
Charts |
Dataset |
Notes |
|---|---|---|---|
Customer_Numbers_Check |
2+2 |
master |
Replaced old tables |
New_Trending_volumes |
4 |
search_volumes |
Monthly |
Potential_Customer_Growth |
1 |
master |
— |
keyword_categorisation |
3 |
keyword_classification |
— |
Share_Of_Voice_Values |
16 |
master |
— |
kw_SV_SOV_table |
4+5 |
master |
— |
Playstation_Positions_Master |
38 |
positions |
— |
Search_Volumes |
1 |
master |
Monthly |
igdb_unreleased_data_normalised |
6 |
master |
— |
CX_PAA_comp |
2 |
master |
Weekly |
igdb_unreleased_data_normalised_metricSegmented |
3 |
master |
— |
7. Filters (Looker Studio)¶
Filter Name |
Table |
Description |
|---|---|---|
Tag_null_filter |
Customer_Numbers_Check |
Tag IS NULL |
Playstation Sites Only |
Customer_Numbers_Check |
Site Contains playstation.com |
Playstation Sites Only (Positions) |
Playstation_Positions_Master |
Include playstation.com |
Competitors Only |
Customer_Numbers_Check |
Exclude Playstation |
Site Filter |
Share_Of_Voice_Values |
Include playstation.com |
Tag Filter |
Customer_Numbers_Check |
Exclude NULL Tags |
Search Landscape Filter |
Playstation_Positions_Master |
Exclude SERPFeature IS NULL |
SearchTerm Remove Duplicates |
New_Trending_volumes |
Remove duplicates |
Branded Terms Filter |
New_Trending_volumes |
Exclude game titles |
Category_Null |
Search_Volumes |
Needs validation |
Customer_Page |
kw_SV_SOV_table |
Category = CX |
Customer_Positions |
Playstation_Positions_Master |
Category = CX |
Customer_trending |
New_Trending_volumes |
Subcategory contains Error Codes |
Sankey_null |
Customer_Numbers_Check |
EstimatedNumberOfCustomersReceived ≠ 0 |
PAA Account Error Codes |
CX_PAA_comp |
Multi-condition filter |
Many more as required |
— |
— |
8. Triggers¶
Competitor Triggers¶
Dataset |
Table |
Time IST |
|---|---|---|
main |
mainPosEbayCom |
6 |
main |
mainPosAmazon |
6 |
main |
mainPosBoulanger |
6 |
main |
mainPosRakuten |
7 |
main |
mainPosReddit |
7 |
main |
mainPosSteam |
7 |
main |
mainPosTarget |
8 |
main |
mainPosVoidGaming |
8 |
main |
mainPosXbox |
8 |
main |
mainPosYouTube |
10 |
main2 |
mainPosMicromania |
8 |
main2 |
mainPosMetacritic |
8 |
main2 |
mainPosGamepro |
8 |
main2 |
mainPos4Players |
10 |
main2 |
IGN |
6 |
main2 |
EA |
6 |
main2 |
Ubisoft |
6 |
main2 |
PSNET |
7 |
main2 |
PS repair |
7 |
main2 |
Nintendo |
7 |
PlayStation Triggers¶
Dataset |
Table |
Time IST |
|---|---|---|
ps_positions |
mainPosUS |
9 |
ps_positions |
mainPosJP |
9 |
ps_positions |
mainPosDE |
9 |
ps_positions |
mainPosUK |
10 |
ps_positions |
mainPosFR |
10 |
9. Notes¶
• New KW SOV and AOV queries resolve missing SearchTerm field.
• Multiple legacy queries disabled to reduce redundancy.
• PS positions table was archived to reduce storage cost.
• Monthly classic query must run after Search Volume update.