Im Laufe der Arbeiten am Konzept des Projektes „InstaLOC 2.0“ suchte ich nach einer Distinctausgabe über >1 Spalten. Konkret ging es hier um eine nachträgliche Ausgabe von Location-IDs zwecks Nachcrawling kompletter Länder.
Die Abfrage auf die Datenbankdatei „locplain_big.db“ lautet:
select distinct name, lat, long from locations where country_code = 'cn';
„cn“ lässt sich beliebig austauschen.
Im Rahmen der aktuellen Recherchearbeiten gelangten folgende Optionen in die nähere Auswahl:
(1) Google Maps Library: https://sourceforge.net/projects/gmlibrary/
(2) Google Maps in Delphi with TMS: https://flixengineering.com/archives/381
(3) DelphiMaps (alt!) https://github.com/WouterVanNifterick/delphi-maps
Um die Jahreswende veranlasste ich den angekündigten Wechsel der Datenerfassungstechnologie. Im Zuge dessen fanden konzeptionelle „Brainstormings“ bzgl. einer Abstrahierung sämtlicher Auswertungsansätze statt. Dies bedeutet, dass die Erkenntnisse aus dem Instagramuniversum auf weitere Plattformen und deren offenen Schnittstellen angewendet werden. Beispiele hierfür sind – aktuell – Twitter, Youtube, Pinterest, Tumblr und in bestimmten / eng gefassten Ansätzen Weibo + Facebook.
Interessant(er) geworden sind die Erkenntnisse aus den Zusammenführungen der anonymisierten InstaLOC-Rohdaten mit Openstreetmap. Folgende Datei / Tabelle verdeutlicht den aktuellen Datenbestand auf Basis von ca. 1.4Mio / 2.5 Mio abgeglichenen Geodaten:
Download: Instaloc_ Datenbestand/Statistik (Ergebnisse aus der Prüfung auf eine ca. 60GB umfassende SQLite-DB)
Die Datei beschreibt die Auflistung aller erfassten Länder nach Aufsummierung der codierten User, der Postings und – im experimentellen Status – der Aktiviäten in Form der Aufsummierten Zeitstempel. Hochinteressant ist die Position von Deutschland, was Rückschlüsse auf die Landesrolle im Sinne des Marketings auf der Plattform vermuten lässt.
In den nächsten 2 Wochen werde ich mich tiefergehender mit diversen Datamining- und Datenauswertungsvarianten auseinander setzen und nach Visualisierungsoptionen recherchieren. Die (Teil)fokussierung auf Geodaten erzwingt bspw. die Anbindung an die bekannten Maps-Services (Googlemaps, Openstreetmap etc.).
Gesprächsanfragen bitte an office(at)pontipix.de senden. Freigaben von Informationen und das Zulassen tiefergehender Gespräche werden in einem 4er Team diskutiert.
Vor einigen Tagen konnte ich die Datenerfassungsprozedur „InstaLOC 1.0 (BETA)“ finaliseren und mit den Openstreetmapmetadaten zusammen führen (siehe hierzu die Texte aus dem Blog).
Hier nun einige Basisdatenauszüge:
(1) 5000 beliebteste Tagwolken aus Deutschland
Link: https://bit.ly/2GoHgc1
Bit.ly-Statistik: https://bit.ly/2GoHgc1+
(2) 5000 beliebteste Tagwolken aus Bayern
Link: https://bit.ly/2S6mpeX
Bit.ly-Statistik: https://bit.ly/2S6mpeX+
(3) 5000 beliebteste Tagwolken aus Erfurt
Link: https://bit.ly/2UPRzce
Bit.ly-Statistik: https://bit.ly/2UPRzce+
(4) 5000 beliebteste Tagwolken aus Leipzig
Link: https://bit.ly/2QFKmgw
Bit.ly-Statistik: https://bit.ly/2QFKmgw+
(5) 5000 beliebteste Tagwolken aus Sachsen
Link: https://bit.ly/2Bvv8A3
Bit.ly-Statistik: https://bit.ly/2Bvv8A3+
Das Datenformat ist CSV und die Spalten sind: Tagwolke+Likes+Comments+Anzahl->Tags. Die Sortierung ist Likes->absteigend und die Tagzählung wurde mit der Anweisung length(tag) - length(replace(tag, ',', ''))+1 realisiert.
Bei Interesse an weiteren Einblicken freue ich mich auf einen Anruf oder eine eMail.
Durch die Zusammenführung beider Datenbanken wird eine wechselseitige Analyse auf Basis von
CREATE TABLE locations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
lat STRING,
long STRING,
name STRING,
osm_type STRING,
house_number STRING,
road STRING,
city STRING,
county STRING,
state_district STRING,
state STRING,
postcode STRING,
country STRING,
country_code STRING,
suburb STRING,
building STRING,
ruins STRING,
city_district STRING,
commercial STRING,
museum STRING,
library STRING,
aerodome STRING,
raceway STRING,
hamlet STRING,
hotel STRING,
clothes STRING,
parking STRING,
theatre STRING,
restaurant STRING,
footway STRING,
residental STRING,
supermarket STRING,
attraction STRING,
memorial STRING,
school STRING,
mall STRING,
beach STRING,
place_of_worship STRING,
hairdresser STRING,
stadium STRING,
bank STRING,
viewpoint STRING,
neigbourhood STRING,
university STRING,
car STRING,
zoo STRING,
fuel STRING,
bakery STRING,
bar STRING,
bus_stop STRING,
artwork STRING,
guest_house STRING,
village STRING,
cycleway STRING,
industrial STRING,
town STRING,
retail STRING,
wood STRING,
adress29 STRING,
residential STRING,
locality STRING,
garden STRING,
track STRING,
fast_food STRING,
pharmacy STRING,
picnic_site STRING,
castle STRING,
water STRING,
theme_park STRING,
golf_course STRING,
fort STRING,
car_wash STRING,
chemist STRING,
pedestrian STRING,
books STRING,
clinic STRING,
playground STRING,
community_centre STRING,
travel_agency STRING,
swimming_pool STRING,
sports_centre STRING,
hospital STRING,
florist STRING,
public_building STRING,
arts_centre STRING,
town_hall STRING,
cafe STRING,
pub STRING,
car_rental STRING,
fire_station STRING,
college STRING,
information STRING,
construction STRING,
viewpoint STRING
);
erlaubt. Themenwelten und Zielgruppen lassen sich nun auf Basis von hinterlegten Hotels, Straßen, PLZ, Stadtteilen oder auch Restaurants anstellen.
Heute wurde ein Update der InstaLOC-Systeme veranlasst. Die Hauptdatendatei umfasst nun ~40GB mit folgenden Grundzahlen:
(1) Unique Beiträge: 64.000.547
(2) Unique (codierte) Usernames: 14.980.773
(3) Unique Locations: 2.096.459 (vorher: 1.7 Mio)
Eine Besonderheit wird hier von den Tagwolken eingenommen. Diese sind mit der aktuellen Abfragelogik nicht mehr als „Distinct“ in einer annehmbaren Geschwindigkeit auslesbar. Sehr wahrscheinlich werde ich einen Berechnungsdurchlauf in der kommenden Nacht durchführen lassen.
Das Projekt wurde, nach Rückmeldung(en) der vielen Geschäftsfreund_innen und Kund_innen, umkonzeptioniert, so dass externe Datenquellen angedockt werden können. Hier wurde ein spezielles Problem (final) gelöst, welches so beschrieben werden kann:
Die konkrete Bestimmung von Städten auf Basis der Instagramlocationangaben ist teilweise fehlerhaft / nicht möglich.
Die konkrete Bestimmung von Stadtteilen und deren Vergleichbarkeiten innerhalb EINER Stadt ist auf Basis der Instagramlocationangaben nur eingeschränkt möglich.
Die Ausgangslage wurde nun so entschärft:
(a) Export der Locationdaten in Form einer Distinct-Anweisung.
(b) Extrakt der Angaben „lat“, „long“ und „name“ aus dem Datenexport via for-to-do-schleife.
(c) Übergabe von „lat“ und „long“ an die Openstreetmap-API, Extrakt der Resultate und Speichern in eine neue Datenbankdatei.
Für die nachfolgenden Beispiele gilt:
„The Database made available under the Open Database License: http://opendatacommons.org/licenses/odbl/1.0/. Any rights in individual contents of the database are licensed under the Database Contents License: http://opendatacommons.org/licenses/dbcl/1.0/
Der Datenbestand umfasst (jeweils unique):
164.222 Eintragungen / 1.7 Mio
24.185 Stadtteile
221 Länder zu 203 Ländercodes
1630 States (Bundesländer etc.)
1131 State Districts (Bezirke etc.)
9715 County (Gemeinden, Regionen etc.)
Download Beispieldaten, Überblick (PDF)
Länder+Ländercodes
Länder+Ländercode+Aufzählung-Städte/a>
Berlin+Stadtteil+PLZ
Srilanka+Stadt+Stadtteil+PLZ
China+Stadt+Stadtteil+PLZ
Indien+Stadt+Stadtteil+PLZ
BRD+Stadt+Stadtteil+PLZ
USA+Stadt+Stadtteil+PLZ
Da die Abgleichsprozedur noch aktiv geschaltet ist, werden sich die Werte mit sehr hoher Wahrscheinlichkeit verändern. Interessant ist hier die zukünftige Beobachtung der TOP-Länder aus der 2. Beispieldatei.
Die verbleibenden 1.5 Mio Eintragungen werden „sehr wahrscheinlich“ in den nächsten 1 1/2 Wochen komplett abgearbeitet und mit Finalisierung wird das Recherchekonzept um einen weiteren Prüfpunkt erweitert. Folgende Szenarien sind dann „denkbar“
(a) Gezielte Analyse von Stadtteilen auf:
-> Zielgruppen
-> Trends via Kombination aus Tags, Tagwolken, Summe Likes + Comments, Zeitstempel
(b) Vergleich von Stadtteilen
(c) Vergleich von Städten auch auf Stadtteilebene
(d) Querprüfung auf Städte, Länder, Stadtteile etc. bzgl. der generellen Aktivitäten im Medium „Instagram“
Weitere Ideen, Anregungen oder Lust auf Austausch? Gern unter den bekannten Telefonnummern und office(at)pontipix.de
Gestern bereinigte ich einen Bug in den Systemen von „InstaLOC“. Hierbei ging es um eine Aufbereitung der Locationdatensätze (1.7Mio) in eine leichter lesbare Form. Aufgefallen war mir, dass bspw. chinesische Zeichen in Form von „???“ beim Auslesen und Übertragen dargestellt wurden. Dies ist das Unicode-Problem und meine Lösung war simpel: Wechsel der Spaltenvariable von „VARCHAR“ auf „STRING“.
Download: http://pontimania.de/_dbdownload/testauszuege.zip
Inhalt sind 3 Exceltabellen:
(a) Testdaten nach der Tagsuche „Urlaub“
(b) Testdaten nach der Locationsuche „Bayern“
(c) Testdaten nach der Locationsuche „Germany“.
Die Tabellen (b) und (c) beziehen sich auf Substrings, welche in der Spalte „Location“ befinden und die Beispieldatensätze haben ein festgelegtes (!) Limit i.H.v. 50.000 Zeilen. Die Dateien haben die Inhalte: Tags, Anzahl Likes + Comments, Locationangaben, Zeitstempel.
Je nach Abfrage und Projekt lassen sich folgende Fragen beantworten:
(a) Wieviele User schreiben in welchem Zeitfenster über den Urlaub an welchen Orten?
(b) Welche Orte werden an Hotspots von welchen Usergruppen besucht und was wird da geschrieben?
(c) Welche User / Usergruppen neigen zur Trendbildung an welchen Orten und / oder zu welchen Tags (Themen, Themenwelten)?
(d) Welche Orte werden zu welchen Zeitfenstern|Jahreszeiten|Monaten besonders stark frequentiert und was wird da geschrieben?
(e) An welchen Orten machen lokale Marketingkampagnen Sinn? (gemessen am Kommunikationsvolumen, Inhalte)
(f) Welche Emotionen werden mit Orten + Trends verbunden? (gemessen an Tags, Uservolumen, Sprachfarbe, Piktogramme und Fotofilter)
(g) Was wird mit Produkten vebrinden? (gemessen an Tags neben den Branchentags, Locations)
(h) Existiert eine Zielgruppe für mein Produkt? (gem. an Location, Tags, Taggestaltung, Zeitstempel)?
(i) Wo befindet sich meine Zielgruppe? (Query: Tag|Tagkombination auf Location)
Konkretere Antworten auf diese und weitere Fragen beantworte ich gern nach Projektsichtung und Teambesprechungen. Kontakt: office(at)pontipix.de oder via den üblichen Kanälen (siehe Impressum).
Heute wurde der vorhandene Datenbestand aus den einzelnen Dateien zusammengeführt und entsprechend gespeichert.
Der Befehl für den technischen Vorgang ist:
INSERT INTO instaloc_main.locations(url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit) select url,tag,likes,comments,erstellzeit,post_id,username,location,filter,pruefzeit FROM _main_10112018.locations group by url;
Die Datenbank hat folgende Daten
Speicher: ca 30GB
Analysierbar sind:
49.676.379 Beiträge
36.574.115 Tagwolken
12.705.076 (codierte) Nutzer
1.794.336 Loations
23.358.582 Zeitstempel
Mit Etablierung der InstaLOC-Datenbanken und den angedockten Zielgruppen- und Marktanalysen wurden zeitgleich diverse Tests mit bestehenden Accounts durchgeführt.
Diese Szenarien sollten klären:
(a) Followerwachstum
(b) Interaktionen auf den Medien, Videos, Stories
(c) Interaktionsqualitäten und Nachhaltigkeiten
Vor ca. 4 Wochen wurden 2 Testaccounts ausgewählt, Gespräche geführt und an das System angedockt.
Die beobachteten Effekte sind:
(a) Followerwachstum (gerundet)
Account: (A)
Thema: Kunst, Art, Fotografie
13.000 Follower -> 26.000 Follower -> 40.000 Follower
Account: (B)
Thema: Coaching/Consulting
1.500 Follower -> 17.000 Follower -> 20.000 Follower
(b) Interaktionen auf die Medien
Account: (A)
Thema: Kunst, Art, Fotografie
Interaktionsspannbreite, Bilder – Likes: 130 bis ~3.000
Interaktionsspannbreite, Videos – Likes + Views
Account: (B)
Thema: Coaching/Consulting
Interaktionsspannbreite, Bilder – Likes: ~350 – ~800
Account (A) entwickelt sich erstaunlich gut und es sind ca. 200-260 dauerhafte Besucher_innen auf die verlinkte Seite identifizierbar. Ebenfalls beobachte ich starke Kommunikation via „DM“, eMail und auch Telefon aus den Spektren der angesprochenen Zielgruppen (hier: Galerien, Fotograf_innen, Kunst allgem.).
Account (B) zeigt eine starke Etablierung der zielgruppenorientierten Kommentare und eine Einpendelung der Interaktionen auf ~800 / Medium. Erste Anfragen via DM und eMails sind ebenfalls nachvollziehbar.
Nach Zahlensichtung, Ergebnisanalysen und diverse Brainstormings wurden letzte Woche weitere Accounts aus dem Bereich „Handel“ und „Home & Living“ in die Strategie überführt. Hier sind Effekte analog zu Account (A) und Account (B) sichtbar.
Interesse an einer Partnerschaft? Fragen?
Gerne unter office(at)pontipix.de
Weitere Informationen unter: https://www.pontimania.de/
